Excel-suli II.: Feltételes összegek és átlagok
Folytatjuk az ismerkedést az Excel ritkábban használt függvényeivel és Magyarország településeivel.
Haladjunk tovább a megkezdett sorozatunkkal, amelyben a magyar irányítószámokon mutattuk be, hogy az Excel kevesek által használt függvényei mi mindenre képesek. Ez alkalommal szeretnénk bevonni kísérleteinkbe a községeket és nagyközségeket is, ezért első lépésként látogassunk el a Központi Statisztikai Hivatal oldalára, majd a Kiadványok menüből válasszuk ki a Helységnévtárat.
Ezen az oldalon nemcsak a települések, hanem a településrészek neveire is kereshetünk (például "Budaihegy"), sőt, irányítószám, távhívó körzetszám, továbbá a négy- és ötjegyű KSH-azonosítók alapján is megtalálhatunk mindent. A települések adatlapjai több tucat hasznos és érdekes adatot tartalmaznak a nemzetiségi névtől a jogálláson, a régión és megyén, az elmúlt 25 év lakónépességén, a nemzetiségi és vallási megoszláson át egészen a területszervezési változásokig. Ezeket az adatokat egyszerűen letölthetjük Excel- és OpenDocument-számolótábla formátumokban az alkalmazás Letöltés oldaláról.
Ezúttal a települések adatai közül csak néhányra lesz szükségünk, ezért hozzunk létre egy új munkafüzetet, azon belül is egy "települések" munkalapot, és erre másoljuk át a helységek megnevezését, jogállását (város, község stb.), a megye megnevezését, továbbá a területet, a lakónépességet és a lakások számát, bár ez utóbbiakból egyelőre csak a lakónépességet fogjuk használni. Miután a táblázat egyaránt tartalmazza Budapest és 23 budapesti kerülete adatait, ezért keressük meg és töröljük Budapest adatainak sorát, valamint a megyenév oszlopban a "főváros"-t cseréljük le "Budapest"-re. Végül, némi egyszerűsítésként a "megyeszékhely, megyei jogú város" jogállásokat egyszerűsítsük le "megyei jogú város"-ra a [Kezdőlap] Keresés és kijelölés/Csere funkcióval.
Összegek és átlagok
Bemelegítésként kezdjük egy egyszerű feladattal: nézzük meg, hogy a különböző jogállású helységekből hány darab van, és mekkora ezek átlagos népessége. Ehhez először is egy új munkalapon soroljuk fel az öt különböző jogállást (A2:A6): község, nagyközség, város, megyei jogú város, fővárosi kerület. Ezután a B2-es cellában használjuk a múlt hónapról már ismerős DARABTELI függvényt a következő formában:.
, azaz számoltassuk meg a programmal, hogy a "települések" munkalap B oszlopában hányszor szerepel az A2 cellában található szöveg, azaz a "község". Jó esetben megkapjuk a 2698-at eredményül, és ha ezután a cella jobb alsó sarkát megfogjuk, és lehúzzuk B6-ig, akkor már meg is kaptuk, hogy az országban 110 nagyközség, 322 város, 23 megyei jogú város (amiből 18 megyeszékhely) és persze 23 fővárosi kerület van. Ahhoz, hogy az átlagos népességet megkapjuk - legalábbis hagyományos módon -, először is szükségünk van a különböző jogállású településtípusok össznépességére, amit a SZUMHA függvénnyel tudunk kiszámoltatni.
A SZUMHÁ-nak meg kell adnunk egy feltételtartományt, egy feltételt és egy összegtartományt. (Ez utóbbit el is hagyhatjuk, ekkor meg fog egyezni a feltételtartománnyal.) Így a községek népességének kiszámításához írjuk be a C2-es cellába a következő képletet: .
Mint látható, a feltételtartomány ez esetben is a "települések" munkalap B oszlopa, a feltétel ismét az A2-es cellában található "község", ám összegezni már a "települések" munkalap E oszlopát fogjuk, amiben az egyes helységek lakónépességei találhatók. (A SZUMHA függvény feltételeként, elsősorban számok esetében használhatunk kisebb-nagyobb ellenőrzéseket is, amiket idézőjelek közé kell tenni, például "<5" vagy ">=2,5".) Nincs is más dolgunk, mint ezt az összeget elosztani az imént kiszámított darabszámmal (D2=C2/B2), majd a C2 és D2 cellákat kijelölni, és lehúzni a hatodik sorig.
Amennyiben tényleg csak az átlagokra van szükségünk, az Excel 2007-ben és az újabb verziókban használhatjuk az ÁTLAGHA függvényt is, aminek a szintaxisa pontosan megegyezik a SZUMHÁ-éval, vagyis először a kiértékelendő tartományt kell megadnunk, majd a feltételt, végül azon cellák tartományát, amelyek számtani közepét szeretnénk kiszámítani.