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

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

Использование таблицы данных

Инструмент Таблица данных предназначен для вычисления результата для нескольких вариантов одной или двух определенных переменных. После расчета все возможные варианты появятся в виде таблицы, которая называется матрицей факторного анализа. Таблица относится к набору инструментов анализа «что если», который находится на ленте на вкладке «Данные» в блоке «Инструменты для работы с данными». До Excel 2007 этот инструмент назывался «Таблица поиска», что было даже точнее, чем его нынешнее название.

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

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

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

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

Способ 1: применение инструмента с одной переменной

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

Итак, на данный момент нам предлагаются следующие условия кредита:

• Срок кредита — 3 года (36 месяцев);
• Сумма кредита — 900 000 рублей;
• Процентная ставка — 12,5% годовых.

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

необходимо рассчитать, какой будет размер ежемесячного платежа, который включает погашение кредита кредитной организации и выплату процентов. Для этого в Excel есть оператор PMT.

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

= PMT (ставка; кпер; пс; шс; тип)

«Ставка» — это аргумент, определяющий процентную ставку выплат по кредиту. Индикатор устанавливается на период. Срок оплаты — один месяц. Следовательно, годовую ставку 12,5% следует разделить на количество месяцев в году, которое равно 12.

«Кпер» — это аргумент, определяющий количество периодов на весь период ссуды. В нашем примере срок составляет один месяц, а срок кредита — 3 года или 36 месяцев. Таким образом, количество периодов будет в начале 36.

«ПС» — аргумент, определяющий текущую стоимость ссуды, то есть это размер тела ссуды на момент ее выдачи. В нашем случае это 900 000 руб.

«БС» — это аргумент, указывающий размер кредитной организации на момент ее полного погашения. Конечно, этот показатель будет нулевым. Это необязательный аргумент. Если его пропустить, предполагается, что он равен числу «0».

«Тип» также является необязательным аргументом. Он сообщает вам, когда именно будет произведен платеж: в начале периода (параметр — «1») или в конце периода (параметр — «0»). Как мы помним, наша оплата производится в конце календарного месяца, т.е значение этого аргумента будет равно «0». Но поскольку этот флаг является необязательным и по умолчанию, если вы его не используете, предполагается, что значение уже равно «0», поэтому в этом примере вы вообще не можете его использовать.

1. Итак, перейдем к расчету. Выберите ячейку листа, в которой будет отображаться вычисленное значение. Нажмите кнопку «Вставить функцию».
2. Мастер запускается. Переходим в категорию «Финансовые», выбираем из списка название «PMT» и нажимаем на кнопку «ОК».
3. После этого активируется окно аргументов вышеупомянутой функции.

   Ставим курсор в поле «Ставка», затем щелкаем по ячейке листа со значением годовой процентной ставки. Как видите, его координаты сразу отображаются в поле. Но, как мы помним, нам нужна месячная ставка, а затем делим результат на 12 (/ 12).

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

   В поле «Ps» необходимо указать координаты ячейки, содержащей размерность кредитной организации. Мы делаем это. Мы также ставим знак «-» перед отображаемыми координатами. Дело в том, что функция PMT по умолчанию дает окончательный результат с отрицательным знаком, справедливо считая ежемесячный платеж по кредиту убытком. Но для ясности при использовании таблицы данных нам нужно, чтобы это число было положительным. Поэтому мы ставим знак минус перед одним из аргументов функции. Как вы знаете, умножение «минуса» на «минус» в итоге дает «плюс».

   Мы вообще не вводим никаких данных в поля «Фс» и «Тип». Нажимаем на кнопку «ОК».

4. После этого оператор рассчитывает и выводит в заранее заданную ячейку результат общей ежемесячной оплаты — 30 108,26 руб. Но проблема в том, что заемщик может платить максимум 29000 рублей в месяц, то есть ему следует найти банк, который предлагает условия с более низкой процентной ставкой, или уменьшить кредитную организацию, или увеличить срок кредита. Таблица поиска поможет нам рассчитать различные варианты действий.
5. Во-первых, давайте воспользуемся поисковой таблицей с одной переменной. Посмотрим, как будет меняться размер обязательного ежемесячного платежа при различных вариациях годовой ставки, начиная от 9,5% годовых и заканчивая 12,5% годовых с шагом 0,5%. Все остальные условия оставляем без изменений. Мы строим табличный диапазон, названия столбцов которого будут соответствовать различным изменениям процентной ставки. В этом случае оставим строку «Ежемесячные платежи» как есть. Его первая ячейка должна содержать вычисленную ранее формулу. Для получения дополнительной информации вы можете добавить строки «Общая сумма ссуды» и «Общая сумма процентов». Столбец, содержащий расчет, выполняется без заголовка.
6. Далее рассчитаем общую сумму кредита на текущих условиях. Для этого выберите первую ячейку в строке «Общая сумма ссуды» и умножьте содержимое ячеек «Ежемесячный платеж» и «Срок ссуды». Затем нажмите клавишу Enter.
7. Чтобы рассчитать общую сумму процентов в текущих условиях, мы аналогичным образом вычитаем сумму кредитора из общей суммы кредита. Чтобы просмотреть результат на экране, нажмите кнопку Enter. Таким образом, мы получаем сумму, которую переплачиваем при погашении кредита.
8. Пришло время применить инструмент «Таблица данных». Выбираем весь массив таблиц, кроме названий строк. Далее переходим во вкладку «Данные». Нажимаем кнопку на ленте «Анализ« что, если »», которая находится в группе инструментов «Работа с данными» (в Excel 2016 группа инструментов «Прогноз»). Затем открывается небольшое меню. В нем выбираем пункт «Таблица данных…».
9. Откроется небольшое окно с названием «Таблица данных». Как видите, у него два поля. Поскольку мы работаем с одной переменной, нам нужна только одна. Поскольку мы меняем переменную по столбцам, мы будем использовать поле «Заменить значения столбцами в». Поместите туда курсор, затем щелкните ячейку в исходном наборе данных, которая содержит текущий процент. После того, как координаты ячейки отобразятся в поле, нажмите кнопку «ОК».
10. Инструмент рассчитывает и заполняет весь диапазон таблицы значениями, соответствующими различным вариантам процентной ставки. Если вы поместите курсор на любой элемент этой области таблицы, вы увидите, что в строке формул отображается не обычная формула для расчета платежа, а специальная формула неразрушимого массива. То есть изменить значения в отдельных ячейках больше нельзя. Результаты расчета можно удалить только вместе, а не по отдельности.

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

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

Урок: Расчет аннуитета в Excel

Способ 2: использование инструмента с двумя переменными

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

1. Нарисуем новый массив таблиц. Теперь в названиях столбцов будет указан срок кредита (от 2 до 6 лет в месяцах с шагом в один год), а в строках — размер тела кредита (от 850 000 до 950 000 рублей с шаг 10000 руб). В этом случае обязательным условием является то, чтобы ячейка, в которой находится формула расчета (в нашем случае PMT), находилась на границе имен строки и столбца. Без этого условия инструмент не будет работать при использовании двух переменных.
2. Затем мы выбираем весь результирующий диапазон таблицы, включая имена столбцов, строки и ячейку с формулой PMT. Переходим во вкладку «Данные». Как и в прошлый раз, в группе инструментов «Работа с данными» нажимаем на кнопку «Анализ» что если », в открывшемся списке выбираем пункт« Таблица данных…».
3. Откроется окно инструмента Таблица данных. В этом случае нам нужны оба поля. В поле «Заменить значения столбцов в» указываем координаты ячейки, содержащей в первичных данных срок кредита. В поле «Заменить значения для строк в» укажите адрес ячейки исходных параметров, содержащей размерность кредитора. После того, как все данные были введены. Нажимаем на кнопку «ОК».
4. Программа вычисляет и заполняет диапазон таблицы данными. На пересечении строк и столбцов теперь можно увидеть, какой именно будет ежемесячный платеж с учетом соответствующей годовой процентной ставки и указанного срока кредита.
5. Как видите, есть некоторые ценности. Для других задач может быть и больше. Поэтому, чтобы сделать вывод результатов более наглядным и сразу определить, какие значения не удовлетворяют определенному условию, вы можете использовать инструменты визуализации. В нашем случае это будет условное форматирование. Мы выбираем все значения диапазона таблицы, за исключением заголовков строк и столбцов.
6. Перейдите на вкладку «Главная» и щелкните значок «Условное форматирование». Он находится на панели инструментов «Стили» на ленте. В открывшемся меню выберите пункт «Правила выбора ячеек». В дополнительном списке щелкните запись «Меньше…».
7. Далее открывается окно для настройки условного форматирования. В левом поле укажите меньшее значение, при котором будут выбираться ячейки. Как вы помните, нас устраивает условие, что ежемесячный платеж по кредиту будет меньше 29 000 рублей. Вставляем указанный номер. В правом поле вы можете выбрать цвет выделения, хотя вы можете оставить его по умолчанию. После того, как вы ввели все необходимые настройки, нажмите кнопку «ОК».
8. После этого все ячейки, значения которых соответствуют указанному выше условию, будут выделены цветом.

Проанализировав массив таблиц, можно сделать некоторые выводы. Как видите, при существующем сроке кредита (36 месяцев) для инвестирования указанной суммы ежемесячного платежа нам необходимо взять кредит не более 860 000,00 рублей, что на 40 000 меньше, чем планировалось изначально.

Если мы все же намерены взять кредит в размере 900 000 рублей, то срок кредита должен составлять 4 года (48 месяцев). Только в этом случае размер ежемесячного платежа не будет превышать установленный лимит в 29 000 рублей.

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

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

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