Назначение ячеек для искомого результата.

ИНСТРУКЦИЯ ДЛЯ РЕШЕНИЯ в EXCEL

ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ (ЗЛП).

Решение задачи оптимизации в Excel состоит из следующих основных этапов:

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

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

3. Запуск команды пункта меню Сервис ® Поиск решения.

4. Ввод зависимостей из математической модели в диалоговое окно Поиска решений:

4.1. Назначение ячейки с целевой функцией.

4.2. Назначение ячеек для искомого результата.

4.3. Ввод ограничений и граничных условий.

5. Настройка параметров для решения задачи

6. Выполнение решения.

7. Формирование отчетов и анализ полученных результатов

Последовательность решения продемонстрирована примере Задачи линейного программирования, исходные данные которой и математическая модель приведены в ОБРАЗЦЕ (рис. 1).

1 этап. Создание формы для ввода исходных данных задачи линейного программирования

Форма для размещения исходных данных на листе Excel для решения ЗЛП может иметь вид как на рис. 2.

Весь текст на рис.2 является комментарием и на решение задачи не влияет.

2 этап. Ввод исходных данных задачи линейного программирования

Исходные данные и зависимости из математической модели вносят в подготовленную на 1 этапе форму (рис.2).

Ввод исходных значений из условия задачи.

1.1. В ячейки B3:C3 вводят значения цен единицы продукции каждого вида.

1.2. Ячейки с искомым результатом В4:С4 заполнять необязательно, хотя можно первоначально занести произвольные значения, например, 1.

1.3. В ячейки B7:C10 вводят матрицу норм расхода времени каждого типа оборудования на выпуск единицы каждого вида продукции.

1.4. В ячейки F7:F10 вводят значения фондов рабочего времени для каждого типа оборудования.

1.5. В ячейки E7:E10 заносят знак ограничения на фонды рабочего времени в виде <= .

Ввод зависимостей согласно математической модели задачи (рис. 24).

2.1. В ячейку D4 вводят формулу для целевой функции в одном из двух видов:

= B3 * B4 + C3 * C4либо = СУММПРОИЗВ(B3:C3;B4:C4)

Функция СУММПРОИЗВ(массив1; массив2; массив3;…) вычисляет сумму попарных произведений эементов массивов, указанных в параметрах функции. СУММПРОИЗВ( ) находится в Мастере функций в категории Математические.

Для ее ввода необходимо:

· в массив1 ввести диапазон B3:C3.

· в массив2 ввести диапазон B4:C4.

· нажать экранную кнопку ОК

2.2. В ячейку D7 вводят формулу для первого ограничения в одном из двух видов:

= B7 * B4 + C7 * C4либо = СУММПРОИЗВ(B7:C7;B4:C4)

2.3. Для дальнейшего копирования формулы из ячейки D7 вводят в формулу абсолютную адресацию для ячеек B4:C4:

= B7 * $B$4 + C7 * $C$4либо = СУММПРОИЗВ(B7:C7;$B$4:$C$4)

2.4. Копируют формулу из ячейки D7 в диапазон ячеек D8:D10.

После 2 этапа таблица для решения ЗЛП в Excel в режиме отображения формул будет иметь вид как на рис. 3.

3 этап. Выполнение команды пункта меню Сервис ® Поиск решения.

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

Замечание. Описание назначения рабочих полей и экранных кнопок диалогового окна «Поиск решения» приведено в Приложении к инструкции для решения в Excel ЗЛП.

4 этап. Ввод зависимостей из математической модели в диалоговое окно Поиска решений.

1. Назначение ячейки с целевой функцией.

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

· Мышью указывают ячейку: D4.

· Выбирают направление целевой функции:

Равной:· Максимальному значению.

Назначение ячеек для искомого результата.

· Курсор помещают в поле Изменяя ячейки.

· Мышью указывают диапазон ячеек: $B$4:$C$4.

12
• Далее ⇒