1. Запустите редактор электронных таблиц MS EXCEL.
2. Создайте таблицу штатного расписания и введите исходные данные, как показано на рис. 14. Исходными данными в данной задаче будут значения в столбцах: «Должность», «Коэффициент А», «Коэффициент В», «Количество сотрудников».
Выделите отдельную ячейку, в которой будет находиться переменная х, т. е. зарплата программиста, и все дальнейшие расчеты задайте с учетом этого. В эту ячейку введите временно произвольное число.
В столбце «Зарплата сотрудника» (столбец D на рис. 14) введите формулу для расчета заработной платы по формуле, заданной в условии. Например, для ячейки D3 формула будет иметь следующий вид: =$C$15*B3+C3, (где ячейка С15 задана с абсолютной ссылкой, что позволит дальше просто скопировать формулу в остальные ячейки, при помощи функции автозаполнения).
Рисунок 14 - Исходные данные для задачи.
В столбец «Суммарная зарплата» (столбец F, на рис. 14) введите формулу для расчета заработной платы всех работающих на этой должности. Например, для ячейки F3 формула будет выглядеть так: =D3*E3. Далее скопируйте эту формулу вниз по столбцу при помощи функции автозаполнения.
В ячейке F13 рассчитайте суммарный фонд заработной платы компании.
3. Произведите подбор зарплат сотрудников компании для суммарной заработной платы, заданной в условии задачи.
Для этого в меню «Сервис» щелкните строку «Подбор параметра».
Функция «Подбор параметра» позволяет подобрать нужное значение в одной ячейке, изменяя значения в другой ячейке. В данном случае ячейка, в которой нужно подобрать значение – это ячейка F13, в которой содержится формула для расчета общего фонда заработной платы. Изменяемая ячейка – это ячейка С15, в которой содержится значение зарплаты программиста (до начала расчета там находится произвольное число!).
В появившемся окне «Подбор параметра», необходимо заполнить три ячейки.
Установить в ячейке – указываем ссылку на ячейку, в которой будем подбирать значение (в нашем примере это ячейка F13).
Значение – нужно набрать цифрами значение, которое является заданным по условию задачи (в нашем примере это 350 000 руб.).
Изменяя значение ячейки – ссылка на ячейку, значение которой будет меняться (в нашем примере это ячейка $C$15).
После ввода данных нажмите кнопку ОК, после чего произойдет расчет заработной платы сотрудников по заданному условию при фонде заработной платы 350 000 руб., появится окно «Результат подбора параметра», в котором будет сообщение, что решение найдено. Также может появиться это же окно с сообщением, что решение не найдено. В этом случае необходимо отменить операцию и проверить правильность ввода данных.
Полученный результат приведен на рис. 15.
Рисунок 15 – Результат подбора параметров по заданным условиям.
4. Сделайте вывод о проделанной работе и запишите его на вашем листе электронной книги EXCEL.
Вопросы для закрепления теоретического материала
1. Какие типы задач можно решить при помощи надстройки «Подбор параметра»?
2. Какие численные методы реализованы в надстройке Excel «Подбор параметра»?
Задание для практического занятия
Известно, что в штате компании состоят:
- 1 человек - аналитик бизнес-процессов;
- 2 человека функциональных аналитиков;
- 3 менеджера проектов;
- 5 программистов;
- 1 инженер по технической документации;
- 2 юриста;
- 1 бухгалтер;
- 5 начальников отделов;
- 1 технический директор;
- 1 генеральный директор.
Общий месячный фонд заработной платы составляет 350 000 руб. необходимо определить, какими должны быть оклады сотрудников. При этом надо знать, что оклад всех сотрудников является линейной функцией от оклада программиста, т. е. ЗП=Аi*х+Вi, где х – оклад программиста, Аi – коэффициент, показывающий, во сколько раз превышается значение х;
Вi – на сколько превышается значение х.
Дополнительное задание
1. Используя надстройку «Подбор параметра» и таблицу штатного расписания, сделанную Вами на практическом занятии № 8, последовательно определите заработные платы сотрудников фирмы для различных значений фонда заработной платы: 450 000 руб., 500 000 руб., 550 000 руб., 600 000 руб., 650 000 руб., 700 000 руб.
2. Используя надстройку «Подбор параметра» и таблицу штатного расписания, сделанную Вами на практическом занятии № 8, определите заработную плату сотрудников фирмы для ряда заданных значений фонда заработной платы. Для этого из табл. 2 выберите коэффициенты для расчета, согласно заданному варианту. Ограничение по фонду заработной платы остается таким же, что и в исходной задаче.
