Folytatjuk Office-sorozatunkat, a Word és a OneNote után a programcsomag talán legizgalmasabb szoftveréhez érkeztünk, amely szakértő kezek alatt tudja megmutatni valódi komplexitását. Persze használhatjuk alapvető szöveges listák kezelésére vagy éppen egyszerűbb összeadásokra is, ám az igazi izgalmak akkor következnek, mikor a táblázatokban megtalálható adathalmazokat elkezdjük hasznosítani, egyre több és több információt kinyerve azokból, kiaknázva minden lehetséges összefüggést. Sorozatunk első részében ezért nem akarunk feltárni egy egész területet, inkább csapongunk a táblázatkezelő képességei között, hogy kedvcsinálóként egy konkrét példán át megmutathassuk az Excel szépségeit; nézzük meg tehát, mi mindent tehetünk egy irányítószámmal.
Az univerzális FKERES
Nem véletlenül választottunk a postai adatot; olyan számsorról van szó, amelyből számtalan információ kiköveztethető az egyes pozícióban lévő karakterek alapján, másrészt gyakori adatról, bármikor összefuthatunk egy-egy címekkel teli adatbázissal. Elsőként nézzük meg, miként tudunk egy négyjegyű számsorhoz települést társítani, tehát hogyan kerül például a 4244 kód mellé Újfehértó automatikusan egy többezres listában. Ehhez egy olyan kiindulási dokumentumra lesz szükségünk, amely tartalmazza az összes irányítószámot, és az azokhoz rendelt településneveket. Keressünk rá valamelyik internetes keresőben a „Magyarország irányítószámok xls” kifejezésre, majd mentsük le valamelyik szimpatikus Excel-állományt. Nyissuk meg azt, és ellenőrizzük hitelességét. Ha mindent rendben találunk, térjünk vissza eredeti dokumentumunkhoz, navigáljunk az irányítószámok mellett található oszlop első sorába, majd a szalagon keressük meg a [Képletek] csoporton belül a [Függvény beszúrása] lehetőséget, és kattintsunk rá.
Függvény beszúrása: a megjelenő panelen rengeteg segítséget kapunk
A megjelenő panelen az FKERES-függvényt válasszuk, amely működése során egy táblázat bal szélső oszlopában keres egy általunk megadott értéket, majd ha megtalálja azt, akkor az adott sor meghatározott oszlopából visszatér egy adattal. Azaz példánkban a négyjegyű irányítószám találatakor az ahhoz tartozó várost fogja kiírni. Ennek megfelelően a keresési érték az irányítószámot tartalmazó cella legyen (esetünkben A3), a táblánál adjuk meg azokat a rekordokat, amelyeken belül a keresést végre akarjuk hajtani (mivel másik dokumentumban kutattunk, nálunk ez az iranyitoszamok.xlsx fájl ZIP munkafüzetének A1--B3112 cellák közötti területe volt), a kiírandó számnál az eredménynek szánt oszlopok értékét adjuk meg (nálunk 2), a tartományban pedig a pontos egyezés reményében nullára állítsuk a keresést. Képletünk a leírtaknak megfelelően a következőképpen épül fel: „=FKERES(A3;[iranyitoszamok.xlsx]ZIP'!$A$1:$B$3112;2;0)”.
Hibakezelés
Egy lépéssel már közelebb jutottunk a rendezett táblázathoz, ám jó eséllyel nem működik majd tökéletesen a képletünk, hacsak kiindulási irányítószám-listánk nem tartalmazott minden budapesti irányítószámot. Ha rendre a „#HIÁNYZIK” hibaüzenettel traktál minket az Excel, akkor biztosak lehetünk benne, hogy nekünk kell utólag kezelnünk a főváros kerületeit; szerencsénkre ez nem túl bonyolult művelet. Segítségünkre áll a HAHIBA függvény, ami azonnal aktiválja magát, amint egy képlet hibás értékkel tér vissza. Esetében két paramétert kell megadni: az eredeti képletet, majd pontosvesszővel elválasztva a hiba esetén visszaadandó értéket. Előbbi példánkból kiindulva ez a következő lesz: „=HAHIBA(FKERES(….);”Budapest”)”, ahol az FKERES rész a fentebb már megadott képlettel behelyettesíthető. Természetesen a HAHIBA szintaxisán belül különböző feltételeket is beállíthatunk: a HA függvényt használva több eredményt is kiírathatunk. Utóbbi módszer mélyebb megértéséhez vegyünk egy újabb egyszerű példát, és nézzük meg, miként választhatjuk külön a nyugat- és kelet-magyarországi településeket, valamint a fővárost és annak agglomerációját az irányítószám alapján.
Irányított beillesztés: külső adatbázisokat is felhasználhatunk
Feltételek és elágazások
Mivel tudjuk, hogy a 3–6 kezdetű számok Kelet-, a 7-9 közöttiek pedig biztosan Nyugat-Magyarországot jelölik, viszonylag egyszerű a dolgunk. Fogjuk a HA függvényt, adjuk meg a feltételt, majd az igaz és a hamis értékeket). Ez alapján a dunántúli városokat a „HA(A3>=7;"Nyugat-Magyarország";”Nem nyugat”)” kifejezéssel szűrhetjük ki, ahol értelemszerűen a „nem nyugat” kitételt érdemes tovább finomítanunk egy újabb HA használatával a 3–6 kezdetű irányítószámokat kikapva, majd egy újabb feltétellel az agglomerációt és a fővárost kell különválasztanunk, így végül a „=HA(C3>=7;"Nyugat-Magyarország";HA(3<=C3;"Kelet-Magyarország";HA(1;"Budapest";"Agglomeráció")))” kifejezéshez kell eljutnunk. Aki akarja, a kettes irányítószámmal rendelkező településeket is szétválaszthatja, mi most utóbbit nem részleteznénk bővebben, hiszen egy új FKERES-táblával, HA-elágazással könnyen megoldható a dolog. Foglalkozzunk inkább egy nagyon nem mellékes problémával, amin eddig egyszerűen átugrottunk, nevezetesen hogy képletünkben egy egész cellát vizsgáltunk, pedig csak az irányítószám első számjegyét kellett volna elemeznünk. Pótoljuk be a hiányosságot a BAL függvényt használva, ami egy szövegből balról haladva kikapja az általunk megadott első x darab karaktert a BAL(cella,x) szintaxis alapján. Sajnos a képlet nem számot, hanem karaktert ad vissza, ezért a számszerűsítésével is foglalkoznunk kell, amit az ÉRTÉK() függvény végez el nekünk, így a fentebb már leírt hosszú feltételrendszerbe a C3 helyére mindenhova az „=ÉRTÉK(BAL(C3;1))” kifejezést helyettesítsük be, hogy a képletünk hibamentesen működhessen.
Település mérete
Lépjünk tovább, nézzük meg az irányítószám alapján a települések méretét, ami nem lesz túlságosan nehéz, hiszen már mindegyikhez van nevünk. Vonjunk be ehhez külső segítséget. A Wikipédia oldalán keressünk rá a „Magyarország városainak listája” szócikkre, jelöljük ki az egészet, majd az Excelben hozzunk létre egy új Varosok nevű munkalapot, és a szalag [Kezdőlap] fülén a [Beillesztés] ikonjára kattintva válasszuk a „Célformátummal egyező formátum” lehetőséget. Töröljük a felesleges cellákat, csupán a várostípus és a népesség oszlopokra lesz a továbbiakban szükségünk.
Feltételes formázás: gyors és hatékony adatvizualizálás a cellákon belül
Lépjünk vissza a kiindulási munkalapunkra, majd vessük be ismét az FKERES-függvényt, csak ezúttal a település neve alapján keressük ki annak a típusát, egyúttal egy HAHIBA elágazást is bevetve, hogy a listában nem szereplő helyiségeket is kezelni tudjuk. Mi a „=HAHIBA(FKERES(B3;Varosok!A2:B346;2;0);"Kisebb település")” képletet használtuk e célra, de a végleges kifejezés dokumentumonként eltérhet.
Ha a lakosságszámot is jelölni akarjuk, akkor kissé meg kell bolygatnunk a dokumentumunkat, a főváros kerületeit külön szedve. A Wikipédia oldalán ezt információt is megtaláljuk, így célravezető a Városok munkalapon lévő Budapest sort eltávolítani, majd elhelyezni ott mind a 23 kerületet „Budapest 101–123” névvel ellátva azokat, hogy a későbbiekben egyszerű legyen az azokra történő hivatkozás. Ezután térjünk vissza kiindulási munkalapunkra, és módosítsuk első FKERES-függvényünket úgy, hogy a budapesti kerületek pontosan a másik munkalapon látható formában jelenjenek meg. Ismét az ÉRTÉK és a BAL utasításokat kell használnunk, az első három karaktert kiszedve az irányítószámból, csak ezúttal az ÖSSZEFŰZ parancsot is bevetjük majd, hogy a két karakterláncot egyesítsük. Ügyködésünk végén a „=HAHIBA(FKERES(A3;ZIP!A2:B3361;2;0);ÖSSZEFŰZ("Budapest ";ÉRTÉK(BAL(A3;3))))” kifejezéshez hasonló láncot kell kapnunk. Ennek birtokában már nem lesz nehéz a népességszám kigyűjtése az FKERES használatával, csupán a listában nem szereplő kisebb településeket kell valahogy kezelnünk. A HAHIBA használatával szöveges visszajelzést is kiírhatunk, illetve egy tetszőleges számot is bevethetünk, hogy a későbbiekben vizualizálni tudjuk az eredményt. Mi ez utóbbi opciót választottuk, és a kisebb községeknél, falvaknál az 500-as értéket írtuk be a „=HAHIBA(FKERES(B3;Varosok!A2:C369;3;0);500)” kifejezést használva.
Kaptunk egy listát, benne településekkel, az azokhoz tartozó népességszámmal és egyéb más adatokkal, amikkel eddig nem foglalkoztunk. Ezeket érdemes összevetni, kombinálni egymással, és felállítani közöttük egy sorrendet. A népesség mérete jó kiindulópont ahhoz, hogy kipróbáljuk az Excel feltételes formázás funkcióját, amit a szalag [Kezdőlap] csoportjában érhetünk el. Segítségével közvetlenül a cellán belül vizualizálhatjuk az értékeket adatsávokkal és színskálákkal, akár egyedi szabályokat és minimum–maximum értékeket beállítva. Próbáljuk ki, a témához illően válasszuk ki az [Adatsávok] és az [Egyszínű kitöltés] lehetőséget.
Már majdnem készen vagyunk, már csak azt nézzük meg, hogy a táblázatunkban mennyien szerepelnek azok, akik a fővárosból, annak agglomerációjából vagy netán Kelet- és Nyugat-Magyarországról érkeztek. Ezt az adott oszlop elemeinek az összeadásával érhetjük el, azaz meg kell néznünk, hogy ott például mennyi „Főváros” bejegyzés szerepel. Ehhez a DARABTELI függvényt használjuk, amely szöveges értékek összesítésére is alkalmas, és paraméterezésekor csak a tartományt és a keresett kifejezést kell megneveznünk, azaz esetünkben nagyjából a „=DARABTELI(D3:D22;"Főváros")” kifejezéssel érdemes számolni.
(Nyitókép: Maciej Kasprzyk)
