Поиск решения в Excel: оптимизация на практическом примере
«Поиск решения» в Excel подбирает значения нескольких ячеек так, чтобы целевая формула достигла максимума, минимума или заданного результата. Ниже — рабочий пример производственного плана, на котором видно, как подготовить модель, задать ограничения и проверить найденный ответ.
Что делает «Поиск решения»
Обычная формула отвечает на вопрос «что получится при таких исходных данных». Надстройка «Поиск решения», или Solver, работает в обратную сторону: вы задаёте желаемый результат и разрешаете Excel менять выбранные ячейки в допустимых пределах. Инструмент полезен, когда вариантов много и перебор вручную ненадёжен.
В модели есть четыре обязательных элемента:
- целевая ячейка — формула, которую нужно максимизировать, минимизировать или привести к значению;
- изменяемые ячейки — неизвестные, которые Solver вправе подбирать;
- ограничения — условия по ресурсам, бюджету, срокам, минимальным объёмам и целым числам;
- метод решения — алгоритм, соответствующий виду формул.
По справке Microsoft, надстройка меняет значения переменных ячеек, соблюдая ограничения, и может работать с GRG Nonlinear, Simplex LP и Evolutionary. В Excel для Интернета Solver не поддерживается: модель нужно открыть в настольной версии. Актуальное описание возможностей есть в официальной инструкции Microsoft.
Как включить надстройку
- Откройте Файл → Параметры → Надстройки.
- Внизу окна выберите Надстройки Excel и нажмите Перейти.
- Отметьте Поиск решения и подтвердите подключение.
- Проверьте вкладку Данные: справа должна появиться команда Поиск решения.
Если команда не появилась, перезапустите Excel и убедитесь, что надстройки не запрещены политикой организации. Названия пунктов могут немного отличаться между выпусками Office и операционными системами.
Практический пример: оптимальный план выпуска
Компания производит два продукта. Продукт A приносит 900 рублей маржинального дохода на единицу и требует 2 часа оборудования и 1 час специалиста. Продукт B приносит 1 200 рублей, требует 1 час оборудования и 2 часа специалиста. На неделю доступны 100 часов оборудования и 80 часов специалиста. Нужно выбрать объёмы A и B, чтобы максимизировать общий результат.
| Показатель | Продукт A | Продукт B | Доступно |
|---|---|---|---|
| Количество, шт. | B2 — переменная | C2 — переменная | — |
| Доход на единицу, руб. | 900 | 1 200 | — |
| Оборудование, ч | 2 | 1 | 100 |
| Работа специалиста, ч | 1 | 2 | 80 |
В B2 и C2 поставьте нули: это изменяемые значения. В D3 рассчитайте общий маржинальный доход формулой =B2*B3+C2*C3. В D4 вычислите расход оборудования =B2*B4+C2*C4, в D5 — трудозатраты =B2*B5+C2*C5. Такая структура важнее оформления: Solver должен видеть формулы, связывающие результат с переменными.
Если вы ещё неуверенно работаете с ссылками и арифметикой, сначала повторите базовые формулы в Excel. Для более крупной модели полезно отдельно продумать допущения и сценарии — это разобрано в статье о финансовой модели бизнеса.
Как заполнить окно Solver
- Оптимизировать целевую функцию: укажите D3.
- До: выберите «Максимум».
- Изменяя ячейки переменных: укажите диапазон B2:C2.
- Добавьте ограничения: D4<=100, D5<=80, B2:C2>=0.
- Если выпуск возможен только целыми единицами, добавьте для B2:C2 ограничение целое.
- Метод: для этой линейной модели выберите Simplex LP.
- Нажмите Найти решение, затем сохраните найденные значения.
Результат примера: Solver находит 40 единиц A и 20 единиц B. Тогда D3 = 60 000 ₽, D4 = 100 часов оборудования, D5 = 80 часов работы специалиста. Оба ресурса использованы полностью; ограничение на целые значения ответ не меняет.
Без требования целых чисел модель даст математический оптимум, который может содержать дробные единицы. Это нормально для часов, килограммов или долей бюджета, но неверно для людей, станков и неделимых товаров. Ограничения должны отражать реальный смысл показателей, а не только помогать получить красивое число.
Как проверить найденное решение
Не принимайте результат только потому, что Excel показал сообщение об успехе. Проведите четыре проверки:
- подставленные значения действительно дают целевую сумму по исходной формуле;
- ни одно ограничение не превышено и не забыто;
- единицы измерения согласованы: часы с часами, рубли с рублями, недели с неделями;
- неотрицательность и целочисленность заданы там, где это требуется бизнес-смыслом.
Для линейной задачи полезно запросить отчёт по результатам или устойчивости и посмотреть, какие ресурсы использованы полностью. Если небольшое изменение исходных данных резко меняет план, решение чувствительно: его стоит перепроверить на нескольких сценариях.
Как выбрать метод решения
| Метод | Когда подходит | Пример |
|---|---|---|
| Simplex LP | Целевая функция и ограничения линейны | План выпуска, распределение бюджета по фиксированным коэффициентам |
| GRG Nonlinear | Есть гладкие нелинейные зависимости | Оптимизация цены при нелинейной функции спроса |
| Evolutionary | Формулы разрывны, используют условия или имеют много локальных решений | Комбинационные модели и сложные правила выбора |
Если линейная задача не решается Simplex LP, сначала ищите ошибку в формулах и ограничениях, а не переключайте алгоритмы наугад. Неверно выбранная модель остаётся неверной при любом методе.
Освойте оптимизацию вместе с формулами, отчётами и анализом данных
Курс «Excel + Google Таблицы с нуля до PRO» помогает выстроить системный навык: от уверенной работы с формулами и подготовкой данных до сводных таблиц, отчётов и практических моделей.
Почему «Поиск решения» не работает
- Целевая ячейка содержит число, а не формулу. Она должна зависеть от изменяемых ячеек.
- Переменные нигде не участвуют. Проверьте адреса в формулах.
- Ограничения противоречат друг другу. Например, минимальный выпуск требует больше ресурсов, чем доступно.
- Забыты единицы и знаки. Ограничение «не менее» случайно задано как «не более».
- Нужны целые значения, но ограничение не установлено.
- Выбран неподходящий метод. Для линейной модели начните с Simplex LP.
Хорошая оптимизационная таблица остаётся понятной без окна Solver: исходные данные отделены от переменных, формулы подписаны, ограничения видны рядом с фактическим расходом. Тогда модель можно проверить и объяснить коллеге.
Вопросы и ответы
- Где находится «Поиск решения» в Excel?
После подключения надстройки команда появляется на вкладке «Данные». Если её нет, откройте параметры Excel, раздел надстроек, выберите надстройки Excel и отметьте «Поиск решения».
- Чем Solver отличается от подбора параметра?
Подбор параметра меняет одну ячейку ради заданного результата. Solver может менять несколько переменных, оптимизировать максимум или минимум и одновременно соблюдать набор ограничений.
- Можно ли использовать «Поиск решения» в Excel Online?
Официальная справка Microsoft указывает, что надстройка не поддерживается в Excel для Интернета. Для настройки и запуска задачи потребуется настольная версия Excel.