Useiden pivot-taulukoiden suodatus samanaikaisesti

Kun luot monimutkaisia ​​raportteja ja erityisesti dashboardeja Microsoft Excelissä, on hyvin usein tarpeen suodattaa samanaikaisesti useita pivot-taulukoita kerralla. Katsotaan kuinka tämä voidaan toteuttaa.

Tapa 1: Yleinen slicer saman tietolähteen pivotien suodattamiseen

Jos pivotit rakennetaan yhden lähdetietotaulukon pohjalta, niin helpoin tapa on käyttää niitä suodattamaan niitä samanaikaisesti jakso on graafinen painikesuodatin, joka on yhdistetty kaikkiin pivot-taulukoihin kerralla.

Lisää se valitsemalla mikä tahansa solu yhteenvedosta ja välilehdestä analyysi valitse joukkue Liitä viipale (Analysoi - Lisää viipaloija). Valitse avautuvassa ikkunassa niiden sarakkeiden valintaruudut, joiden mukaan haluat suodattaa tietoja, ja napsauta OK:

Luotu leikkuri suodattaa oletusarvoisesti vain sen pivotin, jota varten se luotiin. Kuitenkin käyttämällä painiketta Raportoi yhteydet (Ilmoita yhteyksistä) kieleke Viipale (viipaleet) voimme helposti lisätä muita yhteenvetotaulukoita suodatettujen taulukkojen luetteloon:

Menetelmä 2. Yleinen siivu eri lähteiden yhteenvetojen suodattamiseen

Jos pivotisi ei ole rakennettu yhden, vaan eri lähdetietotaulukoiden mukaan, yllä oleva menetelmä ei toimi, koska ikkunassa Raportoi yhteydet vain ne yhteenvedot, jotka on luotu samasta lähteestä, näytetään.

Voit kuitenkin kiertää tämän rajoituksen helposti, jos käytät tietomallia (keskustelimme siitä yksityiskohtaisesti tässä artikkelissa). Jos lataamme taulukomme malliin ja linkitämme ne sinne, suodatus koskee molempia taulukoita samanaikaisesti.

Oletetaan, että meillä on kaksi myynti- ja kuljetuskulujen taulukkoa syöttötietona:

Oletetaan, että meidän on tehtävä tehtävämme rakentaa jokaiselle niistä oma yhteenveto ja sitten suodattaa ne samanaikaisesti kaupunkien mukaan, joilla on yhteinen leikkaus.

Teemme seuraavaa:

1. Muuta alkuperäiset taulukot dynaamiksi älykkääksi taulukoksi pikanäppäimellä Ctrl+T tai käskyt Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona) ja anna heille nimet tablProdaji и tabTransport kieleke Rakentaja (Design).

2. Lataa molemmat taulukot vuorotellen malliin -painikkeella Lisää tietomalliin Power Pivot -välilehdellä.

Näitä taulukoita ei voi suoraan linkittää mallissa, koska vaikka Power Pivot tukee vain yksi-moneen-suhteita, eli edellyttää, että yhdellä taulukoista ei ole kaksoiskappaleita sarakkeessa, johon linkitämme. Meillä on sama molemmissa kentän taulukoissa Kaupunki on toistoja. Joten meidän on luotava toinen välihakutaulukko, jossa on luettelo yksilöllisistä kaupunkinimistä molemmista taulukoista. Helpoin tapa tehdä tämä on Power Query -apuohjelma, joka on rakennettu Exceliin vuoden 2016 versiosta lähtien (ja Excel 2010-2013 -apuohjelma on ladattu ilmaiseksi Microsoftin verkkosivuilta).

3. Kun olet valinnut minkä tahansa solun "älykkäästä" taulukosta, lataamme ne yksitellen Power Queryyn -painikkeella Pöydästä/valikoimasta kieleke Päiväys (Tiedot – taulukosta/alueesta) ja valitse sitten Power Query -ikkunassa päälle Pääasiallinen tiimit Sulje ja lataa – Sulje ja lataa sisään (Etusivu — Sulje&Lataa — Sulje&Lataa...) ja tuontivaihtoehto Luo vain yhteys (Luo vain yhteys):

4. Yhdistämme molemmat taulukot yhdeksi komennolla Tiedot – Yhdistä kyselyt – Lisää (Tiedot – Yhdistä kyselyt – Liitä). Samannimiset sarakkeet otsikossa sopivat toistensa alle (kuten sarake Kaupunki), ja ne, jotka eivät täsmää, sijoitetaan eri sarakkeisiin (mutta tämä ei ole meille tärkeää).

5. Poista kaikki sarakkeet paitsi sarake Kaupunkinapsauttamalla hiiren oikealla painikkeella sen otsikkoa ja valitsemalla komennon Poista muut sarakkeet (Poista muut sarakkeet) ja poista sitten kaikki päällekkäiset kaupunkinimet napsauttamalla hiiren kakkospainikkeella sarakkeen otsikkoa uudelleen ja valitsemalla komennon Poista kaksoiskappaleet (Poista kaksoiskappaleet):

6. Luotu viiteluettelo ladataan tietomalliin kautta Etusivu — Sulje ja lataa — Sulje ja lataa (Etusivu — Sulje&Lataa — Sulje&Lataa...) ja valitse vaihtoehto Luo vain yhteys (Luo vain yhteys) ja mikä tärkeintä! – laita valintaruutu päälle Lisää nämä tiedot tietomalliin (Lisää nämä tiedot tietomalliin):

7. Nyt voimme palata Power Pivot -ikkunaan (välilehti powerpivot - painike johto), vaihtaa Kaavionäkymä (Kaavionäkymä) ja linkitä myynti- ja kuljetuskustannustaulukomme luodun kaupunkien välihakemiston kautta (vetämällä kenttiä taulukoiden välillä):

8. Nyt voit luoda kaikki tarvittavat pivot-taulukot luotua mallia varten -painikkeella yhteenvetotaulukko (Pivot-taulukko) on Pääasiallinen (Koti) -välilehti Power Pivot -ikkunassa ja valitsemalla minkä tahansa solun missä tahansa pivotissa, välilehdellä analyysi Lisää viipale -painiketta Liitä viipale (Analysoi - Lisää slicer) ja valitse viipalointi luetteloruudusta Kaupunki lisätyssä hakemistossa:

Nyt klikkaamalla tuttua painiketta Raportoi yhteydet on Leikkaa välilehti (Slicer – Raportoi yhteydet) näemme kaikki yhteenvetomme, koska ne on nyt rakennettu liittyviin lähdetaulukoihin. Jää vielä ottaa puuttuvat valintaruudut käyttöön ja napsauttaa OK – ja viipalointityökalumme alkaa suodattaa kaikkia valittuja pivot-taulukoita samanaikaisesti.