Цель урока: изучить построение экономической модели для решения транспортной задачи.
Пример. Три склада должны обеспечить поставку компьютерных мониторов в четыре магазина. Объемы заявок магазинов и объемы поставок складами заданы.
Объемы заявок (в шт.):
Объемы поставок (в шт.):
Транспортная компания, которая должна перевезти мониторы (штучный товар), рассчитала затраты на перевозку одного монитора по каждому маршруту и составила таблицу удельных затрат на перевозки. Найти план перевозок с минимальной стоимостью.
Таблица удельных затрат на перевозки (в денежных единицах на единицу товара):
Схема размещения исходных данных компьютерной модели.
Схема размещения расчетной таблицы компьютерной модели транспортной задачи.
План перевозок справа и снизу обрамлен ячейками для сумм.
Для копирования таблицы из раздела «Исходные данные» следует выделить диапазон A4:E8 и скопировать его на диапазон A13:E17.
Начальные значения ячеек плана перевозок могут быть любыми числами, поэтому скопированные значения ячеек плана перевозок можно оставить.
В ячейку A18 вводится надпись «Сумма». В ячейку B18 вводится формула суммы значений ячеек этого столбца расчетной таблицы. Затем этой формулой надо заполнить диапазон C18:E18.
В ячейку F14 вводится надпись «Сумма». В ячейку F15 вводится формула суммы значений ячеек этой строки расчетной таблицы. Затем этой формулой заполняется диапазон F16:17.
В задаче необходимо найти такой план перевозок, для которого их суммарная стоимость минимальна в сравнении с другими планами.
Построим функцию V, которая вычисляет суммарную стоимость перевозок по заданному в модели плану.
Затраты на перевозку товара по одному маршруту «склад i — магазин j» будут равны произведению стоимости перевозки на ее объем. Стоимость перевозки задана в ячейке таблицы удельных затрат, а объем — в такой же ячейке таблицы плана перевозок.
Для построения функции V нужно перемножить значения соответствующих ячеек двух таблиц (диапазонов), а затем все произведения сложить. Введем формулу для вычисления функции V в ячейку А20.
Для ввода значения функции V используем математическую функцию СУММПРОИЗВ(), которая возвращает сумму произведений ячеек двух диапазонов (массивов).
Табличный курсор устанавливаем на ячейку A20, щелчком по кнопке Вставить функцию в строке формул, открываем окно Вставка функции. Проводим поиск функции по ее имени, затем дважды щелкаем по ее имени в списке. Открывается окно для ввода аргументов. В таблице выделяем диапазон B6:E8. Он записывается в поле аргумента Массив1. Затем в окне щелкаем по полю второго аргумента Массив2 и в таблице выделяем массив B15:E17. Щелчком по кнопке OK завершаем ввод.
Теперь следует вызвать окно Параметры поиска решения и ввести исходные данные для поиска решения.
Целевая функция V размещена в ячейке A20, критерий — Минимум, изменяя ячейки переменных диапазона B15:E17 (ячейки плана перевозок), переходим к вводу ограничений.
Нетрудно увидеть, что суммы в ячейках правее плана перевозок должны давать объемы поставок, которые заданы в правом столбце начальных данных. А суммы в ячейках ниже плана перевозок — объемы заявок, которые заданы в последней строке начальных данных.
Такие ограничения Excel позволяет задавать как равенства диапазонов.
Щелкаем по кнопке Добавить. В новом окне для ввода ограничений в левое поле выделяем диапазон B18:E18. Во втором поле выбираем знак равенства. В третье поле вводим диапазон B9:E9. Щелкаем по кнопке OK.
Аналогично вводим условие равенства диапазонов F15:F17 и F6:F8.
Так как товар штучный, следует ячейки диапазона B15:E17 плана перевозок сделать целыми и неотрицательными.
После настроек в окне Параметры поиска решения щелкаем по кнопке Найти решение.
Подтвержденные результаты решения задачи.
Задания по теме урока
1. Повторите на компьютере рассмотренное в параграфе построение компьютерной расчетной модели для транспортной задачи и найдите решение этой задачи.
2. Решите следующую транспортную задачу. Компания владеет тремя заводами, которые производят штучную продукцию одного вида и имеют производительность 600, 300, 330 единиц продукции в год. Компания обязалась за год поставить в четыре города соответственно 350, 350, 230 и 300 единиц продукции. Таблица удельных стоимостей перевозки имеет вид:
Домашнее задание
§ 15 электронного пособия, ответить на вопросы
Форма отправки
Форма видна только зарегистрированным пользователям.