Табличный процессор microsoft excel
 Скачать 1.83 Mb.
|
Практическое задание № 4«Использование функций и фильтра» Цель: освоить приемы написания формул с использованием функций, копирования формул, автозаполнения и фильтрации данных. В табличном процессоре Excel создайте, оформите и рассчитайте таблицы 1 и 2, как приведено на рисунках.  Рис. 45. Расчет таблицы с использованием функций Технология выполнения задания Сохраните новую книгу под именем Использование функций на рабочий диск в папку Информатика. В шапке таблицы (строка 2) необходимо установить в формате ячеек параметры выравнивания: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам. К диапазону ячеек A2:H42 применить тип обрамления – все границы. В ячейки A1:H2 данные вводятся с клавиатуры. Данные в столбец А вносятся с помощь автозаполнения. Для этого в ячейку А3 вводится с клавиатуры цифра «1». Курсор ставится в ячейку А3 и выбираются коман- ды: Заполнить, Прогрессия. В диалоговом окне устанавливаются параметры: по столб- цам; предельное значение – 40. В столбце B данные в ячейки B3 и B4 вводятся с клавиатуры («1» и «140», соот- ветственно). В ячейке B5 составляется формула = B3+2. В ячейке B6 формула = B4-10. Для расчета ячеек ниже выделяются обе ячейки с исходными формулами B5:B6 и ко- пируются в B7:B42. В столбцах C:H расчеты производятся с помощью функций Excel. Функции вы- зываются нажатием кнопки F(х), затем выбирается соответствующая категория и функция, в открывшимся диалоговом окне вводятся параметры (аргументы) функции. В столбце C проверка условия осуществляется с помощью логической функции: ЕСЛИ(B3<10;да;нет). Для выделения значений цветом для ячеек С3:С42 необходимо установить условное форматирование с правилом «равно». Для «да» следует выбрать красный формат, для «нет» - зеленый. В столбце D возведение в степень осуществляется через одноименную матема- тическую функцию: Степень(B3;A3). Формат числа – экспоненциальный. Через функцию Степень в Excel также можно извлекать корень из числа. В столбце E используем: Степень(D3;1/A3). В столбце F вычисление косинуса производится через соответствующую мате- матическую функцию: COS(B3). Форматирование числа: числовой, число десятичных знаков – 2, отрицательные – красный. Чтобы положительные числа отображались си- ним цветом и со знаком «+», нужно в формате числа выбрать Все форматы и добавить недостающий текст: [Синий]+0,00_ ;[Красный]-0,00\ В столбце G округление значения столбца F до десятых в большую сторону: ОКРУГЛВВЕРХ(F3;1). Формат числа как в столбце F. В столбце H необходимо с помощью одноименной функции из категории Дата и время вычислить текущую дату и от полученного значения вычесть данные столбца B: ТДАТА()-B3. Форматирование числа: Дата; *14 марта 2001 г.  Рис. 46. Фильтр данных В ячейках шапки таблицы A2:H2 установите фильтр. (Рис. 46) Настройте фильтр на отображение: в столбце C – значение «нет»; в столбце F числовой фильтр - больше или равно 0. Отобразите все данные таблицы (Очистите фильтр).  На Листе 2 книги Использование функций создайте таблицу 2 (Рис. 47) и рас- считайте ее сводные показатели на основе данных таблицы 1.Рис. 47. Функции свода данных В ячейке B3 установите проверку данных – список: да;нет. В ячейке B4 необходимо определить количество совпадений в столбце С табл. 1 с условием для расчета (B3) с помощью статистической функции: СЧЁ- ТЕСЛИ(Лист1!C3:C42;B3). В ячейке B5 вычислить сумму значений в столбце B табл. 1 при совпадении в столбце С значений с условием для расчета (B3) с помощью математической функции: СУММЕСЛИ (Лист1!C3:C42;B3;Лист1!B3:B42). В ячейке B6 посчитать сумму произведений значений столбца A и F (табл. 1) с помощью математической функции: СУММПРОИЗВ (Лист1!A3:A42;Лист1!F3:F42). В ячейке B7 определить среднее арифметическое значение данных столбца B с помощью статистической функции: СРЗНАЧ(Лист1!B3:B42). В ячейке B8 найти минимальное значение столбца H (табл. 1) с помощью стати- стической функции: МИН(Лист1!H3:H42). В ячейке B9 нужно найти минимальное значение столбца H (табл. 1), у которых в столбце B (табл. 1) выполняется условие данного расчета (ячейка B3). Расчет делается с помощью логической и статистической функций, а также с применением формулы массива (нажать Ctrl+Shift+Enter): {=МИН(ЕСЛИ(Лист1!C3:C42=B3;Лист1!H3:H42))}. Поменяйте условие расчета на «нет». Проверьте правильно ли изменились свод- ные данные. В ячейке B10 установите проверку данных – список: =Лист1!$A$3:$A$42. В ячейке B11 дата должна выбираться из таблицы 1, соответствующая порядко- вому номеру установленному в ячейке B10. Используем функцию Вертикальный ПРо- смотр из категории ссылки и массивы: =ВПР(B10;Лист1!$A$2:$H$42;СТОЛБЕЦ(Лист1!$H$2);0). Аргументы функции ВПР: Искомое значение: B10; Таблица: Лист1!$A$2:$H$42; Номер столбца: СТОЛ- БЕЦ(Лист1!$H$2) или 8; Интервальный просмотр: 0.) Поменяйте № п/п на 10. Проверьте правильно ли изменились сводные данные. Сохраните книгу. |