Тренутно можемо динамички мењати или ажурирати изведене табеле помоћу Екцел табела или динамичких именованих опсега. Али ове технике нису сигурне. Како ћете и даље морати ручно освежавати изведену табелу. Ако имате велике податке који садрже хиљаде редова и колона, Екцел табеле вам неће много помоћи. Уместо тога, то ће учинити вашу датотеку тешком. Дакле, једини начин остаје ВБА.
У овом чланку ћемо научити како можемо учинити да наша изведена табела аутоматски промени извор података. Другим речима, аутоматизоваћемо ручни процес промене извора података тако да динамички укључује нове редове и колоне додате изворним табелама и тренутно одражава промене у изведеној табели.
Упишите код у лист са изворним подацима
Пошто желимо да ово буде потпуно аутоматско, користићемо модуле за писање кода уместо основног модула. То ће нам омогућити да користимо догађаје на радном листу.
Ако су изворни подаци и изведене табеле на различитим листовима, написаћемо ВБА код за промену извора података изведене табеле у објекту листа који садржи изворне податке (не који садржи изведену табелу).
Притисните ЦТРЛ+Ф11 да бисте отворили ВБ едитор. Сада идите на Пројецт Екплорер и пронађите лист који садржи изворне податке. Двапут кликните на њу.
Отвориће се нова област за кодирање. Можда нећете видети промене, али сада имате приступ догађајима на радном листу.
Кликните на леви падајући мени и изаберите радни лист. На левом падајућем менију изаберите деактивирај. Видећете празан подписник написан на радном листу_деативате назива поља кода. Наш код за динамичку промену изворних података и освежавање заокретне табеле иде у овај блок кода. Овај код ће се покретати кад год пређете са листа са подацима на било који други лист. Овде можете прочитати о свим догађајима на радном листу.
Сада смо спремни за примену кода.
Изворни код за динамичко ажурирање изведене табеле са новим опсегом
Да бих објаснио како то функционише, имам радну свеску. Ова радна свеска садржи два листа. Лист 1 садржи изворне податке који се могу променити. Лист 2 садржи изведену табелу која зависи од изворних података листа 2.
Сада сам овај код написао у области кодирања листа 1. Користим догађај Ворксхеет_Деацтивате, тако да се овај код покреће ради ажурирања изведене табеле кад год пређемо са изворног листа са подацима.
Приватни под -радни лист_Деацтивате () Дим пт Као заокретна табела Дим пц Као ПивотЦацхе Дим извор_дата као опсег лстров = ћелије (Ровс.Цоунт, 1) .Енд (клУп) .Ров лстцол = Целлс (1, Цолумнс.Цоунт) .Енд (клТоЛефт). Колона Сет соурце_дата = Ранге (Целлс (1, 1), Целлс (лстров, лстцол)) Сет пц = ТхисВоркбоок.ПивотЦацхес.Цреате (клДатабасе, СоурцеДата: = соурце_дата) Сет пт = Схеет2.ПивотТаблес ("ПивотТабле1") пт.ЦхангеПивот пц Енд Суб
Ако имате сличну радну свеску, можете директно копирати ове податке. У наставку сам објаснио да овај код функционише.
Ефекат овог кода можете видети на гиф -у испод.
Како овај код аутоматски мења изворне податке и ажурира изведене табеле?
Прво смо користили догађај ворксхеет_деацтивате. Овај догађај се активира само када се лист са кодом пребаци или деактивира. Дакле, код се аутоматски покреће.
Сада за промену изворних података изведене табеле мењамо податке у изведеној кеш меморији.
Заокретна табела се креира помоћу изведене кеш меморије. Пивот кеш садржи старе изворне податке све док се изведена табела не ручно освежи или док се опсег изворних података ручно не промени.
Направили смо референце назива заокретних табела пт, заокретне кеш меморије са именом пц и опсега који се зове соурце_дата. Изворни подаци ће садржати целе податке.
Да бисмо динамички добили целу табелу као опсег података, одређујемо последњи ред и последњу колону.
лстров = Ћелије (Ровс.Цоунт, 1) .Енд (клУп) .Ров
лстцол = Ћелије (1, Колоне.Број). Крај (клТоЛефт). Колона
Помоћу ова два броја дефинишемо извор_податке. Сигурни смо да ће распон изворних података увек почети од А1.
Подеси соурце_дата = Опсег (ћелије (1, 1), ћелије (лстров, лстцол))
Сада имамо изворне податке који су динамични. Само га требамо користити у изведеној табели.
Чувамо ове податке у заокретној кеш меморији јер знамо да заокретна кеш меморише све податке.
Подесите пц = ТхисВоркбоок.ПивотЦацхес.Цреате (клДатабасе, СоурцеДата: = соурце_дата)
Затим дефинишемо изведену табелу коју желимо да ажурирамо. Пошто желимо да ажурирамо изведену табелу1 (назив заокретне табеле. Можете проверити име заокретне табеле на картици за анализу док бирате заокретну табелу.) На листу 1, поставили смо пт као што је приказано испод.
Подесите пт = Схеет2.ПивотТаблес ("ПивотТабле1")
Сада једноставно користимо ову изведену кеш меморију за ажурирање изведене табеле. Користимо цхангеПивотЦацхе метод пт објекта.
пт.ЦхангеПивотЦацхе пц
И имамо заокретну табелу аутоматизовану. Ово ће аутоматски ажурирати вашу изведену табелу. Ако имате више табела са истим извором података, само користите исти кеш у сваком објекту изведене табеле.
Па да, момци, ево како можете динамички променити опсег извора података у Екцелу. Надам се да сам довољно објаснио. Ако имате питања у вези са овим чланком, јавите ми у одељку за коментаре испод.
Како аутоматски освежити заокретне табеле помоћу ВБА: За аутоматско освежавање изведених табела можете користити ВБА догађаје. Користите ову једноставну линију кода за аутоматско ажурирање изведене табеле. Можете користити било који од 3 метода аутоматског освежавања изведених табела.
Покрените макро ако је на листу унета било каква промена у наведеном опсегу: У вашим ВБА поступцима, имали бисте потребу да покрећете макрое када се промени одређени опсег или ћелија. У том случају, за покретање макроа када се изврши промена циљног опсега, користимо догађај цханге.
Покрени макро када дође до било какве промене на листу | Дакле, за покретање вашег макроа кад год се лист ажурира, користимо ВБА догађаје радног листа.
Најједноставнији ВБА код за означавање тренутног реда и колоне помоћу | Користите овај мали ВБА исечак да означите тренутни ред и колону листа.
Догађаји радног листа у програму Екцел ВБА | Догађај радног листа је заиста користан када желите да се ваши макрои покрену када се на листу појави одређени догађај.
Популарни чланци:
50 Екцел пречица за повећање продуктивности | Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.Функција ВЛООКУП у Екцелу | Ово је једна од најчешће кориштених и популарних функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
ЦОУНТИФ у програму Екцел 2016 | Бројте вредности са условима користећи ову невероватну функцију. Не морате филтрирати податке да бисте рачунали одређену вредност. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел | Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.