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

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

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

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

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

Общие сведения о таблицах данных

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

Дополнительные сведения см. в статье Функция ПЛТ.

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

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

Анализ "что если" - таблица с одной переменной

Выноска 1 Ячейка ввода.

Выноска 2 Список значений, которые Excel подставляет в ячейку ввода B3.

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

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

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

Анализ "что если" - таблица с двумя переменными

Выноска 1 Ячейка ввода из столбца.

Выноска 2 Список значений, которые Excel подставляет в ячейку ввода из строки – B4.

Выноска 3 Ячейка ввода из строки.

Этап 4 Список значений, которые Excel подставляет в ячейку ввода из столбца – B3.

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

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

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

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

Ориентация таблицы данных

Действие

По столбцу (значения переменной находятся в столбце)

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

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

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

По строке (значения переменной находятся в строке)

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

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

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

  2. В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.

    Элемент "Таблица данных"

    В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.

    Диалоговое окно "Таблица данных"

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

Ориентация таблицы данных

Необходимые действия

По столбцу

Введите ссылку на ячейку ввода в поле Подставлять значения по строкам. На первом рисунке ячейка ввода — это B3.

По строке

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

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

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

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

Ориентация таблицы данных

Необходимые действия

По столбцу (значения переменной находятся в столбце)

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

По строке (значения переменной находятся в строке)

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

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

  2. В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.

    Элемент "Таблица данных"

    В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.

    Диалоговое окно "Таблица данных"

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

Ориентация таблицы данных

Необходимые действия

По столбцу

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

По строке

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

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

  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 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.

    Элемент "Таблица данных"

    В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.

    Диалоговое окно "Таблица данных"

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

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

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

Анализ "что если" - таблица с двумя переменными

Выноска 1 Ячейка ввода из столбца.

Выноска 2 Список значений, которые Excel подставляет в ячейку ввода из строки – B4.

Выноска 3 Ячейка ввода из строки.

Этап 4 Список значений, которые Excel подставляет в ячейку ввода из столбца – B3.

Важно : Если выбран этот вариант вычисления, при пересчете книги таблицы данных не пересчитываются. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9. Чтобы использовать эту клавишу в Mac OS X версии 10.3 или более поздней, сначала необходимо отключить ее назначение в Exposé. Дополнительные сведения см. в статье Сочетания клавиш в Excel.

  1. В меню Excel выберите пункт Параметры.

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

См. также

Введение в анализ "что если"

Прогнозирование тенденций в данных

Пример анализа с помощью надстройки "Поиск решения"

Использование средства подбора параметров для получения требуемого результата путем изменения входного значения

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

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

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

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

×