6.Таблица подстановки

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

В Excel предусмотрено создание таблиц  подстановки  двух  видов:

1 вариант: тестируется изменение значения одной или нескольких формул при изменении отдельного параметра.

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

Таблица подстановки для одной переменной.

Задача

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

Решение

Для решения задачи необходимо выполнить следующие действия:

1. Поместить в ячейку значение потенциальной процентной ставки - входной диапазон.

2. Ввести величину кредита в ячейку вне табличных данных.

3. Ввести соответствующую формулу: =ПЛТ(А2/12;360;С1), где А2 – это месячный процент; 360 – срок погашения ссуды в месяцах; С1 – ссылка на величину суммы взайма.

Примечание

Формула ссылается на ячейку А2, которая в данный момент пуста, при общете формулы Excel присваиваем пустым ячейкам 0, поэтому функция в ячейке С2 возвращает величину ежемесячных выплат рассчитанную при нулевой процентной ставке. Закончив ввод исходных данных выделяем диапазон табличных данных—минимальный прямоугольный блок, захватывающий формулу и входной диапазон—затем вкладка—данные—анализ что если—таблица данных—появляется окно таблица данных—в место нахождения в ячейке ввода указывая в поле:- подставлять значения по столбцам в _____, подставлять значения по строкам в______.

Ячейка ввода это ячейка, на которую ссылается ячейка в формуле таблицы под (в нашем случае А2). Если входной диапазон является строкой ссылка на входную ячейку задается в поле «подставлять значение по столбцам в ___.» Если строкой то наоборот подставляется значение по строкам в ____. Далее вставляем результат и нажимаем ОК. Получаем искомый результат.

У нас создается формула массива ({и}) не могу вносить изменения.

Таблица подстановки с несколькими формулами.

В таблице подстановки можно включить не одна, а несколько формул.

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

Таблица подстановки для двух переменных.

Задача:

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

РЕШЕНИЕ:

1. вводим величину процентных ставок.

2.данными заполнить второе множество (продолжить выполнять по ссуде за месяц) в строке расположенной непосредственно выше первой.

3.размер ссуды выносим за таблицу, поскольку наша таблица зависит от двух переменных, в формуле следует вводить ячейку, расположенную на пересечении строки и столбца, содержащих два множества входных значений. =ПЛТ(А2/12;В1;I1)

4.формула вернет ошибку (#дел/0!) поскольку для ячейки А2 и В1, используемые в качестве аргумента пустые, из за чего получается или большой или маленький результат, который программа не в состоянии интрапретировать.

5.выделяем диапазон.

6.данные---анализ «что-если»---таблица данных---в поле значения по столбцам В1, а по строкам А2---ОК.