Методика решения задачи в MS Excel

Компания имеет 3 склада, территориально расположенных в различных районах города. Заказы на перевозку грузов поступают из сети 5 магазинов, расположенных по всей территории города.

Склады	Магазины
М1	М2	М3	М4	М5
Номер склада	Запас	Стоимость перевозок
S1
S2
S3
	Потребности магазинов

Решение:

Цель задачи:

Удовлетворить потребность 5 розничных магазинов в товарах, находящихся на 3 складах и сохранить при этом общие расходы на перевозку на минимальном уровне.

Построение математической модели:

Обозначим

X_ij - количество продукции, отправляемой со склада i в магазин j

C_ij – стоимость перевозкиединицы продукции со склада i в магазин j

Математическая модель будет состоять из ряда ограничений:

a) Исходя из физического смысла задачи X_ij ³0;C_ij³0

b) Ограничения по предложению (со склада нельзя вывезти продукции больше, чем там имеется).

(1)

c) Ограничения по спросу (в магазины следует завести не меньше продукции, чем её требуется):

(2)

Общая стоимость перевозок (целевая функция) равна: (3)

Необходимо определить такие неотрицательные значения переменных X_ij, которые удовлетворяют ограничениям (1) и (2) и обращают в минимум целевую функцию Z (3). В такой постановке задача является транспортной задачей линейного программирования.

Необходимым и достаточным условием разрешимости транспортной задачи является условие баланса:

(4)

Где - суммарное количество продукции на складах

(при этом - количество продукции на одном складе)

- суммарное количество продукции, требуемое в магазинах

(при этом - количество продукции, которое требуется j- му магазину).

В нашем случае

=15+20+20=60; =20+12+5+8+15=60

= =60

Следовательно, задача с балансом.

Разработка ЭТ с начальным планом решения

ЭТ приведена на рис. 1 – режим вычислений, рис.2- режим показа формул.

1. Подготовка блока ячеек с исходными данными

В ячейках В5:В7 помещаем сведения о наличии продукции на складах. В ячейках С9:G9- сведения о потребностях магазинов. В ячейках С5:G7 – данные о стоимости перевозок единицы продукции со складов в магазин.

2. Построим начальный план перевозок

Считаем, что с каждого склада в каждый магазин везут одну деталь (ячейки С11: G13 заполним единицами)

3. Вычислим количество перевозимой продукции

а) В ячейку В11 – введем формулу для вычисления количества продукции, вывозимой с 1-го склада:

=СУММ(С11:G11).

Аналогично в ячейки В12, В13 введём формулы для вычисления количества продукции, вывозимой со второго и третьего складов:

=СУММ(С12:G12).

=СУММ(С13:G13).

Для начального плана перевозок все суммы равны 5.

Б). В ячейку С15 введём формулу для вычисления количества продукции, которую везём в первый магазин

=СУММ(С11:С13).

Аналогично в ячейки D15:G15 введём формулы для вычисления количества продукции, которую везём во 2-ой, 3-ий, 4-й, 5-й магазины.

В ячейку D15 =СУММ(D11:D13)

В ячейку E15 =СУММ(E11:E13)

В ячейку F15 =СУММ(F11:F13)

В ячейку G15 =СУММ(G11:G13).

4. Определим стоимость перевозок в каждый из магазинов

Для определения стоимости перевозок в 1-й магазин т.е.

Z₁=X₁₁C₁₁+X₁₂C₁₂+X₁₃C₁₃

Введём в ячейку С16 формулу:

=СУММПРОИЗВ(С5:С7;С11:С13).

В ЯЧЕЙКУ D16 введём формулу для вычисления стоимости перевозок в остальные магазины: =СУММПРОИЗВ(D5:D7;D11:D13).

В ячейке E16 =СУММПРОИЗВ(E5:E7;E11:E13);

В ячейке F16 =СУММПРОИЗВ(F5:F7;F11:F13);

В ячейке G16 =СУММПРОИЗВ(G5:G7;G11:G13).

5. Определим общую стоимость перевозок (целевую функцию ЦФ)

Z=Z₁+Z₂+Z₃+Z₄+Z₅ (5)

Введём в ячейку В17 формулу:

=СУММ(С16:G16)

Для нашего начального плана получится 44.

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

Дата добавления: 2015-06-28