2. Таблица данных
Подставляя в исходную таблицу разные значения, можно проследить зависимость результата вычислений от переменных, составляющих формулу целевой ячейки. Однако так мы получаем только один вариант решения. Чтобы получить максимум информации, можно воспользоваться для анализа инструментом Excel Таблица подстановки.
Таблицу подстановки можно создавать с одной или двумя переменными. Значения переменных задаются в виде списков. При создании таблицы подстановки список исходных значений задается в виде строки или в виде столбца таблицы. Это упражнение должно помочь научиться создавать оба типа таблиц подстановки. Для этого воспользуемся примером из предыдущего упражнения. Таблица подстановки с одной переменной (одной ячейкой ввода) позволит нам рассчитать значения коэффициента наращивания и суммы выплат для разных сроков вкладов.
В качестве базы возьмем таблицу расчета сложных процентов по вкладу из предыдущего упражнения. Применяемые формулы остаются неизменными.
1. На свободном месте листа создайте список значений подстановки для переменной одной или нескольких формул. Это можно сделать в отдельном столбце или строке. В нашем случае значения подстановки - различные сроки вклада, представленные в диапазоне Е2:Е25. Значения подстановки будут поочередно копироваться в ячейку ввода для вычисления.
2. Формулу, в которую нужно подставлять значения, введите в качестве заголовка правого столбца или в его верхнюю строку если расчет будет начинаться именно со значения подстановки, представленного в таблице исходных данных, как в примере (рис. 4). Причем эта формула должна буква в букву копировать соответствующую формулу из таблицы с исходными данными. Так, формула в ячейке F2 нашего примера должна бать полностью идентична формуле в ячейке С5: =(1 +СЗ)^C4.
Рис. 4. Создание таблицы данных с одной ячейкой ввода
3. При необходимости введите в следующей (справа) ячейке этой же строки дополнительную формулу. Формула в ячейке G2 нашего примера должна быть полностью идентична формуле в ячейке С7: =С2*С5. Точно так же можно создать столбцы для расчета еще нескольких формул.
Примечание: Обратите внимание на обязательное наличие в формулах прямых или косвенных ссылок на ячейку, определенную в качестве ячейки ввода (в нашем случае ячейку С4). Формулы для вычисления обязательно должны быть представлены в верхней строке (или в левом крайнем столбце), поскольку значения в списке могут быть считаны только в направлении возрастания номеров строк (столбцов).
4. Выделите диапазон, содержащий значения подстановки и формулы.
5. Выберите команду Данные / Анализ "Что-если" / Таблица данных, чтобы вывести на экран диалоговое окно Таблица данных.
6. Поскольку значения подстановки расположены в столбце (будьте внимательны!), поместите курсор в поле Подставлять значения по строкам в (имеются в виду строки столбца значений подстановки) и укажите ячейку ввода (С4). Если значения подстановки расположены в строке (!), укажите соответствующую ячейку ввода в поле Подставлять значения по столбцам в. Щелкните на кнопке ОК, чтобы запустить процесс создания таблицы подстановки. Результат — составленная таблица подстановки с одной переменной (рис. 5)
Рис. 5. Таблицы подстановки с одной ячейкой ввода
Примечание: Результатом таблицы подстановки является массив, который в нашем случае описывается формулой ={TABLE(;C4)} (обратите внимание, формула массива заключена в фигурные скобки). Этот массив можно обрабатывать только как единое целое. Изменить отдельные ячейки нельзя - Excel всякий раз аккуратно об этом извещает. Чтобы дополнить уже созданную таблицу подстановки, введите дополнительные значения в соответствующие ячейки, выделите диапазон, содержащий формулы и значения для подстановки, и снова выберите команду Данные ► Таблица подстановки.
Взглянув на получившуюся таблицу подстановки, мы с уверенностью можем сказать, что, ограничившись четвертью миллиона, можно достичь цели менее чем за два года. С другой стороны, и три года не дадут вплотную приблизиться к миллиону.