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

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

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

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

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

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

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

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

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

входную ячейку.

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

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

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

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

входную ячейку столбца.

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

входную ячейку строки.

Список значений, которые 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.

входную ячейку столбца.

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

входную ячейку строки.

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

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

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