Таблиця підстановок в Excel

Як зробити таблицю даних (таблицю підстановок) в Excel: аналіз «що, якщо» з однією і двома змінними на прикладі платежу PMT за кредитом, умовне форматування і прискорення обчислень.

Таблиця підстановок, або таблиця даних (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 грн. Роздільник аргументів (крапка з комою чи кома) залежить від регіональних налаштувань.

Таблиця з однією змінною

Подивимося, як змінюється платіж при різних ставках.

  1. У стовпець, наприклад C3:C9, введіть ставки: 9 %, 9,5 %, 10 % … 12,5 %.
  2. У клітинку на рядок вище й на стовпець правіше від першого значення (D2) введіть формулу або посилання на неї: =PMT(B3/12;B4;-B5). Праворуч можна додати інші формули — загальну суму виплат =D2*B4 тощо.
  3. Виділіть діапазон від клітинки над першою ставкою до останньої формули й останнього значення (C2:D9).
  4. «Дані» → «Аналіз what-if» → «Таблиця даних».
  5. Значення стоять у стовпці, тож у полі «Клітинка введення стовпця» вкажіть B3 (клітинку зі ставкою), поле рядка залиште порожнім. «OK».

Excel заповнить таблицю формулою масиву {=TABLE(;B3)}: редагувати окремі клітинки результату не можна, лише всю таблицю. Значення для 12,5 % збігається з результатом звичайної формули — так легко перевірити, що все налаштовано правильно.

Таблиця з двома змінними

Тепер змінюємо одночасно термін і суму кредиту.

  1. У клітинку на перетині (наприклад, F2) введіть формулу =PMT(B3/12;B4;-B5).
  2. Праворуч від неї в рядку (G2:K2) — терміни: 24, 36, 48, 60, 72 місяці; під нею в стовпці (F3:F8) — суми кредиту: 850 000, 900 000 … 1 100 000.
  3. Виділіть увесь діапазон разом з формулою, рядком і стовпцем значень (F2:K8) і відкрийте «Таблиця даних».
  4. «Клітинка введення рядка» — B4 (термін, бо терміни стоять у рядку), «Клітинка введення стовпця» — B5 (сума). «OK».

Щоб одразу побачити прийнятні варіанти, виділіть результати і застосуйте «Основне» → «Умовне форматування» → «Правила виділення клітинок» → «Менше ніж» з вашою межею платежу.

Якщо файл гальмує

Таблиці даних переобчислюються щоразу, коли перераховується аркуш, навіть без змін у даних. У великих книгах це сповільнює роботу. Microsoft радить у параметрах обчислень вибрати «Автоматично, крім таблиць даних»: тоді таблиці перераховуються лише за F9.

Часті питання

Як зробити таблицю підстановки в Excel? (таблица подстановки в excel)

Введіть значення змінної стовпцем, над ними праворуч — формулу, виділіть діапазон і виберіть «Дані» → «Аналіз what-if» → «Таблиця даних», вказавши клітинку введення.

Чому не можна змінити клітинку в таблиці даних?

Результати — частина однієї формули масиву TABLE. Змінювати можна вхідні значення або видалити всю таблицю результатів цілком.

Скільки змінних підтримує таблиця даних?

Одну або дві. Для трьох і більше використовуйте сценарії.

Де «Майстер підстановок» у нових версіях Excel?

Окремого майстра немає: потрібна команда — «Таблиця даних» у меню «Аналіз what-if» на вкладці «Дані».

Схожі інструкції