Как автоматически обновить диапазон сводной таблицы?

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

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

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

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

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

ПРИМЕЧАНИЕ . Сводные таблицы являются частью СРЕДНЕГО НАВЫКА EXCEL .

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

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

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

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

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

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

1. Выберите одну из ячеек в ваших данных.
2. Используйте сочетание клавиш Ctrl + T или перейдите к → Вставить вкладку → Таблицы → Таблица.
3. Вы получите всплывающее окно с текущим диапазоном данных.
   
4. Нажмите ОК.
5. Теперь, чтобы создать сводную таблицу, выберите любую ячейку в ваших данных. Перейдите на вкладку «Дизайн» → «Инструменты» → «Суммирование с помощью сводной таблицы».
   
6. Нажмите ОК.

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

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

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

1. Выберите одну из ячеек в источнике данных.
2. Используйте сочетание клавиш Ctrl + T или перейдите к → Вставить вкладку → Таблицы → Таблица.
3. Вы получите всплывающее окно с текущим диапазоном данных.
4. Нажмите ОК.
5. Теперь выберите одну из ячеек сводной таблицы и перейдите в → Анализ → Данные → Изменить источник данных → Изменить источник данных (раскрывающееся меню).
6. Вы получите всплывающее окно для повторного выбора источника данных или вы также можете ввести имя таблицы в записи диапазона.
   
7. Нажмите ОК.

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

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

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

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

1. Перейдите в → Вкладка «Формулы» → «Определенные имена» → «Диспетчер имен».
2. После того, как вы нажмете на менеджер имен, вы увидите всплывающее окно.
   
3. В окне диспетчера имен нажмите «Создать», чтобы создать именованный диапазон.
4. В окне нового имени введите
   1. Имя для вашего нового ассортимента. Я использую имя «SourceData».
   2. Укажите размер диапазона. Вы можете выбрать между текущим листом или книгой.
   3. Добавьте комментарий, описывающий ваш именованный диапазон. Введите приведенную ниже формулу в строку ввода «Ссылка».
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. В конце нажмите ОК.

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

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

Добавив новые данные в исходный лист, просто обновите сводную таблицу.

Как работает эта формула?

В приведенной выше формуле я использовал функцию смещения для создания динамического диапазона.

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

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

Единственное, на что вам нужно обратить внимание, это чтобы между столбцом А и строкой 1 не было пустой ячейки.

Обновить сводную таблицу с помощью кода VBA

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

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Что вам нужно изменить, прежде чем использовать его в своей книге.

1. Строка 13: измените имя исходного листа.
2. Строка 14: Измените имя листа сводной таблицы.
3. Строка 17: Измените имя сводной таблицы.

Если вы по-прежнему сталкиваетесь с какими-либо проблемами при использовании этого кода, напишите мне в поле для комментариев. Теперь позвольте мне показать вам, как работает этот код, чтобы вы могли легко изменить его в соответствии с вашими потребностями.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

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

 PivotName = "PivotTable2"

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

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

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

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

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

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