Тема: использование функций в расчетах MS Excel

Лабораторная работа №3

Цель занятия. Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.

Задание 1. Создать таблицу динамики розничных цен и произвести расчет средних значений.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (Пуск à Программы à Microsoft office à Microsoft Excel).

2. Переименуйте ярлык Лист 1, присвоив ему имя «Динамика цен».

3. На листе «Динамика цен» создайте таблицу по образцу.

4. Произведите расчет изменения цены в колонке «Е» по формуле:

Изменение цены =
Цена на 01. 06.2003/Цена на 01. 04.2003

Не забудьте задать процентный формат чисел в колонке «Е».

5. Рассчитайте средние значения по колонкам, пользуясь мастером функций Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции в строке формул или на вкладке Формулы àгруппа Библиотека функций à кнопка Вставить функцию или вызвать меню к кнопке à категория Статистические à функция СРЗНАЧ).

6. После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК. В ячейке В14 появится среднее значение данных колонки «В».

7. Аналогично рассчитайте средние значения в других колонках.

8. В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (вкладка Формулы à группа Библиотека функций à вызвать меню к кнопке à функция СЕГОДНЯ).

9. Выполните текущее сохранение файла «Лабораторная работа 3».

Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.

Исходные данные:

1. На листе 2 создайте таблицу по заданию. Объединение выделенных ячеек произведите используя кнопку Объединить и поместить в центре на вкладке Главная à группа Выравнивание или вызовите диалоговое окно Формат ячеек à вкладка Выравнивание à установите флажок .

Краткая справка. Изменение направления текста производится путем поворота текста на 90°, вызываемого на вкладке Главная à группа Выравнивание à вызовите диалоговое окно Формат ячеек à вкладка Выравнивание à установите Ориентацию — поворот надписи на 90°.

2. Произведите расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.

3. Постройте график изменения количества рабочих дней по годам и странам. Подписи оси «X» задайте с помощью вкладки Работа с диаграммой à вкладка Конструктор à группа Данные à щелкнуть по кнопке Выбрать данные à в появившемся окне в области Элементы легенды (ряды) выделить пункт Ряд1, а в области Подписи горизонтальной оси выбрать кнопку Изменить. В открывшемся окне Подписи оси указать интервал ячеек B3:M3. Нажать ОК.

4. После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100. Для форматирования оси выполните щелчок правой кнопкой мыши по ней, выбрать в контекстном меню пункт Формат оси и в разделе Параметры оси и задать соответствующие значения.

5. Выполните текущее сохранение файла «Лабораторная работа 3».

Задание 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ). Исходные данные:

1. На очередном свободном листе электронной книги создайте таблицу по заданию.

2. Произвести расчет Премии (25 % от базовой ставки) по формуле

Премия =
Базовая ставка * 0,25 при условии, что

План расходования ГСМ > Фактически израсходовано ГСМ.

Для расчета Премии установите курсор в ячейке F4, запустите мастер функций и выберите функцию ЕСЛИ (категория - Логические à ЕСЛИ).

3. Задайте условие и параметры функции ЕСЛИ.

В первой строке «Логическое выражение» задайте условие С4 > D4.

Во второй строке задайте формулу расчета премии, если условие выполняется Е4*0,25.

В третьей строке задайте значение 0, поскольку в этом случае премия не начисляется.

4. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для этого установите курсор в ячейку D4 и выберите на вкладке Данные à группа Сортировка и фильтр à щелкните по кнопке .

Задание 4. Скопировать таблицу котировки курса доллара (лист «Курс доллара») и произвести под таблицей расчет средних значений, максимального и минимального значений курсов покупки и продажи доллара. Расчет произвести с использованием «Мастера функций».

1. Откройте одновременно файлы MS Excel «Лабораторная работа 1» и «Лабораторная работа 3»

2. Скопируйте содержимое листа «Курс доллара» (Лабораторная работа 1, лист 1) на новый лист. Для этого воспользоваться командой Переместить /Скопировать контекстного меню ярлыка.

3. В разделе Переместить выбранные листы в книгу: выберите «Лабораторная работа 3». Не забудьте для копирования поставить галочку в окошке Создавать копию.

4. Перемещать и копировать листы можно перетаскивая их ярлычки, а для копирования удерживайте нажатой клавишу [Ctrl] (это осуществляется в пределах одной книги).

5. Рассчитайте максимальное, минимальное и среднее значения по колонкам «Курс покупки» и «Курс продажи», пользуясь мастером функций. Функция СРЗНАЧ, МАКС, МИН находятся в разделе «Статистические».

6. Сохраните измененияи распечатайте результаты выполнения лабораторной работы.