Kontingenčná tabuľka Excel
Kontingenčné tabuľky sú jadrom súhrnu správy o veľkom množstve údajov. Môžeme tiež automatizovať proces vytvárania kontingenčnej tabuľky pomocou kódovania VBA. Sú dôležitou súčasťou každej správy alebo informačného panela, v programe Excel je ľahké vytvárať tabuľky pomocou tlačidla, ale vo VBA musíme napísať nejaké kódy na automatizáciu našej kontingenčnej tabuľky, pred programom Excel 2007 a jeho staršími verziami vo VBA sme nemuseli vytvoriť medzipamäť pre kontingenčné tabuľky, ale v programe Excel 2010 a jeho novších verziách sú vyžadované pamäte cache.
VBA nám môže na našom pracovisku ušetriť veľa času. Aj keď zvládnutie nie je také ľahké, ale stojí za to stráviť čas, aby ste sa to naučili. Trvalo mi 6 mesiacov, kým som pochopil proces vytvárania kontingenčných tabuliek pomocou VBA. Viete, čo so mnou tých 6 mesiacov urobilo zázraky, pretože som urobil toľko chýb pri pokuse o vytvorenie kontingenčnej tabuľky.
Skutočnosť je však taká, že som sa poučil zo svojich chýb a teraz píšem tento článok, aby som vám ukázal, ako vytvoriť kontingenčné tabuľky pomocou kódu.
Iba kliknutím na tlačidlo môžeme vytvárať prehľady.
Kroky na vytvorenie kontingenčnej tabuľky vo VBA
Na vytvorenie kontingenčnej tabuľky je dôležité mať k dispozícii údaje. Za týmto účelom som vytvoril nejaké fiktívne údaje. Môžete si stiahnuť zošit, aby ste so mnou mohli sledovať rovnaké údaje.
Krok 1: Kontingenčná tabuľka je objekt, ktorý odkazuje na kontingenčnú tabuľku a deklaruje premennú ako kontingenčné tabuľky.
Kód:
Vedľajšia kontingenčná tabuľka () Stlmiť PT ako Koncový podstavec kontingenčnej tabuľky
Krok 2: Predtým, ako najskôr vytvoríme kontingenčnú tabuľku, musíme vytvoriť kontingenčnú pamäť na definovanie zdroja údajov.
V bežných pracovných hárkoch vytvorí kontingenčná tabuľka bez toho, aby nás trápila, na pozadí kontingenčnú pamäť. Ale vo VBA musíme tvoriť.
Na tento účel definujte premennú PivotCache.
Kód:
Dim PCache ako PivotCache
Krok 3: Ak chcete určiť rozsah údajov otočenia, definujte premennú ako rozsah.
Kód:
Dim PRange As Range
Krok 4: Ak chcete vložiť kontingenčnú tabuľku, potrebujeme samostatný hárok na pridanie pracovných hárkov pre kontingenčnú tabuľku, ktorý deklaruje premennú ako pracovný hárok.
Kód:
Stlmiť hárok PS ako pracovný hárok
Krok 5: Podobne pri odkazovaní na údaje obsahujúce pracovný hárok deklarujte jednu ďalšiu premennú ako pracovný hárok.
Kód:
Dim DSheet As Worksheet
Krok 6: Nakoniec, ak chcete nájsť posledný použitý riadok a stĺpec, definujte ďalšie dve premenné ako Long.
Kód:
Dim LR As Long Dim LC As Long
Krok 7: Teraz musíme vložiť nový hárok, aby sme vytvorili kontingenčnú tabuľku. Ak ešte existuje nejaký kontingenčný list, musíme ho najskôr odstrániť.
Krok 8: Teraz nastavte objektovú premennú PSheet a DSheet na kontingenčný list a údajový list.
Krok 9: Nájdite v údajovom liste posledný použitý riadok a posledný použitý stĺpec.
Krok 10: Teraz nastavte rozsah otáčania pomocou posledného riadku a posledného stĺpca.
Týmto sa dokonale nastaví rozsah údajov. Automaticky vyberie rozsah údajov, aj keď v údajovom liste dôjde k pridaniu alebo vymazaniu údajov.
Krok 11: Predtým, ako vytvoríme kontingenčnú tabuľku, musíme vytvoriť kontingenčnú pamäť. Nastavte premennú kontingenčnej pamäte cache pomocou nižšie uvedeného kódu VBA.
Krok 12: Teraz vytvorte prázdnu kontingenčnú tabuľku.
Krok 13: Po vložení kontingenčnej tabuľky musíme najskôr vložiť pole riadku. Vložím teda riadkové pole ako svoj stĺpec Krajina.
Poznámka: Stiahnite si zošit, aby ste porozumeli údajovým stĺpcom.
Krok 14: Teraz ešte jednu položku, ktorú vložím do poľa riadku ako druhú položku polohy. Produkt vložím ako druhú riadkovú položku do poľa riadku.
Krok 15: Po vložení stĺpcov do poľa riadku musíme vložiť hodnoty do poľa stĺpca. Do poľa stĺpca vložím segment.
Krok 16: Teraz musíme do dátového poľa vložiť čísla. Do dátového poľa vložte teda výraz „Predaj“.
Krok 17: Hotovo so súhrnnou časťou kontingenčnej tabuľky. Teraz musíme naformátovať tabuľku. Na formátovanie kontingenčnej tabuľky použite nasledujúci kód.
Ak chcete zobraziť položky s hodnotami v riadku, vo forme tabuľky, do dolnej časti pridajte nasledujúci kód.
Dobre, máme hotovo, ak tento kód spustíme pomocou klávesu F5 alebo manuálne, potom by sme mali dostať takúto kontingenčnú tabuľku.
Takto môžeme pomocou kódovania VBA automatizovať proces vytvárania kontingenčnej tabuľky.
Pre vašu informáciu som uviedol kód nižšie.
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
