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

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

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

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

Додайте різні сценарії до таблиці «Прогноз продажів на 2017 рік».

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

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

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

• Найгірший випадок, коли відсоток Sales_Growth становить 2%, COGS становить 25%, а витрати становлять 20%

Щоб створити перший сценарій, натисніть кнопку «Додати» у діалоговому вікні «Диспетчер сценаріїв», щоб відкрити діалогове вікно «Додати сценарій», введіть « Найвірогідніший випадок» у полі «Назва сценарію», а потім натисніть «ОК».

Пам’ятайте, що три клітинки, вибрані в даний момент на аркуші, G3, G4 і G6, вже перераховані в текстовому полі «Зміна клітинок» цього діалогового вікна. Зверніть увагу, що Excel 2016 автоматично встановлює прапорець «Запобігати змінам» під заголовком «Захист» у нижній частині діалогового вікна «Додати сценарій», щоб користувачі не могли змінювати значення в цих клітинках аркуша. Якщо ви хочете, щоб їх значення також були приховані, обов’язково виберіть параметр Приховати, перш ніж натиснути кнопку OK.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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