Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Советы

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

forest_paw 28.02.2026 364

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

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

Используется встроенная надстройка «Поиск решения» (Solver) настольного Excel для Windows или Mac. В Windows откройте «Файл → Параметры → Надстройки», выберите управление «Надстройки Excel → Перейти» и включите «Поиск решения». На Mac — «Сервис → Надстройки Excel». В веб-версии этот путь не применяется; отдельные надстройки имеют свои условия.

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

Включение надстройки Solver; используйте путь к надстройкам для своей платформы и языка интерфейса.
В Windows включите «Поиск решения» через «Файл → Параметры → Надстройки → Надстройки Excel → Перейти». Старое сочетание Alt+T, I относится к английскому интерфейсу.

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

  1. Создайте модель в 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.
Целевая ячейка — общий фонд оплаты труда в неделю в B17. Изменяемые ячейки — количество сотрудников по графикам в B4:B10. Ограничения — необходимое количество сотрудников в D14:J14.
Целевая ячейка — общий фонд оплаты труда в неделю в B17. Изменяемые ячейки — количество сотрудников по графикам в B4:B10. Ограничения — необходимое количество сотрудников в D14:J14.
  1. Настройте параметры Solver:
    • На вкладке «Данные» нажмите значок Solver.
    • Установите целевую ячейку B17 на минимум (МИН / MIN).
    • Укажите изменяемые ячейки: B4:B10.
    • Добавьте ограничения:
      • D12:J12 >= D14:J14 (фактическое количество >= необходимого).
      • B4:B10 >= 0 (неотрицательные значения).
      • B4:B10 — целые числа (Integer).
    • Выберите метод решения «Симплекс LP» (Simplex LP) и нажмите «Решить» (Solve).
В диалоговом окне Параметры Solver укажите B17 как целевую ячейку и выберите Min. Укажите изменяемые ячейки как B4:B10. Есть три ограничения: D12:J12 >= D14:J14. B4:B10 должны быть целыми числами. B4:B10 должны быть неотрицательными.
В диалоговом окне Параметры Solver укажите B17 как целевую ячейку и выберите МИН / MIN. Укажите изменяемые ячейки как B4:B10. Есть три ограничения.

Solver ищет минимальную стоимость при заданных потребностях и ограничениях. Утверждать, что оптимум всегда равен 30 сотрудникам, нельзя без конкретных значений D14:J14. Если допустимое решение сокращает штат с 38 до 30, при пяти рабочих днях по 68 экономия составит 2720 в неделю, то есть примерно 21,05%: (38−30)×5×68. Число 544 соответствует экономии за один оплачиваемый день, а не за неделю.

Иллюстрация сокращения штата. При изменении 38 → 30 одинаково оплачиваемых сотрудников снижение фонда составляет около 21,05%; достижимость зависит от потребностей по дням.
Иллюстрация сокращения штата. При изменении 38 → 30 одинаково оплачиваемых сотрудников снижение фонда составляет около 21,05%; достижимость зависит от потребностей по дням.

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

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

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

Поделиться:

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