Пошук рішення

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

ПРИМІТКА

Інструмент є надбудовою Якщо свого часу виконувалася повна установка Excel, команда знаходиться в меню Сервіс В іншому випадку потрібно в меню Сервіс вибрати команду Надбудови і в списку доступних надбудов встановити прапорець Якщо ж у списку немає варіанту , доведеться запустити програму установки Excel ще раз Цей процес описаний у додатку А «Установка Microsoft Excel»

Розглянемо завдання, відповідь в якій можна знайти за допомогою команди Уявіть, що ви розробляєте план рекламної кампанії Загальний бюджет на випуск друкованої рекламної продукції становить $ 12 000 000, при цьому вкрай бажано, щоб число потенційних читачів цієї реклами не падало нижче 800 мільйонів, а вся кампанія розгорнулася на сторінках шести друкованих видань, назвемо їх для прикладу Ізд1, изд2, ІздЗ і т д Кожне з цих видань має своє коло читачів і різну вартість розміщення рекламних матеріалів на своїх сторінках Ваше завдання полягає в тому, щоб оптимізувати витрати, тобто в межах виділеної суми досягти максимального ефекту при дотриманні певних додаткових умов:

► у кожному виданні повинно бути надруковано не менше 6 оголошень

► не можна витрачати більше третини всієї суми на одне видання

► загальна вартість розміщення реклами в ІздЗ і Изд4 не повинна перевищувати $ 7 500 000

На рис 1823 показаний один з можливих варіантів рішення

Рис 1823 Для вирішення завдання з декількома параметрами краще використовувати інструмент

ДИВИСЬ ТАКОЖ

У цьому розділі наводяться лише загальні відомості про роботу з інструментом Більш детальну інформацію, включаючи тлумачення повідомлень про помилки, ви знайдете в довідковій системі Excel

Поставлену задачу можна спробувати вирішити вручну шляхом підбору значень в осередках D2: D7, не забуваючи при цьому про накладені обмеження і стежачи за кінцевою сумою витрат в комірці Е8 Наприкінці решт, напевно, вам * і вдасться оптимізувати витрати, але повірте, силами програмного вирішувача це буде зроблено у багато разів швидше, адже при розрахунках застосовується не просто перебір значень параметрів, а ряд спеціально пристосованих алгоритмів

Рис 1824 Діалогове вікно

Щоб почати роботу з інструментом , виберіть цю команду в меню Сервіс На екрані зявиться діалогове вікно , продемонстроване на рис 1824 Необхідно заповнити всі поля цього вікна, тобто вказати кінцевий результат (мета, в даному випадку – мінімізацію витрат), змінювані комірки (кількість оголошень в кожному виданні) і обмеження (умови, які повинні дотримуватися при розрахунках вони виділені рамкою в нижній частині листа на рис 1823)

Вказівка ​​очікуваного результату

У полі Встановити цільову комірку (Set Target Cell) задається мета пошуку рішення У розглянутому прикладі нашої завданням є мінімізація загальних витрат (значення в комірці Е8), тому в зазначений поле слід ввести посилання на цю клітинку, а перемикач Рівної (Equal To) встановити в положення мінімального значення (Min)

РАДА –

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

В принципі, вказувати цільову комірку в діалоговому вікні Пошук рішень необовязково Якщо залишити поле Встановити цільову комірку порожнім, клацнути на кнопці Параметри і встановити прапорець Показувати результати ітерацій (Show Iteration Results), то інструмент займеться перебором деяких або всіх можливих комбінацій, що задовольняють зазначеним критеріям У цьому випадку результат пошуку не вийде за межі обмежень, але не факт, що буде оптимальним

ДИВИСЬ ТАКОЖ

Для отримання додаткових відомостей про прапорці Показувати результати ітерацій звертайтеся до розділу «Перегляд проміжних результатів» цієї глави

Завдання змінних

На наступному кроці роботи з інструментом Пошук рішень потрібно визначити змінювані комірки, тобто ті, в яких значення можуть змінюватися У нашому прикладі це осередки D2: D7 Можна або ввести посилання на діапазон в поле Змінюючи осередку, або клацнути на кнопці Припустити (Guess), і програма сама запропонує змінювані комірки, з заданої цільової осередку моделі

Завдання обмежень

Останній крок – додавання в модель обмежень – не є необхідним Коли він опущений, пошук рішення виконується без жодних умов Якщо ж потрібно уточнити критерії пошуку, клацніть на кнопці Додати (Add) в діалоговому

вікні і заповніть поля діалогового вікна Додавання обмеження (Add Constraint) Малюнок 1825 демонструє, як слід вводити обмеження на загальні витрати на рекламу (значення в комірці Е8) – вони не повинні перевищувати загальний бюджет проекту (значення в комірці G11)

Рис 1825 Щоб ввести обмеження в цьому вікні, клацніть на кнопці Додати в діалоговому вікні

На рис 1826 показано діалогове вікно після заповнення всіх його полів і накладення обмежень Зверніть увагу, що обмеження перераховані в алфавітній послідовності, яка може і не збігатися з порядком їх введення

Рис 1826 Діалогове вікно Пошук рішень, підготовлене до вирішення завдання

Зауважте також, що два обмеження зліва від оператора порівняння включають посилання на діапазони Вираз $ D $ 2: $ D $ 7> = $ G $ 15 передбачає, що значення в будь-якій комірці діапазону D2: D7 зобовязане бути більше 6 або дорівнює цьому числу, а умова

$ F $ 2: $ F $ 7 <= $ G $ 14 вказує, що значення в будь-якій комірці діапазону F2: F9 не повинно перевищувати 33,3%. Кожне з двох виразів є коротким записом шести окремих обмежень. При такому способі запису обмежень у правій частині виразу повинна стояти або посилання на одиночну осередок, або посилання на діапазон тієї ж розмірності, що і в лівій частині виразу, або константа.

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

Виконати (Solve) У нашому прикладі з рекламною кампанією програма знаходить

оптимальне рішення при фіксованій кінцевої мети і введених обмеженнях і в результаті виводить на екран діалогове вікно, представлене на рис 1827 Значення, отримані в осередках на робочому листі, якраз і показують найкращий варіант Їх можна або залишити на аркуші, встановивши перемикач в положення Зберегти знайдене рішення (Keep Solver Solution) і натиснувши кнопку ОК, або відновити вихідні значення Для другого випадку встановіть перемикач в положення Відновити вихідні значення (Restore Original Values) і натисніть OK (або натисніть кнопку Відміна (Cancel)) Також є можливість зберегти знайдене рішення як сценарій

Рис 1827 Коли відповідь знайдений, на екрані зявляється діалогове вікно Результати пошуку рішення

Обмеження на кількість розрядів у відповіді

Оскільки решатель для знаходження оптимальної відповіді виконує ряд чисто математичних обчислень, їх результатом може бути будь-яке число У нашому варіанті при заданих обмеженнях кількість оголошень для публікації в одному виданні не обовязково буде дорівнювати цілому числу, тобто, хоча рішення задачі і оптимально, його не можна реалізувати на практиці Неможливо, наприклад, розмістити в одному виданні 53,3 рекламного оголошення, хоча саме цей результат отримано для Изд4

Щоб програма знаходила тільки цілі значення певних змінних, натисніть кнопку Додати (Add) в діалоговому вікні Пошук рішень У діалоговому вікні Додавання обмеження введіть посилання на діапазон D2: D7, відкрийте список в середній частині цього діалогового вікна і виберіть пункт Цел (Int), як показано на рис 1828 У полі Обмеження (Constraint) зявиться слово «Ціле» (integer) Натисніть кнопку ОК, щоб повернутися до основного діалоговому вікна

Рис 1828 Щоб обмежити рішення безліччю цілих чисел, виберіть в другому списку діалогового вікна пункт Цел

При пошуку цілочисельного рішення Excel не просто округлює числа в меншу сторону, а виконує їх оптимізацію з урахуванням обмеження Так, при розміщенні 53 рекламних оголошень в Изд4 зявляється можливість купити додаткові місця на рекламу вИзд5,що при дуже маленькому збільшенні бюджету дозволяє розширити аудиторію потенційних читачів на цілих 2 млн осіб

РАДА

Установка цілочисельних обмежень зазвичай веде до ускладнення завдання в геометричній прогресії, а внаслідок цього і до збільшення часу пошуку оптимальної відповіді Приклад, наведений вище, досить простий, і відповідь знаходиться швидко, але для складних моделей пошук рішення серед цілочисельних значень може виявитися дуже ресурсоємним процесом Тому намагайтеся звертатися до даного обмеження тільки в дійсно критичних випадках Зокрема, цілочисельні рішення є обовязковими для задач, в яких змінні в змозі приймати тільки два значення, наприклад 1 і 0 («Так» і «Ні») Тоді в діалоговому вікні Зміна обмеження (Change Constraint) у списку, в середній частині вікна потрібно вибрати варіант

«Двоич» (bin)

Збереження моделі пошуку рішення

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

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

1 Відкрийте діалогове вікно , вибравши в меню Сервіс однойменну команду

2 Клацніть на кнопці Параметри (Options), щоб вивести на екран діалогове вікно Параметри пошуку рішення (Solver Options), показане на рис 1829 Потім натисніть кнопку Зберегти модель (Save Model) Програма попросить вказати клітинку або діапазон робочого аркуша, в які будуть записані параметри пошуку рішення

3 Задайте порожню комірку, клацнувши на ній або ввівши посилання, і натисніть кнопку ОК Зберігаються параметри зявляться на аркуші, починаючи із зазначеної комірки, і займуть при цьому необхідну кількість сусідніх осередків (Тому попередньо також слід переконатися, що осередки нижче заданої не містять даних)

4 Щоб скористатися збереженими параметрами, натисніть кнопку Завантажити модель (Load Model) у діалоговому вікні Параметри пошуку рішення і задайте діапазон, в який була записана схема пошуку рішення

Рис 1829 Кнопки Зберегти модель і Завантажити модель дозволяють зберегти,

а потім знову завантажити параметри пошуку рішення

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

Збереження результатів у вигляді сценарію

Існує ще один спосіб запису параметрів пошуку рішення – збереження їх у вигляді іменованих сценаріїв Як ви могли помітити, в діалоговому вікні Результати пошуку рішення, представленому на рис 1827, є кнопка Зберегти сценарій (Save Scenario) Клацніть на цій кнопці, щоб призначити імя сценарію для поточних значень в змінюваних осередках Записаний таким чином сценарій є відмінною базою для проведення подальшого аналізу «що-якщо», заснованого на підборі змінних

ДИВИСЬ ТАКОЖ

Детальніше про сценарії розповідається в розділі «Диспетчер сценаріїв» даної глави

Інші параметри пошуку рішення

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

► Поля Максимальний час (Max Time) і Граничне число ітерацій (Iterations) дозволяють обмежити час вирішення часом роботи програми і кількістю ітерацій відповідно Як тільки одне з цих умов виконається, обчислення

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

► Поле Відносна похибка (Precision) відповідає за точність, тобто за те, наскільки знайдене рішення має бути близько до заданого цільового значення при дотриманні всіх обмежень Чим ближче значення відносної похибки до 1, тим точність нижче Однак якщо встановити значення похибки менше, ніж 0,000001 (за замовчуванням), це може привести до істотного збільшення часу обчислення

► Параметр Допустиме відхилення (Tolerance) використовується тільки при вирішенні завдань з цілочисельними значеннями і визначає в процентному відношенні допустиму у вирішенні помилку

► Якщо ви не знайомі з методами лінійної оптимізації, то перемикачі Оцінки (Estimates), Різниці (Derivatives) і Метод пошуку (Search) краще залишити в тих положеннях, в яких вони встановлені за замовчуванням Щоб отримати додаткову інформацію про ці параметри, зверніться до довідкової системи Excel

Лінійна оптимізація

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

Прапорець Лінійна модель (Assume Linear Model) слід встановлювати тільки для задач лінеаризації Наприклад, моделі, в яких присутні виключно операції віднімання або додавання, лінійни за своєю природою Але в реальних ситуаціях ви будете мати справу в основному з нелінійними описами У них входять операції множення або ділення, а також непропорційні функції, такі як експонентні і логарифмічні, або спеціалізовані функції Excel, наприклад ПЛТ

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

РАДА

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

Перегляд проміжних результатів

Якщо ви хочете бачити результати підбору параметрів не тільки за фактом завершення роботи вирішувача, а й для інших, проміжних, стадій, встановіть в діалоговому вікні Параметри пошуку рішення прапорець Показувати результати ітерацій (Show Iteration Results) Тепер після кожної ітерації на екрані буде зявлятися діалогове вікно Поточний стан пошуку рішення (Show Trial Solution), що пропонує вам вибір: зберегти ці параметри у вигляді сценарію, припинити пошук рішення або продовжити ітеративний процес

ПРИМІТКА

Слід враховувати, що значення, що відображаються в діалоговому вікні Поточний стан пошуку рішення, можуть не задовольняти всім заданим обмеженням і не бути оптимальними

Створення звітів

Після виконання пошуку оптимального рішення можноорганізовать всі отримані дані у вигляді будь-якого з трьох типів звітів: Результати (Answer), Стійкість (Sensitivity) і Межі (Limits) Для цього потрібно виділити потрібні назви в діалоговому вікні Результати пошуку рішення і натиснути у ньому кнопку ОК (Щоб виділити кілька звітів, утримуйте клавішу Ctrl) Кожен звіт зберігається на окремому аркуші поточної книги

Звіт по стійкості

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

– для змінюваних осередків, а інша – для обмежень Правий стовпець в кожному розділі надає інформацію про стійкість

Кожній змінною осередку і обмеженню в звіті відводиться власна рядок В області для змінюваних осередків вказується величина нормованого градієнта– Показника того, як цільова осередок реагує на збільшення значення у відповідній змінною осередку на одиницю Точно так само в розділі для обмежень множник Лагранжа демонструє, який вплив на цільову комірку надає мінімальний зміну відповідного обмеження

Звіт за результатами

Звіт за результатами включає в себе цільову комірку, список змінюваних осередків і обмежень Додатково в ньому відображається така інформація, якстатус (Стан) істрогістькожного з обмежень Обмеження може знаходитися в одному з трьох станів: Повязане (Binding), незвязаних (Not Binding) або Не виконане (Not Satisfied) Строгість виражається в різниці між фактичним значенням у клітинці обмеження при отриманні рішення і числом, заданих в правій частині виразу обмеженняПовязане обмеження(Суворе, сильне, жорстке) вимагає збігу значень (розбіжність дорівнює 0) Несвязанное обмеження (Мяке,

слабке) – дотримання з ненульовим значенням різниці

ПРИМІТКА

Якщо в діалоговому вікні Параметри пошуку рішення встановлений прапорець Лінійна модель,

доступним виявиться тільки звіт за результатами

Звіт по межам

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

Якщо рішення не знайдено

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

Пошук рішення не може знайти відповідного рішення Це означає, що рішення не знайдено, оскільки не вдалося задовольнити всім обмеженням До подібної ситуації призводить неакуратна постановка задачі – Вона виникає, якщо умови суперечать / один одному або, по суті, несумісні (наприклад, коли потрібно, щоб рекламна кампанія охопила 800 млн читачів при витратах в $ 1 000 000) У деяких випадках дане повідомлення виводиться в ситуації, коли початкові значення в змінюваних осередках занадто нереалістичні Якщо ви вважаєте, що обмеження внутрішньо несуперечливі і завдання можна вирішити, спробуйте задати інші початкові значення в змінюваних осередках і повторити процедуру пошуку

Пошук зупинений (досягнуте максимальне число ітерацій) Щоб не витрачати даремно час на вирішення потенційно нерозвязною завдання, після досягнення заданої кількості ітерацій пошук припиняється, і на екрані зявляється це повідомлення Тут можна клацнути на кнопці Продовжити (Continue) або на кнопці Стоп (Stop) У першому випадку обчислення тривають і перериваються, тільки якщо знайдено оптимальне рішення, вичерпано відведений час або з якихось інших обєктивних причин Якщо рішення вимагає більшої кількості ітерацій, змініть значення в поле Граничне число ітерацій (Iterations) у діалоговому вікні Параметри пошуку рішення

Пошукзупинений (минув заданий на пошук час) Це повідомлення аналогічно попередньому Обчислення зупиняються після закінчення встановленого за замовчуванням часу Час пошуку рішення можна збільшити в поле Максимальний час (Max Time) діалогового вікна Параметри пошуку рішення

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*