Как создать динамическую сводную таблицу для автоматического обновления расширяющихся данных в Excel?

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

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

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

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

1. Выберите диапазон данных и одновременно нажмите клавиши Ctrl + T. В открывшемся диалоговом окне Создать таблицу нажмите кнопку ОК.

2. Затем исходные данные будут преобразованы в табличный диапазон. Продолжая выделение табличного диапазона, нажмите Вставка > Сводная таблица.

3. В окне Создание сводной таблицы выберите место размещения сводной таблицы и нажмите ОК (в данном случае я размещаю сводную таблицу на текущем листе).

4. В панели Полей сводной таблицы перетащите поля в соответствующие области.

5. Теперь, если вы добавите новые данные внизу или справа от исходного диапазона, перейдите к сводной таблице, щелкните её правой кнопкой мыши и выберите Обновить из контекстного меню.

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

Создание динамической сводной таблицы с использованием функции СМЕЩ

В этом разделе я покажу вам, как создать динамическую сводную таблицу с помощью функции СМЕЩ.

1. Выберите исходный диапазон данных, нажмите Формулы > Диспетчер имен. Смотрите скриншот:

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

• Введите имя для диапазона в поле Имя;
• Скопируйте приведенную ниже формулу в Ссылается на поле;
  =СМЕЩ('dynamic pivot with table'!$A$1,0,0,СЧЁТЗ('dynamic pivot with table'!$A:$A),СЧЁТЗ('dynamic pivot with table'!$1:$1))
• Нажмите кнопку ОК.

Примечание: В формуле 'dynamic pivot with table' — это имя рабочего листа, содержащего исходный диапазон; $A$1 — первая ячейка диапазона; $A:$A — первый столбец диапазона; $1:$1 — первая строка диапазона. Пожалуйста, измените их в соответствии с вашим собственным исходным диапазоном данных.

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

4. Нажмите Вставка > Сводная таблица.

5. В окне Создание сводной таблицы введите имя диапазона, которое вы указали на шаге 2, выберите место размещения сводной таблицы и нажмите кнопку ОК.

6. В панели Полей сводной таблицы перетащите поля в соответствующие области.

7. После добавления новых данных в исходный диапазон данные в сводной таблице будут обновлены при нажатии на опцию Обновить.

Связанные статьи