Решение транспортных задач в MS Excel

При этом необходимо:
Ввести исходные данные в ячейки рабочего листа EXCEL;
Разметить

блоки ячеек на рабочем листе EXCEL, необходимые для моделирования объемов перевозок и формирования элементов математической модели задачи;
Сформировать на рабочем листе EXCEL элементы математической модели;
Настроить программу " Поиск решения" и выполнить ее.

Инструмент Поиск Решения

Ежедневно каждый из заводов может изготовить 100, 150 и 50

условных единиц кирпича. Потребности в кирпиче  на каждом из строящихся объектов ежедневно составляют 75, 80, 60 и 85 условных единиц. Тарифы перевозок одной условной единицы кирпича с каждого из заводов к каждому из строящихся объектов задаются матрицей транспортных расходов С.




Требуется составить такой план перевозок кирпича к строящимся объектам, при котором общая стоимость перевозок будет минимальной

потребителей.
Для наглядности блоки ячеек с введенными данными желательно обвести рамками

Ввод исходных данных

ячеек:
"Матрица перевозок» для моделирования объемы перевозок;
"Фактически реализовано", для моделирования фактической

реализация продукции;
"Фактически получено", для моделирования фактического удовлетворение спроса;
"Транспортные расходы по потребителям", для вычисления транспортных расходов по каждому потребителю;
Ячейку "Итого расходы", в которой вычисляются итоговые транспортные расходы по всем потребителям (целевая ячейка).
Для наглядности указанные блоки ячеек целесообразно обвести рамками.

Разметка блоков ячеек рабочего листа EXCEL

I14) внесем формулу, суммирующую ячейки той же строки в блоке

«Матрица перевозок» (ячейки С14:F14)
Скопируйте формулу на все остальные ячейки блока.

Сформируем блок "Фактически получено":
В первую ячейку блока "Фактически получено" (ячейка С18) внесем формулу, суммирующую ячейки того же столбца в блоке «Матрица перевозок» (ячейки С14:С16);
Скопируйте формулу на все остальные ячейки блока.

Формирование элементов математической модели

С21) введем формулу =СУММ (С6:С8*С14:С16):
Наведите курсор на кнопку автосуммирования и

щелкните левой клавишей мыши;
Нажмите клавишу “Delete ”;
Селектируйте первый столбец блока “Матрица Транспортных расходов” (столбец С6:С8);
Нажмите клавишу *;
Селектируйте первый столбец блока “Матрица превозок” (столбец С14:С16);
Активируйте строку формул, наведя на неё курсор и щелкнув затем левой клавишей мыши;
Нажмите одновременно три клавиши: “CTRL”+“SHIFT”+“ENTER”;
Копируем формулу в остальные ячейки блока

Формирование элементов математической модели

I21) - внесем формулу, суммирующую ячейки той же строки в

блоке «Транспортные расходы по потребителям» (ячейки С21:F21)

Формирование элементов математической модели

рабочий лист EXСEL примет вид
Формирование элементов математической модели

"Сервис" Убедитесь, что в поле “Установить целевую ячейку” диалогового окна

программы “Поиск решения” указана ячейка $I$21.
Установите курсор на переключатель “Равной Минимальному значению”;
Установите курсор в поле “Изменяя ячейки” и селектируйте блок ячеек “Матрица перевозок” (блок С14:F16)

Настройка программы Поиск решения

ограничения” селектируйте блок “Фактически реализовано” (ячейки I14:I16);
Убедитесь, что оператор сравнения

<= уже выбран.
В поле “Ограничение” селектируйте блок ячеек “Предложение поставщиков” (блок I6:I8)
Убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке

Настройка программы Поиск решения

(ячейки С18:F18);
Установите курсор на значение  >=  (больше или равно);
В поле

“Ограничение” селектируйте блок “Спрос потребителей” (ячейки С10:F10);
Убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке

Настройка программы Поиск решения

перевозок” (блок С14:F16);
Установите курсор на значение  >=  (больше или равно);
В

поле “Ограничение” наберите 0.
Убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке






Тот же результат достигается установкой флажка "Неотрицательные значения" в окне диалога "Параметры поиска решения".

Настройка программы Поиск решения

окне диалога “Параметры поиска решения” установим флажок “Линейная модель”;
можно изменить

другие параметры поиска;
Вернемся в окно «Поиск решения»
В окне "Поиск решения" щелкаем на кнопке "Выполнить»

Настройка программы Поиск решения

транспортной задачи
Решение задачи

Для завершения расчетов щелкните на кнопке ОК.
Решение задачи