Таблица данных — это диапазон ячеек, в котором можно изменять значения в некоторых ячейках и вы можете найти разные ответы на вопрос. Хороший пример таблицы данных с использованием функции 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.

Data table with two variables
 

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

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

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

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

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

  2. Выполните одно из указанных ниже действий.

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


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

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

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

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

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

  5. Выполните одно из указанных ниже действий.

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

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

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

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

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

  1. Сделайте это.

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

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

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

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

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

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

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

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

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

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

    В следующем примере, в котором начальные значения формулы ввели в ячейки B3, B4 и B5, введите формулу =PMT(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 содержит формулу платежа =PMT(B3/12;B4;-B5),которая использует две ячейки ввода: B3 и B4.

Data table with two variables

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

Чтобы повысить производительность вычислений, выполните указанные здесь действия.

  1. Щелкните Файл > параметры > Формулы.

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

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

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

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

Если вы знаете, какой результат следует ожидать от формулы, но точно не знаете, какое входные значения должна получить формула, используйте функцию Goal-Seek формулы. Дополнительные сведения см. в статье Использование средств поиска целью для поиска нужного результата путем изменения входного значения.

Excel Решатель

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

Подключив разные числа к ячейке, вы можете быстро получить ответы на различные вопросы. Хороший пример — использование функции PMT с различными процентными ставками и периодами займа (в месяцах), чтобы выяснить, какая часть кредита вы можете позволить себе дом или автомобиль. Числа введите в диапазон ячеек, который называется таблицей данных.

Таблица данных — это диапазон ячеек B2:D8. Значение В4, сумму займа и ежемесячные платежи в столбце D можно изменить автоматически. Используя процентную ставку 3,75 %, D2 возвращает ежемесячный платеж в размере 1042,01 долларов США по формуле =PMT(C2/12;$B$3,$B$4).

Диапазон ячеек B2:D8 представляет собой таблицу данных

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

Используйте проверку с одной переменной, чтобы увидеть, как различные значения одной переменной в формуле изменяют результаты. Например, можно изменить процентную ставку для ежемесячного платежа по ипотеке с помощью функции PMT. Значения переменных (процентные ставки) введите в одном столбце или строке, а результаты будут показаны в ближайшем столбце или строке.

В этой книге ячейка D2 содержит формулу платежа = PMT(C2/12;$B$3;$B$4). Ячейка B3 — это ячейка переменной, в которой можно подключить другую продолжительность терминов (количество периодов ежемесячных платежей). В ячейке D2 функция ПЛТ подсоеднит процентную ставку 3,75%/12, 360 месяцев и кредит на сумму 225 000 долларов США и вычислит ежемесячный платеж в размере 1 042,01 долларов США.

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

В этой книге ячейка C3 содержит формулу платежа =PMT($B$3/12;$B$2;B4), которая использует две ячейки переменных: B2 и B3. В ячейке C2 функция ПЛТ подключается к процентной ставке 3,875%/12, 360 месяцев и займу в размере 225 000 долларов США и вычисляет сумму ежемесячного платежа в размере 1 058,03 долларов США.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединение к программе предварительной оценки Майкрософт

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?

Спасибо за ваш отзыв!

×