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

Що таке таблиця даних в Excel?

Створіть таблицю даних з однією змінною

• Таблиця даних, орієнтована на стовпці
• Рядково-орієнтована таблиця даних
• Кілька формул для таблиці даних з однією змінною

Створіть таблицю даних із двома змінними

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

Інші операції з використанням таблиць даних

• Видалити таблицю даних
• Редагувати результат таблиці даних
• Перерахувати таблицю даних вручну

Що таке таблиця даних в Excel?

У Excel таблиця даних — це один із інструментів аналізу «Що, якщо», який дозволяє експериментувати з різними вхідними значеннями для формул і спостерігати за змінами у вихідних даних формули. Цей інструмент надзвичайно корисний для вивчення різних сценаріїв і проведення аналізу чутливості, особливо коли формула залежить від кількох змінних.

В Excel є два типи таблиць даних:

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

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

Створіть таблицю даних з однією змінною

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

Дивовижний! Використання ефективних вкладок у Excel, таких як Chrome, Edge, Firefox і Safari!
Заощаджуйте 50% свого часу та зменшуйте тисячі клацань миші щодня!

---

Таблиця даних, орієнтована на стовпці

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

Припустімо, ви розглядаєте позику в розмірі 50,000 3 доларів США, яку плануєте погасити протягом 36 років (еквівалентно XNUMX місяцям). Щоб оцінити, який щомісячний платіж буде прийнятним виходячи з вашої зарплати, вам цікаво дізнатися, як різні відсоткові ставки вплинуть на суму, яку вам потрібно платити щомісяця.

Крок 1. Налаштуйте базову формулу

Для розрахунку платежу тут я встановлю відсотки 5%. У комірку B4 введіть формулу PMT, яка розраховує щомісячний платіж на основі відсоткової ставки, кількості періодів і суми кредиту. Перегляньте скріншот:

= -PMT($B$1/12, $B$2*12, $B$3)

Крок 2: Перелічіть процентні ставки в стовпці

У стовпці вкажіть різні процентні ставки, які ви хочете перевірити. Наприклад, перелічіть значення від 4% до 11% із кроком 1% у стовпці та залиште принаймні один порожній стовпець праворуч для результатів. Перегляньте скріншот:

Крок 3: Створіть таблицю даних

1. У клітинку E2 введіть цю формулу: = B4.
   
   примітки: B4 це комірка, де розташована ваша основна формула, це формула, результати якої ви хочете бачити зміненими, коли ви змінюєте процентну ставку.
   
2. Виберіть діапазон, який включає вашу формулу, список процентних ставок і суміжні комірки для результатів (наприклад, виберіть від D2 до E10). Перегляньте скріншот:
   
3. Перейдіть до стрічки, натисніть на дані , а потім натисніть Що-якщо аналіз > Таблиця даних, див. скріншот:
   
4. У Таблиця даних діалоговому вікні, натисніть на Комірка введення стовпця (оскільки ми використовуємо стовпець для введення значень) і виберіть клітинку змінної, на яку посилається ваша формула. У цьому прикладі ми вибираємо B1, який містить процентну ставку. Нарешті натисніть OK , див. знімок екрана:
   
5. Excel автоматично заповнить порожні клітинки поруч із кожним зі значень ваших змінних (різні відсоткові ставки) відповідними результатами. Перегляньте скріншот:
   
6. Застосуйте потрібний числовий формат до результатів (валюта) відповідно до ваших потреб. Тепер таблицю даних, орієнтовану на стовпці, створено успішно, і ви можете швидко переглянути результати, щоб оцінити, які суми щомісячних платежів будуть доступними для вас, коли процентна ставка зміниться. Перегляньте скріншот:
   

Рядково-орієнтована таблиця даних

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

Крок 1. Перелічіть процентні ставки в рядку

Розташуйте значення змінних (процентні ставки) у рядку, переконавшись, що є принаймні один порожній стовпець ліворуч для формули та один порожній рядок нижче для результатів, див. знімок екрана:

Крок 2: Створіть таблицю даних

1. У клітинку A9 введіть цю формулу: = B4.
   
2. Виберіть діапазон, який включає вашу формулу, список процентних ставок і суміжні комірки для результатів (наприклад, виберіть від A8 до I9). Потім натисніть дані > Що-якщо аналіз > Таблиця даних.
3. У Таблиця даних діалоговому вікні, натисніть на Осередок введення рядка (оскільки ми використовуємо рядок для введення значень), і виберіть комірку змінної, на яку посилається ваша формула. У цьому прикладі ми вибираємо B1, який містить процентну ставку. Нарешті натисніть OK , див. знімок екрана:
   
4. Застосуйте потрібний числовий формат до результатів (валюта) відповідно до ваших потреб. Тепер таблиця даних, орієнтована на рядки, створена, дивіться знімок екрана:
   

Кілька формул для таблиці даних з однією змінною

Створення таблиці даних однієї змінної з кількома формулами в Excel дозволяє побачити, як зміна одного введення впливає на кілька різних формул одночасно. У наведеному вище прикладі, що, якщо ви хочете побачити зміну процентних ставок як для виплат, так і для загальних відсотків? Ось як це налаштувати:

Крок 1. Додайте нову формулу для розрахунку загальної суми відсотків

У клітинку B5 введіть таку формулу, щоб обчислити загальну суму відсотків:

=B4*B2*12-B3

Крок 2: Упорядкуйте вихідні дані таблиці даних

У стовпці перелічіть різні відсоткові ставки, які ви хочете перевірити, і залиште принаймні два порожні стовпці праворуч для результатів. Перегляньте скріншот:

Крок 3. Створіть таблицю даних:

1. У клітинку E2 введіть цю формулу: = B4 створити посилання на розрахунок погашення у вихідних даних.
2. У клітинку F2 введіть цю формулу: = B5 створити посилання на загальний інтерес до вихідних даних.
   
3. Виберіть діапазон, який включає ваші формули, список процентних ставок і суміжні клітинки для результату (наприклад, виберіть від D2 до F10). Потім натисніть дані > Що-якщо аналіз > Таблиця даних.
4. У Таблиця даних діалоговому вікні, натисніть на Осередок введення стовпця (оскільки ми використовуємо стовпець для введення значень) і виберіть клітинку змінної, на яку посилається ваша формула. У цьому прикладі ми вибираємо B1, який містить процентну ставку. Нарешті натисніть OK , див. знімок екрана:
   
5. Застосуйте потрібний числовий формат до результатів (валюта) відповідно до ваших потреб. І ви можете побачити результати для кожної формули на основі змінних значень змінних.
   

Створіть таблицю даних із двома змінними

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

Тут я намалював простий ескіз, щоб допомогти вам краще зрозуміти вигляд і структуру таблиці даних із двома змінними.

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

У наведеному нижче наборі даних ми маємо відсоткову ставку, термін і суму кредиту, а також розрахували щомісячний платіж за цією формулою: =-PMT($B$1/12, $B$2*12, $B$3) так само. Тут ми зосередимося на двох ключових змінних із наших даних: процентній ставці та сумі позики, щоб спостерігати, як зміни обох цих факторів одночасно впливають на суми погашення.

Крок 1: Налаштуйте дві змінні змінні

1. У стовпці вкажіть різні процентні ставки, які ви хочете перевірити. дивіться знімок екрана:
   
2. У рядку введіть різні значення суми позики безпосередньо над значеннями стовпця (починаючи з однієї клітинки праворуч від клітинки формули), див. знімок екрана:
   

Крок 2: Створіть таблицю даних

1. Розмістіть свою формулу на перетині рядка та стовпця, де ви перерахували значення змінних. У цьому випадку я введу цю формулу: = B4 у клітинку E2. Перегляньте скріншот:
   
2. Виберіть діапазон, який включає суми позики, процентні ставки, комірку формули та комірки, де відображатимуться результати.
   
3. Потім натисніть кнопку дані > Що-якщо аналіз > Таблиця даних. У діалоговому вікні Таблиця даних:
   • У Осередок введення рядка виберіть посилання клітинки на клітинку введення для значень змінних у рядку (у цьому прикладі B3 містить суму позики).
   • У Стовпець введення cell виберіть посилання клітинки на клітинку введення для значень змінних у стовпці (B1 містить процентну ставку).
   • А потім натисніть OK Кнопка.
     
4. Тепер Excel заповнить таблицю даних результатами для кожної комбінації суми кредиту та процентної ставки. Він забезпечує пряме уявлення про те, як різні комбінації сум кредиту та процентних ставок впливають на щомісячний платіж, що робить його цінним інструментом для фінансового планування та аналізу.
   
5. Нарешті, вам слід застосувати потрібний числовий формат до результатів (валюта) відповідно до ваших потреб.
   

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

• Щойно створена таблиця даних має бути на тому самому аркуші, що й вихідні дані.
• Результат таблиці даних залежить від комірки формули у вихідному наборі даних, і будь-які зміни в цій комірці формули автоматично оновлюють вихідні дані.
• Після обчислення значень за допомогою таблиці даних їх неможливо скасувати Ctrl + Z. Однак ви можете вручну вибрати всі значення та видалити їх.
• Таблиця даних генерує формули масиву, тому окремі клітинки в таблиці не можна змінити або видалити.

Інші операції з використанням таблиць даних

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

Видалити таблицю даних

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

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

Редагувати результат таблиці даних

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

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

Перерахувати таблицю даних вручну

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

Щоб уникнути автоматичного виконання Excel обчислень для всіх таблиць даних щоразу, коли в робочій книзі вноситься зміна, можна змінити режим обчислення з автоматичного на ручний. Будь ласка, зробіть наступне:

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

Після зміни цього параметра під час повторного обчислення всієї книги Excel більше не буде автоматично оновлювати обчислення у ваших таблицях даних.

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

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