DAAD.org.ua
Зміст
Досить часто потрібно розрахувати підсумковий результат для різних комбінацій вступних даних. Таким чином користувач зможе оцінити всі можливі варіанти дій, відібрати ті, результат взаємодії яких його задовольняє, і, нарешті, вибрати найоптимальніший варіант. В Excel для виконання даного завдання існує спеціальний інструмент – " Таблиця даних» ( " таблиця підстановки» ). Давайте дізнаємося, як ним користуватися для виконання зазначених вище сценаріїв.
Читайте також: Підбір параметра в Excel
Використання таблиці даних
Інструмент " Таблиця даних» призначений для обчислення результату при різних варіаціях однієї або двох визначених змінних. Після розрахунку всі можливі варіанти постануть у вигляді таблиці, яку називають матрицею факторного аналізу. " Таблиця даних» відноситься до групи інструментів «аналіз &171;що робити, якщо&187;» , яка розміщена на стрічці у вкладці »дані" в блоці " робота з даними» . До версії Excel 2007 цей інструмент носив найменування " таблиця підстановки» , що навіть більш точно відображало його суть, ніж нинішня назва.
Таблицю підстановки можна використовувати у багатьох випадках. Наприклад, типовий варіант, коли потрібно розрахувати суму щомісячного платежу по кредиту при різних варіаціях періоду кредитування і суми позики, або періоду кредитування і процентної ставки. Також цей інструмент можна використовувати при аналізі моделей інвестиційних проектів.
Але також слід знати, що надмірне застосування даного інструменту може привести до гальмування системи, так як перерахунок даних проводиться постійно. Тому рекомендується в невеликих табличних масивах для вирішення аналогічних завдань не використовувати цей інструмент, а застосовувати копіювання формул за допомогою маркера заповнення.
Виправданим застосування " таблиці даних» є тільки у великих табличних діапазонах, коли копіювання формул може забрати велику кількість часу, а під час самої процедури збільшується ймовірність допущення помилок. Але і в цьому випадку рекомендується в діапазоні таблиці підстановки відключити автоматичний перерахунок формул, щоб уникнути зайвого навантаження на систему.
Головна відмінність між різними варіантами застосування таблиці даних полягає в кількості змінних, які беруть участь в обчисленні: одна змінна або дві.
Спосіб 1: застосування інструменту з однією змінною
Відразу давайте розглянемо варіант, коли таблиця даних використовується з одним змінним значенням. Візьмемо найбільш типовий приклад з кредитуванням.
Отже, в даний час нам пропонуються наступні умови кредитування:
| термін кредитування | 3 роки (36 місяців) |
| сума позики | 900000 рублів |
| процентна ставка | 12,5% річних |
Виплати відбуваються в кінці платіжного періоду (місяця) за ануїтетною схемою, тобто, рівними частками. При цьому, спочатку всього терміну кредитування значну частину виплат складають процентні платежі, але в міру скорочення тіла процентні платежі зменшуються, а збільшується розмір погашення самого тіла. Загальна ж виплата, як вже було сказано вище, залишається без змін.
Потрібно розрахувати, яка буде сума щомісячного платежу, що включає в себе погашення тіла кредиту і виплат за відсотками. Для цього в Ексель є оператор ПЛТ .
ПЛТ відноситься до групи фінансових функцій і його завданням є обчислення щомісячного кредитного платежу ануїтетного типу на підставі суми тіла кредиту, терміну кредитування і процентної ставки. Синтаксис цієї функції представлений в такому вигляді
=ПЛТ (ставка;кпер;пс;БС;тип)
»Ставка" &8212; аргумент, що визначає процентну ставку кредитних виплат. Показник виставляється за період. У нас період виплат дорівнює місяцю. Тому річну ставку в 12,5% слід розбити на число місяців в році, тобто, 12.
«Kper» &8212; аргумент, що визначає чисельність періодів за весь термін надання кредиту. У нашому прикладі період дорівнює одному місяцю, а термін кредитування становить 3 роки або 36 місяців. Таким чином, кількість періодів буде рано 36.
«ПС» &8212; аргумент, що визначає приведену вартість кредиту, тобто, це розмір тіла кредиту на момент його видачі. У нашому випадку цей показник дорівнює 900000 рублів.
" БС» &8212; аргумент, що вказує на величину тіла кредиту на момент його повної виплати. Природно, що даний показник буде дорівнює нулю. Цей аргумент не є обов'язковим параметром. Якщо його пропустити, то мається на увазі, що він дорівнює числу «0».
" Тип» &8212; також необов'язковий аргумент. Він повідомляє про те, коли саме буде проводитися платіж: на початку періоду (параметр – «1» ) або в кінці періоду (параметр – «0» ). Як ми пам'ятаємо, у нас платіж проводиться в кінці календарного місяця, тобто, величина цього аргументу буде дорівнює «0» . Але, з огляду на те, що цей показник не є обов'язковим, і за замовчуванням, якщо його не використовувати, значення і так мається на увазі рівним «0» , то в зазначеному прикладі його взагалі можна не застосовувати.
- Отже, приступаємо до розрахунку. Виділяємо осередок на аркуші, куди буде виводитися розрахункове значення. Клацаємо по кнопці " Вставити функцію» .
- Запускається Майстер функцій . Виробляємо перехід в категорію »фінансові" , вибираємо з переліку найменування " ПЛТ» і клацаємо по кнопці «OK» .
- Слідом за цим відбувається активація віконця аргументів вищевказаної функції.
Ставимо курсор в поле»Ставка" , після чого натискаємо по осередку на аркуші зі значенням річної процентної ставки. Як бачимо, в поле тут же відображаються її координати. Але, як ми пам'ятаємо, нам потрібна місячна ставка, а тому виробляємо розподіл отриманого результату на 12 ( /12 ).
У полі " Kper» таким же чином вносимо координати осередків терміну кредиту. В цьому випадку ділити нічого не треба.
У полі «Пс» потрібно вказати координати комірки, що містить величину тіла кредиту. Виконуємо це. Також ставимо перед відобразиться координатами знак «-» . Справа в тому, що функція ПЛТ за замовчуванням видає підсумковий результат саме з негативним знаком, справедливо вважаючи щомісячний кредитний платіж збитком. Але нам для наочності застосування таблиці даних потрібно, щоб дане число було позитивним. Тому ми і ставимо знак »мінус" перед одним із аргументів функції. Як відомо, множення »мінус" на «мінус» в результаті дає «плюс» .
В поля " Бс» і »Тип" дані взагалі не вносимо. Клацаємо по кнопці «OK» .
- Після цього оператор робить підрахунок і виводить в заздалегідь позначену комірку результат загального щомісячного платежу – 30108,26 рублів. Але проблема полягає в тому, що позичальник в змозі платити максимум 29000 рублів на місяць, тобто, йому слід або знайти банк, що пропонує умови з більш низькою процентною ставкою, або зменшити тіло позики, або збільшити термін кредитування. Прорахувати різні варіанти дій нам допоможе таблиця підстановок.
- Для початку використовуємо таблицю підстановок з однією змінною. Подивимося, як буде змінюватися величина обов'язкового місячного платежу при різних варіаціях річної ставки, починаючи від 9,5% річних і закінчуючи 12,5% річних з кроком 0,5% . Всі інші умови залишаємо незмінними. Креслимо табличний діапазон, найменування колонок якого будуть відповідати різним варіаціям процентної ставки. При цьому рядок »щомісячні виплати" залишаємо так, як є. У першій її осередку повинна міститися формула, яку ми розрахували раніше. Для більшої інформативності можна додати рядки " загальна сума кредиту» і «загальна сума відсотків» . Стовпець, в якому знаходиться розрахунок, робимо без заголовка.
- Далі розрахуємо загальну суму позики при поточних умовах. Для цього виділяємо першу клітинку рядка " загальна сума кредиту» і множимо вміст осередків " Щомісячний платіж» і " термін кредиту» . Після цього клацаємо по клавіші Enter .
- Для розрахунку загальної суми відсотків при поточних умовах аналогічним чином віднімаємо від загальної суми позики величину тіла кредиту. Для виведення результату на екран клацаємо по кнопці Enter . Таким чином ми отримуємо суму, яку переплачуємо при поверненні позики.
- Тепер настав час застосувати інструмент " Таблиця даних» . Виділяємо весь табличний масив, крім найменувань рядків. Після цього переходимо у вкладку »дані" . Клацаємо по кнопці на стрічці «аналіз &171;що робити, якщо&187;» , яка розміщена в групі інструментів " робота з даними» (в Excel 2016 група інструментів «Прогноз» ). Потім відкривається невелике меню. У ньому вибираємо позицію " Таблиця даних...» .
- Відкривається невелике віконце, яке так і називається " Таблиця даних» . Як бачимо, у нього є два поля. Так як ми працюємо з однією змінною, то нам знадобиться тільки одне з них. Так як у нас зміни змінної відбувається по стовпцях, то ми будемо використовувати поле «замінити значення за стовпцями на» . Встановлюємо туди курсор, а потім натискаємо по осередку в початковому наборі даних, яка містить поточну величину відсотків. Після того, як координати комірки відобразилися в полі, тиснемо на кнопку «OK» .
- Інструмент робить розрахунок і заповнює весь табличний діапазон значеннями, які відповідають різним варіантам процентної ставки. Якщо встановити курсор в будь-який елемент даної табличній області, то можна побачити, що в рядку формул відображається не звичайна формула розрахунку платежу, а спеціальна формула нерозривного масиву. Тобто, змінювати значення в окремих осередках тепер не можна. Видаляти результати розрахунку можна тільки всі разом, а не окремо.
Крім того, можна помітити, що величина щомісячного платежу при 12.5% річних, отримана в результаті застосування таблиці підстановок, відповідає величині при тому ж розмірі відсотків, яку ми отримали шляхом застосування функції ПЛТ . Це зайвий раз доводить правильність розрахунку.
Проаналізувавши даний табличний масив, слід сказати, що, як бачимо, тільки при ставці 9,5% річних виходить прийнятний для нас рівень щомісячного платежу (менше 29000 рублів).
Урок: Розрахунок ануїтетного платежу в Ексель
Спосіб 2: використання інструменту з двома змінними
Звичайно, відшукати в даний час банки, які видають кредит під 9,5% річних, дуже складно, якщо взагалі реально. Тому подивимося, які варіанти існують вкластися в прийнятний рівень щомісячного платежу при різних комбінаціях інших змінних: величини тіла позики і терміну кредитування. При цьому процентну ставку залишимо незмінною (12,5%). У вирішенні даного завдання нам допоможе інструмент " Таблиця даних» з використанням двох змінних.
- Креслимо новий табличний масив. Тепер в найменуваннях стовпців буде вказуватися термін кредитування (від 2 до 6 років в місяцях з кроком в один рік), а в рядках & 8212; величина тіла кредиту (від 850000 до 950000 рублів з кроком 10000 рублів). При цьому обов'язковою умовою є те, щоб осередок, в якій знаходиться формула розрахунку (в нашому випадку ПЛТ ), розташовувалася на кордоні найменувань рядків і стовпців. Без виконання даної умови інструмент при використанні двох змінних працювати не буде.
- Потім виділяємо весь отриманий табличний діапазон, включаючи найменування стовпців, рядків і комірку з формулою ПЛТ . Переходимо у вкладку »дані" . Як і в попередній раз, клацаємо по кнопці «аналіз &171;що робити, якщо&187;» , в групі інструментів " робота з даними» . У списку вибираємо пункт " Таблиця даних...» .
- Запускається вікно інструменту " Таблиця даних» . В даному випадку нам будуть потрібні обидва поля. У полі «замінити значення за стовпцями на» вказуємо координати осередку, що містить термін кредиту в первинних даних. У полі " підставляти значення за рядками в» вказуємо адресу осередку вихідних параметрів, що містить величину тіла кредиту. Після того, як всі дані введені. Клацаємо по кнопці «OK» .
- Програма виконує розрахунок і заповнює табличний діапазон даними. На перетині рядків і стовпців тепер можна спостерігати, яким саме буде щомісячний платіж, при відповідній величині річних відсотків і зазначеному терміні кредитування.
- Як бачимо, значень досить багато. Для вирішення інших завдань їх може бути ще більше. Тому, щоб зробити видачу результатів більш наочною і відразу визначити, які значення не задовольняють заданій умові, можна використовувати інструменти візуалізації. У нашому випадку це буде умовне форматування. Виділяємо всі значення табличного діапазону, виключаючи заголовки рядків і стовпців.
- Переміщаємося у вкладку»Головна" і клацаємо по значку " Умовне форматування» . Він розташований у блоці інструментів «стилі» на стрічці. У розкрився меню вибираємо пункт " Правила виділення комірок» . У додатковому списку натискаємо по позиції »менше..." .
- Слідом за цим відкривається вікно налаштування умовного форматування. У лівому полі вказуємо величину, менше якої осередки будуть виділені. Як пам'ятаємо, нас задовольняє умова, при якому щомісячний платіж по кредиту становитиме менше 29000 рублів. Вписуємо дане число. У правому полі існує можливість вибору кольору виділення, хоча можна залишити його і за замовчуванням. Після того, як всі необхідні настройки введені, клацаємо по кнопці «OK» .
- Після цього всі осередки, значення в яких відповідають вищеописаному умові, будуть виділені кольором.
Проаналізувавши табличний масив, можна зробити деякі висновки. Як бачимо, при існуючому терміні кредитування (36 місяців), щоб вкластися в вище зазначену суму щомісячного платежу, нам потрібно взяти позику не перевищує 860000,00 рублів, тобто, на 40000 менше спочатку запланованого.
Якщо ж ми все-таки мають намір брати кредит розміром 900000 рублів, то термін кредитування повинен становити 4 роки (48 місяців). Тільки в такому випадку розмір щомісячного платежу не перевищить встановлену межу в 29000 рублів.
Таким чином, скориставшись даними табличним масивом і проаналізувавши «за» і «проти» кожного варіанту, позичальник може прийняти конкретне рішення про умови кредитування, вибравши найбільш відповідає його побажанням варіант з усіх можливих.
Звичайно, таблицю підстановок можна використовувати не тільки для розрахунку кредитних варіантів, але і для вирішення безлічі інших завдань.
Урок: Умовне форматування в Екселі
Загалом, потрібно відзначити, що таблиця підстановок є дуже корисним і порівняно простим інструментом для визначення результату при різних комбінаціях змінних. Застосувавши одночасно з ним умовне форматування, крім того, можна візуалізувати отриману інформацію.
