2 Таблицы постановки с двумя переменными
Если результаты вычислений должны зависеть от двух параметров, то необходимо использовать таблицу подстановки с двумя переменными. Например, для анализа рассмотренной выше таблицы период выплаты можно принять за первое множество переменных, а размер ежемесячного взноса — за второе, в результате чего будет получена таблица с двумя переменными. В каждом столбце (или строке) этой таблицы будет содержаться информация о размере ежемесячных выплат при различных процентных ставках для определенных сроков займа.
Структура таблицы подстановки с двумя переменными показана на рис. 8. Такая таблица может содержать только одну формулу, которая помещается в ячейку, расположенную в верхнем левом углу. (В таблице подстановки с одной переменной эта ячейка остается пустой.)
Рисунок 8 – Таблица подстановки с двумя переменными
Таблица подстановки с двумя переменными формируется на основе пяти компонентов:
ячейки ввода для значений подстановки в строке, в которую будут подставлены исходные значения, находящиеся в строке;
ячейки ввода для значений подстановки в столбце, в которую будут подставлены исходные значения, находящиеся в столбце;
строки исходных значений;
столбца исходных значений;
одной формулы, которая содержит ссылки на ячейки ввода для значений подстановки в строке и столбце.
Таблица подстановки с двумя переменными имеет одинаковое число строк и столбцов. Исходные данные берутся из заголовков столбцов и названий строк. Содержимое других ячеек вычисляется следующим образом. Исходные значения из строки и столбца последовательно подставляются в соответствующие ячейки ввода. Определяемый при помощи заданной формулы результат помещается в ячейку, находящуюся на пересечении строки и столбца, из которых берутся исходные значения для расчета.
Напомним, что мы будем рассчитывать величину накоплений в зависимости от периода и размера выплат в пенсионный фонд. Формула помещается в ячейку В6. Ячейкой ввода для исходных значений, находящихся в столбцах, является ячейка С2, а ячейкой ввода для исходных значений, находящихся в строках, - ячейка В2.
Порядок действий, выполняемых при создании таблицы подстановки с двумя переменными, аналогичен описанному выше. Различие состоит лишь в том, что пользователь должен указать в диалоговом окне Таблица подстановки две ячейки ввода. Таблица подстановки для примера показана на рис. 9.
Рисунок 9 – Заполненная таблица подстановки с двумя переменными
Сохраните результат созданной таблицы подстановки двумя переменными.
Заметим, что диапазон ячеек С7:Н12 содержит формулу
{=ТАБЛИЦА(В2;С2)}
В этом случае в формуле указываются два аргумента. Один из них представляет собой ссылку на ячейку, в которую подставляются значения первого параметра, расположенные в строке. Другим аргументом служит адрес ячейки, куда подставляются значения второго параметра, то есть значения из столбца.
3 Редактирование таблиц подстановки
В таблице подстановки можно редактировать исходные значения в строках и столбцах. При этом она автоматически пересчитывается. Ни один из результатов изменить нельзя. Поэтому таблица подстановки имеет большую степень защиты, чем таблица, построенная путем копирования формулы в ячейки, которые должны содержать результаты.
Будучи скопированной в другое место рабочей книги (или в другую рабочую книгу), таблица подстановки перестает быть таковой. Все входящие в нее формулы копируются как формулы, причем ссылки на ячейки обновляются в соответствии с новыми положениями. А все результирующие значения превращаются в константы рабочего листа, даже в том случае, если в исходной таблице они рассчитывались с помощью формул.
Для удаления результатов из таблицы подстановки необходимо выделить все ее ячейки и вызвать команду Правка/Очистить/Все. Если отмечены не все ячейки, то Excel выдает сообщение, что часть таблицы подстановки изменить нельзя.
Заменить таблицу можно путем повторного выделения области, в которой она должна располагаться, и выполнения описанных выше операций по ее созданию.