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