ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В РАСЧЕТАХ MS EXCEL
Задание 1. Создать таблицу динамики розничных цен и произвести расчет средних значений.
Исходные данные представлены в таблице.
1.Откройте файл Расчеты.
| A | B | C | D | E | |||||
| Динамика розничных цен на молоко цельное разливное, руб./ литр | |||||||||
| Регионы Российской Федерации | на 01.04.2006 год | на 01.05.2006 год | на 01.06.2006 год | изменение цены в % (01.06.2006 к 01.04.2006) | |||||
| Поволжский р-н | |||||||||
| Республика Калмыкия | 7,36 | 7,36 | 6,29 | ? | |||||
| Республика Татарстан | 3,05 | 3,05 | 3,05 | ? | |||||
| Астраханская обл. | 8,00 | 7,85 | 7,75 | ? | |||||
| Волгоградская обл. | 12,08 | 12,12 | 11,29 | ? | |||||
| Пензенская обл. | 8,68 | 8,75 | 9,06 | ? | |||||
| Самарская обл. | 7,96 | 7,96 | 7,96 | ? | |||||
| Саратовская обл. | 11,40 | 11,10 | 11,08 | ? | |||||
| Ульяновская обл. | 5,26 | 5,26 | 5,26 | ? | |||||
| Среднее значение по району | ? | ? | ? | ||||||
2.Переименуйте ярлык Лист 5, присвоив ему имя Динамика цен.
3.На листе Динамика цен создайте таблицу по образцу.
4.Произведите расчет изменения цены в колонке Е по формуле:
Изменение цены = Цена на 01.06.2006 / Цена на 01.04.2006
Задать процентный формат чисел в колонке Е (Формат - Ячейки - Число - Процентный).
5.Рассчитайте средние значения по колонкам, пользуясь мастером функций fx. Функция СРЗНАЧ находится в разделе Статистические. Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции fx или командой Вставка - Функция) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические - СРЗНАЧ)
После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В16 и нажмите кнопку ОК. В ячейке В14 появится среднее значение данных колонки В. Аналогично рассчитайте средние значения в других колонках.
6.В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Вставка – Функция - Дата и Время - Сегодня).
7.Выполните текущее сохранение файла (Файл - Сохранить).
Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.
Исходные данные представлены в таблице.
1.На очередном свободном листе электронной книги Расчеты создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментов Объединить и поместить в центреили командой меню (Формат - Ячейки - вкладка Выравнивание - Отображение – Объединение ячеек).
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||||
| Кто сколько работает Среднее количество рабочих дней в год на работника наемного труда | Среднее по стране за период | |||||||||||||||||
| Годы | ||||||||||||||||||
| Великобритания | ? | |||||||||||||||||
| Германия | ? | |||||||||||||||||
| США | ? | |||||||||||||||||
| Япония | 201 0 | ? | ||||||||||||||||
| Среднее за год | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | |||||
Изменение направления текста в ячейках производится путем поворота текста на 900 в зоне Ориентацияокна Формат ячеек, вызываемого командой Формат - Ячейки - вкладка Выравнивание - Ориентация – поворот надписи на 900
2.Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.
3.Построить график изменения количества рабочих дней по годам и странам. Подписи оси Х задайте при построении графика на втором экране мастера диаграмм (вкладка Ряд, область Подписи оси Х).
4.После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления100. Для форматирования оси выполните двойной щелчок мыши по ней и по вкладке Шкала диалогового окна Формат осизадайте соответствующие параметры оси.
5.Выполните текущее сохранение файла Расчеты (Файл - Сохранить).
Задание 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).
Исходные данные представлены в таблице.
1.На свободном листе электронной книги Расчеты создайте таблицу по заданию.
2.Произвести расчет Премии (25% от базовой ставки) по формуле:
Премия = Базовая ставка * 0,25 при условии, что
План расходования ГСМ > Фактически израсходованного ГСМ.
| A | B | C | D | E | F | ||||
| Расчет премии за экономию горючесмазочных материалов (ГСМ) | |||||||||
| Тбельный № | Ф.И.О. | План расходования ГСМ (литров) | Фактически израсходовано ГСМ (литров) | Базовая ставка (руб.) | Премия (25% от базовой ставки, если План>Факти- ческого израсходов | ||||
| Сергеева А.В. | 2000,00 | ? | |||||||
| Петров С.П. | 2000,00 | ? | |||||||
| Сидоров А.О. | 2000,00 | ? | |||||||
| Кремнев В.В. | 1000,00 | ? | |||||||
| Андреев П.Р. | 1000,00 | ? | |||||||
| Васильев П.Л. | 2000,00 | ? | |||||||
| Гордеев А.В. | 2000,00 | ? | |||||||
| Серов В.В. | 2000,00 | ? | |||||||
| Рогов Р.Р. | 1000,00 | ? | |||||||
| Марков А.П. | 2000,00 | ? | |||||||
| Диев Д.Ж. | 2000,00 | ? | |||||||
| Жданов П.О. | 2000,00 | ? | |||||||
Для проверки условия используйте функцию ЕСЛИ.
Для расчета Премии установите курсор в ячейке F14, запустите мастер функций (кнопкой Вставка функции fx или командой Вставка - Функция) и выберите функцию ЕСЛИ (категория – Логические / ЕСЛИ).
Задайте условие и параметры функции ЕСЛИ. В первой строке Логическое выражение задайте условие C4 > D4.
Во второй строке задайте формулу расчета премии, если условие выполняется E4*0,25.
В третьей строке задайте значение 0, поскольку в этом случае (невыполнение условии я) премия не начисляется.
3.Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду Сортировка, задайте сортировку по столбцу Фактически израсходовано ГСМ.
4.Конечный вид расчетной таблицы начисления премии приведен ниже.
| A | B | C | D | E | F | |||
| Расчет премии за экономию горючесмазочных материалов (ГСМ) | ||||||||
| Тбельный № | Ф.И.О. | План расходования ГСМ (литров) | Фактически израсходовано ГСМ (литр.) | Базовая ставка (руб.) | Премия (25% от базовой ставки, если План>Факти- ческого израсходов | |||
| Сергеева А.В. | 2000,00 | 500,00р. | ||||||
| Петров С.П. | 2000,00 | |||||||
| Сидоров А.О. | 2000,00 | |||||||
| Кремнев В.В. | 1000,00 | 250,00р. | ||||||
| Андреев П.Р. | 1000,00 | |||||||
| Васильев П.Л. | 2000,00 | |||||||
| Гордеев А.В. | 2000,00 | |||||||
| Серов В.В. | 2000,00 | |||||||
| Рогов Р.Р. | 1000,00 | 250,00р. | ||||||
| Марков А.П. | 2000,00 | 500,00р. | ||||||
| Диев Д.Ж. | 2000,00 | 500,00р. | ||||||
| Жданов П.О. | 2000,00 | 500,00р. | ||||||
5.Выполните текущее сохранение файла Расчеты (Файл - Сохранить).