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

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

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

Фундамент решения, или как перевести условие задачи на язык математики

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

1. Целевая функция. Это то, что мы хотим максимизировать или минимизировать. В нашем случае — это суммарный прирост урожайности от всех внесенных удобрений во всех зонах. Если мы знаем, какой прирост дает 1 кг каждого удобрения в каждой зоне, то общая формула будет выглядеть как сумма произведений количества внесенного удобрения на соответствующий ему прирост. Наша задача — добиться максимального значения этой функции.
2. Изменяемые ячейки (переменные). Это те параметры, которыми мы можем управлять, чтобы достичь цели. В нашей задаче это количество (в кг) каждого из трех видов удобрений, которое будет внесено на 1 гектар в каждой из четырех климатических зон. Именно эти значения и должен будет найти Excel.
3. Система ограничений. Это правила и лимиты реального мира, которые нельзя нарушать. Для нашей задачи они таковы:
   • Ограничение по запасам: Общее количество использованного удобрения каждого вида (например, азотного) не может превышать его имеющийся запас на складе.
   • Ограничение по площади: Мы не можем вносить удобрения на площадь, превышающую имеющиеся посевные площади в каждой из четырех климатических зон.
   • Ограничение неотрицательности: Логичное и важное условие — мы не можем внести отрицательное количество удобрений. Их количество должно быть больше или равно нулю.

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

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

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

Создайте на листе Excel несколько логических блоков:

• Блок исходных данных: Сюда мы аккуратно переносим всю информацию из условия задачи: таблицу с приростом урожайности от 1 кг каждого удобрения в каждой зоне, общие запасы каждого вида удобрений и доступные посевные площади по зонам.
• Блок искомых переменных: Создайте таблицу, повторяющую по структуре таблицу прироста урожайности. Ячейки в этой таблице пока оставьте пустыми — именно их и будет заполнять «Поиск решения», подбирая оптимальное распределение удобрений.
• Блок расчета целевой функции: Выделите одну ячейку, в которой будет рассчитываться общий прирост урожайности. Для этого идеально подходит функция СУММПРОИЗВ. Она перемножит соответствующие ячейки из таблицы прироста урожайности и таблицы искомых переменных, а затем сложит результаты. Это и будет наша целевая ячейка.
• Блок расчета ограничений: Для каждого ограничения нужно создать свою формулу. Например, чтобы рассчитать общий расход первого вида удобрения, нужно сложить количество этого удобрения, выделенное для каждой из четырех зон. Так мы получим «левые части» наших ограничений, которые «Поиск решения» будет сравнивать с «правыми» (запасами на складе).

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

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

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

Следуйте простому алгоритму:

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

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

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

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

• «Оптимизировать целевую функцию»: Сюда нужно ввести ссылку на ячейку, где у нас рассчитывается суммарный прирост урожайности (та самая, с формулой СУММПРОИЗВ).
• «До»: Так как наша цель — получить максимальный прирост, выбираем переключатель «Максимума».
• «Изменяя ячейки переменных»: В это поле мы вносим диапазон пустых ячеек, которые мы подготовили для искомых значений — то есть, сколько килограмм каждого удобрения вносить в каждой зоне.
• «В соответствии с ограничениями»: Это самый важный блок. Нажимаем кнопку «Добавить» и последовательно вводим все наши условия. Для каждого ограничения мы указываем ячейку с расчетным значением (например, общий расход первого удобрения), выбираем знак (<=, =, >=) и указываем ячейку с лимитом (например, запас этого удобрения на складе). Таким образом мы добавляем ограничения по запасам удобрений и по посевным площадям.
• «Сделать переменные без ограничений неотрицательными»: Обязательно поставьте эту галочку. Она гарантирует, что программа не будет рассматривать варианты с отрицательным количеством удобрений.
• «Выберите метод решения»: Поскольку наша целевая функция и все ограничения являются линейными (переменные не возводятся в степень, не перемножаются друг на друга), мы должны выбрать «Симплекс-метод». Это наиболее эффективный алгоритм для задач линейного программирования.

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

Анализ полученных результатов, или что нам говорят цифры

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

Что же означают полученные данные?

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

Для оформления вывода в контрольной работе недостаточно просто скопировать таблицу с результатами. Необходимо написать развернутое заключение, например: «В результате оптимизации с помощью надстройки «Поиск решения» в MS Excel был найден оптимальный план распределения удобрений. Для получения максимального прироста урожайности в размере [значение из целевой ячейки] тонн, необходимо распределить удобрения следующим образом: [далее следует описание плана на основе найденных значений]». Такой вывод демонстрирует не только умение пользоваться инструментом, но и глубокое понимание экономического смысла задачи.

Мы прошли весь путь от постановки задачи до ее полного решения и анализа. Давайте кратко подведем итоги.

Итак, мы убедились, что любая, даже самая сложная на первый взгляд, задача по оптимизации поддается решению, если действовать системно. Мы разбили весь процесс на логичные и последовательные шаги: сначала формализовали условие, переведя его на язык математической модели, затем построили наглядную вычислительную схему в Excel, после чего настроили мощный инструмент «Поиск решения» и, наконец, проанализировали полученный результат. Теперь вы вооружены не просто готовым решением для контрольной, но и эффективным методом, который пригодится вам в решении множества других академических и практических задач.

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

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