Перехід між різними наборами значень за допомогою сценаріїв

В Excel передбачено засоби аналізу "what-if" ("якщо") трьох видів: сценарії, таблиці даних і підбір параметра. Сценарії та таблиці даних прогнозують можливі результати на основі наборів вхідних значень. Підбір параметра працює навпаки: прогнозує вхідні значення, які могли дати певний результат.

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

На додачу до цих трьох інструментів, можна інсталювати надбудови, які допомагають виконувати аналіз "what-if", наприклад надбудову "Пошук розв’язання". Ця надбудова схожа на підбір параметра, але може включати більше змінних. Прогнози також можна створити за допомогою маркера заповнення й різноманітних команд, вбудованих у програму Excel. Для складніших моделей можна скористатися надбудовою "Пакет аналізу".

Припустімо, що вам потрібно скласти бюджет, але ви не впевнені щодо свого прибутку. Ви можете визначити різні можливі значення прибутку за допомогою сценаріїв, а потім переключати їх, щоб виконати аналіз "what-if".

Наприклад, припустімо, що за песимістичним сценарієм бюджету валовий дохід складає 50 000 грн, а вартість проданих товарів – 13 200 грн, що дає вам 36 800 грн валового прибутку. Щоб визначити цей набір значень як сценарій, спочатку потрібно ввести значення на аркуші, як показано на зображенні нижче.

Змінювані клітинки містять значення, які вводяться, а клітинка результату – формулу, що використовує змінювані клітинки (на цьому зображенні клітинка B4 містить формулу =B2-B3).

Далі потрібно зберегти ці значення як сценарій у діалоговому вікні Диспетчер сценаріїв. Виберіть елементи Дані > Аналіз «якщо» > Диспетчер сценаріїв > Додати.

У діалоговому вікні Додавання сценарію введіть ім’я "Песимістичний" і вкажіть, що для різних сценаріїв змінюються значення в клітинках B2 та B3. Якщо виділити змінювані клітинки на аркуші, перш ніж додавати сценарії, Диспетчер сценаріїв вставить ці клітинки автоматично, в іншому разі ви можете ввести їх вручну або скористатися діалоговим вікном для виділення клітинок праворуч від діалогового вікна "Змінювані клітинки".

Захист. Ви також можете захистити свої сценарії, вибравши відповідні параметри в розділі "Захист". Зніміть прапорці, якщо захист не потрібен.

• Установіть прапорець заборонити зміни, щоб заборонити редагування сценарію, коли аркуш захищено.

• Установіть прапорець приховати, щоб сценарій не відображався, коли аркуш захищено.

Тепер припустімо, що за оптимістичним сценарієм бюджету валовий дохід складає 150 000 грн, а вартість проданих товарів – 26 000 грн, що дає вам 124 000 грн валового прибутку. Щоб визначити цей набір значень як сценарій, потрібно створити ще один сценарій, назвати його "Оптимістичний" і вказати інші значення для клітинок B2 (150 000) і B3 (26 000). Оскільки валовий прибуток у клітинці B4 обчислюється за формулою (як різниця між доходом у клітинці B2 і витратами у клітинці B3), клітинку B4 не потрібно змінювати для оптимістичного сценарію.

Коли ви збережете сценарій, він стане доступним у списку сценаріїв, які ви можете використовувати для аналізу "what-if". З урахуванням значень на зображенні вище, якщо відобразити оптимістичний сценарій, значення на аркуші зміняться, як на зображенні нижче.

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

Ці сценарії можна зібрати на одному аркуші за допомогою команди Об’єднати. Кожне джерело може надавати скільки завгодно значень змінюваних клітинок. Наприклад, усі відділи мають надати прогнози щодо витрат, і лише деякі з них – щодо доходів.

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

Збираючи різні сценарії з кількох джерел, слід використовувати однакову структуру клітинок у кожній книзі. Наприклад, дохід завжди має записуватися в клітинку B2, а витрати – у клітинку B3. Якщо структура сценаріїв із різних джерел відрізняється, можуть виникнути труднощі з об’єднанням результатів.

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

Звіт за сценарієм на основі сценаріїв із попередніх двох прикладів мав би приблизно такий вигляд:

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

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