Таблиця даних у Excel (Зміст)
- Таблиця даних в Excel
- Як створити таблицю даних в Excel?
Таблиця даних в Excel
Таблиці даних використовуються для аналізу змін, виявлених у вашому кінцевому результаті, коли певні змінні змінюються з вашої функції чи формули. Таблиці даних є однією з існуючих частин інструментів аналізу What-If, які дозволяють спостерігати за результатами, експериментуючи з різними значеннями змінних та порівнюючи результати, збережені в таблиці даних.
Існує два типи даних :
- Таблиця даних з однією змінною.
- Таблиця даних з двома змінними.
Як створити таблицю даних в Excel?
Таблицю даних у Excel дуже просто та легко створити. Давайте розберемося з роботою таблиці даних у Excel за деякими прикладами.
Ви можете завантажити цю таблицю даних Excel Шаблон таблиці тут - Шаблон таблиці ExcelТаблиця даних в прикладі Excel №1 - Таблиця даних з одною змінною
Таблиці даних з однією змінною ефективні в разі аналізу змін результату вашої формули при зміні значень для однієї вхідної змінної.
Використовуйте випадок таблиці з однією змінною в Excel:
Таблиця даних з однією змінною корисна в сценаріях, коли людина може спостерігати, як різні процентні ставки змінюють суму своєї іпотечної суми, яку потрібно сплатити. Розглянемо рисунок нижче, який показує розмір іпотеки, обчислений на основі процентної ставки за допомогою функції PMT.
У таблиці вище наведені дані, де розмір іпотеки обчислюється на основі процентної ставки, періоду іпотеки та суми позики. Він використовує формулу PMT для обчислення місячної суми іпотеки, яку можна записати як = PMT (C4 / 12, C5, -C3).
У разі дотримання місячної суми іпотеки за різними процентними ставками, де процентна ставка вважається змінною. Для цього необхідно створити одну таблицю змінних даних. Крок для створення однієї таблиці змінних даних полягає в наступному:
Крок 1: Підготуйте стовпчик, що складається з різних значень процентних ставок. У стовпчик, який виділено на малюнку, ми ввели різні значення процентних ставок.
Крок 2: У комірці (F2), яка знаходиться на один рядок вище, і діагоналі до стовпця, який був підготовлений вами на попередньому кроці, введіть this = C6.
Крок 3: Виберіть весь підготовлений стовпець за значеннями різних відсоткових ставок разом із коміркою, куди ви вставили значення, тобто клітинку F2.
Крок 4: Клацніть на вкладці "Дані" та виберіть "Що робити, якщо аналіз", а з висказаних опцій виберіть "Таблиця даних".
Крок 5: З'явиться діалогове вікно таблиці даних.
Крок 6: У комірці введення стовпця зверніться до комірки C4 та натисніть кнопку ОК.
У діалоговому вікні ми посилаємося на комірку C4 у комірці введення стовпця і тримаємо комірку введення рядків порожньою, оскільки ми готуємо таблицю даних з однією змінною.
Крок 7: Після виконання всіх кроків ми отримуємо всі різні суми іпотеки для всіх введених процентних ставок у колонці E (без позначки), а різні суми іпотеки спостерігаються у графі F (позначено).
Таблиця даних у прикладі Excel №2 - Таблиця даних з двома змінними
Дві змінні таблиці даних корисні в сценаріях, коли користувачеві необхідно спостерігати за змінами в результаті своєї формули, коли вони змінюють дві вхідні змінні одночасно.
Приклад використання таблиці з двома змінними в Excel:
Таблиця з двома змінними є корисною у сценаріях, коли людина може спостерігати, як різні процентні ставки та суми позики змінюють суму своєї іпотечної суми, яку потрібно сплатити. Замість обчислення окремих значень окремо ми можемо спостерігати це з миттєвими результатами. Розглянемо рисунок нижче, який показує розмір іпотеки, обчислений на основі процентної ставки за допомогою функції PMT.
Наведений вище приклад подібний нашому прикладу, показаному в попередньому випадку для однієї таблиці змінних даних. Тут розмір іпотеки в комірці С6 розраховується виходячи з процентної ставки, строку іпотеки та суми позики. Він використовує формулу PMT для обчислення місячної суми іпотеки, яку можна записати як = PMT (C4 / 12, C5, -C3).
Для того, щоб пояснити таблицю даних із двома змінними з посиланням на наведений вище приклад, ми покажемо різні суми іпотеки та виберемо найкращий, який вам підходить, дотримуючись різних значень процентних ставок та суми позики. Для цього потрібно створити таблицю даних із двома змінними. Крок для створення однієї таблиці змінних даних полягає в наступному:
Крок 1: Підготуйте стовпчик, який складається з різних значень процентних ставок та суми позики.
Ми підготували стовпець, що складається з різних процентних ставок, і в діагоналі клітинки до початкової комірки стовпця ми ввели різні значення суми позики.
Крок 2: У комірці (E2), що знаходиться на один рядок вище, до стовпця, який ви підготували на попередньому кроці, введіть this = C6.
Крок 3: Виберіть весь підготовлений стовпець за значеннями різних відсоткових ставок разом із коміркою, куди ви вставили значення, тобто клітинку E2.
Крок 4: Клацніть на вкладці "Дані" та виберіть "Що робити, якщо аналіз", а з висказаних опцій виберіть "Таблиця даних".
Крок 5: З'явиться діалогове вікно таблиці даних. У "Вхідній комірці стовпця" посилайтесь на клітинку C4, а у "Вхідну клітинку рядка" C3. Обидва значення вибираються, коли ми змінюємо обидві змінні та натискаємо кнопку ОК.
Крок 6: Після виконання всіх кроків ми отримуємо різні значення сум іпотеки для різних значень процентних ставок та суми позики.
Що слід пам’ятати про таблицю даних в Excel
- Для однієї таблиці змінних даних "Вхідна комірка рядків" залишається порожньою, а в двох таблицях змінних даних заповнюються "Вхідна комірка рядка" та "Вхідна комірка стовпця".
- Після того, як буде проведено аналіз What-If і обчислені значення, ви не можете змінити або змінити будь-яку комірку з набору значень.
Рекомендовані статті
Це керівництво до таблиці даних у Excel. Тут ми обговорюємо його типи та способи створення прикладів таблиці даних та шаблонів Excel, які можна завантажити. Ви також можете переглянути ці корисні функції у відмінність -
- Посібник з функції DATEVALUE в Excel
- ЧАСТОТА Функція Excel, яку ви повинні знати
- Як використовувати функцію прогнозу Excel?
- COUNTIF Функція Microsoft Excel