Создание таблиц подстановки
Таблицы подстановки позволяют выполнить расчет и анализ данных для одного или двух наборов данных.
Таблицы подстановки используются в следующих случаях:
· Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом.
· Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами.
На использование таблиц подстановки наложены некоторые ограничения. Самое главное ограничение - это то, что она может временно оперировать только с одной или двумя ячейками исходных данных. Другими словами, нельзя создать таблицу подстановки, которая бы использовала комбинацию трех или более ячеек с исходными данными.
Команда Данные - Таблица подстановки позволяет создавать таблицы подстановки.
В таблице подстановки с одним входом приводятся результаты расчетов по одной или нескольким формулам при различных значениях одного входного параметра.
общий макет таблицы подстановки с одним входом.
 не используется
| Произвольное количество формул или ссылок на формулы | |||||||
| Результаты расчетов для разных значений входного параметра (формула массива) | ||||||||
| ||||||||
| массив исходных данных | ||||||||
Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами, по которым рассчитывается результат. Можно использовать любое количество ссылок на формулы (или только одну). Верхняя левая ячейка таблицы не используется. Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в соответствующий столбец (в ячейку, которая находится под ячейкой с соответствующей формулой или ссылкой на формулу).
Пример. Расчет ипотечной ссуды (файл Таблица_подстановки)
На рабочем листе расположены данные
| Расчет ипотечной ссуды | ||||||
| A | B | C | D | |||
| Исходные данные | ||||||
| Цена | ||||||
| Первый взнос | 20% | |||||
| Срок погашения ссуды | ||||||
| Процентная ставка | 8,00% | |||||
| Результаты расчета | ||||||
| Размер ссуды | =D4*(1-D5) | |||||
| Месячная плата | =ПЛТ(D7/12;D6;-D10) | |||||
| Общая сумма | =D11*D6 | |||||
| Общая сумма комиссионных | =D12-D4 | |||||
ПЛТ – стандартная финансовая функция расчета процентов по кредиту
Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных, при изменении ставок от 7% до 9% с шагом 0,25%.
заготовка таблицы подстановки для описанного примера.
Строка 2 состоит из ссылок на соответствующие ячейки с формулами.
| Таблица подстановки | ||||
| 8,00% | ||||
| 7,00% | ||||
| 7,25% | ||||
| 7,50% | ||||
| 7,75% | ||||
| 8,00% | ||||
| 8,25% | ||||
| 8,50% | ||||
| 8,75% | ||||
| 9,00% |
Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11), а затем выберите команду Данные - Таблица подстановки. Появится диалоговое окно «Таблица подстановки»
Необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7). Щелкните на кнопке OK, и Excel заполнит таблицу соответствующими результатами.
Таблица подстановки с одним входом может быть организована вертикально или горизонтально. Если значения исходных данных, которые должны подставляться во входную ячейку, расположены в строке, введите ссылку на эту ячейку в поле Подставлять значения по столбцам в, которое находится в диалоговом окне Таблица подстановки.