Excel VBA Refresh Pivot Table
Когато вмъкваме обобщена таблица в листа, след като данните се променят, данните на обобщената таблица не се променят, трябва да го направим ръчно, но във VBA има изявление за опресняване на обобщена таблица, което е expression.refreshtable , като използваме това, можем да опресним обобщена таблица, като се позоваваме на работния лист, състоящ се от нея, или можем да се позовем на всички обобщени таблици в работните листове и да ги опресним наведнъж.
Осевата таблица е жизненоважна при анализа на огромното количество данни. Помага от анализирането, обобщаването, както и от правилната интерпретация на данните. Един от проблемите с тази обобщена таблица обаче е, че тя няма да се обнови автоматично, ако има някаква промяна в изходните данни. Потребителят трябва да опресни обобщената таблица, като отиде до конкретната обобщена таблица всеки път, когато има промяна. Но кажете сбогом на ръчния процес, защото тук имаме метода за опресняване на обобщената таблица веднага щом направите някаква промяна в обобщената таблица.
Как да опреснявам автоматично VBA код на данните от обобщената таблица?
Единственият момент, когато трябва да се актуализира обобщената таблица, е всеки път, когато има някаква промяна в изходните данни на обобщената таблица, към която се отнасяме.
Например погледнете данните по-долу и обобщена таблица.
Сега ще сменя числата в изходните данни, т.е. от А1 на В17.
В клетка B9 трябва да променя стойността от 499 на 1499, т.е. 1000 увеличение на данните, но ако погледнете въртенето, все пак показва резултата като 4295 вместо 5295. Трябва да обновя ръчно моята обобщена таблица, за да актуализирам обобщена таблица.
За да преодолеем този проблем, трябва да напишем прост код на макрос на Excel, за да опресним обобщената таблица, когато има някаква промяна в изходните данни.
# 1 - Прост макрос за опресняване на цялата таблица
Стъпка 1: Променете събитието на листа с данни
Трябва да задействаме събитието за промяна в листа с данни. В редактора на visual basic кликнете два пъти върху листа с данни.
След като щракнете двукратно върху листа, изберете „Работен лист“ и изберете събитието като „Промяна“.
Ще видите автоматична подпроцедура, отворена като Worksheet_Change (ByVal Target As Range)
Стъпка 2: Използвайте обект на работен лист
Обърнете се към листа с данни с помощта на обекта Worksheets.
Стъпка 3: Вижте обобщена таблица по име
Обърнете се към името на обобщената таблица с името на обобщената таблица.
Стъпка 4: Използвайте метода Refresh Table
Изберете метода като „Refresh Table“.
Сега този код ще опресни обобщената таблица „Обобщена таблица1“, когато има някаква промяна в изходния лист с данни. Можете да използвате кода по-долу. Трябва само да промените името на обобщената таблица.
Код:
Частен работен лист_Промяна (Работен лист за целта като обхват от ByVal) ("Лист с данни"). Обобщени таблици ("Обобщена таблица1"). Опресняване
# 2 - Опресняване на всички обобщени таблици от същия работен лист
Ако имате много обобщени таблици в един и същ работен лист, можете да опресните всички обобщени таблици само с едно щракване. Използвайте кода по-долу, за да опресните всички обобщени таблици в листа.
Код:
Sub Refresh_Pivot_Tables_Example1 () Работни листове ("Лист с данни"). Изберете с ActiveSheet .PivotTables ("Таблица1"). RefreshTable .PivotTables ("Таблица2"). RefreshTable .PivotTables ("Таблица3"). RefreshTable .Pivot4 "Таблици." RefreshTable .PivotTables ("Таблица5"). RefreshTable End With End Sub
Трябва да промените името на работния лист и имената на обобщената таблица според данните на работния ви лист.
# 3 - Опреснете всички таблици в работната книга
Малко вероятно е всички обобщени таблици да са на един и същ работен лист. Обикновено за всеки отчет се опитваме да добавим отделни обобщени таблици в отделни листове. В тези случаи не можем да продължим да пишем кода за всяка осева таблица, която трябва да се обнови.
И така, това, което можем да направим, е с един код, използвайки цикли, можем да прегледаме всички обобщени таблици в работната книга и да ги опресним с едно щракване на бутона.
Кодът по-долу ще премине през всяка обобщена таблица и ще ги освежи.
Код 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT като обобщена таблица за всяка PT в ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub
Код 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC като PivotCache за всеки компютър в ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
И двата кода ще направят освежаване на обобщените таблици.
Ако искате обобщената таблица да се обнови веднага след като има някаква промяна в листа с данни на въртящия се лист, трябва да копирате и поставите горните кодове в събитието Промяна на работния лист в тази работна книга.
# 4 - Избягвайте времето за зареждане, като използвате събитие за деактивиране на работен лист
Когато използваме събитието „Промяна на работен лист“, то продължава да се опреснява дори когато няма промяна в източника на данни, но ако се случи някаква промяна в работния лист.
Дори ако въведете една единствена точка в работния лист, той се опитва да опресни обобщената таблица. За да избегнем това, можем да използваме метода „Деактивиране на работен лист“ вместо метода „Промяна на работен лист“.
Деактивирайте актуализациите на събитията в обобщената таблица при преместване от един лист на друг лист.
