Filtriranje više zaokretnih tablica istovremeno

Prilikom izrade složenih izvješća i, posebice, nadzornih ploča u Microsoft Excelu, vrlo je često potrebno istovremeno filtrirati nekoliko pivot tablica odjednom. Pogledajmo kako se to može implementirati.

1. metoda: opći rezač za filtriranje stožera na istom izvoru podataka

Ako su pivotovi izgrađeni na temelju jedne tablice izvornih podataka, tada je najlakši način da ih koristite za istovremeno filtriranje odjeljak je filter grafičkog gumba povezan sa svim pivot tablicama odjednom.

Da biste ga dodali, odaberite bilo koju ćeliju u jednom od sažetaka i na kartici Analiza odaberite tim Zalijepi krišku (Analiziraj — Umetni rezač). U prozoru koji se otvori označite okvire za stupce po kojima želite filtrirati podatke i kliknite OK:

Stvoreni rezač će prema zadanim postavkama filtrirati samo stožer za koji je stvoren. Međutim, pomoću gumba Prijavi veze (Prijavi veze) kartica Kriška (Krestice) možemo jednostavno dodati druge tablice sažetaka na popis filtriranih tablica:

Metoda 2. Opći odsječak za filtriranje sažetaka o različitim izvorima

Ako su vaši stožeri izgrađeni ne prema jednoj, već prema različitim izvornim tablicama podataka, tada gornja metoda neće raditi, jer u prozoru Prijavi veze prikazuju se samo oni sažeci koji su izgrađeni iz istog izvora.

Međutim, ovo ograničenje možete lako zaobići ako koristite podatkovni model (o njemu smo detaljno raspravljali u ovom članku). Ako učitamo svoje tablice u Model i tamo ih povežemo, tada će se filtriranje primijeniti na obje tablice u isto vrijeme.

Recimo da kao ulazne podatke imamo dvije tablice za troškove prodaje i transporta:

Pretpostavimo da smo suočeni sa zadatkom sastavljanja vlastitog sažetka za svaki od njih, a zatim ih simultano filtriramo po gradovima sa zajedničkim rezom.

Radimo sljedeće:

1. Pretvaranje naših izvornih stolova u dinamične pametne stolove pomoću prečaca na tipkovnici Ctrl+T ili naredbe Početna – Format kao tablica (Početna — Formatiraj kao tablicu) i daj im imena tablProdaji и tab Prijevoz kartica Konstruktor (Oblikovati).

2. Učitajte obje tablice redom u model pomoću gumba Dodaj u podatkovni model na kartici Power Pivot.

Neće biti moguće izravno povezati ove tablice u Modelu, jer dok Power Pivot podržava samo odnose jedan prema više, tj. zahtijeva da jedna od tablica nema duplikate u stupcu koji povezujemo. Isto imamo u obje tablice na terenu Grad ima ponavljanja. Stoga moramo stvoriti još jednu međutablicu za pretraživanje s popisom jedinstvenih naziva gradova iz obje tablice. Najlakši način da to učinite je pomoću funkcije dodatka Power Query, koja je ugrađena u Excel od verzije 2016. (a za Excel 2010-2013 besplatno se preuzima s Microsoftove web stranice).

3. Odabravši bilo koju ćeliju unutar "pametne" tablice, učitavamo ih jednu po jednu u Power Query pomoću gumba Iz tablice/raspona kartica Datum (Podaci — iz tablice/raspona) a zatim u Power Query prozoru odaberite on Glavni ekipe Zatvori i napuni – Zatvori i napuni (Početna — Zatvori&Učitaj — Zatvori&Učitaj u…) i mogućnost uvoza Samo stvorite vezu (Samo stvoriti vezu):

4. Obje tablice naredbom spajamo u jednu Podaci – Kombiniraj upite – Dodaj (Podaci — Kombiniraj upite — Dodaj). Stupci s istim nazivima u zaglavlju će stati jedan pod drugi (kao stupac Grad), a oni koji se ne poklapaju bit će smješteni u različite stupce (ali to nam nije važno).

5. Izbriši sve stupce osim stupca Graddesnim klikom na njegov naslov i odabirom naredbe Izbrišite ostale stupce (Ukloni druge stupce) a zatim uklonite sve duplicirane nazive gradova ponovnim desnim klikom na naslov stupca i odabirom naredbe Ukloni duplikate (Ukloni duplikate):

6. Izrađeni popis referenci učitava se u podatkovni model putem Početna — Zatvori i utovari — Zatvori i utovari (Početna — Zatvori&Učitaj — Zatvori&Učitaj u…) i odaberite opciju Samo stvorite vezu (Samo stvoriti vezu) i ono najvažnije! – uključite potvrdni okvir Dodajte ove podatke u podatkovni model (Dodajte ove podatke u podatkovni model):

7. Sada možemo, vraćajući se na Power Pivot prozor (kartica powerpivot - dugme Upravljanje), prebaciti na Prikaz grafikona (Prikaz dijagrama) i povezati naše tablice prodajnih i transportnih troškova kroz kreirani posredni imenik gradova (povlačenjem polja između tablica):

8. Sada možete izraditi sve potrebne pivot tablice za kreirani model pomoću gumba sažetak tablice (zaokretna tablica) on Glavni (Dom) u Power Pivot prozoru i, odabirom bilo koje ćelije u bilo kojem zaokretu, na kartici Analiza gumb za dodavanje kriške Zalijepi krišku (Analiziraj — Umetni rezač) i odaberite rezanje u okviru s popisom Grad u dodanom imeniku:

Sada, klikom na poznati gumb Prijavi veze on Pločica kriške (Slicer — izvješće o vezama) vidjet ćemo sve naše sažetke, jer su sada izgrađeni na povezanim izvornim tablicama. Ostaje uključiti potvrdne okvire koji nedostaju i kliknuti na OK – i naš rezač će početi filtrirati sve odabrane zaokretne tablice u isto vrijeme.