seychellesartprojects.org

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, i když zvládnutí to není tak snadné, ale stojí za to věnovat čas tomu, abychom se to naučili. Trvalo mi 6 měsíců, než jsem pochopil proces vytváření kontingenčních tabulek pomocí VBA. Víte, co pro mě těch 6 měsíců 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

Tuto šablonu kontingenční tabulky VBA si můžete stáhnout zde - Šablona kontingenční tabulky VBA

K vytvoření kontingenční tabulky je důležité mít data. Za tímto účelem jsem vytvořil několik fiktivních dat, můžete si stáhnout sešit 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ž vytvoříme kontingenční tabulku, musíme nejprve vytvořit kontingenční mezipaměť, abychom definovali zdroj dat.

V běžné kontingenční tabulce listu bez obtěžování nás vytvoří kontingenční mezipaměť na pozadí. 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:  K vložení kontingenční tabulky potřebujeme samostatný list pro přidání listu pro kontingenční tabulku deklarujte proměnnou jako list.

Kód:

 Dim PSheet As Worksheet

Krok 5:  Podobně jako odkaz na data obsahující list deklarujte jednu další proměnnou jako list.

Kód:

 Dim DSheet As Worksheet

Krok 6: Nakonec najděte poslední použitý řádek a sloupec a 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 přidání nebo odstranění dat.

Krok 11: Před vytvořením kontingenční tabulky 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 nejprve 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 položku druhé pozice. 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: Jsme hotovi se souhrnnou částí kontingenční tabulky, nyní musíme tabulku naformátovat. K formátování kontingenční tabulky použijte níže uvedený kód.

Poznámka: Chcete-li mít více různých stylů tabulky, nahrajte jim makro a získejte styly tabulky.

Chcete-li zobrazit položky s hodnotami v řádcích v tabulce, přidejte níže uvedený kód dole.

Dobře, jsme hotovi, když spustíme tento kód pomocí klávesy F5 nebo ručně, měli bychom získat takovou kontingenční tabulku.

Stejně jako pomocí kódování VBA můžeme 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