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

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

Excel может создавать таблицы подстановки следующих типов:

- таблицы подстановки с одной переменной и с одной или несколькими формулами;

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

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

1) процентной ставки на сумму возврата вклада;

2) процентной ставки на коэффициент увеличения вклада;

3) величины процентной ставки и изменения срока вклада на сумму возврата вклада.

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

Откройте файл Подбор.xls (см. лабораторную работу №5) и активизируйте лист 3, присвойте ему имя Таблицы подстановки.

Перед началом таблицы вставьте две пустые строчки.

Сделайте на этом же листе еще две копии таблицы с пятью пустыми строками перед каждым экземпляром таблицы.

В качестве переменной используйте процентную ставку (ячейка ввода В5), которая может принимать значения от 3 до 10%. Введите эти значения в столбец D согласно приведенному образцу (Таблица 6).

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

	A	B	C	D	E
				Процент	Сумма возврата
					=B3*B6
	Размер вклада	5000руб.		3%
	Срок вклада			4%
	Процентная ставка	5%		5%
	Коэфф. увеличения	1,28		6%
	Сумма возврата	6381руб.		7%
				8%
				9%
				10%

В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы вклада: =В3*В6.

Выделите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.

Введите команды ДАННЫЕ à Таблица подстановки и в диалоговом окне в поле Подставлять значения по строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) - $B$5.

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

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

Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: = (1+В5)^В4.

Выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕ à Таблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $B$5.

Проанализируйте полученные результаты.

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

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

Таблицы подстановки с двумя переменными позволяет решить еще одну задачу – определить влияние величины процентной ставки и изменения срока вклада на сумму возврата вклада.

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

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

Скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10

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

В строку правее формулы введите значения подстановки для второй переменной – срока вклада – от 5 до 10 лет.

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

Введите команду ДАННЫЕ à Таблица подстановки.

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

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

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

Проанализируйте полученные результаты.

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