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

Страницы работы

Содержание работы

Министерство образования и науки РФ

Новосибирский Государственный Технический Университет

(НГТУ)

Отчет

по лабораторной работе №3

«Сценарии и анализ данных».

Выполнил: студент гр. ВИ-61

Данилов А.С.

Проверила: Лыгина Н.И.

Новосибирск – 2009 год.

Часть I.

Решение задач линейной оптимизации с помощью инструмента «Поиск решения».

Дано: Завод выпускает 3 вида автомобилей, прибыль от автомобиля каждого вида в рублях составляет соответственно 100, 200, 70 рублей, затраты на материалы для производства одного автомобиля 100,100, 50 руб., на заработную плату – 40, 30, 35 руб., прибыль – 100, 200, 70 руб., а расходы – 20, 15, 22 руб. Определить объем выпуска автомобиля каждого вида, обеспечивающих минимум расхода электроэнергии при условии, что на покупку расходных материалов выделено 33000 руб., на зарплату – 12000 руб., а прибыль должна составить не менее 53000 руб.

Решение: Составим математическую модель. Обозначим: хi – количество автомобилей i-го типа. Расходы на электроэнергию равны 20*х1+15*х2+22*х3. Её нужно минимизировать. Функция, для которой ищется экстремум (максимум или минимум) носит название целевой функции. Беспредельному увеличению количества автомобилей препятствуют ограничения. Ограничена сумма денег, которую выделили на материалы и заработную плату, отсюда два неравенства: 100*20*х1+100*15*х2+50*22*х3<=33000 и 400*х1+300*х2+350*х3<=12000. Кроме того, количество автомобилей не может быть отрицательным, поэтому хi >=0.

Как известно из курса ИО, формально задача оптимизации записывается так:

20*х1+15*х2+22*х3 -> min

100*20*х1+100*15*х2+50*22*х3<=32000

40*х1+30*х2+35*х3<=11000

100*х1+200*х2+70*х3<=53000

хi >=0.

Теперь решим эту задачу в EXCEL.

Пользуясь данными, сначала создадим таблицу:

Рис.1. Таблица для вычисления оптимального количества автомобилей

В столбцах C, D, E, F содержатся константы: расход на материалы, расход на зарплату и т.д. для каждого вида автомобилей. Требуется определить количество автомобилей каждой марки (ячейки G3:G5) и минимизировать расходы на электроэнергию.

Ячейка C9 – это целевая ячейка. В неё записывается целевая функция. Это: =СУММ(ПРОИЗВЕД(F3;G3);ПРОИЗВЕД(F4;G4);ПРОИЗВЕД(F5;G5))

В ячейках C11:C13 записываем левые части ограничений, а правые части ограничений пишем в соседние ячейки:

B11 - =СУММ(ПРОИЗВЕД(100;G3);ПРОИЗВЕД(100;G4);ПРОИЗВЕД(50;G5))

B12 - =СУММ(ПРОИЗВЕД(40;G3);ПРОИЗВЕД(30;G4);ПРОИЗВЕД(35;G5))

B13 - =СУММ(ПРОИЗВЕД(100;G3);ПРОИЗВЕД(200;G4);ПРОИЗВЕД(70;G5))

Выделяем ячейку C9 с целевой функцией. Далее открываем меню СЕРВИС/ПОИСК РЕШЕНИЯ.

Рис. 2. Инструмент ПОИСК РЕШЕНИЯ.

Нажимаем на кнопку «Добавить».

Рис. 3. Ограничения.

Аналогичным способом добавляем и другие ограничения.

Окончательный результат см. ниже:

Рис.4 Результаты работы  инструмента ПОИСК РЕШЕНИЯ

Как видно, расходы на электроэнергию минимальны. Они равны 3975 р. Количество автомобилей – только 2 тип, 265 шт., поскольку на производство автомобилей этой марки нужно меньше всего электроэнергии.

Согласитесь, это не очень хорошо – производить автомобили только одного вида. Но это уже определяется условиями задачи, минимум энергии, значит минимум энергии. Насчет разнообразия автомобилей в условии задач ничего не было. Разумеется, можно поставить ограничения, чтобы две остальные ячейки были больше нуля, но глупая программа посчитает опять же по минимуму энергии. 

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

Рис. 5-6. Сохранение сценария.

Приступаем ко второму этапу работы. Будем смотреть, что произойдет, если цена на электроэнергию поднимется с 15 р. До 40р.

Для этого добавляем новый сценарий. Диспетчер сценариев вызывается меню СЕРВИС/СЦЕНАРИИ…

Рис. 7. Диспетчер сценариев.

Пишем название сценария. И выбираем изменяемые ячейки. В нашем случае это ячейка F4.

Рис. 8. Задание имени сценария.

Рис. 9. Изменение значений ячеек сценария

В ячейке F4 пишем вместо»15» - «40». Всё, готово. Далее включаем сценарий. Получится то, что нарисовано ниже.

Рис. 10. Результат работы сценария «С учетом изменения цены на электроэнергию».

Далее, вызываем меню сценариев и нажимаем на кнопку «Отчет».

Рис. 11-13. Создание отчета.

Вообще-то, заголовки строк можно поменять, чтобы было яснее и нагляднее.

Рис.14. Заключительный вариант отчета.

Рис. 15. Диаграмма.

Напоследок выводим диаграмму для наглядности. Следует обратить внимание на что, что нужно выбирать диапазон ячеек – Только то, что находится ниже надписи «Результат». Все остальное нам не нужно. И еще нужно удалить столбец «Текущие значения».

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

Часть II.

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

Даны нелинейные уравнения:

Похожие материалы

Информация о работе

Предмет:
Моделирование
Тип:
Отчеты по лабораторным работам
Размер файла:
481 Kb
Скачали:
0