Kontingenční tabulka Excel VBA
Kontingenční tabulka VBA vám pomůže shrnout zprávy z velké sady dat. Pivot lze považovat za nepatrnou formu celého souboru dat. Rychlý náhled na velkou datovou sadu je možný prostřednictvím kontingenční tabulky. Kontingenční tabulka je snadný způsob, jak odpovídajícím způsobem filtrovat data. Z dostupných dat můžete zvýraznit data tak, jak chcete. Kontingenční tabulka vám umožňuje kombinovat obrovské údaje analyzovat data a vytvářet zprávy, které splňují vaše obchodní požadavky.
Excel nabízí vestavěnou kontingenční tabulku, kterou lze snadno vytvořit použitím kontingenční tabulky na datovém listu. Je možné vygenerovat sestavu automaticky v Excelu, jakmile jsou data dodána. Kódy VBA vám pomohou vytvořit automatickou kontingenční tabulku.
Jak vytvořit kontingenční tabulku pomocí Excel VBA?
Běžné kroky pro vložení kontingenční tabulky je nejprve vložení kontingenční tabulky z nabídky Vložit a výběr tabulek, které chcete změnit, do kontingenční tabulky. Vybraná tabulka se stane zdrojovými daty a kontingenční tabulka bude vytvořena odpovídajícím způsobem.
Chcete-li vytvořit sestavu kontingenční tabulky, musíte vybrat pole pro filtry, hodnoty atd. Podobným způsobem lze tyto kroky automatizovat pomocí kódů VBA. Abychom mohli pracovat s kontingenční tabulkou, potřebujeme data v Excelu. Naučme se, jak vytvořit kontingenční tabulku pomocí kódů Excel VBA.
Tuto šablonu tabulky kontingenčních tabulek VBA si můžete stáhnout zde - šablony kontingenčních tabulek VBA kontingenčních tabulekKrok 1 : Kontingenční tabulka by měla být považována za referenční objekt. Vytvořte objekt pro kontingenční tabulku na dalších kódech, které bude tento objekt použit pro nasměrování kontingenční tabulky. Pojmenujte proměnnou jako pvtable. Deklarovat objekt funkce a kontingenční tabulky.
Kód:
Sub PivotTable () Dim pvtable As PivotTable End Sub
Krok 2: K uchování datových souborů potřebujeme kontingenční mezipaměť pro deklarování zdroje dat. Před vytvořením kontingenční tabulky deklarujte kontingenční proměnnou hotovosti. Deklarovat proměnnou pvcache pro pivot cache.
Kód:
Dim pvcache jako PivotCache
Krok 3: Data jsou v pracovním listu a měla by specifikovat rozsah zasažení buňky, kterou potřebujete. V kontingenční tabulce jsou zdrojová data rozprostřena jako řádky a sloupce, aby bylo možné určit konkrétní rozsah, potřebujeme proměnnou. Definujte pvrange jako proměnnou rozsahu.
Kód:
Dim pvrange As Range
Krok 4: List musí vložit kontingenční tabulku, kterou musíte vytvořit. Deklarovat proměnnou jako pracovní list. Definujte pvsheet jako list.
Kód:
Dim pvsheet As Worksheet
Krok 5: K použití datového listu, který by měl obsahovat data, která chcete vykreslit jako kontingenční tabulku, potřebujete podobnou proměnnou. Proměnná datového listu je tedy deklarována jako pdsheet.
Kód:
Dim pdsheet As Worksheet
Krok 6: Potřebujete dvě další proměnné jako dlouhý datový typ k označení posledního použitého řádku a sloupce pro vytvoření kontingenční tabulky. Může to být libovolný řádek nebo sloupec, takže existuje šance, že počet řádků a sloupců překročí limit typu dat celého čísla. Pojmenujme to plr a plc.
Kód:
Dim plr As Long Dim plc As Long
Krok 7: Dalším krokem je odstranění, pokud již existuje nějaká kontingenční tabulka. To pomůže vyhnout se záměnám, na které tabulce jsou zdrojová data, která mají být vykreslena. Odstraňte předchozí list kontingenční tabulky a vytvořte nový list pro vložení kontingenční tabulky.
Kód:
Při obnovení chyby další Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Delete 'pro odstranění existující kontingenční tabulky v listu Worksheets.Add After: = ActiveSheet' pro přidání nového listu ActiveSheet.Name = " pvsheet "'přejmenovat list na" pvsheet "On Error GoTo 0
Krok 8: Přiřaďte proměnnou objektu pro otočný list a datový list k proměnným pvsheet a pdsheet. To bude dále použito ke stanovení pracovních listů.
Kód:
Nastavit pvsheet = Pracovní listy ("pvsheet") Nastavit pdsheet = Pracovní listy ("pdsheet")
Krok 9: Jakmile jsou pracovní listy nastaveny, další položkou je, že potřebujeme poslední použitý řádek a sloupec pro vytvoření kontingenční zprávy. Najděte poslední použitý řádek a sloupec pomocí deklarovaných proměnných plr a plc.
Kód:
'dvě proměnné najít Poslední použitý řádek a sloupec v pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column
Krok 10: Jak jsme již dříve zmínili, data v listu jsou určena rozsahem buněk. V dalším kroku musíte nastavit rozsah otáčení. Je již deklarováno jako proměnná pro použití rozsahu pivotů „pvrange“.
Kód:
'inicializuje rozsah dat kontingenční tabulky Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Protože rozsah je nastaven pomocí vlastnosti resize buněčných rozsahů, změní se velikost pvrange odpovídajícím způsobem upraví rozsah pivotů. Upraví se tedy pvrange tak, že dojde k jakémukoli přidání nebo odstranění řádků nebo sloupců.
Krok 11: Je čas nastavit mezipaměť kontingenční tabulky, která je zdrojem kontingenční tabulky. Použijte objekt pvcache k nastavení zdrojové mezipaměti.
Kód:
'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Krok 12: Zde bude přehled prodeje různých produktů převeden do kontingenční tabulky. Vytvořte kontingenční tabulku jako prázdnou, do které můžete přidat další sadu dat.
Kód:
'nová prázdná kontingenční tabulka Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Krok 13: Jakmile je pivot vložen, musíte určit různá pole, která chcete vložit do kontingenční tabulky. Vložte tedy pole prvního řádku. Zde začíná první řádek produktem.
Kód:
'Vložit produkt do řádku vyplněného pomocí pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End with
Krok 14: Další je určení druhého pole, které chcete vložit do kontingenční tabulky. Stejným způsobem vložte do pole kontingenční tabulky pole druhého řádku.
Kód:
'Vložení ulice do řádkového souboru a pozice 2 pomocí pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with
Krok 15: Vložte další pole do kontingenční tabulky a dalším je město. Zadejte kód pro vložení pole města.
Kód:
'Vložte město do sloupce vyplněného pomocí pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End with
Krok 16: Až do této doby byla vložená data typu textu. Nyní musíme do kontingenční tabulky vložit počet prodejů.
Kód:
'Vložit sloupec Prodej do datového pole S pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End with
Krok 17: Vložili jste pole, která potřebují vytvořit kontingenční tabulku. A kontingenční tabulka je téměř hotová, nyní můžete nastavit formát kontingenční tabulky. Tím se určí typ tabulky prostřednictvím stylu tabulky. Rozvržení osy řádků je také nastaveno tak, jak chcete.
Kód:
'nastavte formát kontingenční tabulky pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"
Krok 18: Chcete-li zobrazit položky s hodnotami vloženými do řádků v podobě tabulky, přidejte níže uvedený kód.
Kód:
'pro zobrazení kontingenční tabulky v podobě tabulky pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Krok 19: Stisknutím tlačítka spuštění nebo stisknutím klávesy F5 spusťte kód. Tím se vytvoří kontingenční tabulka z listu zdroje dat. Data uvedená v řádcích a sloupcích se změní na kontingenční tabulku. Kontingenční tabulka bude viditelná na listu kontingenční tabulky.
Zkontrolujte výstup a můžete vidět, že zdroj dat je převeden do kontingenční tabulky, jak je uvedeno níže, uvedené sloupce jsou převedeny do formulářů filtru.
Pivotní pole jsou viditelná na pravé straně. Můžete provést změny podle potřeby, jak se data musí ukázat.
Pro vaši informaci jsem níže uvedl kód.
Kód:
Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Work Dimension Dimddsheet As Worksheet Dim plr As Long Dim plc Tak Long on Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet" ") .Delete 'pro odstranění existující kontingenční tabulky v listu Worksheets.Add After: = ActiveSheet' pro přidání nového listu ActiveSheet.Name =" pvsheet "" pro přejmenování listu na "pvsheet" On Error GoTo 0 Set pvsheet = Pracovní listy ("pvsheet") Set pdsheet = Worksheets ("pdsheet") 'dvě proměnné najít Poslední použitý řádek a sloupec v pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Buňky (1, Columns.Count) .End (xlToLeft) .Column 'inicializace datového rozsahu kontingenční tabulky Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nová prázdná kontingenční tabulka Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Vložit produkt do řádku vyplněného pomocí pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End with' Insert Street to Řádek a pozice 2 S pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with 'Insert town to Column Fileed with pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 End with 'Insert Sales column do datového pole With pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End with' set the formát Kontingenční tabulka pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'pro zobrazení kontingenční tabulky v tabulkovém tvaru pvsheet.PivotTables ("Sales_ReXout"). .DisplayAlerts = True Application.ScreenUpdating = True End Sub
Co si pamatovat
- Určete zdrojový list, kam mají být data dodána pro kontingenční tabulku.
- Počáteční řádek a sloupec by měly být uvedeny správně.
- Pomocí nezbytných objektů určete mezipaměť kontingenční tabulky, rozsah, začátek a konec řádků.
- Uspořádejte zdroj dat ve správném formátu, protože se jedná o proces automatizace.
Doporučené články
Toto je průvodce kontingenční tabulkou VBA. Zde diskutujeme o tom, jak vytvořit kontingenční tabulku pomocí kódů VBA v Excelu, spolu s příkladem a šablonou Excel ke stažení. Další informace naleznete také v následujících článcích -
- Jak odstranit kontingenční tabulku v Excelu?
- Použití VBA v aplikaci PowerPoint
- Otočný stůl s více listy
- Jak vytvořit kráječ kontingenčních stolů?
- Aktualizovat kontingenční tabulku ve VBA
- VBA On Error Resume Next (Příklady)
- Excel VBA ScreenUpdating