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

Расчет платежей

Таблицы подстановки в Excel применяются для анализа данных при выборе оптимального варианта финансового решения. Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок.
Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

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

Изменения двух исходных значений, просматривая результаты только одной формулы. При использовании таблицы с двумя переменными значениями одно из них располагается в столбце, другое — в строке; результат вычислений получают на пересечении строки и столбца.

На конкретном примере

Задание: использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами.
Рассчитать ежемесячные выплаты по займу и платежи по процентам. Исходные данные приведены на рисунке ниже.
Порядок работы
1. Запустите MicrosoftExcel и создайте новую электронную книгу.
2. Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.

Таблицы данных в Эксель
Таблица — заготовка для решения

3. Расчет ежемесячных выплат по займу происходит с помощью функции ПЛТ (). В ячейку В5 введите формулу:
=ПЛТ ($В$4/12;$В$3*12;$В$2).
Ежемесячная выплата составит 10178,42 р.
4. Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:
=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2)
Платежи по процентам составят 1350 р.
Краткая справка. При решении задач, связанных с использованием таблицы подстановки, рекомендуется применять в формулах абсолютную адресацию ячеек. Это способствует правильному выполнению вычислений в ячейках рабочего листа.
5. После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (смотрите рисунок ниже). В верхней строке будущей таблицы над местом расположения результатов указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно поместить в ячейку саму формулу (ячейки В9 и С9 содержат формулы). Слева расположить различные значения исходных данных, которые необходимо протестировать. Подготовку исходного диапазона данных — различных величин процентной ставки в рассматриваемом примере можно осуществить с помощью маркера автозаполнения.
6. Выделить ячейки, которые будут содержать таблицу. При этом самым левым столбцом таблицы должен быть столбец исходных значений, а в самой верхней строкой должна быть строка анализируемых формул.

Расчет ежемесячного платежа в Эксель
Расчет платежей

7. Далее вам нужно выделить диапазон ячеек A9:C18 , после чего перейти на вкладку данные, «анализ что-если» таблица данных. Первое поле «подставлять значения по столбцам в» оставить пустым, а в поле «подставлять значения по строкам в» указать ячейку с величиной процентной ставки зафиксировав ее знаками доллара $B$4.

Подстановка данных в Эксель
Подстановка данных

Результат расчетов:

Результат расчетов в Эксель
Результат
Таблицы подстановки в Excel обновлено: Ноябрь 9, 2018 автором: Анатолий Бузов

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *