Часто при расчетах в Excel у нас возникают ситуации, когда нужно просчитать формулу при изменении переменной или переменных. За примерами далеко ходить не надо:

  1. Какой будет ежемесячный платеж, если берется кредит на 100 тыс. руб. на 3 года при различных процентных ставках.
  2. Сколько необходимо платить в месяц, если взять в кредит на год, два, три, четыре, пять при различных процентных ставках банка.

Смотрите также видеоверсию статьи «Инструмент Таблица данных в Excel».

Задача 1. А ведь ничего сложного

На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel ПЛТ (PMT). Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.

Расчет ежемесечного платежа с помощью ПЛТ
Расчет ежемесечного платежа с помощью ПЛТ

Общая сумма выплат рассчитывается как ежемесячный платеж умноженный на количество периодов (всего 36 месяцев), проценты переплаты – это общая сумма выплат минус сумма займа.

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

Благодаря правильному использованию различных типов ссылок и автозаполнению, можно сделать подсчет для различных процентных ставок
Благодаря правильному использованию различных типов ссылок и автозаполнению, можно сделать подсчет для различных процентных ставок
Решение первой задачи с помощью инструмента «Таблица данных».

Шаг 1. Делается первый необходимый расчет и создается заготовка под заполнение данными остальных расчетов. Здесь, кстати, абсолютно не важно каким типом ссылок пользоваться, поскольку автозаполнение применятся не будет.

При использовании "Таблица данных" не важен тип ссылок в формуле
При использовании «Таблица данных» не важен тип ссылок в формуле

Шаг 2. Выделяется диапазон с тестовым расчетом и заготовкой под остальные данные (в нашем случае это диапазон B5:G8), дальше выбирается инструмент «Таблица данных» на вкладке Данные, группа «Прогноз», команда «Анализ что если» (для версии Excel 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).

Выбор инструмента "Таблица данных" на ленте интерфейса
Выбор инструмента «Таблица данных» на ленте интерфейса

Шаг 3. В диалоговом окне необходимо указать ссылку на ячейку, которая является переменной для расчетов. В нашем случае мы хотим посчитать различные варианты задачи при различных вариантах процентной ставки, значит необходимо поставить ссылку на годовой процент, который используется в вычислениях. Ссылку на годовой процент необходимо поставить в поле «Подставлять значения по столбцам в:», т.к. у нас значения заполняются по столбцам, а варианты годового процента расположены горизонтально. Если бы варианты различного процента были расположены вертикально, тогда ссылку необходимо было ставить во второе поле.

Также, поскольку у нас расчет идет по одной переменной, то заполняем только одно поле диалогового окна, а второе оставляем пустым.

Выбор ссылки на переменную в диалоговом окне
Выбор ссылки на переменную в диалоговом окне

После нажатия «ОК» мы получи результат, а при выборе любого из значений, которое было рассчитано, в строке формул увидим формулу массива «{=ТАБЛИЦА(B5;)}», а не формулы расчета, как в случае с автозаполнением.

После расчета в строке формул находится только специальная функция ТАБЛИЦА
После расчета в строке формул находится только специальная функция ТАБЛИЦА

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

Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.

Подготовительная таблица выглядит следующим образом.

Заготовка для прогноза значений с двумя переменными
Заготовка для прогноза значений с двумя переменными

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

Расстановка ссылок на переменные в диалоговом окне
Расстановка ссылок на переменные в диалоговом окне

Результат работы инструмента «Таблица данных»

Результат работы инструмента "Таблица данных" с двумя входами
Результат работы инструмента «Таблица данных» с двумя входами

Конечно, данный инструмент работает не только с функцией расчет платежа по аннуитету ПЛТ, а с любой формулой, где необходимо проследить изменения в зависимости от изменения определенных переменных.

Больше примеров работы с финансовыми функциями, инструментом «Таблица данных» в восьмом занятии курса «Excel от новичка до профессионала».