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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

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

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

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

Шаг 1: Настройте базовую формулу

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

= -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, предполагает расположение данных таким образом, чтобы значения переменных были перечислены по строке, а не вниз по столбцу. Опираясь на приведенный выше пример, давайте выполним шаги для создания таблицы данных, ориентированной на строки.

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

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

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

1. В ячейке A9 введите эту формулу: =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 с 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 содержит процентную ставку).
   • И затем нажмите OK Кнопку.
     
4. Теперь Excel заполнит таблицу данных результатами для каждой комбинации суммы кредита и процентной ставки. Она предоставляет прямое представление о том, как различные комбинации сумм кредита и процентных ставок влияют на ежемесячный платеж, что делает ее ценным инструментом для финансового планирования и анализа.
   
5. Наконец, вы должны применить желаемый числовой формат к результатам (валюта) согласно вашим потребностям.
   

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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