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

Август 12, 2022

Главная
Математика
Решение транспортных задач в MS Excel

Содержание

2. Для решения транспортной задачи в EXCEL используется инструмент ПОИСК РЕШЕНИЯ. При этом необходимо: Ввести исходные данные
3. Пример Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно каждый из заводов может
4. Исходными данными для решения транспортной задачи являются: матрица транспортных расходов; предложение поставщиков; спрос потребителей. Для наглядности
5. Ввод исходных данных
6. Кроме исходных данных на рабочем листе EXCEL размещают вспомогательные блоки ячеек: "Матрица перевозок» для моделирования объемы
7. Пример
8. Сформируем блок "Фактически реализовано»: В первую ячейку блока "Фактически реализовано" (ячейка I14) внесем формулу, суммирующую ячейки
9. Формируем блок “Транспортные расходы по потребителям”: В первую ячейку блока (ячейка С21) введем формулу =СУММ (С6:С8*С14:С16):
10. Сформируем целевую функцию транспортной задачи в ячейке “Итого расходы” (ячейка I21) - внесем формулу, суммирующую ячейки
11. После формирования элементов математической модели и целевой функции транспортной задачи рабочий лист EXСEL примет вид Формирование
12. Селектируйте ячейку “Итого расходы” (ячейка I21); Выберите пункт "Поиск решения" меню "Сервис" Убедитесь, что в поле
13. Для задания ограничений щелкните кнопку “Добавить” В диалоговом окне команды “Добавление ограничения” селектируйте блок “Фактически реализовано”
14. Продолжим ввод ограничений: Щелкните кнопку “Добавить” и селектируйте блок “Фактически получено” (ячейки С18:F18); Установите курсор на
15. Введем тривиальные ограничения: Щелкните кнопку “Добавить” и селектируйте блок ячеек “Матрица перевозок” (блок С14:F16); Установите курсор
16. Окно программы “Поиск решения” примет вид Настройка программы Поиск решения
17. При необходимости установим параметры поиска с помощью кнопки “Параметры”: в появившемся окне диалога “Параметры поиска решения”
18. На рабочем листе EXCEL в блоке "Матрица перевозок" появляется решение транспортной задачи Решение задачи
20. Скачать презентацию

Слайд 2

Для решения транспортной задачи в EXCEL используется инструмент ПОИСК РЕШЕНИЯ. При

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

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

Слайд 3

Пример
Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно

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

Слайд 4

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

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

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

Слайд 5

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

Слайд 6

Кроме исходных данных на рабочем листе EXCEL размещают вспомогательные блоки ячеек:
"Матрица

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

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

Слайд 7

Пример

Слайд 8

Сформируем блок "Фактически реализовано»:
В первую ячейку блока "Фактически реализовано" (ячейка I14)

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

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

Слайд 9

Формируем блок “Транспортные расходы по потребителям”:
В первую ячейку блока (ячейка С21)

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

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

Слайд 10

Сформируем целевую функцию транспортной задачи в ячейке “Итого расходы” (ячейка I21)

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

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

Слайд 11

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

лист EXСEL примет вид

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

Слайд 12

Селектируйте ячейку “Итого расходы” (ячейка I21);
Выберите пункт "Поиск решения" меню "Сервис"

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

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

Слайд 13

Для задания ограничений щелкните кнопку “Добавить”
В диалоговом окне команды “Добавление ограничения”

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

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

Слайд 14

Продолжим ввод ограничений:
Щелкните кнопку “Добавить” и селектируйте блок “Фактически получено” (ячейки

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

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

Слайд 15

Введем тривиальные ограничения:
Щелкните кнопку “Добавить” и селектируйте блок ячеек “Матрица перевозок”

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

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

Слайд 16

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

Слайд 17

При необходимости установим параметры поиска с помощью кнопки “Параметры”:
в появившемся окне

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

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

Слайд 18

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

задачи

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

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

Содержание

Для решения транспортной задачи в EXCEL используется инструмент ПОИСК РЕШЕНИЯ. При

ПримерДля строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно

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

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

Кроме исходных данных на рабочем листе EXCEL размещают вспомогательные блоки ячеек:"Матрица

Пример

Сформируем блок "Фактически реализовано»:В первую ячейку блока "Фактически реализовано" (ячейка I14)

Формируем блок “Транспортные расходы по потребителям”:В первую ячейку блока (ячейка С21)

Сформируем целевую функцию транспортной задачи в ячейке “Итого расходы” (ячейка I21)

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

Селектируйте ячейку “Итого расходы” (ячейка I21);Выберите пункт "Поиск решения" меню "Сервис"

Для задания ограничений щелкните кнопку “Добавить”В диалоговом окне команды “Добавление ограничения”

Продолжим ввод ограничений:Щелкните кнопку “Добавить” и селектируйте блок “Фактически получено” (ячейки

Введем тривиальные ограничения:Щелкните кнопку “Добавить” и селектируйте блок ячеек “Матрица перевозок”

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

При необходимости установим параметры поиска с помощью кнопки “Параметры”:в появившемся окне

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

Похожие презентации

Пример
Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Ежедневно

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

Кроме исходных данных на рабочем листе EXCEL размещают вспомогательные блоки ячеек:
"Матрица

Сформируем блок "Фактически реализовано»:
В первую ячейку блока "Фактически реализовано" (ячейка I14)

Формируем блок “Транспортные расходы по потребителям”:
В первую ячейку блока (ячейка С21)

Селектируйте ячейку “Итого расходы” (ячейка I21);
Выберите пункт "Поиск решения" меню "Сервис"

Для задания ограничений щелкните кнопку “Добавить”
В диалоговом окне команды “Добавление ограничения”

Продолжим ввод ограничений:
Щелкните кнопку “Добавить” и селектируйте блок “Фактически получено” (ячейки

Введем тривиальные ограничения:
Щелкните кнопку “Добавить” и селектируйте блок ячеек “Матрица перевозок”

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

При необходимости установим параметры поиска с помощью кнопки “Параметры”:
в появившемся окне