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

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

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

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

Исходные данные (рисунок 10) – из предыдущей задачи (копируем на листы 4–6).

Рисунок 10 – Исходные данные

Задание:

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

11

Задача 1 – по одному параметру (процентная ставка)

Задача 1 выполняется на листе 4.

Сначала составьте список процентных ставок (рисунок 11, 12).

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

Рисунок 12 – Режим проверки формул

Вячейки Е2 и F2 вводим формулы, такие же как в ячейках В4 и В5.

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

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

ячейки G2, Н2 и т.д.

Примечание:

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

12

Перед построением таблицы подстановки выделите диапазон ячеек со списком исходных значений и диапазон ячеек с формулами (D2:F16). После этого выберите в меню Данные команду Таблица подстановки.

а)

б) Рисунок 13 – Вызов команды Таблица подстановки из меню Данные:

а) для MS Word 2003; б) для MS Word 2007.

В первой части задания построим таблицу подстановки по одной переменной, в качестве которой выберем процентную ставку. Это значение находится в ячейке В3, на которую ссылаются формулы в ячейках В4 и В5.

Установите курсор ввода в поле Подставлять значение по строкам в диалоговое окно Таблица подстановки. Выделите ячейку В3, чтобы вставить её адрес в поле ввода, и нажмите кнопку ОК. После этого начнется процесс создания таблицы подстановки.

Рисунок 14 – Диалоговое окно Таблица подстановки

13

Рисунок 15 – Созданная таблица подстановки

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

При внимательном изучении результата (рисунок 13) видно, что для построения таблицы подстановки использовалась формула:

{=ТАБЛИЦА(;В3)}

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

Примечание:

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

14