Як виконувати аналізи «що якщо» з таблицями даних у Excel 2016

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

Використання таблиці з одним введенням для аналізу

У таблиці з одним введенням ви дізнаєтеся, якими будуть різні результати формули, якщо змінити одну вхідну клітинку у формулі. Тут ця вхідна клітинка є процентною ставкою за позикою. Метою цієї таблиці даних є з’ясувати, як відрізняються щомісячні платежі за 30-річною іпотекою в розмірі 250 000 доларів США з урахуванням різних процентних ставок. Процентна ставка в комірці B4 є вхідною коміркою.

Таблиця даних з одним введенням.

Щоб створити таблицю з одним введенням, виконайте наведені нижче дії.

На робочому аркуші введіть значення, які ви хочете замінити значенням у комірці введення.

Щоб таблиця введення працювала, потрібно ввести замінні значення в потрібне місце:

• У стовпці: введіть значення в стовпці, починаючи з однієї клітинки нижче та однієї клітинки ліворуч від клітинки, де розташована формула. У наведеному прикладі формула знаходиться в клітинці E4, а значення знаходяться в діапазоні клітинок D5:D15.

• У рядку: введіть значення в рядку, починаючи з однієї клітинки вище та однієї клітинки праворуч від клітинки, де знаходиться формула.

Виділіть блок комірок з формулою та замініть значення.

Виділіть прямокутник клітинок, який охоплює клітинку формули, клітинку поруч із нею, усі замінні значення та порожні клітинки, де незабаром з’являться нові обчислення.

• У стовпці: виберіть клітинку формули, комірку зліва від неї, усі клітинки із замінними значеннями та клітинки під коміркою формули.

• У рядку: виберіть клітинку формули, комірку над нею, замінні значення в клітинках праворуч і пусті клітинки, де з’являться нові обчислення.

На вкладці «Дані» натисніть кнопку «Аналіз «Що якщо»» і виберіть у спадному списку «Таблиця даних».

Ви побачите діалогове вікно Таблиця даних.

У текстовому полі Комірка введення рядка або Комірка введення стовпця введіть адресу комірки, де розташоване введене значення.

Щоб ввести цю адресу комірки, вийдіть за межі діалогового вікна Таблиця даних і клацніть клітинку. Вхідне значення — це значення, з яким ви експериментуєте під час аналізу. У випадку з показаним робочим аркушем введене значення розташоване в клітинці B4, клітинці, яка містить процентну ставку.

Якщо нові обчислення відображаються в рядках, введіть адресу комірки введення в текстове поле Комірка введення рядка; якщо обчислення відображаються в стовпцях, введіть адресу вхідної комірки в текстовому полі Комірка введення стовпця.

Натисніть OK.

Excel виконує обчислення та заповнює таблицю.

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

Використання таблиці з двома входами для аналізу

У таблиці з двома вводами ви можете експериментувати з двома вхідними клітинками, а не з однією. Повертаючись до прикладу платежу по кредиту, ви можете обчислити не тільки те, як змінюються платежі за кредитом із зміною процентних ставок, але й як змінюються платежі, якщо змінюється термін кредиту. Цей малюнок показує таблицю з двома вхідними параметрами для аналізу щомісячних платежів за кредитом з урахуванням різних процентних ставок і двох різних термінів кредиту: 15 років (180 місяців) і 30 років (360 місяців).

Таблиця з двома вхідними даними.

Виконайте такі дії, щоб створити таблицю з двома вхідними даними:

Введіть один набір замінних значень під формулою в тому самому стовпці, що й формула.

Тут різні процентні ставки вводяться в діапазон клітинок D5:D15.

Введіть другий набір замінних значень у рядку праворуч від формули.

Тут вводяться 180 і 360. Ці цифри представляють кількість місяців терміну дії позики.

Виберіть формулу та всі замінні значення.

Зробіть це правильно, і ви виберете три стовпці, включаючи формулу, замінні значення під нею та два стовпці праворуч від формули. Ви виділяєте великий блок комірок (діапазон D4:F15, у цьому прикладі).

На вкладці «Дані» натисніть кнопку «Аналіз «Що якщо»» і виберіть у спадному списку «Таблиця даних».

З’явиться діалогове вікно Таблиця даних.

У текстовому полі Комірка введення рядка введіть адресу комірки, на яку посилається вихідна формула, до якої можна підключити замінні значення праворуч від формули.

Введіть адресу комірки, вийшовши за межі діалогового вікна та вибравши клітинку. Тут, наприклад, рядки праворуч від формули вказують на довжину значень-замінників позики. Тому виберіть комірку B5, клітинку, на яку посилається вихідна формула, де вказано тривалість позики.

У текстовому полі Комірка введення стовпця введіть адресу комірки, на яку посилається вихідна формула, де є замінні значення під формулою.

Замінні значення під клітинкою формули є процентними ставками. Тому виберіть клітинку B4, комірку, на яку посилається вихідна формула, де вводиться процентна ставка.

Натисніть OK.

Excel виконує обчислення та заповнює таблицю.