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 ÁTLAG-ot, az üres cellát és a nullát (5.3);
  • a DARABTELI-t és a SZUMHA-t (9.1, 9.2);
  • a #ZÉRÓOSZTÓ! hiba kezelését (8.6);
  • a kerekítést (5.5).

Az ÁTLAGHA

Ugyanúgy épül fel, mint a SZUMHA (9.2): ÁTLAGHA(tartomány;feltétel;[átlag_tartomány]).

KérdésKé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ásKé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).

  1. 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?
  2. Ugyanígy az angolosok és a németesek átlagát.
  3. 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)?
  4. 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.
  5. Ellenőrizd a jóslás tanulságát: a SZUMHA/DARABTELI hányados és az ÁTLAGHA hol tér el, és miért?
  6. 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.