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

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

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

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

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

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

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

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

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

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

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

Выполните следующие действия для повышения производительности вычислений.

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

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

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

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

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

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

×