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