Excel VBA kimutató táblázat
A forgatótáblák jelentik a nagy mennyiségű adat jelentésének összefoglalását. Automatizálhatjuk a pivot tábla létrehozásának folyamatát a VBA kódolással is. Ezek fontos részei minden jelentésnek vagy irányítópultnak, az excelben könnyen táblákat lehet készíteni egy gombbal, de a VBA-ban néhány kódot kell írnunk a pivot tábla automatizálásához, az Excel 2007 és annak korábbi VBA verziói előtt nem volt szükség hozzon létre egy gyorsítótárat a pivot táblákhoz, de az excel 2010-ben és annak újabb verzióiban gyorsítótárakra van szükség.
A VBA rengeteg időt takaríthat meg számunkra a munkahelyünkön. Annak ellenére, hogy ennek elsajátítása nem olyan egyszerű, de érdemes időt tölteni ennek megtanulására. 6 hónapot vettem igénybe, hogy megértsem a pivot táblák VBA-n keresztüli létrehozásának folyamatát. Tudja, hogy ez a 6 hónap csodákat tett nekem, mert annyi hibát követtem el, hogy megpróbáltam létrehozni a forgatótáblát.
De valójában tanultam a hibáimból, és most ezt a cikket írom, hogy bemutassam, hogyan lehet kóddal létrehozni pivot táblákat.
Csak egy gombnyomással jelentéseket készíthetünk.
Pivot-tábla létrehozásának lépései a VBA-ban
A pivot tábla létrehozásához fontos, hogy legyen adatok. Ehhez létrehoztam néhány dummy adatot. Letöltheti a munkafüzetet, hogy ugyanazokkal az adatokkal kövesse velem.
1. lépés: A kimutatási tábla egy olyan objektum, amely hivatkozik a kimutató táblára, amely a változót kimutatásos táblának nyilvánítja.
Kód:
Sub PivotTable () Dim PTable mint PivotTable End Sub
2. lépés: Mielőtt először létrehoznánk egy pivot táblát, létre kell hoznunk egy pivot cache-t az adatok forrásának meghatározásához.
A szokásos munkalapokon az elfordulási táblázat anélkül, hogy gondot okozna velünk, pivot gyorsítótárat hoz létre a háttérben. De a VBA-ban alkotnunk kell.
Ehhez definiálja a PivotCache változót.
Kód:
Dim PCache mint PivotCache
3. lépés: A pivot adattartomány meghatározásához határozza meg a változót tartományként.
Kód:
Dim PRange As Range
4. lépés: Pivot tábla beszúrásához külön lapra van szükségünk, hogy munkalapokat adjunk hozzá a pivot táblához, hogy a változót munkalapként deklaráljuk.
Kód:
Dim PSheet munkalapként
5. lépés: Hasonlóképpen, a munkalapot tartalmazó adatok hivatkozására deklaráljon még egy változót munkalapként.
Kód:
Dim DSheet munkalapként
6. lépés: Végül az utoljára használt sor és oszlop megtalálásához definiáljon még két változót Hosszúként.
Kód:
Dim LR olyan hosszú Dim LC olyan hosszú
7. lépés: Most egy új lapot kell behelyeznünk egy forgatótábla létrehozásához. Előtte, ha van ilyen kimutató lap, akkor azt törölnünk kell.
8. lépés: Most állítsa a PSheet és a DSheet objektumváltozót Pivot Sheet és Data Sheet értékre.
9. lépés: Keresse meg az utolsó használt sort és az utoljára használt oszlopot az adatlapon.
10. lépés: Most állítsa be az elfordulási tartományt az utolsó sor és utolsó oszlop használatával.
Ez tökéletesen beállítja az adattartományt. Automatikusan kiválasztja az adattartományt, még akkor is, ha az adatlapon bármilyen adatot hozzáadnak vagy törölnek.
11. lépés: Mielőtt létrehoznánk egy pivot táblát, létre kell hoznunk egy pivot cache-t. Állítsa be a pivot cache változót az alábbi VBA kód használatával.
12. lépés: Most hozzon létre egy üres forgatótáblát.
13. lépés: A forgatótábla behelyezése után először be kell illesztenünk a sormezőt. Tehát beillesztem a sor mezőt Országom oszlopként.
Megjegyzés: Töltse le a munkafüzetet az adatoszlopok megértéséhez.
14. lépés: Most még egy elemet beillesztek a sormezőbe második pozícióként. A sor mezőbe második sorként beillesztem a Terméket.
15. lépés: Az oszlopok beszúrása után a sormezőbe értékeket kell beszúrnunk az oszlopmezőbe. Az oszlopmezőbe beillesztem a „Szegmenst”.
16. lépés: Most számokat kell beillesztenünk az adatmezőbe. Szóval írja be az „Értékesítés” elemet az adatmezőbe.
17. lépés: Elkészült a forgatótábla összefoglaló része. Most meg kell formáznunk a táblázatot. A pivot tábla formázásához használja az alábbi kódot.
A sorban megadott értékelemek táblázatos formában való megjelenítéséhez adja hozzá az alábbi kódot az alján.
Oké, készen vagyunk, ha ezt a kódot az F5 billentyűvel vagy manuálisan futtatjuk, akkor meg kell kapnunk a pivot táblázatot.
Így a VBA kódolással automatizálhatjuk a pivot tábla létrehozásának folyamatát.
Tájékoztatásul megadtam az alábbi kódot.
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 Long On Error Folytatás a következő Application.DisplayAlerts = hamis Application.ScreenUpdating = hamis munkalapok ("Pivot Sheet "). Törlés 'Ezzel törli a meglévő pivot tábla munkalap munkalapokat. Add után: = ActiveSheet' Ez új munkalapot ad hozzá ActiveSheet.Name =" Pivot Sheet "'Ez átnevezi a munkalapot" Pivot Sheet "be On GoTo 0 Set PSheet = Worksheet ("Pivot Sheet") Set DSheet = Worksheet ("Data Sheet") '' Legutóbb használt sor és oszlop keresése az adatlapon LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Állítsa be a pivot tábla adattartományát Set PRange = DSheet.Cellák (1, 1). Átméretezés (LR, LC) "Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)" Üres forgatótábla létrehozása PTable = PCache.CreatePivotTable (TableDestination: = PSheet). Cellák (1, 1), TableName: = "Értékesítési_jelentés") 'Ország beszúrása a PSheet.PivotTables ("Értékesítési_jelentés") iktatott sorba. PivotFields ("Ország") .Orientation = xlRowField .Position = 1 Vége "Termék beszúrása" Sorba iktatva és 2. pozíció a PSheet.PivotTables használatával ("Értékesítési_jelentés"). PivotFields ("Termék") .Orientation = xlRowField .Position = 2 Vége: "Szegmens beszúrása az oszlopba iktatáshoz és 1. pozíció a PSheet.PivotTables használatával (" Értékesítési_jelentés "). PivotFields ("Szegmens") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales oszlop beillesztése az adatmezőbe a PSheet-lel.PivotTables ("Értékesítési_jelentés"). PivotFields ("Értékesítés"). Orientation = xlDataField .Position = 1 Vége: "Formátum Pivot-tábla PSheet.PivotTables (" Értékesítési_jelentés "). ShowTableStyleRowStripes = Igaz PSheet.PivotTables2" "Sales_Report2" "PivotStyleMedium14" 'Megjelenítés táblázatos formában PSheet.PivotTables ("Értékesítési_jelentés"). 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
