Решение задач моделирования и оптимизации с помощью программ Excel и Mathcad
А1, А2, А3 …Аi, …Аm
на которых сосредоточены запасы како-либо груза в объемах соответственно:
а1, а2, а3 …аi, …аm
где величина аi – максимально возможное количество груза в i пункте отправления. Тогда суммарный запас груза у всех поставщиков составляет:

2. Имеются n пунктов назначения,
B1, B2, B3 …Bj, …Bn
которые подали заявки на поставку грузов в объемах соответственно:
b1, b2, b3, …bj, …bn
тогда суммарная величина заявок составляет:

3. Стоимость перевозки 1 единицы груза от поставщика Аi к потребителю Bj обозначим ci,j (транспортный тариф).
Общая стоимость перевозок составляет матрицу транспортных издержек С.
Критерием оптимальности выберем суммарные затраты (издержки по перевозки груза).
Все исходные данные транспортной задачи записываются в виде таблицы (транспортной), в которой xi,j – значение объема перевозки грузов от поставщика Аi к потребителю Bj.
| Пункты отправления | Пункты назначения | Запасы аi | |||
| В1 | В2 | …Вj ... | Вn | ||
| А1 |
c11 x11 |
c12 x12 |
c1j x1j |
c1n x1n |
а1 |
| А2 |
c21 x21 |
c22 x22 |
c2j x2j |
c2n x2n |
а2 |
| …Аi… |
ci1 xi1 |
ci2 xi2 |
cij xij |
cin xin |
аi |
| Аm |
cm1 xm1 |
cm2 xm2 |
cmj xmj |
cmn Xmn |
аm |
| Заявки bj | b1 | b2 | bj | bn | |
Задача заключается в определении плана перевозок матрицы x, где
![]()
x
(i = 1, m; j
= 1, n ),
который удовлетворяет следующим условиям:
1. План перевозок от i поставщика всем потребителям (сумма по строке), ограничивается запасом на складе j поставщика.

2. План поставок j потребителю от всех поставщиков (сумма по столбцу) должен быть не меньше объема заявок j потребителя.

3. Из физического смысла задачи определяем, что объемы перевозок xij не могут быть отрицательными.
xij ≥ bj; j = 1, n
4. Затраты на перевозку равны:
m n
P(X) = ∑∑ cij* xij
i=1 j=1
X – план перевозок, то есть матрица X.
Таким образом, экономико-математическая модель задачи можно записать в виде:
- Найти минимальное значение целевой функции:
m n
P(X)
= ∑∑ cij*
xij min
i=1 j=1
Данная экономико-математическая модель является одной из разновидностей транспортной задачи.
Условие: требуется минимизировать затраты на перевозку грузов от заводов поставщиков (А1, А2, А3) на торговые склады В1, В2, В3. Товары могут доставляться с любого завода на любой склад, при условии:
Минимальное количество поставляемого груза с завода А1 и завода А2 к каждому потребителю составляет 160 тонн.
С завода А2 на склад В1 нужно привезти не более 200 тонн груза, а с завода А3 на склад В1 – не менее 100 тонн.
Необходимо учесть:
- возможности поставщиков;
- максимально удовлетворить заявки склада.
Дано:
1. Потребности склада:
В1 – 500;
В2 – 600;
В3 – 400.
2. Мощность заводов:
А1 – 500;
А2 – 600;
А3 – 500.
3. Стоимость перевозки единицы груза:
| Потребители | В1 | В2 | В3 |
| Поставщики | |||
| А1 | 9 | 6 | 2 |
| А2 | 4 | 15 | 15 |
| А3 | 17 | 7 | 8 |
Ручной поиск оптимального плана.
Цель: составить оптимальный план вручную
Используя стандартный программный пакет Excel, необходимо интуитивно задать пять возможных вариантов плана перевозки, я нашла наилучший интуитивный план перевозок продукции, при котором затраты на перевозку были минимальные.
| Стоимость перевозки единицы груза от завода к складу. | |||||
| Потребитель | |||||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 9 | 6 | 2 | ||
| А2 | 4 | 15 | 15 | ||
| А3 | 17 | 7 | 8 | ||
| План перевозок от завода к складу | |||||
| Потребитель | План поставок | Мощность завода | |||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 180 | 160 | 160 | 500 | 500 |
| А2 | 160 | 180 | 160 | 500 | 600 |
| А3 | 160 | 260 | 80 | 500 | 500 |
| Поставлено по складу | 500 | 600 | 400 | ||
| Потребность складов | 500 | 600 | 400 | ||
| Стоимость перевозок по каждому складу | Трансп. расходы | ||||
| 4980 | 5480 | 3360 | 13820 | ||
| Стоимость перевозки единицы груза от завода к складу. | |||||
| Потребитель | |||||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 9 | 6 | 2 | ||
| А2 | 4 | 15 | 15 | ||
| А3 | 17 | 7 | 8 | ||
| План перевозок от завода к складу | |||||
| Потребитель | План поставок | Мощность завода | |||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 180 | 160 | 160 | 500 | 500 |
| А2 | 180 | 160 | 160 | 500 | 600 |
| А3 | 140 | 280 | 80 | 500 | 500 |
| Поставлено по складу | 500 | 600 | 400 | ||
| Потребность складов | 500 | 600 | 400 | ||
| Стоимость перевозок по каждому складу | Трансп. расходы | ||||
| 4720 | 5320 | 3360 | 13400 | ||
| Стоимость перевозки единицы груза от завода к складу. | |||||
| Потребитель | |||||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 9 | 6 | 2 | ||
| А2 | 4 | 15 | 15 | ||
| А3 | 17 | 7 | 8 | ||
| План перевозок от завода к складу | |||||
| Потребитель | План поставок | Мощность завода | |||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 180 | 160 | 160 | 500 | 500 |
| А2 | 160 | 200 | 160 | 520 | 600 |
| А3 | 160 | 140 | 80 | 380 | 500 |
| Поставлено по складу | 500 | 500 | 400 | ||
| Потребность складов | 500 | 600 | 400 | ||
| Стоимость перевозок по каждому складу | Трансп. расходы | ||||
| 4980 | 4940 | 3360 | 13280 | ||
| Стоимость перевозки единицы груза от завода к складу. | |||||
| Потребитель | |||||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 9 | 6 | 2 | ||
| А2 | 4 | 15 | 15 | ||
| А3 | 17 | 7 | 8 | ||
| План перевозок от завода к складу | |||||
| Потребитель | План поставок | Мощность завода | |||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 160 | 160 | 160 | 480 | 500 |
| А2 | 160 | 160 | 160 | 480 | 600 |
| А3 | 180 | 270 | 80 | 530 | 500 |
| Поставлено по складу | 500 | 590 | 400 | ||
| Потребность складов | 500 | 600 | 400 | ||
| Стоимость перевозок по каждому складу | Трансп. расходы | ||||
| 5140 | 5250 | 3360 | 13750 | ||
| Стоимость перевозки единицы груза от завода к складу. | |||||
| Потребитель | |||||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 9 | 6 | 2 | ||
| А2 | 4 | 15 | 15 | ||
| А3 | 17 | 7 | 8 | ||
| План перевозок от завода к складу | |||||
| Потребитель | План поставок | Мощность завода | |||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 170 | 170 | 160 | 500 | 500 |
| А2 | 170 | 170 | 170 | 510 | 600 |
| А3 | 160 | 260 | 70 | 490 | 500 |
| Поставлено по складу | 500 | 600 | 400 | ||
| Потребность складов | 500 | 600 | 400 | ||
| Стоимость перевозок по каждому складу | Трансп. расходы | ||||
| 4930 | 5390 | 3430 | 13750 | ||
Вывод:
Наилучшим планом из составленных вручную с помощью программы Excel является план №3, т.к. при данном плане затраты на перевозку минимальные и составляют 13280 руб.
Программный поиск оптимального решения.
Нахождение оптимального решения с помощью программы оптимизации в Excel.
| Стоимость перевозки единицы груза от завода к складу. | |||||
| Потребитель | |||||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 9 | 6 | 2 | ||
| А2 | 4 | 15 | 15 | ||
| А3 | 17 | 7 | 8 | ||
| План перевозок от завода к складу | |||||
| Потребитель | План поставок | Мощность завода | |||
| Поставщик | В1 | В2 | В3 | ||
| А1 | 180 | 160 | 160 | 500 | 500 |
| А2 | 200 | 160 | 160 | 520 | 600 |
| А3 | 120 | 280 | 80 | 480 | 500 |
| Поставлено по складу | 500 | 600 | 400 | ||
| Потребность складов | 500 | 600 | 400 | ||
| Стоимость перевозок по каждому складу | Трансп. расходы | ||||
| 4460 | 5320 | 3360 | 13140 | ||
Excel получил результат:
С завода А1 в склад В1 необходимо поставить 180 тонн груза, на В2 – 160 тонн, на В3 – 160 тонн. С завода А2 в склад В1 – 200 тонн, в В2 – 160 тонн, в В3 – 160 тонн. С завода А3 в склад В1 – 120 тонн, в В2 – 280 тонн, в В3 – 80 тонн.
Затраты на перевозку составляют 13140 руб.
Отчет.
| Целевая ячейка (Минимум) | ||||||
|
Ячейка |
Имя |
Исходное значение |
Результат |
|||
| $E$16 | Трансп. расходы | 0 | 13140 | |||
| Изменяемые ячейки | ||||||
|
Ячейка |
Имя |
Исходное значение |
Результат |
|||
| $B$10 | А1 В1 | 0 | 180 | |||
| $C$10 | А1 В2 | 0 | 160 | |||
| $D$10 | А1 В3 | 0 | 160 | |||
| $B$11 | А2 В1 | 0 | 200 | |||
| $C$11 | А2 В2 | 0 | 160 | |||
| $D$11 | А2 В3 | 0 | 160 | |||
| $B$12 | А3 В1 | 0 | 120 | |||
| $C$12 | А3 В2 | 0 | 280 | |||
| $D$12 | А3 В3 | 0 | 80 | |||
| Ограничения | ||||||
|
Ячейка |
Имя |
Значение |
Формула |
Статус |
Разница |
|
| $E$10 | А1 План поставок | 500 | $E$10<=$F$10 | связанное | 0 | |
| $E$11 | А2 План поставок | 520 | $E$11<=$F$11 | не связан. | 80 | |
| $E$12 | А3 План поставок | 480 | $E$12<=$F$12 | не связан. | 20 | |
| $B$13 | Поставлено по складу В1 | 500 | $B$13=$B$14 | не связан. | 0 | |
| $C$13 | Поставлено по складу В2 | 600 | $C$13=$C$14 | не связан. | 0 | |
| $D$13 | Поставлено по складу В3 | 400 | $D$13=$D$14 | не связан. | 0 | |
| $B$10 | А1 В1 | 180 | $B$10>=0 | не связан. | 180 | |
| $C$10 | А1 В2 | 160 | $C$10>=0 | не связан. | 160 | |
| $D$10 | А1 В3 | 160 | $D$10>=0 | не связан. | 160 | |
| $B$11 | А2 В1 | 200 | $B$11>=0 | не связан. | 200 | |
| $C$11 | А2 В2 | 160 | $C$11>=0 | не связан. | 160 | |
| $D$11 | А2 В3 | 160 | $D$11>=0 | не связан. | 160 | |
| $B$12 | А3 В1 | 120 | $B$12>=0 | не связан. | 120 | |
| $C$12 | А3 В2 | 280 | $C$12>=0 | не связан. | 280 | |
| $D$12 | А3 В3 | 80 | $D$12>=0 | не связан. | 80 | |
| $B$11 | А2 В1 | 200 | $B$11<=200 | связанное | 0 | |
| $B$12 | А3 В1 | 120 | $B$12>=100 | не связан. | 20 | |
| $B$10 | А1 В1 | 180 | $B$10>=160 | не связан. | 20 | |
| $C$10 | А1 В2 | 160 | $C$10>=160 | связанное | 0 | |
| $D$10 | А1 В3 | 160 | $D$10>=160 | связанное | 0 | |
| $B$11 | А2 В1 | 200 | $B$11>=160 | не связан. | 40 | |
| $C$11 | А2 В2 | 160 | $C$11>=160 | связанное | 0 | |
| $D$11 | А2 В3 | 160 | $D$11>=160 | связанное | 0 | |
2. Нахождение оптимального решения в системе Mathcad.

Mathcad получил результат:
С завода А1 в склад В1 необходимо поставить 180 тонн груза, на В2 – 160 тонн, на В3 – 160 тонн. С завода А2 в склад В1 – 200 тонн, в В2 – 160 тонн, в В3 – 160 тонн. С завода А3 в склад В1 – 120 тонн, в В2 – 280 тонн, в В3 – 80 тонн.
Затраты на перевозку составляют 13140 руб.
Построение таблицы полученных решений.
| План, найденный вручную | План, найденный программным путем | ||||||
| № | 1 | 2 | 3 | 4 | 5 | Excel | Mathcad |
| Трансп. расходы | 13820 | 13400 | 13280 | 13750 | 13750 | 13140 | 13140 |

Вывод:
Наименьшие затраты на перевозку грузов получается при решении с помощью программы оптимизатора в Excel и в системе Mathcad.
Анализ результатов.
Из графика видно, что наилучшим планом, составленным вручную является план №4 с транспортными расходами 13280 руб. Оптимальным планом является план полученный с помощью оптимизаторов в Excel и Mathcad. При этом транспортные расходы составляют 13140 руб.
По оптимальному плану мощность завода А1 задействована не полностью, а мощности заводов А2 и А3 полностью.
При долгосрочных планах можно предложить на будущее увеличить мощности заводов А1, а уменьшить мощность в заводах А2 и А3.