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

У програмі 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 Analysis "> Таблиці даних (у групі Знаряддя даних або Прогнозування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" > Data Table (Таблиця даних ) (у групі Знаряддя даних або Прогнозування Excel 2016 ).

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

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

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

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

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

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

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

1. Виберіть пункт Параметри файлу > > формули.

2. У розділі Параметри обчислення в розділі Обчислення натисніть кнопку Автоматично, за винятком таблиць даних.

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

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

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

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

Пошук розв'язання Excel

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