Построение Таблицы подстановки для одной переменной
Предположим, что требуется определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 млн. руб., выданной на 3 года, при разных процентных ставках.
Для решения задачи целесообразно воспользоваться Таблицей подстановки ЕХСЕL. Предварительно необходимо подготовить исходные данные на рабочем листе ЕХСЕL, как показано на рисунке:
Для заполнения таблицы необходимо выполнить следующую последовательность действий.
1. Ввести в ячейку D7 формулу для расчета периодических постоянных выплат по займу при условии, что он полностью погашается в течение срока займа,
= ПЛТ (С4/12, СЗ*12, С2)
2. Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета – С7:D13.Исходные данные в нашем примере расположены в столбце С8:С13, поэтому результаты подстановки также будут расположены в столбце (D8:D13).
3. В меню Данные выберите команду Таблица подстановки. На экране появится диалоговое окно Таблицы подстановки:
Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В нашем примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в диалогового окна в абсолютных координатах.
Если исходные данные расположены в строке, то ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по столбцам в.
4. При нажатии кнопки OK EXCEL заполнит столбец результатов, как показано на рисунке:
Обратите внимание, что полученные периодические выплаты имеют отрицательный знак, так как сумма займа в функции ПЛТ была введена как положительное значение.
Если в Таблицу подстановки требуется включить больше формул, использующих исходные значения процентных ставок, то дополнительные формулы вставляются справа от существующей в той же строке. Затем необходимо выделить всю таблицу, включая полученные ранее значения, и заполнить диалоговое окно команды Таблица подстановки.
Построение Таблицы подстановки для двух переменных
Для анализа данных в ЕХСЕL можно построить таблицу, которая вычисляет результат подстановки двух переменных в одну формулу.
Предположим, необходимо найти ежемесячные выплаты по займу размером 300 млн. руб. для различных сроков погашения и процентных ставок.
Для того, чтобы создать Таблицу подстановки для этой задачи, необходимо выполнить следующие действия.
1. Ввести первое множество входных значений (процентные ставки) в столбец, например, в ячейки В8:В13.
2. Ввести второе множество входных значений (сроки погашения) в строку, расположенную выше и правее на одну ячейку от начала первого диапазона. В нашем примере это ячейки С7:F7.
3. Ввести формулу для расчета на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В7. Если исходные данные введены на рабочем листе ЕХСЕL так, как показано на рисунке, то формула для расчета постоянных периодических выплат по займу при полном его погашении в течение срока займа выглядит следующим образом:
=ПЛТ(С4/12,С3*12.С2)
Подготовка таблицы показана на рисунке:
Таким образом, формула для расчета располагается в верхнем левом углу области, значения одной переменной занимают столбец, расположенный ниже от формулы, а значения второй переменной – строку справа от формулы.
4. Далее необходимо выделить диапазон таблицы данных, включающий все входные значения и формулу расчета, то есть В7:F13.
5. Выбрать, в меню Данные команду Таблица подстановки и заполнить диалоговое окно:
6. При нажатии кнопки ОК ЕХСЕL выполнит расчет таблицы подстановки.
Результаты расчета:
При изменении суммы EXCEL автоматически пересчитает всю таблицу.
