Для создания таблицы подстановки с одной переменной необходимо выполнить следующую последовательность действий.
1. Сформировать в виде столбца или строки список значений, которые следует подставлять в ячейку ввода. Значения в списке могут быть считаны только в направлении возрастания номеров строк или столбцов, поэтому список следует формировать в порядке увеличения значений.
2. Ввести одну или несколько формул, которые связаны с ячейкой ввода. При этом:
a.) если подставляемые для ячейки ввода значения расположены в виде столбца, тоформулу нужно вводить в ячейку, находящуюся правее и выше первого введенного значения;
b.) если подставляемые для ячейки ввода значения сформированы в виде строки, тоформулу следует вводить в пустую ячейку, расположенную на один столбец левее и на одну строку ниже первого подставляемого значения.
3. Выделить диапазон ячеек, содержащий формулы и значения подстановки.
4. Выполнить команду Данные ► Таблица подстановки.
5. В диалоговом окне Таблица подстановки ввести абсолютный адрес ячейки ввода.
a.) Ссылку на ячейку ввода для значений подстановки, расположенных в столбце, нужно указывать в поле Подставлять значения по строкам в:…
b.) Ссылку на ячейку ввода для значений подстановки, расположенных в строке, нужно указывать в поле Подставлять значения по столбцам в:…
c.) Для создания таблицы подстановки с одной переменной и двумя и более формулами дополнительные формулы следует вводить справа от ранее введенной формулы в той же строке, если подставляемые для ячейки ввода значения сформированы в виде столбца или ниже введенной формулы в том же столбце, если подставляемые для ячейки ввода значения сформированы в виде строки.
d.) При этом не следует забывать, что все используемые для подстановки формулы должны быть прямо или косвенно связаны с одной и той же ячейкой ввода.
При внесении изменений в исходные данные будет производиться автоматический пересчет (обновление) всей таблицы подстановки.
Внимание! Рассчитанные значения таблицы подстановки представляют собой массив данных, поэтому удалить отдельное значение таблицы подстановки невозможно. Удалить можно только одновременно все значения. Для этого необходимо выделить в таблице все рассчитанные значения и ввести команду ПРАВКА ► Очистить ► Содержимое.
Вы можете включить любое количество выходных формул при создании таблицы данных с одной переменной. Если входной диапазон является столбцом, введите вторую формулу непосредственно справа от первой, третью справа от второй и т.д. Для различных столбцов допускаются разные формулы, но все они должны использовать одни и те же входные ячейки.
Пример. Предположим, вы рассматриваете возможность покупки дома, для чего вам придется взять ссуду в 2000000 руб. на 30 лет, и вы хотите вычислять месячные выплаты по этой ссуде для нескольких процентных ставок. Эту информацию может предоставить таблица данных для одной переменной.
Чтобы создать такую таблицу, выполните описанные ниже действия.
1. В новом рабочем листе введите интересующие вас процентные ставки. Для этого примера введите 6%, 6,5%, 7%, 7,5%, 8%, 8,5% в ячейки В3:В8. Этот диапазон называется входным диапазоном, так как он содержит входные значения, которые мы хотим проверить.
2. Введите величину ссуды в ячейку вне таблицы данных. Например, введите 2000000 в ячейку С1. Это позволит нам легко изменять величину ссуды при проверке различных сценариев.
3. Затем введите формулу, в которой используется входная переменная. В данном случае введите в ячейку С2 формулу (см. рис. 15) =ППЛАТ (А2/12;360;С1)
Здесь А2/12 – месячная процентная ставка, 360 – срок ссуды в месяцах, С1 – ссуда. Обратите внимание, что эта формула ссылается на ячейку А2, которая в данный момент пустая. Поскольку ячейка А2 пустая, то функция возвращает величину ежемесячных выплат, необходимую для погашения ссуды при нулевой процентной ставке. Ячейка А2 является только меткой, через которую Excel будет подставлять значения из входного диапазона. На самом деле Excel не изменяет значение, хранящееся в этой ячейке, поэтому такой меткой может быть любая ячейка рабочего листа вне диапазона таблицы данных.
Рис. 15
4. Выделите диапазон таблицы данных – минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона. В данном случае выделите диапазон В2:С8.
5. В меню Данные выберите команду Таблица подстановки.
6. В окне диалога Таблица подстановки (рис. 16) задайте местонахождение входной ячейки. Входная ячейка – это ячейка-метка, на которую ссылается формула таблицы данных, в данном случае А2. Чтобы таблица данных заполнялась правильно, вы должны ввести ссылку на входную ячейку в нужное поле. Если входные значения расположены в строке, введите ссылку на входную ячейку в поле Подставлять значения по столбцам в. Если значения во входном диапазоне расположены в столбце, используйте поле Подставлять значения по строкам в. В данном примере входные значения расположены в столбце, поэтому введите $А$2 в поле Подставлять значения по строкам в или щелкните на этом поле и затем – на ячейке А2.
Рис. 16
7. Нажмите кнопку ОК. Excel поместит результаты вычисления формулы для каждого входного значения в свободных ячейках диапазона таблицы данных. В нашем примере Excel поместит шесть результатов в диапазоне С3:С8, как показано ниже на рис. 17. Обратите внимание на формулу в строке формул.
Рис. 17
При создании этой таблицы Excel использует формулу массива {=ТАБЛИЦА(;A2)}
Эта формула вводится в каждую ячейку диапазона С3:С8, который называется диапазоном результатов. В нашей таблице эта формула вычисляет значения функции ППЛАТ для каждой процентной ставки, указанной в диапазоне В2:В8.
Функция ТАБЛИЦА, используемая в формуле, имеет следующий синтаксис: =ТАБЛИЦА(входная ячейка для строки; входная ячейка для столбца)
Поскольку в нашем примере входные значения расположены в столбце, Excel использует ссылку на входную ячейку А2 для столбца в качестве второго аргумента функции и оставляет первый аргумент пустым (на что указывает точка с запятой).
После построения таблицы можно изменить формулу таблицы данных или любые значения во входном диапазоне для создания другого множества результатов. Например, предположим, что для покупки дома вы решили занять только 1500000. Если вы измените величину ссуды в ячейке С1 на 1500000, значения в выходном диапазоне изменятся.
Таблица с двумя формулами
Пример. Предположим, что вы рассматриваете возможность покупки дома и для этого вам придется взять ссуду в размере 1800000 на 30 лет. Вы хотите узнать, какие будут месячные выплаты по этой ссуде для нескольких процентных ставок во входном диапазоне, и хотите сравнить их с выплатами для ссуды в 2000000. Вы можете расширить таблицу, созданную в предыдущем примере, включив в нее обе формулы.
Чтобы добавить новую формулу в существующую таблицу данных, выполните описанные ниже действия.
1. В ячейку справа от существующей формулы (в данном случае в ячейку D2) введите новую формулу. Для нашего примера введите =ППЛАТ (А2/12;360;D1) Обратите внимание, что, как и первая формула, эта формула ссылается на ту же самую ячейку А2.
2. Введите 1800000 в ячейку D2.
3. Выделите диапазон таблицы, в данном случае В2:D8.
4. В меню Данные выберите команду Таблица подстановки, введите ссылку на входную ячейку $А$2 в поле Подставлять значения по строкам в и нажмите кнопку ОК. Результат представлен на рис. 18.
