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

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

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

• Таблица данных, ориентированная на столбцы
• Таблица данных, ориентированная на строки
• Несколько формул для таблицы данных с одной переменной

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

Ключевые моменты с использованием таблиц данных

Другие операции по использованию таблиц данных

• Удаление таблицы данных
• Изменить результат таблицы данных
• Пересчитать таблицу данных вручную

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

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

В Excel есть два типа таблиц данных:

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

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

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

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

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Edge, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

---

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

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

Предположим, вы рассматриваете возможность получения кредита в размере 50,000 3 долларов США, который вы планируете погасить в течение 36 лет (что эквивалентно XNUMX месяцам). Чтобы оценить, какой ежемесячный платеж будет доступен в зависимости от вашей зарплаты, вам интересно узнать, как различные процентные ставки повлияют на сумму, которую вам нужно платить каждый месяц.

Шаг 1. Установите базовую формулу

Для расчета платежа здесь я установлю процентную ставку 5%. В ячейку B4 введите формулу ПЛТ, она рассчитывает ежемесячный платеж на основе процентной ставки, количества периодов и суммы кредита. Смотрите скриншот:

= -PMT($B$1/12, $B$2*12, $B$3)

Шаг 2. Перечислите процентные ставки в столбце

В столбце перечислите различные процентные ставки, которые вы хотите протестировать. Например, укажите значения от 4% до 11% с шагом 1% в столбце и оставьте хотя бы один пустой столбец справа для результатов. Смотрите скриншот:

Шаг 3. Создайте таблицу данных.

1. В ячейку E2 введите следующую формулу: = B4.
   
   Внимание: B4 — это ячейка, в которой находится ваша основная формула. Это формула, результаты которой вы хотите видеть измененными при изменении процентной ставки.
   
2. Выберите диапазон, включающий формулу, список процентных ставок и соседние ячейки для результатов (например, выберите от D2 до E10). Смотрите скриншот:
   
3. Перейдите на ленту, нажмите на значок Данные Вкладка, выберите Что-Анализ > Таблица данных, см. снимок экрана:
   
4. В Таблица данных диалоговом окне щелкните в Столбец Ячейка ввода поле (поскольку мы используем столбец для входных значений) и выберите ячейку переменной, на которую ссылается ваша формула. В этом примере мы выбираем B1, содержащий процентную ставку. Наконец, нажмите OK кнопку, см. снимок экрана:
   
5. Excel автоматически заполнит пустые ячейки рядом с каждым из ваших значений переменных (различные процентные ставки) соответствующими результатами. Смотрите скриншот:
   
6. Примените к результатам желаемый числовой формат (валюта) в соответствии с вашими потребностями. Теперь таблица данных, ориентированная на столбцы, успешно создана, и вы можете быстро просмотреть результаты, чтобы оценить, какие суммы ежемесячных платежей будут для вас доступны при изменении процентной ставки. Смотрите скриншот:
   

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

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

Шаг 1. Перечислите процентные ставки подряд.

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

Шаг 2. Создайте таблицу данных.

1. В ячейку А9 введите следующую формулу: = B4.
   
2. Выберите диапазон, включающий формулу, список процентных ставок и соседние ячейки для результатов (например, выберите от A8 до I9). Затем нажмите Данные > Что-Анализ > Таблица данных.
3. В Таблица данных диалоговом окне щелкните в Ячейка ввода строки (поскольку мы используем строку для входных значений) и выберите ячейку переменной, на которую есть ссылка в вашей формуле. В этом примере мы выбираем B1, содержащий процентную ставку. Наконец, нажмите OK кнопку, см. снимок экрана:
   
4. Примените к результатам желаемый числовой формат (валюта) в соответствии с вашими потребностями. Теперь таблица данных, ориентированная на строки, создана, см. снимок экрана:
   

Несколько формул для таблицы данных с одной переменной

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

Шаг 1. Добавьте новую формулу для расчета общей суммы процентов.

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

=B4*B2*12-B3

Шаг 2. Упорядочьте исходные данные таблицы данных.

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

Шаг 3. Создайте таблицу данных:

1. В ячейку E2 введите следующую формулу: = B4 создать ссылку на расчет погашения в исходных данных.
2. В ячейку F2 введите следующую формулу: = B5 для создания ссылки на общий интерес к исходным данным.
   
3. Выберите диапазон, включающий формулы, список процентных ставок и соседние ячейки для результата (например, выберите от D2 до F10). Затем нажмите Данные > Что-Анализ > Таблица данных.
4. В Таблица данных диалоговом окне щелкните в Ячейка ввода столбца поле (поскольку мы используем столбец для входных значений) и выберите ячейку переменной, на которую ссылается ваша формула. В этом примере мы выбираем B1, содержащий процентную ставку. Наконец, нажмите OK кнопку, см. снимок экрана:
   
5. Примените к результатам желаемый числовой формат (валюта) в соответствии с вашими потребностями. И вы можете увидеть результаты для каждой формулы на основе различных значений переменных.
   

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

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

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

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

В приведенном ниже наборе данных у нас есть процентная ставка, срок кредита и сумма кредита, а также мы рассчитали ежемесячный платеж по этой формуле: =-PMT($B$1/12, $B$2*12, $B$3) также. Здесь мы сосредоточимся на двух ключевых переменных из наших данных: процентной ставке и сумме кредита, чтобы увидеть, как изменения обоих этих факторов одновременно влияют на суммы погашения.

Шаг 1. Настройте две изменяющиеся переменные.

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

Шаг 2. Создайте таблицу данных.

1. Поместите формулу на пересечение строки и столбца, в которых вы указали значения переменных. В данном случае я введу такую ​​формулу: = B4 в ячейку Е2. Смотрите скриншот:
   
2. Выберите диапазон, включающий суммы кредита, процентные ставки, ячейку формулы и ячейки, в которых будут отображаться результаты.
   
3. Затем нажмите Данные > Что-Анализ > Таблица данных. В диалоговом окне «Таблица данных»:
   • В Ячейка ввода строки выберите ссылку на ячейку ввода для значений переменных в строке (в этом примере B3 содержит сумму кредита).
   • В Столбец ввода ячейкиВ поле l выберите ссылку на ячейку ввода для значений переменных в столбце (B1 содержит процентную ставку).
   • А затем нажмите OK Кнопка.
     
4. Теперь Excel заполнит таблицу данных результатами для каждой комбинации суммы кредита и процентной ставки. Он дает прямое представление о том, как различные комбинации сумм кредита и процентных ставок влияют на ежемесячный платеж, что делает его ценным инструментом финансового планирования и анализа.
   
5. Наконец, вам следует применить к результатам желаемый числовой формат (валюта) в соответствии с вашими потребностями.
   

Ключевые моменты с использованием таблиц данных

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

Другие операции по использованию таблиц данных

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

Удаление таблицы данных

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

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

Изменить результат таблицы данных

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

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

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

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

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

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

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

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

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