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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Итак, приступаем к расчету. Выделяем ячейку на листе, куда будет выводиться расчетное значение. Клацаем по кнопке «Вставить функцию».

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

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

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

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

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

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

Если же мы все-таки намерены брать кредит размером 900000 рублей, то срок кредитования должен составлять 4 года (48 месяцев). Только в таком случае размер ежемесячного платежа не превысит установленную границу в 29000 рублей.

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12747 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

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

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