Транспортна задача в Microsoft Excel
Транспортна задача являє собою задачу пошуку найбільш оптимального варіанту перевезень однотипного товару від виробника до споживача. Її основою є модель, широко застосовувана в різних сферах математики і економіки. У Microsoft Excel є інструменти, які значно полегшують рішення транспортної задачі. З'ясуємо, як їх використовувати на практиці.
зміст
Загальний опис транспортної задачі
Головною метою транспортної задачі є пошук оптимального плану перевезень від постачальника до споживача при мінімальних витратах. Умови такого завдання записуються у вигляді схеми або матриці. Для програми Excel використовується матричний тип.
Якщо загальний обсяг товару на складах постачальника дорівнює величині попиту, транспортна задача називається закритою. Якщо ці показники не рівні, то таку транспортну задачу називають відкритою. Для її вирішення умови слід привести до закритого типу. Для цього додають фіктивного продавця або фіктивного покупця з запасами або потребами рівними різниці між попитом і пропозицією в реальній ситуації. При цьому в таблиці витрат додається додатковий стовпець або рядок з нульовими значеннями.
Інструменти для вирішення транспортної задачі в Ексель
Для вирішення транспортної задачі в Excel використовується функція «Пошук рішення». Проблема в тому, що за замовчуванням вона відключена. Для того, щоб включити цей інструмент, потрібно виконати певні дії.
- Робимо переміщення у вкладку «Файл».
- Кількома по підрозділу «Параметри».
- У новому вікні переходимо по напису «Надбудови».
- У блоці «Управління», який знаходиться внизу вікна, в випадаючому списку зупиняємо вибір на пункті «Надбудови Excel». Робимо клік по кнопці «Перейти ...».
- Запускається вікно активації надбудов. Встановлюємо прапорець біля пункту «Пошук рішення». Кількома по кнопці «OK».
- Внаслідок цих дій у вкладці «Дані» в блоці налаштувань «Аналіз» на стрічці з'явиться кнопка «Пошук рішення». Вона нам і знадобиться при пошуку вирішення транспортної задачі.
урок: функція «Пошук рішення» в Ексель
Приклад рішення транспортної задачі в Excel
Тепер давайте розберемо конкретний приклад вирішення транспортної задачі.
умови завдання
Маємо 5 постачальників і 6 покупців. Обсяги виробництва цих постачальників складають 48, 65, 51, 61, 53 одиниць. Потреба покупців: 43, 47, 42, 46, 41, 59 одиниць. Таким чином, загальний обсяг пропозиції дорівнює величині попиту, тобто, ми маємо справу із закритою транспортної завданням.
Крім того, за умовою дана матриця витрат перевезень з одного пункту в інший, яка відображена на ілюстрації нижче зеленим кольором.
Рішення завдання
Перед нами стоїть завдання за умов, про які було сказано вище, звести транспортні витрати до мінімуму.
- Для того, щоб вирішити задачу, будуємо таблицю з точно такою ж кількістю осередків, як і у вищеописаної матриці витрат.
- Виділяємо будь-яку вільну позицію на аркуші. Кількома по значку «Вставити функцію», розміщеному зліва від рядка формул.
- Відкривається «Майстер функцій». У списку, який пропонує він, нам слід відшукати функцію СУММПРОИЗВ. Виділяємо її і тиснемо на кнопку «OK».
- Відкривається вікно введення аргументів функції СУММПРОИЗВ. Як перший аргумент внесемо діапазон комірок матриці витрат. Для цього досить виділити курсором дані осередки. Другим аргументом виступить діапазон комірок таблиці, яка була приготовлена для розрахунків. Потім, тиснемо на кнопку «OK».
- Кількома по осередку, яка розташована зліва від верхньої лівої комірки таблиці для розрахунків. Як і минулого разу викликаємо Майстер функцій, відкриваємо в ньому аргументи функції СУММ. Клікнувши по полю першого аргументу, виділяємо весь верхній ряд елементів таблиці для розрахунків. Після того, як їх координати занесені у відповідне поле, натискаємо на кнопку «OK».
- Стаємо в нижній правий кут комірки з функцією СУММ. З'являється маркер заповнення. Тиснемо на ліву кнопку миші і тягнемо маркер заповнення вниз до кінця таблиці для розрахунку. Таким чином ми скопіювали формулу.
- Кількома по осередку розміщеної зверху від верхньої лівої комірки таблиці для розрахунків. Як і в попередній раз викликаємо функцію СУММ, але на цей раз в якості аргументу використовуємо перший стовпець таблиці для розрахунків. Тиснемо на кнопку «OK».
- Копіюємо маркером заповнення формулу на весь рядок.
- Переходимо у вкладку «Дані». Там в блоці інструментів «Аналіз» натискаємо на кнопку «Пошук рішення».
- Відкриваються параметри пошуку рішення. В поле «Оптимізувати цільову функцію» вказуємо осередок, що містить функцію СУММПРОИЗВ. У блоці «До» встановлюємо значення «Мінімум». В поле «Змінюючи осередки змінних» вказуємо весь діапазон таблиці для розрахунку. У блоці налаштувань «Згідно з обмеженнями» тиснемо на кнопку «Додати», щоб додати кілька важливих обмежень.
- Запускається вікно додавання обмеження. Перш за все, нам потрібно додати умову того, що сума даних в рядках таблиці для розрахунків повинна дорівнювати сумі даних у рядках таблиці з умовою. В поле «Посилання на осередки» вказуємо діапазон суми в рядках таблиці розрахунків. Потім виставляємо знак рівності (=). В поле «Обмеження» вказуємо діапазон сум в рядках таблиці з умовою. Після цього, тиснемо на кнопку «OK».
- Аналогічним чином додаємо умова, що стовпці двох таблиць повинні бути рівні між собою. Додаємо обмеження, що сума діапазону всіх осередків в таблиці для розрахунку повинна бути більшою або рівною 0, а також умова, що вона повинна бути цілим числом. Загальний вигляд обмежень повинен бути таким, як представлений на зображенні нижче. Обов'язково простежте, щоб біля пункту «Зробити змінні без обмежень невід'ємними» стояла галочка, а методом вирішення був обраний «Пошук рішення нелінійних задач методом ОПГ». Після того, як всі налаштування вказані, тиснемо на кнопку «Знайти рішення».
- Після цього відбувається розрахунок. Дані виводяться в осередку таблиці для розрахунку. Відкривається вікно результатів пошуку рішення. Якщо результати вас задовольняють, тисніть на кнопку «OK».
Як бачимо, рішення транспортної задачі в Excel зводиться до правильного формування вступних даних. Самі розрахунки виконує замість користувача програма.