Создание таблиц подстановки

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

Таблицы подстановки используются в следующих случаях:

· Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом.

· Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами.

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

Команда Данные - Таблица подстановки позволяет создавать таблицы подстановки.

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

общий макет таблицы подстановки с одним входом.

Создание таблиц подстановки - student2.ru не используется   Произвольное количество формул или ссылок на формулы    
                 
                 
                 
      Результаты расчетов для разных значений входного параметра (формула массива)    
         
         
         
  Создание таблиц подстановки - student2.ru      
         
         
                 
  массив исходных данных            
             
                 

Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами, по которым рассчитывается результат. Можно использовать любое количество ссылок на формулы (или только одну). Верхняя левая ячейка таблицы не используется. 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), а затем выберите команду Данные - Таблица подстановки. Появится диалоговое окно «Таблица подстановки»

Создание таблиц подстановки - student2.ru

Необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7). Щелкните на кнопке OK, и Excel заполнит таблицу соответствующими результатами.

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

Наши рекомендации