Adatkezelés - Szűrés

Íme egy újabb gyöngyszem.  Július legvégén volt egy Excel VBA kurzusom, ahol arra szoktam buzdítani a résztvevőket, hogy bátran hozzanak saját problémákat, és ha belefér, megoldjuk őket, vagy legalább hozok rá elvi vagy gyakorlati megoldást a tanfolyamon. Milu azzal állt elő, hogy rendszeresen kap egy bizonyos riportot, ami mindig ugyanazokat az oszlopokat tartalmazza, de gyakran eltérő oszlop sorrendben. Mivel az lenne a feladat, hogy a kapott riportokat egy eredményfájlba gyűjtse, aminek az oszlopsorrendje adott,a másolás előtt  az aktuálisan kapott riportot a gyűjtőfájl oszlopsorrendjéhez kell igazítani. Mivel ismétlődő a feladat, jó lenne lemakrózni, hogy automatikusan futhasson. Kell tehát találni egy olyan megoldást, ami függetlenül az aktuális oszlopsorrendtől, mindig egy előre rögzített sorrend alapján elkészít egy másolatot, majd azt a gyűjtőfájl végére fűzi.

Ebben az anyagban kiemeltem az oszlopsorrend változtatására a lehetséges módszereket.

Sorrend változtatás vontatással vagy vágólappal

• Kivágás -> Kivágott cellák beszúrása (Cut ->Paste Cut cells) vagy
• Shift+vontatás

Módosítás az oszlopok rendezésével

Mivel az Excel a 2007-es verziótól képes valamely sor alapján rendezni az oszlopokat, megoldás lehet, ha a helyes sorrendet az aktuális oszlopnevek fölé képezzük (kézzel vagy képlettel), majd a kapott számok alapján átrendezzük az oszlopokat.

• Data -> Sort -> Options -> Left to Right
• Adatok ->Rendezés ->Beállítások ->Balról jobbra

A helyes oszlop sorszámokat kézzel vagy a Hol.Van (Match) függvénnyel állíthatjuk elő. A Videóban megmutatom ezt a megoldást is.

Módosítás az irányított szűrő (Advanced Filter) segítségével

Ezt a megoldást Márti gyűjtőmunkájának köszönhetjük. Ebben az esetben az aktuális riport táblázat mellé vagy alá, akár másik munkalapra feltesszük a helyes sorrendű fejlécet, majd alkalmazzuk a speciális vagy irányított szűrőt.

1. Kijelöljük vagy aktiváljuk a Riport táblázatot
2. Elindítjuk az irányított szűrőt
   

1. Beállítjuk az alábbiak szerint, és az eredmény a jó fejléc szerinti oszlopsorrend lesz
   

Irányitott szűrő beállítás

Letölthető minta dokumentum: oszlop_sorrend_modositas.xlsx

Mindez működés közben

• Oszlopsorrend változtatása

Tovább... →

Gyakran látom a különböző Excel fórumokon azt a kérdést, hogy hogyan lehet adatokat beilleszteni egy olyan táblázatba, amelynek vannak elrejtett sorai? Ez leginkább szűrés alkalmával fordul elő. Tehát azt szeretnénk, hogy a szűrt táblázat egyik oszlopának egy kijelölt tartományába, egy másik tartományból másolhassunk adatokat.

A forrás és a céltartomány ugyanabban a táblázatban van

Sajnos erre a kérdésre nem is olyan egyszerű a válasz (de legalább van), mert az Excel nem igazán van felkészítve ilyesmi feladatra. Nézzük mi történik akkor, ha megpróbáljuk a szokásos beillesztést (CP=Copy-Paste)

1. Szűrjük a táblát
2. Kijelöljük a másolandó tartományt ->Másolás(Ctrl+C) Ezt tehetjük a szokásos módon, vagy a speciális kijelölés->Csak a látható cellák (Visible cells only)
3. Kijelöljük a céltartományt (lényeges, hogy mérete megegyezzen a forráséval)
4. Beillesztés (Ctrl+V)

Az eredmény

Ugyanezt az üzenetet kapjuk akkor is, ha megpróbáljuk átvontatni a forrástartományt a céltartományra. Ha nem jelöljük előre ki a céltartományt, csak az első celláját, akkor pedig az történik, hogy a beillesztés a nem látható (szűrt) cellákba is megtörténik. ezt pedig semmiképpen nem szeretnénk.

A megoldás

Szűrt állapotban a céltartomány celláiból át kell hivatkozni a forrástartomány celláira, majd érték beillesztéssel megszüntetni  a képletet. Ez csak akkor működik biztonságosan, ha ugyanabban a táblázatban van a két tartomány, és így biztosított, hogy közösek az elrejtett sorok.

A forrás és céltartomány különböző táblázatban van

Ebben az esetben az sem biztos, hogy a forrás tartomány is szűrt, de ha igen, az valószínűtlen, hogy ugyanazok a sorok lesznek rejtettek, mint a céltartomány esetén. Erre a helyzetre az Excel felület nem kínál megoldást, tehát makrót (VBA kódot) kell készíteni. Ebben első lépésként meg kell vizsgálni, hogy a forrás és a céltartomány látható (nem szűrt) sorainak (és oszlopainak)  száma megegyezik-e, majd soronként másolhatunk úgy, hogy a céltartományban csak a látható sorokba illesztjük a forrás tartomány aktuális látható sorát. A Videóban látható mindez kicsit bővebben.

Következő rész: A makrós megoldás

Letölthető munkafüzet: beillesztes-szurt-tartomanyba

• Nézzük, hogy lehet adatokat másolni csak a látható cellákba

Tovább... →