В этом руководстве будет показано, как обновить сводную таблицу или все сводные таблицы с помощью VBA.
Сводные таблицы - это исключительно мощный инструмент для работы с данными в Excel. Сводные таблицы позволяют нам анализировать и интерпретировать большие объемы данных путем группировки и суммирования полей и строк.
Когда мы создаем сводную таблицу, данные хранятся в так называемом кэше сводной таблицы. Таким образом, данные хранятся в кэш-памяти ПК, что ускоряет работу сводной таблицы.
Если источник данных сводной таблицы обновлен, сама сводная таблица не обновляется. Пользователю в Excel необходимо щелкнуть Обновить все на вкладке «Данные» на ленте, чтобы обновить исходные данные сводной таблицы.
В качестве альтернативы мы можем написать макросы на VBA, которые обновят данные за нас!
Обновить все подключения к данным
Самый простой способ обновить данные - использовать VBA для имитации того, что Обновить все кнопка делает на ленте.
| 123 | Sub RefreshConnections ()ActiveWorkbook.RefreshAllКонец подписки |
Этот метод обновит все подключения к любым данным в активной книге. Он также не просто обновит одну сводную таблицу, но обновит несколько сводных таблиц, если у вас есть несколько подключенных к разным наборам данных.
Обновить все сводные таблицы
Чтобы обновить только сводные таблицы в нашей книге, но исключить любые другие подключения к данным, мы можем использовать метод под названием RefreshTable.
Если у нас есть несколько сводных таблиц в нашей книге, нам нужно будет пройти через все сводные таблицы, чтобы обновить их все. Для этого мы сначала объявляем переменную сводной таблицы, а затем создаем цикл для каждого цикла, чтобы просмотреть все сводные таблицы в активной книге.
| 123456 | Sub RefreshPivotsOnly ()Dim tblPivot As PivotTableДля каждого tblPivot в ActiveWorkbook.PivotTablestblPivot.RefreshTableСледующий tblPivotКонец подписки |
Мы можем использовать аналогичный макрос для обновления сводных таблиц в нашем Активный лист а не всю книгу. Затем мы перебрали сводные таблицы в ActiveSheet а не ActiveWorkbook.
| 123456 | Sub RefreshActiveSheetPivotsOnly ()Dim tblPivot As PivotTableДля каждого tblPivot в ActiveSheet.PivotTablestblPivot.RefreshTableСледующий tblPivotКонец подписки |
Этот макрос был бы наиболее полезен, если бы у нас был легкий доступ к нему на нашем ActiveSheet. Для этого мы можем создать кнопку на листе для запуска макроса.
Обновить одну сводную таблицу
Если мы просто хотим обновить сводную таблицу, над которой мы работаем, а не все остальные сводные таблицы в книге, нам нужно определить конкретную сводную таблицу. Это, конечно, если вы знаете имя сводной таблицы - в данном случае Сводная таблица1.
| 123 | Sub RefreshOneTableActiveSheet.PivotTables ("PivotTable1"). RefreshTableКонец подписки |
Обновление кеша сводной таблицы
Если у нас есть несколько сводных таблиц в нашей книге, но все они используют одни и те же данные, мы можем обновить кеш сводной таблицы, а не обновлять фактическую сводную таблицу. При обновлении кеша автоматически обновляются все сводные таблицы, использующие данные, содержащиеся в кэше.
| 123456 | Sub RefreshCache ()Dim chPivot As PivotCacheДля каждого chPivot в ActiveWorkbook.PivotCacheschPivot.RefreshСледующий chPivotКонец подписки |
