Поиск решения в Excel: оптимальный план выпуска с ограничениями и целыми числами

Одна формула прибыли зависит от многих чисел сразу: сколько выпустить каждого товара, сколько вложить в каждый канал, сколько людей поставить в смену. Справка Microsoft описывает надстройку как инструмент, который подбирает значения ячеек переменных так, чтобы выполнялись ограничения, а целевая формула принимала нужное значение^[1]. Поиск решения в Excel перебирает их за вас, а вручную это бессмысленно: их слишком много, и ограничения легко нарушить незаметно.

Путь такой: включить надстройку и найти кнопку на вкладке «Данные», выделить целевую ячейку и ячейки переменных, выбрать метод и запустить расчёт, проверить ответ и ограничения, при ошибке восстановить исходные значения, а затем сохранить модель и применить её к своим данным. Остальные темы листа и анализа собраны в каталоге уроков тренажёра Excel онлайн.

Ключевые факты: поиск решения в Excel

• Надстройка подбирает значения ячеек переменных так, чтобы целевая формула достигла максимума, минимума или заданного числа^[1].
• В одной модели можно задать до 200 ячеек переменных^[1].
• Типы int, bin и dif допустимы только в ограничениях на ячейки переменных^[1].
• Блок «Применяется к» в справке по надстройке начинается с Microsoft 365 и заканчивается Excel 2016, а разработчиком указана компания Frontline Systems^[1].
• Метод LP Simplex в отличие от GRG Nonlinear рассчитан на линейные модели, а Evolutionary — на негладкие^[1].

Что делает поиск решения в Excel и где его включить

Поиск решения в Excel подбирает значения ячеек переменных под максимум, минимум или заданное число в целевой ячейке. Команда стоит на вкладке «Данные» в группе Analysis — в русской справке группа названа «Анализ»^[5]. Если кнопки нет, надстройку сначала включают: по английской справке путь такой^[2]:

1. File → Options → Add-Ins.
2. В поле Manage выберите Excel Add-ins и нажмите Go.
3. Отметьте флажок Solver Add-in и нажмите OK.
4. Если надстройки нет в списке, нажмите Browse; если Excel сообщит, что она не установлена, ответьте Yes.

После этого кнопка появится в группе Analysis на вкладке Data^[2]. Русская страница справки переведена машинно, поэтому названия полей окна ниже даны по английской версии.

Разобравшись с включением, переходим к модели — без неё окну надстройки нечего оптимизировать.

Пошаговый пример: план выпуска шкафов и тумб

Поиск решения находит план выпуска, при котором прибыль максимальна, а ограничения по древесине и часам сборки соблюдены. Мастерская делает шкафы и тумбы; план стоит в B5:C5, итоги считает СУММПРОИЗВ.

1. Выделите D2 и на вкладке «Данные» нажмите кнопку надстройки — откроется окно Solver Parameters.
2. В Set Objective оставьте D2 и выберите Max.
3. В By Changing Variable Cells укажите B5:C5.
4. Кнопкой Add добавьте ограничения D3<=E3, D4<=E4 и B5:C5>=0.
5. Выберите метод LP Simplex: модель линейная.
6. Нажмите Solve и в окне Solver Results выберите Keep Solver Solution.

Ответ модели — 25 шкафов и 15 тумб с прибылью 137 500, и при таком плане расходуется ровно 90 м² древесины и 80 часов^[6]. Перебор вершин в движке тренажёра это подтверждает: план из 30 шкафов даёт 120 000, план из 40 тумб — 100 000, а соседний 26 и 14 выходит за лимит древесины^[6]. Соберите эту модель в уроке тренажёра и проверьте, что ответ держит оба ограничения. Как устроена функция итогов, разобрано на странице о СУММПРОИЗВ.

Однако метод LP Simplex подходит только линейной модели, а у других задач правила иные.

Методы решения: чем отличаются LP Simplex, GRG и Evolutionary

Поиск решения считает модель одним из трёх методов, и выбор зависит от того, как целевая формула связана с переменными^[1]. Метод подбирают под тип модели: каждый из трёх рассчитан на свой класс задач.

Ограничения задают отношениями <=, =, >=, а также int, bin и dif; три последних ставят только на ячейки переменных^[1]. Int делает ответ целым, bin — нулём или единицей, dif — набором разных целых чисел.

Флажок Show Iteration Results в Options показывает каждое пробное решение по шагам^[1]. Последние настройки окна сохраняются вместе с листом, когда вы сохраняете книгу — например, через Ctrl+S^[4].

Остаётся понять, когда даже правильный метод не спасёт модель.

Когда поиск решения не подойдёт

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

• Если ячеек переменных больше 200, окно их не примет: справка ограничивает модель этим числом^[1].
• Если в целевой ячейке число, а не формула, надстройке нечего оптимизировать — цель должна содержать формулу^[5].
• Когда ячейки переменных не связаны с целевой ни прямо, ни через другие ячейки, ответ не изменится^[5].
• При работе на телефоне или планшете надстройка недоступна: по описанию справки её нет на мобильных устройствах^[2].
• Если неизвестное одно и условий нет, проще обратный расчёт одной ячейкой без надстройки.

Для Google Таблиц справка Google описывает дополнение Goal Seek, которое меняет одну ячейку^[3]; оптимизации с ограничениями в этой справке нет. На Mac надстройка тоже работает: в списке редакций справки Microsoft есть Microsoft 365, Excel 2024 и Excel 2021 для Mac^[1]. Если результат не устроил, нажмите Restore Original Values или отмените последнее действие сочетанием Ctrl+Z^[4], поправьте ограничения и запустите поиск решения снова.