Таблицы данных для двух переменных

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

Предположим, что вы хотите создать таблицу данных, которая вычисляет месячные выплаты по ссуде в 2000000, но на этот раз нужно изменять не только процентную ставку, а также и срок ссуды. Требуется узнать месячные выплаты для процентной ставки 6, 6,5, 7, 7,5, 8 и 8,5% при сроках 15, 20, 25 и 30 лет (соответственно 180, 240, 300 и 360 месяцев).

Чтобы создать такую таблицу, выполните описанные ниже действия.

1. Введите первое множество входных значений в столбец. Как и раньше, введите шесть значений процентных ставок в ячейки В3:В8.

2. Введите второе множество входных значений в строке, начинающейся выше и правее на одну ячейку от начала первого диапазона. В данном случае введите в ячейки C2:F2 сроки ссуды: 180, 240, 300 и 360.

3. Введите величину ссуды в ячейку вне области таблицы, в данном случае в ячейку H2.

4. Теперь можно создать формулу для таблицы. Поскольку это таблица с двумя переменными, формула должна быть введена в ячейку на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В2. Хотя в таблицу данных для одной переменной можно включить любое число формул, в таблице с двумя переменными допускается использование только одной формулы. Формула для таблицы в нашем примере будет иметь следующий вид:
=ППЛАТ (А2/12;В1;H2)
Объедините ячейки С1:F1 и введите надпись - Кол-во месяцев. Объедините ячейки А3:А8 и введите надпись – Ставка:

На рис. 19. представлен результат, полученный на данный момент. Формула возвращает ошибочное значение # ДЕЛ/0!, так как в вычислениях используются две пустые ячейки (А2 и В1) и в результате получается слишком большое или очень малое число, которое Excel не может представить. Как вы увидите дальше, этот результат не оказывает никакого воздействия на работу таблицы.

Рис. 19

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

6. В меню Данные выберите команду Таблица подстановки и задайте входные ячейки. Поскольку это таблица с двумя переменными, вы должны задать две входные ячейки: одну для первого множества входных значений, другую – для второго. В этом примере введите ссылку для первой входной ячейки $B$1 в поле Подставлять значения по столбцам в: и затем введите ссылку для второй входной ячейки $А$2 в поле Подставлять значения по строкам в:

7. Нажмите клавишу <Enter> или кнопку ОК для вычисления таблицы. Результат представлен на рис. 20.

Рис. 20.