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

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

Инструмент Поиск Решения Для решения транспортной задачи в EXCEL используется инструмент ПОИСК РЕШЕНИЯ. При этом необходимо: Ввести исходные данные в ячейки рабочего листа EXCEL; Разметить блоки ячеек на рабочем листе EXCEL, необходимые для моделирования объемов перевозок и формирования элементов математической модели задачи; Сформировать на рабочем листе EXCEL элементы математической модели; Настроить программу " Поиск решения" и выполнить ее.

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

Ввод исходных данных Исходными данными для решения транспортной задачи являются: ◦ матрица транспортных расходов; ◦ предложение поставщиков; ◦ спрос потребителей. Для наглядности блоки ячеек с введенными данными желательно обвести рамками

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

Пример

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

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

Формирование элементов математической модели Сформируем целевую функцию транспортной задачи в ячейке “Итого расходы” (ячейка I 21) - внесем формулу, суммирующую ячейки той же строки в блоке «Транспортные расходы по потребителям» (ячейки С 21: F 21)

Формирование элементов математической модели После формирования элементов математической модели и целевой функции транспортной задачи рабочий лист EXСEL примет вид

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

Настройка программы Поиск решения Для задания ограничений щелкните кнопку “Добавить” ◦ В диалоговом окне команды “Добавление ограничения” селектируйте блок “Фактически реализовано” (ячейки I 14: I 16); ◦ Убедитесь, что оператор сравнения <= уже выбран. ◦ В поле “Ограничение” селектируйте блок ячеек “Предложение поставщиков” (блок I 6: I 8) Убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке

Настройка программы Поиск решения Продолжим ввод ограничений: ◦ Щелкните кнопку “Добавить” и селектируйте блок “Фактически получено” (ячейки С 18: F 18); ◦ Установите курсор на значение >= (больше или равно); ◦ В поле “Ограничение” селектируйте блок “Спрос потребителей” (ячейки С 10: F 10); Убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке

Настройка программы Поиск решения Введем тривиальные ограничения: ◦ Щелкните кнопку “Добавить” и селектируйте блок ячеек “Матрица перевозок” (блок С 14: F 16); ◦ Установите курсор на значение >= (больше или равно); ◦ В поле “Ограничение” наберите 0. Убедитесь, что окно диалога команды “Добавление ограничения” имеет вид, показанный на рисунке Тот же результат достигается установкой флажка "Неотрицательные значения" в окне диалога "Параметры поиска решения".

Настройка программы Поиск решения Окно программы “Поиск решения” примет вид

Настройка программы Поиск решения При необходимости установим параметры поиска с помощью кнопки “Параметры”: ◦ в появившемся окне диалога “Параметры поиска решения” установим флажок “Линейная модель”; ◦ можно изменить другие параметры поиска; ◦ Вернемся в окно «Поиск решения» В окне "Поиск решения" щелкаем на кнопке "Выполнить»

Решение задачи На рабочем листе EXCEL в блоке "Матрица перевозок" появляется решение транспортной задачи

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