Таблица данных в Excel: создание одномерных и двумерных таблиц данных

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

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

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

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

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

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

Другие операции с таблицами данных

• Удаление таблицы данных
• Редактирование результатов таблицы данных
• Ручной пересчёт таблицы данных

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

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

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

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

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

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

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

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

---

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

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

Допустим, вы рассматриваете кредит на сумму $50 000, который планируете погасить за 3 года (то есть за 36 месяцев). Чтобы подобрать комфортный для вас ежемесячный платёж с учётом вашего дохода, вы хотите проанализировать, как разные процентные ставки повлияют на сумму ежемесячного платежа.

Шаг 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. Перейдите на ленту, щелкните вкладку Данные, затем выберите Анализ «что если» > Таблица данных. См. снимок экрана:
   Data Table"/>
4. В диалоговом окне Таблица данных щелкните в поле Ячейка ввода столбца (поскольку для входных значений используется столбец) и выберите ячейку переменной, на которую ссылается ваша формула. В данном примере мы выбираем ячейку B1, содержащую процентную ставку. Наконец, нажмите кнопку ОК. См. снимок экрана:
   
5. Excel автоматически заполнит пустые ячейки, расположенные рядом с каждым из ваших значений переменной (различными процентными ставками), соответствующими результатами. См. снимок экрана:
   
6. Примените к результатам нужный числовой формат («Денежный») в соответствии с вашими требованиями. Теперь таблица данных с ориентацией по столбцам успешно создана, и вы можете быстро оценить, какие суммы ежемесячного платежа окажутся для вас приемлемыми при изменении процентной ставки. См. снимок экрана:
   

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

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

Шаг 1: разместите процентные ставки в строке

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

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

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

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

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

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

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

=B4*B2*12-B3

Шаг 2: организуйте Исходные данные таблицы данных

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

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

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

Раскройте магию Excel с помощью KUTOOLS AI

• Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно по простым командам.
• Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
• Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
• Интерпретация формул: С лёгкостью разбирайтесь в сложных формулах.
• Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.

Расширьте возможности Excel с помощью инструментов на базе ИИ.Скачать сейчаси ощутите эффективность, как никогда раньше!

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

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

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

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

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

Шаг 1: настройте две изменяемые переменные

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

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

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

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

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

Другие операции с таблицами данных

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

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

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

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

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

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

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

Ручной пересчёт таблицы данных

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

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

Перейдите на вкладку Формулы, затем выберите Параметры вычислений → Автоматически, кроме таблиц данных (см. снимок экрана):

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

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

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