Создание таблиц подстановки

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

Таблицы подстановки используются в следующих случаях:

· Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом.

· Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами.

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

Команда Данные - Таблица подстановки позволяет создавать таблицы подстановки.

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

общий макет таблицы подстановки с одним входом.

не используется		Произвольное количество формул или ссылок на формулы
			Результаты расчетов для разных значений входного параметра (формула массива)
	массив исходных данных

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

Пример. Расчет ипотечной ссуды (файл Таблица_подстановки)

На рабочем листе расположены данные

	Расчет ипотечной ссуды
	A	B	C	D
	Исходные данные
	Цена
	Первый взнос	20%
	Срок погашения ссуды
	Процентная ставка	8,00%
	Результаты расчета
	Размер ссуды		=D4*(1-D5)
	Месячная плата		=ПЛТ(D7/12;D6;-D10)
	Общая сумма		=D11*D6
	Общая сумма комиссионных		=D12-D4

ПЛТ – стандартная финансовая функция расчета процентов по кредиту

Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных, при изменении ставок от 7% до 9% с шагом 0,25%.

заготовка таблицы подстановки для описанного примера.

Строка 2 состоит из ссылок на соответствующие ячейки с формулами.

Таблица подстановки
8,00%
7,00%
7,25%
7,50%
7,75%
8,00%
8,25%
8,50%
8,75%
9,00%

Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11), а затем выберите команду Данные - Таблица подстановки. Появится диалоговое окно «Таблица подстановки»

Необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7). Щелкните на кнопке OK, и Excel заполнит таблицу соответствующими результатами.

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