Сведение параметров займа в таблицу данных

Если вы решили взять кредит, вам придется подобрать множество переменных, не последней из которых является процентная ставка. Команда ДанныеРабота с даннымиАнализ “что-если”Таблица данных позволяет подытожить результаты вычислений, использующих разные входные данные.

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

Создание односторонней таблицы данных

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

На следующем рисунке показана односторонняя таблица данных (диапазон D2:G9), отображающая три вычисления (размер платежа, общая сумма платежей и общая сумма процентов) для займа с использованием восьми процентных ставок от 6,75% до 8,50%. Для ввода используется ячейка В2. Обратите внимание, что диапазон E1:G1 принадлежит таблице данных – эти ячейки содержат обычные надписи.

Для создания таблицы данных выполните следующие действия:

1. В первой строке таблицы данных введите формулы, возвращающие результаты.

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

Е2: =В6
F2: =В7
G2: =В8

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

В примере входным значением является процентная ставка. Разные значения входной величины введены в ячейки D2:D9. Обратите внимание, что в первой строке таблицы данных (строка 2) отображены результаты для первого входного значения.

3. Выделите диапазон, содержащий введенные на предыдущих шагах значения.

В нашем примере это D2:G9.

4. Выберите команду ДанныеРабота с даннымиАнализ “что-если”Таблица данных. Откроется диалоговое окно, показанное на рисунке.

5. В поле Подставлять значения по строкам введите ссылку на ячейку, соответствующую входной переменной. В нашем случае это $В$2.

6. Поле Подставлять значения по столбцам оставьте пустым и щелкните на кнопке ОК.

Excel вставит формулу массива, в которой используется функция ТАБЛИЦА с одним аргументом.

Совет

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

Обратите внимание, что формула массива не вводится в весь диапазон ячеек, выбранный в п.4, – первый столбец и первая строка выделения остаются неизменными.

Создание двусторонней таблицы данных

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

На рисунке ниже показана двусторонняя таблица данных (диапазон В7:J16), которая отображает платежи по займу, используя восемь процентных ставок и девять сумм кредита.

Для создания этой таблицы данных выполните следующие действия.

1. Введите формулу, которая будет возвращать результаты, формирующие таблицу данных.

В представленном примере формула в ячейке В7 является ссылкой на ячейку В5, содержащую формулу вычисления платежа.

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

Первой входной переменной будет процентная ставка; ее разные значения введите в диапазон С7:J7.

3. Введите различные значения второй входной переменной в первый столбец таблицы данных.

В данном примере второй входной переменной будет величина кредита; ее разные значения введите в диапазон В8:В13.

4. Выделите диапазон ячеек, охватывающий введенные на предыдущих шагах значения. В примере это диапазон В7:J13.

5. Выберите команду ДанныеРабота с даннымиАнализ “что-если”Таблица данных. Откроется диалоговое окно Таблица данных.

6. В поле Введите значения по строкам введите ссылку на первую ячейку ввода (в данном примере это В2).

7. В поле Введите значения по столбцам введите ссылку на вторую ячейку ввода (в данном примере это В1).

8. Щелкните на ОК.

Программа Excel вставит формулу массива, в которой используется функция ТАБЛИЦА с двумя аргументами.

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

=ПЛТ(В2*(ВЗ/12);В4;-В1)*В4-В1

В этом примере функция ТАБЛИЦА отобразит общую сумму платежей по процентам, а не платежи за период.

Совет

Если окажется, что таблица данных существенно замедляет вычисления в рабочей книге, выберите команду ФормулыВычислениеПараметры вычисленийАвтоматически, кроме таблиц данных. После этого можно запустить пересчет значений таблицы, нажав клавишу <F9>.

В начало

Полезное

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

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