Filtrarea mai multor tabele pivot simultan

Atunci când se creează rapoarte complexe și, în special, tablouri de bord în Microsoft Excel, este foarte adesea necesar să se filtreze simultan mai multe tabele pivot deodată. Să vedem cum poate fi implementat acest lucru.

Metoda 1: General Slicer pentru filtrarea pivoturilor pe aceeași sursă de date

Dacă pivoturile sunt construite pe baza unui tabel de date sursă, atunci cea mai simplă modalitate este să le folosiți pentru a le filtra simultan secțiune este un filtru de buton grafic conectat la toate tabelele pivot simultan.

Pentru a o adăuga, selectați orice celulă dintr-unul din rezumat și din filă Analiză alege echipa Paste Slice (Analizați — Inserați dispozitivul de tăiere). În fereastra care se deschide, bifați casetele pentru coloanele după care doriți să filtrați datele și faceți clic OK:

Selectorul creat va filtra, în mod implicit, numai pivotul pentru care a fost creat. Cu toate acestea, folosind butonul Raportați conexiunile (Raportați conexiunile) fila Felie (felii) putem adăuga cu ușurință alte tabele rezumative la lista de tabele filtrate:

Metoda 2. Secțiune generală pentru filtrarea rezumatelor pe diferite surse

Dacă pivoții dvs. au fost construiți nu în funcție de unul, ci în funcție de diferite tabele de date sursă, atunci metoda de mai sus nu va funcționa, deoarece în fereastră Raportați conexiunile sunt afișate doar acele rezumate care au fost construite din aceeași sursă.

Cu toate acestea, puteți ocoli cu ușurință această limitare dacă utilizați modelul de date (am discutat în detaliu în acest articol). Dacă încărcăm tabelele noastre în Model și le legăm acolo, atunci filtrarea se va aplica ambelor tabele în același timp.

Să presupunem că avem două tabele pentru costurile de vânzări și de transport ca date de intrare:

Să presupunem că ne confruntăm cu sarcina de a construi propriul rezumat pentru fiecare dintre ele și apoi de a le filtra simultan pe orașe cu o tăietură comună.

Facem următoarele:

1. Transformarea tabelelor noastre originale în tabele inteligente dinamice cu o comandă rapidă de la tastatură Ctrl+T sau comenzi Acasă – Formatați ca tabel (Acasă — Format ca tabel) si da-le nume tablProdaji и tabTransport fila Constructor (Proiecta).

2. Încărcați ambele tabele pe rând în Model folosind butonul Adăugați la modelul de date pe fila Power Pivot.

Nu va fi posibilă legarea directă a acestor tabele în Model, deoarece, în timp ce Power Pivot acceptă doar relații unu-la-mai multe, adică necesită ca unul dintre tabele să nu aibă duplicate în coloana pe care ne conectăm. Avem același lucru în ambele tabele din domeniu Oraș sunt repetari. Deci trebuie să creăm un alt tabel de căutare intermediar cu o listă de nume unice de orașe din ambele tabele. Cel mai simplu mod de a face acest lucru este cu funcționalitatea de completare Power Query, care a fost încorporată în Excel încă din versiunea 2016 (iar pentru Excel 2010-2013 este descărcată gratuit de pe site-ul Microsoft).

3. După ce am selectat orice celulă din tabelul „inteligent”, le încărcăm una câte una în Power Query cu butonul De la masă/gamă fila Date (Date - Din tabel/gamă) și apoi în fereastra Power Query selectați pornit Principalul echipe Închideți și încărcați - Închideți și încărcați (Acasă — Închide&Încărcare — Închide&Încărcare la...) și opțiunea de import Doar creați o conexiune (Creați doar conexiune):

4. Unim ambele tabele într-unul singur cu comanda Date – Combinați interogări – Adăugați (Date — Combinați interogări — Adăugați). Coloanele cu aceleași nume în antet se vor potrivi unele sub altele (ca o coloană Oraș), iar cele care nu se potrivesc vor fi plasate în coloane diferite (dar acest lucru nu este important pentru noi).

5. Ștergeți toate coloanele, cu excepția coloanei Orașfăcând clic dreapta pe titlul acestuia și selectând comanda Ștergeți alte coloane (Eliminați alte coloane) și apoi eliminați toate numele de oraș duplicat făcând din nou clic dreapta pe antetul coloanei și selectând comanda Eliminați duplicatele (Elimină duplicatele):

6. Lista de referințe creată este încărcată în Modelul de date prin Acasă — Închideți și încărcați — Închideți și încărcați (Acasă — Închide&Încărcare — Închide&Încărcare la...) și alegeți opțiunea Doar creați o conexiune (Creați doar conexiune) si cel mai important lucru! – activați caseta de selectare Adăugați aceste date la modelul de date (Adăugați aceste date la modelul de date):

7. Acum putem, revenind la fereastra Power Pivot (fila powerpivot - buton Management), schimba cu Vizualizarea graficului (Vizualizare diagramă) și legați tabelele noastre de costuri de vânzări și transport prin directorul intermediar creat de orașe (prin tragerea câmpurilor între tabele):

8. Acum puteți crea toate tabelele pivot necesare pentru modelul creat folosind butonul tabel rezumativ (Masă rotativă) on Principalul (Acasă) din fereastra Power Pivot și, selectând orice celulă din orice pivot, pe filă Analiză butonul adăugați felie Paste Slice (Analizați — Inserați dispozitivul de tăiere) și alegeți să tăiați în caseta de listă Oraș în directorul adăugat:

Acum, făcând clic pe butonul familiar Raportați conexiunile on Fila felie (Slicer — Raportați conexiunile) vom vedea toate rezumatele noastre, deoarece acestea sunt acum construite pe tabele sursă înrudite. Rămâne să activați casetele de selectare lipsă și să faceți clic pe OK – iar slicerul nostru va începe să filtreze toate tabelele pivot selectate în același timp.