Разработка числовой экономико-математической модели задачи
Всю информацию по составлению кормосмеси записывают в числовую матричную экономико-математическую модель задачи, которая приводится в приложении Г.
Технико-экономическими коэффициентами матрицы задачи являются в основном показатели содержания различных питательных веществ в единице каждого вида корма и кормовой добавки.
В задачу входят 18 ограничений, которые записаны по строкам матрицы.
Первое условие задачи, которое требует того, что бы общий вес всех компонентов кормосмеси был равен 100 граммам. Оно записывается таким образом:
x1+ x2+ …+x8 = 100.
Следующие пять ограничений задачи позволяют обеспечить содержание в кормосмеси необходимого количество различных питательных веществ. Так ограничение по содержанию обменной энергии записывается как:
2,6x1+ 2,6x2+2,75x3+2,5x4+9x6 ≥ 245
Аналогичным образом составляются ограничения 3-6.
Каждая группа и вид кормов может изменяться в определенных пределах. Поэтому устанавливаются нижние и верхние границы включения в состав кормосмеси их содержание. В пределах разницы между нижней и верхней границей может изменяться содержание каждой группы и вида корма.
Требования по включению в состав кормосмеси минимально и максимально содержания отдельных групп кормов осуществляется с помощью включения в задачу дополнительных ограничений (7-12).
Так, например, требование по включению в состав кормосмеси не мене 88% концентрированных кормов (ограничение 7) можно записать следующим образом:
2,6 x1 + 2,6x2 ≥ 215,6
Замечание. Ограничения по включению отдельных групп и видов кормов строятся по балансу обменной энергии (в килокалориях).
В задачу входят дополнительные ограничения по включению отдельных видов кормов в состав кормосмеси. Например, для полного сбалансирования рассчитываемого состава кормосмеси по минимальному содержанию соли (ограничение 13) вводится ограничение вида:
x7 ≥ 0,03
Ограничения 14-16 имеют аналогичный характер построения.
Следующая группа ограничений определяет включение отдельных видов кормов в соответствующую группу. Так ограничения 17-18 по включению рыбной муки в группу кормов животного происхождения выглядят так:
17) x4 ≥ 0,2(x3 + x4) или после преобразования: –0,2x3 + 0,8x4 ≥ 0.
18) –0,4x3 + 0,6x4 ≥ 0.
Разработка числовой экономико-математической модели задачи заканчивается разработкой функционала. Целевая функция задачи имеет следующую математическую запись:
С= 0,37х1 + 0,41х2 + 0,65х3 + 2,6х4 + 0,03х5 + 1,75х6 + 0,1х7 + 0,45х8 " min
Решение задачи на ПК
Мощным средством анализа данных MS Excel является надстройка Solver (Поиск решения). С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета.
Программа Поиск решений – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных и нелинейных задач оптимизации. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:
– количество неизвестных – 200;
– количество формульных ограничений – 100;
– количество предельных условий на неизвестные – 400.
Программа Поиск решений при инсталляции пакета MS Office 2007 могла быть не установлена, перед ее активизацией обратитесь к команде [Данные] приложения Excel.
По умолчанию в MS Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка [Microsoft Office] , щелкните [Параметры Excel], а затем выберите категорию [Надстройки]. В поле [Управление] выберите значение [Надстройки Excel] и нажмите кнопку [Перейти]. В поле [Доступные надстройки] установите флажок рядом с пунктом [Поиск решения] и нажмите кнопку [ОК]. После запуска программы на экране появится окно [Поиск решения].
Прежде чем воспользоваться программой Поиск решения необходимо создать файл данных. Файл данных создается в электронных таблицах Excel.
Электронная таблица с исходными данными оптимизационной задачи представим в приложении Д. Необходимо уточнить следующее. В ячейках B4:I4 должны быть поставлены нули – это изменяющиеся ячейки. Здесь в результате решений будут находиться значения искомых переменных величин.
В ячейке J5:J22 необходимо ввести формулы. Например, для ячейки J5 формула выглядит следующим образом:
=СУММПРОИЗВ(B5:I5;$B$4:$I$4).
Целевая ячейка L23 содержит формулу:
=СУММПРОИЗВ(B23:I23; $B4$4:$I$4).
В этой ячейке будет находиться минимальное значение целевой функции.
В диалоговом окне Поиск решения необходимо задать ряд параметров (рисунок 1). В поле [Установить целевую ячейку] следует указать адрес ячейки, содержимое которой должно быть оптимизировано. В нашем случае это ячейка М23, в которой представлена минимум стоимости кормосмеси. Очень важно, чтобы эта ячейка содержала формулу и была хотя бы косвенно связана с влияющими ячейками, иначе при изменении одной ячейки не будет изменяться другие. Поскольку в нашем примере необходимо минимизировать значения в целевой ячейке, выделите в группе опций [Равной] опцию [Минимальному значению]. В поле [Изменяя ячейки] необходимо задать область ячеек, значения которых программа должна заменить для получения оптимального результата. Активизируйте указанное поле ввода и выделите ячейки C4:J4 (в случае необходимости переместите диалоговое окно или укажите адрес области вручную).
Рисунок 1 – Окно Поиск решения
Теперь следует приступить к заданию ограничений, играющих немаловажную роль. Ограничения могут применяться к зависимым, влияющим или другим ячейкам, прямо или косвенно связанным с целевой ячейкой. Заданные ограничения следует указать в одноименном поле диалогового окна Поиск решения.
Чтобы задать ограничения, нажмите кнопку [Добавить], после чего на экране появится диалоговое окно, в котором можно выполнить все необходимые установки (рисунок 2).
Рисунок 2 – Окно Добавление ограничений
В поле [Ссылка на ячейку] этого диалогового окна следует указать адрес ячейки, содержимое которой должно удовлетворять заданному ограничению. В поле необходимо задать значения или адрес ячейки, если требуемое значение содержится в определенной ячейке. Содержимое указанных полей соединяется с помощью известных операторов сравнения.
После ввода ограничений необходимо нажать кнопку ОК, при этом диалоговое окно задания ограничений будет закрытым. Тем самым все параметры для запуска программы Поиск решения заданы. При необходимости, используя кнопку [Изменить], Вы сможете в дальнейшем изменить заданные ограничения. После нажатия кнопки [Удалить] выделенное ограничение будет удалено из списка. Чтобы можно было без особых проблем несколько раз последовательно повторить поиск оптимального значения для одной и той же целевой ячейки с измененными параметрами, записи в диалоговом окне Поиск решения сохраняются. При следующем вызове программы Поиск решения необходимо изменить только желаемый параметр. Вышесказанное имеет силу только во время одного сеанса работы с программой и при работе только с текущей рабочей книгой. При сохранении рабочей книги после применения программы Поиск решения параметры, установленные в диалоговом окне Поиск решения, также будут сохранены. В дальнейшем после открытия данной рабочей книги и активизации программы Поиск решения все параметры снова будут представлены на экране.
Далее необходимо установить параметры. Для этого необходимо нажать кнопку [Параметры]. В окне Параметры поиска решения необходимо отметить параметры [Линейная модель] и [Неотрицательные значения], затем нажать кнопку [ОК] (Рисунок 3).
Рисунок 3 – Окно Параметры поиска решения
Запустите процесс вычисления нажатием кнопки [Выполнить] и следите за строкой состояния. В ней отображаются отдельные шаги выполнения операции поиска решения. После завершения вычислений новые значения будут вставлены в таблицу, а на экране появится диалоговое окно, информирующее о завершении процесса поиска решения (рисунок 4).
Рисунок 4 – Окно Результаты поиска решения
В полученном решении программа предоставляет свободу выбора. Полученный результат можно отразить в текущей рабочей книге или представить в виде отчета. Отчет создается на отдельном рабочем листе. При задании режима создания отчета в списке [Тип отчета] следует выбрать [Тип отчета]. Если активизирована опция [Сохранить найденное решение], то после закрытия диалогового окна нажатием кнопки [ОК], новые значения будут представлены в текущей таблице (приложение Е).
Если поиск решения успешно закончен, в окне диалога [Результаты поиска решения] выводится одно из следующих сообщений:
– решение найдено. Все ограничения и условия оптимальности выполнены;
– все ограничения соблюдены с установленной точностью и найдено заданное значение целевой ячейки;
– поиск свелся к текущему решению. Все ограничения выполнены;
– значение целевой ячейки, не менялось в течение последних пяти итераций. Решение возможно найдено или итеративный процесс улучшает решение очень медленно;
Если поиск не способен достичь оптимального решения, в окне диалога [Результаты поиска решения] выводится одно из следующих сообщений:
– поиск не может улучшить текущее решение. Все ограничения выполнены;
– поиск остановлен (истекло заданное на поиск время);
– поиск остановлен (достигнуто максимальное число итераций);
– значение целевой ячейки не сходятся;
– поиск не может найти подходящего решения;
– поиск остановлен по требованию пользователя.
– условия для линейной модели не удовлетворяются.
– при поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения;
– мало памяти для решения задачи.
Примечания:
1 Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Excel будет пересчитан с учетом последних найденных значений для изменяемых ячеек.
2 Чтобы создать отчет на основе решения, найденного с помощью Поиск решения, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет создан на новом листе книги. Если Поиск решения не удастся найти решение, параметры создания отчета будут недоступны.
3 Чтобы сохранить значения изменяемых ячеек в виде сценария, который затем можно будет просмотреть, нажмите кнопку [Сохранить сценарий] в диалоговом окне [Результаты поиска решения]и введите имя сценария в поле[Имя сценария].