Автообновление сводной таблицы
На днях компания Microsoft анонсировала, что в следующих версиях Excel появится возможность автоматического обновления сводных таблиц. За это будет отвечать кнопка Автообновление (Auto Refresh) на вкладке Анализ сводной таблицы (PivotTable Analyze):
Для текущей сводной таблицы (т.е. той, где сейчас стоит активная ячейка) можно будет либо нажать, либо отжать эту кнопку, переключая, таким образом, сводную в режим автоматического обновления. В этом режиме любые изменения в исходном диапазоне данных приведут к обновлению сводной "на лету", без необходимости отдельно нажимать кнопку Обновить (Refresh), как это требуется сейчас.
Также стоит упомянуть, что :
- Если несколько сводных построены на общем источнике (кэше), то обновляться тоже будут они все разом.
- Изначально автообновление выключено, но можно его включить по умолчанию для всех создаваемых сводных таблиц, задав соответствующую опцию через Файл - Параметры - Данные - Изменить макет по умолчанию (File - Options - Edit default layout)
- Автообновление нельзя включить для сводных, построенных по Модели данных Power Pivot или на результатах запроса Power Query.
Пока что эта функция находится в стадии тестирования и доступна только добровольцам-тестировщикам (Office Insider Beta Channel), но имеет смысл ждать её появления вскоре и для обычных пользователей (возможно, с какими-то доработками после тестирования).
В общем и целом - это приятная новость, но реализовать подобное весьма легко с помощью макросов - на любой версии и прямо сейчас. Причем даже для сводных на основе Power Query и Power Pivot.
Суть способа в создании специального макроса, который будет:
- Перехватывать событие изменения листа с исходными данными
- Определять, находится ли изменённая ячейка в таблице, по которой построена сводная (ведь менять могут и произвольные ячейки за её пределами).
- Обновлять сводную
Сразу уточним, что в коде нужно будет указать два имени - имя исходной таблицы данных и имя сводной, которая по ним построена.
Имя исходной таблицы можно посмотреть на вкладке Конструктор таблиц (Table Design), выделив любую ячейку в таблице:
Имя сводной таблицы можно посмотреть на вкладке Анализ сводной таблицы (PivotTable Analyze), аналогично выделив в ней любую ячейку:
После уточнения имён, можно приступать к процессу. Для начала щёлкнем правой кнопкой мыши по ярлычку листа с исходными данными (т.е. листа, где находится Таблица1 в нашем случае) и выберем команду Просмотреть код (View code). Откроется окно редактора макросов, куда нужно будет скопировать и вставить следующий макрос:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable, sd As Range
Set pt = ActiveSheet.PivotTables("Сводная таблица1")
Set sd = Range("Таблица1")
If Not Intersect(Target, sd) Is Nothing Then
pt.RefreshTable
End If
End Sub
Если сводная таблица у вас расположена не на том же листе, где данные, то строку с определением переменной pt нужно будет подправить, явно прописав в ней имя листа, где лежит сводная:
Set pt = Worksheets("Лист1").PivotTables("Сводная таблица1")
После этого изменение любой ячейки в пределах таблицы исходных данных (Таблица1) будет автоматом обновлять сводную. Причём этот макрос будет также замечательно работать и для сводных, созданных на основе запросов Power Query или модели данных Power Pivot, т.е. когда исходная Таблица1 сначала загружается и обрабатывается в Power Query / Power Pivot, а уже потом создаётся сводная на их основе.
Если же сводную вы создавали на базе обычного диапазона, а не динамической "умной" таблицы, то её имя в коде задавать уже не нужно, т.к. мы можем определять его автоматически из параметров самой сводной таблицы, используя свойство SourceData. Однако потребуется добавить преобразование адреса исходного диапазона данных из режима R1C1 (в котором его выдаёт SourceData) в обычный "морской бой" с помощью метода ConvertFormula:
Set sd = Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
Ну и, конечно же, можно не заморачиваться и прописать ссылку на диапазон исходных данных напрямую - причем даже с запасом, чтобы предусмотреть в будущем добавление новых строк в исходные данные:
Set sd = Worksheets("Лист1").Range("A1:E5000")
И не забудьте в конце сохранить файл в формате с поддержкой макросов (xlsm) или в формате двоичной книги (xlsb) - иначе все добавленные в книгу макросы будут удалены и магия не сработает.
Само собой, реализовать всё вышеописанное имеет смысл только в том случае, если обновление вашей сводной не занимает долгое время. Иначе овчинка выделки уже не стоит :)
Ссылки по теме
- Создание отчётов при помощи сводных таблиц
- Преимущества сводной по Модели данных
- Одновременная фильтрация нескольких сводных таблиц
Если сводная таблица на другом листе, то третью строку макроса вот так написать (Лист2 - это там где сводная)
Set pt = Worksheets("Лист2").PivotTables("Сводная таблица1")Сейчас добавлю в статью этот момент.
А если сводная у вас на другом листе, то в коде нужно уточнить это в строке:
Set pt = Worksheets("Лист2").PivotTables("Сводная таблица1")
Я писал об этом в тексте сразу после кода макроса - посмотрите, пожалуйста.