- „GetPivotData“ naudojimas vertei gauti
- Sukamosios lentelės kūrimas lape
- Sukamosios lentelės kūrimas naujame lape
- Laukų įtraukimas į „Pivot“ lentelę
- „Pivot“ lentelės ataskaitos išdėstymo keitimas
- „Pivot“ lentelės ištrynimas
- Suformatuokite visas suvestines lenteles darbaknygėje
- „Pivot“ lentelės laukų pašalinimas
- Filtro kūrimas
- „Pivot“ lentelės atnaujinimas
Š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 |