Пример решения задачи линейного программирования с помощью MS Excel

Хозяйство специализируется в полеводстве на производстве зерна, сахарной свеклы и подсолнечника. В с.-х. предприятии имеются 3200 га пашни, трудовые ресурсы в объеме 7 000 чел.-дней и минеральные удобрения в объеме 15000 ц.д.в. Требуется найти такое сочетание посевных площадей, которое обеспечило бы получение максимума прибыли.

Следует также учесть, что

- площадь посева технических культур (сахарной свеклы и подсолнечника) не должна превышать 25% общей площади пашни;

- хозяйством заключен договор на продажу зерна в объеме 65000 ц.

Для разработки экономико-математической модели необходима подготовка входной информации (табл. 1).

Таблица 1

За неизвестные примем площади посева сельскохозяйственных культур по видам:

X 1 - зерновых культур

X 2 - сахарной свеклы

X 3 - подсолнечника

Для построения экономико-математической модели задачи необходимо учесть все условия. В данном случае, по этим условиям можно составить пять ограничений:

- сумма площадей посева сельскохозяйственных культур не должна превышать площади, имеющейся в хозяйстве (3200 га). Коэффициентами при неизвестных в этом ограничении характеризуют расход пашни на 1 га каждой сельскохозяйственной культуры. В данном случае технико-экономические коэффициенты по неизвестным будут равняться единице. В правой части записывается общая площадь пашни.

1) Х1+Х2+Х3<=3200

- сумма площадей посева технических культур не должна превышать площади, которая может быть отведена для этой цели (3200*0,25=800 га). Коэффициентами при неизвестных в этом ограничении характеризуют расход пашни, отведенной под посевы технических культур, на 1 га каждой технической сельскохозяйственной культуры. В данном случае технико-экономические коэффициенты по неизвестным Х2 и Х3 будут равняться единице, а по нетехническим сельскохозяйственным культурам (Х3) - нулю. В правой части записывается максимальная площадь пашни, которая может быть отведена под посевы технических культур.

2) Х2+Х3<=800

- третье и четвертое ограничения гарантируют, что использование трудовых ресурсов и минеральных удобрений не превысит их наличие в хозяйстве. Другими словами, сумма произведений норм затрат ресурсов на 1 га на площади посева соответствующих сельскохозяйственных культур не должна превышать объемов ресурсов, имеющихся в с.-х. предприятии. Коэффициентами при неизвестных в этих ограничениях будут являться нормы расхода ресурсов (в третьем ограничении – трудовых ресурсов, в четвертом – минеральных удобрений) на 1 га площади посева сельскохозяйственных культур. В данном случае технико-экономические коэффициенты взяты из таблицы 1. В правой части записывается наличие этих ресурсов в хозяйстве.

3) 1,5Х1+4,5Х2+1,5Х3<=7000

4) 2Х1+15Х2+2,3Х3<=15000

- пятое ограничение гарантирует производство запланированного объема зерна. В качестве коэффициентов при переменных выступает выход зерна с 1 га площади посева с.-х. культур. При неизвестной Х1 это урожайность зерновых (таблица 1). При переменных Х2 и Х3 этот коэффициент равен нулю. В правой части записывается план производства зерна.

5) 26Х1>=65000

В результате получена система пяти линейных неравенств с тремя неизвестными. Требуется найти такие неотрицательные значения этих неизвестных Х1>=0; Х2>=0; Х3>=0, которые бы удовлетворяли данной системе неравенств и обеспечивали получение максимума прибыли от отрасли растениеводства в целом:

Z max = 2,89Х1+7,93Х2+3,53Х3

В качестве коэффициентов при неизвестных в целевой функции выступает прибыль, получаемая с 1 га площади посева сельскохозяйственных культур. Эти коэффициенты рассчитаны на основании данных таблицы 1.

Поскольку данная задача решается с помощью MS Excel , то и подготовку всей входной информации для построения экономико-математической модели целесообразно осуществлять также с использованием этого табличного процессора (рис 1). Это облегчает не только расчеты технико-экономических коэффициентов и других данных, но и дает в дальнейшем возможность автоматического обновления информации в экономико-математической модели.

Рисунок 1

Вся разработанная информация сводится в развернутую экономико-математическую модель и заносится в рабочий лист MS Excel . (Рис. 2.)

Рисунок 2

Данные в модель рекомендуется заносить в виде ссылок на ячейки с соответствующей информацией в расчетных рабочих листах или рабочих листах с исходными сведениями. На рисунке 3 показано, как в ячейке F9 представлена информация по норме затрат удобрений на 1 га посева подсолнечника.

Рисунок 3

В столбцы А («№»), В («Ограничения»), С («Единицы измерения») и H («Тип ограничений») вводятся соответствующие данные непосредственно в модель (рис.1). Они не используются в расчетах и служат для информативности и облегчения понимания содержания модели. В столбец I («Объем ограничений») вводятся ссылки на ячейки, содержащие соответствующую названию столбца информацию (значения правых частей построенных ранее неравенств).

Для искомых величин переменных Х1 , Х2 , Х3 нами были оставлены пустые ячейки - соответственно D5 , E 5 , F 5 . Изначально пустые ячейки программа MS Excel воспринимает как ячейки, значение которых равно нулю. Столбец G , названный нами «Сумма произведений », предназначен для определения суммы произведений значений искомых неизвестных (ячейки D5 , E 5 , F 5 ) и технико-экономических коэффициентов по соответствующим ограничениям (строки 6-10) и целевой функции (строка 11). Таким образом, в столбце G определяется:

- - количество используемых ресурсов (ячейка G6 – общей площади пашни; G7 – пашни, которая может быть использована под посевы технических культур; G8 – трудовых ресурсов; G9 – минеральных удобрений);

- - количество произведенного зерна (ячейка G10 );

- - величина прибыли (ячейка G11 ).

На рисунке 2 показано, как в ячейке G11 реализуется запись суммы произведений значений переменных (площадей посева с.-х. культур - ячейки D5 , E 5 , F 5 ) на соответствующие прибыли с 1 га их посева(ячейки D11 , E 11 , F 11 )с помощью функции MS Excel «СУММПРОИЗВ ». Так как при написании данной формулы использованы абсолютные адресации на ячейки от D5 до F 5 ,эта формула может быть скопирована в другие ячейки от G 6 до G10 .

Таким образом, построен опорный план (рис. 2) и получено первое допустимое решение. Значения неизвестных Х1 , Х2 , Х3 равны нулю (ячейки D5 , E 5 , F 5 - пустые ячейки), ячейки столбца G «Сумма произведений» по всем ограничениям (строкам 6-10) и целевой строке (строка 11) также имеют нулевые значения.

Экономическая интерпретация первого опорного плана звучит следующим образом: в хозяйстве имеются ресурсы, рассчитаны все технико-экономические коэффициенты, но процесс производства еще не начат; ресурсы не использовались, и, соответственно, прибыли нет.

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

После выбора данной команды появится диалоговое окно (рис. 4).

Рисунок 4

Поскольку в качестве критерия оптимизации нами выбрана максимизация прибыли, в поле Установить целевую ячейку введите ссылку на ячейку, содержащую формулу расчета прибыли. В нашем случае это ячейка $G$11 . Чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек (влияющими, в данном случае это и изменяемые ячейки, являются ячейки, которые предназначены для хранения значений искомых неизвестных), переключатель установите в положение максимальному значению ;

В поле Изменяя ячейки введите ссылки на изменяемые ячейки, разделяя их запятыми; либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($ D $5:$ F $5 ).

В поле Ограничения введите все ограничения, накладываемые на поиск решения. Добавление ограничения рассмотрим на примере добавления первого ограничения по общей площади пашни.

В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Добавить . Появится следующее диалоговое окно (рис. 5)

Рисунок 5

В поле Ссылка на ячейку введите адрес ячейки, на значение которой накладываются ограничения. В нашем случае, это ячейка $ G $6 , где находится формула расчета используемой пашни в текущем плане.

Выберите из раскрывающегося списка условный оператор <= , который должен располагаться между ссылкой и ограничением.

В поле Ограничение введите ссылку на ячейку, в которой находится значение наличия площади пашни в хозяйстве, либо ссылка на это значение. В нашем случае, это ячейка $ I $6

В результате диалоговое окно примет следующий вид (рис. 6).

Рисунок 6

Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить . Аналогично вводятся и другие ограничения. Чтобы вернуться в диалоговое окно Поиск решения , нажмите кнопку OK .

После выполнения вышеперечисленных инструкций диалоговое окно Поиск решения будет иметь следующий вид (рис. 7).

Рисунок 7

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

Флажок Линейная модель в диалоговом окне Параметры Поиска решения (рис. 8) позволяет задать любое количество ограничений. Флажок Неотрицательные значения позволит соблюсти условие неотрицательности переменных (при решении нашей задачи – поставить обязательно). Остальные параметры можно оставить без изменений, либо установить нужные для вас параметры, при необходимости используя справку.

Рисунок 8

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

- чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения .

Рисунок 9

Для того чтобы прервать поиск решения, нажмите клавишу ESC .

Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек. В результате решения и сохранения результатов поиска на листе модель примет следующий вид (табл. 10).

Рисунок 10

В ячейках D5 -F5 получены значения искомых неизвестных (площади посева равны: зерновых -2500 га, сахарной свеклы - 661 га, подсолнечника – 39 га), в ячейках G6 -G9 определены объемы используемых ресурсов (общей площади пашни – 3200 га; площади пашни, которая может быть использована под посевы технических культур – 700 га; трудовых – 6781,9 чел.-дней; минеральных удобрений – 15000 ц.д.в.), в ячейке G10 установлено количество произведенного зерна (65000 ц.). При всех этих значениях величина прибыли достигает 12603,5 тыс. руб. (ячейка G11 ).

В случае если в результате поиска не было найдено решение, удовлетворяющее заданным условиям, в диалоговом окне Результаты поиска решения появится соответствующее сообщение (рис. 11).

Рисунок 11

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

Если ограничений в модели очень много, то визуально достаточно трудно сравнивать и проверять на верность каждую строку. Для облегчения рекомендуется добавить в модель еще один столбец «Проверка», где с помощью функций MS Excel «ЕСЛИ » и «ОКРУГЛ » можно организовать автоматическую проверку (рис. 12).

Рисунок 12

Требуется определить, в каком количестве надо выпустить продукцию четырех типов Прод1, Прод2, Прод3, Прод4, для изготовления которой требуются ресурсы трех видов: трудовые, сырье и финансы. Количество ресурса каждого вида, необходимое для выпуска единицы продукции данного типа, называется нормой расхода. Нормы расхода, а также прибыль, получаемая от реализации единицы каждого типа продукции, приведены на рис. 1.

Рисунок 1.

Математическая модель задачи имеет вид:

где x j – количество выпускаемой продукции j-го типа; F – функция цели; в левых частях выражений ограничений указаны величины потребного ресурса , а правые части показывают количество имеющегося ресурса .

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

Для решения задачи с помощью Excel следует создать форму для ввода исходных данных и ввести их. Форма ввода показана на рис. 2.

В ячейку F6 введено выражение целевой функции как суммы произведений значений прибыли от выпуска единицы продукции каждого типа на количество выпускаемой продукции соответствующего типа. Для наглядности на рис. 3 представлена форма ввода исходных данных в режиме вывода формул.

В ячейки F8:F10 введены левые части ограничений для ресурсов каждого вида.

Рисунок 2.

Рисунок 3.

Решение задачи линейного программирования

Для решения задач линейного программирования в Excel используется мощный инструмент, называемый Поиск решения . Обращение к Поиску решения осуществляется из меню Сервис , на экран выводится диалоговое окно Поиска решения (рис. 4).

Рисунок 4.

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

1 Назначить целевую функцию, для чего установить курсор в поле Установить целевую ячейку окна Поиск решения и щелкнуть в ячейке F6 в форме ввода;

2 Включить переключатель значения целевой функции, т.е. указать ее Равной Максимальному значению ;

3 Ввести адреса изменяемых переменных (x j): для этого установить курсор в поле Изменяя ячейки окна Поиск решения, а затем выделить диапазон ячеек B3:E3 в форме ввода;

4 Нажать кнопку Добавить окна Поиск решения для ввода ограничений задачи линейного программирования; на экран выводится окно Добавление ограничения (рис. 5) :

Ввести граничные условия для переменных x j (x j ³0), для этого в поле Ссылка на ячейку указать ячейку В3, соответствующую х 1 , выбрать из списка нужный знак (³), в поле Ограничение указать ячейку формы ввода, в которой хранится соответствующее значение граничного условия, (ячейка В4), нажать кнопку Добавить ; повторить описанные действия для переменных х 2 , х 3 и х 4 ;

Ввести ограничения для каждого вида ресурса, для этого в поле Ссылка на ячейку окна Добавление ограничения указать ячейку F9 формы ввода, в которой содержится выражение левой части ограничения, наложенного на трудовые ресурсы, в полях Ограничение указать знак £ и адрес Н9 правой части ограничения, нажать кнопку Добавить ; аналогично ввести ограничения на остальные виды ресурсов;

После ввода последнего ограничения вместо Добавить нажать ОК и возвратиться в окно Поиск решения.

Рисунок 5.

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

В окне Поиск решения нажать кнопку Параметры , на экран выводится окно Параметры поиска решения (рис. 6);

Установить флажок Линейная модель, что обеспечивает применение симплекс-метода;

Указать предельное число итераций (по умолчанию – 100, что подходит для решения большинства задач);

Установить флажок , если необходимо просмотреть все этапы поиска оптимального решения;

Нажать ОК , возврат в окно Поиск решения .

Рисунок 6.

Для решения задачи нажать кнопку Выполнить в окне Поиск решения , на экране – окно Результаты поиска решения (рис. 7), в котором содержится сообщение Решение найдено. Все ограничения и условия оптимальности выполнены. Если условия задачи несовместны, то выводится сообщение Поиск не может найти подходящего решения . Если целевая функция не ограничена, то появляется сообщение Значения целевой ячейки не сходятся .

Рисунок 7.

Для рассматриваемого примера решение найдено и результат оптимального решения задачи выводится в форме ввода: значение целевой функции, соответствующее максимальной прибыли и равное 1320, указывается в ячейке F6 формы ввода, оптимальный план выпуска продукции х 1 =10, х 2 =0, х 3 =6, х 4 =0 указывается в ячейках В3:С3 формы ввода (рис. 8).

Количество использованных для выпуска продукции ресурсов выводится в ячейки F9:F11: трудовых – 16, сырья – 84, финансов – 100.

Рисунок 8.

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

Рисунок 9.

Рисунок 10.

Чтобы продолжить поиск решения, следует нажимать кнопку Продолжить в окне Текущее состояние поиска решения .

Анализ оптимального решения

Прежде чем, перейти к анализу результатов решения, представим исходную задачу в форме

введя дополнительные переменные у i , представляющие собой величины неиспользованных ресурсов.

Составим для исходной задачи двойственную задачу и введем дополнительные двойственные переменные v i .

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

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

Результаты,

Устойчивость,

Пределы.

Для вызова отчета в поле Тип отчета выделить название нужного типа и нажать ОК .

1 Отчет по результатам (рис. 11) состоит из трех таблиц:

Таблица 1 содержит сведения о целевой функции; в столбце Исходно указывается значение целевой функции до начала вычислений;

Таблица 2 содержит значения искомых переменных x j , полученных в результате решения задачи (оптимальный план выпуска продукции);

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

Для Ограничений в графе Формула приведены зависимости, которые были введены при задании ограничений в окне Поиск решения ; в графе Значение указаны величины использованного ресурса; в графе Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, то в графе Состояние выводится сообщение связанное ; при неполном использовании ресурса в этой графе указывается не связан. Для Граничных условий приводятся аналогичные величины с той лишь разницей, что вместо неиспользованного ресурса показана разность между значением переменной x j в найденном оптимальном решении и заданным для нее граничным условием (x j ³0).

Именно в графе Разница можно увидеть значения дополнительных переменных y i исходной задачи в формулировке (2). Здесь у 1 =у 3 =0, т.е. величины неиспользованных трудовых и финансовых ресурсов равны нулю. Эти ресурсы используются полностью. Вместе с тем, величина неиспользованных ресурсов для сырья у 2 =26, значит, имеются излишки сырья.

Рисунок 11.

2 Отчет по устойчивости (рис. 12)состоит из двух таблиц.

В таблице 1 приводятся следующие значения:

Результат решения задачи (оптимальный план выпуска);

- Нормир. стоимость , т.е. величины, показывающие, насколько изменится целевая функция при принудительном включении единицы продукции соответствующего типа в оптимальный план;

Коэффициенты целевой функции;

Предельные значения приращения коэффициентов целевой функции, при которых сохраняется оптимальный план выпуска.

В таблице 2 содержатся аналогичные данные для ограничений:

Величины использованных ресурсов;

- Теневая цена , показывающая, как изменится целевая функция при изменении величины соответствующего ресурса на единицу;

Допустимые значения приращений ресурсов, при которых сохраняется оптимальный план выпуска продукции.

Рисунок 12.

Отчет по устойчивости позволяет позволяет получить двойственные оценки.

Как известно, двойственные переменные z i показывают, как изменится целевая функция при изменении ресурса i-го типа на единицу. В отчете Excel двойственная оценка называется Теневой ценой .

В нашем примере сырье не используется полностью и его ресурс у 2 =26. Очевидно, что увеличение количества сырья, например, до 111 не повлечет за собой увеличения целевой функции. Следовательно, для второго ограничения двойственная переменная z 2 =0. Таким образом, если по данному ресурсу есть резерв, то дополнительная переменная будет больше нуля, а двойственная оценка этого ограничения равна нулю.

В рассматриваемом примере трудовые ресурсы и финансы использовались полностью, поэтому их дополнительные переменные равны нулю (у 1 =у 3 =0). Если ресурс используется полностью, то его увеличение или уменьшение повлияет на объем выпускаемой продукции, и следовательно, на величину целевой функции. Двойственные оценки ограничений на трудовые и финансовые ресурсы отличны от нуля, т.е. z 1 =20, z 3 =10.

Значения двойственных оценок находим в Отчете по устойчивости , в таблице 2, в графе Теневая цена .

При увеличении (уменьшении) трудовых ресурсов на единицу целевая функция увеличится (уменьшится) на 20 единиц и будет равна

F=1320+20×1=1340 (при увеличении).

Аналогично, при увеличении объема финансов на единицу целевая функция будет

F=1320+10×1=1330.

Здесь же, в графах Допустимое увеличение и Допустимое уменьшение таблицы 2, показаны допустимые пределы изменения количества ресурсов j-го вида. Например, для при изменении приращения величины трудовых ресурсов в пределах от –6 до 3,55, как показано в таблице, структура оптимального решения сохраняется, т.е наибольшую прибыль обеспечивает выпуск Прод1 и Прод3, но в других количествах.

Дополнительные двойственные переменные также отражены в Отчете по устойчивости в графе Нормир. стоимость таблицы 1.

Если основные переменные не вошли в оптимальное решение, т.е. равны нулю (в примере х 2 =х 4 =0), то соответствующие им дополнительные переменные имеют положительные значения (v 2 =10, v 4 =20). Если же основные переменные вошли в оптимальное решение (х 1 =10, х 3 =6), то их дополнительные двойственные переменные равны нулю (v 1 =0, v 3 =0).

Эти величины показывают, насколько уменьшится (поэтому знак минус в значениях переменных v 2 и v 4) целевая функция при принудительном выпуске единицы данной продукции. Следовательно, если мы захотим принудительно выпустить единицу продукции вида Прод3, то целевая функция уменьшится на 10 единиц и будет равна 1320 -10×1 =1310.

Обозначим через Dс j изменение коэффициентов целевой функции в исходной модели (1). Эти коэффициенты определяют прибыль, получаемую при реализации единицы продукции j-го вида.

В графах Допустимое увеличение и Допустимое Уменьшение таблицы 1 Отчета по устойчивости показаны пределы изменения Dс j , при которых сохраняется структура оптимального плана, т.е. будет выгодно по-прежнему выпускать продукцию вида Продj. Например, при изменении Dс 1 в пределах -12£ Dс 1 £ 40, как показано в отчете, по-прежнему будет выгодно выпускать продукцию вида Прод1. При этом значение целевой функции будет F=1320+x 1 ×Dс j =1320+10×Dс j .

3 Отчет по пределам приведен на рис. 13. В нем показывается, в каких пределах могут изменяться значения x j , вошедшие в оптимальное решение, при сохранении структуры оптимального решения. Кроме этого, для каждого типа продукции приводятся значения целевой функции, получаемые при подстановке в оптимальное решение значения нижнего предела выпуска изделий соответствующего типа при неизменных значениях выпуска остальных типов. Например, если при оптимальном решении х 1 =10, х 2 =0, х 3 =6, х 4 =0 положить х 1 =0 (нижний предел) при неизменных х 2 , х 3 и х 4 , то значение целевой функции будет равно 60×0+70×0+120×6+130×0=720.

Решение задач линейного программирования в MS Excel

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

Если данная надстройка установлена, то «Поиск реше­ния»запускается из меню «Сервис». Если такого пункта нет, следует выполнить команду «Сервис - Надстройки...» и вы­ставить флажок против надстройки «Поиск решения».

Решение задачи оптимизации состоит из трёх этапов.

A. Создание модели задачи оптимизации.

B. Поиск решения задачи оптимизации.

C. Анализ найденного решения задачи оптимизации.

Рассмотрим подробнее эти этапы.

Этап А.

На этапе создания модели вводятся обозначения неиз­вестных, на рабочем листе заполняются диапазоны исход­ными данными задачи, вводится формула целевой функ­ции.

Этап В.

Команда «Сервис - Поиск решения» открывает диалоговое окно «Поиск решения», в котором, в свою очередь, имеются следующие поля:

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

«Равной» - служит для выбора варианта оптимизации зна­чения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить чис­ло, введите его в поле.

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

«Предположить» - используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле «Установить целевую ячейку». Результат поиска отображается в поле «Изменяя ячейки».

«Ограничения» - служит для отображения списка гранич­ных условий поставленной задачи.

«Добавить» - служит для отображения диалогового окна «Добавить ограничение».

«Изменить» - служит для отображения диалоговое окна «Изменить ограничение».

«Удалить» – служит для снятия указанного ограничения.

«Выполнить» – служит для запуска поиска решения по­ставленной задачи.

«Закрыть» - служит для выхода из окна диалога без запус­ка поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки «Парамет­ры, Добавить, Изменить или Удалить».

«Параметры» - служит для отображения диалогового окна «Параметры поиска решения», в котором можно загрузить или сохранить оптимизируемую модель и ука­зать предусмотренные варианты поиска решения.

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

Для решения задачи оптимизации выполните следую­щие действия.

1. В меню «Сервис» выберите команду «Поиск решения».

2. В поле «Установить целевую ячейку» введите адрес или имя ячейки, в которой находится формула оптимизируемой модели.

3. Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение, соответствующее максимальному значению.

Чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите пере­ключатель в положение соответствующее минимальному значению.

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

4. В поле «Изменяя ячейки» введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.

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

5. В поле «Ограничения» введите все ограничения, накладываемые на поиск решения.

6. Нажмите кнопку «Выполнить».

Чтобы восстановить исходные данные, установите пере­ключатель в положение «Восстановить исходные значения».

Этап С.

Для вывода итогового сообщения о результате решения используется диалоговое окно «Результаты поиска реше­ния».

Диалоговое окно «Результаты поиска решения» содер­жит следующие поля:

«Восстановить исходные значения» - служит для восста­новления исходных значений влияющих ячеек моде­ли.

«Отчеты» - служит для указания типа отчета, размещаемо­го на отдельном листе книги.

«Результаты» - используется для создания отчета, состоя­щего из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о наложенных ограничениях.

«Устойчивость» - используется для создания отчета, содер­жащего сведения о чувствительности решения к ма­лым изменениям в формуле (поле «Установить целе­вую ячейку», диалоговое окно «Поиск решения») или в формулах ограничений.

«Ограничения» - используется для создания отчета, состоя­щего из целевой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ. Такой отчет не создается для моделей, зна­чения в которых ограничены множеством целых чи­сел. Нижним пределом является наименьшее значе­ние, которое может содержать влияющая ячейка, в то время как значения остальных влияющих ячеек фиксированы и удовлетворяют наложенным ограни­чениям. Соответственно, верхним пределом называ­ется наибольшее значение.

«Сохранить сценарий» - служит для отображения диало­гового окна Сохранение сценария, в котором мож­но сохранить сценарий решения задачи, чтобы ис­пользовать его в дальнейшем с помощью диспетчера сценариев MS Excel.

Одной из возможных задач и моделей линейной оптимизации является задача о планировании производства.

Предприятие должно производить изделия видов: , причем количество каждого выпускаемого изделия не должно превысить спрос и одновременно не должно быть меньше за­планированных величин соответственно. На изготовление изделий идет m видов сырья , за­пасы которых ограничены соответственно величинами Известно, что на изготовление i -ro изделия идет единиц j -го сырья. Прибыль, получаемая от реализации изделий равна соответственно . Требуется так спланировать производство из­делий, чтобы прибыль была максимальной и при этом выполнялся план на производство каждого изделия, но не превышался спрос на него.