Досить часто потрібно розрахувати підсумковий результат для різних комбінацій вступних даних. Таким чином користувач зможе оцінити всі можливі варіанти дій, відібрати ті, результат взаємодії яких його задовольняє, і, нарешті, вибрати найоптимальніший варіант. В Excel для виконання даного завдання існує спеціальний інструмент - «Таблиця даних» ( «Таблиця підстановки»). Давайте дізнаємося, як ним користуватися для виконання зазначених вище сценаріїв.
Крім того, можна помітити, що величина щомісячного платежу при 12.5% річних, отримана в результаті застосування таблиці підстановок, відповідає величині при тому ж розмірі відсотків, яку ми отримали шляхом застосування функції ПЛТ. Це зайвий раз доводить правильність розрахунку.
Проаналізувавши даний табличний масив, слід сказати, що, як бачимо, тільки при ставці 9,5% річних виходить прийнятний для нас рівень щомісячного платежу (менш 29000 рублів).
Урок: Розрахунок аннуитетного платежу в Ексель
Спосіб 2: використання інструменту з двома змінними
Звичайно, відшукати в даний час банки, які видають кредит під 9,5% річних, дуже складно, якщо взагалі реально. Тому подивимося, які варіанти існують вкластися в прийнятний рівень щомісячного платежу при різних комбінаціях інших змінних: величини тіла позики і терміну кредитування. При цьому процентну ставку залишимо незмінною (12,5%). У вирішенні цього завдання нам допоможе інструмент «Таблиця даних» з використанням двох змінних.
- Креслимо новий табличний масив. Тепер в найменуваннях стовпців вказуватиметься термін кредитування (від 2 до 6 років в місцях з кроком в один рік), а в рядках - величина тіла кредиту (від 850000 до 950000 рублів з кроком 10000 рублів). При цьому обов'язковою умовою є те, щоб осередок, в якій знаходиться формула розрахунку (в нашому випадку ПЛТ), розташовувалася на кордоні найменувань рядків і стовпців. Без виконання цієї умови інструмент при використанні двох змінних працювати не буде.
- Потім виділяємо весь отриманий табличний діапазон, включаючи найменування стовпців, рядків і осередок з формулою ПЛТ. Переходимо у вкладку «Дані». Як і в попередній раз, клацаємо по кнопці «Аналіз« що якщо »», в групі інструментів «Робота з даними». У списку вибираємо пункт «Таблиця даних ...».
- Запускається вікно інструменту «Таблиця даних». В даному випадку нам будуть потрібні обидва поля. В поле «Підставляти значення за стовпцями в» вказуємо координати комірки, що містить термін кредиту в первинних даних. В поле «Підставляти значення по рядках в» вказуємо адресу осередки вихідних параметрів, що містить величину тіла кредиту. Після того, як всі дані введені. Клацають по кнопці «OK».
- Програма виконує розрахунок і заповнює табличний діапазон даними. На перетині рядків і стовпців тепер можна спостерігати, яким саме буде щомісячний платіж, при відповідній величині річних відсотків і зазначеному терміні кредитування.
- Як бачимо, значний досить багато. Для вирішення інших завдань їх може бути ще більше. Тому, щоб зробити видачу результатів більш наочною і відразу визначити, які значення не задовольняють заданій умові, можна використовувати інструменти візуалізації. У нашому випадку це буде умовне форматування. Виділяємо всі значення табличного діапазону, виключаючи заголовки рядків і стовпців.
- Переміщаємося у вкладку «Головна» і клацають по значку «Умовне форматування». Він розташований в блоці інструментів «Стилі» на стрічці. У меню, що розкрилося вибираємо пункт «Правила виділення осередків». У додатковому списку натискаємо по позиції «Менше ...».
- Слідом за цим відкривається вікно настройки умовного форматування. У лівому полі вказуємо величину, менш якої комірки будуть виділені. Як пам'ятаємо, нас задовольняє умову, за якої щомісячний платіж по кредиту буде становити менше 29000 рублів. Вписуємо дане число. У правому полі існує можливість вибору кольору виділення, хоча можна залишити його і за замовчуванням. Після того, як всі необхідні настройки введені, клацають по кнопці «OK».
- Після цього всі осередки, значення в яких відповідають вищеописаному умові, будуть виділені кольором.
Проаналізувавши табличний масив, можна зробити деякі висновки. Як бачимо, при існуючому терміні кредитування (36 місяців), щоб вкластися в вище зазначену суму щомісячного платежу, нам потрібно взяти позику не перевищує 860000,00 рублів, тобто, на 40000 менше спочатку запланованого.
Якщо ж ми все-таки мають намір брати кредит розміром 900000 рублів, то термін кредитування повинен становити 4 роки (48 місяців). Тільки в такому випадку розмір щомісячного платежу не перевищить встановлену межу в 29000 рублів.
Таким чином, скориставшись даними табличним масивом і проаналізувавши «за» і «проти» кожного варіанту, позичальник може прийняти конкретне рішення про умови кредитування, вибравши найбільш відповідає його побажанням варіант з усіх можливих.
Звичайно, таблицю підстановок можна використовувати не тільки для розрахунку кредитних варіантів, але і для вирішення безлічі інших завдань.
Урок: Умовне форматування в Ексель
Загалом, потрібно відзначити, що таблиця підстановок є дуже корисним і порівняно простим інструментом для визначення результату при різних комбінаціях змінних. Застосувавши одночасно з ним умовне форматування, крім того, можна візуалізувати отриману інформацію.