Kollégium 8. SZUMHA, ÁTLAGHA

A Kocka Kollégiumban kis csoportokban foglalkoznak a kollégistákkal. Az egyes csoportokat színekkel azonosítják. A félévi értesítők kiosztását követően minősítik a csoportokat  a diákok átlagai alapján, valamint az intézmény honlapján megjelentetik a statisztikai adatokat. Az alábbi feladatban ezt kell elkészítenie.

8. A D30:S33 tartományban számítsa ki hibamentesen másolható képlet segítségével az adott csoport tanulóinak hiányzási, illetve tantárgyi átlagait!

 Kétféle megoldás:

1. Átlagot megkapjuk, ha összeadjuk az összes számot, ami az adott csoporthoz tartozik, és elosztjuk a csoportlétszámmal.

Ehhez a SZUMHA függvényt használjuk, és az előzőleg kiszámolt csoportlétszámot.

SZUMHA - A megadott feltételnek vagy kritériumnak eleget tevő cellákban található értékeket adja össze

Képlete:

 =SZUMHA(tartomány;kritérium;összegtartomány), ahol k meghatározza azon számok halmazát, amelyeket össze szeretnénk adni.

tartomány - a B2:B27 , ahol a csoportok szerepelnek, mivel másolni szeretnénk a képletet, ezt le kell zárni F4 segítségével : $B$2:$B$27

kritérium - a csoport neve, amit össze szeretnénk adni : B30, de mivel nem csak egy csoport van és külön-külön az összes csoportot szeretnénk összegezni, ezért nem teljesen zárjuk le. Addig nyomjuk az F4-et, amíg ezt kapjuk: $B30

összegtartomány : amit össze szeretnénk adni, tehát a hiányzásokat és a tantárgyakat, D2:D27, ezt is csak részben zárjuk le F4-gyel, mert minden hányzás és tantárgy esetében  a saját oszlopát vizsgáltatjuk :D$2:D$27

Az eredményt osztani kell a csoport létszámmal, hogy megkapjuk az átlagot. Ezt is részben zárjuk le, mert a csoport másolásnál változik : $C30

Képlete:

=SZUMHA($B$2:$B$27;$B30;D$2:D$27)/$C30

A cella jobb alsó zöld négyzete segítségével másoljuk a képletet az S oszlopig, majd az így másolt képletet lefelé a zöld csoportig.

Eredmény:

2. Eleve az ÁTLAGHA függvényt használjuk, akkor nem kell osztani, viszont nem használjuk itt fel az előző feladatban kiszámolt létszámot.

ÁTLAGHA -Az adott feltétel vagy kritérium által meghatározott cellák átlagát (számtani közepét) számítja ki

Képlete:

=ÁTLAGHA(tartomány;kritérium;(átlag_tartomány))

tartomány  - a B2:B27 , ahol a csoportok szerepelnek, mivel másolni szeretnénk a képletet, ezt le kell zárni F4 segítségével : $B$2:$B$27

kritérium - a csoport neve, aminek az átlagát szeretnénk megkapni: B30, de mivel nem csak egy csoport van és külön-külön minden csoport átlagát szeretnénk megkapni, ezért nem teljesen zárjuk le. Addig nyomjuk az F4-et, amíg ezt kapjuk: $B30

átlag_tartomány -  amiből az átlagot szeretnénk megkapni, tehát a hiányzásokat és a tantárgyakat, D2:D27, ezt is csak részben zárjuk le F4-gyel, mert minden hiányzás és tantárgy esetében  a saját oszlopát vizsgáltatjuk :D$2:D$27

Képlete:

=ÁTLAGHA($B$2:$B$27;$B30;D$2:D$27)

Itt nem kell osztani, mert már eleve az átlagot számoltattuk ki.

Eredmény: 

 Az eredmény azonos , csak a megoldáshoz használt függvény különbözik.

Hogyan oldanád meg?

9. A T30:T33 tartományban a csoportok átlagát a tantárgyi átlagok alapján számítsa ki!