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

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

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

Оборудование, приборы, аппаратура, материалы: персональный компьютер, Ms Office (Excel)

Задание 1. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Изменение направления текста в ячейках производится путем поворота текста на 90° в зоне Ориентация окна Формат ячеек, вызываемого командой ПКМ/Формат ячеек /вкладка Выравнивание/ Ориентация – поворот надписи на 90°.

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

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

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

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

На очередном свободном листе электронной книги «Расчеты» создайте таблицу расчета премии за экономию горючесмазочных материалов (ГСМ). Исходные данные представлены на рис. 2.
Произвести расчет Премии (25 % от базовой ставки) по формуле

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

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

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

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

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

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

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

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

Рис. 2. Исходные данные.

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

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

В столбце премия задать денежный формат, 2 знака после запятой, обозначение – рубли.

Задание 3. На новом листе создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации.

Константа (постоянная) – абсолютная адресация задается набором символа $ с клавиатуры или нажатием F4.

Введите исходные данные.

При вводе номеров в колонку «А» (числа 1,2,3….) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда, выделите их мышкой и подведите курсор к правому нижнему углу выделенного диапазона до изменения вида курсора на черный крестик, ЛКМ – протянуть вниз до нужного значения.

Выделите цветом ячейку константы – отпускной цены 57,00 р.

Произведите расчеты по формулам:

Выпуск продукции = Количество выпущенных изделий х Отпускная цена одного изделия (С7=С5*$E$2)

Себестоимость одного изделия = Количество выпущенных изделий х Себестоимость одного изделия (С8=С5*С6)

Прибыль от реализации продукции = Выпуск продукции - Себестоимость выпускаемой продукции (С9=С7-С8)

Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции (С10=С9/С8)

- На строку расчета рентабельности продукции наложите Процентный формат чисел. Остальные расчеты произведите в Денежном формате.

- Формулы из колонки С скопируйте автозаполнением по строке в колонки «D» и «E».

Конечный вид таблицы

Задание 4. На новом листе создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.