Автор работы: Пользователь скрыл имя, 01 Июня 2013 в 06:05, курсовая работа
В данной работе будет рассматриваться решение транспортной задачи с помощью построения математической модели и поиска оптимального решения с помощью Excel в надстройки Поиск решения. А также технология решения задачи, анализ решения.
Целью данной работы является на примере данной задачи научиться рассчитывать оптимизацию планов перевозок: рассчитывать наилучший вариант, где меньше всего издержек, а также наихудший, где издержки максимальны. Для реализации поставленной цели нужно решить задачу оптимизации для двух случаев, проанализировать и вывести отчеты.
ВВЕДЕНИЕ………………………………………………………………....3
ГЛАВА 1. ПОСТАНОВКА ЗАДАЧИ……………………………………4
1.1 Поставленная задача…………………………………………………....4
1.2 Математическая модель решения задачи…………………………......6
1.3 Краткая характеристика Excel………………………………………....8
ГЛАВА 2. ТЕХНОЛОГИЯ РЕШЕНИЯ ЗАДАЧИ……………………11
2.1 Ввод в Excel……………………………………………………………11
2.2. Поиск решения задачи………………………………………………..14
2.3 Отчеты………………………………………………………………….16
3. АНАЛИЗ РЕШЕНИЯ ЗАДАЧИ………………………………………22
3.1 Выводы по решению задачи…………………………………………...22
ЗАКЛЮЧЕНИЕ…………………………………………………………...23
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ……………………24
Ввод данных в окно Поиск решения производим в соответствии с
Рис. 2.2.1
Рис. 2.2.1 Ввод данных в окно Поиска решения для транспортной задачи
Полученное оптимальное решение представлено на рис. 2.2.2
Рис. 2.2.2 Оптимальное решение для транспортной задачи
Минимальная
общая стоимость перевозок
После изменения условий получаем другое оптимальное решение. Чтобы проверить, насколько полученный при оптимизации план лучше, чем другие возможные планы, поищем план, приносящий максимум издержек. Для этого нужно будет модифицировать таблицу цен. Ведь мы ставили большую цену перевозки для запрещения некоторых маршрутов, а при поиске максимума такое запрещение можно реализовать, только поставив низкую цену. Таким образом, получаем наихудший план перевозок.
Рис. 2.2.3 Наихудший план перевозок путем максимизации издержек
Наихудший план перевозок (16895 т. р.) - план, приносящий максимум издержек. Для этого нужно будет модифицировать таблицу цен. Ставим меньшую цену перевозки для запрещения некоторых маршрутов.
2.3 Отчеты
Отчет по результатам
В первой таблице приведено исходное и оптимальное содержимое ячейки, в которой записано значение ЦФ. Во второй значения искомых оптимизируемых переменных. Последняя содержит результаты оптимального решения для ограничений и граничных условий. Используется для простой оценки результата и не дает дополнительной информации.
Таблица 2.3.1
Целевая ячейка (Минимум) |
||||
Ячейка |
Имя |
Исходное значение |
Результат | |
$K$9 |
Заказы Запасы |
0 |
15690,00007 | |
Изменяемые ячейки |
||||
Ячейка |
Имя |
Исходное значение |
Результат | |
$C$12 |
A A |
0 |
0 | |
$D$12 |
A B |
0 |
0 | |
$E$12 |
A A |
0 |
0 | |
$F$12 |
A B |
0 |
0 | |
$G$12 |
A A |
0 |
13 | |
$H$12 |
A B |
0 |
0 | |
$I$12 |
A A |
0 |
21 | |
$J$12 |
A B |
0 |
0 | |
$C$13 |
B A |
0 |
0 | |
$D$13 |
B B |
0 |
0 | |
$E$13 |
B A |
0 |
0 | |
$F$13 |
B B |
0 |
0 | |
$G$13 |
B A |
0 |
0 | |
$H$13 |
B B |
0 |
0 | |
$I$13 |
B A |
0 |
0 | |
$J$13 |
B B |
0 |
47 | |
$C$14 |
A A |
0 |
12 | |
$D$14 |
A B |
0 |
0 | |
$E$14 |
A A |
0 |
0 | |
$F$14 |
A B |
0 |
0 | |
$G$14 |
A A |
0 |
0 | |
$H$14 |
A B |
0 |
0 | |
$I$14 |
A A |
0 |
7,999999999 | |
$J$14 |
A B |
0 |
6,75016E-10 | |
$C$15 |
B A |
0 |
0 | |
$D$15 |
B B |
0 |
31 | |
$E$15 |
B A |
0 |
0 | |
$F$15 |
B B |
0 |
19 | |
$G$15 |
B A |
0 |
0 | |
$H$15 |
B B |
0 |
0 | |
$I$15 |
B A |
0 |
0 | |
$J$15 |
B B |
0 |
0 | |
$C$16 |
A A |
0 |
0 | |
$D$16 |
A B |
0 |
0 | |
$E$16 |
A A |
0 |
0 | |
$F$16 |
A B |
0 |
0 | |
$G$16 |
A A |
0 |
0 | |
$H$16 |
A B |
0 |
0 | |
$I$16 |
A A |
0 |
0 | |
$J$16 |
A B |
0 |
0 | |
$C$17 |
B A |
0 |
0 | |
$D$17 |
B B |
0 |
5,000000001 | |
$E$17 |
B A |
0 |
0 | |
$F$17 |
B B |
0 |
0 | |
$G$17 |
B A |
0 |
0 | |
$H$17 |
B B |
0 |
37 | |
$I$17 |
B A |
0 |
0 | |
$J$17 |
B B |
0 |
2,999999999 | |
Ограничения |
||||||
Ячейка |
Имя |
Значение |
Формула |
Статус |
Разница | |
$C$18 |
Заказы A |
2,7967E-11 |
$C$18=0 |
не связан. |
0 | |
$D$18 |
Заказы B |
-1,19508E-09 |
$D$18=0 |
не связан. |
0 | |
$E$18 |
Заказы A |
0 |
$E$18=0 |
не связан. |
0 | |
$F$18 |
Заказы B |
4,4281E-11 |
$F$18=0 |
не связан. |
0 | |
$G$18 |
Заказы A |
3,02975E-11 |
$G$18=0 |
не связан. |
0 | |
$H$18 |
Заказы B |
-1,22827E-09 |
$H$18=0 |
не связан. |
0 | |
$I$18 |
Заказы A |
-1,81535E-09 |
$I$18=0 |
не связан. |
0 | |
$J$18 |
Заказы B |
-1,65983E-09 |
$J$18=0 |
не связан. |
0 | |
$K$12 |
A Запасы |
-1,12868E-09 |
$K$12=0 |
не связан. |
0 | |
$K$13 |
B Запасы |
-1,56024E-09 |
$K$13=0 |
не связан. |
0 | |
$K$14 |
A Запасы |
4,66116E-11 |
$K$14=0 |
не связан. |
0 | |
$K$15 |
B Запасы |
-1,65983E-09 |
$K$15=0 |
не связан. |
0 | |
$K$16 |
A Запасы |
0 |
$K$16=0 |
не связан. |
0 | |
$K$17 |
B Запасы |
-1,49385E-09 |
$K$17=0 |
не связан. |
0 | |
Отчет по устойчивости
Содержит информацию об изменяемых переменных и ограничениях. Позволяет оценить насколько модель подвержена изменениям. Некоторые ограничения связные.
Таблица 2.3.2
Изменяемые ячейки |
|||||||
Результ. |
Нормир. |
Целевой |
Допустимое |
Допустимое | |||
Ячейка |
Имя |
значение |
стоимость |
Коэффициент |
Увеличение |
Уменьшение | |
$C$12 |
A A |
0 |
5 |
45 |
1E+30 |
5 | |
$D$12 |
A B |
0 |
10 |
99999 |
1E+30 |
10 | |
$E$12 |
A A |
0 |
0 |
50 |
5,000000014 |
1E+30 | |
$F$12 |
A B |
0 |
20 |
99999 |
1E+30 |
20 | |
$G$12 |
A A |
13 |
0 |
40 |
0 |
1E+30 | |
$H$12 |
A B |
0 |
15 |
99999 |
1E+30 |
15 | |
$I$12 |
A A |
21 |
0 |
50 |
5 |
0 | |
$J$12 |
A B |
0 |
5 |
99999 |
1E+30 |
5 | |
$C$13 |
B A |
0 |
199853 |
99999 |
1E+30 |
199853 | |
$D$13 |
B B |
0 |
5 |
100 |
1E+30 |
5 | |
$E$13 |
B A |
0 |
199843 |
99999 |
1E+30 |
199843 | |
$F$13 |
B B |
0 |
5 |
90 |
1E+30 |
5 | |
$G$13 |
B A |
0 |
199853 |
99999 |
1E+30 |
199853 | |
$H$13 |
B B |
0 |
15 |
105 |
1E+30 |
15 | |
$I$13 |
B A |
0 |
199843 |
99999 |
1E+30 |
199843 | |
$J$13 |
B B |
47 |
0 |
100 |
5 |
1E+30 | |
$C$14 |
A A |
12 |
0 |
45 |
5 |
1E+30 | |
$D$14 |
A B |
0 |
5 |
99999 |
1E+30 |
5 | |
$E$14 |
A A |
0 |
5,000000016 |
60 |
1E+30 |
5,000000016 | |
$F$14 |
A B |
0 |
15 |
99999 |
1E+30 |
15 | |
$G$14 |
A A |
0 |
10 |
55 |
1E+30 |
10 | |
$H$14 |
A B |
0 |
10 |
99999 |
1E+30 |
10 | |
$I$14 |
A A |
7,999999999 |
0 |
55 |
5,000000016 |
4,999971593 | |
$J$14 |
A B |
6,75016E-10 |
0 |
99999 |
4,999971593 |
199843 | |
$C$15 |
B A |
0 |
199853 |
99999 |
1E+30 |
199853 | |
$D$15 |
B B |
31 |
0 |
95 |
0 |
5 | |
$E$15 |
B A |
0 |
199843 |
99999 |
1E+30 |
199843 | |
$F$15 |
B B |
19 |
0 |
85 |
5 |
1E+30 | |
$G$15 |
B A |
0 |
199853 |
99999 |
1E+30 |
199853 | |
$H$15 |
B B |
0 |
20 |
110 |
1E+30 |
20 | |
$I$15 |
B A |
0 |
199843 |
99999 |
1E+30 |
199843 | |
$J$15 |
B B |
0 |
0 |
100 |
1E+30 |
0 | |
$C$16 |
A A |
0 |
20 |
60 |
1E+30 |
20 | |
$D$16 |
A B |
0 |
9,999971596 |
99999 |
1E+30 |
9,999971596 | |
$E$16 |
A A |
0 |
5,000000014 |
55 |
1E+30 |
5,000000014 | |
$F$16 |
A B |
0 |
20,00000355 |
99999 |
1E+30 |
20,00000355 | |
$G$16 |
A A |
0 |
0 |
40 |
1E+30 |
0 | |
$H$16 |
A B |
0 |
14,9999716 |
99999 |
1E+30 |
14,9999716 | |
$I$16 |
A A |
0 |
0 |
50 |
0 |
1E+30 | |
$J$16 |
A B |
0 |
4,999971594 |
99999 |
1E+30 |
4,999971594 | |
$C$17 |
B A |
0 |
199858 |
99999 |
1E+30 |
199858 | |
$D$17 |
B B |
5,000000001 |
0 |
90 |
5 |
0 | |
$E$17 |
B A |
0 |
199848 |
99999 |
1E+30 |
199848 | |
$F$17 |
B B |
0 |
5 |
85 |
1E+30 |
5 | |
$G$17 |
B A |
0 |
199858 |
99999 |
1E+30 |
199858 | |
$H$17 |
B B |
37 |
0 |
85 |
10 |
1E+30 | |
$I$17 |
B A |
0 |
199848 |
99999 |
1E+30 |
199848 | |
$J$17 |
B B |
2,999999999 |
0 |
95 |
0 |
5 | |
Ограничения |
|||||||
Результ. |
Теневая |
Ограничение |
Допустимое |
Допустимое | |||
Ячейка |
Имя |
значение |
Цена |
Правая часть |
Увеличение |
Уменьшение | |
$C$18 |
Заказы A |
2,7967E-11 |
-10 |
0 |
7,999999999 |
0 | |
$D$18 |
Заказы B |
-1,19508E-09 |
99939 |
0 |
2,999999999 |
0 | |
$E$18 |
Заказы A |
0 |
0 |
0 |
20,99999999 |
0 | |
$F$18 |
Заказы B |
4,4281E-11 |
99929 |
0 |
2,999999999 |
0 | |
$G$18 |
Заказы A |
3,02975E-11 |
-10 |
0 |
21 |
0 | |
$H$18 |
Заказы B |
-1,22827E-09 |
99934 |
0 |
2,999999999 |
0 | |
$I$18 |
Заказы A |
-1,81535E-09 |
0 |
0 |
0 |
1E+30 | |
$J$18 |
Заказы B |
-1,65983E-09 |
99944 |
0 |
8 |
0 | |
$K$12 |
A Запасы |
-1,12868E-09 |
50 |
0 |
0 |
21 | |
$K$13 |
B Запасы |
-1,56024E-09 |
-99844 |
0 |
0 |
8 | |
$K$14 |
A Запасы |
4,66116E-11 |
55 |
0 |
0 |
7,999999999 | |
$K$15 |
B Запасы |
-1,65983E-09 |
-99844 |
0 |
0 |
2,999999999 | |
$K$16 |
A Запасы |
0 |
50 |
0 |
0 |
0 | |
$K$17 |
B Запасы |
-1,49385E-09 |
-99849 |
0 |
0 |
2,999999999 | |
Отчет по пределам
Содержит максимальное значение ЦФ и оптимальные значения независимых переменных. Независимые переменные будут принимать свои предельные (верхние или нижние) значения, можно увидеть как изменится ЦФ.
Таблица 2.3.3
Целевое |
||||||||
Ячейка |
Имя |
Значение |
||||||
$K$9 |
Заказы Запасы |
15690,00007 |
||||||
Изменяемое |
Нижний |
Целевой |
Верхний |
Целевой | ||||
Ячейка |
Имя |
Значение |
предел |
результат |
предел |
результат | ||
$C$12 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$D$12 |
A B |
0 |
1,12868E-09 |
15690,00018 |
1,12868E-09 |
15690,00018 | ||
$E$12 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$F$12 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$G$12 |
A A |
13 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$H$12 |
A B |
0 |
1,12868E-09 |
15690,00018 |
1,12868E-09 |
15690,00018 | ||
$I$12 |
A A |
21 |
21 |
15690,00007 |
21 |
15690,00007 | ||
$J$12 |
A B |
0 |
1,12868E-09 |
15690,00018 |
1,12868E-09 |
15690,00018 | ||
$C$13 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$D$13 |
B B |
0 |
1,19508E-09 |
15690,00007 |
1,19508E-09 |
15690,00007 | ||
$E$13 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$F$13 |
B B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$G$13 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$H$13 |
B B |
0 |
1,22827E-09 |
15690,00007 |
1,22827E-09 |
15690,00007 | ||
$I$13 |
B A |
0 |
1,56024E-09 |
15690,00022 |
1,56024E-09 |
15690,00022 | ||
$J$13 |
B B |
47 |
47 |
15690,00007 |
47 |
15690,00007 | ||
$C$14 |
A A |
12 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$D$14 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$E$14 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$F$14 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$G$14 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$H$14 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$I$14 |
A A |
7,999999999 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$J$14 |
A B |
6,75016E-10 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$C$15 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$D$15 |
B B |
31 |
31 |
15690,00007 |
31 |
15690,00007 | ||
$E$15 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$F$15 |
B B |
19 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$G$15 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$H$15 |
B B |
0 |
1,22827E-09 |
15690,00007 |
1,22827E-09 |
15690,00007 | ||
$I$15 |
B A |
0 |
1,65983E-09 |
15690,00023 |
1,65983E-09 |
15690,00023 | ||
$J$15 |
B B |
0 |
1,65983E-09 |
15690,00007 |
1,65983E-09 |
15690,00007 | ||
$C$16 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$D$16 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$E$16 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$F$16 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$G$16 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$H$16 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$I$16 |
A A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$J$16 |
A B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$C$17 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$D$17 |
B B |
5,000000001 |
5,000000002 |
15690,00007 |
5,000000002 |
15690,00007 | ||
$E$17 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$F$17 |
B B |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$G$17 |
B A |
0 |
#Н/Д |
#Н/Д |
#Н/Д |
#Н/Д | ||
$H$17 |
B B |
37 |
37 |
15690,00007 |
37 |
15690,00007 | ||
$I$17 |
B A |
0 |
1,49385E-09 |
15690,00022 |
1,49385E-09 |
15690,00022 | ||
$J$17 |
B B |
2,999999999 |
3,000000001 |
15690,00007 |
3,000000001 |
15690,00007 |
В моем случае задача является закрытой, сбалансированной т.к. суммарный объем груза, имеющегося у поставщика равен суммарному спросу потребителей и выполняется равенство:
Минимальная
общая стоимость перевозок
ЗАКЛЮЧЕНИЕ
Транспортная задача решена с помощью построения математической модели и поиска оптимального решения с помощью Excel в надстройки Поиск решения и программных методов решения с помощью линейного программирования. Были найдены минимальные издержки по перевозкам грузов, а также был выявлен наихудший план перевозок.
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ
1)