Оптимизация штатного расписания в Excel с помощью надстройки Solver
Надстройка Solver в Excel — это мощный инструмент для решения задач оптимизации, таких как оптимизация штатного расписания и минимизация затрат. В этой статье мы разберем, как использовать Solver для создания эффективного плана работы сотрудников.
Используется встроенная надстройка «Поиск решения» (Solver) настольного Excel для Windows или Mac. В Windows откройте «Файл → Параметры → Надстройки», выберите управление «Надстройки Excel → Перейти» и включите «Поиск решения». На Mac — «Сервис → Надстройки Excel». В веб-версии этот путь не применяется; отдельные надстройки имеют свои условия.
Для использования Solver ваша рабочая книга должна содержать три ключевых элемента: целевую ячейку (Goal), изменяемые ячейки (Input) и ограничения (Constraints).
Рассмотрим задачу: необходимо составить план работы для парка развлечений. Каждый сотрудник работает 5 дней, отдыхает 2 дня. Существует 7 возможных графиков сменности.
- Создайте модель в Excel:
- В ячейках
B4:B10укажите количество сотрудников для каждого графика (изменяемые ячейки). - Целевая ячейка
B17— общий фонд оплаты труда в неделю:=СУММ(B4:B10)*5*68/=SUM(B4:B10)*5*68. Каждый сотрудник работает пять дней по 68 денежных единиц в день; другие выплаты в учебной модели не учитываются. - В ячейках
D14:J14задайте необходимое количество сотрудников на каждый день недели. - В D4:J10 постройте матрицу 0/1: каждая строка — начало пяти последовательных рабочих дней, затем два выходных с переходом через конец недели. В D12 введите
=СУММПРОИЗВ($B$4:$B$10;D4:D10)/=SUMPRODUCT($B$4:$B$10,D4:D10)и скопируйте вправо до J12.
- В ячейках
- Настройте параметры Solver:
- На вкладке «Данные» нажмите значок Solver.
- Установите целевую ячейку
B17на минимум (МИН / MIN). - Укажите изменяемые ячейки:
B4:B10. - Добавьте ограничения:
D12:J12 >= D14:J14(фактическое количество >= необходимого).B4:B10 >= 0(неотрицательные значения).B4:B10— целые числа (Integer).
- Выберите метод решения «Симплекс LP» (Simplex LP) и нажмите «Решить» (Solve).
Solver ищет минимальную стоимость при заданных потребностях и ограничениях. Утверждать, что оптимум всегда равен 30 сотрудникам, нельзя без конкретных значений D14:J14. Если допустимое решение сокращает штат с 38 до 30, при пяти рабочих днях по 68 экономия составит 2720 в неделю, то есть примерно 21,05%: (38−30)×5×68. Число 544 соответствует экономии за один оплачиваемый день, а не за неделю.
После решения проверьте статус Solver, целочисленность и покрытие всех дней. Ручная корректировка сохраняет стоимость только при прежней сумме сотрудников и одинаковой оплате; после неё заново проверьте все ограничения. Равномерность нагрузки и резерв нужно формализовать отдельными ограничениями, если они обязательны.
Solver предоставляет отличную стартовую точку для оптимизации. Используя его результаты и логику, вы можете найти решение, которое минимизирует затраты и улучшает нематериальные аспекты, такие как гибкость графика.