Решение транспортной задачи средствами Microsoft Exel - Транспортная задача

Многие задачи экономико-математического моделирования являются оптимизационными, т. е. в них требуется найти максимальное (минимальное или равное определенному числу) значений некоторой функции, называемой целевой. Реальные задачи имеют много условий, поэтому поиск оптимального решения требует большого объема вычислений. Использование ЭВМ позволяет производить сложные расчеты за короткий срок.

Составим план перевозок с минимальными затратами используя программу Microsoft Excel.

1) Составим на рабочем листе Excel две таблицы. В таблице "Тарифы" записываются исходные числовые данные, в таблицы "План перевозок" продублированы столбец "запасы" и строка "Потребность" для удобства работы с таблицей добавлены столбец "Использовано" и строка "Удовлетворено" (рисунок 1).

заполнение таблицы

Рисунок 1 - Заполнение таблицы "Тарифы"

2) Заполним формулами, необходимыми для создания ограничений на запасы, ячейки G12:G16 столбца "Использовано". В ячейку G12 вводим формулу суммы (=СУММ(B12:F12)). Аналогичные операции производим для каждого поставщика (рисунок 2 и 3).

заполнение формулой ячейки

Рисунок 2 - Заполнение формулой ячейки "Использовано" для поставщика А1

заполнение формулами столбца

Рисунок 3 - Заполнение формулами столбца "Использовано"

3) Заполним формулами, необходимыми для создания ограничений на потребности, ячейки B17:E17 строки "Удовлетворено". В ячейку B17 вводим формулу суммы (=СУММ(B12:B16)). Аналогичные образом производим заполнение последующих столбцов (рисунок 4).

заполнение формулой ячейки

Рисунок 4 - Заполнение формулой ячейки "Удовлетворено" для первого потребителя

4) Запишем общие суммы по столбцам и строкам: в ячейку H17 - по столбцу "Запасы" (=СУММ(H12:H16)) (рисунок 5); в ячейку G17 - по строке "Использовано" (=СУММ(G12:G16)) (рисунок 6); в ячейку G18 - по строке "Потребность" (=СУММ(B18:F18)) (рисунок 7).

общая сумма по столбцу

Рисунок 5 - Общая сумма по столбцу "Запасы"

общая сумма по столбцу

Рисунок 6 - Общая сумма по столбцу "Использовано"

общая сумма по строке

Рисунок 7 - Общая сумма по строке "Потребность"

5) Запишем формулу для целевой функции, для этого выделим ячейку G19, на значке fx на панели инструментов, в категории "Математические" выбираем функцию "СУММПРОИЗВ". В появившемся диалоговом окне выбираем "массив1", проводим указателем мыши по диапазону ячеек B3:F7, затем выбрав "массив2" выделим диапазон ячеек B12:F16. В строке формулы появится функция с выбранными ссылками на ячейке. В самой ячейке G19 пока запишется нулевое значение. Полученная формула дает сумму из 25 попарных произведений указанных ячеек двух таблиц, которая и является для нас целевой функцией (рисунок 8).

заполнение формулой ячейки

Рисунок 8 - Заполнение формулой ячейки "Целевая функция"

6) Укажем необходимые ссылки на ячейки и ограничения для целевой функции, для этого выполним команду "Сервис", "Поиск решения". В появившемся диалоговом окне в поле "Установить целевую" функцию ссылаемся на ячейку G19. Установим в поле "Равной" значение, соответствующее по "минимальному значению". В поле "Изменяя ячейки" указываем диапазон ячеек $B$12:$F$16.

В поле "Ограничения" создаем список всех ограничений для нашей задачи, представленный в таблице19.

Таблица 19 - Список ограничений

Поле "Ссылка на ячейку"

Тип ограничения

Поле "Ограничения"

Примечание

$B$12:$F$16

>=

0

Условие неотрицательности перевозимых количеств продукта

$G$12:$G$16

=

$H$12:$H$16

Условие полного распределения запасов

$B$17:$E$17

=

$B$18:$E$18

Условие полного удовлетворения потребностей

После ввода параметров щелчок на кнопке "Выполнить" запускает поиск оптимального решения. Появляется диалоговое окно "Результаты поиска решения" с сообщением об успехе поиска.

В изменяемые ячейки B12:F16 записываются значения оптимального плана перевозок, ячейку G19 - значение целевой функции, а в ячейки столбца "Использовано" и строки "Удовлетворено" записываются значения, равные значениям соответствующих ячеек столбца "Запасы" и строки "Потребность".

Результаты выполненных расчетов представлены в таблицах 20 и 21.

Таблица 20 - Тарифы транспортной задачи

Таблица 1

Тарифы

Запас

B1

B2

B3

B4

B5

Поставщик А1

4

4

5

4

4

350

Поставщик А2

6

6

7

1

4

270

Поставщик А3

5

1

9

4

1

210

Поставщик А4

8

6

3

7

3

190

Поставщик А5

5

4

7

6

3

200

Потребность

350

270

210

190

200

Таблица 21 - Оптимальный план перевозок

Таблица 2

План перевозок

Использовано

Запас

B1

B2

B3

B4

B5

Поставщик А1

330

0

20

0

0

350

350

Поставщик А2

2

0

0

190

78

270

270

Поставщик А3

0

210

0

0

0

210

210

Поставщик А4

0

0

190

0

0

190

190

Поставщик А5

18

60

0

0

122

200

200

Удовлетворено

350

270

210

190

200

1220

1220

Потребность

350

270

210

190

200

1220

Затраты

3410

Похожие статьи




Решение транспортной задачи средствами Microsoft Exel - Транспортная задача

Предыдущая | Следующая