Оптимизация штатного расписания в Excel с помощью надстройки Solver

Надстройка Solver в Excel — это мощный инструмент для решения задач оптимизации, таких как оптимизация штатного расписания и минимизация затрат. В этой статье мы разберем, как использовать Solver для создания эффективного плана работы сотрудников.

Для использования Solver ваша рабочая книга должна содержать три ключевых элемента: целевую ячейку (Goal), изменяемые ячейки (Input) и ограничения (Constraints).

Рассмотрим задачу: необходимо составить план работы для парка развлечений. Каждый сотрудник работает 5 дней, отдыхает 2 дня. Существует 7 возможных графиков сменности.

1. Создайте модель в Excel:
   • В ячейках B4:B10 укажите количество сотрудников для каждого графика (изменяемые ячейки).
   • Целевая ячейка B17 — общий фонд оплаты труда в неделю (общее количество сотрудников × $68 в день).
   • В ячейках D14:J14 задайте необходимое количество сотрудников на каждый день недели.
   • С помощью функции СУММПРОИЗВ (SUMPRODUCT) в оранжевых ячейках рассчитайте фактическое количество запланированных сотрудников по дням.

2. Настройте параметры Solver:
   • На вкладке «Данные» нажмите значок Solver.
   • Установите целевую ячейку B17 на минимум (МИН (Min)).
   • Укажите изменяемые ячейки: B4:B10.
   • Добавьте ограничения:
     • D12:J12 >= D14:J14 (фактическое количество >= необходимого).
     • B4:B10 >= 0 (неотрицательные значения).
     • B4:B10 — целые числа (Integer).
   • Выберите метод решения «Симплекс LP» (Simplex LP) и нажмите «Решить» (Solve).

Solver найдет оптимальное решение, сократив штат с 38 до 30 сотрудников и сэкономив $544 в неделю. Однако это решение может быть математически оптимальным, но не идеальным с практической точки зрения (например, избыток персонала в среду и четверг).

На основе решения Solver вы можете вручную скорректировать графики, чтобы равномернее распределить нагрузку и создать резерв на случай отсутствий, сохранив при этом тот же уровень затрат.

Solver предоставляет отличную стартовую точку для оптимизации. Используя его результаты и логику, вы можете найти решение, которое минимизирует затраты и улучшает нематериальные аспекты, такие как гибкость графика.