1 Таблицы подстановки
Оценить влияние на некоторую величину нескольких параметров можно с помощью таблиц подстановки. Если вы работаете с формулами и хотели бы знать, какие результаты можно получить в случае, если одно или два используемых в них значения будут изменены в определенном диапазоне, то лучше всего использовать таблицу подстановки.
Таблица подстановки Excel создается на основе ячейки с формулой, содержащей ссылку на ячейку, определенную как поле ввода (ячейка ввода), и списка исходных значений (они последовательно подставляются в ячейку ввода с целью создания списка результатов). Существует две разновидности таблиц подстановки, а именно с одной переменной и с двумя переменными. В первом случае можно изменить значение одной ячейки в формуле, во втором — двух.
Таблицы подстановки с одной переменной
Таблица подстановки с одной переменной используется для вычисления результатов, которые можно поместить в одну строку или столбец, скажем, для определения размеров выплат по процентам при различных значениях процентных ставок (исходные значения). Она представляет собой таблицу со списком исходных значений в первой строке (или столбце). Эти значения последовательно подставляются в формулу, а результаты помещаются в следующую строку (или столбец). Одни и те же исходные значения могут быть подставлены в несколько формул. В таком случае каждый список результатов отображается в соответствующей строке (или столбце).
Таблица подстановки с одной переменной может быть ориентирована по столбцу или по строке (рис. 4). В обоих случаях в ячейку, расположенную слева от строки или выше столбца, где содержатся исходные значения, ничего не вводится. Первая формула в следующей после пустой ячейке служит основой для формирования первого столбца или первой строки результата. Формула обязательно должна содержать ссылку на ячейку ввода. В качестве ячейки ввода может выступать любая ячейка рабочего листа.
Рисунок 4 — Таблицы подстановки с одной переменной
Таблица подстановки с одной переменной формируется на основе трех компонентов: ячейки ввода, одной или нескольких формул, которые содержат ссылку на ячейку ввода, и списка исходных значений. При создании таблицы подстановки данные из списка исходных значений последовательно переносятся в ячейку ввода. Excel производит вычисления по указанным формулам и заносит результаты в таблицу подстановки. Исходные значения и формула должны находиться в соседних ячейках, как показано на рисунках выше. Перед вызовом команды создания таблицы подстановки эти ячейки необходимо выделить.
На примере таблицы для расчета пенсионных платежей показано, как применяется таблица подстановки с одной переменной, показано на примере таблицы для расчета пенсионных платежей. Предположим, нам необходимо определить, как будут меняться накапливаемая сумма и ежемесячная прибавка к пенсии для различных периодов накопления (от 15 до 30 лет). Для этого зададим в вертикальном столбце список значений подстановки (это удобно делать с помощью маркера заполнения), а в строке, находящейся на позицию выше этого списка, — необходимые формулы так, как это показано на рис. 5.
Рисунок 5 – Таблица, подготовленная для вызова команды подстановки
В таблице подстановки используются две формулы. Обратите внимание на формулу в ячейке Е5: именно она содержит ссылку на ячейку С2, которая является ячейкой ввода. Значение в ячейке F5 рассчитывается на основе данных ячейки Е5.
Выделите диапазон, охватывающий исходные значения и формулы, и вызовите команду Данные/Таблица подстановки. После этого появится диалоговое окно Таблица подстановки, в котором нужно задать ссылку на ячейку ввода (рис. 6). Поскольку исходные данные расположены в столбце, ссылку нужно задать в поле Подставлять значения по строкам в .
Рисунок 6 – Окно Таблица подстановки
Рисунок 7 – Созданная таблица подстановки с одной переменной
Сохраните результат созданной таблицы подстановки с одной переменной.
Внимательно изучив полученный результат, вы поймете, что для построения таблицы подстановки использовалась формула
В качестве аргумента здесь выступает ячейка С2, в которую подставляются значения из списка. Вычисленный результат отображается в таблице подстановки.
Вычисление нескольких результатов с помощью таблицы данных в Excel для Mac
Таблица данных – это диапазон ячеек, по которому видно, как изменение одной или двух переменных в формулах повлияет на результаты вычисления этих формул. Таблицы данных позволяют быстро вычислять несколько результатов за одну операцию, а также просматривать и сравнивать результаты всех вариантов формулы на одном листе.
Таблицы данных входят в набор команд, которые называются средствами анализа «что если». При использовании таблиц данных вы выполняете анализ «что если». Средства анализа «что если» изменяют значения в ячейках и показывают, как эти изменения повлияют на результаты формул на листе. Например, вы можете использовать таблицу данных для изменения процентной ставки и срока ссуды с целью определения возможных сумм ежемесячных платежей.
В Excel предлагаются средства анализа «что если» трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения. Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных все результаты представлены в одной таблице на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.
В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя таблица данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам, вторая — по строкам), она позволяет использовать множество разных значений переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.
Общие сведения о таблицах данных
Вы можете создавать таблицы данных с одной или двумя переменными в зависимости от числа переменных и формул, которые необходимо проверить. Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, таблицу данных с одной переменной можно использовать, чтобы узнать, как разные процентные ставки повлияют на размер ежемесячного платежа, вычисляемый с использованием функции ПЛТ. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.
Дополнительные сведения см. в статье Функция ПЛТ.
Ячейка D2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейку ввода B3.
Таблица данных с одной переменной
Список значений, которые Excel подставляет во входной ячейке B3.
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
Ячейка C2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.
Таблица данных с двумя переменными
входную ячейку столбца.
Список значений, которые Excel подставляет во входной ячейке строки B4.
входную ячейку строки.
Список значений, которые Excel подставляет во входной ячейке столбца B3.
Таблицы данных пересчитываются всякий раз при пересчете листа, даже если в них не были внесены изменения. Для ускорения пересчета листа, содержащего таблицу данных, можно изменить параметры вычислений так, чтобы автоматически пересчитывался лист, но не таблицы.
Создание таблицы данных с одной переменной
Входные значения для таблицы данных с одной переменной перечисляются в столбце (таблица данных с ориентацией по столбцам) или в строке (таблица данных с ориентацией по строкам). Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
- Введите в отдельном столбце или в отдельной строке список значений, которые нужно подставлять в ячейку ввода. Оставьте по обе стороны от значений несколько пустых строк и столбцов.
- Выполните одно из указанных ниже действий.
Ориентация таблицы данных
Необходимые действия
По столбцу (значения переменной находятся в столбце)
Введите формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее столбца значений.
На рисунке в разделе «Обзор» показана ориентированная по столбцу таблица данных с одной переменной, формула находится в ячейке D2.
Примечание: Если требуется исследовать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
По строке (значения переменной находятся в строке)
Введите формулу в ячейку, расположенную на один столбец левее первого значения и на одну ячейку ниже строки значений.
Примечание: Если требуется исследовать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
- Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На первом рисунке в разделе «Обзор» это диапазон C2:D5.
- В Excel 2016 для Mac: выберите пункты Данные >Анализ «что если» >Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
Ориентация таблицы данных
Необходимые действия
Введите ссылку на ячейку ввода в поле Подставлять значения по строкам. На первом рисунке ячейка ввода — это B3.
Введите ссылку на ячейку ввода в поле Подставлять значения по столбцам.
Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов имеют денежный формат.
Добавление формулы в таблицу данных с одной переменной
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
- Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
По столбцу (значения переменной находятся в столбце)
Введите новую формулу в пустую ячейку, расположенную в верхней строке таблицы справа от имеющейся формулы.
По строке (значения переменной находятся в строке)
Введите новую формулу в пустую ячейку, расположенную в первом столбце таблицы под имеющейся формулой.
- Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
- В Excel 2016 для Mac: выберите пункты Данные >Анализ «что если» >Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
Ориентация таблицы данных
Необходимые действия
Введите ссылку на ячейку ввода в поле Подставлять значения по строкам.
Введите ссылку на ячейку ввода в поле Подставлять значения по столбцам.
Где находится таблица подстановки в excel
На этом шаге мы рассмотрим создание таблиц подстановки.
При работе с моделью «что-если» в определенный момент времени можно использовать только один сценарий (только один набор данных). Но что если необходимо сравнить результаты нескольких сценариев? Вот несколько вариантов решения подобной проблемы:
- Отпечатать несколько копий рабочего листа с разными сценариями на каждом.
- Скопировать модель на другие листы и расположить их окна на экране так, чтобы были видны все сценарии.
- Вручную создать формулу, которая бы подытоживала значения из ключевых ячеек для каждого сценария.
- Использовать команду Excel Данные | Таблица подстановки для автоматического создания итоговой таблицы.
- Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом .
- Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами .
Создать таблицу подстановки очень просто, но на ее использование наложены некоторые ограничения. Самое главное ограничение — это то, что она может временно оперировать только с одной или двумя ячейками исходных данных. Другими словами, нельзя создать таблицу подстановки, которая бы использовала комбинацию трех или более ячеек с исходными данными.
Создание таблицы подстановки с одним входом
В таблице подстановки с одним входом приводятся результаты расчетов по одной или нескольким формулам при различных значениях одного входного параметра. На рисунке 1 показан общий макет таблицы подстановки с одним входом.
Рис.1. Общий макет таблицы подстановки
Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами, по которым рассчитывается результат. Можно использовать любое количество ссылок на формулы (или только одну). Верхняя левая ячейка таблицы не используется. Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в соответствующий столбец (в ячейку, которая находится под ячейкой с соответствующей формулой или ссылкой на формулу).
В приведенном ниже примере используется рабочий лист, по которому рассчитывается ипотечная ссуда (рис. 2).
Рис.2. Пример рабочего листа
Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных , при изменении ставок от 7% до 9% с шагом 0,25%. На рисунке 3 показана заготовка таблицы подстановки для описанного примера. Строка 2 состоит из ссылок на соответствующие ячейки с формулами.
Рис.3. Подготовка к созданию таблицы подстановки с одним входом
Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11 ), а затем выберите команду Данные | Таблица подстановки . Появится диалоговое окно, показанное на рисунке 4.
Рис.4. Диалоговое окно Таблица подстановки
Вам необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7 ). Щелкните на кнопке OK , и Excel заполнит таблицу соответствующими результатами (рис. 5).
Рис.5. Результат анализа, проведенного с помощью таблицы подстановки с одним входом
Таблица подстановки с одним входом может быть организована вертикально или горизонтально. Если значения исходных данных, которые должны подставляться во входную ячейку, расположены в строке, введите ссылку на эту ячейку в поле Подставлять значения по столбцам в , которое находится в диалоговом окне Таблица подстановки .
Создание таблицы подстановки с двумя входами
Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров. Макет для этого типа таблицы показан на рисунке 6.
Рис.6. Макет таблицы подстановки с двумя входами
Хотя он выглядит практически так же, как и для таблицы подстановки с одним входом, он все же имеет одно существенное отличие: в данную таблицу можно свести результаты расчетов только по одной формуле. В верхней строке таблицы подстановки с одним входом можно разместить любое количество формул или ссылок на них. В таблице подстановки с двумя входами в верхней строке содержатся значения для подстановки второго входного параметра. И только в верхней левой ячейке находится ссылка на ячейку с единственной формулой.
Приведем пример таблицы подстановки с двумя входами. Это пример расчета эффективности проведения рекламной компании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи (рис. 7).
Рис.7. Пример расчета чистой прибыли после проведения рекламной акции
В этой модели используются две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов. Область результата включает следующее:
- Стоимость печатных материалов . Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 — если количество экземпляров не превышает 200000; 0,15 — от 200001 до 300000 экземпляров; 0,10 — если больше 300000. Стоимость отпечатаннх материалов (в зависимости от их количества) определяется по фомуле:
=ЕСЛИ(Разослано_материалов . - Почтовые расходы . Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.
- Число респондентов . Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:
=Процент_ответевших*Разослано_материалов . - Доход на одного респондента . Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.
- Суммарный доход . Суммарный доход вычисляется по простой формуле, в которой величина дохода, полученного от одного заказа, умножается на количество заказов:
=Доход_на_одного_респондента*Число_респондентов . - Суммарные расходы . По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:
=(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов . - Чистая прибыль . Определяется как разность суммарных доходов и суммарных расходов.
Создадим таблицу подстановки с двумя входами, которая позволит вычислить чистую прибыль при разных комбинациях количества разосланных рекламных материалов и предполагаемого процента полученных ответов. Расположите таблицу в диапазоне G4:O14 . Чтобы создать таблицу подстановки, выделите указанный диапазон и выполните команду Данные | Таблица подстановки . В поле Подставлять значения по столбцам в — введите имя ячейки Процент_ответивших , а в поле Подставлять значения по строкам в — имя ячейки Разослано_материалов . На рисунке 8 показан результат выполнения выше описанных действий.
Рис.8. Результат анализа, проведенного с помощью таблицы подстановки с двумя входами
По данным таблицы подстановки с двумя входами можно построить трехмерные диаграммы (рис. 9).
Рис.9. Пример трехмерной диаграммы
Файл с данным примером можно взять здесь.
На следующем шаге мы рассмотрим анализ данных с помощью средства Диспетчер сценариев .
Финансы в 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 только в целях совместимости с предыдущими версиями. В частности, можно реализовать циклические вычисления при помощи макросов. Но, например, для проведения факторного анализа показателей в сложных экономических моделях вряд ли найдется способ проще.
Применение таблиц подстановки оправдано только в случае сложных расчетов. Если в конечном итоге требуется рассчитать одно- или двухпараметрическую формулу, то проще ее скопировать на всю матрицу результатов, используя абсолютные и относительные ссылки. Иными словами, если имеется возможность в эту ячейку ввести расчетную формулу целиком, то вероятнее всего для расчетной модели вообще не следует использовать таблицу подстановки. В нижней части файла-примера показано как таблица подстановки может быть заменена стандартной формулой.