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

1. Сформулируем экономико  - математическую  модель задачи.

Обозначим через Х₁,  Х₂,  Х₃,  Х₄ количество ковров каждого типа.

Целевая функция  - это выражение, которое  необходимо максимизировать

f(x) = 3Х₁ +4Х₂ +3Х₃ +Х₄

Ограничения по ресурсам

7Х₁ +2Х₂ +2Х₃ +6Х₄

80

5Х₁ +8Х₂ +4Х₃ +3Х₄

480

2Х₁ +4Х₂ +Х₃ +8Х₄

130

Х₁, Х₂, Х₃, Х₄

0

Решение

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

Обозначьте  через Х₁, Х₂, Хз, Х₄ количество ковров каждого типа. В нашей задаче оптимальные значения вектора Х =(Х₁, Х₂, Хз, Х₄) будут помещены в ячейках ВЗ:ЕЗ, оптимальное значение целевой функции в ячейке F4.

2. Ввести исходные  данные.

Введем  исходные данные в созданную форму. В результате получим (рис. 19): 

Рис. 19.

3. Введем зависимость  для целевой функции

• Курсор в F4.

• Курсор на кнопку Мастер функций.

На экране диалоговое окно Мастер функций шаг 1 из 2.

• Курсор в окно Категория на категорию  Математические.

Рис. 20. 

• Курсор в окно Функции на СУММПРОИЗВ.                                                             

• В  массив 1 ввести³ В$3:E$3.

• В  массив 2 ввести В4:E4.

• Готово. На экране: в F4 введена функция, как показано на рис. 20.

4. Введем зависимость  для левых частей  ограничений:

• Курсор в F4.

• Копировать в буфер.

• Курсор в F7.

• Вставить из буфера.

• Курсор в F8.

• Вставить из буфера.                   

• Курсор в F9.

• Вставить из буфера.

На этом ввод зависимостей закончен.  

Запуск  Поиска решения. 

6. Назначение  целевой функции (установить целевую ячейку).

• Курсор в поле Установить целевую ячейку.

• Ввести адрес $F$4.
• Ввести  направление целевой функции: Максимальному значению.

         Ввести адреса искомых переменных:

• Курсор в поле Изменяя ячейки.
• Ввести адреса В$3:E$3.

5. Ввод ограничений.

Курсор  в поле  Добавить. Появится диалоговое окно Добавление ограничения (рис. 21). 

Рис. 21.

· В окне Ссылка на ячейку ввести $F$7.

· Ввести знак ограничение  £.

· Курсор в правое окно.

·Вести $H$7.

· Добавить. На экране опять диалоговое окно Добавление ограничения. Ввести остальные ограничения.

· После ввода последнего ограничения ввести ОК.

На  экране появится диалоговое окно Поиск решения с введенными условиями (рис. 22).

Рис. 22.

8) Ввод  параметров для решения ЗЛП (рис. 23).

• Открыть окно Параметры поиска решения.
• Установить флажок Линейная модель, что обеспечивает применение симплекс-метода.
• Установить флажок Неотрицательные значения.
• ОК (На экране диалоговое окно поиска решения).
• Выполнить (На экране диалоговое окно результаты поиска решения – рис. 24)

 

Рис. 23.

Рис. 24.

Полученное решение означает, что максимальный доход 150 тыс.  руб. фабрика может получить при выпуске 30 ковров второго вида и 10 ковров третьего вида. При этом ресурсы труд и оборудование будут использованы полностью, а из 480 кг пряжи  (ресурс сырье) будет использовано 280 кг.

Создание  отчета по результатам поиска решения

Excel позволяет  представить результаты поиска  решения в форме отчёта. Существует три типа таких отчетов:                                  

• Результаты (Answer). В отчет включаются исходные и конечные значения целевой и влияющих ячеек, дополнительные сведения об ограничениях
• Устойчивость (Sensitivity). Отчет, содержащий сведения о чувствительности решения к малым изменениям в изменяемых ячейках иди в формулах ограничений.                                  
• Пределы (Limits). Помимо исходных и конечных значений изменяемых и целевой ячеек в отчет включаются верхние и нижние   границы значений, которые могут принимать влияющие ячейки при соблюдении ограничений.

1. Отчет  по результатам. 

В отчете по результатам содержатся  оптимальные значения переменных Х₁, Х₂, Х₃, Х₄, которые соответственно равны 0,10,30,0;  значение целевой функции – 150,    а  также левые части ограничений.

Пример  № 3.

Рассмотрим  пример нахождения решения для следующей  задачи ЛП:

1. Ввод исходных  данных

Создание  экранной формы и ввод в нее условия задачи

Экранная  форма для ввода условий задачи вместе с введенными в нее исходными данными представлена на рис. 26.

Рис. 25. Экранная форма задачи (1) (курсор в  ячейке F6)

В экранной форме на рис. 25 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка в Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП. Так, например, переменным задачи (1.1) соответствуют ячейки B3 ( ), C3 ( ), D3 ( ), E3 ( ), коэффициентам ЦФ соответствуют ячейки B6 ( 130,5), C6 ( 20), D6 ( 56), E6 ( 87,8), правым частям ограничений соответствуют ячейки H10 ( 756), H11 ( 450), H12 ( 89) и т.д.

Ввод  зависимостей из математической модели в экранную форму

Зависимость для ЦФ

В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно (1) значение ЦФ определяется выражением

Используя обозначения соответствующих ячеек в Excel (см. рис. 25), формулу для расчета ЦФ (2) можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть

Рис. 26. Экранная форма задачи (1) после ввода  всех необходимых формул (курсор в ячейке F6)