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

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

В настоящее время мы можем динамически изменять или обновлять сводные таблицы, используя link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] или `link: / excel-range-name-dynamic- именованные диапазоны в Excel [динамические именованные диапазоны]. Но эти методы не надежны. Поскольку вам все равно придется обновлять сводную таблицу вручную. Если у вас есть большие данные, содержащие тысячи строк и столбцов, таблицы Excel вам не очень помогут. Вместо этого это сделает ваш файл тяжелым. Так что остается единственный способ — VBA.

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

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

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

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

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

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

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

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

Private Sub Worksheet_Deactivate()

Dim pt As PivotTable

Dim pc As PivotCache

Dim source_data As Range

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

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

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

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)

Set pt = Sheet2.PivotTables("PivotTable1")

pt.ChangePivotCache pc

End Sub

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

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

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

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

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

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

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

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

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

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

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

Установите pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data). Затем мы определяем сводную таблицу, которую хотим обновить. Поскольку мы хотим обновить сводную таблицу 1 (имя сводной таблицы. Вы можете проверить имя сводной таблицы на вкладке анализа при выборе сводной таблицы) на листе 1, мы устанавливаем pt, как показано ниже.

Set pt = Sheet2.PivotTables («PivotTable1»)

Теперь мы просто используем этот сводный кеш для обновления сводной таблицы. Мы используем метод changePivotCache объекта pt.

pt.ChangePivotCache pc

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

Так что да, ребята, вот как вы можете динамически изменять диапазон источников данных в 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] | Событие рабочего листа действительно полезно, когда вы хотите, чтобы ваши макросы запускались, когда на листе происходит указанное событие.