Задача № 4

Компания, занимающаяся ремонтом автомобильных дорог, в следующем месяце будет проводить ремонтные работы на пяти участках автодорог. Песок на участки ремонтных работ может поставляться из трех карьеров, месячные объемы предложений по карьерам известны. Из планов производства ремонтных работ известны месячные объемы потребностей по участкам работ. Имеются экономические оценки транспортных затрат (в у.е.) на перевозку 1 тонны песка с карьеров на ремонтные участки.

Таблица 4

Участок работ Карьер	В1	В2	В3	В4	В5	Предложение
А1	4	2	3	4	1	60
А2	2	4	3	5	6	90
А3	6	5	4	6	2	140
Потребности	40	30	90	80	50

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

Решение:

Объединяем диапазоны ячеек A2:F2 и A7:F7 и вводим заголовки матриц «Матрица перевозок (изменяемые ячейки)» и «Исходные данные» соответственно. В диапазон В3:F5 вводим 1, т.е. резервируем место для значений распределения поставок, обеспечивающих минимальные затраты на перевозку песка.

В таблицу «Исходные данные» в ячейки А9:А11 вводим значения из столбца «Предложение» Таблицы 4: 60, 90, 140 соответственно, в ячейки В8:F8 — значения потребностей: 40, 30, 90, 80, 50 соответственно, в ячейки В9:F11 - экономические оценки транспортных затрат. В итоге получим следующие т аблицы:

Рисунок 18

Введем условия мощностей поставщиков. Для этого в ячейку А3 введем формулу: «=СУММ(В3:F3)», скопируем эту формулу в ячейки А4, А5.

В ведем условия удовлетворения запросов потребителей: в ячейку В6 введем формулу «=СУММ (В3:В5)» и скопируем ее для ячеек С6:F6.

Рисунок 19

В ячейке А15 вводим «min», в ячейке В15 вводим формулу: Вставка → Функция → Математические → СУММПРОИЗВ → в поле Массив 1 указать диапазон В9:F11, Массив 2 - В3:F5 → ОК.

Для получения оптимального плана перевозок воспользуемся «Поиском решения». Сервис → Посик решения:

• целевая ячейка — В15,

• ввести следующие ограничения: А3:А5 = А9:А11, В6:F6 = В8:F8,

• нажать кнопку «Параметры»,

• установить флажок «Неотрицательные значения» и «Линейная модель»,

• ОК,

• Выполнить.

В результате получим решение данной задачи:

Рисунок 20

Рисунок 21

Формульный вид