Kontingenční tabulka Excel VBA
Kontingenční tabulky jsou jádrem shrnutí zprávy o velkém množství dat. Můžeme také automatizovat proces vytváření kontingenční tabulky pomocí kódování VBA. Jsou důležitou součástí každé sestavy nebo řídicího panelu, v aplikaci Excel je snadné vytvářet tabulky pomocí tlačítka, ale ve VBA musíme napsat nějaké kódy pro automatizaci naší kontingenční tabulky, před aplikací Excel 2007 a jejími staršími verzemi ve VBA jsme nemuseli vytvořit mezipaměť pro kontingenční tabulky, ale v aplikaci Excel 2010 a jejích novějších verzích jsou vyžadovány mezipaměti.
VBA nám může na našem pracovišti ušetřit spoustu času. Přestože zvládnutí není tak snadné, ale stojí za to strávit čas tím, aby se to naučil. Trvalo mi 6 měsíců, než jsem pochopil proces vytváření kontingenčních tabulek pomocí VBA. Víte, co těch 6 měsíců pro mě udělalo zázraky, protože jsem udělal tolik chyb při pokusu o vytvoření kontingenční tabulky.
Ale skutečná věc je, že jsem se poučil ze svých chyb, a teď píšu tento článek, abych vám ukázal, jak vytvořit kontingenční tabulky pomocí kódu.
Pouhým kliknutím na tlačítko můžeme vytvářet sestavy.
Kroky k vytvoření kontingenční tabulky ve VBA
K vytvoření kontingenční tabulky je důležité mít data. K tomu jsem vytvořil několik fiktivních dat. Sešit si můžete stáhnout a následovat se stejnými daty.
Krok 1: Kontingenční tabulka je objekt, který odkazuje na kontingenční tabulku a deklaruje proměnnou jako kontingenční tabulky.
Kód:
Dílčí kontingenční tabulka () Dim PTable jako koncové dílčí kontingenční tabulky
Krok 2: Než nejdříve vytvoříme kontingenční tabulku, musíme vytvořit kontingenční mezipaměť, abychom definovali zdroj dat.
V běžných pracovních listech vytvoří kontingenční tabulka bez potíží nás na pozadí kontingenční mezipaměť. Ale ve VBA musíme vytvořit.
Za tímto účelem definujte proměnnou PivotCache.
Kód:
Dim PCache jako PivotCache
Krok 3: Chcete-li určit rozsah dat otáčení, definujte proměnnou jako rozsah.
Kód:
Dim PRange As Range
Krok 4: Chcete-li vložit kontingenční tabulku, potřebujeme samostatný list pro přidání listů pro kontingenční tabulku, aby byla proměnná deklarována jako list.
Kód:
Dim PSheet As Worksheet
Krok 5: Podobně pro odkaz na data obsahující list deklarujte jednu další proměnnou jako List.
Kód:
Dim DSheet As Worksheet
Krok 6: Nakonec pro nalezení posledního použitého řádku a sloupce definujte další dvě proměnné jako Long.
Kód:
Dim LR As Long Dim LC As Long
Krok 7: Nyní musíme vložit nový list a vytvořit kontingenční tabulku. Předtím, pokud je tam nějaký kontingenční list, musíme to odstranit.
Krok 8: Nyní nastavte proměnnou objektu PSheet a DSheet na kontingenční list a datový list.
Krok 9: Najděte poslední použitý řádek a naposledy použitý sloupec v datovém listu.
Krok 10: Nyní nastavte rozsah otáčení pomocí posledního řádku a posledního sloupce.
Tím se dokonale nastaví rozsah dat. Automaticky vybere rozsah dat, i když v datovém listu dojde k jakémukoli přidání nebo odstranění dat.
Krok 11: Než vytvoříme kontingenční tabulku, musíme vytvořit kontingenční mezipaměť. Nastavte proměnnou mezipaměti pivot pomocí níže uvedeného kódu VBA.
Krok 12: Nyní vytvořte prázdnou kontingenční tabulku.
Krok 13: Po vložení kontingenční tabulky musíme nejdříve vložit pole řádku. Vložím tedy pole řádku jako můj sloupec Země.
Poznámka: Stáhněte si sešit, abyste porozuměli datovým sloupcům.
Krok 14: Nyní ještě jednu položku vložím do pole řádku jako druhou položku polohy. Vložím Produkt jako druhou řádkovou položku do pole řádku.
Krok 15: Po vložení sloupců do pole řádku musíme vložit hodnoty do pole sloupce. Do pole sloupce vložím „Segment“.
Krok 16: Nyní musíme do datového pole vložit čísla. Do datového pole tedy vložte „Prodej“.
Krok 17: Hotovo se souhrnnou částí kontingenční tabulky. Nyní musíme tabulku naformátovat. K formátování kontingenční tabulky použijte následující kód.
Chcete-li zobrazit položky s hodnotami v řádcích v tabulce, přidejte níže uvedený kód dole.
Dobře, jsme hotovi, pokud tento kód spustíme pomocí klávesy F5 nebo ručně, měli bychom získat takovou kontingenční tabulku.
Takto můžeme pomocí kódování VBA automatizovat proces vytváření kontingenční tabulky.
Pro vaši informaci jsem uvedl níže uvedený kód.
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 List "). Smazat 'Tím se smaže existující list kontingenční tabulky Worksheets.Add After: = ActiveSheet' Tím se přidá nový list ActiveSheet.Name =" Pivot Sheet "'Tím se list přejmenuje na" Pivot Sheet "Při chybě 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 'Nastavit rozsah dat kontingenční tabulky Nastavit PRange = DSheet.Buňky (1, 1). Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Vytvořit prázdnou kontingenční tabulku Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Buňky (1, 1), TableName: = "Sales_Report") 'Vložit zemi do řádku zařazeného do souboru PSheet.PivotTables ("Sales_Report"). PivotFields ("Země") .Orientation = xlRowField .Position = 1 Končit' Vložit produkt do Řádek a pozice 2 s PSheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Končit 'Vložit segment na sloupec a na pozici 1 S PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 End With 'Vložit sloupec prodeje do datového pole pomocí 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") = TableStyle "PivotStyleMedium14" 'Zobrazit v tabulkové formě PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
