1.Понятие таблиц данных (таблиц подстановки)

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

Применяют два вида таблиц:

с одной переменной;

с двумя переменными.

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

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

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

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

Для решения задачи можно использовать Таблицу подстановки Excel, следует выполнить следующие действия:

1.Необходимо подготовить исходные данные на листе в виде таблицы 6.1.

2.Ввести в ячейку С2 формулу для расчета периодических постоянных выплат по займу при условии, что он полностью погашается в течение срока займа (функция ППЛАТ записывается в относительных адресах.). Функция в ячейке С2 имеет следующий вид:

= ППЛАТ(B2/12;5*12;400)

3.Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета. В нашем примере это диапазон В2: С15

4.В меню Данные выберите команду Таблица подстановки. На экране появится диалоговое окно Таблицы подстановки. Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета

(ячейка В2) Щелкните в редактируемом поле Подставлять значения по строкам в (так как данные в примере приведены в столбце), а затем либо введите в поле адрес ячейки, либо щелкните на ячейке, которая указана в формуле (В2).

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

5.При нажатии кнопки ОКExcel заполнит столбец результатов (диапазон С3: С15).

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

Таблица 1.

	A	B	C	D	E	F
1	Таблица подстановки с одним параметром
2			-6,67	Формула в ячейке С2 - ППЛАТ(B2/12;5*12;400)
3	Процентные ставки	9,50%	-8,40	В столбце С приведен результат работы таблицы подстановки
4		9,75%	-8,45
5		10,00%	-8,50
6		10,25%	-8,55
7		10,50%	-8,60
8		10,75%	-8,65
9		11,00%	-8,70
10		11,25%	-8,75
11		11,50%	-8,80
12		11,75%	-8,85
13		12,00%	-8,90
14		12,25%	-8,95
15		12,50%	-9,00

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

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

Таблица 2

	A	B	C	D	E	F	H	I
1	Таблица подстановки с двумя параметрами
2				Месяцы
3		#ДЕЛ/0!	12	24	36	48	60	72
4	Ставки	9,50%	-35,07	-18,37	-12,81	-10,05	-8,40	-7,31
5		9,75%	-35,12	-18,41	-12,86	-10,10	-8,45	-7,36
6		10,00%	-35,17	-18,46	-12,91	-10,15	-8,50	-7,41
7		10,25%	-35,21	-18,50	-12,95	-10,19	-8,55	-7,46
8		10,50%	-35,26	-18,55	-13,00	-10,24	-8,60	-7,51
9		10,75%	-35,31	-18,60	-13,05	-10,29	-8,65	-7,56
10		11,00%	-35,35	-18,64	-13,10	-10,34	-8,70	-7,61
11		11,25%	-35,40	-18,69	-13,14	-10,39	-8,75	-7,66
12		11,50%	-35,45	-18,74	-13,19	-10,44	-8,80	-7,72
13		11,75%	-35,49	-18,78	-13,24	-10,48	-8,85	-7,77
14		12,00%	-35,54	-18,83	-13,29	-10,53	-8,90	-7,82
15		12,25%	-35,59	-18,88	-13,33	-10,58	-8,95	-7,87
16		12,50%	-35,63	-18,92	-13,38	-10,63	-9,00	-7,92
	Формула в ячейке В3:		= ППЛАТ (А5/12;С2;400)

Ввести первое множество входных значений (процентные ставки) в столбец (диапазон В4:В16).

Ввести второе множество входных значений (сроки погашения) в строку, расположенную выше и правее на одну ячейку от начала первого диапазона (диапазон C3:I3).

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

Необходимо выделить диапазон таблицы данных включающий все входные значения и формулу расчета (B3:I 16).

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

6.При нажатии кнопки ОКExcel выполнит расчет таблицы подстановки (см. таблицу .2).

При изменении суммы займа Excel автоматически пересчитает всю таблицу.