Kontingenčná tabuľka VBA Ako vytvoriť kontingenčnú tabuľku v programe Excel pomocou kódu VBA?

• Kontingenčná tabuľka Excel VBA

Kontingenčná tabuľka Excel VBA

Kontingenčná tabuľka VBA vám pomáha zhrnúť správy z veľkej množiny údajov. Pivot môže považovať za malú formu celého súboru údajov. Rýchly prehľad veľkých množín údajov je možný prostredníctvom otočnej tabuľky. Kontingenčná tabuľka je jednoduchý spôsob, ako zodpovedajúcim spôsobom filtrovať údaje. Z dostupných údajov môžete zvýrazniť údaje požadovaným spôsobom. Kontingenčná tabuľka vám umožňuje kombinovať obrovské údaje, analyzovať údaje a vytvárať správy, ktoré spĺňajú vaše obchodné požiadavky.

Excel ponúka vstavanú kontingenčnú tabuľku, ktorú je možné ľahko vytvoriť aplikáciou kontingenčnej tabuľky na datasheet. Je možné vygenerovať správu automaticky v exceli po dodaní údajov. Kódy VBA vám pomôžu vytvoriť automatickú kontingenčnú tabuľku.

Ako vytvoriť kontingenčnú tabuľku pomocou Excel VBA?

Bežné kroky na vloženie kontingenčnej tabuľky je najprv vloženie kontingenčnej tabuľky z ponuky Vložiť a potom vyberte tabuľky, ktoré chcete zmeniť, do kontingenčnej tabuľky. Vybraná tabuľka sa stane zdrojovými údajmi a kontingenčná tabuľka sa vytvorí zodpovedajúcim spôsobom.

Ak chcete zostaviť kontingenčnú správu, musíte vybrať polia do filtrov, hodnôt atď. Podobným spôsobom je možné tieto kroky automatizovať pomocou kódov VBA. Aby sme mohli pracovať s kontingenčnou tabuľkou, potrebujeme excelentné dáta. Naučme sa, ako vytvoriť kontingenčnú tabuľku pomocou kódov Excel VBA.

Túto šablónu tabuľky kontingenčných tabuliek VBA si môžete stiahnuť tu - šablónu tabuľky kontingenčných tabuliek VBA

Krok 1 : Kontingenčná tabuľka by sa mala považovať za referenčný objekt. Vytvorte objekt pre kontingenčnú tabuľku v ďalších kódoch, ktoré sa tento objekt použije na nasmerovanie kontingenčnej tabuľky. Pomenujte premennú ako pvtable. Vyhlásenie objektu funkcie a kontingenčnej tabuľky.

kód:

 Sub PivotTable () Dim pvtable As PivotTable End Sub 

Krok 2: Na uchovávanie dátových súborov potrebujeme pivotnú vyrovnávaciu pamäť, ktorá deklaruje zdroj údajov. Pred vytvorením kontingenčnej tabuľky deklarujte kontingenčnú premennú hotovosti. Vyhlásiť premennú pvcache pre vyrovnávaciu pamäť pivotov.

kód:

 Dim pvcache As PivotCache 

Krok 3: Údaje sú v pracovnom hárku a mali by ste špecifikovať rozsah zasiahnutia bunky, ktorú potrebujete. V kontingenčnej tabuľke sú zdrojové údaje rozložené ako riadky a stĺpce, takže na nasmerovanie konkrétneho rozsahu potrebujeme premennú. Definujte pvrange ako premennú rozsahu.

kód:

 Dim pvrange As Range 

Krok 4: Pracovný list musí vložiť kontingenčnú tabuľku, ktorú musíte vytvoriť. Vyhlásiť premennú ako pracovný hárok. Definujte pvsheet ako pracovný hárok.

kód:

 Dim pvsheet As Worksheet 

Krok 5: Potrebujete podobnú premennú na použitie údajového listu, ktorý by mal obsahovať údaje, ktoré chcete vykresliť ako kontingenčnej tabuľky. Premenná údajového listu je teda deklarovaná ako pdsheet.

kód:

 Dim pdsheet As Worksheet 

Krok 6: Potrebujete ďalšie dve premenné ako dlhý dátový typ na označenie posledného použitého riadku a stĺpca na vytvorenie kontingenčnej tabuľky. Môže to byť ľubovoľný riadok alebo stĺpec, takže existuje pravdepodobnosť, že počet riadkov a stĺpcov prekročí limit celého typu údajov pre celé číslo. Nazvime to plr a plc.

kód:

 Dim plr As Long Dim plc As Long 

Krok 7: Ďalším krokom je odstránenie, ak už existuje nejaká kontingenčná tabuľka. Pomôže to predísť zámene, v ktorej tabuľke sú zdrojové údaje, ktoré sa majú vykresliť. Vymažte predchádzajúci list kontingenčnej tabuľky a vytvorte nový hárok na vloženie kontingenčnej tabuľky.

kód:

 Pri chybe pokračovať ďalej Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Vymazať ', ak chcete odstrániť existujúcu kontingenčnú tabuľku v hárkoch Worksheet.Add After: = ActiveSheet' a pridať nový pracovný hárok ActiveSheet.Name = " pvsheet "'na premenovanie listu na" pvsheet "pri chybe GoTo 0 

Krok 8: Priradte objektovú premennú pre otočný hárok a údajový hárok do premenných pvsheet a pdsheet. Toto sa bude ďalej používať na špecifikáciu pracovných hárkov.

kód:

 Nastaviť pvsheet = Pracovné hárky ("pvsheet") Nastaviť pdsheet = Pracovné hárky ("pdsheet") 

Krok 9: Keď sú pracovné hárky nastavené, ďalšou položkou je, že potrebujeme posledný použitý riadok a stĺpec na vytvorenie kontingenčnej správy. Nájdite posledný použitý riadok a stĺpec pomocou deklarovaných premenných plr a plc.

kód:

 'dve premenné nájsť Posledný použitý riadok a stĺpec v pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 

Krok 10: Ako sme už spomenuli, údaje v pracovnom hárku sú špecifikované rozsahom buniek. V ďalšom kroku musíte nastaviť rozsah otáčania. Už je deklarované ako premenná na použitie rozsahu otáčok „pvrange“.

kód:

 'inicializácia rozsahu dát kontingenčnej tabuľky Nastaviť pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc) 

Pretože rozsah je nastavený pomocou vlastnosti resize of range range, zmení sa veľkosť pvrange podľa toho upraví rozsah pivotov. Takže nastavenie pvrange sa uskutoční, ak dôjde k nejakému pridaniu alebo odstráneniu riadkov alebo stĺpcov.

Krok 11: Je čas nastaviť vyrovnávaciu pamäť pivotov, ktorá je zdrojom kontingenčnej tabuľky. Na nastavenie zdrojovej vyrovnávacej pamäte použite objekt pvcache.

kód:

 'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 

Krok 12: Tu bude správa o predaji rôznych produktov prostredníctvom tohto konvertovaná do kontingenčnej tabuľky. Vytvorte kontingenčnú tabuľku ako prázdnu, do ktorej môžete pridať množinu údajov.

kód:

 'nová prázdna tabuľka kontingenčných tabuliek pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report") 

Krok 13: Po vložení pivotu musíte určiť rôzne polia, ktoré chcete vložiť do kontingenčnej tabuľky. Vložte teda pole prvého riadku. Tu začína prvý riadok produktom.

kód:

 'Vložte produkt do riadku vyplneného pomocou pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End with 

Krok 14: Ďalším krokom je zadanie druhého poľa, ktoré chcete vložiť do kontingenčnej tabuľky. Rovnakým spôsobom vložte ulicu poľa druhého riadku do otočnej tabuľky.

kód:

 'Vložte ulicu do riadku a miesto 2 s pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with 

Krok 15: Vložte ďalšie pole do kontingenčnej tabuľky a ďalšie je mesto. Zadajte kód na vloženie poľa mesta.

kód:

 'Vložte mesto do stĺpca s pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End with 

Krok 16: Až do tohto boli vložené údaje textového typu. Teraz musíme do kontingenčnej tabuľky vložiť počet predajov.

kód:

 'Vložte stĺpec Predaj do dátového poľa S pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End with 

Krok 17: Vložili ste polia, ktoré potrebujú na vytvorenie kontingenčnej tabuľky. A kontingenčná tabuľka je takmer dokončená, teraz môžete nastaviť formát kontingenčnej tabuľky. Týmto sa určí typ tabuľky prostredníctvom štýlu tabuľky. Riadkové osové rozloženie je tiež nastavené podľa vašich predstáv.

kód:

 'nastavte formát kontingenčnej tabuľky pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 

Krok 18: Ak chcete zobraziť položky so zadanými hodnotami riadkov v tabuľkovej forme, pridajte dole uvedený kód.

kód:

 ', aby sa kontingenčná tabuľka zobrazila v tabuľkovej forme pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True 

Krok 19: Stlačte spúšťacie tlačidlo alebo stlačte F5 na spustenie kódu. Tým sa vytvorí hárková tabuľka z listu zdrojov údajov. Údaje uvedené v riadkoch a stĺpcoch sa zmenia na kontingenčnú tabuľku. Kontingenčná tabuľka bude viditeľná na pracovnom liste kontingenčnej tabuľky.

Skontrolujte výstup a môžete vidieť, že zdroj údajov sa prevádza do kontingenčnej tabuľky, ako je uvedené nižšie, uvedené stĺpce sa prevádzajú do formulárov filtrov.

Koncové polia sú viditeľné na pravej strane. Môžete vykonať zmeny podľa potreby, ako sa majú údaje zobraziť.

Pre informáciu uvádzam nižšie uvedený kód.

kód:

 Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Worksheet Dim pdsheet As Worksheet Dim plr As Long Dim plc As Long on Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet" ") .Delete ', ak chcete odstrániť existujúcu kontingenčnú tabuľku v pracovnom hárku Worksheets.Add After: = ActiveSheet' a pridajte nový pracovný hárok ActiveSheet.Name =" pvsheet "" na premenovanie pracovného hárka na "pvsheet" On Error GoTo 0 Set pvsheet = Pracovné hárky ("pvsheet") Nastavia pdsheet = pracovné hárky ("pdsheet") 'dve premenné, aby našli posledný použitý riadok a stĺpec v pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Bunky (1, Columns.Count) .End (xlToLeft) .Column 'inicializácia rozsahu údajov kontingenčnej tabuľky Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nová prázdna kontingenčná tabuľka Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Vložte produkt do riadku vyplneného pomocou pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End with' Insert Street to Riadkový súbor a pozícia 2 s pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End s 'Insert town to Column Fileed with pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column do dátového poľa With pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End with' set the formát Kontingenčná tabuľka pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'na zobrazenie kontingenčnej tabuľky v tabuľkovej podobe .DisplayAlerts = True Application.ScreenUpdating = True End Sub 

Čo je potrebné pamätať

• Zadajte zdrojový list, kam sa majú údaje dodať pre kontingenčnú tabuľku.
• Štartový riadok a stĺpec by mali byť uvedené správne.
• Na určenie pivotnej vyrovnávacej pamäte, rozsahu, začiatku a konca riadkov použite potrebné objekty.
• Usporiadajte zdroj údajov so správnym formátom, pretože ide o proces automatizácie.

Odporúčané články

Toto je príručka pre kontingenčnú tabuľku VBA. Tu diskutujeme o tom, ako vytvoriť kontingenčnú tabuľku pomocou kódov VBA v Exceli spolu s príkladom a šablónou Excel na stiahnutie. Ďalšie informácie nájdete aj v nasledujúcich článkoch -

1. Ako odstrániť kontingenčnú tabuľku v Exceli?
2. Použitie VBA v programe PowerPoint
3. Kontingenčný stôl s viacerými listami
4. Ako vytvoriť krájač kontingenčných tabuliek?
5. Obnoviť kontingenčnú tabuľku vo VBA
6. VBA pri chybe pokračovať ďalej (príklady)
7. Aktualizácia obrazovky Excel VBA