Постановка проблеми
Припустимо, що компанія, в якій ви працюєте, має три склади, звідки товари надходять в п'ять ваших магазинів, розкиданих по всій Москві.
Кожен магазин здатний продати певну кількість відомого нам товару. Кожен зі складів має обмежену місткість. Завдання полягає в тому, щоб раціонально вибрати, з якого складу в які магазини доставити товар, щоб мінімізувати загальні витрати на транспортування.
Перед початком оптимізації необхідно буде скласти на аркуші Excel просту таблицю – нашу математичну модель, що описує ситуацію:
Зрозуміло, що:
- Світло-жовта таблиця (C4:G6) описує вартість доставки одного товару з кожного складу до кожного магазину.
- Фіолетові клітинки (C15:G14) описують кількість товарів, необхідну для продажу кожному магазину.
- Червоні клітинки (J10:J13) відображають місткість кожного складу – максимальну кількість товару, яку може вмістити склад.
- Жовті (C13:G13) і сині (H10:H13) клітинки – це суми рядків і стовпців для зелених клітинок відповідно.
- Загальна вартість доставки (J18) розраховується як сума добутків кількості товарів і відповідних витрат на доставку – для розрахунку тут використовується функція SUMPRODUCT (SUMPRODUCT).
Таким чином, наше завдання зводиться до підбору оптимальних значень зелених клітин. І щоб загальна сума по лінії (сині клітинки) не перевищувала місткість складу (червоні клітинки), і при цьому кожен магазин отримував ту кількість товару, яку йому необхідно продати (сума по кожному магазину в жовті клітини повинні бути максимально наближені до вимог – фіолетові).
рішення
У математиці такі задачі вибору оптимального розподілу ресурсів сформульовані й описані давно. І, звичайно, шляхи їх вирішення вже давно розроблені не шляхом тупого перерахування (яке дуже довго), а в дуже невеликій кількості ітерацій. Excel надає користувачеві такі функції за допомогою надбудови. Пошук рішень (розв'язник) із вкладки дані (Дата):
Якщо на вкладці дані у вашому Excel такої команди немає – нічого страшного – це означає, що надбудова просто ще не підключена. Щоб активувати його, відкрийте філе, А потім виберіть параметри - доповнення - МЕНЮ (Параметри — Надбудови — Перейти). У вікні, яке відкриється, ставимо галочку біля потрібного нам рядка Пошук рішень (розв'язник).
Давайте запустимо надбудову:
У цьому вікні потрібно задати наступні параметри:
- Оптимізація цільової функції (Встановити tгроші клітинка) – тут необхідно вказати кінцеву основну мету нашої оптимізації, тобто рожевий ящик із загальною вартістю доставки (J18). Цільову комірку можна мінімізувати (якщо це витрати, як у нашому випадку), максимізувати (якщо це, наприклад, прибуток) або спробувати довести її до заданого значення (наприклад, точно вписатися в виділений бюджет).
- Зміна клітинок змінних (By заміна клітини) – тут ми вказуємо зелені клітинки (C10: G12), варіюючи значення яких ми хочемо досягти нашого результату – мінімальної вартості доставки.
- Відповідно до обмежень (Тема до обмеження) – перелік обмежень, які необхідно враховувати при оптимізації. Щоб додати обмеження до списку, натисніть кнопку додавати (додати) і введіть умову у вікні, що з’явиться. У нашому випадку це буде обмеження попиту:
та обмеження максимального обсягу складів:
Крім очевидних обмежень, пов'язаних з фізичними факторами (місткість складів і транспортних засобів, бюджетні та часові обмеження і т.д.), іноді необхідно додати обмеження «спеціально для Excel». Так, наприклад, Excel може легко організувати для вас «оптимізацію» вартості доставки, запропонувавши транспортувати товар із магазинів назад на склад – витрати стануть від’ємними, тобто ми отримаємо прибуток! 🙂
Щоб цього не сталося, найкраще залишити прапорець увімкненим. Зробіть необмежену кількість змінних невід’ємними або навіть іноді явно прописувати такі моменти в списку обмежень.
Після встановлення всіх необхідних параметрів вікно має виглядати так:
У випадаючому списку Виберіть метод розв’язування додатково потрібно вибрати відповідний математичний метод розв’язування на вибір із трьох варіантів:
- Симплексний метод це простий і швидкий метод розв’язування лінійних задач, тобто задач, де вихід лінійно залежить від входу.
- Загальний метод зниженого градієнта (OGG) – для нелінійних задач, де існують складні нелінійні залежності між вхідними та вихідними даними (наприклад, залежність продажів від витрат на рекламу).
- Еволюційний пошук рішення – відносно новий метод оптимізації, заснований на принципах біологічної еволюції (привіт Дарвін). Цей метод працює в рази довше перших двох, але може вирішити практично будь-яку задачу (нелінійну, дискретну).
Наша задача чітко лінійна: поставив 1 штуку – витратив 40 рублів, поставив 2 штуки – витратив 80 рублів. тощо, тому симплексний метод є найкращим вибором.
Тепер, коли дані для розрахунку введені, натисніть кнопку Знайдіть рішення (розв'язати)щоб почати оптимізацію. У важких випадках з великою кількістю мінливих комірок і обмежень пошук рішення може зайняти багато часу (особливо з еволюційним методом), але наше завдання для Excel не буде проблемою – через кілька хвилин ми отримаємо такі результати :
Зверніть увагу на те, як цікаво розподілялися обсяги поставок між магазинами, не перевищуючи місткість наших складів і задовольняючи всі заявки на необхідну кількість товару для кожного магазину.
Якщо знайдене рішення нас влаштовує, то ми можемо зберегти його, або відкотитися до вихідних значень і спробувати ще раз з іншими параметрами. Ви також можете зберегти вибрану комбінацію параметрів як сценарій. За бажанням користувача Excel може побудувати три види Звіти по задачі, що розв'язується, на окремих аркушах: звіт про результати, звіт про математичну стійкість розв'язку і звіт про межі (обмеження) розв'язку, проте в більшості випадків вони цікаві лише спеціалістам. .
Однак бувають ситуації, коли Excel не може знайти відповідного рішення. Змоделювати такий випадок можна, якщо вказати в нашому прикладі потреби магазинів у розмірі, що перевищує сумарну місткість складів. Тоді, виконуючи оптимізацію, Excel намагатиметься максимально наблизитися до рішення, а потім відображатиме повідомлення про те, що рішення неможливо знайти. Тим не менш, навіть у цьому випадку ми маємо багато корисної інформації – зокрема, ми можемо побачити «слабкі ланки» наших бізнес-процесів і зрозуміти напрямки для вдосконалення.
Розглянутий приклад, звичайно, відносно простий, але легко масштабується для вирішення набагато складніших завдань. Наприклад:
- Оптимізація розподілу фінансових ресурсів за статтями витрат у бізнес-плані або бюджеті проекту. Обмеженнями, в даному випадку, будуть обсяг фінансування та терміни реалізації проекту, а метою оптимізації є максимізація прибутку та мінімізація витрат проекту.
- Оптимізація розкладу співробітників з метою мінімізації фонду оплати праці підприємства. Обмеженнями, в даному випадку, будуть побажання кожного працівника згідно з графіком зайнятості та вимогами штатного розкладу.
- Оптимізація інвестиційних вкладень – необхідність правильного розподілу коштів між декількома банками, цінними паперами чи акціями підприємств з метою, знову ж таки, максимізації прибутку або (що важливіше) мінімізації ризиків.
У будь-якому випадку доп Пошук рішень (розв'язувач) є дуже потужним і красивим інструментом Excel і вартий вашої уваги, оскільки він може виручити в багатьох складних ситуаціях, з якими вам доводиться стикатися в сучасному бізнесі.