Pag-filter ng Maramihang PivotTables Sabay-sabay

Kapag lumilikha ng mga kumplikadong ulat at, lalo na, mga dashboard sa Microsoft Excel, madalas na kinakailangan na sabay na i-filter ang ilang pivot table nang sabay-sabay. Tingnan natin kung paano ito maipapatupad.

Paraan 1: Pangkalahatang Slicer para sa pag-filter ng mga pivot sa parehong data source

Kung ang mga pivot ay binuo batay sa isang source data table, kung gayon ang pinakamadaling paraan ay gamitin ang mga ito upang i-filter ang mga ito nang sabay-sabay seksyon ay isang graphic na button na filter na konektado sa lahat ng pivot table nang sabay-sabay.

Upang idagdag ito, pumili ng anumang cell sa isa sa buod at sa tab Pagsusuri Piliin ang grupo Idikit ang Hiwa (Pag-aralan - Ipasok ang slicer). Sa window na bubukas, lagyan ng check ang mga kahon para sa mga column kung saan mo gustong mag-filter ng data at mag-click OK:

Ang ginawang slicer ay, bilang default, i-filter lamang ang pivot kung saan ito ginawa. Gayunpaman, gamit ang pindutan Mag-ulat ng Mga Koneksyon (Mag-ulat ng mga koneksyon) tab Hiwa (Mga hiwa) madali tayong magdagdag ng iba pang mga talahanayan ng buod sa listahan ng mga na-filter na talahanayan:

Paraan 2. Pangkalahatang hiwa para sa pagsala ng mga buod sa iba't ibang pinagmumulan

Kung ang iyong mga pivots ay binuo hindi ayon sa isa, ngunit ayon sa iba't ibang mga talahanayan ng pinagmulan ng data, kung gayon ang pamamaraan sa itaas ay hindi gagana, dahil sa window Mag-ulat ng Mga Koneksyon tanging ang mga buod na ginawa mula sa parehong pinagmulan ang ipinapakita.

Gayunpaman, madali mong malalampasan ang limitasyong ito kung gagamitin mo ang Modelo ng Data (tinalakay namin ito nang detalyado sa artikulong ito). Kung ilo-load namin ang aming mga talahanayan sa Modelo at i-link ang mga ito doon, malalapat ang pag-filter sa parehong mga talahanayan sa parehong oras.

Sabihin nating mayroon kaming dalawang talahanayan para sa mga gastos sa pagbebenta at transportasyon bilang input data:

Ipagpalagay na tayo ay nahaharap sa gawain ng pagbuo ng sarili nating buod para sa bawat isa sa kanila at pagkatapos ay i-filter ang mga ito nang sabay-sabay ng mga lungsod na may karaniwang pagbawas.

Ginagawa namin ang sumusunod:

1. Ginagawang Dynamic na Smart Table ang Aming Mga Orihinal na Table na may Keyboard Shortcut Ctrl+T o mga utos Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan) at bigyan sila ng mga pangalan tablProdaji и tabTransport tab Tagagawa (Disenyo).

2. I-load ang parehong mga talahanayan sa Modelo gamit ang pindutan Idagdag sa Modelo ng Data sa tab na Power Pivot.

Hindi posibleng direktang i-link ang mga talahanayang ito sa Modelo, dahil habang sinusuportahan lang ng Power Pivot ang isa-sa-maraming relasyon, ibig sabihin, nangangailangan ang isa sa mga talahanayan na walang mga duplicate sa column kung saan kami nagli-link. Pareho kami sa magkabilang table sa field lungsod may mga pag-uulit. Kaya kailangan nating lumikha ng isa pang intermediate lookup table na may listahan ng mga natatanging pangalan ng lungsod mula sa parehong talahanayan. Ang pinakamadaling paraan upang gawin ito ay gamit ang Power Query add-in functionality, na binuo sa Excel mula noong 2016 na bersyon (at para sa Excel 2010-2013 ito ay nai-download nang libre mula sa Microsoft website).

3. Ang pagkakaroon ng pagpili ng anumang cell sa loob ng "matalinong" talahanayan, nilo-load namin ang mga ito nang paisa-isa sa Power Query gamit ang pindutan Mula sa talahanayan/saklaw tab data (Data — Mula sa talahanayan/saklaw) at pagkatapos ay sa Power Query window piliin ang on Ang pangunahing koponan Isara at i-load – Isara at i-load in (Home — Isara&I-load — Isara&I-load sa…) at opsyon sa pag-import Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon):

4. Pinagsasama namin ang parehong mga talahanayan sa isa gamit ang utos Data – Pagsamahin ang Mga Query – Magdagdag (Data — Pagsamahin ang mga query — Idagdag). Ang mga column na may parehong pangalan sa header ay magkakasya sa ilalim ng bawat isa (tulad ng column lungsod), at ang mga hindi tumutugma ay ilalagay sa iba't ibang column (ngunit hindi ito mahalaga para sa amin).

5. Tanggalin ang lahat ng column maliban sa column lungsodsa pamamagitan ng pag-right click sa pamagat nito at pagpili sa command Tanggalin ang iba pang mga column (Alisin ang iba pang mga column) at pagkatapos ay alisin ang lahat ng mga duplicate na pangalan ng lungsod sa pamamagitan ng pag-right click muli sa heading ng column at pagpili sa command Alisin ang Mga Doble (Alisin ang mga duplicate):

6. Ang ginawang listahan ng sanggunian ay ina-upload sa Modelo ng Data sa pamamagitan ng Tahanan — Isara at i-load — Isara at i-load (Home — Isara&I-load — Isara&I-load sa…) at piliin ang opsyon Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon) at ang pinakamahalagang bagay! – i-on ang checkbox Idagdag ang data na ito sa modelo ng data (Idagdag ang data na ito sa Modelo ng Data):

7. Ngayon ay magagawa na natin, bumalik sa Power Pivot window (tab powerpivot - pindutan pamamahala), Lumipat sa Pagtingin sa Tsart (Tingnan ang diagram) at i-link ang aming mga talahanayan ng mga benta at gastos sa transportasyon sa pamamagitan ng nilikhang intermediate na direktoryo ng mga lungsod (sa pamamagitan ng pag-drag ng mga field sa pagitan ng mga talahanayan):

8. Ngayon ay maaari ka nang gumawa ng lahat ng kinakailangang pivot table para sa ginawang modelo gamit ang button talahanayan ng buod (Pivot Table) on Ang pangunahing (Tahanan) tab sa Power Pivot window at, sa pamamagitan ng pagpili ng anumang cell sa anumang pivot, sa tab Pagsusuri magdagdag ng slice button Idikit ang Hiwa (Suriin — Insert Slicer) at piliin na hatiin sa kahon ng listahan lungsod sa idinagdag na direktoryo:

Ngayon, sa pamamagitan ng pag-click sa pamilyar na pindutan Mag-ulat ng Mga Koneksyon on Maghiwa ng tab (Slicer — Mag-ulat ng mga koneksyon) makikita namin ang lahat ng aming buod, dahil ang mga ito ay binuo na ngayon sa mga nauugnay na talahanayan ng pinagmulan. Ito ay nananatiling paganahin ang nawawalang mga checkbox at mag-click sa OK – at magsisimulang i-filter ng aming slicer ang lahat ng napiling pivot table nang sabay-sabay.