Vízszintes szűrés szeletelővel

Biztosan előfordult már veled, hogy nem csak a sorokat szeretted volna szűrni, hanem az oszlopokat is. Ennek a lehetőségnek az egyik módszerét fogom leírni. A megoldás nem egyszerű, és nem is annyira rugalmas, de amennyire lehetséges megpróbálom a lehető legrugalmasabb megoldást bemutatni.
Ez a kérés a nyílt Facebook csoportunkba érkezett, ahova Te is csatlakozhatsz. Megoldást a Watch Learn Grow Repeat Youtube csatornáján találtam rá, amelyet kicsit átalakítva, dinamikussá téve szeretnék neked ebben a posztban levezetni. Vágjunk is bele!

Van egy táblázat, amelyben az oszlopok a hónapokat tartalmazzák, a sorok pedig neveket. A metszésekben az adott hónap, adott dolgozója elért pontjait lehet látni. 1. kép
A nyers fájlt innen töltheted le: Vízszintes szűrés alapadatok
A megoldást tartalmazó fájlt pedig innen: Vízszintes szűrés megoldva

Az első lépés az, hogy ezt a táblázatot áttranszponálom, tehát ami itt vízszintes, az a transzponált táblában függőleges kell legyen, és fordítva. Szeretném, hogyha rugalmas lenne a táblázat, illetve a szűrés, ezért fel kell készülni arra, hogy nem fix tartományt adjunk meg a fordításnál, hanem a névkezelőben egy dinamikus tartományt fogunk létrehozni, hiszen a hónapok miatt biztosan fog növekedni a táblázat mérete. Az INDIREKT (angolul INDIRECT) függvénnyel ezt a dinamikus tartományt létre tudjuk hozni. Az INDIREKT függvényről az Excel indirekt függvény bejegyzésünkben tájékozódhatsz.

Ha fix tartományt adnánk meg, akkor ebben az esetben az „A5:I13” tartomány lenne. Ezt a tartományt le lehet írni „S1O1” típusú hivatkozással is, ami azért szerencsés, mert az oszlopokat is számként tudjuk megadni. A hivatkozásban az „S”-t követő szám a sor számát, az „O”-t követő szám az oszlop számát jelenti. Tehát az „A5:I13” egyenlő „S5O1:S13O9”. Az INDIREKT függvénynél van lehetőség „S1O1” típusként is megadni a hivatkozást. A hibák elkerülése végett úgy csináljuk, hogy előtte egy cellában megalkotjuk azt a képletet, aminek az eredménye egy olyan szöveg, mint ami meghivatkozza a tartományt. Miután teljesen azonos a szövegként beírt hivatkozás, és a képlet által keletkezett szöveg, akkor bemásoljuk ezt a képletet az INDIREKT függvénybe.

A pontos hivatkozás a munkalap nevével együtt kell legyen, tehát ebben az esetben: („O7”-es cellába leírjuk, hogy mit is szeretnénk látni) 2. kép

‘Eredeti adat’!S5O1:S13O9
Az „O8”-as cellába létrehozzuk azt a képletet, aminek a végeredménye ugyanez kell legyen: 3. kép

=”‘Eredeti adat’!S5O1:S”&DARAB2(‘Eredeti adat’!$A$5:$A$100)+4&”O”&DARAB2(‘Eredeti adat’!$5:$5)

A képlet első része egy statikus szöveg, a munkalap neve, és az „S5O1:S”, tehát a tartomány bal felső sarka fixen meg van adva, hogy az ötödik sor és az első oszlop metszésében lévő cella, azaz az „A5”-ös cella. Ezután az & jellel hozzá van fűzve a számolós rész, azaz kiszámoltatjuk, hogy hány nem üres cella található az „A5:A100”-as tartományban. Ezután hozzáadunk 4-et, hiszen az ötödik sortól kezdődik a tartományunk, és előtte van még 4 sor. Ezután „&” jellel hozzáfűzzük az „O” betűt, és az ötödik sorban a nem üres cellák számát. Ez fogja megadni az oszlopok számát. A DARAB2 (angolul COUNTA) függvényről a DARAB függvény című bejegyzésben olvashatsz.

Ez a képlet dinamikusan fogja növelni a tartomány méretét, tehát ha új névvel bővítjük a listát, illetve új hónappal bővül a táblázat (figyelni kell, hogy ne maradjon ki üres sor, illetve üres oszlop), akkor is megfelelően fogja mutatni a tartomány méretét. Narancssárgával jelölve a plusz adat (sor és oszlop), pirossal látható, hogy a tartomány is változott. 4. kép

Ha már sikerült jó hivatkozási szöveget létrehozni, akkor ezt beépíthetjük az INDIREKT függvénybe, akár egyenesen a névkezelőben. 5. kép

Névnek adjuk meg a „Tábla1” nevet, Hatókör maradhat Munkafüzet, és a Hivatkozás helyre be kell írni, vagy másolni az INDREKT függvényt.=INDIREKT(„‘Eredeti adat’!S5O1:S”&DARAB2(‘Eredeti adat’!$A$5:$A$101)+4&”O”&DARAB2(‘Eredeti adat’!$5:$5);HAMIS)
Az INDIREKT első argumentuma a hivatkozott szöveg, második argumentuma adja meg, hogy „A1” vagy „S1O1” típusú hivatkozással szeretnénk dolgozni. A „Hamis” az „S1O1”-es típust jelenti. Ha mindent jól írtunk be, és nincs szintaktikai hibánk, akkor bezáródik az ablak, ha hibáztunk, akkor hibaüzenet érkezik.

Ezzel sikerült létrehozzuk az első alap adatainak a dinamikus táblázatát.

A következő lépésben elvégezhetjük a transzponálást. Segítségnek az INDEX (angolul INDEX) függvényt fogjuk használni. Az INDEX függvényről bővebben az INDEX függvény magyarázata című bejegyzésünkben olvashatsz.

Az INDEX függvény első argumentuma a dinamikus tábla, amit az előbb hoztunk létre, tehát „Tábla1”, a második argumentuma sorok száma, harmadik argumentuma az oszlopok száma. Mivel mi transzponálni szeretnénk az adatokat, ezért sorok megadásakor az oszlopok számát, az oszlopok megadásánál a sorok számát fogjuk megadni. A hibakezelés miatt, egy HAHIBA (angolul IFERROR) függvénybe helyezzük el az INDEX függvényt. Azért használjuk a HAHIBA függvényt, hogy ez a táblázatunk is dinamikus legyen, és nyugodtan kiterjeszthessük nagyobb tartományra, mint amennyi a jelenlegi adattartomány. HAHIBA függvényről az Excel függvények, amelyeket muszáj ismerned bejegyzésünkben olvashatsz.

Hozzunk létre egy „Transzponált adat” nevű munkalapot! Az „A5”-ös cella képlete ezek alapján:
=HAHIBA(INDEX(Tábla1;OSZLOPOK(‘Eredeti adat’!$A$5:A5);SOROK(‘Eredeti adat’!$A$5:$A5));””)

Ezt a képletet aztán lemásolhatjuk több soron keresztül, én a 25. sorig másoltam le, aztán másolhatjuk tovább az oszlopokra, én az „N” oszlopig másoltam. Ha mindent jól csináltunk, akkor ezt fogjuk látni a másolás után. 6. kép

Ezután erről a tábláról is kell készítenünk egy dinamikus táblát, hasonlóan, mint az első esetben. Ebben az esetben is célszerű először a hivatkozott szöveget létrehozni, leellenőrizni.
Az „O7”-es és „O8”-as cellákban hozzuk létre a szöveget, és a képletet, ami ugyanazt az eredmény adja. 7. kép

Az „O8”-as cella képlete:
=”‘Transzponált adat’!S5O1:S”&DARABTELI(‘Transzponált adat’!$A$5:$A$100;”>”””)+4&”O”&DARABTELI(‘Transzponált adat’!$5:$5;”>”””)
Most a DARABTELI (angolul COUNTIF) függvényt használtuk, ebben az esetben ugyanaz, mint az előző példában a DARAB2 függvény, hiszen a nem üres cellák egyenlőek a teli cellákkal.

A névkezelőben, ebben az esetben is létre kell hozzunk egy újat, most a „Tábla2” nevet adjuk neki, Hatókör maradt a Munkafüzet, és a Hivatkozáshoz a következő képletet másoljuk be:
=INDIREKT(„‘Transzponált adat’!S5O1:S”&DARABTELI(‘Transzponált adat’!$A$5:$A$100;”>”””)+4&”O”&DARABTELI(‘Transzponált adat’!$5:$5;”>”””);HAMIS) 8. kép

Ezzel sikerült létrehozni a transzponált adatok dinamikus táblázatát.

A következő lépés a „Tábla2” nevű dinamikus táblából egy PIVOT kimutatást létrehozni. A Pivot tábláról a Pivot tábla készítése 3 perc alatt című bejegyzésünkben tájékozódhatsz.
A „Beszúrás” menüben a „Kimutatás” gombra kattintva lehet elkezdeni létrehozni a Pivot táblát. A felugró ablakban a következőket adjuk meg. 9. kép

A táblázat vagy tartomány mezőbe adjuk meg a „Tábla2” nevet, hiszen ezt a nevet adtuk a névkezelőben a transzponált adatok dinamikus táblájának. „Új munkalapra” hozzuk létre a kimutatást. Ezután az „OK” gombra kattintva elkészül a kimutatásunk, amit még be kell állítanunk, és a nézetét is át kell állítsuk.
A kimutatásmezőkből egyesével be kell húzni az összes ott található neveket a „Név”-el együtt a Sorok helyre. Figyeljünk a sorrendre, hagyjuk meg az eredeti sorrendet. 10. kép

Miután minden elemet behúztunk a Sorok mezőbe, akkor láthatjuk, hogy nem nagyon értelmezhetőek az adatok a kimutatásban, ezért el kell még végezni egy-két beállítást. Először Kimutatáseszközök Tervezés menüben, az Elrendezés résznél kapcsoljuk ki a részösszegek megjelenítését, majd a Végösszegek megjelenítését sorokban és oszlopokban. Ezt követően a Kimutatás elrendezése menüt lenyitva válasszuk ki a Megjelenítés táblázatos formában pontot. 11. kép

Majd kapcsoljuk ki a „+/-” gombokat. Kimutatáseszközök, Elemzés menü, +/- gombok gombra kattintásával 12. kép

Ezek a beállítások után a kimutatás így néz ki. 13. kép

Szúrjunk be még két sort, hogy a Pivot tábla is az ötödik sortól induljon, illetve nevezzük át a munkalapot „Pivot”-ra.
Ennél a pontnál is szükség van a PIVOT tábla dinamikussá tételére, ugyanúgy fogjuk megtenni, mint eddig. Az „O7”-es „O8”-as cellába ismét létrehozzuk a segédképleteket. 14. kép

Az „O8”-as cella képlete:
=”Pivot!S5O1:S”&DARABTELI(Pivot!$A$5:$A$100;”>”””)+4&”O”&DARABTELI(Pivot!$5:$5;”>”””)
Ezután a Névkezelőben létre tudjuk hozni a következő dinamikus táblánkat. 15. kép

Ezzel sikerült létrehozzuk a Pivot adatok dinamikus táblázatát.

A következő lépésben megismételjük a második lépést, csak most a „Tábla3” nevű dinamikus tábla adja az adatokat.
Hozzuk létre a „Transzponálás megint” nevű munkalapot, és az „A5”-ös cella képletét hozzuk létre a második lépésnek megfelelően:
=HAHIBA(INDEX(Tábla3;OSZLOPOK($A$5:A5);SOROK($A$5:$A5));””)
Ezt a képletet aztán lemásolhatjuk több soron keresztül, én a 25. sorig másoltam le, aztán másolhatjuk tovább az oszlopokra, én az „N” oszlopig másoltam. Ha mindent jól csináltunk, akkor ezt fogjuk látni a másolás után. 16. kép

Kapcsoljunk be az A5:A25 tartományra egy szűrőt, hiszen erre szükségünk lesz, hogy a nevek szerint tudjunk szűrni.

A szeletelőt a PIVOT-hoz kell beszúrni. Ehhez lépjünk át a „Pivot” nevű munkalapra, kattintsunk bele a kimutatásba. Ekkor a Kimutatáseszközök Elemzés menüben válasszuk ki a Szeletelő beszúrása gombot! 17. kép

Az előugró ablakon válasszuk ki a „Név” mezőt, és kattintsunk az „OK” gombra. Ekkor megjelenik a hónapok szeletelője. Ezt a szeletelőt kivágva, és a „Transzponálás megint” nevű munkalapon illesszük be. Használva a szeletelőt, már ki is tudjuk próbálni hogyan működik. Amelyik gombra kattintunk, azt a hónapot fogja megjeleníteni a táblában, tehát vízszintesen fogunk szűrni. Ha bekapcsoljuk, hogy több elemet is ki tudjunk jelölni, akkor több hónapot is meg tudunk jeleníteni. 18. kép

Amennyiben az „Eredeti adat” munkalapon vízszintesen bővítjük az adatokat, azaz hónapokat írunk be, akkor azt nyugodtan megtehetjük, ügyeljünk arra, hogy folytonosak maradjanak az adatok, tehát ne maradjon ki üres oszlop. Ezt követően az „Adatok” menü Összes frissítése gombra kell kattintani, és akkor, – ha kellő mennyiségű oszlopba, sorba másoltuk a képleteket, minden munkalapon, akkor – a frissítés után meg fognak jelenni az új hónapok. A szeletelő is automatikusan bővülni fog. 19. kép

Amennyiben nevekkel szeretnénk bővíteni az alapadatainkat, akkor sem kell mást tegyünk, csak az „Eredeti adat” nevű munkalapon be kell írjuk az új neveket, és a hozzájuk tartozó adatokat. Ezután viszont nem elég az adatok frissítése, hanem a „Pivot” nevű munkalapon, a kimutatásba kattintva, a jobb oldalon megjelenő mezőlistában az újonnan beírt neve(ke)t át kell húzzuk a Sorok mezőbe, mint a harmadik lépésben. Ezután a „Transzponálás megint” munkalapon is fogjuk látni az új neveket, és a hozzájuk tartozó adatokat. 20. kép

Nem volt egyszerű ezt a többszörös transzponálást, és persze a dinamikus táblákat tartalmazó fájlt létrehozni, de azért bízom benne, hogy sikerült végig csinálni az egész folyamatot a leírás alapján.

Ha tetszett, kérlek oszd meg másokkal is!