Таблиця підстановок, або таблиця даних (Data Table), — інструмент Excel, що за один раз обчислює формулу для багатьох значень однієї або двох змінних і виводить усі результати в одну таблицю. Його запускають на вкладці «Дані» → «Аналіз what-if» → «Таблиця даних». Типовий приклад — щомісячний платіж за кредитом при різних ставках і термінах.
Коли потрібна таблиця даних
За довідкою Microsoft, у Excel є три засоби аналізу «що, якщо»:
| Засіб | Що робить | Коли брати |
|---|---|---|
| Таблиця даних | підставляє список значень однієї або двох змінних у формулу і показує всі результати разом | порівняти багато варіантів одразу |
| Сценарії | зберігають набори вхідних значень і перемикаються між ними | змінних більше двох |
| Підбір параметра | за потрібним результатом знаходить одне вхідне значення | відомий результат, невідомий вхід — див. «Підбір параметра в Excel» |
Таблиця даних може мати не більше двох змінних, зате значень кожної — скільки завгодно.
Підготовка: формула платежу
Візьмемо умовний приклад: кредит 900 000 грн на 36 місяців під 12,5 % річних, платежі ануїтетні в кінці місяця. Внесіть вихідні дані в окремі клітинки:
| Клітинка | Значення |
|---|---|
| B3 — річна ставка | 12,5 % |
| B4 — термін, місяців | 36 |
| B5 — сума кредиту | 900 000 |
Щомісячний платіж рахує функція PMT (в українському інтерфейсі може називатися ПЛТ): =PMT(B3/12;B4;-B5). Ставку ділимо на 12, бо період — місяць; суму беремо з мінусом, щоб платіж вийшов додатним. Для цього прикладу результат — близько 30 108 грн. Роздільник аргументів (крапка з комою чи кома) залежить від регіональних налаштувань.
Таблиця з однією змінною
Подивимося, як змінюється платіж при різних ставках.
- У стовпець, наприклад C3:C9, введіть ставки: 9 %, 9,5 %, 10 % … 12,5 %.
- У клітинку на рядок вище й на стовпець правіше від першого значення (D2) введіть формулу або посилання на неї:
=PMT(B3/12;B4;-B5). Праворуч можна додати інші формули — загальну суму виплат=D2*B4тощо. - Виділіть діапазон від клітинки над першою ставкою до останньої формули й останнього значення (C2:D9).
- «Дані» → «Аналіз what-if» → «Таблиця даних».
- Значення стоять у стовпці, тож у полі «Клітинка введення стовпця» вкажіть B3 (клітинку зі ставкою), поле рядка залиште порожнім. «OK».
Excel заповнить таблицю формулою масиву {=TABLE(;B3)}: редагувати окремі клітинки результату не можна, лише всю таблицю. Значення для 12,5 % збігається з результатом звичайної формули — так легко перевірити, що все налаштовано правильно.
Таблиця з двома змінними
Тепер змінюємо одночасно термін і суму кредиту.
- У клітинку на перетині (наприклад, F2) введіть формулу
=PMT(B3/12;B4;-B5). - Праворуч від неї в рядку (G2:K2) — терміни: 24, 36, 48, 60, 72 місяці; під нею в стовпці (F3:F8) — суми кредиту: 850 000, 900 000 … 1 100 000.
- Виділіть увесь діапазон разом з формулою, рядком і стовпцем значень (F2:K8) і відкрийте «Таблиця даних».
- «Клітинка введення рядка» — B4 (термін, бо терміни стоять у рядку), «Клітинка введення стовпця» — B5 (сума). «OK».
Щоб одразу побачити прийнятні варіанти, виділіть результати і застосуйте «Основне» → «Умовне форматування» → «Правила виділення клітинок» → «Менше ніж» з вашою межею платежу.
Якщо файл гальмує
Таблиці даних переобчислюються щоразу, коли перераховується аркуш, навіть без змін у даних. У великих книгах це сповільнює роботу. Microsoft радить у параметрах обчислень вибрати «Автоматично, крім таблиць даних»: тоді таблиці перераховуються лише за F9.
Часті питання
Як зробити таблицю підстановки в Excel? (таблица подстановки в excel
)
Введіть значення змінної стовпцем, над ними праворуч — формулу, виділіть діапазон і виберіть «Дані» → «Аналіз what-if» → «Таблиця даних», вказавши клітинку введення.
Чому не можна змінити клітинку в таблиці даних?
Результати — частина однієї формули масиву TABLE. Змінювати можна вхідні значення або видалити всю таблицю результатів цілком.
Скільки змінних підтримує таблиця даних?
Одну або дві. Для трьох і більше використовуйте сценарії.
Де «Майстер підстановок» у нових версіях Excel?
Окремого майстра немає: потрібна команда — «Таблиця даних» у меню «Аналіз what-if» на вкладці «Дані».