Время проведения – 2 часа

Цель занятия: Изучение технологии подбора параметра при обратных расчетах, возможностей MS Excel для анализа данных

Вопросы для подготовки к работе:

1. Алгоритм работы с функцией подбора параметра.

2. Определение типа данных в ячейках.

3. Понятие абсолютной и относительной адресации.

4. Редактирование и форматирование данных.

Литература: Теоретическая часть методических указаний,   [1.стр. с.262-271]

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

Задание 1. Используя режим подбора параметра, определить штатное расписание фирмы.

Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы. Каждый оклад является линейной функцией от оклада курьера, а именно:

Зарплата=А_i*х+B_i, где х-оклад курьера; А_i, B_i – коэффициенты, показывающие:

А_i –во сколько раз превышается значениех;

B_{i – на сколько} превышается значение х.

1. Создайте таблицу штатного расписания по приведенному образцу в табл.10. введите исходные данные в рабочий лист электронной книги.

Таблица 10

2. Выделите отдельную ячейку D3 для зарплаты курьера (переменная х)и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

3. В столбце D введите формулу для расчета заработной платы по каждой должности. Например для ячейки D6 формула примет вид: =B6*$D$3+C6(ячейка D3 задана в виде абсолютной ссылки). Скопируйте формулу из ячейки D6 вниз по столбцу автозаполнением.

4. В столбце F задайте формулу расчета заработной платы всех работающих в данной должности, например для ячейки F6 формула имеет вид = D6*E6. Скопируйте формулу на весь диапазон.

5. В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.

6. Произведите подбор параметра зарплат сотрудников фирмы для суммарной заработной платы, равной 100000р. Для этого на вкладке Данные/Работа с данными/Анализ, что, если активизируйте команду Подбор параметра (рис.40)

В поле Установить в ячейке введите ссылку на ячейку F14, содержащую формулу расчета заработной платы, в поле Значение наберите искомый результат 100000, в поле Изменяя значение введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера.

Рисунок 40

После ввода команды произойдет расчет заработной платы сотрудником при заданном условии.