Вычисление множественных результатов с помощью таблицы данных

В Microsoft Excel таблицы данных являются частью набора команд, известных как инструменты анализа "что если". При построении и анализе таблиц данных вы выполняете анализ "что если".

Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе. Например, можно использовать таблицу данных для оценки потенциальных ежемесячных платежей при изменении процентной ставки и срока кредита.

Типы анализа "что если"    

В Excel есть три типа инструментов анализа "что если": сценарии, таблицы данных и под параметра. Сценарии и таблицы данных используют наборы входных значений для вычисления возможных результатов. Подбор параметра отличается тем, что использует один результат и вычисляет возможные входные значения, которые приведут к этому результату.

Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, таблицы данных показывают все результаты в одной таблице на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.

В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя таблица данных ограничена одной или двумя переменными (одной для ячейки ввода строки и одной для входной ячейки столбца), она может включать столько различных значений переменных, сколько необходимо. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.

Подробнее см. в статье Введение в анализ "что если".

Создавайте таблицы данных с одной или двумя переменными в зависимости от числа переменных и формул, которые необходимо протестировать.

Таблицы данных с одной переменной    

Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, можно использовать таблицу данных с одной переменной, чтобы узнать, как разные процентные ставки влияют на ежемесячный платеж по ипотеке, используя функцию ПЛТ. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.

На следующей иллюстрации ячейка D2 содержит формулу расчета платежа =ПЛТ(B3/12,B4,-B5), которая ссылается на ячейку ввода B3.

Таблицы данных с двумя переменными    

Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.

На следующей иллюстрации ячейка C2 содержит формулу расчета платежа =ПЛТ(B3/12,B4,-B5), которая использует две ячейки ввода: B3 и B4.

 

Вычисления в таблице данных    

При каждом пересчете листа все таблицы данных пересчитываются, даже если входные данные не изменились. Чтобы ускорить вычисление листа, содержащего таблицу данных, можно изменить параметры вычислений так, чтобы лист пересчитывался автоматически, но без пересчета таблицы данных. Подробнее см. в разделе Ускорение вычислений на листе, содержащем таблицы данных.

Таблица данных с одной переменной содержит входные значения в одном столбце (ориентированная по столбцу) или в одной строке (ориентированная по строке). Каждая формула в таблице данных с одной переменной должна ссылаться только на одну ячейка ввода.

Сделайте следующее:

1. Введите список значений, которые нужно подставить в ячейку ввода: в столбец или в строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.

2. Выполните одно из следующих действий:

   • Если таблица данных ориентирована по столбцу (значения переменных находятся в столбце), введите формулу в ячейке на одну строку выше и на одну ячейку правее от столбца значений. Эта таблица данных с одной переменной ориентирована по столбцам, а формула находится в ячейке D2.
     
     
     
     При необходимости анализировать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.

   • Если таблица данных ориентирована по строке (значения переменных находятся в строке), введите формулу в ячейке на один столбец левее от первого значения и на одну строку ниже строки значений.
     
     При необходимости анализировать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.

3. Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На рисунке выше этот диапазон — C2:D5.

4. На вкладке Данные выберите Анализ "что если" >Таблица данных (в группе Работа с данными или группе Прогноз из Excel 2016).

5. Выполните одно из следующих действий:

   • Если таблица данных ориентирована по столбцу, введите ссылка на ячейку для ячейки ввода в поле Ячейка ввода столбца. На рисунке выше ячейкой ввода является B3.

   • Если таблица данных ориентирована по строке, введите ссылку на ячейку ввода в поле Ячейка ввода строки.

     Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке представлены ячейки с результатами в формате валюты.

Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.

Выполните эти действия

1. Сделайте что-то из следующего:

   • Если таблица данных ориентирована по столбцам, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.

   • Если таблица данных ориентирована по строкам, введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.

2. Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.

3. На вкладке Данные выберите Анализ "что если" > Таблица данных (в группе Работа с данными или группе Прогноз из Excel 2016).

4. Сделайте что-то из следующего:

   • Если таблица данных ориентирована по столбцу, введите ссылку на ячейку ввода в поле Ячейка ввода столбца.

   • Если таблица данных ориентирована по строке, введите ссылку на ячейку ввода в поле Ячейка ввода строки.

В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.

Сделайте следующее:

1. В ячейке на листе введите формулу, ссылающуюся на две ячейки ввода.

   В следующем примере, где начальные значения формулы введены в ячейки B3, B4 и B5, формулу =ПЛТ(B3/12,B4,-B5) следует ввести в ячейку C2.

2. Введите один список входных значений в том же столбце под формулой.

   В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.

3. Введите второй список справа от формулы в той же строке.

   Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.

4. Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5).

   В данном примере выделяется диапазон C2:E5.

5. На вкладке Данные, в группе Работа с данными или Прогноз (в Excel 2016), выберите Анализ "что если" >Таблица данных (в группе Работа с данными или Прогноз из Excel 2016).

6. В поле Ячейка ввода строки введите ссылку на ячейку ввода, для которой указаны входные значения в строке.
   Введите ячейку B4 в поле Ячейка ввода строки.

7. В поле Ячейка ввода столбца введите ссылку на ячейку ввода, для которой указаны входные значения в столбце.
   Введите B3 в поле Ячейка ввода столбца.

8. Нажмите кнопку ОК.

Пример таблицы данных с двумя переменными

Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На следующем рисунке ячейка C2 содержит формулу расчета платежа =ПЛТ(B3/12,B4,-B5), ссылающуюся на две ячейки ввода: B3 и B4.

Если установлен этот параметр вычислений, при пересчете всей книги таблицы данных не пересчитываются. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9.

Чтобы повысить производительность вычислений, сделайте следующее:

1. Выберите Файл > Параметры > Формулы.

2. В разделе Параметры вычислений выберите Автоматически.

   Совет: При необходимости на вкладке Формулы выберите стрелку рядом с параметром Параметры вычислений, затем выберите Автоматически.

Для анализа "что если", можно использовать и другие инструменты Excel, особенно если у вас есть конкретные цели или большие наборы переменных данных.

Подбор параметров

Если вы знаете, какой результат должна дать формула, но не знаете точно, какое входное значение для этого нужно, используйте инструмент "Подбор параметра". См. статью Использование подбора параметра для получения нужного результата путем изменения входного значения.

Решатель Excel

Можно использовать надстройку "Решатель Excel", чтобы найти оптимальное значение для набора входных переменных. Надстройка "Решатель Excel" работает с группой ячеек (называемых переменными решения или просто переменными), которые используются при вычислении формул в целевых и ограничивающих ячейках. Надстройка "Поиск решения" изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке. Подробнее см. в статье Определение и решение задачи с помощью надстройки "Решатель".