В состав инструментов "Анализ что-если" входит так называемая "Таблица данных". Она позволяет провести анализ влияния одной или двух переменных на результаты вычислений.
Пример 1. Таблица данных с одной переменной
Как разные процентные ставки влияют на ежемесячные выплаты по ссуде, вычисляемые с использованием функции ПЛТ?
Таблица данных - это диапазон ячеек, в которых вы можете изменять значения в некоторых из некоторых ячеек и придумывать разные ответы на проблему. Эксперименты с изменяемыми значениями для получения разных результатов являются частью дисциплины, известной как анализ данных.
Когда вы используете таблицы данных, вы делаете анализ «что-если». Например, вы можете использовать таблицу данных для изменения процентной ставки и продолжительности срока, которые используются в кредите для определения возможных ежемесячных платежей. Сценарии и таблицы данных принимают наборы входных значений и определяют возможные результаты. Подобно сценариям, таблицы данных помогают вам изучить набор возможных результатов. В отличие от сценариев, таблицы данных показывают все результаты в одной таблице на одном листе.
Решение
Создайте таблицу с исходными данными для расчетов по образцу. Дайте ячейкам В1, В2 и В3 имена Сумма, Ставка и Срок.
Для анализа влияния процентной ставки на ежемесячные выплаты по ссуде на этом же листе в ячейках А5:В8 создайте заготовку таблицы данных по образцу. В ячейке В5 расположена формула для вычисления ежемесячных платежей по ссуде. В столбце А6:А8 введены значения интересующих нас процентных ставок.
Поиск решения Excel
Использование таблиц данных позволяет легко изучить ряд возможностей с первого взгляда. Поскольку вы сосредоточены только на одной или двух переменных, результаты легко читаются и публикуются в табличной форме. В таблице данных не может вместить более двух переменных. Если вы хотите проанализировать более двух переменных, вместо этого вы должны использовать сценарии. Хотя он ограничен только одной или двумя переменными, таблица данных может включать в себя столько разных значений переменных, сколько вам нужно.
Что нужно сделать дальше?
Сценарий может содержать не более 32 различных значений, но вы можете создать столько сценариев, сколько хотите. Вы можете создавать таблицы переменных с одной или двумя переменными, в зависимости от количества переменных и формул, которые вы хотите протестировать.
Выделите таблицу данных (диапазон А5:В8) и выполните команду Данные/Анализ Что-если/Таблица данных. Появится окно "Таблица данных".
В ячейках В6:В8 появятся результаты расчетов.
Пример 2. Таблица данных с двумя переменными
Для анализа влияния процентной ставки и срока ссуды (количества месяцев) на ежемесячные выплаты по ссуде на этом же листе в ячейках А11:D14 создайте заготовку таблицы данных по образцу. В ячейке A11 расположена формула для вычисления ежемесячных платежей по ссуде. В столбце А12:А14 введены значения интересующих нас процентных ставок, а в строке В11:D11 введены значения количества месяцев .
Таблицы данных с одной переменной Используйте таблицу данных с одной переменной, если вы хотите увидеть, как разные значения одной переменной в одной или нескольких формулах изменят результаты этих формул. Вы вводите значения переменных в один столбец или строку, а результаты отображаются в соседнем столбце или строке.
Таблицы с двумя переменными Используйте таблицу данных с двумя переменными, чтобы увидеть, как различные значения двух переменных в одной формуле изменят результаты этой формулы. Например, вы можете использовать таблицу данных с двумя переменными, чтобы увидеть, как различные комбинации процентных ставок и условий кредита будут влиять на ежемесячный платеж по ипотеке.
Выделите таблицу данных (диапазон А11:D14) и выполните команду Данные/Анализ Что-если/Таблица данных. Появится окно "Таблица данных".
Подставлять значения по столбцам в ячейку B3. Это означает, что значения количества месяцев из ячеек В11:D11 будут подставляться в ячейку Срок (В3). ОК.
Подставлять значения по строкам в ячейку B2. Это означает, что значения процентных ставок из ячеек А6:А8 будут подставляться в ячейку Ставка (В2). ОК.
Вычисление таблиц данных Таблицы данных пересчитываются всякий раз, когда рабочий лист пересчитывается, даже если они не изменились. Чтобы ускорить расчет рабочего листа, который содержит таблицу данных, вы можете изменить параметры расчета, чтобы автоматически пересчитывать рабочий лист, но не таблицы данных.
Создайте таблицу данных с одной переменной
Таблица данных с одной переменной имеет входные значения, которые перечислены либо по столбцу, либо по строке. Формулы, которые используются в таблице данных с одной переменной, должны относиться только к одной входной ячейке. Введите список значений, которые вы хотите заменить во входной ячейке, либо по одному столбцу, либо по одной строке. Выполните одно из следующих действий: Если таблица данных ориентирована на столбцы, введите формулу в ячейке одну строку выше и одну ячейку справа от столбца значений. Если вы хотите изучить эффекты различных значений в других формулах, введите дополнительные формулы в ячейках справа от первая формула. Если таблица данных ориентирована на строку, введите формулу в ячейке один столбец слева от первого значения и одну ячейку ниже строки значений. Если вы хотите изучить эффекты различных значений в других формулах, введите дополнительные формулы в ячейках ниже первой формулы. Оставьте несколько пустых строк и столбцов с обеих сторон значений. . Выберите диапазон ячеек, который содержит формулы и значения, которые вы хотите заменить.
В ячейках В12:D14 появятся результаты расчетов.
Пример 3. Таблица данных с одной переменной и несколькими формулами
В таблицу данных с одной переменной могут быть добавлены несколько формул, но эти формулы должны ссылаться на одну и ту же ячейку исходных данных (в нашем случае это ячейка Ставка). Создайте таблицу по образцу и рассчитайте таблицу данных для двух формул самостоятельно.
Добавить формулу в таблицу данных с одной переменной
- Примечание.
- После создания таблицы данных вы можете изменить формат ячеек результатов.
- На иллюстрации ячейки результатов форматируются как валюта.
Если таблица данных ориентирована на столбцы, введите новую формулу в пустой ячейке справа от существующей формулы в верхней строке таблицы данных. Если таблица данных ориентирована на строку, введите новую формулу в пустой ячейке ниже существующей формулы в первом столбце таблицы данных. Выполните одно из следующих действий. . Выберите диапазон ячеек, который содержит таблицу данных и новую формулу.
Таблица данных представляет собой диапазон ячеек, значения которых можно изменять для получения различных решений проблемы. Например, можно использовать функцию ПЛТ с разными суммами кредитов и процентными ставками для расчета возможного кредита на жилье или автомобиль. Эксперименты с изменяемыми значениями с целью получения разных результатов - часть дисциплины, известной как анализ данных .
Создайте таблицу данных с двумя переменными
Если таблица данных ориентирована на столбцы, введите ссылку ячейки для входной ячейки в поле ячейки ввода столбца. Если таблица данных ориентирована на строку, введите ссылку ячейки для ячейки ввода в поле Ячейка ввода строки. В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна относиться к двум различным входным ячейкам.
Выберите диапазон ячеек, который содержит формулу, как строку, так и столбец значений, и ячейки, в которых вы хотите вычисленные значения. В поле Ячейка ввода столбца введите ссылку на входную ячейку для входных значений в столбце.
- В ячейке на листе введите формулу, которая ссылается на две входные ячейки.
- Введите один список входных значений в том же столбце, под формулой.
- Введите второй список в той же строке, что и формула, справа.
Таблицы данных являются частью набора команд, которые называются средствами анализа «что если». При использовании таблиц данных выполняется анализ «что если».
Анализ «что если» предполагает изменение значений ячеек для выяснения того, как эти изменения повлияют на результаты выполнения формул на листе. Например, таблицу данных можно использовать для варьирования процентной ставки и длительности срока, которые используются в условиях займов, для определения возможных сумм ежемесячного платежа.
Ускорить вычисление на листе, который содержит таблицы данных
Когда вы выбираете этот параметр вычисления, таблицы данных пропускаются, когда остальная часть книги пересчитывается. Если вы знаете результат, который вы хотите получить из формулы, но не знаете, какое входное значение формула должна получить для этого результата, используйте функцию поиска цели.
Решатель работает с группой ячеек, которые используются при вычислении формул в объектных и связанных ячейках. Таблицы данных являются одним из наиболее полезных инструментов анализа чувствительности при финансовом моделировании. Они являются мощными и легкими в реализации. Большинство таблиц чувствительности построены с использованием одного или двух переменных входов.
Типы анализ "что если" Существует три типа инструменты анализа "что если" в Excel: сценарии, таблицы данных и подбор параметров. Сценарии и таблицы данных принимают наборы входных значений и определения возможных результатов. Подбор параметров отличается от сценарии и таблицы данных в том, что он принимает результата и определяет возможные входные значения, которые, результат будет выглядеть.
Другие типы анализа "что если"
Итак, как это может быть полезно при создании трех или даже четырех таблиц переменных данных? Таким образом, одна переменная может быть «расколота» на две или более переменных. Потенциально, можно удвоить или даже увеличить число переменных, которые должны быть сенсибилизированы в таблице данных.
Конечно, важно помнить о распространении данных и анализе информации. Общая настройка не слишком сильно отличается от двух таблиц переменных данных. Сначала настройте формулу расчета. Помните, что три таблицы переменных данных очень похожи на обычные таблицы данных, поэтому применяются те же действия и действия. Входные ячейки должны находиться на том же листе, что и таблица данных, если вы не создаете клон. Ячейка формулы может быть на другом листе, но она должна быть подана прямо или косвенно входными ячейками.
Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных все результаты отображаются в одной таблице на одном листе. Применение таблиц данных облегчает и ускоряет изучение возможных вариантов. Поскольку внимание сосредоточено только на одной или двух переменных, результаты легко воспринимаются; ими также просто обмениваться в табличной форме.
Одна и та же ячейка не может управлять входными переменными и ячейками ввода строки и столбца; если они это сделают, таблица данных не будет работать должным образом. Наконец, выделите всю область. Таблица данных - это диапазон, который оценивает изменение переменных в одной формуле. Вы можете быстро изучить возможности. Чтобы проиллюстрировать их использование, мы добавим один к простому калькулятору ипотеки, показанному ниже. Как и вы, вы можете изменить любое из входных значений, чтобы изменить результаты.
Мы не тратили время на построение калькулятора. Теперь давайте предположим, что вы хотите сравнить меняющиеся процентные ставки - это то, где входит таблица данных. Сначала введите метки ярлыков таблицы данных. Их следует легко получить из ячеек результатов на вашем исходном листе. Кроме того, первый столбец таблицы данных должен содержать вашу переменную. В этом случае это изменение процентной ставки. Если вы используете формулы в первом столбце таблицы данных, обязательно вставьте их со значениями - таблицы данных не могут использовать формулы в первом колонка.
Таблица данных не может принимать более двух переменных. Если необходимо проанализировать больше переменных, вместо таблиц данных лучше использовать сценарии. Несмотря на то что работа с таблицей данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам, а другая - по строкам), при этом можно использовать любое количество различных значений переменных. Сценарий поддерживает только 32 различных значения, но количество сценариев может быть любым.
Теперь вы готовы ввести ссылки таблицы данных следующим образом. Вы готовы создать таблицу данных, но есть последний шаг. Переменная входная ячейка - в этом случае - это значение процентной ставки - должно равняться первому значению в вашей таблице данных. Первая процентная ставка в таблице данных составляет 5%. Не пропустите этот шаг. Чтобы создать таблицу данных, выполните следующие действия.
Полученная таблица данных показывает, как изменяется ежемесячный платеж и другие значения при изменении процентной ставки. Объединив исходный калькулятор с таблицей данных, вы получите лучшее из обоих миров. Вы все равно можете использовать часть калькулятора для изменения всех входных значений, и таблица данных будет отображать изменяющиеся значения с фиксированным набором диапазонов процентов для каждого сценария ипотеки.
Дополнительные сведения о анализ "что если": Общие сведения для анализа "что если" .
Базовые сведения о таблицах данных
В зависимости от числа переменных и формул, которые нужно протестировать, можно создать таблицу данных с одной или двумя переменными.
Анализ «что-если» в Excel
Как только таблица данных находится на месте, вы не можете изменить любую из ссылочных ячеек. Вы не можете использовать ее в любом другом контексте. Например, вы можете построить простые таблицы с одной переменной, которые показывают, как изменение одного входного значения влияет на одну или несколько формул.
Работа с таблицами данных с одной переменной
И вы также можете создавать более сложные данные с двумя переменными, которые показывают, как изменение двух входных значений влияет на одну формулу. Таблицы данных с одной переменной позволяют экспериментировать с тем, как изменение одной входной переменной может повлиять на результат формулы или даже на несколько результатов формул. И анализ «что-если» рассматривает влияние одного изменяющегося входного значения, процентной ставки, на формулу или формулы.
Настройка таблицы с одним переменным
Чтобы настроить таблицу данных с одной переменной, вы упорядочиваете входные значения вдоль левого или верхнего края диапазона рабочих листов и формулы или формулы, которые вы хотите рассчитать по другому краю.Таблицы данных с одной переменной Если нужно отображать как различные значения одной переменной в одну или несколько формул будут изменяться результаты этих формул с помощью таблицы данных с одной переменной. Например, можно использовать таблицу данных с одной переменной для просмотра различных процентной ставки влияют на ежемесячный платеж по ипотеке с помощью функции ПЛТ . Ввод значений переменных в один столбец или строку, а результаты, отображаются в смежных столбцах или строках.
На приведенном ниже рисунке ячейку D2 содержит формулу вычисления платежа, =PMT(B3/12,B4,-B5) , который ссылается на ячейку ввода B3.
Таблицы данных с двумя переменными Такую таблицу целесообразно использовать для просмотра влияния различных значений двух переменных в одной формуле на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать для оценки влияния различных сочетаний процентной ставки и срока кредитования на размер ежемесячной выплаты по закладной.
На приведенном ниже рисунке ячейка C2 содержит формулу вычисления платежа, =PMT(B3/12,B4,-B5) , который будет использоваться две ячейки ввода B3 и B4.
Таблица данных вычислений Таблицы данных пересчитываются при пересчете листа, даже если они не были изменены. Ускорение вычислений для листов, содержащих таблицы данных, вы можете изменить параметры расчета для автоматического пересчета листа, но не таблицы данных. В разделе .
Создание таблицы данных с одной переменной
Входные значения для таблицы данных с одной переменной перечисляются в столбце (таблица данных с ориентацией по столбцам) или в строке (таблица данных с ориентацией по строкам). Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейка ввода.
Если таблица данных ориентирована по столбцам (переменной значения расположены в столбце), введите формулу в ячейке на одну строку выше и на одну ячейку вправо от столбца значений. Таблица данных с одной переменной, рисунке в разделе Обзор ориентирована по столбцам, а содержащейся в ячейке D2 формулу.
Если требуется проверить влияние различных значений в других формулах введите другие формулы в ячейках справа от первой формулы.Если таблица данных ориентирована по строкам (значения переменной содержатся в строке), введите формулу в ячейку на один столбец левее первого значения и на одну ячейку ниже строки значений.
Если необходимо изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.Если таблица данных ориентирована по столбцу, введите в поле ссылка на ячейку на ячейку ввода. В примере показано на рисунке первую ячейку ввода - B3.
Примечание: После создания таблицы данных формат ячеек результатов можно изменить. На рисунке ячейки результатов имеют денежный формат.
Введите список значений, которые нужно подставлять в ячейку ввода, в один столбец или в одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.
Выделите диапазон ячеек, содержащих формулы и значения, которые нужно заменить. На первом рисунке в разделе «Общие сведения» выше таким диапазоном является C2:D5.
На вкладке " данные " в группе Работа с данными или прогноза Анализ "что если" и нажмите кнопку Таблица данных .
Выполните одно из следующих действий:
Добавление формулы в таблицу данных с одной переменной
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Если таблица данных ориентирована по столбцам (значения переменной содержатся в столбце), введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
Если таблица данных ориентирована по строкам (значения переменной содержатся в строке), введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.
Если таблица данных ориентирована по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в .
Если таблица данных ориентирована по строкам, введите ссылку на ячейку ввода в поле Подставлять значения по столбцам в .
Выполните одно из действий, указанных ниже.
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
На вкладке " данные " в группе Работа с данными или прогноза (в Excel 2016) нажмите кнопку Анализ "что если" и нажмите кнопку Таблица данных .
Выполните одно из действий, указанных ниже.
Создание таблицы данных с двумя переменными
В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.
В ячейку листа введите формулу, которая ссылается на две ячейки ввода.
В приведенном ниже примере, введенные формулы начальные значения в ячейки B3, B4 и B5, введите формулы =PMT(B3/12,B4,-B5) в ячейке C2.
В том же столбце ниже формулы введите значения подстановки для первой переменной.
В этом случае вводите различные процентной ставки в ячейке C3, C4 и C5.
Введите второй список в той же строке с формулой справа от нее.
Введите в ячейке D2 и E2 ссуды (в месяцах).
Выделите диапазон ячеек с формулой (C2) строку и столбец значений (C3: C5 и D2:E2), а также ячейки, в которые нужно поместить вычисленные значения (D3:E5).
В этом случае выделите диапазон C2: E5.
На вкладке " данные " в группе Работа с данными или прогноза (в Excel 2016) нажмите кнопку Анализ "что если" и нажмите кнопку Таблица данных .
В поле введите ссылку на ячейку ввода для входных значений в строке.
Ячейку B4
введите в поле Подставлять значения по столбцам
.
В поле Подставлять значения по строкам
введите ссылку на ячейку ввода для входных значений в столбце.
В поле Подставлять значения по строкам
, введите B3
.
Нажмите кнопку ОК .
Пример Таблица данных с двумя переменными можно отобразить как различные сочетания процентная ставка и ссуды повлияет ежемесячный платеж по ипотеке. На приведенном ниже рисунке ячейка C2 содержит формулу вычисления платежа, =PMT(B3/12,B4,-B5) , который использует две ячейки ввода B3 и B4.
Ускорение вычислений для листов, содержащих таблицы данных
Примечание: Если выбран этот параметр вычисления, таблицы данных не пересчитываются при пересчете остальной части книги. Чтобы пересчитать таблицу данных вручную, выделите ее формулы и нажмите клавишу F9.
Что нужно сделать дальше?
Для определенных целей и в случае больших наборов переменных данных можно использовать несколько других средств Excel для выполнения анализа "что если".
подбор параметра
Поиск решения Excel
Надстройка "Поиск решения" Excel можно использовать для поиска оптимального значения на основе числа переменных. "Поиск решения" работает с группы ячеек (называемые переменные решения или просто переменной ячейки), которые используются при вычислении формулы в ячейках целевым и ограничения. Подбираются значения в ячейках переменной решения для удовлетворения ограничения на ограничение ячеек и получения нужного результата для целевой ячейки. Ознакомьтесь со статьей определить и решение задачи с помощью поиска решения .
Примечание: Отказ от ответственности относительно машинного перевода . Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.