Таблица данных — это диапазон ячеек, в котором можно изменить значения в некоторых ячейках и придумать различные ответы на проблему. Хороший пример таблицы данных использует функцию PMT с разными суммами кредитов и процентными ставками для расчета доступной суммы по ипотечному кредиту дома. Экспериментирование с разными значениями для наблюдения за соответствующими различиями в результатах является распространенной задачей при анализе данных.
В Microsoft Excel таблицы данных являются частью набора команд, известных как средства анализа What-If. При создании и анализе таблиц данных выполняется анализ "что если".
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе. Например, таблицу данных можно использовать для изменения процентной ставки и срока кредита, чтобы оценить потенциальные суммы ежемесячных платежей.
Примечание: Вы можете выполнять более быстрые вычисления с помощью таблиц данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в статье Таблицы данных Excel What-If: ускорение вычислений с помощью VBA.
Типы анализа "что если"
Существует три типа средств анализа "что если" в Excel: сценарии, таблицы данных и поиск целей. Сценарии и таблицы данных используют наборы входных значений для вычисления возможных результатов. Поиск целей явно отличается, он использует один результат и вычисляет возможные входные значения, которые бы привели к такому результату.
Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, таблицы данных показывают все результаты в одной таблице на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.
В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя она ограничена только одной или двумя переменными (одна для входной ячейки строки и одна для ячейки ввода столбца), таблица данных может содержать любое количество различных значений переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.
Дополнительные сведения см. в статье Введение в анализ What-If.
Создайте таблицы данных с одной или двумя переменными в зависимости от количества переменных и формул, которые необходимо протестировать.
Таблицы данных с одной переменной
Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, можно использовать таблицу данных с одной переменной, чтобы увидеть, как различные процентные ставки влияют на ежемесячный платеж по ипотеке с помощью функции PMT. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.
На следующем рисунке ячейка D2 содержит формулу оплаты =PMT(B3/12;B4;-B5)), которая ссылается на входную ячейку B3.
Таблицы данных с двумя переменными
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
На следующем рисунке ячейка C2 содержит формулу оплаты =PMT(B3/12;B4;-B5)), которая использует две входные ячейки: B3 и B4.
Вычисления таблицы данных
При пересчете листа все таблицы данных также будут пересчитываться, даже если данные не были изменены. Чтобы ускорить вычисление листа, содержащего таблицу данных, можно изменить параметры вычисления , чтобы автоматически пересчитывать лист, но не таблицы данных. Дополнительные сведения см. в разделе Ускорение вычислений на листе с таблицами данных.
Таблица данных с одной переменной содержит входные значения либо в одном столбце (ориентированный на столбец), либо в строке (в виде строк). Любая формула в таблице данных с одной переменной должна ссылаться только на одну ячейка ввода.
Сделайте следующее:
-
Введите список значений, которые нужно заменить во входной ячейке: вниз по одному столбцу или через одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.
-
Выполните одно из следующих действий:
-
Если таблица данных ориентирована на столбец (значения переменных находятся в столбце), введите формулу в ячейке на одну строку выше и одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.
-
Если таблица данных ориентирована на строки (значения переменных находятся в строке), введите формулу в ячейке на один столбец слева от первого значения и одну ячейку под строкой значений.Если вы хотите изучить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
-
-
Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На рисунке выше этот диапазон — C2:D5.
-
На вкладке Данные щелкните Анализ что если > Таблица данных (в группе Инструменты данных или Прогноз Excel 2016 ).
-
Выполните одно из следующих действий:
-
Если таблица данных ориентирована на столбцы, введите ссылка на ячейку для входной ячейки в поле Ячейка ввода столбца . На рисунке выше входная ячейка имеет значение B3.
-
Если таблица данных ориентирована на строки, введите ссылку на ячейку входной ячейки в поле Ячейка ввода строки .
Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов отформатированы в виде валюты.
-
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Выполните эти действия
-
Выполните одно из следующих действий:
-
Если таблица данных ориентирована на столбцы, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
-
Если таблица данных ориентирована на строки, введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.
-
-
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
-
На вкладке Данные щелкните Анализ что если > Таблица данных (в группе Инструменты данных или Прогноз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 ) щелкните Что если анализ > таблица данных (в группе Инструменты данных или Прогноз Excel 2016 ).
-
В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке.Введите ячейку B4 в поле Ячейка ввода строки .
-
В поле Входная ячейка столбца введите ссылку на входную ячейку для входных значений в столбце.Введите B3 в поле Входная ячейка Столбца .
-
Нажмите кнопку ОК.
Пример таблицы данных с двумя переменными
Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ячейка C2 содержит формулу оплаты =PMT(B3/12;B4;-B5)), которая использует две входные ячейки: B3 и B4.
Если этот параметр вычисления задан, вычисления таблицы данных не выполняются при пересчете для всей книги. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9.
Чтобы повысить производительность вычислений, выполните следующие действия.
-
Выберите Пункт Параметры > файла > формулы.
-
В разделе Параметры вычисления в разделе Вычислить щелкните Автоматически, кроме таблиц данных.
Совет: При необходимости на вкладке Формулы щелкните стрелку на вкладке Параметры вычисления, а затем выберите пункт Автоматически, кроме таблиц данных (в группе Вычисление ).
Вы можете использовать несколько других средств Excel для анализа "что если", если у вас есть определенные цели или большие наборы переменных данных.
Подбор параметров
Если вы знаете результат, который следует ожидать от формулы, но не знаете точно, какое входное значение необходимо формуле, чтобы получить этот результат, используйте функцию Goal-Seek. См. статью Использование поиска цели, чтобы найти нужный результат, изменив входное значение.
Решатель Excel
Вы можете использовать надстройку "Решение Excel", чтобы найти оптимальное значение для набора входных переменных. Решатель работает с группой ячеек (называемых переменными решения или просто переменными ячейками), которые используются при вычислении формул в целевых и ограничивающих ячейках. Надстройка "Поиск решения" изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке. Дополнительные сведения см. в этой статье : Определение и решение проблемы с помощью средства решения.
Подключив различные числа к ячейке, вы можете быстро придумать различные ответы на проблему. Отличным примером является использование функции ПМТ с разными процентными ставками и периодами кредита (в месяцах), чтобы выяснить, сколько кредита вы можете позволить себе для дома или автомобиля. Числа введите в диапазон ячеек, называемый таблицей данных.
Здесь таблица данных — это диапазон ячеек B2:D8. Вы можете изменить значение в B4, сумму кредита и ежемесячные платежи в столбце D автоматически обновляться. Используя процентную ставку 3,75 %, D2 возвращает ежемесячный платеж в размере 1042,01 долл. США по следующей формуле: =PMT(C2/12,$B$3,$B$4).
Можно использовать одну или две переменные в зависимости от количества переменных и формул, которые требуется протестировать.
Используйте тест с одной переменной, чтобы увидеть, как различные значения одной переменной в формуле изменят результаты. Например, можно изменить процентную ставку для ежемесячного платежа по ипотеке с помощью функции PMT. Переменные значения (процентные ставки) вводятся в один столбец или строку, а результаты отображаются в соседнем столбце или строке.
В этой динамической книге ячейка D2 содержит формулу оплаты =PMT(C2/12,$B$3,$B$4). Ячейка B3 — это переменная ячейка, в которой можно подключить другую длину срока (количество ежемесячных периодов оплаты). В ячейке D2 функция PMT включает процентную ставку 3,75%/12, 360 месяцев и кредит на сумму 225 000 долл. США и вычисляет ежемесячный платеж в размере 1042,01 долл. США.
Используйте тест с двумя переменными, чтобы увидеть, как различные значения двух переменных в формуле изменят результаты. Например, можно протестировать различные сочетания процентных ставок и количества периодов ежемесячных платежей для расчета платежа по ипотеке.
В этой динамической книге ячейка C3 содержит формулу оплаты =PMT($B$3/12,$B$2,B4), в которой используются две ячейки переменных: B2 и B3. В ячейке C2 функция PMT включает процентную ставку 3,875%/12, 360 месяцев и кредит на сумму 225 000 долл. США и вычисляет ежемесячный платеж в размере 1058,03 долл. США.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
