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

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

 

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

Исходные данные представлены на рис. 1

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

1. Запустите редактор электронных таблиц Microsoft Excel (при стандартной установке MS Office выполните Пуск/Про­граммы/Microsoft Excel)

2. Откройте файл «Расчеты», созданный в Практических ра­ботах 1 и 2 (Файл/Открыть).

Рис. 1 Исходные данные для задания

 

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

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

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

Изменение цены = Цена на 01.06.2003/Цена на 01.04.2003

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

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

Для расчета функции среднего значения уста­новите курсор в соответствующей ячейке для расчета среднего значения (В14), запустите мастер функций (кнопкой Вставка функции f_x или командой Вставка /Функция) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические/СРЗНАЧ) (рис. 2)

Рис. 2 Выбор функции расчета среднего значения СРЗНА Ч

 

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

Рис. 3. Выбор диапазона данных для расчета функции среднего значения

 

В ячейке В14 появится среднее значение данных колонки «В»

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

 

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

8. Выполните текущее сохранение файла (Файл/Сохранить)

 

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

Исходные данные представлены на рис. 4.

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

1. На очередном свободном листе электронной книги «Расче­ты» создайте таблицу по заданию Объединение выделенных яче­ек произведите кнопкой панели инструментов Объединить и по­местить в центре или командой меню (Формат/ Ячейки/вкладка Выравнивание/отображение — Объединение ячеек)

Рис. 4. Исходные данные для задания

рис. 5 График

Краткая справка Изменение направления текста в ячейках производится путем поворота текста на 90° в зоне Ори­ентация окна Формат ячеек, вызываемого командой Формат/ Ячейки/вкладка Выравнивание/Ориентация — поворот надписи на 90° (рис. 6)

 

Рис. 6. Поворот надписи на 90º

 

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

3. Построить график изменения количества рабочих дней по годам и странам. Подписи оси «X» задайте при построении гра­фика на втором экране мастера диаграмм (вкладка Ряд, область Подписи оси «X»).

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

Рис. 7. Задание параметра шкалы оси графика

 

5. Выполните текущее сохранение файла «Расчеты» (Фат/Со­хранить).

 

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

Исходные данные представлены на рис. 8.

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

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

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

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

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

 

Рис. 8 Исходные данные для задания

 

Для проверки условия используйте функцию ЕСЛИ.

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

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

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

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

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

3. Произведите сортировку по столбцу фактического расходо­вания ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данные команду Сортировка, задайте сортировку по столбцу «Фактически израс­ходовано ГСМ» (рис. 10)

Рис. 9 Задание параметров функции ЕСЛИ

Рис. 10 Задание параметров сортировки данных

Рис. 11 Конечный вид задания

 

4. Конечный вид расчетной таблицы начисления премии приведен на рис. 11

5. Выполните текущее сохранение файла «Расчеты» (Файл/Со­хранить)

 

Задания для самостоятельной работы

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

Скопируйте содержимое листа «Курс доллара» на новый лист (Правка/Переместить/Скопировать лист) Можно восполь­зоваться командой Переместить/Скопировать контекстного меню ярлычка Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 12)

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

Краткая справка. Для выделения максимального/мини­мального значений установите курсор в ячейке расчета, выбе­рите встроенную функцию Excel МАКС (МИН) из категории

Рис. 12 Копирование листа электронной книги

«Статистические», в качестве первого числа выделите диапазон ячеек значений столбца В4:В23 (для второго расчета выделите диапазон С4:С23)

Содержание отчета

1. Номер практического занятия, тема.
2. Цель занятия.
3. Краткие теоретические сведения.
4. Ответы на контрольные вопросы.

Контрольные вопросы.

1. Как заполнить строку или столбец повторяющимися значениями?

2. Как выполнить автозаполнение арифметической прогрессией?

3. Как автоматически заполнить несколько ячеек подряд в строке или столбце последовательными датами?

Практическое занятие №10