Подбор параметра.

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

Таблица подстановки позволяет представить результаты формул в зависимости от значений одной или двух переменных, которые используются в этих формулах. С помощью команды Данные Þ Кнопка Анализ «что-если» Þ Таблица данных можно создать два типа таблиц.

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

Чтобы создать такую таблицу на рабочем листе вводятся процентные ставки 6; 6,5; 7; 7,5 и 8% в ячейки B3:B7. Должна обязательно присутствовать формула, которая использует входную переменную, разместим ее в ячейку С2:

=ПЛТ(В2/12;360;200000),

где: ПЛТ – финансовая функция для вычисления суммы периодического

платежа;

В2/12 - месячная процентная ставка;

360 - срок ссуды в месяцах (12*30=360);

200000 - размер ссуды.

Рисунок 5.20 – Таблица данных для одной переменной

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

Выделить диапазон таблицы данных, включающий в себя формулу и все значения входного диапазона: B2:C7. Выполнить команду Таблица данных (рис 5.20). Ввести ссылку $В$2 в поле Подставлять значения по строкам в. Нажать ОК. Excel выведет шесть результатов в диапазон С3:С7 (рис. 5.21).

Таблицы подстановки для двух переменных.

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

Рисунок 5.21 – Окно таблицы данных

Для создания такой таблицы ввести входной диапазон процентных ставок в ячейки B3:B7, как в предыдущем примере. Ввести второе множество входных значений (15; 20; 25; 30 и 35 лет) в диапазон C2:G2.

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

=ПЛТ(А2/12;B1;200000)

Формула возвращает ошибочное значение #ДЕЛ/0!, так как в вычислениях используются две пустые ячейки, и в результате получается или слишком большое или очень малое число, которое Excel не может представить. Этот результат не оказывает никакого влияния на работу таблицы.

Выделить диапазон таблицы данных B2:G7. Выполнить команду Таблица данных и задать входной диапазон. Поскольку эта таблица с двумя переменными, то следует задать две входные ячейки. Ввести ссылку $B$1 в поле Подставлять значения по столбцам в и $A$2 в поле Подставлять значения по строкам в (рис. 5.22). Нажать ОК.

Рисунок 5.22 - Таблица данных с двумя переменными

Можно редактировать формулы и входные значения, но изменять содержимое ячеек в выходном диапазоне результатов нельзя.

С помощью команды Данные Þ Анализ «что-если» Þ Подбор параметра можно выполнять подбор значения аргумента под заданное значение функции. Например, мы берем кредит на сумму 500000 руб. на 5 лет с выплатами поквартально под 20% годовых. В результате нам придется платить по 40121,29 руб. каждый квартал в течение 5 лет (рис. 5.23).

Рисунок 5.23 – Подготовка данных

Рисунок 5.24 – Окно Подбор параметра

Такая выплата нам не подходит, так как мы можем выплачивать не более 30000 руб. в квартал. Под заданную сумму платежа поберем процентную ставку. В диалоговом окне Подбор параметра (рис. 5.24) задается в поле Установить в ячейке $B$5 требуемое значение функции в поле Значение, в поле Изменяя значение ячейки указывается адрес ячейки, содержащей один из аргументов функции, а именно процентную ставку.

Рисунок 5.25 – Итог подбора параметра

В результате получаем (рис. 5.25), что мы при заданных условиях можем взять кредит только под 7% годовых. В этом примере можно также подобрать по очереди значения срока и суммы кредита.

Дата добавления: 2017-02-01