Практическая работа 3
Тема: Использование функций в расчетах MS EXCEL
Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS EXCEL.
Задание 1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Исходные данные представлены ниже.
Порядок работы
- Запустите редактор электронных таблиц MS Excel (Пуск/Программы/ MS Office/ MS Excel).
- Переименуйте ярлычок Лист1, присвоив ему имя «Динамика цен».
- На листе «Динамика цен» создайте таблицу по образцу.
- Произведите расчет изменения цены в колонке «E» по формуле
Изменение цены = Цена на 01.06.2003/Цена на01.04.2003.
Не забудьте задать процентный формат чисел в колонке «E»
(Главная/ Число/Процентный).
- Рассчитайте средние значения по колонкам, пользуясь мастером функций fx. Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (B14), запустите мастер функций (Формула/Вставить функцию) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические/СРЗНАЧ).
После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения B6: B13 и нажмите кнопку ОК. В ячейке B14 появится среднее значение данных колонки «B».
Аналогично рассчитайте средние значения в других колонках.
- Выполните текущее сохранение файла.
Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.
Исходные данные представлены ниже.
Порядок работы
- На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментов Объединить и поместить в центре или командой меню (Главная/Число /вкладка Выравнивание /отображение – Объединение ячеек).
Краткая справка. Изменение направления текста в ячейках производится путем поворота текста на 90о в зоне Ориентация окна Формат ячеек, вызываемого командой Главная/Число /вкладка Выравнивание/Ориентация – поворот надписи на 90о.
- Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.
- Построить график изменения количества рабочих дней по годам и странам. (Вставка/График). Подписи оси «X» задайте года (В области диаграммы, Выбрать данные/ Подписи горизонтальной оси – выбрать диапазон В3:М3; элементы легенды - выбрать диапазон А4:А7).
- После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100. Для форматирования оси выполните двойной щелчок мыши по ней и на вкладке Параметры оси диалогового окна Формат оси задайте соответствующие параметры оси.
- Выполните текущее сохранение файла «Расчеты».
Задание 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).
Исходные данные представлены ниже.
Порядок работы
- На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.
- Произвести расчет Премии (25% от базовой ставки) по формуле
Премия = Базовая ставка х 0,25 при условии, что
План расходования ГСМ>Фактически израсходовано ГСМ.
Для проверки используйте функцию ЕСЛИ.
Для подсчета Премии установите курсор в ячейке F4, запустите мастер функции (Формула/логические) и выберите функцию ЕСЛИ (категория – Логические/ЕСЛИ).
Задайте условие и параметры функции ЕСЛИ. В первой строке «Логическое выражение»задайте условие C4>D4.
Во второй строке задайте формулу расчета премии, если условие выполняется E4*0,25.
В третьей строке задайте значении 0, поскольку в этом случае (невыполнения условия) премия не начисляется.
3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор в любую ячейку таблицы, выберите в меню Данные команду Сортировка, задайте сортировку по столбцу «Фактически израсходовано ГСМ».
4. Конечный вид расчетной таблицы начисления премии приведен ниже.
- Выполните текущее сохранение файла «Расчеты» (Файл/Сохранить).
Дополнительное задание
Задание 4. Скопировать таблицу котировки курса доллара (лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием «Мастера функций».
Скопируйте содержимое листа «Курс доллара» на новый лист.
Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию EXCEL Формула/ Вставить функцию МАКС (МИН) ИЗ КАТЕГОРИИ «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца B4:B23 (для второго расчета выделите диапазон C4:C23).