1 Таблицы подстановки

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

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

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

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

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

Рисунок 4 - Таблицы подстановки с одной переменной

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

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

Рисунок 5 – Таблица, подготовленная для вызова команды подстановки

В таблице подстановки используются две формулы. Обратите внимание на формулу в ячейке Е5: именно она содержит ссылку на ячейку С2, которая является ячейкой ввода. Значение в ячейке F5 рассчитывается на основе данных ячейки Е5.

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

Рисунок 6 – Окно Таблица подстановки

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

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

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

{=ТАВЛИЦА(;С2)}

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