Часто в расчетах Excel возникают ситуации, когда нам нужно вычислить формулу при изменении переменной или переменных. Примеры не нужно искать далеко:
- Каким будет ежемесячный платеж, если вы возьмете кредит в размере 100 тыс. на 3 года под разные процентные ставки.
- Сколько вам придется платить в месяц, если вы возьмете кредит на один год, два года, три года, четыре года, пять лет под разные банковские проценты.
Смотрите также видеоверсию «Инструмент для работы с таблицами данных Excel».
Задача 1. А ведь ничего сложного
В действительности, в этих расчетах нет ничего экстраординарного. Для расчета ежемесячного аннуитетного платежа используется финансовая функция Excel PLT (PMT). Соответственно, зная желаемую сумму кредита и срок кредитования, можно рассчитать ежемесячный платеж.
Расчет ежемесячного платежа с помощью PMT
Общая сумма погашения рассчитывается как ежемесячный платеж, умноженный на количество периодов (всего 36 месяцев), а проценты на переплату — это общая сумма погашения минус сумма кредита.
Теперь для выполнения аналогичных расчетов достаточно добавить нужные варианты годовых процентов, правильно использовать абсолютные и относительные ссылки в формулах расчета, ссылаться на данные для расчетов и использовать автозаполнение.
Правильно используя различные типы ссылок и автозаполнение, можно выполнять расчеты для различных процентных ставок
Решение первой задачи с помощью инструмента «Таблица данных».
Шаг 1: Выполняется первый расчет и создается шаблон данных для заполнения остальных расчетов. Кстати, не имеет значения, какой тип ссылок используется, потому что автозаполнение не будет применяться.
Если вы используете «Таблицу данных», то не имеет значения, какого типа ссылки находятся в формуле
Шаг 2: Выбираем диапазон с тестовыми расчетами и пустой диапазон для остальных данных (в нашем случае это диапазон B5:G8), затем выбираем инструмент «Таблица данных» на вкладке «Данные», группа «Прогнозы», команда «Анализировать, что если» (для Excel версии 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).
Выбор инструмента Таблица данных на ленте интерфейса
Шаг 3: В диалоговом окне укажите ссылку на ячейку, которая является переменной для вычисления. В нашем случае мы хотим рассчитать разные варианты задачи для разных вариантов процентных ставок, а это значит, что нам нужно включить ссылку на годовую процентную ставку, используемую в расчете. Ссылка на годовой процент должна быть включена в поле «Заменить значения по столбцу в:», так как у нас значения заполнены по столбцу, а варианты годового процента расположены горизонтально. Если бы варианты разных процентов были расположены вертикально, то ссылку пришлось бы поместить во второе поле.
Кроме того, поскольку в расчете участвует только одна переменная, мы заполняем только одно поле в диалоге, а другое оставляем пустым.
Выбор ссылки на переменную в диалоговом окне
Когда мы нажимаем «OK», мы получаем результат, и когда мы выбираем любое из вычисленных значений, мы видим формулу массива «{=TABLE(B5;)}» в строке формул, вместо формул вычисления, как в автозаполнении.
После вычисления в строке формул остается только специальная функция ТАБЛИЦА
При решении первой задачи с одной переменной, очевидно, проще просто использовать автозаполнение; однако не спешите с выводами, поскольку инструмент Parameter Matching может быть использован для прогнозирования вычисления двух переменных.
Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.
Таблица подготовки выглядит следующим образом.
Подготовительная таблица для прогнозирования значений с двумя переменными
При выборе таблицы важно, чтобы левая верхняя ячейка содержала формулу с расчетом, в которую следует вставить новые значения переменных.
Развертывание ссылок на переменные в диалоговом окне
Результат работы инструмента «Таблица данных
Результат работы инструмента «Таблица данных» с двумя входными данными
Конечно, этот инструмент работает не только с функцией расчета аннуитета PLT, но и с любой формулой, где вы хотите отслеживать изменения на основе изменения определенных переменных.
Больше примеров работы с финансовыми функциями, инструментом Таблица данных в восьмом уроке курса «Excel от новичка к продвинутому».
- Темы
- Excel