Как динамически обновлять все сводные таблицы источника данных в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: excel-pivot-tables

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

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

Запись кода в лист исходных данных Поскольку мы хотим, чтобы это было полностью автоматическим, мы будем использовать модули листа для написания кода вместо основного модуля. Это позволит нам использовать `link: / events-in-vba-the-worksheet-events-in-excel-vba [события рабочего листа]`.

Если исходные данные и сводные таблицы находятся на разных листах, мы напишем код VBA для изменения источника данных сводной таблицы в объекте листа, который содержит исходные данные (не содержащий сводные таблицы). Нажмите CTRL + F11, чтобы открыть редактор VB. Теперь перейдите в проводник проекта и найдите лист, содержащий исходные данные. Дважды щелкните по нему.

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

Щелкните в раскрывающемся меню слева и выберите рабочий лист. В раскрывающемся меню слева выберите деактивировать. Вы увидите пустую подпись, написанную на имени области кода worksheet_deativate. Наш код для динамического изменения исходных данных и обновления сводной таблицы будет помещен в этот блок кода. Этот код будет запускаться всякий раз, когда вы переключитесь с таблицы данных на любой другой лист. Вы можете прочитать обо всех событиях рабочего листа link: / events-in-vba-the-worksheet-events-in-excel-vba [здесь].

Теперь мы готовы реализовать код.

Исходный код для динамического обновления всех сводных таблиц в книге с новым диапазоном Чтобы объяснить, как это работает, у меня есть рабочая книга. Эта рабочая тетрадь состоит из трех листов. Sheet1 содержит исходные данные, которые могут изменяться. Sheet2 и Sheet3 содержат сводные таблицы, которые зависят от исходных данных Sheet2.

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

Private Sub Worksheet_Deactivate()

Dim source_data As Range

'Determining last row and column number

lstrow = Cells(Rows.Count, 1).End(xlUp).Row

lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

'Setting the new range

Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

'Code to loop through each sheet and pivot table

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables



pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=source_data)



Next pt

Next ws

End Sub

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

Вы можете увидеть эффект этого кода на гифке ниже.

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

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

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column

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

Установить исходные_данные = Диапазон (Ячейки (1, 1), Ячейки (lstrow, lstcol))

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

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

Для каждого ws In ThisWorkbook.Worksheets

Для каждой точки в ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Следующая точка

Далее ws

Первая петля проходит через каждый лист. Второй цикл выполняет итерацию по каждой сводной таблице на листе. Сводные таблицы назначаются переменной pt. * Мы используем метод ChangePivotCache объекта pt. Мы динамически создаем сводный кеш с помощью метода ThisWorkbook.PivotCaches.Create. Этот метод принимает две переменные SourceType и SourceData. В качестве типа источника мы объявляем xlDatabase, а в качестве SourceData мы передаем диапазон source_data, который мы вычислили ранее.

Вот и все. Наши сводные таблицы автоматизированы. Это автоматически обновит все сводные таблицы в книге.

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

Статьи по теме:

link: / excel-pivot-tables-how-to-dynamic-update-pivot-table-data-source-range-in-excel [Как динамически обновлять диапазон источников данных сводной таблицы в Excel]: для динамического изменения диапазон исходных данных сводных таблиц, мы используем сводные кеши. Эти несколько строк могут динамически обновлять любую сводную таблицу, изменяя диапазон исходных данных.

link: / custom-functions-in-vba-how-to-auto-refresh-pivot-tables-using-vba-excel [Как автоматически обновлять сводные таблицы с помощью VBA]: для автоматического обновления ваших сводных таблиц вы можете использовать События VBA. Используйте эту простую строку кода для автоматического обновления сводной таблицы. Вы можете использовать любой из 3 методов автоматического обновления сводных таблиц.

link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Запустить макрос, если любое изменение, сделанное на листе в указанном диапазоне]: * В ваших практиках VBA вы бы получить необходимость запускать макросы при изменении определенного диапазона или ячейки. В этом случае для запуска макросов при изменении целевого диапазона мы используем событие изменения.

link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Запускать макрос при любых изменениях на листе] | Итак, чтобы запускать ваш макрос всякий раз, когда лист обновляется, мы используем события рабочего листа VBA.

link: / events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [Простейший код VBA для выделения текущей строки и столбца с использованием] | Используйте этот небольшой фрагмент VBA, чтобы выделить текущую строку и столбец листа.

link: / events-in-vba-the-worksheet-events-in-excel-vba [События рабочего листа в Excel VBA] | Событие рабочего листа действительно полезно, когда вы хотите, чтобы ваши макросы запускались, когда на листе происходит указанное событие.