Как автоматического обновления сводной таблицы с помощью VBA в Microsoft Excel
В этой статье мы узнаем, как автоматически обновлять сводную таблицу с помощью VBA в Microsoft Excel 2010.
Почему нам нужно обновлять диаграмму сводной таблицы?
Как мы все знаем, всякий раз, когда мы вносим изменения в исходные данные сводной таблицы, они не сразу отражаются в сводной таблице. Нам нужно обновить сводные таблицы, чтобы увидеть изменения. А если вы отправите обновленный файл без обновления сводных таблиц, вы можете почувствовать себя неловко.
Итак, в этой статье мы узнаем, как автоматически обновлять сводную таблицу с помощью 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 для включения новых данных. Мы узнаем об этом в следующем уроке.
Надеюсь, эта статья о том, как автоматически обновлять сводную таблицу с помощью VBA в Microsoft Excel, носит пояснительный характер. Дополнительные статьи о вычислении значений и соответствующих формулах Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].
Статьи по теме:
link: / excel-pivot-tables-how-to-dynamic-update-pivot-table-data-source-range-in-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]: событие рабочего листа действительно полезно, когда вы хотите, чтобы ваши макросы запускались, когда указанное событие происходит в Лист.
Популярные статьи:
link: / tips-if-condition-in-excel [Как использовать функцию ЕСЛИ в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если ЛОЖЬ .
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию VLOOKUP в Excel]: это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция панели инструментов. Это поможет вам суммировать значения для конкретных условий.
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.