Увійти за допомогою облікового запису Microsoft
Увійдіть або створіть обліковий запис.
Привіт,
Select a different account.
У вас кілька облікових записів
Виберіть обліковий запис, за допомогою якого потрібно ввійти.

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

Додаткові відомості

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

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

Загальні відомості про аналіз "якщо"

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

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

Огляд формул в Excel

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

Виявлення та виправлення помилок у формулах

Сполучення клавіш в Excel

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

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

Потрібна додаткова довідка?

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

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

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?

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

×