За допомогою засобів аналізу What-If класичній програмі Excel можна використовувати різні набори значень в одній або кількох формулах, щоб дослідити всі можливі результати.
Наприклад, за допомогою аналізу "what-if" можна сформувати два кошториси з певним рівнем прибутку. Ви також можете вказати потрібний результат формули та визначити, які набори значень формуватимуть цей результат. В Excel є кілька засобів, які можна використовувати для аналізу потрібного типу.
Зауважте, що тут наведено лише загальні відомості про ці засоби. Довідку з кожного конкретного розділу див. за посиланнями.
Огляд
Аналіз "what-if" – це процес змінення значень у клітинках, що дає змогу переглянути вплив цих змін на результат формул на аркуші.
У програмі Excel є три види засобів аналізу What-If: сценарії, підбір параметра та таблиці даних. Сценарії й таблиці даних визначають можливі результати на основі наборів вхідних значень. Таблиця даних працює лише з однією або двома змінними, але може приймати низку різних значень для цих змінних. Сценарій може включати кілька змінних, але підтримує не більше 32 значень. Підбір параметра працює по-іншому: визначає можливі вхідні значення, які можуть дати певний результат.
Крім цих трьох засобів, можна інсталювати надбудови, які допомагають виконувати What-If аналіз, наприклад "Пошук розв'язання". Ця надбудова схожа на підбір параметра, але може включати більше змінних. Прогнози також можна створити за допомогою маркера заповнення й різноманітних команд, вбудованих у програму Excel.
Для більш просунутих моделей можна використовувати надбудову "Пакет аналізу".
Використання сценаріїв для аналізу різних змінних
Сценарій – це набір значень, що зберігаються в Excel і можуть автоматично заміняти один одного клітинки аркуша. Ви можете створити та зберегти різні групи значень на аркуші, а потім переключатися на нові сценарії, щоб переглянути різні результати.
Наприклад, у вас є два сценарії кошторису: песимістичний і оптимістичний. За допомогою диспетчера сценаріїв можна створити обидва сценарії на одному аркуші, а потім переключатися між ними. Для кожного сценарію потрібно вказати клітинки, які змінюються, і значення, що використовуються. Коли ви переключаєтеся між сценаріями, у клітинці результату відображаються різні значення змінюваних клітинок.
1. Змінювані клітинки
2. Клітинка результату
1. Змінювані клітинки
2. Клітинка результату
Якщо в сценаріях потрібно використовувати певні відомості від кількох людей, розташовані в окремих книгах, ви можете зібрати ці книги, а потім об’єднати їхні сценарії.
Створивши або зібравши всі потрібні сценарії, ви можете створити підсумковий звіт, який містить відомості з цих сценаріїв. У підсумковому звіті всі відомості про сценарії відображаються в одній таблиці на новому аркуші.
Примітка.
Повторне обчислення звітів за сценаріями не здійснюється автоматично. Якщо змінити значення сценарію, ці зміни не відображатимуться в наявному підсумковому звіті. Натомість потрібно створити новий підсумковий звіт.
Використання функції "Підбір параметра" для отримання бажаного результату
Якщо ви знаєте, який результат має повернути формула, і потрібно визначити, яке саме вхідне значення дає такий результат, можна скористатися функцією " Підбір параметра ". Уявімо, що вам потрібно позичити трохи грошей. Ви знаєте, яка сума вам потрібна, як довго ви хотіли б виплачувати позику та скільки ви готові віддавати щомісяця. Скористайтеся функцією "Підбір параметра", щоб визначити відсоткову ставку, за якої ви зможете вчасно виконати свої зобов’язання.
Клітинки B1, B2 та B3 – це значення для суми позики, терміну її дії та відсоткової ставки.
Клітинка B4 відображає результат формули =PMT(B3/12,B2,B1).
Примітка.
Функція "Підбір параметра" працює лише з одним змінним вхідним значенням. Якщо ви хочете визначити кілька вхідних значень, наприклад суму позики та щомісячний платіж, скористайтеся надбудовою "Пошук розв'язання". Докладні відомості про надбудову "Пошук розв'язання" див. в розділі "Підготовка прогнозів і розширені бізнес-моделі", а також за посиланнями в розділі " Див. також ".
Використання таблиць даних для перегляду результатів однієї або двох змінних у формулі
Якщо у формулі використовується одна чи дві змінні, або кілька формул, які використовують одну спільну змінну, можна скористатися таблицею даних , щоб переглянути всі результати в одному місці. За допомогою таблиць даних можна швидко оглядати низки можливостей. Оскільки ви обмежуєтеся лише однією або двома змінними, результати легко читати та поширювати в табличній формі. Якщо для книги ввімкнуто автоматичне переобчислення, дані в таблицях даних відразу переобчислюються. Тому ви завжди матимете свіжі дані.
Клітинка B3 містить введене значення.
Клітинки C3, C4 та C5 – це значення, які Excel замінює на основі значення, введеного в клітинку B3.
Таблиця даних може включати не більше двох змінних. Якщо потрібно проаналізувати більшу кількість змінних, можна скористатися сценаріями. Хоча в них можна використати лише одну або дві змінні, зате в таблицю даних можна додати скільки завгодно різних змінних значень. Сценарій може містити не більше 32 різних значень, проте ви можете створити безліч сценаріїв.
Підготовка прогнозів і розширені бізнес-моделі
Якщо потрібно підготувати прогнози, за допомогою програми Excel можна автоматично створити майбутні значення на основі наявних даних або екстрапольовані значення, що базуються на лінійному тренді чи обчислених тенденціях зростання.
За допомогою маркера заповнення або команди "Ряди " можна ввести ряд значень, які відповідають простому лінійному тренду або тенденції зростання. Щоб розширити комплексні або нелінійні дані, ви можете скористатися функціями робочого аркуша або засобом регресивного аналізу в надбудові "Пакет аналізу".
Хоча підбір параметра може включати лише одну змінну, за допомогою надбудови "Пошук розв'язання" можна прогнозувати більше змінних, повертаючи їх назад. Вона дає змогу знайти оптимальне значення для формули в одній клітинці (яка називається цільовою) на аркуші.
Надбудова "Пошук розв'язання" працює з групами клітинок, пов'язаних із формулою в цільовій клітинці. Ця надбудова налаштовує значення в указаних змінюваних клітинках (які називаються регульованими), щоб отримати результат, визначений у формулі в цільовій клітинці. Ви можете обмежити значення, які надбудова "Пошук розв'язання" може використовувати в моделі. Ці обмеження можуть посилатися на інші клітинки, що впливають на формулу в цільовій клітинці.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.
Додаткові відомості
Використання надбудови "Пошук розв'язання" для бюджетування капітальних вкладень
Виявлення й вирішення проблем за допомогою надбудови "Пошук розв’язання"