В тази статия ще научим как автоматично да опреснявате данните от обобщената таблица в Excel.
Сценарий:
Както всички знаем, когато правим промени в изходните данни на обобщена таблица, това не се отразява веднага в обобщената таблица. Трябва да опресним обобщените таблици, за да видим промените при всяко отваряне на работна книга на Excel. И ако изпратите актуализиран файл, без да опреснявате обобщените таблици, може да почувствате неудобство. Така че научете тук как да намерите опцията за опресняване, когато използвате обобщена таблица
Опреснете данните при отваряне на файл в Excel
Първо създайте обобщена таблица и след това щракнете с десния бутон върху всяка клетка на обобщена таблица.
Отидете на Опции за обобщена таблица> раздел Данни> Поставете отметка в квадратчето, в което се казва Опреснете данните при отваряне на файл
Това ще позволи автоматично актуализиране на данните всеки път, когато файлът бъде отворен.
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме някои данни и първо трябва да създадем обобщена таблица и след това да намерим опциите за активиране на автоматичната актуализация на обобщените таблици.
Създайте обобщена таблица и след това щракнете с десния бутон върху всяка клетка на обобщена таблица, както е показано по -долу.
Изберете Опции за обобщена таблица и това ще отвори диалоговия прозорец Опции за обобщена таблица.
Изберете раздела с данни и след това поставете отметка в квадратчето, което казва Опресняване на данните при отваряне на файла. Можете да извършите това, без да отваряте и затваряте файла с помощта на VBA.
Използване на VBA
Така че тук ще научим как автоматично да опресняваме обобщена таблица, използвайки VBA. Този начин е по -лесен, отколкото си представяте.
Това е простият синтаксис за автоматично опресняване на обобщени таблици в работната книга.
| „Код в обекта на изходния лист
Частен под работен лист_Deactivate () Таблица с име_на_осв.Основни таблици ("pivot_table_name") .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 имам две обобщени таблици. Искам да опресня само една обобщена таблица. За това трябва да знам името на обобщената таблица. За да знаете името на която и да е обобщена таблица, изберете всяка клетка в тази обобщена таблица, отидете на раздела за анализ на осева таблица. От лявата страна ще видите името на обобщената таблица. Можете също да промените името на обобщената таблица тук.
Сега знаем името на обобщената таблица, можем да напишем обикновен ред за опресняване на обобщената таблица.
| Частен под работен лист_Deactivate ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
И е направено.
Сега, когато превключвате от изходните данни, този vba код ще се изпълнява за опресняване на обобщената таблица1. Както можете да видите в gif по -долу.
Как да опресните всички обобщени таблици в работната книга?
В горния пример искахме само да опресним една конкретна обобщена таблица. Но ако искате да опресните всички обобщени таблици в работна книга, просто трябва да направите леки промени в кода си.
| Частен под работен лист_Deactivate ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh За всеки компютър в ThisWorkbook.PivotCaches pc.Опресняване Следващ компютър End Sub |
В този код използваме цикъл For, за да преминем през всеки пивот кеш в работната книга. Обектът ThisWorkbook съдържа всички сводни кешове. За достъп до тях използваме ThisWorkbook.PivotCaches.
Защо да използвате събитие Worksheet_Deactivate?
Ако искате да опресните обобщената таблица веднага щом се направят промени в изходните данни, трябва да използвате събитието Worksheet_Change. Но не го препоръчвам. Това ще накара вашата работна книга да изпълнява кода всеки път, когато направите някаква промяна в листа. Може да се наложи да направите стотици промени, преди да искате да видите резултата. Но Excel ще опреснява обобщената таблица при всяка промяна. Това ще доведе до загуба на време и ресурси за обработка. Така че, ако имате обобщени таблици и данни в различни листове, е по -добре да използвате събитие за деактивиране на работен лист. Позволява ви да довършите работата си. След като преминете към листове с обобщена таблица, за да видите промените, той изменя промените.
Ако имате обобщени таблици и изходни данни на един и същи лист и искате осевите таблици да се опресняват автоматично, може да искате да използвате събитие Worksheet_Change.
| Частен под работен лист_Смяна (ByVal Target As Range)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
Как да опресните всичко в работните книги, когато се направят промени в изходните данни?
Ако искате да опресните всичко в работна книга (диаграми, обобщени таблици, формули и т.н.), можете да използвате командата ThisWorkbook.RefreshAll.
| Частен под работен лист_Смяна (ByVal Target As Range)
ThisWorkbook.RefreshAll End Sub |
Забележка : Кодът не променя източника на данни. Така че, ако добавите данни под изходните данни, този код няма да включва тези данни автоматично. Можете да използвате Excel таблици за съхраняване на изходни данни. Ако не искате да използвате таблици, можем да използваме VBA и за включване на нови данни. Ще го научим в следващия урок.
Надявам се, че тази статия за това как автоматично да опреснявате данните от обобщена таблица в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Как да актуализирате динамично диапазона на източника на данни от обобщена таблица в Excel : За динамично промяна на обхвата на изходните данни на обобщени таблици използваме обобщени кешове. Тези няколко реда могат динамично да актуализират всяка обобщена таблица, като променят диапазона на изходните данни. Във VBA използвайте обекти на обобщени таблици, както е показано по -долу …
Стартирайте макрос, ако на листа са направени промени в посочен диапазон : Във вашите практики на VBA ще имате нужда да стартирате макроси, когато се промени определен диапазон или клетка. В този случай, за да стартираме макроси, когато се направи промяна в целевия диапазон, използваме събитието за промяна.
Стартирайте макрос, когато на листа се извърши някаква промяна : Така че, за да стартираме вашия макрос винаги, когато листът се актуализира, използваме събитията на работния лист на VBA.
Най -простият VBA код за маркиране на текущия ред и колона с помощта : Използвайте този малък VBA фрагмент, за да маркирате текущия ред и колона на листа.
Събития на работния лист в Excel VBA : Събитието на работния лист е наистина полезно, когато искате вашите макроси да се изпълняват, когато на листа се появи определено събитие.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнявайте задачите си в Excel. Тези преки пътища ще ви помогнат да увеличите ефективността на работата си в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
