Транспортне завдання в Excel. Пошук найкращого способу транспортування від продавця до покупця

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

Опис транспортного завдання

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

1. ЗАЧИНЕНО. У цьому випадку попит і пропозиція знаходяться в рівновазі.
2. ВІДЧИНЕНО. Тут немає рівності між попитом і пропозицією. Щоб отримати рішення цієї проблеми, необхідно спочатку привести її до першого типу, зрівнявши попит і пропозицію. Для цього потрібно ввести додатковий показник – наявність умовного покупця або продавця. Крім того, потрібно внести певні зміни до таблиці витрат.

Як увімкнути функцію пошуку рішення в Excel

Для вирішення транспортних задач в Excel є спеціальна функція «Пошук рішення». Він не ввімкнено за замовчуванням, тому вам потрібно виконати наступні кроки:

1. Відкрийте меню «Файл», яке знаходиться у верхньому лівому куті вікна програми.
2. Після цього натисніть на кнопку з параметрами.
3. Далі знаходимо підрозділ «Налаштування» і переходимо в меню управління доповненнями. Це невеликі програми, які працюють у середовищі Microsoft Excel. Бачимо, що спочатку ми клацнули по меню «Надбудови», а потім в правій нижній частині встановили пункт «Надбудови Excel» і натиснули кнопку «Перейти». Усі необхідні дії виділені червоними прямокутниками та стрілками.
4. Далі включаємо надбудову «Пошук рішення», після чого підтверджуємо свої дії натисканням кнопки ОК. Виходячи з опису налаштування, ми бачимо, що воно призначене для аналізу складних даних, таких як наукові та фінансові.
5. Після цього переходимо у вкладку «Дані», де бачимо нову кнопку, яка називається так само, як і надбудова. Його можна знайти в групі інструментів аналізу.

Залишається тільки натиснути на цю кнопку, і ми переходимо до вирішення транспортної задачі. Але перед цим ми повинні поговорити трохи більше про інструмент Solver в Excel. Це спеціальний додаток для Excel, який дозволяє знайти найшвидше рішення проблеми. Характерною особливістю є врахування обмежень, які користувач встановлює на етапі підготовки. Простіше кажучи, це підпрограма, яка дозволяє визначити найкращий спосіб досягнення певного завдання. Такі завдання можуть включати наступне:

1. Інвестування, завантаження складу або будь-яка інша подібна діяльність. Включаючи доставку товару.
2. Найкращий спосіб. Це включає такі цілі, як досягнення максимального прибутку за мінімальних витрат, як досягти найкращої якості за допомогою наявних ресурсів тощо.

Окрім транспортних завдань, це доповнення також використовується для таких цілей:

1. Розробка плану виробництва. Тобто скільки одиниць продукту необхідно виробити, щоб отримати максимальний дохід.
2. Знайти розподіл праці за різними видами робіт так, щоб загальні витрати на виробництво товару чи послуги були найменшими.
3. Встановіть мінімальний час, який знадобиться для виконання всієї роботи.

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

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

Надбудова Solver перераховує різні значення змінних, які передаються цільовій функції таким чином, щоб вона була максимальною, мінімальною або дорівнювала певному значенню (це саме обмеження). Є ще одна дещо схожа за принципом роботи функція, яку часто плутають з «Пошуком рішення». Він називається «Вибір параметрів». Але якщо копнути глибше, різниця між ними колосальна:

1. Функція Goal Seek не працює з більш ніж однією змінною.
2. Він не передбачає можливості встановлення обмежень на змінні.
3. Вона здатна визначити лише рівність цільової функції певному значенню, але не дає можливості знайти максимум і мінімум. Тому для нашого завдання він не підходить.
4. Здатний ефективно обчислювати лише модель лінійного типу. Якщо модель є нелінійною, то вона знаходить значення, яке найближче до початкового значення.

Транспортне завдання набагато складніше за своєю структурою, тому надбудови «Вибір параметрів» для цього недостатньо. Розглянемо докладніше, як реалізувати функцію «Пошук рішення» на практиці на прикладі транспортної задачі.

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

Щоб наочно продемонструвати, як на практиці розв'язувати транспортні задачі в Excel, наведемо приклад.

Умови задач

Припустимо, у нас є 6 продавців і 7 покупців. Попит і пропозиція між ними розподіляються відповідно таким чином: 36, 51, 32, 44, 35 і 38 одиниць є продавцями, а 33, 48, 30, 36, 33, 24 і 32 одиницями є покупцями. Якщо ви підсумуєте всі ці значення, то побачите, що попит і пропозиція знаходяться в рівновазі. Тому ця задача є закритого типу, яка вирішується дуже просто.

Крім того, у нас є інформація про те, скільки потрібно витратити на транспортування з пункту А в пункт Б (у прикладі вони виділені жовтими клітинками).

Рішення – покроковий алгоритм

Тепер, після того, як ми ознайомилися з таблицями з вихідними даними, ми можемо використовувати наступний алгоритм вирішення цієї задачі:

1. Спочатку ми робимо таблицю, що складається з 6 рядків і 7 стовпців.
2. Після цього переходимо до будь-якої клітинки, яка не містить жодних значень і при цьому лежить за межами новоствореної таблиці, і вставляємо функцію. Для цього натисніть кнопку fx, яка розташована зліва від рядка введення функції.
3. У нас є вікно, в якому потрібно вибрати категорію «Математика». Яка функція нас цікавить? Виділений на цьому скріншоті. функція SUMPRODUCT множить діапазони або масиви між собою та підсумовує їх. Саме те, що нам потрібно. Після цього натисніть клавішу ОК.
4. Далі на екрані з'явиться вікно, в якому потрібно вказати параметри функції. Вони такі:
   1. Масив 1. Це перший аргумент, в який ми записуємо діапазон, виділений жовтим. Ви можете встановити параметри функції за допомогою клавіатури або вибравши відповідну область лівою кнопкою миші.
   2. Масив 2. Це другий аргумент, який є новоствореною таблицею. Дії виконуються так само.

Підтвердіть свою дію, натиснувши кнопку ОК.

1. Після цього клацаємо лівою кнопкою миші по комірці, яка є верхньою лівою у новоствореній таблиці. Тепер знову натисніть кнопку вставки функції.
2. Вибираємо ту ж категорію, що і в попередньому випадку. Але цього разу нас цікавить функція SUM.
3. Тепер настає етап заповнення аргументів. В якості першого аргументу ми пишемо верхній рядок таблиці, яку ми створили на початку. Як і раніше, це можна зробити, виділивши ці комірки на аркуші, або вручну. Підтверджуємо свої дії натисканням кнопки ОК.
4. Ми побачимо результати в комірці з функцією. У цьому випадку це нуль. Далі перемістіть курсор в правий нижній кут, після чого з'явиться маркер автозаповнення. Виглядає як маленький чорний плюш. Якщо вона з'явилася, затисніть ліву кнопку миші і перемістіть курсор в останню клітинку нашої таблиці.
5. Це дає нам можливість перенести формулу в усі інші комірки та отримати правильні результати без додаткових обчислень.
6. Наступним кроком буде виділення верхньої лівої клітинки та вставлення функції SUM в неї. Після цього вводимо аргументи і за допомогою маркера автозаповнення заповнюємо всі клітинки, що залишилися.
7. Після цього переходимо безпосередньо до вирішення проблеми. Для цього ми скористаємося надбудовою, яку включили раніше. Переходимо у вкладку «Дані», там знаходимо інструмент «Пошук рішення». Натискаємо на цю кнопку.
8. Тепер перед нашими очима з'явилося вікно, через яке можна налаштувати параметри нашого доповнення. Давайте розглянемо кожен із цих варіантів:
   1. Оптимізуйте цільову функцію. Тут нам потрібно вибрати клітинку, що містить функцію SUMPRODUCT. Ми бачимо, що ця опція дозволяє вибрати функцію, для якої буде здійснюватися пошук рішення.
   2. Раніше. Тут ми встановлюємо опцію «Мінімум».
   3. Змінюючи комірки змінних. Тут ми вказуємо діапазон, що відповідає таблиці, яку ми створили на самому початку (за винятком підсумкового рядка та стовпця).
   4. З урахуванням обмежень. Тут нам потрібно додати обмеження, натиснувши кнопку «Додати».
   5. Пам'ятаємо, яке саме обмеження потрібно створити - сума значень вимог покупців і пропозицій продавців повинна бути однаковою.
9. Завдання обмежень виконується наступним чином:
   1. Посилання на клітинки. Тут ми вводимо діапазон таблиці для розрахунків.
   2. Терміни. Це математична операція, з якою перевіряється діапазон, указаний у першому полі введення.
   3. Значення умови або обмеження. Тут ми вводимо відповідний стовпець у вихідну таблицю.
   4. Після виконання всіх кроків натискаємо кнопку ОК, тим самим підтверджуючи наші дії.

Точно такі ж операції виконуємо для верхніх рядків, ставлячи таку умову: вони повинні бути рівними.

Наступний крок – встановлення умов. Для суми комірок у таблиці нам потрібно встановити такі критерії – більше або дорівнює нулю, ціле число. У результаті ми маємо такий перелік умов, за яких розв’язується задача. Тут потрібно переконатися, що встановлено прапорець біля опції «Зробити змінні без обмежень невід’ємними». Також у нашій ситуації потрібно вибрати метод розв’язання задачі – «Пошук розв’язку нелінійних задач методами ОПГ». Тепер можна сміливо сказати, що налаштування виконано. Тому залишається лише провести розрахунки. Для цього натисніть на кнопку «Знайти рішення».

Після цього всі дані будуть обчислені автоматично, а потім Excel покаже вікно з результатами. Це необхідно для того, щоб ще раз перевірити роботу комп'ютера, так як можливі помилки, якщо умови були попередньо виставлені неправильно. Якщо все вірно, то натисніть кнопку «ОК» і подивіться готову таблицю.

Якщо виявиться, що наша задача стала відкритого типу, то це погано, тому що потрібно відредагувати вихідну таблицю, щоб задача перетворилася на закриту. Однак, коли це буде зроблено, решта алгоритму буде тим самим.

Висновок

Як бачимо, Excel можна використовувати і для дуже складних розрахунків, які на перший погляд недоступні простій комп'ютерній програмі, яка встановлена ​​практично у всіх. Однак це так. Сьогодні ми вже розглянули просунутий рівень використання. Ця тема не така проста, але, як кажуть, дорогу здолає той, хто йде. Головне, дотримуватися плану дій і точно виконувати всі дії, зазначені вище. Тоді помилок не буде, і програма самостійно виконає всі необхідні обчислення. Не потрібно буде думати, яку функцію використовувати і так далі.