Понастоящем можем динамично да променяме или актуализираме обобщени таблици, използвайки Excel таблици или динамични именовани диапазони. Но тези техники не са надеждни. Тъй като все пак ще трябва да опресните ръчно обобщената таблица. Ако имате големи данни, които съдържат хиляди редове и колони, таблиците на Excel няма да ви помогнат много. Вместо това ще направи файла ви тежък. Така че единственият начин остава VBA.
В тази статия ще научим как можем да накараме нашата обобщена таблица автоматично да промени източника на данни. С други думи, ние ще автоматизираме ръчния процес на промяна на източника на данни за динамично включване на нови редове и колони, добавени към изходните таблици и отразяващи незабавно промяната в обобщената таблица.
Запишете код в листа с изходни данни
Тъй като искаме това да бъде напълно автоматично, ще използваме листови модули за писане на код вместо основен модул. Това ще ни позволи да използваме събития от работен лист.
Ако изходните данни и обобщените таблици са в различни листове, ще напишем VBA кода, за да променим източника на данни от обобщена таблица в обекта на листа, който съдържа изходните данни (а не този, който съдържа обобщена таблица).
Натиснете CTRL+F11, за да отворите VB редактора. Сега отидете на Explorer Explorer и намерете листа, който съдържа изходните данни. Щракнете двукратно върху него.
Ще се отвори нова зона за кодиране. Може да не видите никаква промяна, но сега имате достъп до събитията на работния лист.
Щракнете върху лявото падащо меню и изберете работния лист. От лявото падащо меню изберете деактивиране. Ще видите празен подзапис, написан на кодовата област на работен лист_deativate. Нашият код за динамично променящи се изходни данни и опресняване на обобщена таблица ще влезе в този блок код. Този код ще се изпълнява всеки път, когато преминете от листа с данни към друг лист. Можете да прочетете за всички събития на работния лист тук.
Сега сме готови да приложим кода.
Изходен код за динамично обновяване на обобщена таблица с нов диапазон
За да обясня как работи, имам работна книга. Тази работна книга съдържа два листа. Sheet1 съдържа изходните данни, които могат да се променят. Sheet2 съдържа обобщена таблица, която зависи от изходните данни на sheet2.
Сега написах този код в областта за кодиране на sheet1. Използвам събитието Worksheet_Deactivate, така че този код да работи за актуализиране на обобщената таблица, когато преминаваме от изходния лист с данни.
Private Sub Worksheet_Deactivate () Dim pt Като PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Клетки (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Колона Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivot pc End Sub
Ако имате подобна работна книга, можете директно да копирате тези данни. Обясних, че този код работи по -долу.
Можете да видите ефекта от този код в gif по -долу.
Как този код автоматично променя изходните данни и актуализира обобщените таблици?
Преди всичко използвахме събитие на работен лист_деактивиране. Това събитие се задейства само когато листът, съдържащ кода, е превключен или деактивиран. Ето как кодът работи автоматично.
Сега, за да променим изходните данни на обобщената таблица, ние променяме данните в осевия кеш.
Осева таблица се създава с помощта на кеш кеш. Обобщеният кеш съдържа старите източници, докато осевата таблица не се опреснява ръчно или диапазонът на изходните данни не се променя ръчно.
Създадохме препратки към обобщени таблици с име pt, осев кеш с име pc и диапазон с име source_data. Изходните данни ще съдържат всички данни.
За динамично получаване на цялата таблица като диапазон от данни определяме последния ред и последната колона.
lstrow = Клетки (Rows.Count, 1) .End (xlUp) .Row
lstcol = клетки (1, колони. брой). край (xlToLeft). колона
Използвайки тези две числа, ние дефинираме source_data. Определено сме, че обхватът на изходните данни винаги ще започва от A1.
Задайте source_data = диапазон (клетки (1, 1), клетки (lstrow, lstcol))
Сега имаме изходните данни, които са динамични. Просто трябва да го използваме в обобщената таблица.
Ние съхраняваме тези данни в обобщен кеш, тъй като знаем, че в кеш кеша се съхраняват всички данни.
Задайте pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
След това дефинираме обобщената таблица, която искаме да актуализираме. Тъй като искаме да актуализираме обобщената таблица1 (име на обобщена таблица. Можете да проверите името на обобщената таблица в раздела за анализ, докато избирате обобщената таблица.) На лист1, ние задаваме точка, както е показано по -долу.
Задайте pt = Sheet2.PivotTables ("PivotTable1")
Сега просто използваме този обобщен кеш, за да актуализираме обобщената таблица. Използваме метода changePivotCache на pt обект.
pt.ChangePivotCache pc
И нашата автоматична таблица е автоматизирана. Това автоматично ще актуализира вашата обобщена таблица. Ако имате няколко таблици с един и същ източник на данни, просто използвайте един и същ кеш във всеки обект на обобщена таблица.
Така че да, момчета, ето как можете динамично да променяте обхвата на източника на данни в Excel. Надявам се да съм бил достатъчно обяснителен. Ако имате въпроси относно тази статия, уведомете ме в секцията за коментари по -долу.
Как автоматично да опреснявате обобщените таблици с помощта на VBA: За автоматично опресняване на вашите обобщени таблици можете да използвате VBA събития. Използвайте този прост ред код, за да актуализирате автоматично своята обобщена таблица. Можете да използвате някой от 3 метода за автоматично опресняване на обобщени таблици.
Стартирайте макрос, ако на листа са направени промени в посочен диапазон: Във вашите практики на VBA ще имате нужда да стартирате макроси, когато се промени определен диапазон или клетка. В този случай, за да стартираме макроси, когато се направи промяна в целевия диапазон, използваме събитието за промяна.
Стартирайте макрос, когато на листа се извърши някаква промяна | Така че, за да стартираме вашия макрос винаги, когато листът се актуализира, използваме събитията от работния лист на VBA.
Най -простият VBA код за маркиране на текущия ред и колона с помощта | Използвайте този малък VBA фрагмент, за да маркирате текущия ред и колона на листа.
Събития на работния лист в Excel VBA | Събитието на работния лист е наистина полезно, когато искате вашите макроси да се изпълняват, когато в листа настъпи определено събитие.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.