Лекція №8 - Підбір параметрів і пошук розв’язання в Excel, НУДПСУ

Лекція №8

Тема: Підбір параметрів і пошук розв’язання

План

1. Підбір параметрів
2. Пошук рішення

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

Підбір параметрів

Припустимо, що за допомогою обчислення значень деякої складної функції необхідно одержати заздалегідь заданий результат. Як у цьому випадку знайти відповідне значення аргументу? Для розв’язання таких «зворотних» задач призначена команда «Подбор параметра» з меню «Сервис». Покажемо на прикладі, як розв’язуються подібні задачі за допомогою цієї команди.

Задача 2. Ваш друг хоче взяти кредит у банку терміном на 30 років під 10% річних на придбання житла. Але в нього немає можливості повертати більше ніж 200 у.е. на місяць. Який максимальний кредит він може взяти, щоб укластися в цю суму?

Розв’язання. Розв’язуємо спочатку «пряму» задачу за допомогою функції ППЛАТ, починаючи з деякої початкової суми кредиту, наприклад, 50000. З рис. 13 видно, що виплати по цьому кредиту (комірка В4) перевищать $200.

Активізуємо комірку В4 і виконуємо команду «Сервис ® Подбор параметра». У вікні цієї команди (рис. 14) в першому полі за замовчуванням буде зазначена цільова адреса B4, яку залишаємо без зміни. В полі «Значение» вказуємо цільове значення 200, а в полі «Изменяя значение ячейки», вказуємо адресу B1, оскільки саме в цій комірці розташований шуканий аргумент.

Після натискання на кнопку «ОК» з'явиться вікно «Результат подбора параметра» з протоколом розв’язання задачі, а в комірці В1 з'явиться відповідь, яка дорівнює 22790. Задача розв’язана.

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

При розв’язанні задач цим методом варто звертати увагу на точність одержуваних результатів і на вибір початкових наближень, які можуть вплинути на результат, якщо в задачі є кілька розв’язків. Наприклад, спробуємо добути квадратний корінь і запишемо в комірку А1 формулу =А2^2, а потім виконаємо команду «Подбор параметра» для пошуку значення в комірці А2, при якому вміст комірки А1 дорівнює 4. В результаті отримаємо «неточне» значення кореня, що дорівнює 2,000023. Якщо як початкове наближення взяти -1, то отримаємо в результаті –1,99992. 

Пошук розв’язку та оптимізація

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

Відзначимо, що компонента пошуку розв’язання (на англійській Solver) як і пакет аналізу є доповненням (надбудовою) програми Excel і для роботи з нею необхідна команда «Поиск решения» у меню «Сервис». Якщо цієї команди немає в меню, то установка надбудови здійснюється так само, як це було описано вище для пакета аналізу.

По-перше, процедуру пошуку розв’язання можна використовувати для розв’язання рівнянь від декількох змінних . Для цього у вікні команди «Сервис ® Поиск решения» (див. рис. 15) потрібно встановити селекторну кнопку «Равной» у позицію «значению 0». В цьому випадку в якості цільової вказується комірка, в якій записана функція f, а в полі «Изменяя ячейки» вказуються адреси, де розташовані значення змінних . Іншими словами, пошук розв’язання є узагальненням процедури підбору параметра для багатьох змінних. Аналогічно за допомогою цього інструменту можна розв’язувати системи рівнянь від декількох змінних. Для розв’язання рівнянь в Excel використовується алгоритм нелінійної оптимізації градієнтного типу (GRG2), розроблений американськими вченими.

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

Функція f називається цільовою, а функції  - функціями обмежень. Якщо функції f і  лінійні, то відповідна задача називається задачею лінійного програмування. Якщо потрібно, щоб значення змінних були цілими, то відповідна задача називається задачею цілочисельного програмування. Для розв’язання лінійних і цілочисельних задач оптимізації в Excel використовується відповідно симплекс метод і алгоритм спрямованого перебору (гілок і меж), розроблені фахівцями компанії Frontline Systems Inc.

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

Задача 3. З однакової сировини компанія може випускати два види продукції "А" і "Б". Потрібно визначити, в якій кількості виробляти продукцію кожного виду, щоб дістати максимальний прибуток, якщо відомі собівартості та ціни для кожного виду продукції, розміри накладних витрат, а також нижня і верхня межа їх збуту.

Розв’язання. Почнемо розв’язання зі створення, так званої, оптимізаційної моделі. Запишемо на робочому аркуші в діапазоні В3:Е11 наступну таблицю.

Далі, в комірці Е4 запишемо формулу =С4+D4, щоб визначити обсяг  усієї продукції, а в комірки С6 і D6 запишемо відповідно формули =C4*C5 і =D4*D5, щоб обчислити майбутню собівартість партії продукції кожного виду. В комірки С8 і D8 запишемо формули =С6+C7 і =D6+D7, щоб обчислити повну вартість кожної партії. В комірки С10 і D10 запишемо відповідно формули =C4*C9 і =D4*D9, щоб визначити повний виторг, що буде отриманий при продажі кожної партії. Нарешті, в комірки С11 і D11 запишемо формули =С10-C8 і =D10-D8, щоб визначити майбутній прибуток, а в комірку Е11 запишемо формулу =С11+D11 для визначення загального прибутку, що буде отриманий від продажу продукції. На цьому створення оптимізаційної моделі можна вважати завершеним.

Після цього виконуємо команду «Сервис ® Поиск решения» і у вікні цієї команди, що показане на рис. 15, вказуємо в якості цільової комірку Е11, в якій записана цільова функція. Селекторну кнопку «Равной» встановлюємо в положення «максимальному значению». В полі «Изменяя ячейки» вказуємо діапазон С4:D4, де розташовані невідомі значення, а потім вводимо обмеження.

Для введення обмеження потрібно натиснути кнопку «Добавить». Як обмеження вказуємо межі збуту продукції, що дорівніють відповідно 50000 і 400000, і верхню межу на загальний обсяг продукції, що дорівнює 500000. Натискаємо на кнопку «Выполнить» і програма видає результат С4 = 100000 і D4 = 400000. При цьому буде отриманий прибуток, що дорівнює  1297500. Задача розв’язана.

В загальному випадку оптимізаційна модель повинна містити інформацію про цільову комірку, вихідні дані, змінні й обмеження задачі, а також формули, що пов'язують основні параметри задачі. Формули обмежень можна записувати на робочому аркуші чи вказувати безпосередньо у вікні пошуку розв’язання. Для складних формул перший спосіб більш зручний, оскільки в цьому випадку у вікні обмежень можна вказати просто адресу комірки з формулою. Оскільки опис оптимізаційної моделі може забирати значний час програма Excel дозволяє зберігати і завантажувати їх. Для цього за допомогою кнопки «Параметры» у вікні пошуку розв’язання потрібно відкрити додаткове вікно й у ньому натиснути кнопку «Сохранить модель». При збереженні моделі варто вказати весь діапазон, у якому знаходяться вихідні дані і формули.

Зауваження, зроблені нами щодо точності і часу розв’язання при підборі параметра,  ще в більшій мірі  відносяться до пошуку розв’язання, оскільки тут використовуються більш складні чисельні алгоритми. Дуже часто при розв’язанні рівнянь з багатьма змінними програма не може знайти розв’язання за прийнятний час. Тут також може виявитися істотним вибір початкового наближення, що у більшості випадків за замовчуванням передбачається рівним нулю. Крім того, на змінні задачі можна накласти цілочисельні обмеження, що ще більше ускладнює задачу і робить її перебірною.

З повагою ІЦ "KURSOVIKS"!