Excel VBA Pivot táblázat

A VBA Pivot Table segít összefoglalni a jelentéseket egy nagy adatsorból. A Pivot a teljes adatkészlet apró formájának tekinthető. A nagy adatkészlet gyors áttekintése pivot táblán keresztül lehetséges. A pivot tábla egyszerű módszer az adatok ennek megfelelő szűrésére. A rendelkezésre álló adatokból az adatokat kívánt módon kiemelheti. A pivot tábla lehetővé teszi a hatalmas adatok kombinálását, az adatok elemzését és az üzleti igényeinek megfelelő jelentések készítését.

Az excel beépített pivot táblát kínál, amelyet könnyű elkészíteni, ha pivot táblát alkalmaznak az adatlap fölé. Lehetőség van arra, hogy automatikusan generáljon jelentést excel formátumban, miután az adatok rendelkezésre álltak. A VBA kódok segítenek az automatikus pivot tábla létrehozásában.

Hogyan készíthetünk Pivot táblát az Excel VBA használatával?

A pivot tábla beillesztésének általános lépése az, hogy először beilleszt egy pivot táblát a Beszúrás menüből, majd válassza ki azokat a táblázatokat, amelyeket pivot táblává kíván változtatni. A kiválasztott táblázat lesz a forrásadatok, és a forgótábla ennek megfelelően kerül létrehozásra.

Pivot jelentés elkészítéséhez ki kell választania a szűrők, értékek stb. Mezőit. Hasonló módon ezeket a lépéseket a VBA kódok segítségével automatizálhatjuk. Ahhoz, hogy egy pivot táblával működhessen, szükségünk van egy excel adatlapra. Tanuljuk meg, hogyan lehet pivot táblát létrehozni az Excel VBA kódok segítségével.

Itt töltheti le a VBA Pivot Table Excel sablont - VBA Pivot Table Excel sablon

1. lépés : A Pivot táblát referenciaobjektumnak kell tekinteni. Hozzon létre egy objektumot a pivot táblához a további kódokon, ez az objektum a pivot tábla mutatására szolgál. Nevezze meg a változót pvtable formátumban. Nyisson meg egy funkciót és a pivot tábla objektumot.

Kód:

 Sub PivotTable () Dim pvtable mint PivotTable End Sub

2. lépés: Az adatfájlok megőrzéséhez pivot gyorsítótárra van szükség az adatforrás deklarálásához. Pivot tábla létrehozása előtt nyilatkozzon egy pivot cash változót. Nyilatkozzon a pvcache változóval a pivot gyorsítótár számára.

Kód:

 Dim pvcache PivotCache néven

3. lépés: Az adatok a munkalapon belül vannak, és meg kell határozniuk egy tartományt, amelyen elérik a szükséges cellát. A forgótáblában a forrásadatok sorok és oszlopokként vannak elosztva, így egy adott tartományra mutató változóra van szükségünk. Adja meg a pvrange tartománytartomány változóként.

Kód:

 Dim pvrange As Range

4. lépés: A munkalapnak be kell helyeznie a létrehozandó pivot táblát. Nyilatkozzon egy változót munkalapként. Adja meg a pvsheet munkalapot.

Kód:

 Dim pvsheet Munkalapként

5. lépés: Ehhez hasonló változóra van szüksége az adatlap használatához, amelynek tartalmaznia kell azokat az adatokat, amelyeket pivot táblának kell ábrázolni. Tehát az adatlap-változót pdsheet-ként deklaráljuk.

Kód:

 A pdsheet tompítása munkalapként

6. lépés: Szükség van még két változóra, mint hosszú adattípus, hogy jelezze az utoljára használt sort és oszlopot a pivot tábla létrehozásához. Ez bármilyen sor vagy oszlop lehet, így fennáll annak esélye, hogy sorok és oszlopok száma meghaladja az egész adattípus korlátozását. Nézzük plr és plc.

Kód:

 Dim plr, amíg Dim plc, amíg

7. lépés: A következő lépés a törlés, ha van már létrehozott pivot tábla. Ez segít elkerülni a zavarokat, amelyeken a tábláztatni kell a forrásadatokat. Ezért törölje az előző pivot táblát, és hozzon létre egy új lapot a pivot táblának beillesztéséhez.

Kód:

 Hiba esetén Folytatódik a következő alkalmazás.DisplayAlerts = Hamis alkalmazás.ScreenUpdating = Hamis munkalapok ("pvsheet"). Törölje a "Munkalapok.lap munkalap" meglévő pivot táblájának törléséhez. Hozzáadás után: = ActiveSheet 'új munkalap hozzáadásához ActiveSheet.Name = " pvsheet "'- a munkalap átnevezése" pvsheet "-re GoTo 0 hiba esetén

8. lépés: Jelöljön ki egy objektumváltozót a forgólaphoz és az adatlaphoz az pvsheet és az pdsheet változókhoz. Ezt tovább fogják használni a munkalapok meghatározásához.

Kód:

 Set pvsheet = Munkalapok ("pvsheet") Állítsa pdsheet = Munkalapok ("pdsheet")

9. lépés: Amint a munkalapok be vannak állítva, a következő elemre szükségünk van az utoljára használt sorra és oszlopra a pivot jelentés létrehozásához. Keresse meg az utoljára használt sort és oszlopot a deklarált plr és plc változók segítségével.

Kód:

 'két változó az utoljára használt sor és oszlop megtalálásához az pdsheet-ben plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column

10. lépés: Mint korábban már említettük, a munkalap adatait a cellák tartománya határozza meg. A következő lépésben be kell állítania a forgó tartományt. A változónak már deklarálva van a „pvrange” pivot tartomány használata.

Kód:

 'pivot tábla adattartományának inicializálása Állítsa be a pvrange = pdsheet.Cells (1, 1) értéket .Rizeize (plr, plc)

Mivel a tartományt a cellatartomány átméretezési tulajdonsága alapján állítják be, a pvrange átméretezésével ennek megfelelően módosítja a pivot tartományt. Tehát a pvrange módosul, ha történik-e sorok vagy oszlopok hozzáadása vagy törlése.

11. lépés: Ideje beállítani a pivot gyorsítótárat, amely a pivot tábla forrása. A pvcache objektum segítségével állítsa be a forrás gyorsítótárát.

Kód:

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

12. lépés: Itt a különböző termékek értékesítési jelentése ezen keresztül pivot táblává lesz konvertálva. Hozzon létre egy pivot táblát üresen, amelyhez az adatkészletet tovább hozzáadhatja.

Kód:

 'új üres pivot tábla beállítása pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

13. lépés: A pivot behelyezése után meg kell adnia a különböző mezőket, amelyeket be szeretne illeszteni a pivot táblába. Tehát illessze be az első sor mezőjét. Az első sor itt a termékkel kezdődik.

Kód:

 'Termék beszúrása a sorba, pvsheet.PivotTables („Sales_Report”) segítségével. PivotFields („Termék”) .Orientation = xlRowField .Position = 1

14. lépés: Ezután adja meg a második mezőt, amelyet be szeretne illeszteni a pivot táblába. Ugyanígy illessze be a második sor mező utcáját a forgótáblába.

Kód:

 'Helyezze be az utcát a sorba, 2. pozícióba helyezve a pvsheet.PivotTables („Sales_Report”) segítségével. PivotFields („Street”) .Orientation = xlRowField .Position = 2 Vége a

15. lépés: Helyezze be a következő mezőt a pivot táblába, a következő pedig egy város. Adja meg a kódot a városmező beillesztéséhez.

Kód:

 'Helyezze be a várost az oszlopba, amelyet pvsheet.PivotTables („Sales_Report”) segítségével töltött be. PivotFields („Town”) .Orientation = xlColumnField .Position = 1 Vége a

16. lépés: Addig, amíg a beillesztett adatok szöveget nem tartalmaznak. Most be kell illesztenünk az értékesítések számát a pivot táblába.

Kód:

 'Helyezze be az Értékesítés oszlopot az adatmezőbe a pvsheet.PivotTables („Sales_Report”) segítségével. PivotFields („Értékesítés”) .Orientation = xlDataField .Position = 1 Végződik

17. lépés: Beillesztette azokat a mezőket, amelyekhez pivot táblázatot kell létrehozni. És a pivot tábla majdnem elkészült, most beállíthatja a pivot tábla formátumát. Ez megadja a tábla típusát a táblázati stíluson keresztül. A sor tengelyének elrendezése a kívánt módon van beállítva.

Kód:

 'állítsa be a Pvsot Table pvsheet.PivotTables ("Sales_Report") formátumot. ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"

18. lépés: A sorba benyújtott érték elemek táblázatos megjelenítéséhez adja hozzá az alábbiakban szereplő kódot.

Kód:

 'pivot tábla pvsheet.PivotTables ("Sales_Report") táblázatos megjelenítéséhez. RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

19. lépés: A kód futtatásához nyomja meg a Futtatás gombot, vagy nyomja meg az F5 billentyűt. Ez pivot táblát hoz létre az adatforrás lapról. A sorokban és oszlopokban megadott adatok a pivot táblába kerülnek. A pivot tábla látható lesz a pivot table munkalapon.

Ellenőrizze a kimenetet, és láthatja, hogy az adatforrás az alábbiak szerint átalakult-e a pivot táblába, az említett oszlopok pedig átalakultak a szűrőlapokba.

A pivot mezők a jobb oldalon láthatóak. Az adatok megjelenítésének igénye szerint változtathat.

Tájékoztatásul az alábbiakban megadtam a kódot.

Kód:

 Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Dim pvsheet As Munkalap Dim pdsheet Munkalap Dim plr Am Long Dim plc Amíg hiba Folytatás Next Application.DisplayAlerts = Hamis alkalmazás.ScreenUpdates = p Hamis munkalap "). Törölje a 'létező pivot tábla törlését a Munkalapok munkalapon. Hozzáadás után: = ActiveSheet' új munkalap hozzáadásához ActiveSheet.Name =" pvsheet "'a munkalap átnevezése" pvsheet "-re Hiba esetén GoTo 0 Állítsa pvsheet = Munkalapok ("pvsheet") Állítsa be az pdsheet = Munkalapok ("pdsheet") két változót az utoljára használt sor és oszlop megtalálásához az pdsheet-ben plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cellák (1, oszlopok.szám) .End (xlToLeft) .Oszlop 'inicializáló pivot táblázati adattartomány beállítása pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'új üres pivot tábla Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") "A pvsheet.PivotTables (" Sales_Report ") fájlba helyezett sor sorba helyezése. PivotFields (" Termék ") .Orientation = xlRowField .Position = 1 Az" Utcát beszúrva befejeződik. Támogatott sor és 2. pozíció a pvsheet.PivotTables ("Sales_Report") segítségével. PivotFields ("Street") .Orientation = xlRowField .Position = 2 Vége: "Helyezzen be várost az oszlopba, kitöltve a pvsheet.PivotTables (" Sales_Report "). PivotFields (" Város ") .Orientation = xlColumnField .Position = 1 Vége az 'Értékesítés oszlop beillesztése az adatmezőbe az pvsheet.PivotTables (" Sales_Report ") mezővel. PivotFields (" Értékesítés ") .Orientation = xlDataField .Position = 1 Vége a' beállítással. formátum Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" ", hogy a pivot táblát táblázatos formában jelenítsék meg: pvsheet.PivotTables." .DisplayAlerts = True Application.ScreenUpdating = True End Sub

Dolgok, amikre emlékezni kell

  • Adja meg a forráslapot, amelyhez az adatokat pivot táblához kell szolgáltatni.
  • A kezdő sort és az oszlopot megfelelően meg kell említeni.
  • Használja a szükséges objektumokat a pivot gyorsítótár, a sorok kezdő és záró pontjainak pontos meghatározásához.
  • Rendelje meg az adatforrást a megfelelő formátummal, mivel ez az automatizálási folyamat.

Ajánlott cikkek

Ez egy útmutató a VBA Pivot Table-hoz. Itt megvitatjuk, hogyan lehet pivot táblát létrehozni VBA kódokkal, excel formátumban, valamint egy példával és letölthető excel sablonnal. A következő cikkeket is megnézheti további információkért -

  1. Hogyan lehet törölni a Pivot táblázatot az Excel programban?
  2. VBA használata a PowerPoint alkalmazásban
  3. Pivot tábla több lapmal
  4. Hogyan készíthetünk Pivot Table Slicer-et?
  5. Frissítse a Pivot táblát a VBA-ban
  6. VBA hiba miatt folytatódik tovább (példák)
  7. Excel VBA képernyőfrissítés

Kategória:

VBA