Як користуватися диспетчером сценаріїв у Excel 2013

Параметр «Диспетчер сценаріїв» у Excel 2013 у спадному меню кнопки «Аналіз що якщо» на вкладці «Дані» на стрічці дає змогу створювати та зберігати набори різних вхідних значень, які дають різні обчислені результати, іменовані сценаріями . Оскільки ці сценарії зберігаються як частина книги, ви можете використовувати їх значення, щоб відтворити що-якщо, просто відкривши Менеджер сценаріїв і запропонувавши Excel показати сценарій на робочому аркуші.

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

Як налаштувати різні сценарії в Excel 2013

Ключом до створення різних сценаріїв для таблиці є визначення різних клітинок у даних, значення яких можуть змінюватися в кожному сценарії. Потім ви виділяєте ці клітинки (відомі як змінні клітинки) на робочому аркуші, перш ніж відкрити діалогове вікно «Диспетчер сценаріїв», клацнувши «Дані» → «Аналіз що якщо» → «Диспетчер сценаріїв» на стрічці або натиснувши Alt+AWS.

Нижче ви побачите таблицю Sales Forecast 2014 після вибору трьох змінних осередків на робочому аркуші — H3 з назвою Sales_Growth, H4 з назвою COGS (Собівартість проданих товарів) і H6 з назвою Витрати — а потім відкриєте діалогове вікно Менеджер сценаріїв (Alt+AWS). ).

Давайте створимо три сценарії, використовуючи такі набори значень для трьох змінних клітинок:

• Найімовірніший випадок, коли відсоток приросту продажів становить 5%, COGS – 20%, а витрати – 28%

• Найкращий випадок, коли відсоток приросту продажів становить 8%, COGS – 18%, а витрати – 20%

• Найгірший випадок, коли відсоток приросту продажів становить 2%, COGS – 25%, а витрати – 35%

Щоб створити перший сценарій, натисніть кнопку «Додати» у діалоговому вікні «Диспетчер сценаріїв», щоб відкрити діалогове вікно «Додати сценарій», введіть « Найвірогідніший випадок» у полі «Назва сценарію», а потім натисніть «ОК». (Пам’ятайте, що три клітинки, вибрані в даний момент на аркуші, H3, H4 і H6, уже перераховані в текстовому полі «Зміна клітинок» цього діалогового вікна.)

Потім Excel відображає діалогове вікно «Значення сценарію», де ви приймаєте такі значення, які вже введені в кожному з трьох текстових полів (з таблиці «Прогноз продажів 2014»), Sales_Growth, COGS і Expenses, перш ніж натиснути кнопку «Додати»:

• 0,05 у текстовому полі Sales_Growth

• 0,2 у текстовому полі COGS

• 0,28 у текстовому полі Витрати

Завжди призначайте назви діапазонів змінним клітинкам, перш ніж почати створювати різні сценарії, які їх використовують. Таким чином, Excel завжди відображає назви діапазонів клітинок, а не їх адреси в діалоговому вікні «Значення сценарію».

Після натискання кнопки «Додати» Excel знову відображає діалогове вікно «Додати сценарій», де ви вводите « Найкращий випадок» у полі «Назва сценарію» та наступні значення в діалоговому вікні «Значення сценарію».

• 0,08 у текстовому полі Sales_Growth

• 0,18 у текстовому полі COGS

• 0,20 у текстовому полі Витрати

Після внесення цих змін знову натисніть кнопку Додати. Після цього відкриється діалогове вікно Додати сценарій, де ви вводите найгірший випадок як назву сценарію та такі значення сценарію:

• 0,02 у текстовому полі Sales_Growth

• 0,25 у текстовому полі COGS

• 0,35 у текстовому полі Витрати

Оскільки це останній сценарій, який ви хочете додати, натисніть кнопку OK замість Додати. Зробивши це, знову відкриється діалогове вікно Менеджера сценаріїв, на цей раз у вікні списку сценаріїв відображаються назви всіх трьох сценаріїв — найімовірніший, найкращий та найгірший випадок.

Щоб Excel вставив змінені значення, призначені будь-якому з цих трьох сценаріїв, до таблиці Sales Forecast 2014, клацніть назву сценарію в цьому списку, а потім кнопку Показати.

Додавши різні сценарії для таблиці в електронну таблицю, не забудьте зберегти книгу після закриття діалогового вікна Менеджера сценаріїв. Таким чином, ви матимете доступ до різних сценаріїв кожного разу, коли відкриватимете книгу в Excel, просто відкриваючи Менеджер сценаріїв, вибираючи назву сценарію та натискаючи кнопку Показати.

Як створити підсумковий звіт Excel 2013

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

Щоб створити підсумковий звіт, відкрийте діалогове вікно Менеджера сценаріїв (Дані→ Аналіз що, якщо→ Менеджер сценаріїв або Alt+AWS), а потім натисніть кнопку Підсумок, щоб відкрити діалогове вікно Підсумок сценарію.

Це діалогове вікно дає вам вибір між створенням (статичного) підсумку сценарію (за замовчуванням) і (динамічного) звіту зведеної таблиці сценаріїв. Ви також можете змінити діапазон комірок у таблиці, яка включена в розділ «Комірки результату» підсумкового звіту, налаштувавши діапазон комірок у текстовому полі «Комірки результату», перш ніж натиснути «ОК», щоб створити звіт.

Після натискання кнопки «ОК» Excel створює підсумковий звіт для змінених значень у всіх сценаріях (і на поточному аркуші) разом із обчисленими значеннями в клітинках результатів на новому аркуші (під назвою «Зведення сценарію»). Потім ви можете перейменувати та змінити розташування робочого аркуша Підсумок сценарію, перш ніж зберегти його як частину файлу книги.