Таблиця даних в Microsoft Excel

Досить часто потрібно розрахувати підсумковий результат для різних комбінацій вступних даних. Таким чином користувач зможе оцінити всі можливі варіанти дій, відібрати ті, результат взаємодії яких його задовольняє, і, нарешті, вибрати найоптимальніший варіант. В Excel для виконання даного завдання існує спеціальний інструмент - «Таблиця даних» ( «Таблиця підстановки»). Давайте дізнаємося, як ним користуватися для виконання зазначених вище сценаріїв.

Читайте також: Підбір параметра в Excel

Використання таблиці даних

Інструмент «Таблиця даних» призначений для того, щоб розраховувати результат при різних варіаціях однієї або двох певних змінних. Після розрахунку всіх можливих варіанти постануть у вигляді таблиці, яку називають матрицею факторного аналізу. «Таблиця даних» відноситься до групи інструментів «Аналіз« що якщо »», яка розміщена на стрічці у вкладці «Дані» в блоці «Робота з даними». До версії Excel 2007 цей інструмент носив найменування «Таблиця підстановки», що навіть більш точно відображало його суть, ніж нинішня назва.

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

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

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

Головна відмінність між різними варіантами застосування таблиці даних складається в кількості змінних, які беруть участь в обчисленні: одна змінна або дві.

Спосіб 1: застосування інструменту з однією змінною

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

Отже, в даний час нам пропонуються наступні умови кредитування:

• Термін кредитування - 3 роки (36 місяців);
• Сума позики - 900000 рублів;
• Процентна ставка - 12,5% річних.

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

Потрібно розрахувати, якою буде сума щомісячного платежу, що включає в себе погашення тіла кредиту і виплат за відсотками. Для цього в Ексель є оператор ПЛТ.

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

=ПЛТ(ставка;кпер;пс;бс;тип)

«Ставка» - аргумент, який визначає процентну ставку кредитних виплат. Показник виставляється за період. У нас період виплат дорівнює місяцю. Тому річну ставку в 12,5% слід розбити на число місяців у році, тобто, 12.

«Кпер» - аргумент, який визначає чисельність періодів за весь термін надання кредиту. У нашому прикладі період дорівнює одному місяцю, а термін кредитування складає 3 роки або 36 місяців. Таким чином, кількість періодів буде рано 36.

«ПС» - аргумент, який визначає наведену вартість кредиту, тобто, це розмір тіла кредиту на момент його видачі. У нашому випадку цей показник дорівнює 900000 рублів.

«БС» - аргумент, який вказує на величину тіла кредиту на момент його повної виплати. Природно, що даний показник буде дорівнює нулю. Цей аргумент не є обов'язковим параметром. Якщо його пропустити, то мається на увазі, що він дорівнює числу «0».

«Тип» - також необов'язковий аргумент. Він повідомляє про те, коли саме буде проводитися платіж: на початку періоду (параметр - «1») або в кінці періоду (параметр - «0»). Як ми пам'ятаємо, у нас платіж проводиться в кінці календарного місяця, тобто, величина цього аргументу буде дорівнює «0». Але, з огляду на те, що цей показник не є обов'язковим, і за замовчуванням, якщо його не використовувати, значення і так мається на увазі рівним «0», то в зазначеному прикладі його взагалі можна не застосовувати.

1. Отже, приступаємо до розрахунку. Виділяємо осередок на аркуші, куди буде виводитися розрахункове значення. Клацають по кнопці «Вставити функцію».
2. Запускається Майстер функцій. Виробляємо перехід в категорію «Фінансові», вибираємо з переліку найменування «ПЛТ» і клацають по кнопці «OK».
3. Слідом за цим відбувається активація віконця аргументів вищевказаної функції.

   Ставимо курсор в поле «Ставка», після чого натискаємо по осередку на аркуші зі значенням річної процентної ставки. Як бачимо, в поле тут же відображаються її координати. Але, як ми пам'ятаємо, нам потрібна місячна ставка, а тому виробляємо розподіл отриманого результату на 12 (/ 12).

   В поле «Кпер» таким же чином вносимо координати осередків терміну кредиту. В цьому випадку ділити нічого не треба.

   В поле «Пс» потрібно вказати координати комірки, що містить величину тіла кредиту. Виконуємо це. Також ставимо перед відобразиться координатами знак «-». Справа в тому, що функція ПЛТ за замовчуванням видає підсумковий результат саме з негативним знаком, справедливо вважаючи щомісячний кредитний платіж збитком. Але нам для наочності застосування таблиці даних потрібно, щоб дане число було позитивним. Тому ми і ставимо знак «мінус» перед одним з аргументів функції. Як відомо, множення «мінус» на «мінус» в результаті дає «плюс».

   У поля «Бс» і «Тип» дані взагалі не вносимо. Клацають по кнопці «OK».

4. Після цього оператор проводить підрахунок і виводить в заздалегідь позначену осередок результат загального щомісячного платежу - 30108,26 рублів. Але проблема полягає в тому, що позичальник в змозі платити максимум 29000 рублів на місяць, тобто, йому слід або знайти банк, що пропонує умови з більш низькою процентною ставкою, або зменшити тіло позики, або збільшити термін кредитування. Прорахувати різні варіанти дій нам допоможе таблиця підстановок.
5. Для початку використовуємо таблицю підстановок з однією змінною. Подивимося, як буде змінюватися величина обов'язкового місячного платежу при різних варіаціях річної ставки, починаючи від 9,5% річних і закінчуючи 12,5% річних з кроком 0,5%. Всі інші умови залишаємо незмінними. Креслимо табличний діапазон, найменування колонок якого будуть відповідати різним варіаціям процентної ставки. При цьому рядок «Щомісячні виплати» залишаємо так, як є. У першій її осередку повинна міститися формула, яку ми розрахували раніше. Для більшої інформативності можна додати рядки «Загальна сума кредиту» і «Загальна сума відсотків». Стовпець, в якому знаходиться розрахунок, робимо без заголовка.
6. Далі розрахуємо загальну суму позики при поточних умовах. Для цього виділяємо першої комірки в рядку «Загальна сума кредиту» і множимо комірки «Щомісячний платіж» і «Термін кредиту». Після цього клацаємо по клавіші Enter.
7. Для розрахунку загальної суми відсотків при поточних умовах аналогічним чином віднімаємо від загальної суми позики величину тіла кредиту. Для виведення результату на екран клацаємо по кнопці Enter. Таким чином ми отримуємо суму, яку переплачуємо при поверненні позики.
8. Тепер настав час застосувати інструмент «Таблиця даних». Виділяємо весь табличний масив, крім найменувань рядків. Після цього переходимо у вкладку «Дані». Клацаємо по кнопці на стрічці «Аналіз« що якщо »», яка розміщена в групі інструментів «Робота з даними» (в Excel 2016 група інструментів «Прогноз»). Потім відкривається невелике меню. У ньому вибираємо позицію «Таблиця даних ...».
9. Відкривається невелике віконце, яке так і називається «Таблиця даних». Як бачимо, у нього є два поля. Так як ми працюємо з однією змінною, то нам знадобиться тільки одне з них. Так як у нас зміни змінної відбувається за стовпцями, то ми будемо використовувати поле «Підставити значення за стовпцями в». Встановлюємо туди курсор, а потім натискаємо по осередку в вихідному наборі даних, яка містить поточну величину відсотків. Після того, як координати осередку відобразилися в поле, тиснемо на кнопку «OK».
10. Інструмент проводить розрахунок і заповнює весь табличний діапазон значеннями, які відповідають різним варіантам процентної ставки. Якщо встановити курсор в будь-який елемент даної табличній області, то можна побачити, що в рядку формул відображається не звичайна формула розрахунку платежу, а спеціальна формула нерозривного масиву. Тобто, змінювати значення в окремих осередках тепер не можна. Видаляти результати розрахунку можна тільки все разом, а не окремо.

Крім того, можна помітити, що величина щомісячного платежу при 12.5% річних, отримана в результаті застосування таблиці підстановок, відповідає величині при тому ж розмірі відсотків, яку ми отримали шляхом застосування функції ПЛТ. Це зайвий раз доводить правильність розрахунку.

Проаналізувавши даний табличний масив, слід сказати, що, як бачимо, тільки при ставці 9,5% річних виходить прийнятний для нас рівень щомісячного платежу (менш 29000 рублів).

урок: Розрахунок аннуитетного платежу в Ексель

Спосіб 2: використання інструменту з двома змінними

Звичайно, відшукати в даний час банки, які видають кредит під 9,5% річних, дуже складно, якщо взагалі реально. Тому подивимося, які варіанти існують вкластися в прийнятний рівень щомісячного платежу при різних комбінаціях інших змінних: величини тіла позики і терміну кредитування. При цьому процентну ставку залишимо незмінною (12,5%). У вирішенні цього завдання нам допоможе інструмент «Таблиця даних» з використанням двох змінних.

1. Креслимо новий табличний масив. Тепер в найменуваннях стовпців вказуватиметься термін кредитування (від 2 до 6 років в місцях з кроком в один рік), а в рядках - величина тіла кредиту (від 850000 до 950000 рублів з кроком 10000 рублів). При цьому обов'язковою умовою є те, щоб осередок, в якій знаходиться формула розрахунку (в нашому випадку ПЛТ), розташовувалася на кордоні найменувань рядків і стовпців. Без виконання цієї умови інструмент при використанні двох змінних працювати не буде.
2. Потім виділяємо весь отриманий табличний діапазон, включаючи найменування стовпців, рядків і осередок з формулою ПЛТ. Переходимо у вкладку «Дані». Як і в попередній раз, клацаємо по кнопці «Аналіз« що якщо »», в групі інструментів «Робота з даними». У списку вибираємо пункт «Таблиця даних ...».
3. Запускається вікно інструменту «Таблиця даних». В даному випадку нам будуть потрібні обидва поля. В поле «Підставляти значення за стовпцями в» вказуємо координати комірки, що містить термін кредиту в первинних даних. В поле «Підставляти значення по рядках в» вказуємо адресу осередки вихідних параметрів, що містить величину тіла кредиту. Після того, як всі дані введені. Клацають по кнопці «OK».
4. Програма виконує розрахунок і заповнює табличний діапазон даними. На перетині рядків і стовпців тепер можна спостерігати, яким саме буде щомісячний платіж, при відповідній величині річних відсотків і зазначеному терміні кредитування.
5. Як бачимо, значний досить багато. Для вирішення інших завдань їх може бути ще більше. Тому, щоб зробити видачу результатів більш наочною і відразу визначити, які значення не задовольняють заданій умові, можна використовувати інструменти візуалізації. У нашому випадку це буде умовне форматування. Виділяємо всі значення табличного діапазону, виключаючи заголовки рядків і стовпців.
6. Переміщаємося у вкладку «Головна» і клацають по значку «Умовне форматування». Він розташований в блоці інструментів «Стилі» на стрічці. У меню, що розкрилося вибираємо пункт «Правила виділення осередків». У додатковому списку натискаємо по позиції «Менше ...».
7. Слідом за цим відкривається вікно настройки умовного форматування. У лівому полі вказуємо величину, менш якої комірки будуть виділені. Як пам'ятаємо, нас задовольняє умову, за якої щомісячний платіж по кредиту буде становити менше 29000 рублів. Вписуємо дане число. У правому полі існує можливість вибору кольору виділення, хоча можна залишити його і за замовчуванням. Після того, як всі необхідні настройки введені, клацають по кнопці «OK».
8. Після цього всі осередки, значення в яких відповідають вищеописаному умові, будуть виділені кольором.

Проаналізувавши табличний масив, можна зробити деякі висновки. Як бачимо, при існуючому терміні кредитування (36 місяців), щоб вкластися в вище зазначену суму щомісячного платежу, нам потрібно взяти позику не перевищує 860000,00 рублів, тобто, на 40000 менше спочатку запланованого.

Якщо ж ми все-таки мають намір брати кредит розміром 900000 рублів, то термін кредитування повинен становити 4 роки (48 місяців). Тільки в такому випадку розмір щомісячного платежу не перевищить встановлену межу в 29000 рублів.

Таким чином, скориставшись даними табличним масивом і проаналізувавши «за» і «проти» кожного варіанту, позичальник може прийняти конкретне рішення про умови кредитування, вибравши найбільш відповідає його побажанням варіант з усіх можливих.

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

урок: Умовне форматування в Ексель

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

• ← Відкриття таблиць ODS в Microsoft Excel
• SQL запити в Microsoft Excel →