Завантаження надбудови "Пошук рішення" в Excel

Applies To Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel для iPhone iPhone для планшетів з Android Excel для телефонів з Android Excel Mobile

"Пошук рішення" - це програмна надбудова для Microsoft Office Excel, яка доступна при установці Microsoft Office або Excel.

Щоб можна було працювати з надбудовою "Пошук рішення", її потрібно спочатку завантажити до Excel.

1. Перейдіть до розділ Установки > файлів.
2. Виберіть команду Надбудови, а потім у полі Управління виберіть пункт Надбудови Excel.
3. Натисніть кнопку Перейти.
4. У вікні Доступні надбудови встановіть прапорець Пошук рішення та натисніть кнопку ОК.

• Якщо надбудова Пошук рішення відсутня у списку поля Доступні надбудови, натисніть кнопку Оглядзнайти її.
• Якщо з'явиться повідомлення про те, що надбудова "Пошук рішення" не встановлена ​​на комп'ютері, натисніть кнопку Так, щоб встановити її.

1. У меню Сервіс виберіть Надбудови Excel.
2. У полі Доступні надбудови встановіть прапорець Пошук рішення та натисніть кнопку ОК.
   • Якщо надбудова Пошук рішення відсутня у списку поля Доступні надбудовинатисніть кнопку Оглядзнайти її.
   • Якщо з'явиться повідомлення про те, що надбудова "Пошук рішення" не встановлена ​​на комп'ютері, натисніть у діалоговому вікні кнопку Так, щоб встановити.

Після завантаження надбудови "Пошук рішення" на вкладці Дані стане доступна кнопка Пошук рішення.

В даний час надбудова "Пошук рішень", що надається компанією Frontline Systems, недоступна для Excel на мобільних пристроях.

Solver – це безкоштовна надбудова для Excel. Щоб отримати додаткові відомості, знайдіть додатковий компонент "Пошук рішення" в Магазині Office.

В даний час надбудова "Пошук рішень", що надається компанією Frontline Systems, недоступна для Excel на мобільних пристроях.

Solver – це безкоштовна надбудова для Excel. Щоб отримати додаткові відомості, знайдіть додатковий компонент "Пошук рішення" в Магазині Office.

В даний час надбудова "Пошук рішень", що надається компанією Frontline Systems, недоступна для Excel на мобільних пристроях.

Solver – це безкоштовна надбудова для Excel. Щоб отримати додаткові відомості, знайдіть додатковий компонент "Пошук рішення" в Магазині Office.

Додаткові відомості

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.

Пошук рішення EXCEL. Знайомство

Пошук рішення будемо розглядати в MS EXCEL 2010 (ця надбудова зазнала деяких змін порівняно з попередньою версією в MS EXCEL 2007). У цій статті розглянемо:

• створення оптимізаційної моделі на аркуші MS EXCEL
• налаштування пошуку рішення;
• Найпростіший приклад (лінійна модель).

Встановлення пошуку рішення

Команда Пошук рішення знаходиться у групі Аналіз на вкладці Дані .

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

• На вкладці Файл виберіть команду Параметри , а потім - категорію Надбудови ;
• У полі Управління виберіть значення Надбудови Excel та натисніть кнопку Перейти;
• У полі Доступні надбудови встановіть прапорець поруч із пунктом Пошук рішення та натисніть кнопку ОК.

Примітка . Вікно Надбудови також доступно на вкладці Розробник . Як увімкнути цю вкладку читайте тут.

Після натискання кнопки Пошук рішення у групі Аналіз, відкриється його діалогове вікно .

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

Про моделі

Цей розділ для тих, хто тільки знайомиться з поняттям «Оптимізаційна модель».

Порада . Перед використанням Пошуку рішення настійно рекомендуємо вивчити літературу щодо вирішення оптимізаційних завдань та побудови моделей.

Нижче наведено невеликий лікнеп на цю тему.

Надбудова Пошук рішення допомагає визначити найкращий спосіб зробити щось :

• "Щось" може включати виділення грошей на інвестиції, завантаження складу, доставку товару або будь-яку іншу предметну діяльність, де потрібно знайти оптимальне рішення.
• "Найкращий спосіб" або оптимальне рішення в цьому випадку означає: максимізацію прибутку, мінімізацію витрат, досягнення найкращої якості та ін.

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

• Визначити план виробництва, у якому дохід від реалізації виробленої продукції максимальний;
• Визначити схему перевезень , коли він загальні витрати на перевезення були б мінімальними;
• Знайти розподіл кількох верстатів за різними видами робіт, щоб загальні витрати на виробництво продукції були б мінімальними;
• Визначити мінімальний термін виконання всіх робіт проекту (критичний шлях).

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

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

• Вибір параметра працює лише з моделями з однією змінною;
• у ньому неможливо встановити обмеження для змінних;
• визначається не максимум або мінімум цільової функції, а її рівність нікому значенню;
• ефективно працює лише у разі лінійних моделей, у нелінійному випадку знаходить локальний оптимум (найближчий до первісного значення змінної).

Підготовка оптимізаційної моделі у MS EXCEL

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

Порада . Організуйте дані моделі так, щоб на одному аркуші MS EXCEL була лише одна модель. В іншому випадку, для виконання розрахунків доведеться постійно зберігати та завантажувати налаштування Пошуку рішення (Див. нижче).

Наведемо алгоритм роботи з Пошуком рішення , який радять самі розробники ( ]]> www.solver.com ]]> ):

• Визначте комірки зі змінними моделями (decision variables);
• Створіть формулу в комірці, яка розраховуватиме цільову функцію вашої моделі (objective function);
• Створіть формули в комірках, які обчислюватимуть значення, що порівнюються з обмеженнями (ліва сторона виразу);
• За допомогою діалогового вікна Пошук рішення введіть посилання на комірки, що містять змінні, на цільову функцію, на формули для обмежень та самі значення обмежень;
• Запустіть Пошук рішення для знаходження оптимального рішення.

Зробимо всі ці кроки на простому прикладі.

Простий приклад використання Пошуку рішення

Необхідно завантажити контейнер товарами, щоб вага контейнера була максимальною. Контейнер має об'єм 32 куб. Товари містяться в коробках та ящиках.Кожна коробка з товаром важить 20кг, її об'єм складає 0,15м3.

Дані моделі організуємо в такий спосіб (див. файл прикладу ).

Змінні моделі (кількість кожного виду тари) виділені зеленим. Цільова функція (загальна вага всіх коробок і ящиків) – червоним. C6) – це загальна вага всіх коробок та ящиків, завантажених у контейнер. розраховуємо загальний об'єм - =СУМПРОИЗВ(B7:C7;B8:C8) . Пошук рішення введемо посилання на комірки, що містять змінні, цільову функцію, формули для обмежень і самі значення обмежень (або посилання на відповідні комірки).

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

Порада : у статті "Пошук рішення MS EXCEL. Екстремум функції з декількома змінними. Граничні умови задані рівняннями" показано рішення задачі, в якій функція та граничні умови задані у явному вигляді, тобто математичними виразами типу F(x1, x2, x3) =x1+2*x2+6*x3, що значно полегшує побудову моделі, т.к. особливо осмислювати завдання: можна просто підставити змінні x у полі змінні, а обмеження ввести у поле вікна Пошуку рішення.

Резюме

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

Пошуку рішення не вдалося знайти рішення (Solver could not find a feasible solution)

Це повідомлення з'являється, коли Пошук рішення не зміг знайти поєднань значень змінних, які одночасно задовольняють всі обмеження. Якщо ви використовуєте Симплекс метод вирішення лінійних завдань , то можна бути впевненим, що рішення справді не існує. Якщо ви використовуєте метод вирішення нелінійних завдань, який завжди починається з початкових значень змінних, це може також означати, що допустиме рішення далеко від цих початкових значень. Якщо ви запустите Пошук рішення з іншими початковими значеннями змінних, можливо, рішення буде знайдено. Припустимо, що при розв'язанні задачі нелінійним методом, комірки зі змінними були залишені не заповненими (тобто початкові значення дорівнюють 0), і Пошук рішення не знайшов рішення. Це не означає, що рішення справді не існує (хоча це може бути й так). Тепер, ґрунтуючись на результатах певної експертної оцінки, в комірки зі змінними введемо інший набір значень, який, на Вашу думку, близький до оптимального. У цьому випадку, Пошук рішення може знайти рішення (якщо воно справді існує).

Примітка . Про вплив нелінійності моделі на результати розрахунків можна прочитати в останній розділ статті Пошук рішення MS EXCEL (4.3). Вибір місця відкриття нового представництва.

У будь-якому випадку (лінійному чи нелінійному), Ви повинні спочатку проаналізувати модель на несуперечність обмежень, тобто умов, які не можуть бути задоволені одночасно. Найчастіше це пов'язано з неправильним вибором співвідношення (наприклад =) або граничного значення. Якщо, наприклад, у розглянутому вище прикладі, значення максимального обсягу встановити 16 м3 замість 32 м3, це обмеження суперечитиме обмеженню за мінімальною кількістю місць (110), т.к. мінімальній кількості місць відповідає обсяг рівний 16,5 м3 (110 * 0,15, де 0,15 - обсяг коробки, тобто найменшої тари). Встановивши як обмеження максимального обсягу 16 м3, Пошук рішення не знайде рішення.

При обмеженні 17 м3 Пошук рішення знайде рішення.

Деякі настройки Пошуку рішення

Метод вирішення Розглянута вище модель лінійної, тобто. цільова функція (M - загальна вага, яка може бути максимальна) виражена наступним рівнянням M = a1 * x1 + a2 * x2, де x1 і x2 - це змінні моделі (кількість коробок і ящиків), а1 і а2 - їх ваги. У лінійній моделі обмеження також мають бути лінійними функціями від змінних. У нашому випадку обмеження за обсягом V = b1 * x1 + b2 * x2 також виражається лінійною залежністю. Очевидно, що інше обмеження - Максимальна кількість тари (n) – також лінійно x1+x2

Постановка та вирішення задачі за допомогою надбудови "Пошук рішення"

"Пошук рішення" - це надбудова для Microsoft Excel, яку можна використовувати для аналізу "що якщо".З її допомогою можна знайти оптимальне значення (максимум або мінімум) формула, що міститься в одному осередку, званому цільовою, з урахуванням обмежень на значення в інших осередках з формулами на аркуші. Надбудова "Пошук рішення" працює з групою осередків, званих осередками змінних рішення або просто осередками змінних, які використовуються при розрахунку формул у цільових осередках та осередках обмеження. Надбудова "Пошук рішення" змінює значення в осередках змінних рішення відповідно до меж комірок обмеження і виводить потрібний результат у цільовому осередку.

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

У наведеному нижче прикладі кількість проданих одиниць у кожному кварталі залежить від рівня реклами, що опосередковано визначає обсяг продажу, пов'язані витрати та прибуток. Надбудова "Пошук рішення" може змінювати щоквартальні витрати на рекламу (осередки змінних рішення B5: C5) до обмеження в 20 000 рублів (осередок F5), поки загальний прибуток (цільовий осередок F7) не досягне максимального значення. Значення в осередках змінних використовуються для обчислення прибутку за кожний квартал, тому вони пов'язані з формулою в цільовій осередку F7, = СУМ (Q1 Прибуток: Q2 Прибуток).

1. Осередки змінних

2. Осередок з обмеженнями

Після виконання процедури отримано такі значення.

1. На вкладці Дані у групі Аналіз натисніть кнопку Пошук рішення.
   

Примітка: Якщо команда "Рішувач" або група "Аналіз " недоступна, необхідно активувати засіб вирішення надбудови. Див. статтю Активація надбудови "Рішувач".

• Щоб значення цільового осередку було максимальним із можливих, встановіть перемикач у положення Макс.
• Щоб значення цільового осередку було мінімальним із можливих, встановіть перемикач у положення Мін.
• Щоб встановити конкретне значення для цільової комірки, встановіть перемикач у положення Значення та введіть у поле потрібне число.
• У полі Змінюючи комірки змінних введіть імена діапазонів осередків змінних рішення або посилання на них. Несуміжні посилання розділяйте комами. Осередки змінних повинні бути прямо або опосередковано пов'язані з цільовим осередком. Можна задати до 200 осередків змінних.

1. У діалоговому вікні Параметри пошуку рішення натисніть кнопку Додати.
2. У полі Посилання на комірку введіть посилання на комірку або ім'я діапазону осередків, значення яких накладаються обмеження.
3. Клацніть зв'язок ( , =, >=, int, bin або dif ), яка потрібна між зазначеним осередком та обмеженням. Якщо клацнути int, у полі Обмеження з'явиться ціле число. Якщо клацнути bin, двійковий файл з'явиться у полі Обмеження . Якщо клацнути dif, у полі Обмеження з'явиться alldifferent.
4. Якщо у полі Обмеження було вибрано відношення =, введіть число, посилання на комірку (або ім'я комірки) або формулу.
5. Виконайте одну з наведених нижче дій.
   • Щоб прийняти це обмеження та додати інше, натисніть кнопку Додати.
   • Щоб прийняти обмеження та повернутися до діалогового вікна Параметри вирішувача, натисніть кнопку ОК.
     Нотатка Відносини int, bin і dif можна застосовувати лише в обмеженнях для осередків змінних прийняття. Щоб змінити або видалити існуюче обмеження, виконайте наведені нижче дії.
6. У діалоговому вікні Параметри пошуку рішення клацніть обмеження, яке потрібно змінити або видалити.
7. Натисніть кнопку Змінити та внесіть зміни або натисніть кнопку Видалити.

• Щоб зберегти значення рішення на аркуші, у діалоговому вікні Результати пошуку рішення виберіть варіант Зберегти знайдене рішення.
• Щоб відновити вихідні значення перед натисканням кнопки Знайти рішення, виберіть варіант Відновити вихідні значення.
• Ви можете перервати пошук рішення, натиснувши клавішу ESC. Аркуш Excel буде перерахований з урахуванням останніх знайдених значень для осередків змінних рішення.
• Щоб створити звіт на основі знайденого рішення, виберіть тип звіту в полі Звіти та натисніть кнопку ОК. Звіт буде розміщено на новий аркуш книги. Якщо рішення не знайдено, будуть доступні лише деякі звіти або вони взагалі не будуть доступні.
• Щоб зберегти значення комірки змінної рішення як сценарій, який можна буде відобразити пізніше, натисніть кнопку Зберегти сценарій у діалоговому вікні Результати пошуку рішення, а потім введіть ім'я цього сценарію в полі Назва сценарію.

1. Після встановлення завдання натисніть кнопку Параметри у діалоговому вікні Параметри пошуку рішення.
2. Щоб переглянути значення всіх знайдених рішень, у діалоговому вікні Параметри встановіть прапорець Показувати результати ітерацій та натисніть кнопку ОК.
3. У діалоговому вікні Параметри пошуку рішення натисніть кнопку Знайти рішення.
4. У діалоговому вікні Показати попереднє рішення виконайте одну з наведених нижче дій.
   • Щоб зупинити пошук рішення та вивести на екран діалогове вікно Результати пошуку рішення, натисніть кнопку Стоп.
   • Щоб продовжити процес пошуку рішення та переглянути наступний варіант рішення, натисніть кнопку Продовжити.

1. У діалоговому вікні Параметри пошуку рішення натисніть кнопку Параметри.
2. У діалоговому вікні на вкладках Усі методи, Пошук вирішення нелінійних завдань методом ОЗУ і Еволюційний пошук рішення виберіть або введіть потрібні установки.

1. У діалоговому вікні Параметри пошуку рішення натисніть кнопку Завантажити/зберегти.
2. Введіть діапазон осередків для області моделі та натисніть кнопку Зберегти або Завантажити. У разі збереження моделі введіть посилання на першу комірку вертикального діапазону порожніх осередків, в якому слід розмістити модель оптимізації. Під час завантаження моделі введіть посилання на весь діапазон осередків, що містить оптимізацію.

Порада: Щоб зберегти останні параметри, налаштовані у діалоговому вікні Параметри пошуку рішення, разом з листом, зберегти книгу. Кожен аркуш у книзі може мати свої параметри надбудови "Пошук рішення" і всі вони зберігаються. Крім того, для аркуша можна визначити більше одного завдання, якщо натискати кнопку Завантажити чи зберегти для збереження завдань окремо.

У діалоговому вікні Параметри пошуку рішення можна вибрати будь-який із наведених нижче алгоритмів або методів пошуку рішення.

• Нелінійний спосіб узагальненого понижуючого градієнта (ОЗУ). Використовується для гладких нелінійних завдань.
• Симплекс-метод. Використовується для лінійних завдань.
• Еволюційний метод Використовується для негладких завдань.

Важливо: Спочатку потрібно увімкнути надбудову "Пошук рішення". Щоб отримати додаткові відомості, див. Завантаження надбудови "Пошук рішення".

У наведеному нижче прикладі кількість проданих одиниць у кожному кварталі залежить від рівня реклами, що опосередковано визначає обсяг продажу, пов'язані витрати та прибуток.Надбудова "Пошук рішення" може змінювати щоквартальні витрати на рекламу (осередки змінних рішення B5: C5) до обмеження в 20 000 рублів (осередок D5), поки загальний прибуток (цільовий осередок D7) не досягне максимального значення. Значення в осередках змінних використовуються для обчислення прибутку за кожний квартал, тому вони пов'язані з формулою в цільовій осередку D7, = СУМ (Q1 Прибуток: Q2 Прибуток).

В результаті виконання отримані такі значення:

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

Примітка: Відносини int, бін та раз можна використовувати тільки в обмеженнях для осередків, в яких перебувають змінні рішення.

Необхідні дії

Прийняти обмеження та додати інше

Натисніть кнопку Додати.

Прийняти обмеження та повернутися до діалогового вікна Параметри пошуку рішення

Натисніть кнопку ОК.

1. Щоб перервати пошук рішення, натисніть клавішу ESC. Аркуш Excel буде перерахований з урахуванням останніх знайдених значень для осередків змінних.
2. Щоб створити звіт на основі знайденого рішення, виберіть тип звіту в полі Звіти та натисніть кнопку ОК. Звіт буде розміщено на новий аркуш книги. Якщо рішення не знайдено, звіт не буде доступний.
3. Щоб зберегти значення комірки змінного рішення як сценарій, який можна буде відобразити пізніше, натисніть кнопку Зберегти сценарій у діалоговому вікні Результати пошуку рішення, а потім введіть ім'я цього сценарію в полі Назва сценарію.

Примітка: Якщо буде досягнуто максимального часу пошуку рішення або кількість ітерацій, а рішення ще не буде знайдено, засіб "Пошук рішення" виведе діалогове вікно Показати попереднє рішення.

Порада: Щоб зберегти останні параметри, налаштовані у діалоговому вікні Параметри пошуку рішення, разом з листом, зберегти книгу. Кожен аркуш у книзі може мати свої параметри надбудови "Пошук рішення" і всі вони зберігаються.Крім того, для аркуша можна визначити більше одного завдання, якщо натискати кнопку Завантажити/зберегти для збереження завдань окремо.

Нелінійний метод узагальненого знижувального градієнта (ОЗУ)

Використовується за замовчуванням для моделей з усіма функціями Excel, окрім ЯКЩО, ВИБІР, ПЕРЕГЛЯД та інші ступінчасті функції.

Пошук вирішення лінійних завдань симплекс-методом

Використовуйте цей метод для задач лінійного програмування. У формулах моделі, які залежать від осередків змінних, повинні використовуватися функції СУМ, СУМПРОВИЗВ, +, - та *.

Еволюційний пошук рішення

Цей метод, заснований на генетичних алгоритмах, найкраще підходить у тому випадку, якщо в моделі використовуються функції ЯКЩО, ВИБІР і ПЕРЕГЛЯД з аргументами, які залежать від осередків змінних.

Примітка: Авторські права на частини програмного коду надбудови "Пошук рішення" версій 1990-2010 належать компанії Frontline Systems, Inc. Авторські права на частини версії 1989 року належать компанії Optimal Methods, Inc.

Так як програми надбудов не підтримуються в Excel в Інтернеті, ви не зможете використовувати надбудову "Рішувач" для аналізу даних "що якщо" для пошуку оптимальних рішень.

Якщо у вас є класична програма Excel, можна використовувати кнопку Відкрити в Excel , щоб відкрити книгу, щоб використовувати надбудову Решателя.

Додаткова довідка з надбудови "Пошук рішення"

За додатковою довідкою з надбудови "Пошук рішення" звертайтесь за цими адресами:

Frontline Systems, Inc.
P.O. Box 4288
Похило село, NV 89450-4288
(775) 831-0300
Веб-сайт: http://www.solver.com
Електронна пошта: [email protected]
довідка щодо вирішення проблеми за адресою www.solver.com.

Авторські права на частини програмного коду надбудови "Пошук рішень" версій 1990-2009 належать компанії Frontline Systems, Inc. Авторські права на частини версії 1989 року належать компанії Optimal Methods, Inc.

Додаткові відомості

Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.