Vairāku rakurstabulu vienlaicīga filtrēšana

Veidojot sarežģītas atskaites un jo īpaši informācijas paneļus programmā Microsoft Excel, ļoti bieži ir nepieciešams vienlaikus filtrēt vairākas rakurstabulas vienlaikus. Apskatīsim, kā to var īstenot.

1. metode. Vispārīgs griezējs viena un tā paša datu avota rakursu filtrēšanai

Ja pivoti ir veidoti, pamatojoties uz vienu avota datu tabulu, tad vienkāršākais veids ir tos izmantot, lai tos vienlaikus filtrētu. daļa ir grafisko pogu filtrs, kas vienlaikus savienots ar visām rakurstabulām.

Lai to pievienotu, atlasiet jebkuru šūnu vienā no kopsavilkumiem un cilnē Analīze izvēlieties komandu Ielīmējiet šķēli (Analizēt — ievietot šķēlēju). Atvērtajā logā atzīmējiet izvēles rūtiņas kolonnām, pēc kurām vēlaties filtrēt datus, un noklikšķiniet uz OK:

Izveidotais sadalītājs pēc noklusējuma filtrēs tikai to rakursu, kuram tas tika izveidots. Tomēr, izmantojot pogu Ziņot par savienojumiem (Ziņot par savienojumiem) tab Šķēle (Šķēles) filtrēto tabulu sarakstam varam viegli pievienot citas kopsavilkuma tabulas:

2. metode. Vispārējā daļa dažādu avotu kopsavilkumu filtrēšanai

Ja jūsu pivoti tika veidoti nevis pēc vienas, bet pēc dažādām avota datu tabulām, tad iepriekš minētā metode nedarbosies, jo logā Ziņot par savienojumiem tiek parādīti tikai tie kopsavilkumi, kas izveidoti no tā paša avota.

Tomēr jūs varat viegli apiet šo ierobežojumu, ja izmantojat datu modeli (mēs to detalizēti apspriedām šajā rakstā). Ja mēs ielādējam savas tabulas modelī un saistīsim tās tur, tad filtrēšana attieksies uz abām tabulām vienlaikus.

Pieņemsim, ka mums ir divas pārdošanas un transporta izmaksu tabulas kā ievades dati:

Pieņemsim, ka mēs saskaramies ar uzdevumu izveidot savu kopsavilkumu katram no tiem un pēc tam vienlaikus filtrēt tos pēc pilsētām ar kopīgu griezumu.

Mēs veicam šādas darbības:

1. Pārvērtiet mūsu sākotnējās tabulas par dinamiskām viedām tabulām, izmantojot īsinājumtaustiņus Ctrl+T vai komandas Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu) un dod viņiem vārdus tablProdaji и tabTransports tab Konstruktors (Dizains).

2. Ielādējiet abas tabulas pēc kārtas modelī, izmantojot pogu Pievienot datu modelim cilnē Power Pivot.

Šīs tabulas modelī nebūs iespējams tieši saistīt, jo, lai gan Power Pivot atbalsta tikai attiecības viens pret daudziem, ti, vienai no tabulām ir nepieciešams, lai kolonnā, kurā mēs saistām, nebūtu dublikātu. Mums ir tas pats abās tabulās laukā Pilsēta ir atkārtojumi. Tāpēc mums ir jāizveido vēl viena starpposma uzmeklēšanas tabula ar unikālu pilsētu nosaukumu sarakstu no abām tabulām. Vienkāršākais veids, kā to izdarīt, ir Power Query pievienojumprogrammas funkcionalitāte, kas ir iebūvēta programmā Excel kopš 2016. gada versijas (un programmai Excel 2010–2013 tā tiek lejupielādēta bez maksas no Microsoft vietnes).

3. Atlasot jebkuru šūnu “viedajā” tabulā, mēs tās ielādējam pa vienam Power Query ar pogu No galda/diapazona tab Datums (Dati — no tabulas/diapazona) un pēc tam Power Query logā atlasiet Ieslēgt Galvenais komandas Aizvērt un ielādēt – aizvērt un ielādēt (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt...) un importēšanas iespēja Vienkārši izveidojiet savienojumu (Izveidojiet tikai savienojumu):

4. Mēs savienojam abas tabulas vienā ar komandu Dati — Apvienot vaicājumus — Pievienot (Dati — apvienot vaicājumus — pievienot). Kolonnas ar vienādiem nosaukumiem galvenē ietilps viena zem otras (kā kolonna Pilsēta), un tie, kas neatbilst, tiks ievietoti dažādās kolonnās (bet tas mums nav svarīgi).

5. Dzēst visas kolonnas, izņemot kolonnu Pilsētaar peles labo pogu noklikšķinot uz tās nosaukuma un atlasot komandu Dzēst citas kolonnas (Noņemt citas kolonnas) un pēc tam noņemiet visus dublētos pilsētu nosaukumus, vēlreiz ar peles labo pogu noklikšķinot uz kolonnas virsraksta un atlasot komandu Noņemt dublikātus (Noņemt dublikātus):

6. Izveidotais atsauces saraksts tiek augšupielādēts datu modelī, izmantojot Sākums — Aizveriet un ielādējiet — Aizveriet un ielādējiet (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt...) un izvēlieties iespēju Vienkārši izveidojiet savienojumu (Izveidojiet tikai savienojumu) un pats galvenais! – ieslēdziet izvēles rūtiņu Pievienojiet šos datus datu modelim (Pievienojiet šos datus datu modelim):

7. Tagad mēs varam, atgriežoties Power Pivot logā (cilne powerpivot - poga vadība), pāriet uz Diagrammas skats (Diagrammas skats) un savienojiet mūsu pārdošanas un transporta izmaksu tabulas, izmantojot izveidoto pilsētu starpdirektoriju (velkot laukus starp tabulām):

8. Tagad varat izveidot visas nepieciešamās rakurstabulas izveidotajam modelim, izmantojot pogu kopsavilkuma tabula (Rakurstabula) on Galvenais (Mājas) cilni Power Pivot logā un, atlasot jebkuru šūnu jebkurā rakursā, cilnē Analīze poga pievienot šķēli Ielīmējiet šķēli (Analizēt — ievietot šķēlēju) un sarakstlodziņā izvēlieties šķēlumu Pilsēta pievienotajā direktorijā:

Tagad, noklikšķinot uz pazīstamās pogas Ziņot par savienojumiem on Šķēles cilne (Šķēlējs — ziņot par savienojumiem) mēs redzēsim visu mūsu kopsavilkumu, jo tie tagad ir veidoti uz saistītām avotu tabulām. Atliek iespējot trūkstošās izvēles rūtiņas un noklikšķiniet uz OK – un mūsu sadalītājs vienlaikus sāks filtrēt visas atlasītās rakurstabulas.