rangsor-kezelő függvények I.
2025-12-07 Excel 2024
fogalmak
Ha egy szám-halmaz elemeit nagyság szerint sorrendbe állítjuk, akkor ez a halmaz elemeinek rangsora. A rangsor lehet növekvő- vagy csökkenő rendezettségű. Előbbi esetben a legkisebb szám-, utóbbi esetben a legnagyobb szám áll a sor elején.
A szám, a halmaz rangsorában elfoglalt helye a szám rangsor-pozíciója. Egy szám rangsor-pozícióját egy sorszámmal adjuk meg: hányadik ez a szám a halmaz rangsorában.
A halmazban többször előforduló számok rangsor-pozíciójának sorszámozása szoftverenként eltérő. Az Excel sorszámozó függvényei az „ismétlő” és az „átlagoló” módszert alkalmazzák. Az ismétlő sorszámozás az azonos számokhoz az ismétlődő szám első előfordulásának sorszámát rendeli. Tehát, ha a halmaz három darab a tizenhármas számot tartalmaz, akkor mindhárom az első tizenhármasnak kiosztott hetes sorszámot kapja. Az átlagolós módszer a feltételezett folyamatos sorszámok számtani közepét rendeli az ismétlődő számokhoz. Az előbbi példánál maradva, az átlagolós módszer szerint mindhárom szám sorszáma a hét, a nyolc és a kilenc számtani közepe lesz.
A rangsor legkisebb számát a halmaz „minimum”-ának, legnagyobb számát pedig a halmaz „maximum”-ának nevezzük. A rangsorban közepén álló szám a halmaz „medián”-ja. Persze a rangsor közepén álló számról, csak páratlan elemszámú halmaz esetén beszélhetünk. Ha a halmaz elemeinek száma páros, akkor a középen álló két szám számtani közepe lesz a halmaz mediánja.
egyszerű rangsor-kezelő függvények
Ebben a cikkben a leggyakrabban használt rangsor-kezelő függvényeket mutatom be. Úgy is fogalmazhatnék, hogy a „mindennapi élet” rangsor-kezelő függvényeit.
Ahogy így elnézegetjük a táblázatot, megállapíthatjuk hogy, olyan túl sok variációnk nincs: vagy a számot kérdezzük le a rangsor-pozíciója alapján, vagy a rangsor-pozíciót a szám alapján.
min, max
A két függvénnyel a számok rangsorának első illetve utolsó elemét kérdezhetjük le. A „számok”-ba természetesen beleértjük a dátumokat és az időpontokat is. A függvényeknek legfeljebb kettőszázötvenöt argumentumot adhatunk meg. Ezek lehetnek hivatkozások, kifejezések és esetleg konstansok.
A függvények hivatkozás-argumentumaikban figyelmen kívül hagyják a logikai értékeket és a szöveg adattípusú számokat, de viszont a kifejezés-argumentumaikban deklaráltakat számként kezelik. A HAMIS logikai érték nullának (0) az IGAZ egynek (1) felel meg. A kifejezés-argumentumaik kiértékeléseként kapott, nem szám-karaktereket tartalmazó szövegek, mindkét függvény esetében, hibaértéket generálnak.
minha, maxha
A két függvény a feltételekkel szelektált halmaz legkisebb, illetve legnagyobb számát adja eredményül. Első argumentumukkal kell a szám-halmazt deklarálnunk. További argumentumaik párban állnak: tartomány-feltétel.
Első ránézésre érthetetlen ez a sok tartomány. Hát nem a szám-halmaz elemeit akarjuk szelektálni?
Csak Léna és Géza adott be esszét az A csoportból, tehát az ő pontszámaik közül kellett kiválasztania a függvénynek a „legnagyobbat”. Természetesen közvetlenül a számokat is szelektálhatjuk, csak akkor a számokat tartalmazó tartományt kell az argumentum-páros első elemeként megadnunk.
Bonyolultabb a helyzet ha a tartományok nem vektorok, hanem mátrixok.
Az ábrán látható példában két feltétel-tartományt látunk. A létszámot és a darabszámot. Beszürkítettem azokat a cellákat, amelyek megfelelnek a feltételüknek. A termelékenység a számok tartománya, de mondhatjuk úgy is, hogy a statisztikai tartomány. Minden olyan cella részt vesz a statisztikai vizsgálatban, amelynek pozíciója a feltétel-tartományokban eleget tett a saját feltételének. Ezeket a cellákat is beszürkítettem.
A különböző nagyságú vagy eltérő tájolású tartomány-argumentumok #ÉRTÉK! hibaértéket generálnak.
a feltétel szintaktikai szabályai
A feltétel két elemből áll: reláció és viszonyítási alap. A reláció lehet: nem egyenlő (<>), kisebb (<), kisebb, de legfeljebb egyenlő (<=), nagyobb (>) és nagyobb, de legalább egyenlő (>=). A jelek sorrendje (<>, <=, >=) kötött. A rossz sorrend, például „=>”, nem hibaüzenetet, hanem nullát (0) eredményez.
Az „egyenlőség” reláció szándékosan maradt ki a felsorolásból, mert az egyenlőségjelet nem kell kitennünk, ha azonosságot kerestetünk.
A viszonyítási alap lehet: szám, dátum, idő, logikai érték, szöveg, hivatkozás és kifejezés. Hogyan kell ezeket a típusokat a feltételbe beírnunk, ha egyenlőséget deklarálunk.
Tehát, a dátumot, az időpontot és a szöveget idézőjelezni kell a viszonyítási alapban. Ha nem egyenlőséget deklarálunk, akkor...
...az idézőjelek között álló relációs jeleket „és” karakterrel (&) kapcsoljuk a viszonyítási alaphoz.
A feltétel szövegkonstansaiban helyettesítő karaktereket is használhatunk: a csillagot (*) meghatározatlan számú- és a kérdőjelet (?) egyetlen karakter helyettesítésére. Figyelem, figyelem! A „meghatározatlan szám”-ba a nulla is beletartozik!
A kérdőjelet és a csillagot, nem karakter-helyettesítő szerepben, a „hullámvonal” karakterrel (~) kell a viszonyítási alapban szerepeltetni. A feltételben a kis- és nagybetűk nincsenek megkülönböztetve.
És még néhány különleges feltétel...
kics, nagy
A szám-halmaz meghatározott sorszámú legnagyobb, illetve legkisebb számát a kétargumentumos NAGY illetve KICSI függvényekkel választhatjuk ki. Például, a harmadik legnagyobb- vagy a má¬so¬dik legkisebb számot. Első argumentumukkal a halmazt tartalmazó tartományt deklaráljuk, a másodikkal pedig a kiválasztandó elemet határozzuk meg a rangsor végpontjától kezdődő sorszámmal.
A képen látható A1:G1 tartomány számai közül választottam ki a harmadik legnagyobbat és a második legkisebbet. A függvények először beolvassák a számokat a memóriába, azután rendezéssel kialakítják a rangsort. Ahogy én is tettem a hatodik sorban. Az elemek sorrendbe állítása csak a számok indexelésével (sorszámozásával) lehetséges. Ahogy én is tettem az ötödik és a hetedik sorban...
A két függvényt általában nem egyetlen-, hanem meghatározott számú elem kiíratására használjuk. A következő képen erre a tipikus alkalmazásra látunk példát.
Először a három legnagyobb pontszámot írattattam ki a D2:D4 tartományban. A függvény második argumentumát, az előzőleg besorszámozott C2:C4 tartomány első cellájára hivatkozva adtam meg. A függvény első argumentumának abszolút hivatkozása „rögzíti” az elemzendő tartományt a kép-let másolásakor, míg a második argumentum relatív hivatkozása teszi lehetővé a sorszámok léptetését a másolatokban.
Ha nincs szükség látható sorszámokra, akkor a SOR függvénnyel közvetlenül képezhetjük a sorszámokat a függvények második argumentumában. A SOR függvény egy sorszámot ad eredményül: az egyetlen argumentumával meghatározott cella sorának indexét. Ezt láthatjuk a C6:C8 tartományban, amelynek képleteivel a három legkisebb pontszámot írattattam ki.
medián
Az Excelben a rangsor közepén álló számot a MEDIÁN függvénnyel választathatjuk ki. Argumentumai cella- és tartományhivatkozások, kifejezések és konstansok lehetnek. A függvény eredményét az üres cellák, a szövegek és a logikai értékek nem befolyásolják. Páros elem-szám esetén a képzett medián a rangsor közepén álló két szám számtani közepe.
A képen az első sorban álló számok rangsora: 128, 3, 2. Középen a hármas áll, akkor ő lesz a három szám mediánja. Az ötödik sorban álló számok rangsora: 128, 3, 2, 0. A rangsor középen a hármas és a kettes áll. Akkor ennek a két számnak a számtani közepe lesz a négy szám mediánja: (2+3)/2 » 2,5.
A MEDIÁN egyik tipikus alkalmazását mutatja a következő példa: egy szám-halmazt két azonos elem-számú részre bontunk a számok nagysága alapján. Egy cégnél szétosztható pénz maradt az év végére. Ez jó hír, de van egy rossz is: a pénz csak a kollégák felének elegendő. A főnök leginkább a hűséget díjazza. A belépési dátumok alapján határozzuk meg, kik számíthatnak prémiumra!
A belépések mediánja tehát 2013-09-28. Ezt a dátumot azonban hiába keressük a C oszlopban, csak 2013-06-06-ot találunk (Goda Ernő), amely pár hónappal korábbi, és 2014- 01-20-at (Engi Ottó), amely pár hónappal későbbi. Találós kérdés: hogy lett akkor a medián annyi, amennyi?
rang.egy, rang.átl
A két függvénnyel tehát egy szám rangsor-pozícióját kérdezhetjük le. Magyarul, a vizsgált szám hányadik a rangsorban. Argumentumaik azonosak.
A harmadik argumentum elhagyható. Ha nem adjuk meg, vagy értéke nulla (0), akkor a rangsor csökkenő-, ha értéke egy (1), akkor növekvő rendezettségű lesz.
A függvény csak a számokat vizsgálja az üres cellákat, a szövegeket és a logikai értékeket figyelmen kívül hagyja. Ha az első argumentumával meghatározott számot a függvény nem találja a halmazban a #HIÁNYZIK hibaértéket kapjuk eredményül.
A két függvény működése csak a halmazban többször előforduló számok kezelésében eltérő. A sorszámok kiosztásakor a RANG.EGY a többször előforduló szám első előfordulásának sorszámát adja a többi ismétlődő számnak is, a RANG.ÁTL viszont az ismétlődések által elfoglalt rangsor-pozíciók átlagát.
Az A oszlopban álló számok rangsorában mindhárom nulla a hármas (3) sorszámot kapott a RANG.EGY függvénytől. A rangsorban így követik egymást a számok: az első a nyolcas (8), a második a hatos (6) a harmadik pedig a nulla (0)...
Ha az ismétlődő számok rangsor-pozíciójának sorszámozás folyamatos lenne, akkor az első nulla a hármas (3), a második nulla a négyes (4), a harmadik nulla pedig az ötös (5) sorszámot kapná. Ezt a három sorszámot átlagolja a RANG.ÁTL függvény és így adja mindhárom nullának a négyes (4) sorszámot.
Tehát, mindkét függvény a többször előforduló számok rangsor-pozíciójának megállapításakor szakadozott sorszám-listát hoz létre, ami ellentmond a sorszámozás fogalmának. „A sorszámozás tárgyak, dolgok, személyek sorrendjének egymás után következő számokkal való megjelölése” mondja az értelmező szótár. Ez a meghatározás excelre lefordítva így hangzik: az ismétlődő szám második előfordulásának sorszáma egyel nagyobb, mint a szám első előfordulásának sorszáma. A harmadiké pedig eggyel nagyobb, mint a másodiké. És így tovább.
Az ismétlődő elemek folyamatos sorszámozásának alapja: az ismétlődő elemek közül az, amelyik előrébb áll a halmazban, az a rangsorban is megelőzi a halmazban őt követő azonos számot. A folyamatos sorszámok képzésénél tehát figyelembe kell venni a vizsgált elem előfordulás-számát is.
A D3-as cella képletében álló DARABHA függvény egyetlen cellát vizsgál, a C3-ast. De a cella nem a szokásos módon, cella-hivatkozással van deklarálva, hanem tartományosan :) $C$3:C3. Sőt a „tartomány” első cellája :) abszolút, az utolsó pedig relatív hivatkozással lett megadva. Ráadásul a függvény második argumentumával azt az értelmetlenséget kérdezzük, hogy a C3-as cellában, hányszor fordul elő a C3-as cellában álló szám?! Ezek a furcsaságok csak a képlet másolásakor kapnak értelmet: minden egyes másolat egy cellával növeli a függvény által vizsgált tartományt a „rögzítésnek” ($C$3) köszönhetően. A második argumentummal feltett kérdés is módosul: hányszor fordul elő a vizsgált tartományban, a vizsgált tartomány utolsó cellájában álló szám.
Ez a képletrész a halmaz minden elemének sorszámát legalább eggyel megnöveli, ezért kell az eredményből egyet levonni.
margitfalvi.arpad@proton.me
