Automatizáljuk szűrésünket Excelben és tanuljunk egy kicsit a makrókról!

Nem komplikált feladat, de tökéletes kiindulópont egy kis VBA-elmélkedéshez

2015. október 09. - Office Guru

Egyszerűbb táblázatok, kisebb listák esetén nem nagyon van értelme az ilyen automatizmusnak, de igazán komplex adathalmazok, felhasználói beavatkozást igénylő listák vagy éppen feltuningolt dashboardok esetén mindenképpen hasznos lehet egy olyan VBA-megoldás, amellyel felhasználó által kiválasztott paraméterek mentén automatikusan tudunk filterezni. A következő posztban erre mutatok egy egyszerűbb példát, amely az érdeklődők számára már jó kiindulási alap lehet további műveletek automatizálására.

Fogjuk a következő mintatáblánkat, amely megyénként és városonként tartalmaz bevételi értékeket - erre fogunk majd gyorsan egy kis automatikus szűrést varázsolni:

171.jpgEzután, bár nem létfontosságú, de célszerű beszúrni egy táblát a Ribbon Insert füle alatt található Tables szekció Table menüje segítségével a kis táblázatunkra, hogy később aztán könnyebben hivatkozhassunk rá:

172.jpg

173.jpgHa ezt megtettük, egyrészt megjelenik a Ribbonon a táblára használható Design fül, ahol egyrészt csinosítgathatjuk listánkat, másrészt itt láthatjuk azt is, hogy mostantól Table2 néven hivatkozhatunk majd rá (persze ez átírható bármi másra).

Következő lépésként döntsük el, hogy pontosan hogy is akarunk majd szűrést automatizálni, tehát például drilldown menüből válasszon majd a user és arra szűrjünk makróval vagy szövegboxba írja be a szükséges információkat stb.

Én most a példámban szövegboxba fogom bekérni az információkat (de ugyanezzel a logikával működik más elemekkel is), úgyhogy most szépen menjünk a Ribbon Developer fülére és a Controls szekcióban az Insert menü alól szúrjunk be két Active-X-es szövegdobozt, majd valami kis leírást is mellékeljünk, hogy mit is várunk ezekbe a dobozokba:

174.jpgVegyük észre, hogy a boxok beszúrásával a Controls szekcióban be is kapcsoltuk a Tervező (Design) módot, amelyre azért lesz szükségünk továbbra is, mert mindkét szövegboxunkat meg kell kicsit formáznunk. Kattintsunk jobb gombbal az egyiket kijelölve, majd a legördülő menüből válasszuk a Properties menüpontot:

175.jpgItt két dolgot tegyünk meg (tehetünk többet is, de maradjunk az egyszerű lépéseknél), egyrészt a BackColor tulajdonságnál válasszunk ki más háttérszínt (engem megőrjít a szimpla fehér) illetve a LinkedCell tulajdonságnál adjuk meg mondjuk a Q1 cellát:

176.jpgEzt ismételjük meg a másik szövegboxnál is, de ott Q2 cellát kapcsoljuk hozzá a mezőnkhöz. Ezután egyébként a Q oszlopot akár el is rejthetjük, hiszen ezekre a segédcellákra másoknak nem lesz szüksége (igen, tudom, hogy ez csak felesleges lépés, de haladjunk szépen lassan a folyamatok megértésében). Ezzel egyébként azt tettük, hogy a szövegboxainkba beírt értékek bekerülnek majd a Q1 illetve a Q2 cellákba.

A hangulat kedvéért még varázsoljunk egy gombot is a táblánk mellé szintén az Insert menüből a Command Buttonra való kattintással, majd utána ha már létrehoztuk, jobb gombbal itt is menjünk be Properties menübe, hogy legalább a Caption tulajdonságot módosítsuk és beírjunk valami szöveget a gombunkhoz, valahogy így:

177.jpgNagyjából most itt tartunk, sok már nincs hátra, hogy működőképes legyen az aprócska kis makrónk:

178.jpgInnen jön a VBA-lépés, ahova úgy jutunk át legegyszerűbben, hogy jobb egérgombot nyomunk a gombunkon, majd a View Code menüre kattintunk:

179.jpgEzzel pedig létre is hoztuk a kattintás eseményt, azaz az eseményt, amely akkor fog bekövetkezni, ha userünk rákattint a gombra:

180.jpgInnentől pedig már csak két lépés és két sornyi kód van hátra, méghozzá a következő kód két sorban és két verzióban:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=[q1]

Mit is tesz ez a sor? Fogja Table2 tartományunkat az aktív sheeten (ha szimplán csak összeolvassuk a kód első részét, akkor is értelmezhető ez), majd tesz rá egy Filtert - ezután pedig szűrni fog a tábla második oszlopára (Field:= paraméter határozza meg) a Criteria1:= paraméternél megadott értékre, jelen esetben Q1 cellára, ahova az első szövegboxunkba beírt eredményt tároltuk be.

A másik sor a fentiek és a második szövegboxba kért információ alapján:

ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3, Criteria1:=">[q2]"

Az aktív sheeten fogja Table2 táblánkat, tesz rá egy filtert, majd a tábla harmadik oszlopában le fog szűrni a kritériumban megadott paramétert teljesítő értékekre, azaz azokra, amelyek értéke nagyobb, mint a Q2 cella (figyeljünk rá, hogy Q2-ben számként legyen az értékünk!).

181.jpgMost menjünk vissza az Excelhez, a Controls szekcióban kapcsoljuk ki a Tervező módot majd próbálgassuk csak művünket:

182.jpgUgye milyen izgalmas volt ez az egyszerű feladat, ha még nem mélyedtünk el VBA-ban?

Csak okulásként álljon itt egy lista a táblákkal kapcsolatos egyéb VBA-parancsokról, amelyet mindenképpen használjunk fel egy kis gyakorlásra szobánk áldásos magányában:

183.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr757953594

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

♔bаtyu♔ 2015.10.11. 09:26:05

Apró pici kis programozás, kimenet pedig xml file és a csákó az összes mezőre szűrhet. Ráadásul még alapból jól is néz ki, e mellett az user kattintásra válogathat is a különféle stílusok között. A több telephely, több felhasználó sem probléma, sőt az sem, ha a ezek életükben alig láttak Excelt.

Szerintem ezért nem terjedt el annyira a makrózás, pedig érdekes terület.

Imre Fodor 2016.09.29. 09:11:51

Hello, ezzel kapcsolatban lenne egy olyan érdekes kérdésem, hogy hogyan lehet megoldani az összevont cellákon való szűrést? A cél az lenne, hogy három sorban (külön cellában) lévő adat mellett egy dátum, összevont (három sor) cellában. Ha a dátumra szűrök akkor a bal oldalon csak a legfelső sor marad látható ugye, de nekem mind a három kellene... Most egy nem annyira elegáns megoldással a dátum is három sorban szerepel, de az 1. és 3. sorban fehér betűszínnel... :-) Így a szűrő azokban a cellákban is megtalálja... viszont mivel más műveleteket is végzek később az adatokkal, kicsit zavaró, hogy a dátum is három helyen van ott... Minden segítséget örömmel fogadok és előre is köszi!

toportyánféreg 2016.11.05. 15:53:05

Itt (www.extendoffice.com/documents/excel/1955-excel-filter-merged-cells.html) írnak egy módszert, de a lényege ugyanaz, minden sorban szerepel a dátum, csak utána ráhúz egy cellaegyesítést, de a formátummásolóval (nem a cellaegyesítéssel), és így megmaradnak alatta a dátumok. Működik, de szerintem ettől függetlenül az egész cellaegyesítés felejtős, ha még később is dolgozni kell az adatokkal..