зміст
«Пошук рішення» — надбудова Excel, за допомогою якої можна вибрати найкраще рішення задач, виходячи із заданих обмежень. Функція дає можливість планувати співробітників, розподіляти витрати або інвестиції. Знаючи, як працює ця функція, ви заощадите час і зусилля.
Що таке пошук рішень
У поєднанні з різними іншими опціями в Excel є одна менш популярна, але вкрай потрібна функція «Пошук рішення». Незважаючи на те, що знайти його непросто, знайомство з ним і використання допомагає вирішити багато проблем. Опція обробляє дані і дає оптимальне рішення з дозволених. У статті описано, як безпосередньо працює Пошук рішення.
Як увімкнути функцію «Пошук рішення».
Незважаючи на ефективність, розглянута опція не знаходиться на видному місці панелі інструментів або контекстного меню. Більшість користувачів, які працюють в Excel, не знають про його наявність. За замовчуванням ця функція вимкнена, для її відображення виконайте такі дії:
- Відкрийте «Файл», натиснувши на відповідну назву.
- Натисніть на розділ «Налаштування».
- Потім виберіть підрозділ «Додатки». Тут будуть відображатися всі доповнення програми, внизу з'явиться напис «Управління». Праворуч від нього буде спливаюче меню, де ви повинні вибрати «Додатки Excel». Потім натисніть «Перейти».
1 - На моніторі з’явиться додаткове вікно «Надбудови». Поставте прапорець поруч із потрібною функцією та натисніть OK.
- Потрібна функція з’явиться на стрічці праворуч від розділу «Дані».
Про моделі
Ця інформація буде надзвичайно корисною для тих, хто тільки знайомиться з поняттям «модель оптимізації». Перед використанням «Пошуку рішення» рекомендується вивчити матеріали по методиці побудови моделей:
- розглянутий варіант дозволить визначити найкращий спосіб для виділення коштів на інвестиції, завантаження приміщень, поставку товарів або інші дії, де необхідно знайти найкраще рішення.
- «Оптимальним методом» у такій ситуації буде: збільшення доходу, зниження витрат, підвищення якості тощо.
Типові завдання оптимізації:
- Визначення виробничого плану, при якому прибуток від реалізації випущеної продукції буде максимальним.
- Визначення транспортних карт, під час яких витрати на транспортування мінімізовані.
- Пошук розподілу кількох машин для різних видів робіт, щоб знизити собівартість продукції.
- Визначення найкоротшого терміну виконання робіт.
Важливо! Для формалізації завдання необхідно створити модель, яка відображає основні параметри предметної області. В Excel модель — це набір формул, які використовують змінні. Розглянутий варіант шукає такі показники, щоб цільова функція була більшою (меншою) або дорівнювала вказаному значенню.
Підготовчий етап
Перш ніж розміщувати функцію на стрічці, потрібно зрозуміти, як ця опція працює. Наприклад, є інформація про продаж товарів, зазначених у таблиці. Завдання - призначити знижку на кожну позицію, яка склала б 4.5 млн руб. Параметр відображається всередині комірки під назвою target. На його основі розраховуються інші параметри.
Нашим завданням буде розрахувати знижку, на яку множаться суми продажу різних товарів. Ці 2 елементи з'єднані формулою, яка записується так: =D13*$G$2. Де в D13 записана загальна кількість для реалізації, а $G$2 — адреса потрібного елемента.
Використання функції та її налаштування
Коли формула готова, потрібно використовувати безпосередньо саму функцію:
- Потрібно перейти в розділ «Дані» і натиснути «Пошук рішення».
- Відкриється «Параметри», де задаються необхідні параметри. У рядку «Оптимізація цільової функції:» необхідно вказати клітинку, де відображається сума знижок. Координати можна прописати самостійно або вибрати з документа.
- Далі потрібно перейти до налаштувань інших параметрів. У розділі «Кому:» можна встановити максимальний і мінімальний ліміти або точне число.
- Потім заповнюється поле «Зміна значень змінних:». Тут вводяться дані потрібної клітинки, яка містить конкретне значення. Координати прописуються самостійно або клацається відповідна комірка в документі.
- Далі редагується вкладка «Згідно з обмеженнями:», де встановлюються обмеження щодо застосовуваних даних. Наприклад, десяткові дроби або від’ємні числа виключаються.
- Після цього відкривається вікно, яке дозволяє додати обмеження в розрахунках. Початковий рядок містить координати комірки або цілого діапазону. За умовами завдання вказуються дані потрібної комірки, де відображається індикатор знижки. Потім визначається знак порівняння. Для нього встановлено значення «більше або дорівнює», щоб остаточне значення не було зі знаком мінус. «Ліміт», установлений у рядку 3, у цій ситуації дорівнює 0. Також можна встановити обмеження за допомогою «Додати». Подальші кроки ті самі.
- Після виконання вищевказаних кроків у найбільшому рядку з’явиться встановлене обмеження. Список може бути великим і залежатиме від складності розрахунків, однак у конкретній ситуації достатньо 1 умови.
- Крім того, є можливість вибрати інші додаткові параметри. Унизу праворуч є опція «Параметри», яка дозволяє це зробити.
- У налаштуваннях можна встановити «Точність обмеження» та «Межі розчину». У нашій ситуації немає необхідності використовувати ці варіанти.
- Після завершення налаштувань запускається сама функція – натисніть «Знайти рішення».
- Після того, як програма виконає необхідні розрахунки і видає остаточні розрахунки в необхідні комірки. Потім відкривається вікно з результатами, де результати зберігаються / скасовуються, або параметри пошуку налаштовуються за новим. Коли дані відповідають вимогам, знайдене рішення зберігається. Якщо попередньо поставити позначку біля пункту «Повернутися до діалогового вікна пошуку рішення», відкриється вікно з параметрами функції.
- Є ймовірність, що розрахунки виявилися помилковими або є необхідність змінити вихідні дані, щоб отримати інші показники. У такій ситуації потрібно знову відкрити вікно налаштувань і ще раз перевірити інформацію.
- Якщо дані точні, можна використовувати альтернативний метод. Для цих цілей вам потрібно натиснути на поточний варіант і вибрати найбільш підходящий спосіб зі списку, що з'явиться:
- Пошук рішення за допомогою узагальненого градієнта для нелінійних задач. За замовчуванням використовується цей параметр, але можна використовувати й інші.
- Знаходження розв’язків лінійних задач на основі симплекс-методу.
- Використання еволюційного пошуку для виконання завдання.
Увага! Якщо перераховані вище варіанти не впоралися з поставленим завданням, слід ще раз перевірити дані в налаштуваннях, так як часто це основна помилка в таких завданнях.
- Коли бажана знижка отримана, залишається застосувати її для розрахунку суми знижки на кожну позицію. Для цього виділяється початковий елемент графи «Сума знижки», записується формула «=D2*$G$2» і натисніть «Enter». Знаки долара ставляться так, що при розтягуванні формули на сусідні рядки G2 не змінюється.
- Тепер буде отримано суму знижки на початковий товар. Потім слід навести курсор на кут комірки, коли він стає «плюсом», натискається ЛКМ і формула розтягується на потрібні рядки.
- Після цього стіл буде остаточно готовий.
Завантажити/зберегти параметри пошуку
Цей параметр корисний під час застосування різних параметрів обмежень.
- У меню «Параметри пошуку рішень» натисніть «Завантажити/зберегти».
- Введіть діапазон для області моделі та натисніть «Зберегти» або «Завантажити».
При збереженні моделі вводиться посилання на 1 комірку порожнього стовпця, де буде розміщена модель оптимізації. Під час завантаження моделі вводиться посилання на весь діапазон, який містить модель оптимізації.
Важливо! Для збереження останніх налаштувань у меню «Параметри рішення» зберігається робоча книга. Кожен аркуш у ньому має власні параметри надбудови Solver. Крім того, можна встановити більше ніж 1 завдання для аркуша, натиснувши кнопку «Завантажити або зберегти», щоб зберегти окремі завдання.
Простий приклад використання Solver
Завантажувати контейнер ємностями необхідно так, щоб його маса була максимальною. Бак має об'єм 32 куб. м. Наповнений ящик має масу 20 кг, його об'єм 0,15 куб. м. Ящик – 80 кг 0,5 куб. м. Необхідно, щоб загальна кількість контейнерів становила не менше 110 шт. Дані організовані таким чином:
Змінні моделі позначені зеленим кольором. Цільова функція виділена червоним кольором. Обмеження: за найменшою кількістю контейнерів (більше або дорівнює 110) та за вагою (=SUMPRODUCT(B8:C8;B6:C6) – загальна маса тари в тарі.
За аналогією розглянемо загальний обсяг: =SUMPRODUCT(B7:C7;B8:C8). Така формула необхідна для встановлення ліміту загального обсягу тари. Потім через «Пошук рішення» вводяться посилання на елементи зі змінними, формулами і самими показниками (або посилання на конкретні осередки). Звичайно, кількість контейнерів є цілим числом (це теж обмеження). Натискаємо «Знайти рішення», в результаті чого знаходимо таку кількість контейнерів, коли загальна маса максимальна і враховані всі обмеження.
Пошук рішення не вдалося знайти рішення
Таке сповіщення з’являється, коли відповідна функція не знайшла комбінацій балів змінних, які задовольняють кожне обмеження. При використанні симплексного методу цілком можливо, що розв’язку немає.
Коли використовується метод розв'язування нелінійних задач, у всіх випадках починаючи з початкових показників змінних, це свідчить про те, що можливе рішення далеке від таких параметрів. Якщо ви запускаєте функцію з іншими початковими індикаторами змінних, то, ймовірно, є рішення.
Наприклад, при використанні нелінійного методу елементи таблиці зі змінними не були заповнені, і функція не знаходила розв'язків. Це не означає, що рішення немає. Тепер з урахуванням результатів певної оцінки в елементи зі змінними вносять інші дані, близькі до отриманих.
У будь-якій ситуації ви повинні спочатку перевірити модель на відсутність конфлікту обмежень. Часто це пов'язано з неправильним підбором коефіцієнта або граничного показника.
У наведеному вище прикладі максимальний показник обсягу становить 16 куб. м замість 32, оскільки таке обмеження суперечить показникам щодо мінімальної кількості місць, адже вона відповідатиме кількості 16,5 куб. м.
Висновок
Виходячи з цього, опція «Пошук рішення» в Excel допоможе у вирішенні конкретних завдань, які досить важко або неможливо вирішити звичайними способами. Складність застосування цього методу полягає в тому, що спочатку ця опція прихована, тому більшість користувачів не підозрюють про її наявність. Крім того, функція досить складна для вивчення та використання, але при належному дослідженні вона принесе велику користь і полегшить обчислення.