Mitme PivotTable-liigendtabeli samaaegne filtreerimine

Komplekssete aruannete ja eriti armatuurlaudade loomisel Microsoft Excelis on väga sageli vaja korraga filtreerida mitu pivot-tabelit. Vaatame, kuidas seda saab rakendada.

1. meetod: üldine viilutaja sama andmeallika pöördepunktide filtreerimiseks

Kui pivotid on üles ehitatud ühe lähteandmete tabeli alusel, siis on kõige lihtsam kasutada neid üheaegselt filtreerides lõik on graafiline nupufilter, mis on ühendatud kõigi pivot-tabelitega korraga.

Selle lisamiseks valige ühest kokkuvõttest ja vahekaardilt mis tahes lahter analüüs vali meeskond Kleepige viil (Analüüsi – Sisesta viilutaja). Avanevas aknas märkige nende veergude ruudud, mille järgi soovite andmeid filtreerida ja klõpsake OK:

Loodud viilutaja filtreerib vaikimisi ainult pivoti, mille jaoks see loodi. Kasutades aga nuppu Teata ühendustest (Teatage ühendustest) tab Viil (Viilud) saame lihtsalt lisada filtreeritud tabelite loendisse muid kokkuvõtlikke tabeleid:

2. meetod. Üldlõik erinevate allikate kokkuvõtete filtreerimiseks

Kui teie pivotid on ehitatud mitte ühe, vaid erinevate lähteandmete tabelite järgi, siis ülaltoodud meetod ei tööta, kuna aknas Teata ühendustest kuvatakse ainult need kokkuvõtted, mis on koostatud samast allikast.

Andmemudeli kasutamisel saate sellest piirangust siiski hõlpsasti mööda minna (arutasime seda selles artiklis üksikasjalikult). Kui laadime oma tabelid mudelisse ja linkime need sinna, rakendub filtreerimine mõlemale tabelile korraga.

Oletame, et meil on sisendandmetena kaks tabelit müügi- ja transpordikulude kohta:

Oletame, et seisame silmitsi ülesandega koostada igaühe jaoks oma kokkuvõte ja seejärel filtreerida need samaaegselt ühise lõikega linnade kaupa.

Teeme järgmist:

1. Muutke meie algsed tabelid dünaamilisteks nutikateks tabeliteks klaviatuuri otsetee abil Ctrl+T või käske Avaleht – vorminda tabelina (Avaleht — vorminda tabelina) ja anna neile nimed tablProdaji и tabTransport tab Ehitaja (Kujundus).

2. Laadige mõlemad tabelid kordamööda mudelisse, kasutades nuppu Lisa andmemudelisse vahekaardil Power Pivot.

Neid tabeleid ei saa mudelis otse linkida, sest kuigi Power Pivot toetab ainult üks-mitmele seoseid, st nõuab, et ühel tabelil ei oleks lingitavas veerus duplikaate. Meil on põllul mõlemas tabelis sama Linn on kordusi. Seega peame looma veel ühe vahepealse otsingutabeli koos ainulaadsete linnanimede loendiga mõlemast tabelist. Lihtsaim viis seda teha on Power Query lisandmooduli funktsionaalsusega, mis on Excelisse sisse ehitatud alates 2016. aasta versioonist (ja Exceli 2010-2013 jaoks laaditakse see tasuta alla Microsofti veebisaidilt).

3. Olles valinud „nutika” tabeli suvalise lahtri, laadime need ükshaaval Power Querysse nupuga Tabelist/vahemikust tab kuupäev (Andmed – tabelist/vahemikust) ja seejärel valige Power Query aknas sees Põhiline meeskonnad Sulgege ja laadige – sulgege ja laadige sisse (Avaleht — Sulge&laadi — Sule&laadi...) ja impordi võimalus Looge lihtsalt ühendus (Loo ainult ühendus):

4. Ühendame mõlemad tabelid käsuga üheks Andmed – Ühenda päringud – Lisa (Andmed – kombineeri päringud – lisa). Päises samade nimedega veerud mahuvad üksteise alla (nagu veerg Linn) ja need, mis ei ühti, paigutatakse erinevatesse veergudesse (aga see pole meie jaoks oluline).

5. Kustutage kõik veerud, välja arvatud veerg Linnparemklõpsates selle pealkirjal ja valides käsu Kustutage muud veerud (Eemalda muud veerud) ja seejärel eemaldage kõik dubleerivad linnanimed, paremklõpsates uuesti veeru pealkirja ja valides käsu Eemalda duplikaadid (Eemalda duplikaadid):

6. Loodud viiteloend laaditakse andmemudelisse üles kaudu Avaleht — Sulgege ja laadige — Sulgege ja laadige sisse (Avaleht — Sulge&laadi — Sule&laadi...) ja valige valik Looge lihtsalt ühendus (Loo ainult ühendus) ja mis kõige tähtsam! – lülitage märkeruut sisse Lisage need andmed andmemudelisse (Lisage need andmed andmemudelisse):

7. Nüüd saame naasta Power Pivoti aknasse (vahekaart PowerPivot - nupp juhtimine), vaheta Diagrammi vaade (Skeemi vaade) ja linkida meie müügi- ja transpordikulude tabelid loodud linnade vahekataloogi kaudu (lohistades välju tabelite vahel):

8. Nüüd saate nupu abil luua kõik vajalikud pivot-tabelid loodud mudeli jaoks kokkuvõtlik tabel (liigendtabel) on Põhiline (Kodu) vahekaarti Power Pivot aknas ja valides mis tahes pivoti lahtri, vahekaardil analüüs viilu lisamise nupp Kleepige viil (Analüüsi – Sisesta viilutaja) ja valige loendikastis viil Linn lisatud kataloogis:

Nüüd, klõpsates tuttaval nupul Teata ühendustest on Lõika vahekaart (Viilutaja – teatage ühendustest) näeme kogu oma kokkuvõtet, sest need on nüüd üles ehitatud seotud lähtekooditabelitele. Jääb alles lubada puuduvad märkeruudud ja klõpsata OK – ja meie viilutaja hakkab kõiki valitud pivot-tabeleid korraga filtreerima.