Таблицы данных для одной переменной

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

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

1. На новом рабочем листе введите интересующие вас процентные ставки. Для этого примера введите 6, 6,5, 7, 7,5, 8 и 8,5 процентов в ячейки ВЗ:В8 (мы называем этот диапазон входным диапазоном, т.к. он содержит входные значения, которые мы хотим проверить).

2. Затем введите формулу, которая использует входную переменную. В данном случае введите в ячейку С2 формулу:

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

где А2/12 – месячная процентная ставка, 360 – срок ссуды в месяцах и 200000 – размер ссуды. Обратите внимание, что эта формула ссылается на ячейку А2, которая в данный момент пустая (при расчете числовых формул MS Excel присваивает пустым ячейкам значение 0). Как вы можете заметить, поскольку А2 пустая, то функция возвращает величину ежемесячных выплат, необходимую для погашения ссуды при нулевой процентной ставке. Ячейка А2 является только меткой, через которую Excel будет подставлять значения из входного диапазона. На самом деле Excel не изменяет хранимое значение в этой ячейке, поэтому такой меткой может быть любая ячейка рабочего листа вне диапазона таблицы данных.

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

4. Выполните командуДанные / Работа с данными / Анализ «что-если» / Таблица данных… В окне диалога Таблица данныхзадайте местонахождение входной ячейки в полеПодставлять значения по строкам в или в поле Подставлять значения по столбцам в. Входная ячейка – это ячейка-метка, на которую ссылается формула таблицы данных, в данном случае, А2. Чтобы таблица данных заполнялась правильно, вы должны ввести ссылку на входную ячейку в нужное поле. Если входные значения расположены в строке, введите ссылку на входную ячейку в полеПодставлять значения по столбцам в. Если значения во входном диапазоне расположены в столбце, используйте полеПодставлять значения по строкам в. В данном примере входные значения расположены в столбце, поэтому введите $А$2 в полеПодставлять значения по строкам в.

5. Нажмите кнопку ОК. Excel выведет значения формулы для каждого входного значения в ячейках диапазона таблицы данных. В нашем примере Excel выведет шесть результатов в диапазоне СЗ:С8. При создании этой таблицы данных Excel ввел формулу массива {=ТАБЛИЦА(;А2)} в каждую ячейку в диапазоне СЗ:С8 (диапазон результатов). В нашей таблице формула ТАБЛИЦА вычисляет значения функции ПЛТ для каждой процентной ставки в столбце В. Например, формула в ячейке С5 вычисляет размер выплаты при ставке, равной 7 процентам.

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

=ТАБЛИЦА(входная ячейка для строки ;входная ячейка для столбца).

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

После построения таблицы можно изменить формулу таблицы данных или любые значения во входном диапазоне для создания другого множества результатов. Например, предположим, что для покупки дома вы решили занять только $185 000. Если вы измените формулу в ячейке С2 на =ПЛТ(А2/12;360; 185000) значения в выходном диапазоне изменятся.