Министерство образования и науки РФ
Новосибирский Государственный Технический Университет
(НГТУ)
Отчет
по лабораторной работе №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.
Решение нелинейных уравнений графическим методом и с помощью инструментов «Поиск решения» и «Подбор параметра».
Даны нелинейные уравнения:
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.
