Перейти к основному контенту
Office

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

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

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

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

Примечание: Вы можете выполнять более быстрые вычисления с помощью таблиц данных и Visual Basic для приложений (VBA). Дополнительные сведения можно найти в статье что такое таблицы данных в Excel: более быстрые вычисления с помощью VBA.

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

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

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

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

Ознакомьтесь с дополнительными сведениями в этой статье, посвященными анализу " что если".

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

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

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

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

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

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

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

На приведенном ниже рисунке ячейка C2 содержит формулу оплаты = ПЛТ (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 введены формулой = ПЛТ (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.

Data table with two variables

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

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

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

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

    • Во всех остальных версиях выберите параметры > файлов > формул.

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

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

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

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

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

Приложение Excel "Поиск решения"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

×