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

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

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

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

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

Пример

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

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

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

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

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

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

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

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

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

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

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

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