Excel-suli III.: Kategóriák és esetszámok
Folytatjuk az ismerkedést az Excel kevésbé ismert függvényeivel, és megnézzük, hogyan tudjuk egyszerűen kategorizálni adatainkat.
Office-sorozatunk első részében bemutattuk az FKERES függvény egyik képességét, a pontos egyezésre keresést. Az FKERES (és ritkábban használt párja, a VKERES) másik fontos funkciója, amikor nem pontos egyezés szerint akarunk valamit megtalálni, hanem azt keressük, hogy egy adott érték melyik tartományba esik. Ilyenkor az FKERES első három paraméterét, a kereséséi értéket, a táblát, amiben keresünk és az oszlopszámot ugyanúgy kell megadnunk, mint amikor pontos egyezésre keresünk, míg a negyedik, "tartományban keres" nevű paraméternek IGAZ-nak kell lennie, vagy miután ennek ez az alapértelmezett értéke, akár el is hagyhatjuk, így például az =FKERES($B2;$F$2:$H$6;2) és az =FKERES($B2;$F$2:$H$6;2;IGAZ) képlet pontosan ugyanazt az eredményt adja.
Ebben az esetben az FKERES megkeresi az első olyan cellát az F2:H6 tartomány első oszlopában, amelynek értéke már nagyobb, mint a B2-es celláé, majd visszalép egyet, végül találatként visszaadja az adott sor második oszlopában található értéket. Ehhez természetesen olyan táblára van szükségünk, aminek az első oszlopában lévő - az esetek túlnyomó többségében, de nem feltétlenül numerikus - értékeknek növekvő sorrendben kell elhelyezkedniük, különben egyes esetekben, ha nem is hibát, de hibás eredményt kapunk eredményül. Amennyiben a keresési érték - ami lehet szám, százalék, dátum, idő, de akár szöveg vagy logikai érték is - kisebb, mint a táblázat első oszlopának első eleme, akkor a már jól ismert #HIÁNYZIK hibaüzenetet kapjuk eredményül, hiszen a tartomány első soránál nincs hova visszalépni, nincs "nulladik sor".
Nézzünk két példát is az FKERES ilyenfajta használatára. Nemrégiben az iskolások megkapták bizonyítványaikat, amiben az egyes tantárgyak év végi érdemjegyét a tanév közben szerzett osztályzatok (súlyozott) átlagából számolták ki a tanárok - gyakorlatilag hasonló algoritmust alkalmazva. Hogyan néz ez ki Excel-módra? Első lépésként az A oszlopban soroljuk fel a tantárgyakat, a B oszlopban pedig - a manapság leggyakrabban - az e-napló által kiszámított átlagokat.
Ezt követően hozzunk létre egy olyan ötsoros táblát, aminek első oszlopában a klasszikus 0,0; 1,5; 2,5; 3,5; 4,5 határok szerepelnek (az első tétel persze csak a túlzott pesszimizmus miatt), mellettük pedig az érdemjegyek egytől ötig és ezek szokásos szöveges változata az elégtelentől a jelesig. Innentől kezdve a "bizonyítvány kitöltéséhez" nem kell mást tennünk, mint a C2-es cellába beírni a fentebb már példaként hozott képletet:
,a D2-be pedig ugyanezen jegy szöveges változatához harmadik paraméternek a 2-est kijavítani 3-asra: =FKERES($B2;$F$2:$H$6;3), végül kijelölni ezt a két cellát, és lehúzni az utolsó tantárgyig.
Népességnagyság-csoportok
Másik példánkhoz a múlt hónapban is használt, a Helységnévtár letölthető adataiból "kiollózott" lakónépesség-táblázatot használjuk. Ugyan ez esetben nem igazán, de például egy térképes megjelenítéshez kimondottan hasznos, ezért az A oszlopban helyezzük el a településneveket, a B oszlopba pedig az egyes települések 2014. január 1-jén érvényes lakónépesség-adatokat (a 2015-ös településszintű adatok az ősz elején jelennek majd meg).
Az E2:G8 tartományban hozzunk létre hét, tipikusnak mondható népességnagyság-csoportot úgy, hogy az E oszlopba írjuk az egyes kategóriák alsó határértékét, az F-be egy-egy gondolatjelet (Alt+0150), hogy kicsit jobban nézzen ki ez a "jelmagyarázat-tábla", az F oszlopban pedig ne konkrét értékeket adjunk meg, hanem mindig vonjunk ki egyet a következő kategória alsó határértékéből (G2 =E3-1, G3 =E4-1 stb.), az utolsó, G8-as cellában pedig számoltassuk ki a legnagyobb lakónépesség-értéket [=MAX(B:B)].
Elvileg az egyes kategóriák jelölésére használhatnánk például az alsó határértékeket is, de ennél "megszokottabb" megoldás, hogy a H "segédoszlopot" feltöltjük 1-től 7-ig. Így az első település "kategorizálása" a következő képlettel történik:
Ez fogja a Fejér megyei Aba lakóinak számát (4471) a B2-es cellából, megkeresi az első olyan alsó határértéket, amely már nagyobb ennél a számnál (5000), visszalép egy sort, majd visszaadja ebből a sorból a negyedik, azaz a H oszlopban lévő cella értékét, a 3-at. Ezt követően már nincs is más dolgunk, mint a C2-es cellában található képletet lehúzni a 3177. sorig, vagy egyszerűen kijelölni a C2-es cellát, majd duplán kattintani a kijelölés jobb alsó sarkában lévő kis négyzetre, hogy kategorizáljuk valamennyi települést.