Filtriranje više zaokretnih tabela istovremeno

Prilikom kreiranja složenih izvještaja i, posebno, nadzornih ploča u Microsoft Excel-u, vrlo je često potrebno istovremeno filtrirati nekoliko pivot tablica. Hajde da vidimo kako se ovo može implementirati.

Metoda 1: Općenito Slicer za filtriranje stožera na istom izvoru podataka

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

Da biste ga dodali, odaberite bilo koju ćeliju u jednom od sažetaka i na kartici analiza izaberite tim Paste Slice (Analiziraj — Ubaci rezač). U prozoru koji se otvori označite polja za kolone po kojima želite filtrirati podatke i kliknite OK:

Kreirani rezač će, prema zadanim postavkama, filtrirati samo stožer za koji je kreiran. Međutim, korišćenjem dugmeta Prijavite veze (Prijavite veze) tabulator Rezina (kriške) možemo lako dodati druge sažete tabele na listu filtriranih tabela:

Metoda 2. Opšti odsjek za filtriranje sažetaka na različitim izvorima

Ako su vaši pivotovi napravljeni ne prema jednoj, već prema različitim tabelama izvornih podataka, gornja metoda neće raditi, jer u prozoru Prijavite veze prikazuju se samo oni sažeci koji su napravljeni iz istog izvora.

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

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

Pretpostavimo da smo suočeni sa zadatkom da napravimo vlastiti sažetak za svaki od njih, a zatim ih istovremeno filtriramo po gradovima sa zajedničkim rezom.

Radimo sljedeće:

1. Pretvaranje naših originalnih tablica u dinamične pametne tablice pomoću prečice na tastaturi Ctrl+T ili komande Početna – Formatirajte kao tabelu (Početna — Format kao tabela) i dajte im imena tablProdaji и tabTransport tabulator konstruktor (Dizajn).

2. Učitajte obje tabele naizmjenično u model pomoću dugmeta Dodaj u model podataka na kartici Power Pivot.

Neće biti moguće direktno povezati ove tabele u modelu, jer dok Power Pivot podržava samo odnose jedan-prema-više, tj. zahteva da jedna od tabela nema duplikate u koloni na koju povezujemo. Imamo isto u obje tabele na terenu grad ima ponavljanja. Dakle, moramo kreirati još jednu međutabelu za pretraživanje sa listom jedinstvenih imena gradova iz obje tabele. Najlakši način da to učinite je pomoću funkcije dodatka Power Query, koja je ugrađena u Excel od verzije iz 2016. (a za Excel 2010-2013 se besplatno preuzima sa Microsoft web stranice).

3. Nakon što smo odabrali bilo koju ćeliju unutar „pametne“ tablice, učitavamo ih jednu po jednu u Power Query pomoću gumba Iz tabele/opseg tabulator podaci (Podaci — iz tabele/opseg) a zatim u prozoru Power Query odaberite uključeno Glavni Timovi Zatvori i učitaj – Zatvori i učitaj (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…) i opciju uvoza Samo stvorite vezu (Samo kreirajte vezu):

4. Naredbom spajamo obje tabele u jednu Podaci – Kombiniraj upite – Dodaj (Podaci — Kombinirajte upite — Dodati). Kolone sa istim imenima u zaglavlju će stati jedna ispod druge (kao kolona grad), a oni koji se ne poklapaju bit će smješteni u različite kolone (ali to za nas nije važno).

5. Izbrišite sve kolone osim kolone graddesnim klikom na njen naslov i odabirom naredbe Izbrišite druge kolone (Uklonite ostale kolone) a zatim uklonite sve duple nazive gradova ponovnim desnim klikom na naslov kolone i odabirom naredbe Ukloni duplikate (Ukloni duplikate):

6. Kreirana referentna lista se učitava u model podataka putem Početna — Zatvori i učitaj — Zatvori i učitaj (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…) i odaberite opciju Samo stvorite vezu (Samo kreirajte vezu) i ono najvažnije! – uključite potvrdni okvir Dodajte ove podatke u model podataka (Dodajte ove podatke u model podataka):

7. Sada možemo, vraćajući se na prozor Power Pivot (tab powerpivot - dugme upravljanje), prebaciti na Prikaz grafikona (Prikaz dijagrama) i povežite naše tabele troškova prodaje i transporta kroz kreirani međudirektorijum gradova (prevlačenjem polja između tabela):

8. Sada možete kreirati sve potrebne pivot tabele za kreirani model pomoću dugmeta sumarna tabela (Pivot Table) on Glavni (Dom) karticu u prozoru Power Pivot i, odabirom bilo koje ćelije u bilo kojoj osovini, na kartici analiza dugme dodaj rez Paste Slice (Analiza — Ubaci Slicer) i odaberite isječak u okviru liste grad u dodanom direktoriju:

Sada, klikom na poznato dugme Prijavite veze on Slice tab (Slicer — Prijavite veze) videćemo sav naš sažetak, jer su sada izgrađeni na povezanim izvornim tabelama. Ostaje da omogućite potvrdne okvire koji nedostaju i kliknete na OK – i naš rezač će početi filtrirati sve odabrane pivot tablice u isto vrijeme.