Задание 1. Некоторое предприятие производит четыре вида продукции А, В, С, D, используя для этого три вида ресурсов.

Лабораторная работа № 4

Поддержка процесса принятия решений средствами MS Excel. Работа со средствами "Поиск решения", "Диспетчер сценариев".

Цель: изучение возможностей средств "Поиск решения", "Диспетчер сценариев" в MS Excel для моделирования СППР.

Ход работы:

1. Изучить рекомендации для пользования инструментами анализа данных MS Excel

2. Проанализировать возможности их применения как средств моделирования в составе СППР.

3. Выполнить задание согласно варианту (табл. 2, 3)

4. Оформить отчет по лабораторной работе, включающий выводы относительно возможностей использования средств анализа данных MS Excel в СППР, описание примеров, иллюстративный материал.

Рекомендации по использованию средства анализа данных MS Excel "Поиск решения"

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

Для вызова процедуры поиска решения необходимо выбрать команду Данные / Поиск решения после чего откроется диалоговое окно "Поиск решения" (рис. 1):

Рис. 1. Диалоговое окно "Поиск решения"

Элементы диалогового окна "Поиск решения":

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

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

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

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

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

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

Задание 1. Некоторое предприятие производит четыре вида продукции А, В, С, D, используя для этого три вида ресурсов.

Таблица 1.

Нормы затрат ресурсов на производство

Нормы затрат ресурсов на производство каждого вида продукции (в условных единицах) приведены в таблице 1.

Какой вид продукции и сколько ее единиц нужно выпускать, чтобы прибыль была максимальной?

Условие задачи представлена в таблице 2. Следующие показатели остаются неизменны:

• Прибыль на един. товара, руб;

• Объем ресурсов.

Вариант выбирается согласно номера студента по списку в журнале.

Таблица 2.

Варианты заданий

Пример решения. обозначим:

Х1 - количество произведенной продукции А;

Х2 - количество произведенной продукции В;

Х3 - количество произведенной продукции С;

Х4 - количество произведенной продукции D;

F - целевая функция, величина максимальной прибыли.

Построим математическую модель задачи (1):

F = 35 Х₁ + 30 Х₂ + 48 Х₃ + 46 Х₄

(1)

Ход решения задачи в MS Excel

1. На рабочем листе создать таблицу как показано на рис. 2.

2. В ячейки В9 - В12 записываем:

· В9: =B3*B$8+C3*C$8+D3*D$8+E3*E$8;

· В10: =B4*B$8+C4*C$8+D4*D$8+E4*E$8;

· В11: =B5*B$8+C5*C$8+D5*D$8+E5*E$8;

· В12: =B6*B8+C6*C8+D6*D8+E6*E8.

Данные формулы соответствуют математической модели задачи (1).

1. выполняем команду Данные / Поиск решения и заполняет диалоговое окно "Поиск решения" как показано на рис 3.

Рис 2. Таблица для заполнения в MS Excel.

Рис. 3. Пример диалогового окна "Поиск решения"

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

1. После выполнения данных шагов таблица на рабочем листе MS Excel будет выглядеть как показано на рис. 4:

Рис 4. Таблица в MS Excel после выполнения расчетов.

Как видно из рис. 3 Максимальный доход составляет 751,33 руб. при условии, что предприятие произведет 17 единиц продукции вида D