VBA ceļvedis rakurstabulās

Šī apmācība parādīs, kā strādāt ar rakurstabulām, izmantojot VBA.

Pivot tabulas ir datu apkopošanas rīki, kurus varat izmantot, lai no saviem datiem iegūtu galvenos ieskatus un kopsavilkumus. Apskatīsim piemēru: mums ir avota datu kopa šūnās A1: D21, kas satur informāciju par pārdotajiem produktiem, kas parādīta zemāk:

GetPivotData izmantošana vērtības iegūšanai

Pieņemsim, ka jums ir rakurstabula ar nosaukumu PivotTable1 ar vērtību Pārdošana laukā Vērtības/Dati, Produkts kā rindas un lauku Reģions kā Slejas. Varat izmantot metodi PivotTable.GetPivotData, lai atgrieztu vērtības no rakurstabulām.

Šis kods atgriezīs rakurstabulā USD 1 130,00 (kopējais pārdošanas apjoms austrumu reģionā):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Pārdošana", "Reģions", "Austrumi")

Šajā gadījumā pārdošana ir “datu lauks”, “lauks1” ir reģions un “vienums1” ir austrumi.

Šis kods no rakurstabulas atdos 980 USD (kopējais produkta ABC pārdošanas apjoms Ziemeļu reģionā):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Pārdošana", "Produkts", "ABC", "Reģions", "Ziemeļi")

Šajā gadījumā pārdošana ir “datu lauks”, “lauks1” ir produkts, “vienums1” ir ABC, “lauks2” ir reģions un “vienums2” ir ziemeļi.

Varat arī iekļaut vairāk nekā 2 laukus.

GetPivotData sintakse ir šāda:

GetPivotData (DataField, 1. lauks, 1. punkts, 2. lauks, 2. punkts… ) kur:

Parametrs	Apraksts
Datu lauks	Datu lauks, piemēram, pārdošana, daudzums utt., Kurā ir skaitļi.
1. lauks	Kolonnas vai rindas lauka nosaukums tabulā.
1. punkts	Vienuma nosaukums 1. laukā (pēc izvēles).
2. lauks	Kolonnas vai rindas lauka nosaukums tabulā (neobligāti).
2. punkts	Vienuma nosaukums 2. laukā (pēc izvēles).

Pivot tabulas izveide uz lapas

Lai izveidotu rakurstabulu, pamatojoties uz iepriekš minēto datu diapazonu, aktīvās darbgrāmatas lapas 1 šūnā J2, mēs izmantotu šādu kodu:

1234567891011

Darblapas ("Sheet1"). Šūnas (1, 1) .IzvēlietiesActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", versija: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Izklājlapas ("Sheet1"). Atlasiet

Rezultāts ir šāds:

Pivot tabulas izveide uz jaunas lapas

Lai izveidotu rakurstabulu, pamatojoties uz iepriekš minēto datu diapazonu, uz jaunas aktīvās darbgrāmatas lapas, mēs izmantotu šādu kodu:

12345678910111213

Darblapas ("Sheet1"). Šūnas (1, 1) .IzvēlietiesLoksnes. PievienotActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", versija: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Izklājlapas ("Sheet2"). Atlasiet

Lauku pievienošana rakurstabulai

Jūs varat pievienot laukus jaunizveidotajai rakurstabulai ar nosaukumu PivotTable1, pamatojoties uz iepriekš minēto datu diapazonu. Piezīme. Lapai, kurā ir jūsu rakurstabula, jābūt aktīvai lapai.

Lai pievienotu produktu rindu laukam, izmantojiet šādu kodu:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produkts"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produkts"). Pozīcija = 1

Lai laukam Slejas pievienotu reģionu, izmantojiet šādu kodu:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1

Lai pievienotu pārdošanas sadaļu Vērtības ar valūtas numura formātu, izmantojiet šādu kodu:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Pārdošana"), "Pārdošanas summa", xlSumAr ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Pārdošanas summa").NumberFormat = "$#, ## 0.00"Beigt ar

Rezultāts ir šāds:

Pivot tabulas pārskata izkārtojuma maiņa

Pivot tabulas pārskatu izkārtojumu var mainīt. Šis kods mainīs jūsu rakurstabulas pārskatu izkārtojumu uz tabulas formu:

1	ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18"

Pivot tabulas dzēšana

Pivot tabulu var izdzēst, izmantojot VBA. Šis kods aktīvajā lapā izdzēsīs rakurstabulu ar nosaukumu PivotTable1:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueAtlase. Skaidrs saturs

Formatējiet visas darbgrāmatas rakurstabulas

Jūs varat formatēt visas darbgrāmatas rakurstabulas, izmantojot VBA. Šis kods izmanto cilpas struktūru, lai cilpotu cauri visām darbgrāmatas lapām un dzēstu visas darbgrāmatas rakurstabulas:

12345678910111213

ApakšformatēšanaAllThePivotTablesInAWorkbook ()Dim wks kā darblapaDim wb kā darbgrāmataIestatīt wb = ActiveWorkbookDim pt Kā rakurstabulaKatrai nedēļai wb. LoksnesPar katru pt In wks.PivotTablept.TableStyle2 = "PivotStyleLight15"Nākamais ptNākamās nedēļasBeigu apakš

Lai uzzinātu vairāk par cilpu izmantošanu VBA, noklikšķiniet šeit.

Pivot tabulas lauku noņemšana

Varat noņemt laukus rakurstabulā, izmantojot VBA. Šis kods noņems lauku Produkts sadaļā Rindas no rakurstabulas ar nosaukumu PivotTable1 aktīvajā lapā:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produkts"). Orientācija = _xlSlēpts

Filtra izveide

Pivot tabula ar nosaukumu PivotTable1 ir izveidota, izmantojot sadaļu Produkts rindās un Pārdošana sadaļā Vērtības. Varat arī izveidot filtru savai rakurstabulai, izmantojot VBA. Šis kods izveidos filtru, pamatojoties uz reģionu sadaļā Filtri:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1

Lai filtrētu rakurstabulu, pamatojoties uz vienu pārskata vienumu, šajā gadījumā Austrumu reģionā, izmantojiet šādu kodu:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Reģions"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Reģions"). CurrentPage = _"Austrumi"

Pieņemsim, ka vēlaties filtrēt rakurstabulu, pamatojoties uz vairākiem reģioniem, šajā gadījumā uz austrumiem un ziemeļiem, jūs izmantotu šādu kodu:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Reģions"). _EnableMultiplePageItems = PatiesaAr ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Reģions").PivotItems ("South"). Visible = False.PivotItems ("West"). Visible = FalseBeigt ar

Pivot tabulas atsvaidzināšana

Pivot tabulu varat atsvaidzināt VBA. Lai atsvaidzinātu konkrētu tabulu ar nosaukumu PivotTable1 VBA, jūs izmantotu šo kodu:

1	ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh