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