Excel-suli II.: Feltételes összegek és átlagok

Községek és városok adatai

Látható, hogy a jogállásoknál megkülönböztetünk községeket és nagyközségeket, valamint városokat és megyei jogú városokat. Mit tehetünk akkor, ha mi nem akarjuk ezeket megkülönböztetni, és nekünk egyben van szükségünk a községek és a városok adataira, függetlenül attól, hogy sima vagy nagyközségek, netán megyei jogú városok? Csak az a megoldás, ha külön-külön számoltatjuk meg és összegezzük ezeket, majd a két értéket összeadjuk? Természetesen nem. Több lehetőségünk is van arra, hogy egy-egy DARABTELI és SZUMHA függvénnyel oldjuk meg a problémát.

Az egyik, kissé speciális megoldás az ún. dzsókerkarakter(ek) használata. Ez esetben azt használjuk ki, hogy a két-két feltétel igencsak hasonlít egymásra, így a községek és nagyközségek számát a "=DARABTELI(települések!B:B;"*község")", míg a városok és megyei jogú városokét a "=DARABTELI(települések!B:B;"*város")" függvénnyel tudjuk lekérdezni. Hasonlóképpen előbbiek lakosainak számát a "=SZUMHA(települések!B:B;"*község";települések!E:E)", míg a 345 városét a "=SZUMHA(települések!B:B;"*város";települések!E:E)" számíthatjuk ki. Sőt, ezekhez hasonlóan működni fog az "=ÁTLAGHA(települések!B:B;"*község";települések!E:E)" és az "=ÁTLAGHA(települések!B:B;"*város";települések!E:E)" képlet a községek és a városok átlagos népességszámának kiszámítására.

.

De mit tehetünk akkor, ha nincs ilyen szerencsénk a két (vagy több) feltétel hasonlóságával? Ilyenkor azt a trükköt vethetjük be, hogy mind a DARABTELI, mind a SZUMHA esetében több feltételt is megadunk - Excel-tömbként. Ehhez először is írjuk be a következő képletet például a B14-es cellába: .

.

Mint látható, a két - idézőjelek közé zárt - feltételünket kapcsos zárójelek közé helyeztük, és pontosvesszővel választottuk el. Azt, hogy mi történik ilyenkor, úgy ellenőrizhetjük a legegyszerűbben, ha a képlet beírása után nem az Enter-billentyűt ütjük le, hanem megnyomjuk az F9-et. Ilyenkor ugyanis az Excel kiszámolja a (rész)eredményt, és egyből meg is jeleníti az egyenlőségjel mögött. Esetünkben most azt kell látnunk a cellában, hogy "={2698;110}", azaz a községek és a nagyközségek számát kapcsos zárójelek között, pontosvesszővel elválasztva. Ugyanakkor megnyomva az Entert ebből csak a 2698, vagyis az első kiszámított érték jelenik meg, ám innentől már sínen vagyunk, hiszen nincs más dolgunk, mint összegezni a különböző feltételekkel kiszámított értékeket. Ehhez előző képletünket kell "közrefogni" a jól ismert SZUM-mal: .

.

és már meg is kaptuk a várt 2808-as értéket. Hasonlóképpen számoltathatjuk meg a városok számát ("=SZUM(DARABTELI(települések!B:B;{"város";"megyei jogú város"}))"), illetve összegezhetjük községek és a városok népességét a  "=SZUM(SZUMHA(települések!B:B;{"község";"nagyközség"};települések!E:E))", illetve a "=SZUM(SZUMHA(települések!B:B;{"város";"megyei jogú város"};települések!E:E))" képletek segítségével.

.

Természetesen ez a megoldás közvetlenül nem használható az ÁTLAGHA esetében, hiszen hiába számoltatjuk ki például a községek és a nagyközségek népességszámainak átlagát az "=ÁTLAGHA(települések!B:B;{"község";"nagyközség"};települések!E:E)" képlettel, a tömbben eredményül kapott - külön-külön persze helyes - számok összege (4558,3) és az átlaga (2279,1) is teljesen fals eredményt ad. Ha "segédcellák" használata nélkül szeretnénk megkapni a végeredményt, akkor marad a "hagyományos függvényhalmozás": =SZUM(SZUMHA(települések!B:B;{"község";"nagyközség"};települések!E:E))/SZUM(DARABTELI(települések!B:B;{"község";"nagyközség"})) - ami természetesen a helyes 1041 főt adja eredményül.

Excel-függvényfordító

Valószínűleg sokakkal előfordult már, hogy egy-egy exceles problémára angol, német vagy más idegen nyelvű honlapon talált megoldást. Ilyenkor többnyire a fő problémát nem is a szövegkörnyezet megértése jelenti, hanem annak kitalálása, hogy a leírásban használt Excel-függvényeknek mi a magyar megfelelője? Elviekben ebben segíthet az Office 1038-as mappájában megtalálható "ezeréves" FUNC.XLS, ám ennél sokkal kényelmesebb és gyorsabb az online Excel-Translator használata.

.

A német Excel-fejlesztő, Mourad Louha által készített Excel szótár- és fordítóprogram, jelenleg 18 nyelven érhető el, közte - Szabó Krisztinának, az Adatkertészet blog szerzőjének köszönhetően - magyarul is. Segítségével nemcsak megkereshetjük az egyes függvénynevek különböző nyelvű fordításait, akár az adott nyelvű listát böngészve, akár az oldal keresőjét használva, hanem a Translator funkcióval összetett Excel-formulákat is egyetlen lépésben lefordíthatunk a program által ismert 35 nyelv bármelyikéről bármelyikére - szükség esetén automatikusan cserélve a vesszőket és pontosvesszőket, valamint a CELLA és INFO függvények attribútumait.

- 01: Mire jó az irányítószám?
- 02: Feltételes összegek és átlagok
- 03: Kategóriák és esetszámok
- 04: Minimum és maximum értékek
- 05: Keresés a tartományokban
- 06: Automatikusan bővülő grafikonok
- 07: Évek, hónapok, napok
- 08: Formás értékek I.
- 09: Formás értékek II.
- 10: Formázások feltételekkel I.
- 11: Formázások feltételekkel II.
- 12: Új diagramtípusok

Oldalak: 1 2