Файл: Лабораторные работы.docx

Скачать файл (2,53Мб)

Заказать решение

ВУЗ: Не указан

Категория: Не указан

Дисциплина: Не указана

Добавлен: 04.12.2023

Просмотров: 61

Скачиваний: 1

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

Рис. 9 «Результаты поиска решения» с кнопкой «Сохранить сценарий»
3.2 После этого выполнения получаем значения (рис. 10.1―10.5), потом на вкладках: «Анализ “что – если”» и «Диспетчер сценариев» (рис. 11), выбираем: «Отчёт», следом – «ОК» (рис. 12), в итоге получаем таблицу «Структура сценариев» (рис. 13).

Рис. 10.1 Результат изменения, при введённом расчёте значении: С=100 и имени его сценария

Рис. 10.2 Результат изменения, при введённом расчёте значении: С=200 и имени его сценария

Рис. 10.3 Результат изменения, при введённом расчёте значении: С=300 и имени его сценария

Рис. 10.4 Результат изменения, при введённом расчёте значении: С=400 и имени его сценария

Рис. 10.5 Результат изменения, при введённом расчёте значении: С=500 и имени его сценария

Рис. 11 Вкладки: «Анализ “что – если”», «Диспетчер сценариев»

Рис. 12 Вкладки: «Диспетчер сценариев», «Отчёт по сценарию»

Рис. 13 Таблица «Структура сценария»
3.3 Для удобства работы изменим таблицу (рис. 13) на более простой вид (рис. 14)

Рис. 14 Таблица «Итоговый сценарий»
3.4 Построение графика с маркерами «Параметрирование по стоимости» (рис. 15.1―15.3)

Рис. 15.1 Создание вертикальных осей: «Сторона» − на левой оси, а «Объём» – на правой

Рис. 15.2 Создание горизонтальной, вспомогательной оси «Стоимость»

Рис. 15.3 График с маркером «Параметрирование по стоимости», со значениями «Сторона» и «Объём»

Лабораторная работа № 8
Линейное программирование с помощью Excel
Цель работы: Понять, как работает линейное программирование, распределяя ресурсы.

Задание на выполнение работы: создать таблицы: график продукций(финансов) и графики «Итоговый сценарий финансов» (продукция, прибыль, трудовые, сырьё и финансы), с использованием программы Excel.

Порядок выполнения работы:

1. Распределение ресурсов

1.1 Создаём таблицу (рис. 1), в ячейки которой вставляем (F6, F9, F10, F11) формулу (СУММПРОИЗВ).

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

Рис. 2 Таблица нормы расходов и прибыли, получаемых от реализации единицы каждого типа продукции

1.3 На представленном рис. 3 работает формула СУММПРОИЗВ, где первая часть значений не изменена (B3:E3), для этого мы и пишем $, а данные второй части для коэффициента ЦФ и трёх видов – меняются.

Рис. 3 Готовая таблица с ссылками функции «СУММПРОИЗВ»
1.4 Формулу (СУММПРОИЗВ) можно ввести самостоятельно, но можно её и вставить. Для этого нужно перейти на вкладку «Формулы», затем на «Вставить функцию». В категории ищем «Математические» формулы, выскакивает окно, представленное на рис. 4.

Рис. 4 Аргументация функции «СУММПРОИЗВ»

2. Работа в диалоговом окне «Поиск решения»

2.1 Для того, чтобы работать с параметрами «Поиска решения», сначала заходим во вкладку «Данные», кликаем на «Поиск решения» (рис. 5).

Рис. 5 Значок «Поиска решения», диалогового окна
2.2 Заходим в диалоговое окно, оптимизируем целевую ячейку $F$6(адрес значения ЦФ), вводим зависимость для целевой функции, которая максимизируется, потом добавляем значения переменных $B$3:$E$3, отмечаем кнопку «Сделать переменные без ограничений неотрицательными» и пишем значения из таблицы (рис. 3) в окно «В соответствии с ограничениями», с помощью кнопки «Добавить» или «Добавление ограничения» (рис. 6, 7).

Рис. 6 «Параметры поиска решения»

Рис. 7 Добавление ограничения (значения из таблицы)

2.3 После того, как всё введено в «Параметры поиска решения» (рис. 6), заходим в «Параметры», где отмечаем те кнопки, которые выделены на рис. 8. После этого нажимаем «ОК» и «Найти решение» (рис. 9). Получили результаты в таблице и построили гистограмму по полученным значениям (рис. 10).

Рис. 8 Параметры «Поиска решения»

Рис. 9 Результаты «Поиска решения»

Рис. 10 Таблица результатов после «Поиска решения» и гистограмма
3. Изменение условий задачи

3.1 Меняем в «Параметре Поиска решения»: B3 = 10, C3 = 5 и D3 = 6 (рис. 11, 12).

Рис. 11 Изменённые «Параметры поиска решения»

Рис. 12 Таблица результатов изменения

3.2 Строим таблицу (рис. 12), в которой добавляем дополнительные необходимые ресурсы: t1, t2, t3 равные (-1) и «Прибыль»; вводим зависимость для новой целевой функции, которая минимизируется. Ячейки I4, I6, I9, I10 и I11 записываем по формуле (рис. 3, 4), получаем результат в таблицах (рис. 13.1, 13.2).

Рис. 13.1 Новая таблица после изменения «Поиска решения»

Рис. 13.2 Результаты формулы СУММПРОИЗ

4. Преодоление несовместимости: Добавляем значения с помощью «Параметров поиска решения» (Из пункта 2) (рис. 14, 15).

Рис. 14 «Параметры поиска решения» с новыми переменными

Рис. 15 Результат изменения в таблице

5. Устранение неограниченности целевой функции: Эта система неравенств имеет неограниченную целую функцию:

Посмотрим, как она будет себя вести в Excel. Создаём таблицу под эти условия неравенств. Получили диалоговое окно «Результаты поиска решения» (рис. 9), которое является признаком неограниченности целевой функции. Для того, чтобы преодолеть неограниченность добавляем ограничение:

и получаем такое неравенство:

Все эти решения можно увидеть на двух рисунках (рис. 16.1, 16.2).

Рис. 16.1 Таблица для решения неограниченности целевой функции

Рис. 16.2 Конечная таблица для решения неограниченности целевой функции

6. Параметрический анализ

6.1 Построим таблицу для пяти вариантов финансов, которые будем менять в «Правой части» таблицы (P11) (рис. 17).

Рис. 17 Таблицы финансов
6.2 Настраиваем «Параметры поиск решения» (рис. 18), в «Правой части» таблицы будем добавлять значения каждого из вариантов финансов в таблицу (P11).

Рис. 18 «Параметры поиск решения»
6.3 После вылета окна «Результаты поиска решения», нажимаем на кнопку «Сохранить сценарий», где мы вводим имя нового сценария, например, Финансы=50, и далее для каждого варианта (рис. 19.1―19.5).