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

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

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

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

Сценаріями можна керувати за допомогою майстра Диспетчер сценаріїв (вкладка Дані, група Аналіз «якщо»).

В 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), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel UserVoice.

Див. також

Таблиці даних

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

Загальні відомості про аналіз "what-if"

Визначення та вирішення задач за допомогою надбудови "Пошук розв’язання"

Використання надбудови "Пакет аналізу" для аналізу складних даних

Огляд формул у програмі Excel

Способи уникнення недійсних формул

Пошук і виправлення помилок у формулах

Сполучення клавіш у програмі Excel для Windows

Сполучення клавіш у програмі Excel для Mac

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×