Kelių „PivotTable“ filtravimas vienu metu

Kuriant sudėtingas ataskaitas ir ypač informacijos suvestines „Microsoft Excel“, labai dažnai reikia vienu metu filtruoti kelias suvestinės lenteles. Pažiūrėkime, kaip tai galima įgyvendinti.

1 būdas: bendroji pjaustyklė, skirta to paties duomenų šaltinio sukiniams filtruoti

Jei suvestinės yra sukurtos remiantis viena šaltinio duomenų lentele, paprasčiausias būdas yra juos naudoti norint juos filtruoti vienu metu skyrius yra grafinis mygtukų filtras, sujungtas su visomis suvestinės lentelėmis vienu metu.

Norėdami jį pridėti, pasirinkite bet kurį langelį vienoje iš santraukos ir skirtuko Analizė pasirinkti komandą Įklijuokite skiltelę (Analizuoti – įterpti pjaustyklę). Atsidariusiame lange pažymėkite langelius prie stulpelių, pagal kuriuos norite filtruoti duomenis, ir spustelėkite OK:

Sukurtas pjaustytuvas pagal numatytuosius nustatymus filtruos tik tą sukimąsi, kuriam jis buvo sukurtas. Tačiau naudojant mygtuką Pranešti apie ryšius (Pranešti apie ryšius) kortelė Gabalas (griežinėliai) į filtruotų lentelių sąrašą galime lengvai įtraukti kitas suvestinės lenteles:

2 metodas. Bendras skirtingų šaltinių santraukų filtravimo skyrius

Jei jūsų pivotai buvo sukurti ne pagal vieną, o pagal skirtingas šaltinio duomenų lenteles, aukščiau pateiktas metodas neveiks, nes lange Pranešti apie ryšius rodomos tik tos santraukos, kurios buvo sukurtos iš to paties šaltinio.

Tačiau galite lengvai apeiti šį apribojimą, jei naudojate duomenų modelį (šiame straipsnyje mes jį išsamiai aptarėme). Jei įkelsime lenteles į modelį ir susiesime jas ten, tada filtravimas bus taikomas abiem lentelėms vienu metu.

Tarkime, kad turime dvi pardavimo ir transportavimo išlaidų lenteles kaip įvesties duomenis:

Tarkime, kad mes susiduriame su užduotimi sukurti savo kiekvieno iš jų santrauką ir vienu metu filtruoti pagal miestus, turinčius bendrą pjūvį.

Mes atliekame šiuos veiksmus:

1. Originalias lenteles paverskite dinaminėmis išmaniosiomis lentelėmis naudodami sparčiuosius klavišus "Ctrl"+T arba komandas Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę) ir duoti jiems vardus tablProdaji и tabTransportas kortelė Konstruktorius (Dizainas).

2. Įkelkite abi lenteles paeiliui į modelį naudodami mygtuką Pridėti prie duomenų modelio skirtuke Power Pivot.

Modelyje šių lentelių tiesiogiai susieti nebus galima, nes nors „Power Pivot“ palaiko tik „vienas su daugeliu“ ryšius, ty reikalauja, kad viena iš lentelių neturėtų dublikatų stulpelyje, kuriame susiejame. Abiejose lauko lentelėse turime tą patį Miestas yra pasikartojimų. Taigi turime sukurti kitą tarpinę paieškos lentelę su unikalių miestų pavadinimų sąrašu iš abiejų lentelių. Paprasčiausias būdas tai padaryti yra naudojant Power Query papildinio funkcionalumą, kuris buvo integruotas į Excel nuo 2016 m. versijos (o Excel 2010–2013 versijai jis atsisiunčiamas nemokamai iš Microsoft svetainės).

3. Pasirinkę bet kurį langelį „protingoje“ lentelėje, įkeliame juos po vieną „Power Query“ mygtuku Iš stalo/asortimento kortelė Duomenys (Duomenys – iš lentelės/diapazono) tada Power Query lange pasirinkite įjungti Pagrindinis komandos Uždaryti ir įkelti – uždaryti ir įkelti (Pagrindinis - Uždaryti ir įkelti - Uždaryti ir įkelti į...) ir importo parinktis Tiesiog sukurkite ryšį (Tik sukurti ryšį):

4. Abi lenteles sujungiame į vieną su komanda Duomenys – Sujungti užklausas – Pridėti (Duomenys – Sujungti užklausas – Pridėti). Stulpeliai su tais pačiais pavadinimais antraštėje tilps vienas po kito (kaip stulpelis Miestas), o tie, kurie nesutampa, bus dedami į skirtingus stulpelius (tačiau mums tai nėra svarbu).

5. Ištrinti visus stulpelius, išskyrus stulpelį Miestasdešiniuoju pelės mygtuku spustelėdami jo pavadinimą ir pasirinkę komandą Ištrinkite kitus stulpelius (Pašalinti kitus stulpelius) ir pašalinkite visus pasikartojančius miestų pavadinimus dar kartą dešiniuoju pelės mygtuku spustelėdami stulpelio antraštę ir pasirinkę komandą Pašalinti dublikatus (Pašalinti dublikatus):

6. Sukurtas nuorodų sąrašas įkeliamas į duomenų modelį per Pagrindinis puslapis — Uždaryti ir įkelti — Uždaryti ir įkelti (Pagrindinis - Uždaryti ir įkelti - Uždaryti ir įkelti į...) ir pasirinkite parinktį Tiesiog sukurkite ryšį (Tik sukurti ryšį) ir svarbiausia! – įjunkite žymimąjį laukelį Pridėkite šiuos duomenis prie duomenų modelio (Pridėkite šiuos duomenis prie duomenų modelio):

7. Dabar galime grįžti į „Power Pivot“ langą (skirtukas „PowerPivot“ - mygtukas valdymas), Pereiti prie Diagramos vaizdas (Diagramos vaizdas) ir susiekite mūsų pardavimo ir transportavimo išlaidų lenteles per sukurtą tarpinį miestų katalogą (vilkant laukus tarp lentelių):

8. Dabar galite sukurti visas reikiamas suvestines lenteles sukurtam modeliui naudodami mygtuką suvestinė lentelė (Suvestinės lentelės) on Pagrindinis (Namai) skirtuką Power Pivot lange ir pasirinkę bet kurį langelį bet kuriame suvestyje, skirtuke Analizė mygtuką pridėti gabalėlį Įklijuokite skiltelę (Analizuoti – įterpti pjaustyklę) ir sąrašo laukelyje pasirinkite pjaustyti Miestas pridėtame kataloge:

Dabar spustelėdami pažįstamą mygtuką Pranešti apie ryšius on Pjaustymo skirtukas (Smulkintuvas – praneškite apie ryšius) matysime visą savo santrauką, nes dabar jos sukurtos ant susijusių šaltinių lentelių. Belieka įjungti trūkstamus žymės langelius ir spustelėti OK – ir mūsų pjaustyklė pradės filtruoti visas pasirinktas suvestinės lenteles tuo pačiu metu.