Както всички знаем, когато правим промени в изходните данни на обобщена таблица, това не се отразява веднага в обобщената таблица. Трябва да опресним обобщените таблици, за да видим промените. И ако изпратите актуализиран файл, без да опреснявате обобщените таблици, може да почувствате неудобство.
Така че в тази статия ще научим как автоматично да опресняваме обобщена таблица, използвайки VBA. Този начин е по -лесен, отколкото си представяте.
Това е простият синтаксис за автоматично опресняване на обобщени таблици в работната книга.
"Код в изходния лист с данни Обект частен под работен лист_деактивиране () име на лист_на_осева_таблица. Обобщени таблици (" име на_оборна_таблица "). PivotCache.Refresh End Sub
Какво представляват Pivot Caches?
Всяка обобщена таблица съхранява данните в осевия кеш. Ето защо Pivot може да показва предишни данни. Когато опресняваме обобщените таблици, той актуализира кеша с нови изходни данни, за да отразява промените в обобщената таблица.
Така че просто се нуждаем от макрос, за да опресним кеша на обобщените таблици. Ще направим това с помощта на събитие на работен лист, така че да не се налага да стартираме макроса ръчно.
Къде да кодирате за автоматично опресняване на обобщените таблици?
Ако вашите изходни данни и обобщените таблици са в различни листове, тогава кодът VBA трябва да бъде в листа с изходни данни.
Тук ще използваме Worksheet_SelectionChange Event. Това ще накара кода да се изпълнява всеки път, когато преминем от изходния лист с данни към друг лист. По -късно ще обясня защо използвах това събитие.
Тук имам изходни данни в sheet2 и обобщени таблици в sheet1.
Отворете VBE с помощта на CTRL+F11. В Explorer Explorer можете да видите три обекта, Sheet1, Sheet2 и Workbook.
Тъй като Sheet2 съдържа изходните данни, щракнете двукратно върху обект sheet2.
Сега можете да видите две падащи менюта в горната част на кодовата област. От първото падащо меню изберете работния лист. И от второто падащо меню изберете Деактивиране. Това ще вмъкне празно подиме Worksheet_Deactivate. Нашият код ще бъде написан в тази подточка. Всички редове, написани в тази подпозиция, се изпълняват веднага щом потребителят премине от този лист към друг лист.
На лист 1 имам две обобщени таблици. Искам да опресня само една обобщена таблица. За това трябва да знам името на обобщената таблица. За да знаете името на която и да е обобщена таблица, изберете всяка клетка в тази обобщена таблица, отидете на раздела за анализ на осева таблица. От лявата страна ще видите името на обобщената таблица. Можете също да промените името на обобщената таблица тук.
Сега знаем името на обобщената таблица, можем да напишем обикновен ред за опресняване на обобщената таблица.
Частен под работен лист_Деактивиране () лист 1. Обобщени таблици ("Обобщена таблица1"). PivotCache.Refresh Краен подмен
И е направено.
Сега, когато превключвате от изходните данни, този vba код ще се изпълнява за опресняване на обобщената таблица1. Както можете да видите в gif по -долу.
Как да опресните всички обобщени таблици в работната книга?
В горния пример искахме само да опресним една конкретна обобщена таблица. Но ако искате да опресните всички обобщени таблици в работна книга, просто трябва да направите леки промени в кода си.
Private Sub Worksheet_Deactivate () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh За всеки компютър в ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
В този код използваме цикъл For, за да преминем през всеки пивот кеш в работната книга. Обектът ThisWorkbook съдържа всички сводни кешове. За достъп до тях използваме ThisWorkbook.PivotCaches.
Защо да използвате събитие Worksheet_Deactivate?
Ако искате да опресните обобщената таблица веднага щом се направят промени в изходните данни, трябва да използвате събитието Worksheet_Change. Но не го препоръчвам. Това ще накара вашата работна книга да изпълнява кода всеки път, когато направите някаква промяна в листа. Може да се наложи да направите стотици промени, преди да искате да видите резултата. Но Excel ще опреснява обобщената таблица при всяка промяна. Това ще доведе до загуба на време и ресурси за обработка. Така че, ако имате обобщени таблици и данни в различни листове, е по -добре да използвате събитие за деактивиране на работен лист. Позволява ви да довършите работата си. След като преминете към листове с обобщена таблица, за да видите промените, той изменя промените.
Ако имате обобщени таблици и изходни данни на един и същи лист и искате осевите таблици да се опресняват автоматично, може да искате да използвате събитие Worksheet_Change.
Частен под работен лист_Промяна (ByVal Target As Range) Лист 1. Пивотаблици ("PivotTable1"). PivotCache.Refresh End Sub
Как да опресните всичко в работните книги, когато се направят промени в изходните данни?
Ако искате да опресните всичко в работна книга (диаграми, обобщени таблици, формули и т.н.), можете да използвате командата ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change (ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Моля, обърнете внимание, че този код не променя източника на данни. Така че, ако добавите данни под изходните данни, този код няма да включва тези данни автоматично. Можете да използвате Excel таблици за съхраняване на изходни данни. Ако не искате да използвате таблици, можем да използваме VBA и за включване на нови данни. Ще го научим в следващия урок.
Така че да колега, ето как можете автоматично да опресните обобщените таблици в Excel. Надявам се да съм бил достатъчно обяснителен и тази статия ви е послужила добре. Ако имате въпроси по тази тема, можете да ме попитате в секцията за коментари по -долу.
Как да актуализирате динамично диапазона на източника на данни от обобщена таблица в Excel: За динамично промяна на обхвата на изходните данни на обобщени таблици използваме обобщени кешове. Тези няколко реда могат динамично да актуализират всяка обобщена таблица, като променят диапазона на изходните данни. Във VBA използвайте обекти на обобщени таблици, както е показано по -долу …
Стартирайте макрос, ако на листа са направени промени в посочен диапазон: Във вашите практики на VBA ще имате нужда да стартирате макроси, когато се промени определен диапазон или клетка. В този случай, за да стартираме макроси, когато се направи промяна в целевия диапазон, използваме събитието за промяна.
Стартирайте макрос, когато на листа се извърши някаква промяна | Така че, за да стартираме вашия макрос винаги, когато листът се актуализира, използваме събитията от работния лист на VBA.
Най -простият VBA код за маркиране на текущия ред и колона с помощта | Използвайте този малък VBA фрагмент, за да маркирате текущия ред и колона на листа.
Събития на работния лист в Excel VBA | Събитието на работния лист е наистина полезно, когато искате вашите макроси да се изпълняват, когато в листа настъпи определено събитие.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.