Методичка по информатике. Практикум по дисциплине Информатика Допущено Редакционноиздательским советом угату в качестве учебного пособия для студентов, обучающихся по направлениям подготовки бакалавров 080100 Экономика
 Скачать 8.99 Mb.
|
|
6.2.2. Варианты заданий для самостоятельного выполнения. 1. Составить таблицу для вычисления n членов арифметической прогрессии и их суммы. Формула n-го члена арифметической прогрессии: ) 1 ( 1 n d a a n . Формула суммы n первых членов арифметической прогрессии: 2 / ) ( 1 n a a S n n , где a 1 – первый член прогрессии, а d – разность арифметической прогрессии. Значения первого члена и разности задайте самостоятельно. 185 2. Составьте таблицу для возведения целых чисел от 1 до 9 в степень n. Показатель степени n Число 1 2 3 4 5 6 7 8 9 1 1 1 1 1 1 1 1 1 1 2 2 4 8 16 32 64 … … … 3 3 9 27 … … … … … … 4 4 16 … … … … … … … 5 5 25 … … … … … … … 6 6 36 … … … … … … … 7 7 49 … … … … … … … 8 8 64 … … … … … … … 9 9 81 … … … … … ... … Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автокопирования формулы! 3. Составить таблицу для зачисления абитуриентов № пп ФИО абитуриента Экзамены Всего баллов Отметка о зачислении Математика Русский Язык Физика 1 Иванов И.Н. 60 50 50 160 Не зачислен 2 Сидоров С.С. 70 70 80 220 Зачислен 3 Петров П.П. 75 85 70 230 Зачислен Зачисляемых студентов и набранные ими баллы дополнительно выделять цветом ячеек, пользуясь командой Формат / Условное форматирование. 4. Составить таблицу для решения квадратных уравнений вида 0 2 c bx ax Предусмотреть возможность решения задания при 0 a . 186 5. Составить таблицу для расчета платы за проезд. 6. Составить таблицу квадратов двузначных чисел, пользуясь абсолютными ссылками на ячейки. 0 1 2 3 4 5 6 7 8 9 1 100 121 144 169 196 225 256 289 324 361 2 400 441 484 529 576 625 676 729 784 841 … … … … … … … … … … … 9 8100 8281 8464 8649 8836 9025 9216 9409 9604 9801 Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автозаполнения! 7. Составить таблицу для расчета выпуска продукции. Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автозаполнения! 187 8. Составить таблицу для анализа зависимости «затраты- эффект» для рекламной деятельности. №пп Наименование видов деятельности Затраты на проект Результат (эффект) проекта Эффектив- ность проекта Суммар- ные затраты Суммарный результат 1 Журналы 300 110 0,367 300 110 2 Газеты 500 40 0,080 800 150 … … … … … … … 7 Выставки 6000 70 0,012 15900 405 Затраты на проект оцениваются в денежных единицах, эффект – отклики на рекламу. Эффективность – отношение эффекта (результата) к затратам. После определения эффективности виды деятельности необходимо ранжировать по ее убыванию, после чего рассчитать оставшиеся показатели (колонки 6,7). Суммарные затраты и суммарный эффект вычисляются нарастающим итогом. 9. Составить таблицу для расчета платежей за электроэнергию. Стоимость 1 кВт.ч, первое начальное и все конечные значения придумать самим, остальные значения рассчитать. Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автозаполнения! 188 10. Составить таблицу для определения маржинальной прибыли и маржинальной рентабельности. Денежные средства на начало периода Наименование Показателя Периоды времени 1 2 3 4 5 6 7 1 Поступления 2 Платежи всего 3 В том числе переменные издержки 4 Постоянные издержки 5 Сальдо 6 Наличие денежных средств 7 Маржинальная прибыль 8 Маржинальная рентабельность Маржинальная прибыль – разность между выручкой за реализацию продукции и переменными затратами (т.е. затратами, растущими пропорционально объему продаж). Маржинальная рентабельность – отношение маржинальной прибыли к переменным затратам. Характеризует предельно возможную эффективность использования финансов. 11. Составить таблицу для вычисления n-членов геометрической прогрессии и их суммы. Формула i-го члена геометрической прогрессии , ,..., 1 , 1 1 n i q a a i i где q – знаменатель геометрической прогрессии. Сумма всех членов геометрической прогрессии ( ) 1 q равна 1 1 ) 1 ( 1 1 q q a q q S n n n Значения первого члена и знаменателя задайте самостоятельно. 189 12. Составить таблицу, позволяющую прогнозировать стоимость товара в зависимости от изменяющейся цены (в $) и изменяющегося курса $. Количество товара Прогноз стоимости товара Цена товара, $ Цена товара, $ Курс $ Январь Февраль Март Апрель … Цена при 26,3 26,7 26,8 … … Курс $, январь 28,567 февраль 28,515 март … … … 13. Составить таблицу для определения сроков и объемов требуемых заемных средств. Денежные средства на начало периода 100 Наименование Показателя Периоды времени 1 2 3 4 5 6 7 1 Поступления 324 3 9 6 45 12 200 2 Платежи 223 16 34 200 34 100 4 3 Сальдо 101 -13 -25 -194 11 -88 196 4 Наличие денежных средств 201 188 163 -31 -20 -108 88 5 Потребность в займе средств нет нет нет 31 20 108 нет Предусмотреть дополнительное визуальное информирование пользователя о результатах расчетов. 190 14. Составить таблицу для расчета сырья, необходимого для производства 100 кг хлеба. 6.2.3. Задание на построение диаграмм для самостоятельного выполнения. Создайте по таблице из задания 6.2.2 произвольные диаграммы, отражающие следующие зависимости: соотношение различных соизмеримых по значениям рядов для каждой строки; процентное соотношение итоговых значений столбцов; сравнение второго и четвертого столбца. Данные для построения выбирать по смыслу, элементы оформления задавать произвольно. Использовать различные типы диаграмм, каждую располагать на отдельном листе. 7. Методика выполнения заданий 7.1. Расчет табличных значений Понятие электронной таблицы Основные правила: 1. Ввод формул начинайте со знака «=». Внутри формулы не допускаются пробелы, а в адресах используются только латинские буквы. 2. Завершайте ввод в ячейку и ее редактирование нажатием клавиши Enter для сохранения выполненных изменений. 3. Для форматирования данных необходимо активизировать ячейку с данными или выделить блок ячеек. Основные команды форматирования вынесены на вкладку ленты Главная. 191 4. Заголовок таблицы введите в ячейку, расположенную выше строки, с которой начинается таблица. Для центрирования заголовка по ширине таблицы выделите блок ячеек в строках с заголовком от первого до последнего столбца с таблицей и выполните команду Объединить и поместить в центре. 5. Для выбора функции =СУММ() использовать вкладку Главнаяпанель Редактирование. Откроем приложение Microsoft Excel. Создадим таблицу ЗАРПЛАТА СОТРУДНИКОВ ФИРМЫ с полями №, ФИО, ОКЛАД, ПРЕМИЯ, ПОДОХОДНЫЙ НАЛОГ, К ВЫДАЧЕ. Для этого в ячейку А1 введем с клавиатуры текст ЗАРПЛАТА СОТРУДНИКОВ ФИРМЫ, данный текст вышел за границы ячейки, пока не будем обращать на это внимания. Аналогично введем соответствующие надписи в столбцы второй строки. В ячейках А2:F2 разрешим перенос по словам, для этого выделим эти ячейки и на панели Выравнивание вкладки Главная в диалоговом окне Формат ячеек (открываемой с панелей инструментов), либо из контекстного меню (вызываемого при нажатии на ячейке правой кнопки мыши) на закладке Выравнивание установим флажок в окне Перенос по словам (рис. 3, рис. 4). Рис. 3. Формат ячеек 192 Рис. 4. Вкладка Выравнивание Изменим размеры столбцов, для этого установим курсор сначала на границе между столбцами В и С (для расширения столбца В) в верхней части таблицы и когда курсор примет вид черной двунаправленной стрелки и при нажатой левой клавише мыши изменить границы столбцов до нужного размера. Для ввода нумерации строк в первый столбец используем прием АВТОЗАПОЛНЕНИЯ, для этого в ячейку А3 введем 1, в ячейку А4 – 2. Выделим обе ячейки и поставим курсор мыши в правый нижний угол выделенного блока ячеек, как только курсор примет вид черного креста (МАРКЕРА ЗАПОЛНЕНИЯ) при нажатой левой клавише мыши протянем заполняемую область до ячейки А10 (включительно). Далее заполним таблицу ( Рис. 6), предварительно определив формат ячеек в столбцах таблицы. Для этого на панели Выравнивание вкладки Главная в диалоговом окне Формат ячеек на закладке Число выбираем категорию Числовые форматы. До этого необходимо выделить интервал ячеек, в которых должен быть определен формат (рис. 5). 193 Рис. 5. Числовые форматы Премия считается как =Оклад*0,1, для этого следует встать на ячейку D3 и ввести формулу =C3*0.1, как показано на рис. 6. Рис. 6. Расчет в столбце «Премия» 194 Рис. 7. Расчеты в таблице ● Подоходный налог считается как = (Оклад+Премия)*0,13, для этого следует встать на ячейку Е3 и ввести формулу =(C3+D3)*0,13. ● Сумма к выдаче считается как =Оклад+Премия–Подоходный налог, для этого следует встать на ячейку F3 и ввести формулу =C3+D3- Е3, (рис. 7) ● Остальные ячейки таблицы рассчитать с помощью автозаполнения. Под таблицей подсчитать итоги по столбцам с помощью значка автосуммирования Главная → Редактирование → Сумма (рис. 8). Рис. 8. Кнопка автосуммирования Условное форматирование Используя команду Условное форматирование выделить другим цветом суммы к выдаче, меньшие 2000 руб. Для задания условного форматирования надо выделить блок ячеек F3:F10 и выбрать команду Главная→Стили→Условное форматирование. В открывшемся меню для задания определенного правила выделения ячеек нужно выбрать пункт Правила выделения ячеек (рис. 9). 195 Рис. 9. Условное форматирование В результате таблица будет иметь следующий вид: Рис. 10. Результат условного форматирования Сортировка При вводе исходных данных в таблицу не учитывался алфавитный порядок, поэтому можем произвести сортировку. Для этого поставим курсор в ячейку В3 (ФИО) и выполним команду Данные→Сортировка и фильтр и нажмем на пиктограмму , таблица отсортировалась. Но при этом мы видим, что в таблице у нас имеются записи с однофамильцами. Произведем дальнейшую сортировку по окладу. Команду настраиваемой сортировки можно вызвать через Данные→Сортировка и фильтр→Сортировка, либо через Главная→Редактирование→Сортировка и фильтр. В появившемся окне Сортировка нужно указать: столбец (раскрывая 196 списки Сортировать ПО); сортировка и порядок списка данных. Для добавления еще одного критерия сортировки нужно использовать кнопку Добавить уровень (рис. 11). Чтобы данные первой строки списка не участвовали в сортировке, нужно поставить флажок «Мои данные содержат заголовки». Рис. 11. Окно Сортировки В результате таблица будет выглядеть, как представлено на рис. 12. Рис. 12. Результат сортировки Чтобы задать сортировку не строк, а столбцов диапазона, нужно, нажав кнопку Параметры, указать «сортировать столбцы 197 диапазона». При нажатии клавиши ОК получаем полностью отсортированную таблицу. Мастер функций В Excel существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры (при этом активируется средство Автозаполнение формул, позволяющее по первым введенным буквам выбрать нужную функцию, рис. 13), а можно выбирать в окне Мастер функций, активируемом кнопкой Вставить функцию на панели Библиотека функций вкладки Формулы или из групп функций на этой же панели (рис. 14), либо с помощью кнопки панели Редактирование вкладки Главная (рис. 15). Полезной возможностью по работе с формулами является отображение всех формул на листе. Это можно сделать, используя команду Формулы→Зависимости формул→Показать формулы. После этого в ячейках вместо вычисленных значений будут показаны записанные формулы. Для возврата в обычный режим нужно еще раз нажать кнопку Показать формулы (рис. 16). Под таблицей с помощью мастера функций, который вызывается по команде Формулы→Библиотека функций→Вставить функцию,подсчитать средний оклад сотрудников, максимальную сумму к выдаче и минимальный подоходный налог. 198 Рис. 13. Автозаполнение формул Рис. 14. Мастер функций 199 Рис. 15. Список кнопки автосуммирование Рис. 16. Режим формул Фильтрация (выборка данных) Для просмотра не всей таблицы, а лишь данных, удовлетворяющих некоторому условию, обращаемся к помощи фильтров. Для установки фильтра выделяется вся таблица, включая заголовок, без итогов. Если требуется просмотреть всю совокупность выбранных данных по тому или иному критерию выбираем Данные→Фильтр. В каждой ячейке заголовка появится кнопка, на которой изображена направленная вниз стрелка. При нажатии этой кнопки появится подменю, в котором выберем пункт Числовые фильтры, что вызовет появление нового диалогового окна. В данном окне установим условия просмотра: Оклад>=3000 (рис. 17). 200 Рис. 17. Фильтрация данных После чего на экране останутся только те записи из таблицы, которые соответствуют, заданному условию. Вернуться к первоначальному виду таблицы можно вновь выбрав ячейку заголовка Оклад, на которой изображен фильтр с направленной вниз стрелкой. При нажатии этой кнопки появится подменю, в котором выберем пункт Удалить фильтр с «Оклад» илипоставить галочкув окошке Выделить все (рис. 18). Выберите людей с зарплатой от 2000 до 3000 рублей. Выберите 3 служащих с наименьшей суммой зарплаты с помощью пункта ПЕРВЫЕ 10. Выберите людей, фамилии которых содержат букву «е». Выберите людей с окладом более 3000, фамилии которых начинаются на букву «С». 201 Рис. 18. Отмена предыдущей фильтрации Использование формул для принятия решений Вычислить аванс в седьмом столбце таблицы по следующему принципу: если сумма к выдаче превышает 3500 рублей, то аванс составляет 2000 рублей, иначе – 40% от суммы к выдаче. Для вычисления значения аванса необходимо использовать функции раздела Логические. Функция ЕСЛИ в зависимости от истинности параметра Лог_выражение возвращает либо Значение_если_истина, либо Значение_если_ложь. Таким образом, вызвав функцию ЕСЛИ (рис. 19), необходимо указать следующие значения параметров. 202 Рис. 19. Выбор функции Если После этого нажатие кнопки ОК завершает создание формулы (рис. 20). После того как высчитан аванс для первого человека в списке, необходимо выполнить АВТОЗАПОЛНЕНИЕ остальных ячеек этого столбца. Рис. 20. Аргументы функции ЕСЛИ 203 Обработка массивов Найти количество человек с окладом более 3000 рублей. Чтобы подсчитать количество таких записей следует воспользоваться функцией СУММ, аргументом которой будет функция ЕСЛИ, проверяющая, не превышает ли очередное значение в столбце «ОКЛАД» числа 3000. При этом будет использована так называемая работа с массивами, так как одну и ту же проверку необходимо выполнить не для одной ячейки (С3>3000), а для диапазона ячеек (С3:С10>3000), который в данном случае называется массивом. Таким образом, значения параметров вложенной функции ЕСЛИ примут следующий вид (рис. 21, рис. 22). Рис. 21. Функция СУММ Рис. 22. Вложение функции ЕСЛИ 204 Однако после того как формула будет введена, в ячейке отобразится ошибка: #ЗНАЧ. Чтобы по формуле, в которой используется работа с массивами, было вычислено правильное значение, необходимо выделить ячейку с формулой, установить курсор в строку формул и нажать сочетание клавиш Ctrl+Shift+Enter. После этого формула будет заключена в фигурные скобки, которые означают работу с массивом:{=СУММ(ЕСЛИ(С3:С10>3000;1;0))}, см. рис. 23. Рис. 23. Результат вычислений Графика Основные правила: Для создания диаграммы необходимо выделить блок данных, на основании которых строится диаграмма. В выделяемый блок данных включите не только числовые данные, но и заголовки строк (столбцов), в которых они расположены. Заголовки будут использованы в качестве подписей по осям (меток) и для формирования условных обозначений (легенды). При выделении блоков с данными для построения диаграмм необходимо соблюдать два правила: выделенный фрагмент должен состоять из равновеликих столбцов; в выделенном фрагменте не должно быть объединенных ячеек. Строка формул 205 Постройте гистограмму «ФИО→Оклад→Аванс». Постройте круговую диаграмму «ФИО→Оклад». Для построения ГИСТОГРАММЫ выделите ячейки, значения которых войдут в гистограмму – диапазон ячеек В3:В10, С3:С10 и диапазон ячеек F3:F10. Для того чтобы выделить несмежные ячейки сначала выделите ячейки столбца В, а затем при нажатой клавише Ctrl – ячейки столбцов С, F. Вызвать диалоговое окно МАСТЕРА ДИАГРАММ можно следующим образом: вкладка Вставка→Гистограмма на панели инструментов. Все изменения удобно проводить с использованием контекстного меню. Попробуйте изменить вид диаграммы и другие параметры (рис. 24). Для построения КРУГОВОЙ ДИАГРАММЫ (рис. 25) необходимо перейти на вкладку Вставка |