Обчислення кількох результатів за допомогою таблиці даних

У Microsoft Excel таблиці даних є частиною набору команд, відомих як засоби аналізу "what-if". Коли ви створюєте та аналізуєте таблиці даних, ви виконуєте аналіз "what-if".

Аналіз "what-if" – це процес змінення значень у клітинках, що дає змогу переглянути вплив цих змін на результат формул на аркуші. Наприклад, за допомогою таблиці даних можна оцінювати суму потенційних щомісячних виплат у разі зміни відсоткової ставки та терміну позики.

Типи аналізу "what-if"    

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

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

Таблиця даних може включати не більше двох змінних. Якщо потрібно проаналізувати більшу кількість змінних, слід використовувати сценарії. Хоча в таблиці даних можна використати лише одну-дві змінні (одна для клітинки введення рядків і одна для клітинки введення стовпців), але можна додати скільки завгодно різних змінних значень. Сценарій може мати не більше 32 різних значень, але ви можете створити безліч сценаріїв.

Дізнайтеся більше в статті Вступ до аналізу "what-if".

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

Таблиці даних з однією змінною    

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

На ілюстрації нижче клітинка D2 містить формулу платежу =PMT(B3/12,B4,-B5), яка посилається на клітинку введення B3.

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

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

На ілюстрації нижче клітинка C2 містить формулу платежу =PMT(B3/12,B4,-B5), яка використовує дві клітинки введення: B3 та B4.

 

Обчислення в таблиці даних    

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

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

Указівки

1. Введіть список значень, які потрібно підставити в клітинку введення – одним стовпцем або одним рядком. Залиште кілька пустих рядків і стовпців з обох боків від значень.

2. Виконайте одну з таких дій:

   • Якщо таблиця даних орієнтована на стовпець (значення змінних містяться в стовпці), введіть формулу в клітинку на один рядок вище і на одну клітинку праворуч від стовпця значень. Ця таблиця даних з однією змінною орієнтована на стовпець, а формула міститься в клітинці D2.
     
     
     
     Якщо потрібно дослідити вплив різних значень на інші формули, введіть додаткові формули в клітинки праворуч від першої формули.

   • Якщо таблиця даних орієнтована на рядок (значення змінних у рядку), введіть формулу в клітинку на один стовпець ліворуч від першого значення і на одну клітинку нижче рядка значень.
     
     Якщо потрібно дослідити вплив різних значень на інші формули, введіть додаткові формули в клітинки під першою формулою.

3. Виберіть діапазон клітинок, що містить формули та значення, які потрібно підставити. На рисунку вище цей діапазон – C2:D5.

4. На вкладці Дані виберіть Аналіз "what-if" > Таблиця даних (у групі Знаряддя даних або Прогноз у Excel 2016).

5. Виконайте одну з таких дій:

   • Якщо таблиця даних орієнтована на стовпець, введіть посилання на клітинку для клітинки введення в полі Клітинка введення стовпця. На рисунку вище клітинка введення – B3.

   • Якщо таблиця даних орієнтована на рядок, введіть посилання на клітинку введення в полі Клітинка введення рядка.

     Примітка.: Після створення таблиці даних може знадобитися змінити формат клітинок результатів. На рисунку клітинки результатів мають формат грошової одиниці.

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

Ось як це зробити

1. Виконайте одну з таких дій:

   • Якщо таблиця даних орієнтована на стовпець, введіть нову формулу в пусту клітинку праворуч від наявної формули у верхньому рядку таблиці даних.

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

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

3. На вкладці Дані виберіть Аналіз "what-if" > Таблиця даних (у групі Знаряддя даних або Прогноз у Excel 2016).

4. Виконайте одну з таких дій:

   • Якщо таблиця даних орієнтована на стовпець, введіть посилання на клітинку введення в полі Клітинка введення стовпця.

   • Якщо таблиця даних орієнтована на рядок, введіть посилання на клітинку введення в полі Клітинка введення рядка.

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

Указівки

1. У клітинці на аркуші введіть формулу, яка посилається на дві клітинки введення.

   У наведеному прикладі, де початкові значення формули введено в клітинки B3, B4 і B5, введіть формулу =PMT(B3/12,B4,-B5) у клітинку C2.

2. Введіть один список вхідних значень у тому самому стовпці нижче формули.

   У цьому випадку введіть різні відсоткові ставки в клітинках C3, C4 та C5.

3. Введіть другий список у тому самому рядку, що й формула, праворуч від неї.

   Введіть терміни позики (у місяцях) в клітинках D2 та E2.

4. Виділіть діапазон клітинок, який містить формулу (C2), рядок і стовпець значень (C3:C5 і D2:E2), а також клітинки, у яких потрібно обчислити значення (D3:E5).

   У цьому випадку виберіть діапазон C2:E5.

5. На вкладці Дані в групі Знаряддя даних або Прогноз (у Excel 2016) виберіть Аналіз "what-if" > Таблиця даних (у групі Знаряддя даних або Прогноз у Excel 2016).

6. У полі Клітинка введення рядка введіть посилання на клітинку введення для вхідних значень у рядку.
   Введіть B4 у поле Клітинка введення рядка.

7. У полі Клітинка введення стовпця введіть посилання на клітинку введення для вхідних значень у стовпці.
   Введіть B3 у поле Клітинка введення стовпця.

8. Натисніть кнопку OK.

Приклад таблиці даних із двома змінними

Таблиця даних із двома змінними показує, як різні комбінації відсоткової ставки та терміну позики впливають на щомісячний іпотечний платіж. На рисунку нижче клітинка C2 містить формулу платежу =PMT(B3/12,B4,-B5), яка використовує дві клітинки введення: B3 та B4.

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

Виконайте такі дії, щоб підвищити продуктивність обчислення:

1. Виберіть Файл > Параметри > Формули.

2. У розділі Параметри обчислення виберіть Автоматично.

   Порада.: За потреби на вкладці Формули клацніть стрілку поруч із кнопкою Параметри обчислення, а потім виберіть Автоматично.

Якщо ви маєте конкретні цілі або великі набори змінних даних, можна скористатися деякими іншими засобами Excel для проведення аналізу "what-if".

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

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

Розв’язник Excel

Надбудова "Розв’язник Excel" дає змогу знайти оптимальне значення для набору вхідних змінних. Розв’язник працює із групою клітинок (які називаються клітинками змінних рішення або просто клітинками змінних), що використовуються для обчислення формул у цільових функціях і клітинках обмежень. Надбудова регулює значення у клітинках змінних відповідно до меж у клітинках обмежень і виводить потрібний результат у клітинці цільової функції. Докладніше в цій статті: Визначення та розв’язання задачі за допомогою Розв’язника.