Це друга стаття із серії з п’яти частин про аналіз даних у Excel. У цьому розділі я покажу вам, як використовувати таблицю даних із двома змінними в Excel.
Інші статті цієї серії:
- Одна таблиця змінних даних у Excel.
- Менеджер сценаріїв в Excel.
- Пошук цілей в Excel.
- Розв’язувач Excel.
Перегляд відео - Таблиця даних із двома змінними в Excel
Таблиця даних із двома змінними найкраще підходить у ситуаціях, коли ви хочете побачити, як змінюється кінцевий результат, коли дві вхідні змінні змінюються одночасно (на відміну від таблиці даних однієї змінної, де змінюється лише одна з вхідних змінних).
Якщо ви хочете аналізувати дані, коли змінюється більше 2 змінних, менеджер сценарію - це найкращий спосіб.
Коли використовувати таблицю даних із двома змінними в Excel
Припустимо, у вас є набір даних, як показано нижче:
У наведеному вище наборі даних ми маємо суму позики, процентну ставку та кількість щомісячних платежів. На основі цих трьох вхідних змінних розраховується щомісячна виплата (вона червоного кольору, оскільки це відтік грошей). Для розрахунку щомісячної виплати використовується наступна формула:
= PMT (B2/12, B3, B1)
Тепер ви можете зробити аналіз, щоб побачити, яка ідеальна комбінація суми кредиту та кількості щомісячних платежів відповідає вашим вимогам. Наприклад, ви можете зберегти щомісячний платіж на рівні 500 доларів США або менше і проаналізувати, яка сума позики та строку оренди може дати вам це.
У такій ситуації слід використовувати таблицю даних із двома змінними.
Налаштування двох змінних таблиці даних в Excel
Нижче наведено кроки для налаштування таблиці даних із двома змінними в Excel:
- У стовпці містяться всі різні значення, які потрібно перевірити на кількість щомісячних платежів. У цьому прикладі ми перевіряємо 72, 84, 96… 240. У той же час розмістіть різні значення суми кредиту в рядку трохи вище значень стовпця (починаючи з однієї комірки праворуч), як показано на малюнку нижче.
- Введіть = B4 у комірку D1, яка на один рядок вище значень у стовпці. Це конструкція, якої потрібно керуватися під час роботи з двома таблицями змінних даних. Також переконайтеся, що значення у комірці D1 залежить від обох змінних (Кількість щомісячних платежів та сума позики). Це не працюватиме, якщо ви вручну введете значення в клітинку D1.
У цьому випадку клітинка D1 відноситься до комірки B4, значення якої обчислюється за формулою, яка використовує комірки B1, B2 і B3.
- Тепер усі дані налаштовані на використання для обчислення таблиці даних із двома змінними.
- Виберіть дані (D1: J16). Перейдіть на вкладку Дані -> Інструменти даних -> Що робити, якщо аналіз -> Таблиця даних
- У діалоговому вікні Таблиця даних використовуйте такі посилання:
- Осередок введення рядка: $ B $ 1
- Осередок введення стовпця: $ B $ 3
- Натисніть OK. Як тільки ви натискаєте OK, він миттєво заповнює всі порожні клітинки у вибраному діапазоні даних. Він швидко дає змогу переглянути щомісячні платежі за різні комбінації суми позики та кількості щомісячних платежів.
Наприклад, якщо ви хочете визначити комбінації Суми позики та Кількості щомісячних платежів, які призведуть до щомісячної виплати менше ніж 500 доларів на місяць, ви можете просто скористатися цим методом таблиці зі 2 змінними.
Примітка:
- Після того, як ви обчислили значення за допомогою таблиці даних, її не можна скасувати за допомогою Control + Z. Однак ви можете вручну вибрати всі значення та видалити їх.
- Ви не можете видаляти/змінювати жодну клітинку у всьому наборі розрахованих значень. Оскільки це масив, вам доведеться видалити всі значення.
