Сценарій – це набір значень, що зберігаються в Excel і можуть автоматично заміняти один одного на аркуші. Ви можете створити різні групи значень, зберегти їх як сценарії та переключатися між ними, щоб переглянути різні результати.
Якщо в сценаріях потрібно використовувати певні відомості від кількох людей, ви можете зібрати ці відомості в окремих книгах, а потім об’єднати сценарії з різних книг в один.
Отримавши всі потрібні сценарії, можна створити звіт за сценарієм, який містить відомості з усіх сценаріїв.
Керування сценаріями здійснюється за допомогою майстра диспетчера сценаріїв із групи Аналіз "What-If" на вкладці "Дані ".
Види аналізу "what-if"
У програмі Excel передбачено три типи засобів What-If аналізу: сценарії, таблиці даних і підбір параметра. Сценарії та таблиці даних прогнозують можливі результати на основі наборів вхідних значень. Підбір параметра працює навпаки: прогнозує вхідні значення, які могли дати певний результат.
Кожен сценарій може включати до 32 змінних значень. Якщо потрібно проаналізувати понад 32 значення, які представляють лише одну-дві змінні, можна скористатися таблицями даних. Хоча в таблиці даних можна використати лише одну-дві змінні (одна для клітинки введення рядків і одна для клітинки введення стовпців), зате можна додати скільки завгодно різних змінних значень. Сценарій може мати не більше 32 різних значень, але ви можете створити безліч сценаріїв.
На додачу до цих трьох інструментів, можна інсталювати надбудови, які допомагають виконувати аналіз "what-if", наприклад надбудову "Пошук розв’язання". Ця надбудова схожа на підбір параметра, але може включати більше змінних. Прогнози також можна створити за допомогою маркера заповнення й різноманітних команд, вбудованих у програму Excel. Для складніших моделей можна скористатися надбудовою "Пакет аналізу".
Створення сценаріїв
Припустімо, що вам потрібно скласти бюджет, але ви не впевнені щодо свого прибутку. Ви можете визначити різні можливі значення прибутку за допомогою сценаріїв, а потім переключати їх, щоб виконати аналіз "what-if".
Наприклад, припустімо, що за песимістичним сценарієм бюджету валовий дохід складає 50 000 грн, а вартість проданих товарів – 13 200 грн, що дає вам 36 800 грн валового прибутку. Щоб визначити цей набір значень як сценарій, спочатку потрібно ввести значення на аркуші, як показано на зображенні нижче.
Змінювані клітинки містять значення, які ви вводите, а клітинка Результат містить формулу на основі змінюваних клітинок (на цій ілюстрації клітинка B4 містить формулу =B2-B3).
Далі потрібно зберегти ці значення як сценарій у діалоговому вікні Диспетчер сценаріїв. Перейдіть на вкладку > Дані What-If додати диспетчер > сценаріїв аналізу>.
У діалоговому вікні Додавання сценарію введіть ім’я "Песимістичний" і вкажіть, що для різних сценаріїв змінюються значення в клітинках B2 та B3. Якщо виділити змінювані клітинки на аркуші, перш ніж додавати сценарії, Диспетчер сценаріїв вставить ці клітинки автоматично, в іншому разі ви можете ввести їх вручну або скористатися діалоговим вікном для виділення клітинок праворуч від діалогового вікна "Змінювані клітинки".
Примітка.
У цьому прикладі лише дві змінювані клітинки (B2 та B3), але сценарій може містити до 32 таких клітинок.
Захист. Ви також можете захистити свої сценарії, вибравши відповідні параметри в розділі "Захист". Зніміть прапорці, якщо захист не потрібен.
- Установіть прапорець Заборонити зміни , щоб запобігти редагуванню сценарію, коли аркуш захищено.
- Установіть прапорець приховати, щоб сценарій не відображався, коли аркуш захищено.
Примітка.
Ці параметри застосовуються лише до захищених аркушів. Докладні відомості про захищені аркуші див. в статті Захист аркуша
Тепер припустімо, що за оптимістичним сценарієм бюджету валовий дохід складає 150 000 грн, а вартість проданих товарів – 26 000 грн, що дає вам 124 000 грн валового прибутку. Щоб визначити цей набір значень як сценарій, потрібно створити ще один сценарій, назвати його "Оптимістичний" і вказати інші значення для клітинок B2 (150 000) і B3 (26 000). Оскільки валовий прибуток (клітинка B4) – це формула ( різниця між доходом (B2) і витратами (B3) – клітинку B4 не потрібно змінювати для сценарію "Найкращий регістр".
Після збереження сценарію він стане доступним у списку сценаріїв, які можна використовувати в аналізі "what-if". З урахуванням значень на зображенні вище, якщо відобразити оптимістичний сценарій, значення на аркуші зміняться, як на зображенні нижче.
Об’єднання сценаріїв
Інколи всі відомості, потрібні для різних сценаріїв, містяться на одному аркуші або в одній книзі. Однак ви також можете зібрати відомості для сценаріїв із різних джерел. Наприклад, ви хочете скласти бюджет компанії. Для цього потрібно зібрати сценарії з різних відділів (як-от відділу збуту, бухгалтерії, виробничого, маркетингового та юридичного відділів), оскільки кожне з цих джерел має різні відомості, які потрібно врахувати в бюджеті.
Ці сценарії можна зібрати на одному аркуші за допомогою команди Об’єднати. Кожне джерело може надавати скільки завгодно значень змінюваних клітинок. Наприклад, усі відділи мають надати прогнози щодо витрат, і лише деякі з них – щодо доходів.
Якщо натиснути кнопку "Об’єднати", Диспетчер сценаріїв завантажить майстер Об’єднання сценаріїв зі списком усіх аркушів в активній книзі та списком інших відкритих книг. У майстрі повідомляється, скільки сценаріїв міститься в кожному вихідному аркуші, який ви вибираєте.
Під час збирання різних сценаріїв із різних джерел слід використовувати однакову структуру клітинок у кожній книзі. Наприклад, дохід завжди має записуватися в клітинку B2, а витрати – у клітинку B3. Якщо структура сценаріїв із різних джерел відрізняється, можуть виникнути труднощі з об’єднанням результатів.
Порада.
Радимо спочатку створити сценарій, а потім розіслати колегам копію книги, яка його містить. Так усі сценарії матимуть однакову структуру.
Звіти за сценаріями
Щоб порівняти кілька сценаріїв, можна створити звіт, який підсумовує їх на одній сторінці. У звіті можна переглянути сценарії поруч або представити їх у звіті зведеної таблиці.
Зведений звіт сценарію на основі попередніх двох прикладів сценаріїв матиме приблизно такий вигляд:
Ви помітите, що програма Excel автоматично додала рівні групування , які розгортатимуть і згортатимуть подання, клацаючи різні селектори.
У кінці підсумкового звіту з'явиться примітка про те, що стовпець Поточні значення представляє значення змінюваних клітинок під час створення підсумкового звіту за сценарієм, а клітинки, змінені для кожного сценарію, виділяються сірим кольором.
Примітка.
- За замовчуванням у цьому звіті використовуються посилання на клітинки, які позначають змінювані клітинки та клітинки результатів. Якщо створити іменовані діапазони клітинок, перш ніж створювати звіт, він міститиме імена діапазонів, а не посилання на клітинки.
- Звіти сценаріїв не переобчислюються автоматично. Якщо змінити значення сценарію, ці зміни не відображатимуться в наявному зведеному звіті, але відобразяться, якщо створити новий зведений звіт.
- Клітинки результатів не потрібні, щоб створити звіт за сценарієм, але знадобляться для звіту зведеної таблиці.
іменованих діапазонів"
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.
Додаткові відомості
Загальні відомості про аналіз "якщо"
Виявлення й вирішення проблем за допомогою надбудови "Пошук розв’язання"
Використання надбудови "Пакет аналізу" для аналізу складних даних
Способи уникнення недійсних формул