Таблица данных Excel, как использовать

Обновлено: 21.11.2024

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

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

Что такое таблица данных в Excel?

В Microsoft Excel таблица данных — это один из инструментов анализа «что, если», который позволяет опробовать различные входные значения для формул и посмотреть, как изменения этих значений повлияют на вывод формул.

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

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

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

Как создать таблицу данных с одной переменной в Excel

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

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

  • B8 содержит формулу FV, по которой рассчитывается итоговое сальдо.
  • B2 — это переменная, которую вы хотите протестировать (начальные инвестиции).

А теперь давайте проведем простой анализ "что, если", чтобы увидеть, каковы будут ваши сбережения через 5 лет в зависимости от суммы ваших первоначальных инвестиций, в диапазоне от 1000 до 6000 долларов США.

Вот шаги для создания таблицы данных с одной переменной:

  1. Введите значения переменных либо в один столбец, либо в одну строку. В этом примере мы собираемся создать таблицу данных, ориентированную на столбцы, поэтому мы вводим значения наших переменных в столбец (D3:D8) и оставляем как минимум один пустой столбец справа для результатов. .
  2. Введите формулу в ячейку на одну строку выше и на одну ячейку справа от значений переменных (в нашем случае E2). Или свяжите эту ячейку с формулой в исходном наборе данных (если вы решите изменить формулу в будущем, вам нужно будет обновить только одну ячейку). Мы выбираем последний вариант и вводим эту простую формулу в E2: =B8

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

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

Таблица данных, ориентированная на строки

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

  1. Введите значения переменных в строке, оставив как минимум один пустой столбец слева (для формулы) и одну пустую строку внизу (для результатов). В этом примере мы вводим значения переменных в ячейки F3:J3.
  2. Введите формулу в ячейку, которая находится на один столбец левее первого значения переменной и на одну ячейку ниже (в нашем случае E4).
  3. Создайте таблицу данных, как описано выше, но введите входное значение (B3) в поле ввода строки:

Как создать таблицу данных с двумя переменными в Excel

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

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

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

  1. Введите формулу в пустую ячейку или свяжите эту ячейку с исходной формулой. Убедитесь, что у вас достаточно пустых столбцов справа и пустых строк ниже, чтобы вместить значения ваших переменных. Как и прежде, мы связываем ячейку E2 с исходной формулой FV, которая вычисляет баланс: =B8
  2. Введите один набор входных значений под формулой в том же столбце (инвестиционные значения в E3:E8).
  3. Введите другой набор значений переменных справа от формулы в той же строке (количество лет в F2:H2).

Таблица данных для сравнения нескольких результатов

Если вы хотите оценить несколько формул одновременно, создайте таблицу данных, как показано в предыдущих примерах, и введите дополнительные формулы:

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

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

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

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

Таблица данных в Excel — 3 вещи, которые вы должны знать

Чтобы эффективно использовать таблицы данных в Excel, имейте в виду три простых факта:

  1. Для успешного создания таблицы данных входные ячейки должны находиться на том же листе, что и таблица данных.
  2. Microsoft Excel использует функцию TABLE(row_input_cell, colum_input_cell) для вычисления результатов таблицы данных:
    • В таблице данных с одной переменной один из аргументов опускается в зависимости от макета (ориентированного на столбцы или строки). Например, в нашей горизонтальной таблице данных с одной переменной используется формула =TABLE(, B3), где B3 — ячейка ввода столбца.
    • В таблице данных с двумя переменными используются оба аргумента. Например, =TABLE(B6, B3), где B6 — ячейка ввода строки, а B3 — ячейка ввода столбца.

Как удалить таблицу данных в Excel

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

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

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

Как редактировать результаты таблицы данных

Поскольку в Excel невозможно изменить часть массива, вы не можете редактировать отдельные ячейки с рассчитанными значениями. Вы можете заменить все эти значения своими, только выполнив следующие действия:

  1. Выберите все полученные ячейки.
  2. Удалите формулу ТАБЛИЦА в строке формул.
  3. Введите нужное значение и нажмите Ctrl + Enter .

Это вставит одно и то же значение во все выбранные ячейки:

После того, как формула ТАБЛИЦА исчезнет, ​​прежняя таблица данных станет обычным диапазоном, и вы сможете редактировать любую отдельную ячейку в обычном режиме.

Как пересчитать таблицу данных вручную

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

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

Это отключит автоматические расчеты таблицы данных и ускорит пересчет всей книги.

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

Вот как вы создаете и используете таблицу данных в Excel. Чтобы поближе ознакомиться с примерами, обсуждаемыми в этом руководстве, вы можете загрузить образец рабочей книги Excel Data Tables. Я благодарю вас за чтение и буду рад видеть вас снова на следующей неделе!

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

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

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

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

Типы анализа возможных вариантов

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

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

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

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

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

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

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

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

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

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

Вычисления таблицы данных

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

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

Выполните следующие действия:

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

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

Если таблица данных ориентирована на столбцы (ваши значения переменных находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.

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

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

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

Выделите диапазон ячеек, содержащий формулы и значения, которые вы хотите заменить. На рисунке выше это диапазон C2:D5.

На вкладке "Данные" нажмите "Анализ "что, если"" > "Таблица данных" (в группе "Инструменты данных" или "Прогноз" в Excel 2016).

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

Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Входная ячейка столбца. На рисунке выше ячейка ввода — B3.

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

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

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

Выполните следующие действия

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

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

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

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

На вкладке "Данные" нажмите "Анализ "что если"" > "Таблица данных" (в группе "Инструменты данных" или "Прогноз" в Excel 2016).

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

Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца.

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

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

Выполните следующие действия:

В ячейку на листе введите формулу, которая относится к двум входным ячейкам.

В следующем примере, где начальные значения формулы вводятся в ячейки B3, B4 и B5, вы вводите формулу =PMT(B3/12,B4,-B5) в ячейку C2.

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

В этом случае введите разные процентные ставки в ячейки C3, C4 и C5.

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

Введите условия кредита (в месяцах) в ячейки D2 и E2.

Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых вы хотите вычислить значения (D3:E5).< /p>

В этом случае выберите диапазон C2:E5.

На вкладке "Данные" в группе "Инструменты данных" или "Прогноз" (в Excel 2016) нажмите "Анализ "что, если"" > "Таблица данных" (в группе "Инструменты данных" или "Прогноз" в Excel 2016).

В поле "Ячейка ввода строки" введите ссылку на ячейку ввода для входных значений в строке.
Введите ячейку B4 в поле ввода строки.

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

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

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

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

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

Нажмите "Файл" > "Параметры" > "Формулы".

В разделе "Параметры расчета" в разделе "Расчет" нажмите "Автоматически, кроме таблиц данных".

Совет. При необходимости на вкладке "Формулы" нажмите стрелку рядом с параметрами расчета, а затем выберите "Автоматические таблицы данных кроме данных" (в группе "Расчет").

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

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

Решатель Excel

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

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

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

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

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

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

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

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

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

Таблицы данных — это один из инструментов Microsoft Excel Что если анализ, наряду с диспетчером сценариев и поиском целей..

Сравнить результаты формулы

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

  • Срок кредита – 4 года, процентная ставка – 5 %.
  • Срок кредита – 3 года, процентная ставка – 4,5%.
  • другие условия и проценты??

В этом руководстве вы узнаете, как:

Расчет ежемесячных платежей с помощью PMT

С помощью функции PMT вы можете рассчитать ежемесячный платеж по кредиту на основе трех переменных:

  1. процентная ставка
  2. количество периодов оплаты
  3. сумма кредита

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

Формула в ячейке C6 делит годовую ставку на 12 для расчета месячной процентной ставки:

Сравнить несколько вариантов

Что делать, если вы хотите протестировать несколько вариантов одной из переменных формулы, например переменной количества платежей, показанной выше?

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

Использование таблицы данных

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

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

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

Создать таблицу данных с 1 переменной

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

ПРИМЕЧАНИЕ. Входные ячейки должны находиться на том же листе, что и таблица данных.

Информация о кредите находится в ячейках C2:C4, а количество платежей — в ячейке C3.

Чтобы настроить таблицу данных:

  • В ячейки B8:B13 введите количество платежей по кредитам сроком от 1 до 6 лет.
  • В ячейке C7 введите функцию ПЛТ, ссылаясь на ячейки с информацией о кредите: =ПЛТ(C2/12,C3,C4)
  • Выберите ячейки B7:C13 — ячейки заголовков и ячейки результатов.
  • На вкладке "Данные" ленты в группе "Инструменты данных" нажмите "Анализ "что, если""
  • В раскрывающемся меню нажмите "Таблица данных".

Откроется диалоговое окно "Таблица данных", в котором можно ввести сведения о входной ячейке

  • Нажмите в поле ввода столбца.
  • Затем в рабочей книге Excel щелкните ячейку C3
    • Эта ячейка содержит переменную для количества платежей.

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

    • Нажмите на одну из ячеек с рассчитанным ежемесячным платежом, и в строке формул будет показано, что эта ячейка содержит функцию ТАБЛИЦА с ячейкой C3 в качестве второго аргумента. Фигурные скобки в начале и в конце формулы указывают на то, что это формула массива.

    Создать таблицу данных с двумя переменными

    В этом примере вы создадите таблицу данных с двумя переменными. С двумя переменными он покажет ежемесячные платежи по кредитам на срок от 1 до 6 лет и процентные ставки от 2% до 6%.

    ПРИМЕЧАНИЕ. Входные ячейки должны находиться на том же листе, что и таблица данных.

    Информация о кредите находится в ячейках C2:C4, с процентной ставкой в ​​C2 и количеством платежей в ячейке C3.

    Чтобы настроить таблицу данных:

    1. В ячейки B8:B13 введите количество платежей по кредитам сроком от 1 до 6 лет.
    2. В ячейки C7:G7 введите процентные ставки от 2 % до 6 %.
    3. В ячейке B7 введите функцию ПЛТ, ссылаясь на ячейки с информацией о кредите: =ПЛТ(C2/12,C3,C4)
    4. Выберите ячейки B7:G13 — ячейки заголовков и ячейки результатов.
    5. На вкладке "Данные" ленты в группе "Инструменты данных" нажмите "Анализ "что, если"", а затем нажмите "Таблица данных".
    6. Щелкните в поле ввода строки, а затем щелкните ячейку C2, содержащую переменную для процентной ставки.
    7. Щелкните поле ввода столбца, а затем щелкните ячейку C3, содержащую переменную для количества платежей.

    Ускорение вычислений в книге

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

    Большинство книг Excel настроены на автоматический расчет. С этим параметром Excel автоматически пересчитывает все открытые книги:

    • при каждом изменении в любой из открытых книг
    • каждый раз, когда вы открываете другую книгу

    Изменить настройки расчета

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

    • На ленте Excel откройте вкладку "Формулы".
    • В крайнем правом углу нажмите команду "Параметры расчета".
    • В раскрывающемся списке параметров выберите "Автоматически, кроме таблиц данных".

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

    Пересчитать таблицу данных

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

    Чтобы вручную пересчитать таблицу данных, выполните следующие действия:

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

    Использовать Excel VBA

    Вы также можете использовать макросы для ускорения вычислений с помощью таблиц данных. См. эту публикацию Чарльза Уильямса, эксперта по вычислениям в Excel: Таблицы данных «что, если» в Excel: более быстрые вычисления с помощью VBA

    Очистить таблицу данных

    Поскольку значения таблицы данных находятся в массиве, вы не можете редактировать или очищать отдельные ячейки. Если вы попытаетесь изменить одну ячейку, вы увидите сообщение об ошибке — «Невозможно изменить часть таблицы данных». Если вы хотите удалить всю таблицу или результирующие значения, выполните следующие действия.

    Чтобы удалить таблицу данных с рабочего листа:

    1. Выберите все ячейки в таблице данных, включая заголовок.
    2. На клавиатуре нажмите клавишу Delete.

    Чтобы очистить только полученные значения:

    1. Выберите все полученные значения в таблице данных.
    2. На клавиатуре нажмите клавишу Delete.

    Видео: создание таблицы данных

    В этом видео Майнда Трейси показывает, как настроить таблицы данных в Excel с использованием нескольких переменных.

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

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

    Загрузить образец файла

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

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

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

    У нас есть входные данные для вычислений в C2:C5.

    • Принцип (C2). Для нашего примера будет установлено значение 10 000 долларов США.
    • Процентная ставка (C3) — это один из входных данных, который будет варьироваться в нашей таблице данных.
    • Термин (C4) — это один из входных параметров, который будет варьироваться в нашей таблице данных.
    • Накопленное значение (C5). Это расчет, на котором будет основана наша таблица данных.

    Создайте таблицу данных в диапазоне B7:K13.

    1. Выделите диапазон, в котором мы хотим разместить таблицу данных.
      • Расчет (B7) – эта ячейка содержит ссылку на расчет, найденный в ячейке C5, с использованием формулы =C5.
      • Ячейки ввода столбца (B8:B13). Этот диапазон содержит различные значения терминов, с которыми мы хотим оценить наши вычисления.
      • Ячейки ввода строки (C7:K7). Этот диапазон содержит различные значения процентных ставок, с которыми мы хотим оценить наши вычисления.
    2. Перейдите на вкладку "Данные".
    3. В разделе Инструменты данных нажмите кнопку "Анализ "что, если"".
    4. Выберите «Таблица данных» в раскрывающемся меню.
    5. Для ячейки ввода строки выберите ввод термина (C4).
    6. В ячейке ввода "Столбец" выберите процентную ставку (C3).
    7. Нажмите кнопку ОК.

    Возможно, вы спросите, что в этом такого особенного и почему мы просто не ввели эту формулу накопленного значения в ячейку C8 и не скопировали ее в диапазоне C8:K13. ?

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

    Об авторе

    Джон МакДугалл

    Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

    Читайте также: