- •МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ЗАДАНИЙ
- •1.ГРАФИЧЕСКИЙ АНАЛИЗ ЧУВСТВИТЕЛЬНОСТИ ОПТИМАЛЬНОГО РЕШЕНИЯ ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ (ЗЛП) В ДВУМЕРНОМ СЛУЧАЕ
- •2. РЕШЕНИЕ ЗАДАЧ ТИПА ТРАНСПОРТНОЙ В MICROSOFT EXCEL, ИСПОЛЬЗУЯ НАДСТРОЙКУ «ПОИСК РЕШЕНИЯ»
- •3. РЕШЕНИЕ С ИСПОЛЬЗОВАНИЕМ EXCEL ОДНОЙ ИЗ ЗАДАЧ ОПТИМИЗАЦИИ ПРИ СЕТЕВОМ ПЛАНИРОВАНИИ И УПРАВЛЕНИИ (СПУ)
- •4. ГРАФИЧЕСКОЕ РЕШЕНИЕ ЗАДАЧ НЕЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ
- •5. БАЛАНСОВЫЕ МОДЕЛИ. МОДЕЛЬ МЕЖОТРАСЛЕВОГО БАЛАНСА
- •6.МОДЕЛИ УПРАВЛЕНИЯ ЗАПАСАМИ
- •ЛИТЕРАТУРА
2. РЕШЕНИЕ ЗАДАЧ ТИПА ТРАНСПОРТНОЙ В MICROSOFT EXCEL, ИСПОЛЬЗУЯ НАДСТРОЙКУ «ПОИСК РЕШЕНИЯ»
Задача линейного программирования типа транспортной
Задачи линейного программирования (ЛП) типа транспортной вводятся и решаются в Excel аналогично одноиндексным задачам (в задачах
неизвестные нумеруются только одним индексом - xi ) [1]. Специфика ввода условия двухиндексной задачи ЛП (в задачах неизвестные нумеруются двумя индексами - xij ) состоит в матричном задании переменных задачи и
коэффициентов целевой функции (ЦФ) [1].
Рассмотрим решение задачи ЛП типа транспортной, суть которой заключается в оптимальной организации (минимизации затрат) транспортных перевозок товара от поставщиков потребителям (табл. 2.1).
|
|
|
|
Таблица 2.1 |
|
|
|
|
|
|
|
Тарифы, |
1-й потре- |
2-й потре- |
3-й потре- |
|
Запасы, |
ден. ед./шт. |
битель (B1) |
битель (B2) |
битель (B3) |
|
ед. |
1-й поставщик (A1) |
12 |
5 |
6 |
|
55 |
2-й поставщик (A2) |
7 |
4 |
13 |
|
50 |
3-й поставщик (A3) |
8 |
5 |
10 |
|
75 |
Потребности, ед. |
40 |
90 |
50 |
|
180 |
Исходные данные транспортной задачи
Целевая функция и ограничения данной задачи имеют вид
L = 12x11 +5x12 + 6x13 +
+7x21 + 4x23 +13x23 +
+8x31 +5x32 +10x33 →min;
|
|
+ x12 + x13 = 55; |
|
|
|
|
|
|
|
||||||
x11 |
|
|
|
|
|
|
|
||||||||
x |
21 |
+ x22 |
+ x23 |
= 50; |
|
|
|
|
|
(2.1) |
|||||
|
|
+ x32 + x33 = 75; |
|
|
|||||||||||
x |
31 |
|
|
|
|||||||||||
|
|
+ x21 + x31 = 40; |
|
|
|
||||||||||
x11 |
|
|
|
||||||||||||
x |
12 |
+ x |
22 |
+ x |
32 |
= 90; |
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||
x13 + x23 + x33 = 50; |
|
|
|
||||||||||||
|
|
≥ 0, x |
|
|
|
|
( |
|
|
|
|
|
) |
|
|
x |
ij |
ij |
-целые |
i = 1,3; j = 1,3 |
, |
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
||||
где xij - величина перевозки от i -го поставщика к |
j -му потребителю. |
22
Экранные формы, задание переменных, целевой функции, ограничений и граничных условий двухиндексной задачи (2.1) и ее решение представле-
ны на рис.2.1, 2.2, 2.3 и в табл.2.2.
Рис.2.1. Экранная форма двухиндексной задачи (2.1) (курсор в целевой ячейке F13).
|
Таблица 2.2 |
|
Формулы экранной формы задачи (2.1) |
||
|
|
|
Элемент математической модели |
Выражение в Excel |
|
Переменные задачи |
C3:E5 |
|
Формула в целевой ячейке F13 |
=СУММПРОИЗВ(C3:E5;C11:E13) |
|
Левые части ограничений по строкам в ячей- |
=СУММ(C3:E3) |
|
=СУММ(C4:E4) |
||
ках F3, F4, F5 |
||
=СУММ(C5:E5) |
||
|
||
Левые части ограничений по столбцам в ячей- |
=СУММ(C3:C5) |
|
=СУММ(D3:D5) |
||
ках C6, D6, E6 |
||
=СУММ(E3:E5) |
||
|
||
Суммарные запасы и потребности в ячейках |
=СУММ(H3:H5) |
|
H7, G8 |
=СУММ(C8:E8) |
Рис.2.2. Ограничения и граничные условия задачи (2.1).
23
После нажатия кнопки Выполнить получаем искомое решение задачи
(2.1).
Рис.2.3. Экранная форма после получения решения задачи (2.1) (курсор в целевой ячейке F13).
На рис. 2.3 в ячейках C3:E5 находятся величины перевозок, обеспечивающие минимальную суммарную стоимость перевозок, величина которой указана в ячейке F13.
Индивидуальные задания. Найти оптимальный план перевозок, используя возможности EXCEL.
Вариант 1 |
B1 |
|
B2 |
|
B3 |
|
B4 |
|
B5 |
7 |
Запасы |
A1 |
|
4 |
|
8 |
|
13 |
|
2 |
|
300 |
|
A2 |
|
9 |
|
4 |
|
11 |
|
9 |
|
17 |
250 |
A3 |
|
3 |
|
16 |
|
10 |
|
1 |
200 |
4 |
200 |
Потребности |
210 |
|
170 |
|
220 |
|
150 |
|
|
|
|
|
|
|
|
|
|
|
|
|
B5 |
|
|
Вариант 2 |
B1 |
|
B2 |
|
B3 |
|
B4 |
|
15 |
Запасы |
|
A1 |
|
24 |
|
50 |
|
45 |
|
27 |
|
200 |
|
A2 |
|
20 |
|
32 |
|
40 |
|
35 |
|
30 |
350 |
A3 |
|
22 |
|
16 |
|
18 |
|
28 |
110 |
20 |
300 |
Потребности |
170 |
|
130 |
|
190 |
|
150 |
|
|
|
|
|
|
|
|
|
|
|
|
|
B5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 3 |
B1 |
|
B2 |
|
B3 |
|
B4 |
|
30 |
Запасы |
|
A1 |
|
15 |
|
3 |
|
6 |
|
10 |
|
150 |
|
A2 |
|
12 |
|
8 |
|
12 |
|
16 |
|
25 |
100 |
A3 |
|
14 |
|
11 |
|
9 |
|
8 |
90 |
15 |
200 |
Потребности |
100 |
|
70 |
|
130 |
|
110 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
24
Вариант 4 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
22 |
|
14 |
|
16 |
28 |
30 |
350 |
A2 |
|
19 |
|
17 |
|
26 |
36 |
36 |
200 |
A3 |
|
37 |
|
30 |
|
31 |
39 |
41 |
300 |
Потребности |
170 |
|
140 |
|
150 |
|
195 |
145 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 5 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
28 |
|
27 |
|
18 |
27 |
24 |
200 |
A2 |
|
18 |
|
26 |
|
27 |
32 |
21 |
250 |
A3 |
|
27 |
|
33 |
|
23 |
31 |
34 |
200 |
Потребности |
90 |
|
100 |
|
120 |
|
110 |
130 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 6 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
40 |
|
19 |
|
25 |
26 |
35 |
230 |
A2 |
|
42 |
|
25 |
|
27 |
15 |
38 |
190 |
A3 |
|
46 |
|
27 |
|
36 |
40 |
45 |
170 |
Потребности |
140 |
|
90 |
|
160 |
|
110 |
150 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 7 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
20 |
|
10 |
|
12 |
13 |
16 |
150 |
A2 |
|
25 |
|
19 |
|
20 |
14 |
10 |
300 |
A3 |
|
17 |
|
18 |
|
15 |
10 |
17 |
250 |
Потребности |
210 |
|
150 |
|
120 |
|
135 |
135 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 8 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
10 |
|
12 |
|
11 |
20 |
40 |
260 |
A2 |
|
14 |
|
8 |
|
9 |
11 |
15 |
270 |
A3 |
|
8 |
|
6 |
|
12 |
14 |
20 |
350 |
Потребности |
220 |
|
170 |
|
210 |
|
150 |
200 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 9 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
15 |
|
23 |
|
26 |
19 |
18 |
150 |
A2 |
|
17 |
|
13 |
|
14 |
25 |
10 |
200 |
A3 |
|
12 |
|
21 |
|
24 |
12 |
9 |
100 |
Потребности |
90 |
|
50 |
|
75 |
|
60 |
75 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вариант 10 |
B1 |
|
B2 |
|
B3 |
|
B4 |
B5 |
Запасы |
A1 |
|
18 |
|
30 |
|
35 |
25 |
40 |
300 |
A2 |
|
12 |
|
14 |
|
22 |
20 |
35 |
350 |
A3 |
|
10 |
|
28 |
|
23 |
19 |
30 |
200 |
Потребности |
145 |
|
195 |
|
150 |
|
140 |
170 |
|
|
|
|
|
|
|
|
|
|
|
Примечание 1. Если задача несбалансированная (открытая), то прежде, чем приступить к ее решению, необходимо ее сбалансировать (закрыть). Для этого ввести в задачу фиктивного поставщика (потребителя) с таким
25
запасом (потребностью), чтобы суммарные запасы равнялись суммарным потребностям [1,5]. Тарифы перевозок от введенного фиктивного поставщика (к введенному фиктивному потребителю) взять нулевыми.
Примечание 2. Решение задачи должно быть представлено либо в виде файла, либо на печатном носителе. Во втором случае должен быть распечатан лист, содержащий решенную задачу, и тот же лист в режиме показа формул – команда Ctrl - `.
26