1 Таблицы подстановки
Оценить влияние на некоторую величину нескольких параметров можно с помощью таблиц подстановки. Если вы работаете с формулами и хотели бы знать, какие результаты можно получить в случае, если одно или два используемых в них значения будут изменены в определенном диапазоне, то лучше всего использовать таблицу подстановки.
Таблица подстановки Excel создается на основе ячейки с формулой, содержащей ссылку на ячейку, определенную как поле ввода (ячейка ввода), и списка исходных значений (они последовательно подставляются в ячейку ввода с целью создания списка результатов). Существует две разновидности таблиц подстановки, а именно с одной переменной и с двумя переменными. В первом случае можно изменить значение одной ячейки в формуле, во втором — двух.
Таблицы подстановки с одной переменной
Таблица подстановки с одной переменной используется для вычисления результатов, которые можно поместить в одну строку или столбец, скажем, для определения размеров выплат по процентам при различных значениях процентных ставок (исходные значения). Она представляет собой таблицу со списком исходных значений в первой строке (или столбце). Эти значения последовательно подставляются в формулу, а результаты помещаются в следующую строку (или столбец). Одни и те же исходные значения могут быть подставлены в несколько формул. В таком случае каждый список результатов отображается в соответствующей строке (или столбце).
Таблица подстановки с одной переменной может быть ориентирована по столбцу или по строке (рис. 4). В обоих случаях в ячейку, расположенную слева от строки или выше столбца, где содержатся исходные значения, ничего не вводится. Первая формула в следующей после пустой ячейке служит основой для формирования первого столбца или первой строки результата. Формула обязательно должна содержать ссылку на ячейку ввода. В качестве ячейки ввода может выступать любая ячейка рабочего листа.
Рисунок 4 — Таблицы подстановки с одной переменной
Таблица подстановки с одной переменной формируется на основе трех компонентов: ячейки ввода, одной или нескольких формул, которые содержат ссылку на ячейку ввода, и списка исходных значений. При создании таблицы подстановки данные из списка исходных значений последовательно переносятся в ячейку ввода. Excel производит вычисления по указанным формулам и заносит результаты в таблицу подстановки. Исходные значения и формула должны находиться в соседних ячейках, как показано на рисунках выше. Перед вызовом команды создания таблицы подстановки эти ячейки необходимо выделить.
На примере таблицы для расчета пенсионных платежей показано, как применяется таблица подстановки с одной переменной, показано на примере таблицы для расчета пенсионных платежей. Предположим, нам необходимо определить, как будут меняться накапливаемая сумма и ежемесячная прибавка к пенсии для различных периодов накопления (от 15 до 30 лет). Для этого зададим в вертикальном столбце список значений подстановки (это удобно делать с помощью маркера заполнения), а в строке, находящейся на позицию выше этого списка, — необходимые формулы так, как это показано на рис. 5.
Рисунок 5 – Таблица, подготовленная для вызова команды подстановки
В таблице подстановки используются две формулы. Обратите внимание на формулу в ячейке Е5: именно она содержит ссылку на ячейку С2, которая является ячейкой ввода. Значение в ячейке F5 рассчитывается на основе данных ячейки Е5.
Выделите диапазон, охватывающий исходные значения и формулы, и вызовите команду Данные/Таблица подстановки. После этого появится диалоговое окно Таблица подстановки, в котором нужно задать ссылку на ячейку ввода (рис. 6). Поскольку исходные данные расположены в столбце, ссылку нужно задать в поле Подставлять значения по строкам в .
Рисунок 6 – Окно Таблица подстановки
Рисунок 7 – Созданная таблица подстановки с одной переменной
Сохраните результат созданной таблицы подстановки с одной переменной.
Внимательно изучив полученный результат, вы поймете, что для построения таблицы подстановки использовалась формула
В качестве аргумента здесь выступает ячейка С2, в которую подставляются значения из списка. Вычисленный результат отображается в таблице подстановки.
Формирование данных с помощью таблиц подстановки.
Таблицей подстановки данных называется диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Для построения таблицы подстановки необходимы:
- одна или две ячейки ввода, в которые будут подставляться исходные значения и на которые ссылается формула подстановки;
- один или два списка значений, которые будет подставляться в ячейки ввода (исходные данные могут располагаться либо в столбце, либо в строке, либо в столбце и строке);
- формула подстановки, которая ссылается на ячейки ввода (для таблиц подстановки с одной переменной формул может быть несколько);
В результате подстановки списка значений в ячейку ввода получается массив значений.
Таблицы подстановки обеспечивают быстрый доступ к выполнению одной операции разными способами. С помощью таблиц подстановки удобно формировать источник данных для построения графиков функций с одной или двумя переменными.
Чтобы создать таблицу подстановки, выделите диапазон, включающий:
- списки значений;
- ячейки, содержащие формулы подстановки;
- диапазон, в который будет помещен результат.
П осле этого воспользуйтесь командой Table (Таблица подстановки) меню Data (Данные). В появившемся диалоговом окне следует указать ячейку ввода в поле Row input cell: (Подставлять значения по столбцам в), если список значений находится в строке или в поле Column input cell: (Подставлять значения по строкам в) в противном случае. Для таблицы подстановки с двумя переменными следует указать обе ячейки ввода.
При построении таблицы подстановки с одной переменной можно указать несколько формул, но все они должны ссылаться на одну ячейку ввода. Таблица подстановки с двумя переменными использует всегда только одну формулу, оперирующую двумя наборами значений.
Изменяя формулы подстановки или данные в списке значений, Вы увидите как это влияет на возвращаемый результат. Изменение или удаление отдельного элемента из массива значений невозможно, т.к. рассчитанные значения содержатся в диапазоне ячеек, использующих одну и ту же формулу. Необходимо удалить все значения, предварительно выделив их.
Таблицы подстановки с одной переменной.
Для таблицы подстановки с одной переменной требутся список значений, расположенный в отдельной строке или отдельном столбце. Формул подстановки может быть несколько, но ссылаться они должны на одну и ту же ячейку ввода. Построим источник данных для графика функции y = x 2 , -5
- В ячейку E1 введем формулу =D1*D1, которая ссылается на ячейку ввода D1.
- В столбец левее и ниже формулы введем значения подстановки для переменной X.
- Выделим диапазон ячеек, содержащий формулу и набор данных подстановки (D1:E12).
- Активизируем диалоговое окно Table (Таблица подстановки) меню Data (Данные).
- В поле Column input cell: (Подставлять значения по строкам в) введем ссылку на ячейку ввода для значений подстановки в столбце (D1).
В результате подстановки будет заполнен массив значений. Выделив диапазон, включающий список значений и массив значений и выбрав тип диаграммы XY Scatter (Точечный) легко можно построить требуемый график.
Таблицы подстановки с двумя переменными.
Чтобы построить таблицу подстановки с двумя переменными, следует ввести два списка значений: в отдельный столбец и отдельную строку. Необходимы также две ячейки ввода, располагающихся вне таблицы данных. Рассчитаем массив значений для построения поверхности z = x 2 — y 2 , -5 :
- В ячейку A1 введем формулу =A13*A13 — A14*A14, которая ссылается на две ячейки ввода A13 и A14.
- В тот же столбец ниже формулы введем значения подстановки для первой переменной (значения аргумента X). Значения подстановки для второй переменной (Y) введем в строку правее формулы.
- Выделим диапазон ячеек, содержащий формулу и оба набора данных подстановки (A1:L12). Ячейки ввода в диапазон входить не должны.
- Активизируем диалоговое окно Table (Таблица подстановки) меню Data (Данные).
- В поле Row input cell: (Подставлять значения по столбцам в) введем ссылку на ячейку ввода для значений подстановки в строке (A14).
- В поле Column input cell: (Подставлять значения по строкам в) введем ссылку на ячейку ввода для значений подстановки в столбце (A13).
В результате подстановки будет заполнен массив значений, выделив который, легко можно построить требуемую поверхность.
Диаграммы в Microsoft Excel.
Вопросы для проверки.
- Что такое ось категорий, ось значений, ось рядов в диаграммах Microsoft Excel?
- Какой инструмент Microsoft Excel служит для построения диаграмм? Как его вызвать?
- Сколько шагов необходимо для построения диаграммы? Какие операции выполняются на каждом шаге?
- Какие типы диаграмм отображают изменения показателей через регулярные промежутки времени или по категориям? Через нерегулярные интервалы?
- Вы хотите построить график неоднозначной функции. Какой тип диаграммы Вы выберете?
- На каком шаге построения диаграммы Вы можете удалить ненужные ряды данных или добавить недостающие?
- Какие существуют возможности размещения диаграммы?
- Существует ли панель инструментов, предназначенная для работы с диаграммами? Как она называется? Как ее вызвать?
- Предположим, Вы создали диаграмму, а меню Chart (Диаграмма) отсутствует. Почему?
- Как можно выделить элемент диаграммы? Как его отформатировать?
- Можно ли изменить ориентацию объемной диаграммы? Если да, то как?
- Как можно добавить данные в диаграмму?
- Что такое таблица подстановки?
- Какие объекты необходимы для того, чтобы создать таблицу подстановки с одной переменной? С двумя переменными?
- Какие элементы можно менять в таблице подстановки? Какие нельзя?
Диаграммы в Microsoft Excel.
Практическое задание.
- С помощью таблицы подстановки с одной переменной создайте источник данных для графика функции, заданной параметрически: x = Cos(t), y = Sin(t), 0 .
- Постройте график, используя результаты предыдущего упражнения.
- С помощью таблицы подстановки с двумя переменными создайте таблицу данных для построения поверхности z = x4— y4, -5 .
- Постройте поверхность, используя результаты предыдущего упражнения.
- Замените формулу подстановки на z = Cos(x) + Cos(y).
- Определите новый диапазон данных для поверхности: -2 .
Назначение таблиц подстановки
Таблицы подстановки — это диапазон смежных ячеек, в которых показаны результаты вычисления определенных формул при изменении значений, влияющих на эти формулы. Таблицы подстановки предлагают удобный способ с помощью одного оператора вычислить, отобразить и сравнить несколько результатов вычисления определенных формул.
Например, на рис. 2.1 показана таблица соответствия между температурными шкалами Фаренгейта и Цельсия. В этой таблице подстановки в диапазоне АЗ:А71 содержатся числа от 32 до 100, соответствующие градусам по Фаренгейту. В диапазоне ВЗ:В71 находятся соответствующие им значения градусов по Цельсию (в интервале от 0 до 37,8 градуса). Из этой таблицы вы можете узнать, что, например, 96 градусов по Фаренгейту (это значение записано в ячейке А67) соответствует 35,6 градуса по Цельсию (ячейка В67).
Вам нет необходимости вводить вручную значения в диапазон ВЗ:В71. Для создания таблицы, показанной на рис. 2.1, надо иметь только значения в диапазоне АЗ:А71 и формулу в ячейке В2 (в данном случае имеем формулу =ПРЕОБР(B1;»F»;»C»)). При создании таблицы подстановки Excel автоматически вычислит значения в диапазоне ВЗ:В71.
Рис. 2.1. Таблица соответствия температурных шкал Фаренгейта и Цельсия (лист разбит на две области для удобства чтения таблицы)
Другой пример показан на рис. 2.2. Это таблица умножения, содержащая результаты попарного умножения чисел от 1 до 15. В диапазоне А4:А18 содержатся числа от 1 до 15, такие же числа записаны в диапазон ВЗ:РЗ. В таблице на пересечении строк и столбцов, соответствующих определенным числам из этих диапазонов, стоит значение произведения данных чисел. Например, в ячейке J10 содержится число 63, равное произведению чисел 7 (записано в ячейке А10) и 9 (записано в ячейке J3).
Рис. 2.2. Таблица умножения
Когда применяются таблицы подстановки
Таблицы подстановки применяются тогда, когда нужен удобный способ отобразить в виде таблицы результаты вычисления одной или нескольких формул для различных входных значений, от которых зависят эти формулы.
Например, вы хотите отобразить в виде таблицы подстановки список оптовых цен и список розничных цен, которые получаются из оптовых цен путем добавления к ним розничной надбавки. Такая таблица показана на рис. 2.3. Здесь в диапазоне АЗ:А102 записаны значения оптовых цен от 10 руб. до 1000 руб. с шагом 10 руб., в диапазоне ВЗ:В102 — соответствующие значения розничных цен при условии, что розничная надбавка составляет 8,8%. Из этой таблицы видно, что, например, оптовой цене 970 руб. соответствует розничная цена в размере 1 055 руб.
Рис. 2.3. Таблица подстановки, показывающая соответствие оптовых и розничных цен
Продолжая предыдущий пример, предположим, что для оптовой цены возможны скидки. Теперь вы хотите на основе оптовых цен создать таблицу розничных цен, где бы учитывалась розничная надбавка 8,8% и возможные скидки на оптовые цены. Такая таблица показана на рис. 2.4. Здесь в диапазоне ВЗ:В103 записаны значения оптовых цен от 10 до 1000 руб. с шагом 10 руб., а в диапазоне C3:V3 — значения скидки от 0 до 95% с шагом 5%. Теперь, чтобы определить розничную цену, если, допустим, оптовая цена равна 970 руб., а скидка равна 15%, достаточно посмотреть на значение ячейки F100, которая располагается на пересечении строки, соответствующей значению 970 руб., и столбца, соответствующего значению 15%, — в этой ячейке содержится число 897, такова розничная цена при данных условиях.
Рис.2.4. Таблица розничных цен с учетом оптовой скидки и розничной надбавки.
Финансы в Excel
Главная 
Статьи Формулы Таблицы подстановки
Таблицы подстановки
| tables2.xls | [Таблицы подстановки] | 42 kB |
Microsoft Excel включает в свой состав несколько интересных средств для анализа данных. Данная статья описывает возможности одного из таких интерфейсных решений для проведения вычислений при помощи «таблицы подстановки» (в последних версиях Excel называется «таблица данных»).
Основным практическим применением таблицы подстановки является создание матриц факторного анализа показателей (анализа чувствительности) экономических моделей на изменение входящих параметров.
В файле-примере к данной статье построена микро-модель условного инвестиционного проекта. Анализ чувствительности проводится сначала для одного параметра — ставки дисконтирования. Во втором случае подставляется два параметра: ставка дисконтирования и сумма первоначальных инвестиций. Задача является типичной для проведения экономического анализа чувствительности. В условном примере вычисляния достаточно простые, поэтому вместо таблицы подстановки можно использовать стандартные формулы. В реальных экономических моделях вычисления гораздо сложнее, и реализовать матрицу чувствительности через формулы не удастся.
Для получения набора результатов функции с одним параметром необходимо сформировать 2 столбца (либо строки): ячейки параметров ячейки результатов. Ссылка на ячейку для получения базового результата анализа должна располагаться в верхнем правом углу диапазона. Там, кстати, может быть и более сложная формула, а не просто ссылка на ячейку.
Затем следует выделить область таблицы, включая ячейку с формулой (в примере B10:C14), и вызвать диалог формирования таблицы подстановки. В Excel2007-2013 — через Данные \ Работа с данными \ Анализ «что-если» \ Таблица данных, в Excel 97-2003 через меню Data \ Table. В диалоге необходимо указать ячейку, в которую следует подставлять указанные в таблице параметры. В примере варианты ставки дисконтирования располагаются по строкам, поэтому заполняем поле диалога «Подставлять значения по СТРОКАМ в:». Указываем ссылку на ячейку с рабочей ставкой дисконтирования, которая применяется в основных расчетах — $B$4.
После закрытия окна будут заполнены значения NPV для разных ставок дисконтирования.
Похожие действия необходимо произвести в случае двухмерной таблицы подстановки (матрицы). В диалоговом окне, кроме ссылки на параметр в строках требуется заполнить поле «Подставлять значения по СТОЛБЦАМ в:». Там указываем ссылку на рабочую ячейку с начальными инвестициями — $B$3. В отличие от вектора при использовании матрицы ссылка на результат должна располагаться в верхнем левом углу таблицы.
Как же это работает? В момент проведения расчетов Excel последовательно подставляет значения из строк и/или столбцов заголовка матрицы к заданным в диалоге ячейкам. После завершения каждого цикла вычислений результат записывается в соответсвующую ячейку таблицы подстановки. Затем происходит переход к следующему параметру, и вычисления повторяются. И так до тех пор, пока не будут перебраны все варианты параметров.
Очевидно, что при работе с большими таблицами подстановки вычисления, производимые в цикле, будут существенно замедлять работу с файлами. Чтобы этого не происходило, в Excel имеется специальный режим расчетов «Автоматически, кроме таблиц». С данной установкой при любом изменении формул, таблицы подстановки обновляться не будет до тех пор, пока пересчет не запущен принудительно (например, по нажатию F9).
Считается, что таблицы подстановки — устаревшее интерфейсное средство, сохраняемое в Excel только в целях совместимости с предыдущими версиями. В частности, можно реализовать циклические вычисления при помощи макросов. Но, например, для проведения факторного анализа показателей в сложных экономических моделях вряд ли найдется способ проще.
Применение таблиц подстановки оправдано только в случае сложных расчетов. Если в конечном итоге требуется рассчитать одно- или двухпараметрическую формулу, то проще ее скопировать на всю матрицу результатов, используя абсолютные и относительные ссылки. Иными словами, если имеется возможность в эту ячейку ввести расчетную формулу целиком, то вероятнее всего для расчетной модели вообще не следует использовать таблицу подстановки. В нижней части файла-примера показано как таблица подстановки может быть заменена стандартной формулой.