Оновити зведену таблицю в VBA

Ми зазвичай створюємо зведену таблицю, коли нам потрібно створити якісь графіки або діаграми або нам потрібно виконати аналіз. Готуючи таблицю зведення, ми можемо отримати загальний вигляд та уявлення про те, що є насправді всередині даних. Це найкращий спосіб знайти спосіб потрапляння в дані. І кожного разу, коли ми вносимо будь-які зміни в дані, нам також потрібно оновлювати зведену таблицю. Так що він також заповнить оновлений кількість даних. Оновити будь-яку зведену таблицю дуже просто, але що робити, якщо у нас в одному файлі excel є 10 секунд зведених таблиць, які нам потрібно оновити. Отже, замість того, щоб оновлювати всі поворотні таблиці одна за одною, ми можемо безпосередньо оновити всі зведені таблиці за один раз за допомогою VBA Refresh Pivot Table.

Як використовувати оновлення зведеної таблиці в Excel VBA?

Нижче наведено різні приклади використання Refresh Pivot Table в Excel з використанням коду VBA.

Ви можете завантажити цей шаблон Excel для оновлення зведеної таблиці VBA тут - VBA Refresh Pivot Table Excel Шаблон

VBA Refresh Pivot Table - Приклад №1

Для оновлення зведеної таблиці спочатку нам потрібно створити зведену таблицю. Нижче ми маємо дані рядка-позиції 1000с, за допомогою якого будемо створювати зведену таблицю.

На наведеному вище скріншоті ми можемо побачити останній рядок у 1001, і ці дані містять інформацію про клієнтів у порівнянні з проданою їм кількістю.

Тепер перейдіть на вкладку меню " Вставка" і натисніть на пункт " Поворотний стіл ".

Ми отримаємо вікно Create PivotTable. Звідти спочатку вибираємо діапазон таблиці, який ми хочемо включити до зведеної таблиці. Потім виберіть будь-яке місце в поточному аркуші для таблиці зведення. Ми також можемо вибрати Новий робочий аркуш.

Після завершення натисніть кнопку Ок.

Ми отримаємо створену зведену таблицю. Тепер перетягніть необхідні поля в різні області, щоб отримати фактичну зведену таблицю. Тут ми перетягуємо ім’я Клієнта у ROWS та Кількість у COLUMNS, як показано нижче.

На цьому завершується створення зведеної таблиці. Але нам потрібно оновити зведену таблицю, змінивши що-небудь у Дані, це можна зробити простим оновленням опції зі списку меню, що випадає правою кнопкою миші, як показано нижче.

Але той же процес може бути автоматизований і через VBA. Для цього нам знадобиться Модуль. Так,

Крок 1: Перейдіть на вкладку меню Вставка та виберіть параметр Модуль зі спадного списку, як показано нижче.

Крок 2: У щойно відкритому модулі напишіть підкатегорію VBA Pivot Refresh або ми можемо вибрати будь-яке ім'я на наш вибір.

Код:

 Sub Pivot_Refresh2 () Кінець Sub

Крок 3: Спочатку визначте змінну як PivotCache, як показано нижче. PivotCache використовує кешовий згорток, а не дані, що використовуються для створення таблиці зведення.

Код:

 Sub Pivot_Refresh2 () Темна таблиця як кінець PivotCache Sub

Крок 4: Тепер ми будемо використовувати цикл For- Every. Тому відкрийте цикл For-Every, як показано нижче.

Код:

 Sub Pivot_Refresh2 () Розмір таблиці як PivotCache для кожної наступної кінця таблиці Sub

Крок 5: Тепер всередині циклу For-End ми запишемо умову, коли ми виберемо відкритий в даний час робочий аркуш, який має таблицю зведення з PivotCache .

Код:

 Sub Pivot_Refresh2 () Темна таблиця як PivotCache для кожної таблиці в цій книзі.

Крок 6: Тепер ми будемо використовувати команду Refresh, призначивши їй визначену змінну Таблиця .

Код:

 Sub Pivot_Refresh2 () Темна таблиця як зведений кеш для кожної таблиці в цій книзі праці.

Крок 7: Цим завершується код, тепер компілюйте код для помилки, натиснувши функціональну клавішу F8. Тепер, щоб перевірити, чи працює написаний код на ні, ми змінили кількість клієнта14 на 69 .

Крок 8: Тепер запустіть код. Ми побачимо, що загальна кількість проти клієнта14 оновлюється до 2183, що виділено жовтим кольором.

VBA Refresh Pivot Table - Приклад №2

Існує ще один спосіб оновлення таблиці Pivot через VBA. Перш ніж рухатися вперед, ми можемо розглянути можливість зміни назви зведеної таблиці або також використовувати ім'я за замовчуванням. Спробуємо дати нову назву таблиці Зворотна. Для цього виберіть зведену таблицю і перейдіть на вкладку меню Аналіз, як показано нижче.

У першому розділі PivotTable Name, ми бачимо, ім'я за замовчуванням як PivotTable1 .

Тепер ми змінимо цю назву. Подумайте про те, як записати нове ім’я як Дані клієнта, як показано нижче

Крок 1: Після цього відкрийте новий модуль і напишіть підкатегорію VBA Refresh, як показано нижче.

Код:

 Sub Pivot_Refresh3 () Кінець Sub

Крок 2: Тепер визначте змінну як PivotTable . Тут, з PivotTable, ми розглянемо повні вихідні дані.

Код:

 Sub Pivot_Refresh3 () Розмірна таблиця як кінець зведеної таблиці Sub

Крок 3: Тепер використовуйте Set із визначеною змінною та виберіть аркуш, який відкривається в даний час.

Код:

 Sub Pivot_Refresh3 () Дім Таблиця як Таблиця встановлення зведеної таблиці = ActiveSheet. Кінець Під

Крок 4: Виберіть ім'я зведеної таблиці, яку ми хочемо оновити разом із змінним типом даних, який ми використовували як PivotTable .

Код:

 Sub Pivot_Refresh3 () Розмірна таблиця як таблиця набору зведених таблиць = ActiveSheet.PivotTables ("Дані про клієнта") Кінець Sub

Крок 5: Нарешті, використовуйте змінну з функцією RefreshTable, щоб виконати її.

Код:

 Sub Pivot_Refresh3 () Дім Таблиця як таблиця набору зведених таблиць = ActiveSheet.PivotTables ("Дані про клієнта").

Крок 6: Зробимо ще кілька змін у даних, щоб дати реальну візуальність застосованого коду.

Ми змінили кількість клієнтів2 на 56 . Тепер, якщо ми запустимо код, у зведеній таблиці Імені Клієнта повинні бути зміни суми кількості для Клієнта2 .

Крок 7: Тепер перейдіть до вікна VBA і складіть код. Якщо помилки не знайдено, запустіть її, натиснувши кнопку відтворення, що знаходиться під рядком меню, як показано нижче. Ми помітимо, що кількість проданої для Customer2 тепер збільшується до 1724 року, вносячи зміни у вихідну таблицю.

Цим ми можемо включити декілька вихідних даних і створити іншу зведену таблицю. А також автоматизувати ці зведені таблиці легко, оскільки нам просто потрібно включити назву зведеної таблиці та аркуш, де знаходиться таблиця.

Плюси VBA Refresh Pivot Table

  • Оновлення зведеної таблиці за кодом VBA потребує зовсім менше часу.
  • Це дуже просто здійснити.

Що потрібно пам’ятати

  • Ми можемо додати декілька таблиць джерел даних та автоматизувати їх за кодом VBA.
  • Щоб побачити зміни відбулися, тримайте вікно VBA та лист Excel паралельно один одному.
  • Краще називати кожну зведену таблицю, якщо ви маєте справу з кількома джерелами даних та зведеними таблицями.
  • Код із назвою зведеної таблиці легко зрозуміти, відстежувати та знаходити.

Рекомендовані статті

Це посібник з VBA Refresh Pivot Table. Тут ми обговорюємо, як використовувати Excel VBA Refresh Pivot Table разом із практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. VBA FileSystemObject (FSO)
  2. Функція Substring Excel
  3. Довжина струни VBA
  4. Формула ЕКСЕЛЬНОГО РОЗМІРУ

Категорія:

VBA