VBA „Pivot“ lentelių vadovas

Ši pamoka parodys, kaip dirbti su „Pivot“ lentelėmis naudojant VBA.

„Pivot“ lentelės yra duomenų apibendrinimo įrankiai, kuriuos galite naudoti, kad iš savo duomenų sudarytumėte pagrindines įžvalgas ir santraukas. Pažvelkime į pavyzdį: A1: D21 langeliuose yra šaltinio duomenų rinkinys, kuriame pateikiama išsami informacija apie parduodamus produktus, parodyta žemiau:

„GetPivotData“ naudojimas vertei gauti

Tarkime, kad turite „PivotTable“, pavadintą „PivotTable1“, o lauke „Vertės/duomenys“ - „Pardavimai“, lauke „Eilutės“ - „Produktas“, o „Stulpeliai“ - „Regionas“. Galite naudoti „PivotTable.GetPivotData“ metodą, norėdami grąžinti vertes iš „Pivot“ lentelių.

Šis kodas grąžins 1 130,00 USD (bendras Rytų regiono pardavimas) iš „PivotTable“:

1	„MsgBox ActiveCell.PivotTable.GetPivotData“ („Pardavimas“, „Regionas“, „Rytai“)

Šiuo atveju Pardavimai yra „Duomenų laukas“, „1 laukas“ - Regionas, o „1 prekė“ - Rytai.

Šis kodas grąžins 980 USD (bendras produkto ABC pardavimas Šiaurės regione) iš „Pivot“ lentelės:

1	„MsgBox ActiveCell.PivotTable.GetPivotData“ („Pardavimas“, „Produktas“, „ABC“, „Regionas“, „Šiaurė“)

Šiuo atveju pardavimai yra „duomenų laukas“, „1 laukas“ - produktas, „1 prekė“ - ABC, „2 laukas“ - regionas, o „2 prekė“ - šiaurė.

Taip pat galite įtraukti daugiau nei 2 laukus.

„GetPivotData“ sintaksė yra tokia:

„GetPivotData“ („DataField“, 1 laukas, 1 punktas, 2 laukas, 2 punktas… ) kur:

Parametras	apibūdinimas
Duomenų laukas	Duomenų laukas, pvz., Pardavimas, kiekis ir kt., Kuriame yra skaičiai.
1 laukas	Lentelės stulpelio arba eilutės lauko pavadinimas.
1 punktas	1 lauko elemento pavadinimas (neprivaloma).
2 laukas	Lentelės stulpelio arba eilutės lauko pavadinimas (nebūtina).
2 punktas	2 lauko elemento pavadinimas (neprivaloma).

Sukamosios lentelės kūrimas lape

Norėdami sukurti „Pivot“ lentelę, pagrįstą aukščiau pateiktu duomenų diapazonu, „Active“ darbaknygės 1 lapo J2 langelyje, naudosime šį kodą:

1234567891011

Darbo lapai („Sheet1“). Ląstelės (1, 1). PasirinkiteActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", versija: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Skaičiuoklės („Sheet1“). Pasirinkite

Rezultatas yra toks:

Sukamosios lentelės kūrimas naujame lape

Norėdami sukurti „Pivot“ lentelę, pagrįstą aukščiau pateiktu duomenų diapazonu, naujame aktyvios darbaknygės lape, naudosime šį kodą:

12345678910111213

Darbo lapai („Sheet1“). Ląstelės (1, 1). PasirinkiteLakštai. PridėtiActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", versija: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Skaičiuoklės („Sheet2“). Pasirinkite

Laukų įtraukimas į „Pivot“ lentelę

Galite pridėti laukus prie naujai sukurtos „Pivot“ lentelės, vadinamos „PivotTable1“, remdamiesi aukščiau pateiktu duomenų diapazonu. Pastaba: lapas, kuriame yra jūsų „Pivot Table“, turi būti „Active Sheet“.

Norėdami pridėti produktą prie eilutės lauko, naudokite šį kodą:

123	ActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Produktas"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Produktas"). Pozicija = 1

Norėdami pridėti regioną prie stulpelių lauko, naudokite šį kodą:

123	ActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). Pozicija = 1

Norėdami pridėti pardavimus į skyrių „Vertės“ naudodami valiutos numerio formatą, naudokite šį kodą:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). "PivotFields" ("Pardavimai"), "Pardavimų suma", xlSumSu „ActiveSheet.PivotTables“ („PivotTable1“). „PivotFields“ („Pardavimo suma“).NumberFormat = "$#, ## 0.00"Pabaiga su

Rezultatas yra toks:

„Pivot“ lentelės ataskaitos išdėstymo keitimas

Galite pakeisti „Pivot“ lentelės ataskaitų išdėstymą. Šis kodas pakeis „Pivot Table“ ataskaitos išdėstymą į lentelės formą:

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

„Pivot“ lentelės ištrynimas

„Pivot“ lentelę galite ištrinti naudodami VBA. Šis kodas ištrins „Pivot Table“, vadinamą „PivotTable1“, aktyviame lape:

12	ActiveSheet.PivotTables ("PivotTable1"). "PivotSelect" ", xlDataAndLabel, TruePasirinkimas. Aiškus turinys

Suformatuokite visas suvestines lenteles darbaknygėje

Galite suformatuoti visas darbaknygės „Pivot“ lenteles naudodami VBA. Šis kodas naudoja kilpos struktūrą, kad galėtų peržiūrėti visus darbaknygės lapus ir ištrinti visas darbaknygės „Pivot“ lenteles:

12345678910111213

SubformavimasAllThePivotTablesInAWorkbook ()Dim wks As WorkheetDim wb Kaip darbo knygaNustatykite wb = ActiveWorkbookDim pt Kaip „PivotTable“Kiekvienai savaitei wb. LakštaiUž kiekvieną pt In wks.PivotTablept.TableStyle2 = "PivotStyleLight15"Kitas ptKitos savaitėsPabaiga Sub

Norėdami sužinoti daugiau apie tai, kaip naudoti kilpas VBA, spustelėkite čia.

„Pivot“ lentelės laukų pašalinimas

Galite pašalinti laukus iš „Pivot“ lentelės naudodami VBA. Šis kodas pašalins lauką Produktas skyriuje Eilės iš „Pivot“ lentelės, pavadintos „PivotTable1“ aktyviame lape:

12	ActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Produktas"). Orientacija = _xlSlėptas

Filtro kūrimas

Sukimo lentelė, vadinama „PivotTable1“, buvo sukurta naudojant „Product in the Rows“, o „Sales“ - „Value“ skiltyje. Taip pat galite sukurti „Pivot Table“ filtrą naudodami VBA. Šis kodas sukurs filtrą pagal regioną skyriuje Filtrai:

123	ActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). Pozicija = 1

Jei norite filtruoti „Pivot“ lentelę pagal vieną ataskaitos elementą, šiuo atveju Rytų regioną, naudokite šį kodą:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Regionas"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). CurrentPage = _"Rytai"

Tarkime, kad norite filtruoti „Pivot“ lentelę pagal kelis regionus, šiuo atveju į rytus ir šiaurę, turėtumėte naudoti šį kodą:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). "PivotFields" ("Regionas"). Pozicija = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Regionas"). _EnableMultiplePageItems = TiesaSu „ActiveSheet.PivotTables“ („PivotTable1“). „PivotFields“ („Regionas“).PivotItems ("Pietų"). Matomas = Netiesa.PivotItems ("Vakarai"). Matomas = NetiesaPabaiga su

„Pivot“ lentelės atnaujinimas

„Pivot“ lentelę galite atnaujinti VBA. Norėdami atnaujinti konkrečią lentelę, vadinamą „PivotTable1“ VBA, naudosite šį kodą:

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