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

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

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

Таблицы данных являются частью набора команд, которые называются средствами анализа «что если». При использовании таблиц данных выполняется анализ «что если».

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

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

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

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

Дополнительные сведения о анализ "что если": Общие сведения для анализа "что если".

Базовые сведения о таблицах данных

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

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

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

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

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

На приведенном ниже рисунке ячейка C2 содержит формулу вычисления платежа, =PMT(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, введите формулы =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 ) нажмите кнопку Анализ "что если" и нажмите кнопку Таблица данных.

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

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

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

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

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

Ускорение вычислений для листов, содержащих таблицы данных

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

    • В Excel 2007 нажмите Кнопку Microsoft Office Изображение кнопки Office , выберите пункт Параметры Excel и выберите категорию формулы.

    • В других версиях, выберите файл > Параметры > формулы.

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

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

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

Что нужно сделать дальше?

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

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

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

Поиск решения Excel

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

К началу страницы

Примечание : Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

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

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

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

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×