Excel táblázatkezelés kezdőknek › 9.3 lecke
Excel tanfolyam · 9.3 lecke
ÁTLAGHA: feltételes átlag
Mennyi a 9. b átlaga az évfolyamdolgozaton? Jobban teljesítettek az angolosok, mint a németesek? Mennyi a sok órát mulasztó diákok átlagpontszáma? A feltételes átlagot az ÁTLAGHA számolja ki egy lépésben. Ebben a leckében azt is megnézzük, mit kezd az üres cellákkal és a nullákkal, és mi történik, ha egyetlen diák sem felel meg a feltételnek.
- ⏱ kb. 45 perc
- 🎯 kezdőknek
- 🎒 10. osztálytól
A lecke végére
- ÁTLAGHA-val feltételes átlagot számolsz;
- kerekíted és összeveted a csoportátlagokat;
- kezeled, ha egy csoportban nincs adat;
- tudod, mit kezd az ÁTLAGHA az üres cellával és a nullával;
- megérted, mikor tér el a SZUMHA/DARABTELI hányadostól.
Amit már tudsz
Az ÁTLAGHA
Ugyanúgy épül fel, mint a SZUMHA (9.2): ÁTLAGHA(tartomány;feltétel;[átlag_tartomány]).
| Kérdés | Képlet (B: osztály, C: nyelv, D: pontszám, E: mulasztás) |
|---|---|
| A 9. b átlaga | =ÁTLAGHA(B2:B60;"9.b";D2:D60) |
| Az angolosok átlaga | =ÁTLAGHA(C2:C60;"angol";D2:D60) |
| A legalább 40 pontos dolgozatok átlaga | =ÁTLAGHA(D2:D60;">=40") (egytartományos alak) |
| A 10 óránál többet mulasztók átlagpontszáma | =ÁTLAGHA(E2:E60;">10";D2:D60) |
- Az eredményt gyakran érdemes kerekíteni:
=KEREKÍTÉS(ÁTLAGHA(…);1)(5.5, 5.7). - Az összehasonlításhoz számold ki a teljes átlagot is (
=ÁTLAG(D2:D60)): egy csoport jobb vagy rosszabb ennél?
Ha nincs adat
Ha egyetlen sor sem felel meg a feltételnek (például a 9. d-ből még senki nem írt dolgozatot), az ÁTLAGHA nem tud osztani, és #ZÉRÓOSZTÓ! hibát ad (4.9). Ez várható hiba, ezért kezelhető (8.6):
| Megoldás | Képlet |
|---|---|
| HAHIBA-val | =HAHIBA(ÁTLAGHA($B$2:$B$60;G2;$D$2:$D$60);"nincs adat") |
| pontosabban: előbb megszámolni | =HA(DARABTELI($B$2:$B$60;G2)=0;"nincs adat";ÁTLAGHA($B$2:$B$60;G2;$D$2:$D$60)) |
💡 Kategóriatábla
Ugyanúgy, mint a 9.1-ben és a 9.2-ben: a kategóriák egy oszlopban (G2: 9.a, G3: 9.b…), a tartományok rögzítve, a feltétel vándorol. Egy táblázatban mutathatod a csoport létszámát (DARABTELI), összegét (SZUMHA) és átlagát (ÁTLAGHA).
Üres cella, nulla, hányados
- Az ÁTLAGHA az átlagtartomány üres és szöveges celláit kihagyja (mint az ÁTLAG, 5.3).
- A 0 valódi érték, ezért beleszámít, és lehúzza az átlagot.
🔮 Jósold meg!
A 9. b-nek 4 diákja van a listán; a pontszámaik: 40, 30, (üres: nem írta meg), 50. Mennyi (a) =ÁTLAGHA(B2:B60;"9.b";D2:D60) (b) =SZUMHA(B2:B60;"9.b";D2:D60)/DARABTELI(B2:B60;"9.b")?
Megoldás
(a) 40: az ÁTLAGHA az üres cellát kihagyja, (40 + 30 + 50) ÷ 3. (b) 30: a SZUMHA 120-at ad, a DARABTELI viszont a 9. b sorait számolja meg, az üres pontszámú diákét is: 120 ÷ 4. A két eredmény tehát akkor tér el, ha a csoportban hiányzó érték van. A „megírók átlagához” az ÁTLAGHA a helyes; ha a hányadost akarnád használni, a nevezőbe a DARABHATÖBB (9.4) kellene a nem üres pontszámokra.
Kihívás: csoportok összevetése
📈 Ki teljesített jobban?
Vedd elő a 9.1-ben készült osztálystatisztikai listát (nem, nyelv, dolgozat, mulasztás; két üres dolgozatcellával).
- Számold ki a teljes átlagot és külön a lányok és a fiúk átlagát egy tizedesjegyre. Melyik csoport teljesített jobban, és mennyivel?
- Ugyanígy az angolosok és a németesek átlagát.
- Mennyi a 10 óránál többet mulasztók és a legfeljebb 10 órát mulasztók átlaga? Mit mutat a különbség, és mit nem (6.5: összefüggés vagy ok)?
- Készíts kategóriatáblát nyelvenként: létszám (DARABTELI), összpontszám (SZUMHA), átlag (ÁTLAGHA). Vegyél fel egy harmadik nyelvet is (például francia), amelyet senki nem tanul, és kezeld a hiányt „nincs adat” felirattal.
- Ellenőrizd a jóslás tanulságát: a SZUMHA/DARABTELI hányados és az ÁTLAGHA hol tér el, és miért?
- Készíts oszlopdiagramot a nyelvenkénti átlagokról (6.2).
Hibavadászat
🐞 1. Peti hibás összesítője
Peti osztályonkénti átlagtáblájában a 9. d sorában #ZÉRÓOSZTÓ! áll, és emiatt a táblázat alatti „legjobb osztályátlag” (=MAX(H2:H5)) is hibát mutat.
Mi a megoldás?
A 9. d-nek nincs dolgozata, ezért az ÁTLAGHA nem tud osztani (2. rész). Peti kezelje a hiányt: =HA(DARABTELI(…)=0;"nincs adat";ÁTLAGHA(…)). A „nincs adat” szöveget a MAX kihagyja, így az is helyes eredményt ad.
🐞 2. Lili lehúzott csoportátlaga
Lili a dolgozatot meg nem író diákoknak 0 pontot írt be, „hogy ne legyen üres cella”. A 9. b átlaga így 30 lett 40 helyett.
Mit tegyen?
A 0 valódi értékként számít az átlagba (3. rész, 5.3). Lili hagyja üresen a meg nem írt dolgozatok celláját (vagy írja be: „nem írta meg”), így az ÁTLAGHA csak a megírt dolgozatokat átlagolja.
Összefoglalás
- =ÁTLAGHA(tartomány;feltétel;[átlag_tartomány]): feltételes átlag.
- Ha nincs megfelelő érték: #ZÉRÓOSZTÓ!; kezelése HA(DARABTELI(…)=0;…) vagy HAHIBA.
- Az üres és a szöveges cellákat kihagyja, a 0-t beleszámolja.
- A SZUMHA/DARABTELI eltér, ha a csoportban hiányzó érték van.
- Kategóriatábla: létszám, összeg és átlag egy helyen, rögzített tartományokkal.
Ellenőrizd magad!
Mit ad az ÁTLAGHA, ha egyik sor sem felel meg a feltételnek?
#ZÉRÓOSZTÓ! hibát.
Hogyan számolod ki a C2:C40 azon értékeinek átlagát, amelyek 50-nél nagyobbak?
=ÁTLAGHA(C2:C40;">50")
Beleszámít-e a 0 az ÁTLAGHA eredményébe?
Igen, a 0 valódi érték.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (statisztikai függvények, feltételtől függő számítások), adatok elemzése, csoportosítása; kapcsolódás a matematikához (átlag, csoportátlagok összevetése) és a kritikus gondolkodáshoz (összefüggés és ok). Az NKP-okostankönyvre épülő emelt szóbeli tételek ÁTLAGHATÖBB-ös feladatának előkészítése.
Módszertan
A lecke az ÁTLAGHA-t a SZUMHA és a DARABTELI rokonaként mutatja be, és a hiányzó adatok kezelésére építi a tanulságot: a jóslás számszerűen megmutatja, mikor és miért tér el a „kézi” hányados a függvénytől. A hiányzó csoport kezelése a 8.6 célzott hibakezelését alkalmazza. A csoportátlagok összevetésénél a 6.5 „összefüggés nem ok” gondolata a statisztikai következtetés helyes megfogalmazására nevel.
Időterv
Az ÁTLAGHA: 8 perc. Ha nincs adat: 7 perc. Üres cella, nulla, hányados és jóslás: 8 perc. Kihívás: 18 perc. Hibavadászat: 4 perc.
Következő lecke: 9.4 Többfeltételes darabszám, összeg és átlag
A 9. b lányai, akik legalább 40 pontot értek el: DARABHATÖBB, SZUMHATÖBB és ÁTLAGHATÖBB.
