Работа 8. Подбор параметра. Организация обратного расчета

Средство MS Excel Подбор параметрапозволяет определить значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.

Цель занятия.Изучение технологии подбора параметра при об­ратных расчетах.

Задание 8.1. Создание штатного расписания фирмы.

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

Краткая справка. Известно, что в штате фирмы состоит:

• 6 курьеров;

• 8 младших менеджеров;

• 10 менеджеров;

• 3 заведующих отделами;

• 1 главный бухгалтер;

• 1 программист;

• 1 системный аналитик;

• 1 генеральный директор фирмы.

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

Каждый оклад является линейной функцией от оклада курьера, а именно:

зарплата = A_i * х+ B_i

где х – оклад курьера; А_i и В_i – коэффициенты, показывающие, во сколько и на сколько превышается величина x.

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

1. Запустите редактор электронных таблиц Microsoft Excel.

2. Создайте таблицу штатного расписания фирмы по приведен­ному образцу (рис.2.19). Введите исходные данные в рабочий лист электронной книги.

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

3. Выделите ячейку D3 для зарплаты курьера (пере­менная «x») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

4. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = В6 * $D$3 + С6, где ячейка D3 задана в виде абсо­лютной адресации. Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.

В столбце F задайте формулу расчета заработной платы всех ра­ботающих в данной должности. Например, для ячейки F6 формула расчета имеет вид: = D6 * Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.

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

5. Произведите подбор зарплат сотрудников фирмы для суммар­ной заработной платы, равной 100000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;

в поле Значение наберите искомый результат 100000;

в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000р. (рис. 2.20).

Рис. 2.20. Результаты расчета окладов сотрудников фирмы

6. Присвойте рабочему листу имя «Штатное расписание 1». Со­храните созданную электронную книгу под именем «Штатное рас­писание» в своей папке.

Анализ задач показывает, что с помощью MS Excel можно ре­шать линейные уравнения. Задания 8.1 и 8.2 показывают, что поиск значения параметра формулы – это не что иное, как чис­ленное решение уравнений. Другими словами, используя возмож­ности программы MS Excel, можно решать любые уравнения с одной переменной.