Как Таблицы автообновления Pivot Использование VBA Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: custom-functions-in-vba

Как мы все знаем, всякий раз, когда мы вносим изменения в исходные данные сводной таблицы, они не сразу отражаются в сводной таблице. Нам нужно обновить сводные таблицы, чтобы увидеть изменения. А если вы отправите обновленный файл без обновления сводных таблиц, вы можете почувствовать себя неловко. Итак, в этой статье мы узнаем, как автоматически обновлять сводную таблицу с помощью VBA. Это проще, чем вы думали.

Это простой синтаксис для автоматического обновления сводных таблиц в книге.

'Code in Source Data Sheet Object

Private Sub Worksheet_Deactivate()

sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh

End Sub

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

Поэтому нам просто нужен макрос для обновления кеша сводных таблиц. Мы сделаем это с помощью link: / events-in-vba-the-workheet-events-in-excel-vba [workheet event], чтобы нам не приходилось запускать макрос вручную. === Где кодировать для автоматического обновления сводных таблиц? Если исходные данные и сводные таблицы находятся на разных листах, тогда код VBA должен находиться в листе исходных данных.

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

Позже я объясню, почему я использовал это событие.

Здесь у меня есть исходные данные на листе 2 и сводные таблицы на листе 1.

Откройте VBE с помощью клавиш CTRL + F11. В проводнике проекта вы можете увидеть три объекта: Sheet1, Sheet2 и Workbook.

Поскольку Sheet2 содержит исходные данные, дважды щелкните объект Sheet2.

Теперь вы можете увидеть два раскрывающихся списка вверху области кода. В первом раскрывающемся списке выберите рабочий лист. И во втором раскрывающемся списке выберите Деактивировать. Это вставит пустое под-имя Worksheet_Deactivate. Наш код будет написан в этом подпункте. Любые строки, написанные в этом подпрограмме, выполняются, как только пользователь переключается с этого листа на любой другой лист.

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

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

Private Sub Worksheet_Deactivate()

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

И это сделано.

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

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

Private Sub Worksheet_Deactivate()

'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

For Each pc In ThisWorkbook.PivotCaches

pc.Refresh

Next pc

End Sub

В этом коде мы используем ссылку: / vba-for-loops-with-7-examples [For loop] для циклического перебора всех сводных кэшей в книге. Объект ThisWorkbook содержит все сводные кэши. Для доступа к ним мы используем ThisWorkbook.PivotCaches.

Зачем использовать событие Worksheet_Deactivate?

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

Если у вас есть сводные таблицы и исходные данные на одном листе, и вы хотите, чтобы сводные таблицы обновлялись автоматически, вы можете использовать `link: / tips-using-worksheet-change-event-to-run-macro-when- любое-изменение-сделано [Событие изменения рабочего листа] `.

Private Sub Worksheet_Change(ByVal Target As Range)

Sheet1.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Как обновить все в книгах при изменении исходных данных? Если вы хотите обновить все в книге (диаграммы, сводные таблицы, формулы и т. Д.), Вы можете использовать команду ThisWorkbook.RefreshAll.

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

Обратите внимание, что этот код не меняет источник данных. Поэтому, если вы добавляете данные под исходными данными, этот код не будет включать эти данные автоматически. Вы можете использовать link: / table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables] для хранения исходных данных. Если вы не хотите использовать таблицы, мы также можем использовать VBA для включения новых данных. Мы узнаем об этом в следующем уроке.

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

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

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

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

Зачем использовать событие Worksheet_Deactivate?

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

Популярные статьи: