Этап. Заполнение окна Поиск решения

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

После появления диалогового окна Поиск решенияследует выполнить следующие действия:

1. В поле с именемУстановить целевую ячейку:ввести абсолютный адрес ячейки $I$2.

2. Для группы Равной:выбрать вариант поиска решения – минимальному значению.

3. В поле с именемИзменяя ячейки:ввести абсолютный адрес ячеек $B$2:$H$2.

4. Добавить 3 ограничения, представляющие минимальные суточные потребности в питательных веществах. С этой целью выполнить следующие действия:

§ для задания первого ограничения в исходном диалоговом окне Поиск решениянажать кнопку с надписьюДобавить(рис. 6.5, а);

§ в появившемся дополнительном окне выбрать ячейку $I$5, которая должна отобразиться в поле с именем Ссылка на ячейку;

§ в качестве знака ограничения из выпадающего списка выбрать нестрогое неравенство " ";

§ в качестве значения правой части ограничения выбрать ячейку $J$5;

§ для добавления первого ограничения в дополнительном окне нажать кнопку с надписьюДобавить;

§ аналогичным образом задать оставшиеся два ограничения (рис. 6.5, б).

Добавить ограничение на допустимые значения переменных. С этой целью выполнить следующие действия:

§ в исходном диалоговом окне Поиск решениянажать кнопку с надписьюДобавить;

§ в появившемся дополнительном окне выбрать диапазон ячеек$В$2:$Н$2, который должен отобразиться в поле с именем Ссылка на ячейку;

§ в качестве знака ограничения из выпадающего списка выбрать нестрогое неравенство " ";

§ в качестве значения правой части ограничения в поле с именем Ограничение:ввести значение 0;

§ для добавления ограничения в дополнительном окне нажать кнопку с надписьюДобавить (рис. 6.6, а).

 

а

б

 

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

а

б

 

Рисунок 6.6 – Ограничения на значения переменных и параметры мастера поиска решения для задачи об оптимальной диете

Этап. Параметры поиска

 В дополнительном окне параметров поиска решения следует выбрать отметки Линейная модельи Неотрицательные значения(рис. 6.6, б).

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

Результатом решения задачи об оптимальной диете являются найденные оптимальные значения переменных: x₁ = 0, x₂ = 0,211, x₃ = 0,109, x₄ = 1,887, x₅= 0, х₆= 0, x₇ = 0, которым соответствует значение целевой функции: fопт= 2587,140. При выполнении расчетов для ячеек В2:I2был выбран числовой формат с 3 знаками после запятой.

Анализ найденного решения показывает, что для удовлетворения суточной потребности в питательных веществах (белки, жиры, углеводы) следует использовать 211 гмяса баранины, 109 г сыра и 1887 г бананов, совсем отказавшись от хлеба, огурцов, помидоров и винограда. При этом общая калорийность найденной оптимальной диеты будетприближенно равна 2590 ккал, что вполне соответствует малоактивному образу жизни без серьезных физических нагрузок. Напомним, что согласно медицинским данным, энергетические затраты работников интеллектуального труда (юристы, бухгалтера, врачи, педагоги) лежат в пределах 3000 ккал.

Рисунок 6.7 – Результат количественного решения задачи

об оптимальной диете

Контрольные вопросы

1. Сформулировать основную задачу линейного программирования. Записать математическую модель ЗЛП.

2. Для чего предназначена надстройка Поиск решения?

3. Что понимают под целевой ячейкой, изменяемыми ячейками?

4. Основные этапы решения ЗЛП с помощью процессора Excel.

5. Как сохранить установочные параметры для поиска решения в виде модели?

6. Какие существуют виды отчетов и как их создать? Продемонстрировать на примере.