Задача.

            Металлургическому заводу требуется уголь с содержанием фосфора не более 0,03% и с долей зольных примесей не более 3,25%. Завод закупает три сорта угля А, В, С с известным содержанием примесей. Содержание примесей и цена исходных продуктов приведены в таблице. В какой пропорции нужно смешивать исходные продукты А, В, С, чтобы смесь удовлетворяла ограничениям на содержание примесей и имела минимальную цену?

Сорт угля

Содержание, %

Цена 1 т, руб.

фосфор

зола

А

0,06

2,0

30

В

0,04

4,0

30

С

0,02

3,0

45

Экономико-математическая модель

            Обозначим через Х1, Х2, Х3 объемы закупок соответствующего сорта угля.

            Целевая функция – это математическая запись критерия оптимальности, т.е. выражение, которое необходимо минимизировать f (х) = 30Х1 + 30Х2 + 45Х3.

            Ограничения по содержанию примесей:

0,06Х1 + 0,04Х2 + 0,02Х3 ≤ 0.03

2,0Х1 + 4,0Х2 + 3,0Х3 ≤ 3,25

        

Решение.

1. Создадим форму для ввода условий задачи. Запустим Microsoft Excel и создадим текстовую форму-таблицу для ввода условий задачи.

2. Укажем адреса ячеек, в которые будет помещен результат решения (изменяемые ячейки). Обозначим через Х1, Х2, Х3 объемы закупок каждого сорта угля. В нашей задаче оптимальные значения компонент вектора Х = (Х1, Х2, Х3) будут помещены в ячейках В3:D3, оптимальное значение целевой функции – в ячейке Е4.

           

3. Введем исходные данные задачи в созданную форму-таблицу.

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

  • Поставим курсор в ячейку Е4.
  • Подведем курсор на кнопку “Мастер функций”, расположенную на панели инструментов.
  • На экране появится диалоговое окно Мастер функций шаг 1 из 2.

  • В окне “Категория” Выбираем категорию Математические;
  • В окне Функции выбираем СУММПРОИЗВ;
  • На экране появится диалоговое окно СУММПРОИЗВ.

  • В строку “Массив 1” вводим В$3:D$3;
  • В строку “Массив 2” вводим В4:D4;
  • Нажимаем кнопку “OK”. На экране: в ячейку Е4 введена функция.

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

  • Поставим курсор в ячейку Е4;
  • На панели инструментов нажмем кнопку Копировать в буфер;
  • Поставим курсор в ячейку Е7;
  • На панели инструментов нажмем кнопку Вставить из буфера;
  • Поставим курсор в ячейку Е8;
  • На панели инструментов нажмем кнопку Вставить из буфера.

В строке Меню в имени Сервис в развернутом меню выберем команду Поиск решения, появится диалоговое окно Поиск решения.

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

  • Установим курсор в строку Установить целевую ячейку;
  • Введем адрес ячейки $Е$4$;
  • Введем направление целевой функции (Минимальному значению);
  • Поставим курсор в строку Изменяя ячейки;
  • Введем адреса искомых переменных $B$3:$D$3.

7. Введем ограничения:

  • Нажмем кнопку Добавить, появится диалоговое окно Добавление ограничений;
  • В строке Ссылка  на ячейку введем адрес $E$7;
  • Введем знак ограничения ≤;
  • В строке Ограничение введем адрес $G$7;
  • Нажмем кнопку Добавить, появится диалоговое окно Добавление ограничений;

  • Введем остальные ограничения задачи по вышеописанному алгоритму;
  • После введения последнего ограничения нажмем кнопку ОК.

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

           

8. Введем параметры для решения ЗЛП:

  • В диалоговом окне нажмем кнопку Параметры. На экране появится окно Параметры поиска решения;

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

Через непродолжительное время появится диалоговое окно Результаты поиска решения и исходная таблица с заполненными ячейками В3:D3 для значений Хi и ячейка Е4 с минимальным значением целевой функции.

            Ниже в таблице приведены результаты решения задачи.

            Отчеты по результатам и устойчивости выглядят следующим образом:

Microsoft Excel 11.0 Отчет по результатам

Microsoft Excel 11.0 Отчет по устойчивости

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