3.3. Решение задачи в среде msexcel(на примере задачи планирования производства).

3.3.1. Ввод условий задачи.

Ввод условий задачи состоит из следующих основных шагов:

1. Создание формы для ввода данных, необходимых для последующего решения.

2. Ввод исходных данных и зависимостей из математической модели.

3. Указание целевой ячейки (ячейки, в которую введена целевая функция), ввод ограничений и граничных условий в диалоговом окне Поиск решения.

Создание формы для ввода данных.

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

• искомых переменных;

• целевой функции;

• правой и левой части неравенств, описывающих ограничения, налагаемые на возможные варианты решения поставленной задачи.

Примерная форма для ввода данных, необходимых для решения «задачи о полках» текст которой приведен выше, представлена на рис. 3.1.

Рис. 3.1. Примерная форма для ввода условий задачи.

Ввод исходных данных и зависимостей из математической модели.

Рис. 3.2. Условия решения задачи.

Отметим, что целевая функция и левые части неравенств, определяющих возможные варианты решения поставленной задачи, вводятся формулой, в которой роль искомых переменных играют адреса ячеек, зарезервированных для вывода их значений после решения задачи, а роль коэффициентов – адреса ячеек, содержащих соответственные коэффициенты.³

Назначение целевой функции, ввод ограничений и граничных условий.

Данная стадия ввода условия задачи осуществляется в диалоговом окне Поиск решения(см. рис. 3.3.) для открытия которого необходимо в менюСервисвыбрать командуПоиск решения.⁴

Рис. 3.3. Диалоговое окно Поиск решения.

В этом диалоговом окне пользователь должен:

1. Назначить целевую ячейку. Для этого в поле «Установить целевую ячейку:» вводится адрес ячейки, содержащей целевую функцию. Затем устанавливается направление последней – значение, к которому она должна стремиться исходя из условий задачи (минимальное, максимальное, конкретное, задаваемое пользователем).

2. В поле «Изменяя ячейки:» ввести адреса ячеек, зарезервированных для искомых переменных.

3. Ввести ограничения и граничные условия. Для этого в диалоговом окне Поиск решения (см. рис.3.3.) нажать на кнопку Добавить. В открывшемся диалоговом окне Добавление ограничений (см. рис. 3.4.):

• в поле «Ссылка на ячейку:» ввести адрес ячейки листа, содержащей формулу для расчета показателя, используемого в качестве левой части неравенства,

• из списка знаков неравенств выбрать необходимый знак,

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

Рис. 3.4. Диалоговое окно Добавление ограничений

Добавление каждого последующего ограничения осуществляется с помощью кнопки Добавить. После окончания ввода ограничений необходимо нажать Ok

3.3.2. Решение задачи.

Установка параметров решения задачи.

С помощью команд, находящихся в диалоговом окне Параметры поиска решения⁶ (рис. 3.5), можно вводить условия для решения задач оптимизации всех классов.

Рис. 3.5. Диалоговое окно Параметры поиска решения

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

Поле «Максимальное время»

Служит для назначения времени в секундах, выделяемого на поиск решения задачи. В поле можно ввести время, не превышающее 32767 с (более 9 часов!). Значение 100, используемое по умолчанию, подходит для решения большинства задач.

Поле «Предельное число итераций»

Служит для назначения числа итераций. Используемое по умолчанию значение 100 подходит для решения большинства задач.

Поля «Относительная погрешность» и «Допустимое отклонение»

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

Флажок «Линейная модель»

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

Получение результата

После нажатия на кнопку Выполнитьдиалогового окнаПоиск решенияна экране появляется диалоговое окноРезультаты поиска решения(рис. 3.6.). Решение найдено, и его результаты приведены в таблице (рис.3.7).

Рис. 3.6. Диалоговое окно Результаты поиска решения

Рис. 3.7. Результаты решения задачи.

Из рисунка видно, что оптимальное решение поставленной задачи предполагает производство:

• полок типа А в количестве 450 штук (В3);

• полок типа В – в количестве 100 штук (С3).

При этом максимальная прибыль будет составлять 1720 единиц, а ресурсы используются следующим образом:

• потребление материала – 1200 единиц (D10);

• использование оборудования – 140 часов (D11).

Задание 3.1. Решить задачу следующего содержания:

Рацион кормления коров на молочной ферме может состоять из трех продуктов — сена, силоса и концентратов. Эти продукты содержат питательные вещества — белок, кальций и витамины. Численные данные представлены в таблице. В расчете на одну корову суточные нормы потребления белка и кальция составляют не менее 200 и 210 г, соответственно. Потребление витаминов строго дозировано и должно быть равно 87 мг в сутки.

Продукты	Питательные вещества
	Белок (г/кг)	Кальций (г/кг)	Витамины (мг/кг)
Сено	50	10	2
Силос	70	6	3
Концентраты	18О	3	1

Составить самый дешевый рацион, если стоимость 1 кг сена, силоса и концентрата составляет, соответственно, 1,5, 2 и 6 рублей.