Транспортне завдання в Microsoft Excel

Зміст

• Загальний опис транспортної задачі
• Інструменти для вирішення транспортної задачі в Ексель
• Приклад вирішення транспортної задачі в Excel
  • Умови завдання
  • Рішення задачі
• Питання та відповіді

Транспортна задача являє собою задачу пошуку найбільш оптимального варіанту перевезень однотипного товару від постачальника до споживача. Її основою є модель, широко застосовується в різних сферах математики та економіки. У Microsoft Excel є інструменти, які значно полегшують вирішення транспортної задачі. З'ясуємо, як їх використовувати на практиці.

Загальний опис транспортної задачі

Головною метою транспортної задачі є пошук оптимального плану перевезень від постачальника до споживача при мінімальних витратах. Умови такого завдання записуються у вигляді схеми або матриці. Для програми Excel використовується матричний тип.

Якщо загальний обсяг товару на складах Постачальника дорівнює величині попиту, транспортна задача іменується закритою. Якщо ці показники не рівні, то таку транспортну задачу називають відкритою. Для її вирішення умови слід привести до закритого типу. Для цього додають фіктивного продавця або фіктивного покупця з запасами або потребами рівними різниці між попитом і пропозицією в реальній ситуації. При цьому в таблиці витрат додається додатковий стовпець або рядок з нульовими значеннями.

Інструменти для вирішення транспортної задачі в Ексель

Для вирішення транспортної задачі в Excel використовується функція &171; пошук рішення & 187; . Проблема в тому, що за замовчуванням вона відключена. Для того, щоб включити даний інструмент, потрібно виконати певні дії.

1. Робимо переміщення у вкладку " Файл» .

2. Натискаємо по підрозділу»Параметри" .

3. У новому вікні переходимо по напису «надбудови» .

4. В блоці «Управління» , який знаходиться внизу вікна, в випадаючому списку зупиняємо вибір на пункті «Надбудови Excel» . Робимо клік по кнопці " Перейти...» .

5. Запускається вікно активації надбудов. Встановлюємо прапорець біля пункту " пошук рішення» . Кількома по кнопці «OK» .

6. Внаслідок цих дій у вкладці»дані" у блоці налаштувань " аналіз» на стрічці з'явиться кнопка " пошук рішення» . Вона нам і знадобиться при пошуку рішення транспортної задачі.

Урок: функція & 171; пошук рішення & 187; в Екселі

Приклад вирішення транспортної задачі в Excel

Тепер давайте розберемо конкретний приклад вирішення транспортної задачі.

Умови завдання

Маємо 5 постачальників і 6 покупців. Обсяги виробництва цих постачальників становлять 48, 65, 51, 61, 53 одиниць. Потреба покупців: 43, 47, 42, 46, 41, 59 одиниць. Таким чином, загальний обсяг пропозиції дорівнює величині попиту, тобто, ми маємо справу із закритою транспортної завданням.

Крім того, за умовою дана матриця витрат перевезень з одного пункту в інший, яка відображена на ілюстрації нижче зеленим кольором.

Рішення задачі

Перед нами стоїть завдання за умов, про які було сказано вище, звести транспортні витрати до мінімуму.

1. Для того, щоб вирішити задачу, будуємо таблицю з точно такою ж кількістю осередків, як і у вищеописаної матриці витрат.

2. Виділяємо будь-яку порожню клітинку на аркуші. Кількома по значку " Вставити функцію» , розміщеному зліва від рядка формул.

3. Відкривається &171;Майстер функцій&187;. У списку, який пропонує Він, нам слід відшукати функцію СУММВИРОБ . Виділяємо її і тиснемо на кнопку «OK» .

4. Відкривається вікно введення аргументів функції СУММВИРОБ . В якості першого аргументу внесемо діапазон осередків матриці витрат. Для цього достатньо виділити курсором дані осередки. Другим аргументом виступить діапазон осередків таблиці, яка була приготовлена для розрахунків. Потім, тиснемо на кнопку «OK» .

5. Натискаємо по осередку, яка розташована зліва від верхньої лівої комірки таблиці для розрахунків. Як і минулого разу викликаємо Майстер функцій, відкриваємо в ньому аргументи функції сума . Клікнувши по полю першого аргументу, виділяємо весь верхній ряд осередків таблиці для розрахунків. Після того, як їх координати занесені у відповідне поле, натискаємо на кнопку «OK» .

6. Стаємо в нижній правий кут комірки з функцією сума . З'являється маркер заповнення. Тиснемо на ліву кнопку миші і тягнемо маркер заповнення вниз до кінця таблиці для розрахунку. Таким чином ми скопіювали формулу.

7. Натискаємо по осередку розміщеної зверху від верхньої лівої комірки таблиці для розрахунків. Як і в попередній раз викликаємо функцію сума , але на цей раз в якості аргументу використовуємо перший стовпець таблиці для розрахунків. Тиснемо на кнопку «OK» .

8. Копіюємо маркером заповнення формулу на весь рядок.

9. Переходимо у вкладку»дані" . Там в блоці інструментів " аналіз» натискаємо на кнопку " пошук рішення» .

10. Відкриваються параметри пошуку рішення. У полі " оптимізувати цільову функцію» вказуємо клітинку, що містить функцію СУММВИРОБ . У блоці «До» встановлюємо значення " мінімум» . У полі " зміна комірок змінних» вказуємо весь діапазон таблиці для розрахунку. У блоці налаштувань " відповідно до обмежень» тиснемо на кнопку »додати" , щоб додати кілька важливих обмежень.

11. Запускається вікно додавання обмеження. Перш за все, нам потрібно додати умову, що сума даних у рядках таблиці для розрахунків повинна дорівнювати сумі даних у рядках таблиці з умовою. У полі " посилання на клітинки» вказуємо діапазон суми в рядках таблиці розрахунків. Потім виставляємо знак дорівнює (=). У полі »обмеження" вказуємо діапазон сум в рядках таблиці з умовою. Після цього, тиснемо на кнопку «OK» .

12. Аналогічним чином додаємо умова, що стовпці двох таблиць повинні бути рівні між собою. Додаємо обмеження, що сума діапазону всіх осередків в таблиці для розрахунку повинна бути більшою або рівною 0, а також умова, що вона повинна бути цілим числом. Загальний вигляд обмежень повинен бути таким, як представлений на зображенні нижче. Обов'язково простежте, щоб біля пункту " зробити змінні без обмежень невід'ємними» стояла галочка, а методом рішення був обраний "пошук рішення нелінійних задач методом ОЗУ" . Після того, як всі налаштування вказані, тиснемо на кнопку " знайти рішення» .

13. Після цього відбувається розрахунок. Дані виводяться в осередки таблиці для розрахунку. Відкривається вікно результатів пошуку рішення. Якщо результати вас задовольняють, тисніть на кнопку «OK» .

Як бачимо, рішення транспортної задачі в Excel зводиться до правильного формування вступних даних. Самі розрахунки виконує замість користувача програма.