Практическое руководство по применению надстройки «Поиск решения» в Excel для задач оптимизации

Что такое задача оптимизации и почему Excel станет вашим главным помощником

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

Многие думают, что для этого нужны сложные и дорогие программы, но мощнейший инструмент уже есть у вас под рукой. Это Microsoft Excel. Внутри него скрыта специальная надстройка «Поиск решения» (Solver), созданная именно для таких задач. Она позволяет находить оптимальные значения, просто описав ей цель (например, максимизировать прибыль) и условия (например, нехватка ресурсов). Для студента, которому нужно сделать контрольную, это идеальный помощник — доступный, наглядный и эффективный.

Теперь, когда мы понимаем, что мы хотим сделать и с помощью какого инструмента, давайте убедимся, что наш Excel готов к работе.

Теоретический минимум, который нужно знать перед началом работы

Прежде чем перейти к практике, важно понять три «кита», на которых строится любая задача оптимизации. Это основа, которую необходимо описать в любой контрольной работе для демонстрации понимания темы.

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

1. Целевая функция. Это главный показатель, который мы стремимся улучшить. Чаще всего это либо максимизация прибыли, либо минимизация затрат. В Excel это одна ячейка с формулой, результат которой мы хотим сделать оптимальным.
2. Переменные решения. Это те величины, которыми мы можем управлять для достижения цели. Например, сколько единиц товара А и товара Б нужно произвести. В Excel это ячейки, значения в которых «Поиск решения» будет подбирать.
3. Ограничения. Это правила и лимиты, которые нельзя нарушать. Это могут быть ограниченные ресурсы (сырье, время работы оборудования, рабочая сила) или определенные требования (выпустить не менее 10 единиц товара).

Внутри «Поиска решения» есть несколько математических методов. Для задач линейного программирования, с которыми вы, скорее всего, столкнетесь, стандартным и самым надежным выбором является «Симплекс ЛП».

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

Как активировать надстройку «Поиск решения» в вашей версии Excel

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

• Откройте Excel, перейдите в меню «Файл» в левом верхнем углу.
• В появившемся меню слева выберите «Параметры» (в старых версиях может называться «Параметры Excel»).
• В открывшемся окне найдите раздел «Надстройки».
• В нижней части окна, в поле «Управление», убедитесь, что выбрано «Надстройки Excel», и нажмите кнопку «Перейти…».
• Поставьте галочку напротив пункта «Поиск решения» и нажмите «ОК».

После этого на вкладке ленты «Данные» у вас появится новая группа «Анализ», а в ней — заветная кнопка «Поиск решения». Теперь ваш Excel полностью готов к решению задач оптимизации.

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

Постановка задачи на практическом примере крахмалопаточного комбината

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

Крахмалопаточный комбинат производит два вида продукции: глюкозу и патоку. Процесс изготовления включает три операции: расщепление сырья, выжимку и варение. Фонд времени работы оборудования для каждой операции ограничен и составляет 1600, 1200 и 1400 часов в месяц соответственно. Требуется определить оптимальный план выпуска продукции, который обеспечит комбинату максимальную прибыль.

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

Исходные данные для производственной задачи

Параметр	Глюкоза (на 1 ед.)	Патока (на 1 ед.)	Лимит ресурсов (часы)
Время на расщепление	2	4	1600
Время на выжимку	3	1	1200
Время на варение	2	2	1400
Прибыль	100	120	—

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

• Цель: Максимизировать общую прибыль от продажи глюкозы и патоки.
• Переменные: Количество единиц глюкозы и количество единиц патоки, которые нужно произвести.
• Ограничения: Общее время, затраченное на расщепление, выжимку и варение, не должно превышать доступный фонд времени для каждой операции.

Мы разложили хаос текстового условия в упорядоченную систему. Теперь перенесем эту логику в среду Excel.

Создаем математическую модель, или как перевести слова на язык формул Excel

Это самый ответственный этап. Мы должны создать на листе Excel «живую» модель, где все элементы связаны между собой формулами. Рекомендуется придерживаться четкой структуры, чтобы не запутаться.

1. Блок переменных (План выпуска)
Выделим две ячейки, куда «Поиск решения» запишет свой ответ. Например, B4 для количества глюкозы и C4 для количества патоки. Пока можно оставить их пустыми или поставить туда нули.

2. Блок ограничений (Расход ресурсов)
Здесь мы рассчитаем, сколько времени будет потрачено на каждую операцию в зависимости от плана выпуска. Для этого идеально подходит функция СУММПРОИЗВ.

• Расход времени на расщепление (ячейка D8): Создайте формулу =СУММПРОИЗВ(B6:C6; $B$4:$C$4), где B6:C6 — это нормы времени на расщепление (2 и 4), а $B$4:$C$4 — это наш план выпуска (ячейки с переменными). Знаки доллара нужны, чтобы зафиксировать ссылку на ячейки плана.
• Аналогично создайте формулы для выжимки (D9) и варения (D10), используя соответствующие нормы времени.
• Рядом с этими расчетами (например, в столбце F) укажите лимиты времени из условия (1600, 1200, 1400). Так вы сможете наглядно сравнивать расход и лимит.

3. Целевая функция (Общая прибыль)
Наконец, создадим формулу для расчета итоговой прибыли. Она также будет зависеть от плана выпуска. В ячейке, скажем, B12, напишите формулу: =СУММПРОИЗВ(B2:C2; $B$4:$C$4), где B2:C2 — это прибыль на единицу продукции (100 и 120).

Важный совет: Перед запуском Solver проверьте модель вручную. Поставьте в ячейки плана (B4 и C4) по единице и посмотрите, как изменились ячейки с расходом ресурсов и общей прибылью. Если цифры меняются логично, значит, формулы работают правильно.

Наша модель готова. Все ячейки связаны формулами. Осталось передать эту модель «умному калькулятору» — надстройке «Поиск решения».

Пошаговая настройка параметров в диалоговом окне «Поиск решения»

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

1. Указать целевую ячейку. В это поле нужно ввести ссылку на ячейку с нашей целевой функцией. В нашем примере это $B$12, где рассчитывается общая прибыль.
2. Установить цель. Так как мы хотим получить максимальную прибыль, устанавливаем переключатель в положение «Максимум».
3. Указать изменяемые ячейки. Здесь мы указываем на ячейки, куда Solver должен подобрать значения. Это наш план выпуска — диапазон $B$4:$C$4.
4. Добавить ограничения. Это самый важный блок. Нажимаем кнопку «Добавить» и вносим наши правила одно за другим:
   • Ссылка на ячейку D8 (расход времени на расщепление) <= Ссылка на ячейку с лимитом 1600.
   • Ссылка на ячейку D9 (расход времени на выжимку) <= Ссылка на ячейку с лимитом 1200.
   • Ссылка на ячейку D10 (расход времени на варение) <= Ссылка на ячейку с лимитом 1400.
   • Не забудьте добавить условие неотрицательности: диапазон переменных $B$4:$C$4 >= 0, чтобы исключить отрицательный объем производства.
5. Выбрать метод решения. Убедитесь, что в выпадающем списке выбран «Симплекс ЛП», так как наша задача является задачей линейного программирования.

Все параметры заданы. Мы на пороге получения ответа. Нажимаем кнопку «Найти решение» и смотрим, что нам удалось вычислить.

Анализ полученных результатов, или что означают найденные цифры

Если все сделано правильно, Excel выдаст сообщение: «Решение найдено. Все ограничения и условия оптимальности выполнены». Это значит, что задача решена успешно. Теперь самое главное в контрольной работе — правильно интерпретировать результат.

Что мы видим на нашем листе Excel?

• Оптимальный план выпуска: В ячейках переменных (B4 и C4) появились конкретные числа. Это и есть ответ на главный вопрос задачи — сколько глюкозы и сколько патоки нужно производить, чтобы прибыль была максимальной.
• Максимальная прибыль: В целевой ячейке (B12) теперь отображается итоговое значение. Это максимально возможная прибыль, которую можно получить при заданных ограничениях.
• Использование ресурсов: Взгляните на блок ограничений. Теперь вы видите, сколько часов работы каждого станка будет использовано при оптимальном плане. Если расход равен лимиту, значит, этот ресурс используется полностью и является «узким местом» или дефицитным. Если расход меньше лимита — ресурс остается в избытке.

Для контрольной работы недостаточно просто написать: «Ответ: произвести 300 ед. глюкозы и 250 ед. патоки». Необходимо дать развернутое описание: «Оптимальный производственный план, обеспечивающий максимальную прибыль в размере N, предполагает выпуск 300 единиц глюкозы и 250 единиц патоки. При этом ресурсы ‘расщепление’ и ‘варение’ будут полностью исчерпаны, а ресурс ‘выжимка’ будет иметь резерв в размере M часов».

Мы нашли оптимальный план. Но что, если условия изменятся? Для ответа на этот вопрос в Excel есть специальные инструменты.

Как читать отчеты о чувствительности и зачем это нужно

В окне результатов «Поиска решения» можно выбрать создание отчетов. Самый полезный из них — Отчет по устойчивости (или чувствительности). Он показывает, насколько стабильно наше решение и как оно изменится, если немного поменять исходные данные. Это демонстрирует более глубокий уровень анализа.

Ключевой показатель в этом отчете — «Теневая цена» для ограничений. Что это такое?

Теневая цена — это показатель того, на сколько увеличится ваша целевая функция (например, общая прибыль), если вы увеличите лимит дефицитного ресурса на одну единицу. Например, если теневая цена для операции «расщепление» равна 20, это означает, что каждый дополнительный час работы этого оборудования (в определенных пределах) принесет комбинату 20 денежных единиц дополнительной прибыли. Если у ресурса есть запас (он не дефицитный), его теневая цена всегда равна нулю, так как его и так хватает.

Анализ теневых цен позволяет принимать управленческие решения: в расширение каких ресурсов стоит вкладывать деньги в первую очередь.

Теперь вы вооружены полным набором инструментов для решения и анализа. В завершение, давайте рассмотрим, каких ошибок стоит избегать.

Типичные ошибки и финальный чек-лист для самопроверки

Даже при правильном понимании теории можно допустить досадную ошибку при настройке. Вот самые распространенные из них:

• Неправильная цель: Вместо «Максимум» случайно оставлен «Минимум».
• Неверные знаки ограничений: Использование знака «>=» вместо «<=» для лимитов ресурсов.
• Целевая ячейка содержит число: Целевая ячейка всегда должна содержать формулу, зависящую от переменных.
• Забыты изменяемые ячейки: Поле «Изменяя ячейки переменных» оставлено пустым.

Чтобы избежать этих проблем, перед тем как считать работу законченной, пройдитесь по этому краткому чек-листу:

1. Модель логична? Проверьте, что все формулы (прибыль, расход ресурсов) корректно ссылаются на ячейки с планом выпуска.
2. Все ли ограничения учтены? Сверьте список ограничений в окне Solver с условием задачи. Не забыли ли вы про неотрицательность?
3. Выбран ли Симплекс-метод? Для задач линейного программирования это обязательное условие.
4. Результаты интерпретированы? Убедитесь, что вы готовы не просто сдать цифры, а объяснить, что они означают для предприятия.

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

Список использованной литературы

1. Аттетков А.В., Галкин С.В., Зарубин В.С. Методы оптимизации: Учебник для вузов /Под ред. В.С. Зарубина, А.П. Крищенко.-М.: Изд-во МГТУ им. Н.Э. Баумана, 2003. -440с. ( Сер. Математика в технич. университете; Вып. X1V )
2. Мурга О.К. Численные методы оптимизации: Учебное пособие. Казань: Изд-во Казан.гос. техн.ун-та им. А.Н. Туполева,2006. 75с.
3. Летова Т.А., Пантелеев А.В. Экстремум функций в примерах и задачах: Учебное пособие. –М.: Изд-во МАИ, 1998. 376с.
4. Васильев Ф.П. Численные методы решения экстремальных задач. М., Наука, 1980, 520 с.
5. Лесин В.В., Лисовец Ю.П. Основы методов оптимизации. М.: Изд-во МАИ 1995, 344 с.
6. Мурга О.К. Численные методы оптимизации. Лабораторный практикум. Казань: Изд-во Казан. гос. техн. ун-та им. А.Н. Туполева,2000. 44с.