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