Решение Транспортной Задачи в Excel

Для решения этой транспортной задачи в Excel нам сначала нужно составить математическую модель, а затем настроить инструмент «Поиск решения». 1. Математическая модель задачи Пусть \( x_{ij} \) — количество продукции, перевозимой от \( i \)-го поставщика к \( j \)-му потребителю. Запасы поставщиков (\( a_i \)): \[ a_1 = 180, \quad a_2 = 350, \quad a_3 = 210 \] Потребности заказчиков (\( b_j \)): \[ b_1 = 110, \quad b_2 = 90, \quad b_3 = 120, \quad b_4 = 80, \quad b_5 = 150 \] Проверим баланс задачи: Суммарные запасы: \( 180 + 350 + 210 = 740 \) Суммарные потребности: \( 110 + 90 + 120 + 80 + 150 = 550 \) Запасы превышают потребности (\( 740 > 550 \)), значит, задача открытая. В Excel мы будем использовать ограничения типа \( \le \) для поставщиков. Целевая функция (минимизация затрат): \[ Z = \sum_{i=1}^{3} \sum_{j=1}^{5} c_{ij} x_{ij} \to \min \] Где \( c_{ij} \) — элементы матрицы затрат из условия. 2. Инструкция для реализации в Excel Для оформления в тетрадь и переноса в таблицу: Шаг 1. Создайте таблицу затрат (ячейки A1:F4): Введите матрицу из условия. В строках — поставщики (П1, П2, П3), в столбцах — потребители (М1, М2, М3, М4, М5). Шаг 2. Создайте таблицу для плана поставок (ячейки A7:F10): Это будут пустые ячейки, которые Excel заполнит значениями \( x_{ij} \). Добавьте справа столбец «Сумма по строке» (используйте формулу \( =СУММ(B8:F8) \)). Добавьте снизу строку «Сумма по столбцу» (используйте формулу \( =СУММ(B8:B10) \)). Шаг 3. Целевая функция: В отдельной ячейке (например, B12) введите формулу: \( =СУММПРОИЗВ(B2:F4; B8:F10) \) Эта формула умножит количество товара на стоимость перевозки и всё сложит. Шаг 4. Настройка «Поиска решения» (Данные -> Поиск решения): 1. Оптимизировать целевую функцию: укажите ячейку B12. 2. До минимума. 3. Изменяя ячейки переменных: укажите диапазон плана поставок (B8:F10). 4. Ограничения: - Суммы по строкам (план) \( \le \) Запасы (180, 350, 210). - Суммы по столбцам (план) \( = \) Потребности (110, 90, 120, 80, 150). - Все ячейки плана \( \ge 0 \). 5. Метод решения: Симплекс-метод. 3. Результат решения (Оптимальный план) После запуска «Поиска решения» вы получите следующие распределения поставок: От П1 (180 ед.): - Потребителю М3: 120 ед. - Потребителю М4: 60 ед. От П2 (350 ед.): - Потребителю М1: 110 ед. - Потребителю М2: 90 ед. - Потребителю М4: 20 ед. - Потребителю М5: 130 ед. (остаток 0) От П3 (210 ед.): - Потребителю М5: 20 ед. (Остальные 190 ед. у П3 останутся на складе, так как потребности полностью удовлетворены). Минимальные затраты составят: \[ Z = (120 \cdot 4) + (60 \cdot 6) + (110 \cdot 1) + (90 \cdot 8) + (20 \cdot 5) + (130 \cdot 3) + (20 \cdot 4) \] \[ Z = 480 + 360 + 110 + 720 + 100 + 390 + 80 = 2240 \] Ответ: Минимальные затраты на перевозку составляют 2240 единиц. План поставок: П1 поставляет М3(120) и М4(60); П2 поставляет М1(110), М2(90), М4(20) и М5(130); П3 поставляет М5(20).