Excel-suli IV.: Minimum és maximum értékek
Az Excelben nem létezik feltételes minimum és maximum, megmutatjuk, hogyan hozhatjuk mégis létre ezeket a sokszor hiányzó függvényeket.
Amikor egynél több "nem üres" cellát jelölünk ki, az Excel - hacsak meg nem változtattuk az alapértelmezett beállításokat - az alsó státussoron azonnal megmutatja, hogy a tartományban mennyi adatot vagy információt tartalmazó cella van, mennyi a (jó esetben azonos típusú) számszerű adatok összege, átlaga, minimuma és maximuma. Ezeket az információkat természetesen különböző Excel-függvényekkel mi magunk is le tudjuk kérdezni.
Valószínűleg senkinek sem kell bemutatnunk a DARAB2, SZUM, ÁTLAG, MIN és MAX függvényeket (a DARAB csak a numerikus cellákat számolja meg). Minden bizonnyal sokaknak még az sem okoz gondot - főleg, ha olvasták az Office-suli korábbi leckéit -, ha az első háromhoz valamilyen feltételt szeretnénk adni. Hiszen az ezeréves DARABTELI-nek és SZUMHA-nak, valamint a 2007-ben debütáló ÁTLAGHA-nak kötelező paramétere a vizsgálandó tartomány és a feltétel, valamint utóbbi kettőnek opcionálisan még megadhatunk összeg- és átlagtartományt is.
De mi van akkor, ha ezekhez hasonlóan csak adott feltételeknek eleget tevő, numerikus értékek minimumát és maximumát szeretnénk megtudni? A közel ötszáz Excel-függvény között meglepő módon ugyanis nem találunk MINHA vagy MAXHA nevűt sem. Így előttünk a lehetőség, hogy egy vagy több lépésben mi magunk rakjuk össze a MIN-t vagy a MAX-ot egy HA-val.
MINHA segédoszloppal
Példánkban az Európai Unió statisztikai hivatalának adatait használjuk; az Eurostat EU-s regionális adatokat tartalmazó oldaláról gyűjtöttem néhány NUTS2 szintű (Nomenclature des Unités Territoriales Statistiques), azaz régiós adatot. Némi válogatás és tisztogatás után rendelkezésünkre áll a 28 EU-s tagállam 272 régiójának fél tucat mutatója, amiből először nézzük meg például a bruttó hazai terméket, azaz a GDP-t.
Miután ez alkalommal első feladatunk, hogy megvizsgáljuk, az Európai Unió országaiban milyen értékek között mozog az egyes régiók GDP-je, másoljuk a négyjegyű NUTS-kódot, a régiók megnevezését, valamint a régiós GDP-adatokat tartalmazó oszlopokat egy tiszta munkafüzet F, G és H oszlopaiba. Azért pont ide, mert az A oszlopban soroljuk fel a 28 országot, a B oszlopban találhatók a kétbetűs országkódok, míg a C és D oszlopokban a minimum és maximum értékek, így egy oszlop kihagyással már láthatjuk is a "nyers adatokat".
Mint az jól látható, a régiókódok első két karaktere minden esetben megegyezik az ország kódjával, a másik két karakterrel pedig szerencsére nem kell foglalkoznunk. (A NUTS-rendszer negyven éves története alatt elég sok változás történt, így a korábban még többé-kevésbé logikus sorszámozás mára egyes országoknál teljesen összekuszálódott.) Próbaként először nézzük Ausztria adatait.
Persze megtehetnénk, hogy a C2-es cellába beírjuk: =MIN(H2:H10), hiszen jelen pillanatban ebben a tartományban található Ausztria kilenc régiójának adata, de igencsak macerás lenne mind a 28 ország esetében megnézni, mettől meddig vannak az egyes országok adatai. Arról nem is beszélve, hogy mi van akkor, ha nem 28 ország, hanem több száz termékcsoport eladási adatait akarjuk megvizsgálni? Vagy mit csinálunk abban az esetben, ha mondjuk a régiókat (termékeket) nem országonként (termékcsoportonként), hanem név szerint rendezték, hiszen így a régiólista az olasz Abruzzóval kezdődik, amit a finn Åland, majd a portugál Alentejo követ, ellentétben az eredeti Burgenland, Niederösterreich, Wien sorrenddel.
Ehelyett leendő segédoszlopunk első cellájába, azaz az I1-be írjuk be az AT rövidítést, majd közvetlenül alá, Burgenlanddal egy sorba a következő képletet:
Ez a képlet megvizsgálja, hogy az adott sorban levő régió kódjának első két betűje megegyezik-e a "fejbe" írt országkóddal, és ha igen, akkor a cellába bemásolja a H2-ben található régiós adatot, ha nem, akkor pedig az alapértelmezett HAMIS-értéket. Ha most ezt a képletet lehúzzuk mind a 272 régió mellé (vagy egyszerűen kijelöljük az I2-es cellát, és duplán kattintunk a jobb alsó sarkába), akkor azt láthatjuk, hogy az Excel az első kilenc sorban megismétli a mellette levő számot, majd egészen a lista aljáig feltölti HAMIS értékkel a segédoszlopunkat.
Így ha erre az oszlopra nézzük meg a minimális [K2 =MIN(I2:I273)] és a maximális [K3 =MAX(I2:I273)] értékeket, akkor is a helyes adatokat kapjuk. Ráadásul ha az I1-es cellát átírjuk BE-re, akkor máris változik a kép, és segédoszlopunk kilenc HAMIS-sal fog kezdődni, amit a 11 belga régió adata követ, majd folytatódik a lista a további HAMIS-okkal, és a K2-es és K3-as cellákban immáron a legkisebb és a legnagyobb Belga GDP-adatot látjuk.
Segédoszlop helyett tömb
Ez így már valamivel szimpatikusabb megoldás, de még messze nem az igazi, hiszen így a 28 országhoz 28 segédoszlopra van szükség, ami kényelmetlen és helypazarló megoldás. Használjunk inkább "virtuális segédoszlopokat", azaz tömböket. Ezek a tömbök tulajdonképpen hasonlítanak a GYAKORISÁG függvénynél létrejövő tömbünkhöz, létrehozásuk is nagyjából ugyanúgy történik, a végeredmény mégis alapvetően más lesz.