- •Microsoft Excel: краткая информация о программе
- •Практическое занятие № 1 Анализ рядов данных. Вычисление линии тренда для случаев однофакторной и многофакторной зависимостей.
- •Задачи для самостоятельного решения
- •Практическое занятие № 2 Решение классической транспортной задачи с помощью Excel
- •Задачи для самостоятельного решения
- •Практическое занятие № 3 Решение многоэтапной транспортной задачи с помощью Excel
- •Задачи для самостоятельного решения
- •Практическое занятие № 4 Решение транспортной задачи с выбором вида транспорта с помощью Excel
- •Задачи для самостоятельного решения
- •Практическое занятие № 5 Решение задачи о назначениях с помощью Excel
- •Задачи для самостоятельного решения
Задачи для самостоятельного решения
Требуется найти оптимальный план транспортировки груза при заданных условиях по критерию «минимум суммарных транспортных затрат».
Задача 1.
Отправители |
Получатели |
Запасы груза, т | ||||||||
Ж |
З |
И |
К |
Л |
М |
Н |
О | |||
1 |
2 |
3 |
4 |
5 |
6 |
|
| |||
А |
1 |
12 |
14 |
6 |
9 |
11 |
16 |
9 |
15 |
200 |
Б |
2 |
14 |
15 |
18 |
12 |
20 |
2 |
2 |
10 |
135 |
В |
3 |
3 |
9 |
6 |
10 |
12 |
19 |
15 |
12 |
110 |
Г |
4 |
24 |
22 |
19 |
16 |
12 |
11 |
16 |
18 |
55 |
Д |
5 |
13 |
7 |
9 |
11 |
6 |
8 |
7 |
8 |
60 |
Е |
6 |
15 |
17 |
11 |
14 |
9 |
12 |
6 |
10 |
140 |
Потребности в грузе, т |
100 |
50 |
70 |
85 |
90 |
120 |
55 |
30 |
|
Задача 2.
Отправители |
Получатели |
Запасы груза, т | ||||||||
Ж |
З |
И |
К |
Л |
М |
Н |
О | |||
1 |
2 |
3 |
4 |
5 |
6 |
|
| |||
А |
1 |
20 |
24 |
33 |
41 |
35 |
29 |
22 |
30 |
110 |
Б |
2 |
25 |
30 |
29 |
26 |
35 |
38 |
34 |
33 |
150 |
В |
3 |
27 |
31 |
27 |
29 |
35 |
37 |
33 |
38 |
140 |
Г |
4 |
27 |
28 |
22 |
25 |
26 |
29 |
37 |
36 |
35 |
Д |
5 |
26 |
28 |
30 |
33 |
35 |
37 |
28 |
29 |
150 |
Е |
6 |
30 |
26 |
37 |
29 |
20 |
22 |
25 |
27 |
230 |
Потребности в грузе, т |
50 |
150 |
180 |
75 |
90 |
135 |
200 |
170 |
|
Примечание. Если задача не сбалансирована, то поиск решения следует проводить используя знаки неравенства в ограничениях.
В случае, если спрос превышает предложение – все запасы будут вывезены (в этой группе ограничений ставится знак «=»), а спрос не полностью удовлетворен (ставится знак «≤»).
В противоположной ситуации знаки ставятся соответственно «≤» - для запасов (так как не полностью вывезены) и «=» - для потребностей.
Практическое занятие № 3 Решение многоэтапной транспортной задачи с помощью Excel
Постановка задачи. Пусть к перевозке представлен однородный груз, который необходимо доставить из пунктов отправления А в количестве 120 т, Б – 130 т, В – 100 т через пункты перевалки Г и Д с пропускной способностью по 200 т каждый в пункты назначения Е с потребностью 50 т, Ж – 150, З – 150 т. Прямые доставки из пунктов отправления в пункты назначения невозможна. Известна стоимость доставки одной тонны груза на каждом направлении. Необходимо определить оптимальный план распределения груза при условии минимальных суммарных транспортных затрат. Представление исходных данных в Excel показано на рис. 18.
Математическая модель многоэтапной транспортной задачи имеет следующий вид.
Целевая функция, направленная на минимум суммарных транспортных затрат на всех этапах транспортировки:
(6)
В данном случае, формула в целевой ячейке должна включать все альтернативные направления доставки на двух этапах перевозки – от отправителей на склад и со склада – получателям (рис. 19).
Ограничение о полном вывозе груза от отправителей:
(7)
Ограничение о частичном использовании пропускной способности пунктов перевалки по прибытии груза:
(8)
Ограничение о полном удовлетворении спроса потребителей:
(9)
Ограничение о частичном использовании пропускной способности пунктов перевалки при вывозе груза:
(10)
Ограничения на возможные значения переменных:
(11)
Ограничения по количеству груза оформляются в Excel так же как в предыдущей задаче.
Обратить внимание необходимо на группы ограничений по пропускной способности пунктов перевалки. Они учитываются дважды: один раз по прибытию груза в пункт перевалки (рис. 20а), второй раз – по вывозу груза из пунктов перевалки потребителям (рис 20б). В ограничениях при выполнении «Поиска решения», необходимо добавить равенство между этими двумя частями.
Дополнительно, при решении многоэтапной транспортной задачи в Excel, необходимо соблюсти условие об одинаковом количестве груза, проходящем по системе на первом и втором этапе перевозки (то есть – сколько груза было вывезено из пунктов отправления, столько должно быть доставлено в пункты назначения) (рис 13).
(12)
Таким образом, поле для решения многоэтапной транспортной задачи выглядит следующим образом (рис. 22):
Решение задачи с помощью функции «Поиск решения» осуществляется по аналогии с примером, рассмотренным в практической работе 2. Решение данной многоэтапной транспортной задачи выглядит следующим образом (рис. 23).