Тема 7. Решение транспортной задачи средствами табличного процессора MS Excel

Требуется решить следующую транспортную задачу. Известны три поставщика с имеющимися у них запасами продукции, четы­ре потребителя, нуждающиеся в данной продукции и транспорт­ные затраты на поставку продукции от поставщиков к потреби­телям. Данные представлены в таблице:

Таблица 7.1

Поставщики	Потребители	Запасы
B1 В1	В2	B3	В4
А1
А2
A3
Потребность

Необходимо прикрепить поставщиков к потребителям с уче­том минимальных транспортных затрат.

Решение задачи в Excel состоит из следующих шагов:

1. создание формы для ввода условий задачи;

2. ввод исходных данных;

3. ввод ограничений и граничных условий;

4. проведение расчетов

1. Создание формы для ввода условий задачи, ввод исходных данных и определение целевой функции.

Создаем матрицу перевозок, которая имеет изменяемые ячей­ки (В14:Е16). После проведения расчетов на ее месте будет записан оптимальный план перевозок. По каждой строке и столбцу мат­рицы перевозок записываем соответствующую формулу:

В ячейки A5:F10 введем исходные данные задачи, а в ячейке С18 запишем значение целевой функции, используя функцию –СУММПРОИЗВ(В2:Е5;В11:Е13). Тем самым получим таблицы, как показано на рис. 7.1.

2. Ввод ограничений и граничных условий

Устанавливаем курсор на ячейку C18, в которой записано значение целевой функции, и вызываем операцию Поиск реше­ния: Сервис → Поиск решения в Excel 2003 или Данные → Анализ → Поиск решения в Excel 2007. В появившемся окне "Поиск ре­шения" в поле Установить целевую функцию следует внести ад­рес ячейки C18, в которой записано значение целевой функции (см. рис. 7.2).

рис. 7.1

Рис. 7.2

После этого следует добавить ограничения, нажав на клавишу Добавить. На экран будет выдано окно "Добавление ограниче­ния" (см. рис. 7.3).

	Рис. 7.3

Все потребности должны быть удовлетворены, и все запасы должны быть распределены.

После ввода последнего ограничения вместо нажатия клави­ши Добавить необходимо нажать клавишу ОК. На экране появится окно "Поиск решения".

3. Решение задачи

Перед тем как выполнить решение задачи, которое проводится и том же окне "Поиск решения", следует выполнить некоторые назначения, нажав на клавишу Параметры. Например, следует установить флажок Неотрицательные значения и флажок Линей­ная модель (см. рис. 7.4).

Рис. 7.4

После проведения всех необходимых установок надо нажать клавишу ОК, что приведет к возврату на окно "Поиск реше­ния". В этом окне следует нажать на клавишу Выполнить, после чего на рабочем поле Excel будет выдан оптимальный план поставок и диалоговое окно "Результаты поиска решения", а в ячейке C18 будет выдан результат целевой функции для данной задачи, равный 590 (см. рис. 7.5).

Рис. 7.5

ПРИЛОЖЕНИЕ 1

Дата добавления: 2014-12-26