1. Создайте следующую таблицу на Листе2 текущей рабочей книги Excel:
| A | B | C | D | E | F | G | H | I | J | |
| Запасы товара на складах, ед. | ||||||||||
| Наим. товара | Ростов | Москва | Ставрополь | Краснодар | ||||||
| Товар1 | ||||||||||
| Товар2 | ||||||||||
| Товар3 | ||||||||||
| Товар4 | ||||||||||
| Товар5 | ||||||||||
| Стоимость доставки единицы товара в г. Сочи, руб. | ||||||||||
| Наим. товара | Ростов | Москва | Ставрополь | Краснодар | ||||||
| Товар1 | ||||||||||
| Товар2 | ||||||||||
| Товар3 | ||||||||||
| Товар4 | ||||||||||
| Товар5 | ||||||||||
| Результаты расчета | ||||||||||
| Наим. товара | Ростов | Москва | Ставрополь | Краснодар | Итого | Max | Спрос | |||
| Товар1 | <= | = | ||||||||
| Товар2 | <= | = | ||||||||
| Товар3 | <= | = | ||||||||
| Товар4 | <= | = | ||||||||
| Товар5 | <= | = | ||||||||
| Цел. ф-ция |
Область ячеек, отмеченная серым цветом, предназначена для размещения результатов расчета: найденных значений объемов перевозок из каждого пункта отправления и значения целевой функции – суммарной стоимости перевозок.
2. В ячейки F22:F26 вставьте формулы для расчета суммарного объема перевозок по каждому товару (используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…):
F22 = СУММ(B22:E22);
F23 = СУММ(B23:E23);
F24 = СУММ(B24:E24);
F25 = СУММ(B25:E25);
F26 = СУММ(B26:E26).
3. Объем перевозок каждого товара не может превышать суммарного запаса этого товара, имеющегося на складах. В ячейки H22:H26 вставьте формулы для расчета предельных объемов перевозок по каждому товару (используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…):
H22 = СУММ(B4:E4);
H23 = СУММ(B5:E5);
H24 = СУММ(B6:E6);
H25 = СУММ(B7:E7);
H26 = СУММ(B8:E8).
4. Целевая функция описывает суммарную стоимость перевозок всех товаров. Стоимость перевозки каждого товара равна произведению стоимости перевозки единицы товара на количество перевозимого товара. Суммарная стоимость перевозок равна сумме стоимостей перевозок всех товаров. В ячейку B28 вставьте формулу для расчета целевой функции (используя кнопку Вставка функции панели инструментов или команду Вставка è Функция…):
B28 = СУММПРОИЗВ(B13:E17;B22:E26).
5. Для решения задачи вызовите меню Сервис è Поиск решения… В открывшемся диалоговом окне Поиск решения укажите:
· Установить целевую ячейку B28 Равной: минимальному значению;
· Изменяя ячейки B22:E26;
· Ограничения:
F22:F26 <= H22:H26 – объем перевозок товара не может быть больше имеющегося в данный момент на складах количества данного товара;
F22:F26 = J22:J26 – объем перевозок должен полностью удовлетворять спрос на товар;
B22:E26 >= 0 – объем перевозок по каждому товару должен быть больше или равен 0;
B22:E26 <= B4:E8 – объем перевозок по каждому товару из каждого пункта отправления не должен превышать имеющегося в данном пункте отправления запаса данного товара.
Для нахождения оптимального решения нажмите кнопку Выполнить. В результате получите значение целевой функции 3220282 руб. при следующих объемах перевозок:
| Наименование товара | Ростов | Москва | Ставрополь | Краснодар |
| Товар1 | ||||
| Товар2 | ||||
| Товар3 | ||||
| Товар4 | ||||
| Товар5 |
6. Сохраните текущую рабочую книгу: Файл è Сохранить.