Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки.

 

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

“Таблица данных” оперирует одной или двумя величинами одновременно.

 

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

1. Следует сформировать таблицу подстановки с одной переменной так, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке).

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

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

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

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

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

7. Выбрать команду Таблица подстановки в меню Данные.

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

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

 

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

Выполнение работы

Записываем исходные данные и формулу для расчета суммы выплат, как и в первом задании. Затем создаем таблицу с данными. Для этого пишем заголовки столбцов, затем - в ячейке С2 записываем формулу =В3, в ячейку В8 -формулу =В4, в ячейку С8 - формулу =В5. Для расчета процента от 15% до 20% с шагом 0,5% записываем формулу =A8+0,005 и копируем ее вниз по столбцу, пока не получим значение 20%. Затем выделяем диапазон ячеек A8:c10 щелкаем на пункте меню Данные → Таблица подстановки и в окошке Подставлять значения по строкам в записываем адрес ячейки с процентной ставкой:

После нажатия на ОК получим следующую таблицу:

	А	В	Формулы:
	Размер	850 000грн.
	Срок вклада
	процент. Ставка	10%	0,1
	коэффициент наращения	4,177248169	=B5/B1
	Сумма выплаты	3 550 660,94грн.	=БC(B3;B2;0;-B1;0)
	процент. Ставка	Коэфф	Сумма выплаты
	15,0%	4,18	3 550 660,94грн.
	15,5%	8,68	7 381 569,09грн.
	16,0%	9,27	7 875 692,74грн.
	16,5%	9,88	8 400 551,65грн.
	17,0%	10,54	8 957 913,24грн.
	17,5%	11,23	9 549 638,35грн.
	18,0%	11,97	10 177 685,70грн.
	18,5%	12,76	10 844 116,66грн.
	19,0%	13,59	11 551 100,08грн.
	19,5%	14,47	12 300 917,48грн.
	20,0%	15,41	13 095 968,34грн.

Таблица вариантов

 

№	Размер вклада	Срок вклада	Процентная ставка	№	Размер вклада	Срок вклада	Процентная ставка
			10,0%				9,0%
			9,0%				8,0%
			8,0%				7,0%
			7,0%				6,0%
			6,0%				5,0%
			5,0%				4,0%
			4,0%				3,0%
			5,5%				4,5%
			7,0%				6,0%
			8,5%				7,5%
			10,0%				9,0%
			11,5%				10,5%
			10,5%				9,5%
			9,5%				8,5%
			8,5%				7,5%