23. fel./9 megoldása (Excel)

AB átlag függvény

Lépésről-lépésre

A táblázat az A1:H151 tartományban százötven hallgató vizsgaadatait tartalmazza a következők szerint: név, nem (1-es vagy 2-es), kor, pontszámok (0 és 100 között) angolból, franciából, számtechből, protokollból és pénzügyből.

(emelt szintű) Feladat: számolja ki a férfiak átlagát minden tárgyra a D155:H155 tartományban

Két megoldás létezik (teljesen egyenértékűek, csak a második megoldáshoz meg kell tanulni még egy függvényt, egy olyat, amely az ECDL vizsgánál más feladatban nem fordul elő, valahol még egy hasonló, az AB.SZUM() függvény jöhet jól) Töltsd le a megoldást tartalmazó munkafüzetet (bejelentkezett felhasználók számára elérhetően, csatolmányként alul) és ennek segítségével kövesd a megoldást lépésről-lépésre.

1. megoldás - segédtáblázat és a jól ismert függvények segítségével

Először egy külön táblázatba kigyűjtjük csak a férfiak elért pontszámait, majd a megadott helyen úgy számoljuk ki az átlagot ahogy az iskolában tanultuk: összeadjuk a segédtáblázatba kigyűjtött értékeket, majd osztjuk mindezt annyival ahány számot kigyűjtöttünk az adott tantárgyból.

Minden lépést egyetlen függvény segítségével oldjuk meg, így a képlet másolásával komolyan számolnunk kell, abszolút és kevert cellacímzésekre lesz szükségünk. Nézzük mindezt lépésről-lépésre

  • A segédtáblázatunkat az igazitól jobbra a J1:N151 készítjük el, ide kigyűjtve a férfiak által tantárgyanként elért eredményeket. a J2 cellába beviendő képlet: =HA($B2=1;D2;""), s ezt a képletet másoljuk a teljes tartomány minden cellájába.
    • A kritériumnál, a HA() függvény első paraméterénél, megkérdezzük, hogy a B oszlopban, a képlettel egy sorban lévő cellában 1-es érték van-e, ekkor férfi az illető :-) A sor hivatkozását relatívnek hagyjuk meg, mivel azt szeretnénk, ha a sor hivatkozása változna sorban történt másolásnál (a következő hallgatónál a következő sorból vegye ezt az értéket)
    • Abban az esetben, ha a B oszlopban 1-es van, akkor a HA() függvénnyel megjelenítjük az elért pontszámot
    • Abban az esetben, ha nem 1-es van a B oszlopban, akkor egyszerűen üresen hagyjuk a segédtáblázatunk celláját. (a SZUM() függvénnyel szándékozunk összeadni az oszlopok tartalmát, s ha itt üres cella van, akkor nem haragszik meg a SZUM() függvény, nem hibázik, a ténylegesen számokat tartalmazó cellákat összeadja és punkt-tum)
  • A következő lépésként a D155 cellába beviendő képlet: =SZUM(J2:J151)/DARAB(J2:J151) a hivatkozások relatívek, s így a képlet másolható a H155 celláig. A képletben a SZUM() függvénnyel összeadtuk az oszlop tartalmát - vagyis a férfiak által elért összpontszámot) - majd ezt osztottuk az összeadásban szereplő pontszámok darabszámával.

A munkánk során is szoktunk segédtáblázatot alkalmazni, amelyeket aztán el is szokás tüntetni, a nyomtatásnál pl egyáltalán nincs szükség ezek megjelenésére. Trükk a segédtáblázat eltüntetésére: az eltüntetés pofon egyszerű - állítsd át a segédtáblázat betűszínét fehérre - a héttérrel megegyező színűre :-) Ha a teljes oszlopok vagy sorok nem tartalmaznak semmi értékes adatot a segédtáblázaton túl, akkor a sorok/oszlopok elrejtése is megoldás.

2. megoldás - AB.ÁTLAG() függvények segítségével

Először tanulmányozd át az AB függvények a számításokban c. írást, s aztán jöhet ez :-) Mivel a feladatot az első lépésben a 155. sorban oldottuk meg, így most a munkafüzet szerinti 156. sorban készítjük ezt el.

  • Létrehozandó a feltétel tartomány A feltételünket a "nem" oszlopra szeretnénk megadni, ezért az oszlop címkéjét bemásoltam a B159 cellába (most még beírni sem bonyolult, de a másolás adja a pontos eredményt bármely más esetben), s csak erre a mezőre (oszlopra) adunk meg feltételt, a feltételt pedig a következő sorban, a címke alatti B160 cellába. A feltételünk az =1, de elég beírni az 1-es számot. A feltétel tartományunk a B159:B160
  • A D156 cellába beviendő képlet: =AB.ÁTLAG($B$1:$H$151;D$1;$B$159:$B$160)
    • Az AB.ÁTLAG() függvény az Adatbázis kategóriában található;
    • adatbázis bemenő értékként a B1:H151 tartományt jelöljük meg, majd tettük abszolúttá, hogy a másolásnál ne mozduljon el ez a hivatkozás. Ez a tartomány tartalmazza az első sort a címekkel, s mindazt az oszlopot, amelyre szükségünk lesz.
    • mező-ként azt az oszlopot kell megadni, amellyel a számítást szeretnénk végeztetni. Az oszlop nevének cellahivatkozását úgy kellett megadni, hogy a képlet másoldásakor a sor ne változzon, viszont az oszlopazonosító igen, hiszen oszlopról-oszlopra a soron következő tantárgynál elért pontszámmal kell majd számolnia. Így lett a második paraméter a D$1
    • Harmadik paraméterként az előzőleg létrehozott feltétel tartományt, vagyis a $B$159:$B$160 tartományt adjuk meg abszolút hivatkozással, hogy a képletet másolva ez ne 'másszon' el.
  • A képletet másoljuk egészen a H156 celláig és készen is vagyunk.

A tanítványaimnak azt szoktam mondani, hogy az AB függvényeket hagyd a tanulnivaló végére, s ha nem férne bele az időbe a vizsgáig, akkor sincs nagy baj, ettől még lehet sikeres a vizsgád. A 23-as feladatban a másik két emeltszintű feladat megoldása tökéletesen kiválthatja ezt a 9-est :-)

A hallgatók nagy része azonban nem is az emelt szintű, sokkal inkább az alapszintű feladatokon bukik el (nincs meg a 90%), s nem azért mert nem tudja, sokkalta inkább a figyelmetlenség az oka, mert pl nem olvassa el a feladatot és félkövérre állította és piros színűre, de mondjuk nem döntötte meg a cella tartalmát ;-)

Szóval nem az AB függvényeken fog múlni :-) de ha vetted a fáradtságot és kipróbáltad, megtanultad, úgy egy jól hasznosítható tudásra tettél szert, gratulálok hozzá :-) További jó tanulást kívánok :-) Kapcsolódó anyagok: AB függvények a számításokban