Mini projekt: büféforgalom elemzése feltételes összesítéssel

Excel táblázatkezelés kezdőknek › 9.8 mini projekt

Excel tanfolyam · 9.8 mini projekt

Büféforgalom elemzése

A diákbüfé egy hétig naplózta az eladásait. A diákönkormányzat tudni szeretné: melyik kategória hozza a legtöbb bevételt, mi a legnépszerűbb termék, hogyan alakult a forgalom a hét második felében, és egy egyszerű „vezérlőpulton” szeretne bármikor rákérdezni egy kategóriára. Ebben a mini projektben a 9. fejezet összes függvényével elemzed a naplót, és minden eredményt ellenőrizhetsz.

  • ⏱ kb. 45 perc
  • 🧩 mini projekt
  • 🎒 10. osztálytól

A projekt végére

  • kategóriánként összesítesz és ellenőrzöd az összesítést;
  • egy- és többfeltételes kérdésekre válaszolsz;
  • mintára és dátumra szűrő feltételt adsz meg;
  • vezérlőpultot építesz legördülő listával;
  • szűrt listán helyesen összesítesz, és diagramot készítesz.

Ehhez használod a 9. fejezet tudását

  • 9.1 DARABTELI; 9.2 SZUMHA; 9.3 ÁTLAGHA;
  • 9.4 TÖBB függvények; 9.5 feltétel cellából;
  • 9.6 helyettesítő karakterek; 9.7 RÉSZÖSSZEG.

A büfénapló

Gépeld be (vagy másold be) a kitalált büfénaplót az A1 cellától. A Bevétel oszlop képlet legyen: =D2*E2.
DátumTermékKategóriaDarabEgységár (Ft)Bevétel (Ft)
2026.10.05.Csokoládéédesség123504200
2026.10.05.Kakaós csigapékáru202905800
2026.10.05.Ásványvízital152003000
2026.10.06.Csokis kiflipékáru182604680
2026.10.06.Csokoládéédesség93503150
2026.10.06.Narancsléital103003000
2026.10.07.Pogácsapékáru251203000
2026.10.07.Csokoládéédesség143504900
2026.10.07.Ásványvízital222004400
2026.10.08.Kakaós csigapékáru162904640
2026.10.08.Gumicukorédesség82502000
2026.10.08.Narancsléital123003600

💡 Először ellenőrizd az adatlistát

Egy fejlécsor, egy rekord egy sor, a dátumok valódi dátumok, a számok jobbra igazodnak, a kategóriák egységesen kisbetűsek, szóköz nélkül (7.1, 7.6). A végösszeg: =SZUM(F2:F13) → 46 370 Ft.

Kérdések

Minden kérdésre egyetlen képlettel válaszolj (a képletet és az eredményt a lista mellé, egy „Elemzés” területre írd, felirattal).
  1. Kategóriatábla: a H oszlopba a kategóriák (édesség, pékáru, ital), mellettük SZUMHA-val a bevételük, DARABTELI-vel a tételek száma, ÁTLAGHA-val egy tétel átlagos bevétele. Alá ellenőrzés: a kategóriák összege egyenlő-e a végösszeggel (9.2)?
  2. Hány darab csokoládét adtak el összesen, és mennyi volt a bevétele?
  3. Mennyi a „Csok”-kal kezdődő termékek összes bevétele (9.6)?
  4. Hány olyan pékáru-tétel volt, amelyből legalább 18 darab fogyott, és mennyi a bevételük (9.4)?
  5. Mennyi volt a bevétel október 7-től (a dátumot egy bemeneti cellába írd, 9.5)?
  6. Melyik volt a legnagyobb egy tételre jutó bevétel (5.3), és melyik kategóriához tartozik?
Megoldások az ellenőrzéshez
  1. Édesség: 14 250 Ft, 4 tétel, átlag 3562,5 Ft; pékáru: 18 120 Ft, 4 tétel, átlag 4530 Ft; ital: 14 000 Ft, 4 tétel, átlag 3500 Ft. Összesen 46 370 Ft: az ellenőrzés IGAZ.
  2. 35 darab (=SZUMHA(B2:B13;"Csokoládé";D2:D13)), 12 250 Ft.
  3. 16 930 Ft (Csokoládé 12 250 + Csokis kifli 4680): =SZUMHA(B2:B13;"Csok*";F2:F13).
  4. 3 tétel (20, 18 és 25 darab), 13 480 Ft: =SZUMHATÖBB(F2:F13;C2:C13;"pékáru";D2:D13;">=18").
  5. 22 540 Ft: =SZUMHA(A2:A13;">="&$J$1;F2:F13), ahol a J1-ben a 2026.10.07. dátum áll.
  6. 5800 Ft (Kakaós csiga, pékáru, október 5.).

Vezérlőpult és diagram

  1. Készíts vezérlőpultot (9.5): Kategória (legördülő lista: édesség; pékáru; ital), Dátumtól (dátum). Eredmények: a kategória bevétele a megadott dátumtól, a tételek száma és egy tétel átlagos bevétele (SZUMHATÖBB, DARABHATÖBB, ÁTLAGHATÖBB; hiány esetén „nincs adat”).
  2. Próbáld ki: pékáru, 2026.10.07-től → 7640 Ft, 2 tétel, átlag 3820 Ft. Ital, 2026.10.06-tól → 11 000 Ft, 3 tétel.
  3. Kapcsold be a szűrőt a listán (7.3), és a Bevétel oszlop alá írj egy RÉSZÖSSZEG(109;F2:F13) képletet a SZUM mellé (9.7). Szűrd a listát a „Csokoládé” termékre: a RÉSZÖSSZEG 12 250 Ft-ot mutat, a SZUM továbbra is 46 370-et.
  4. Készíts oszlopdiagramot a kategóriatábla bevételeiből (6.2), beszédes címmel (például „A pékáru hozta a legtöbb bevételt”) és tengelycímmel (6.7).
  5. Feltételes formázással (8.7) emeld ki a napló azon sorait, ahol a bevétel nagyobb, mint az átlagos tételbevétel.

✅ Ellenőrizd a vezérlőpultot!

A „Dátumtól” mezőbe írd az első napot (2026.10.05.), a kategóriába sorban mind a hármat: a vezérlőpult eredményeinek meg kell egyezniük a kategóriatábla soraival. Ha nem egyeznek, valamelyik feltétel vagy tartomány rossz.

Önellenőrzés és értékelés

✅ Önellenőrző lista

  • A kategóriatábla összege megegyezik a végösszeggel (46 370 Ft).
  • Mind a hat kérdésre a megoldásokkal egyező eredményt kaptam.
  • A feltételes függvényekben a tartományok rögzítettek, és ugyanabban a sorban kezdődnek.
  • A TÖBB függvényeknél az összeg- vagy átlagtartomány az első argumentum.
  • A dátumfeltétel egy bemeneti cellából jön, összefűzéssel.
  • A vezérlőpult legördülő listával működik, és hiány esetén „nincs adat” jelenik meg.
  • Szűréskor a RÉSZÖSSZEG a látható sorokat összesíti.
  • A diagramnak beszédes címe és tengelycímei vannak.
Szempont⭐ Elindultam⭐⭐ Sikerült⭐⭐⭐ Profi vagyok
FüggvényekNéhány kérdésre válaszoltam.Minden kérdés helyes függvénnyel.A legmegfelelőbb függvény, rögzített és párban álló tartományok.
RugalmasságA feltételek a képletekben.Bemeneti cellák, vezérlőpult.Legördülő lista, hibakezelés, ellenőrzés a kategóriatáblával.
BemutatásVan eredmény.Feliratozott elemzés, diagram.Beszédes cím, kiemelés, áttekinthető elrendezés.

Hibavadászat: Peti büféelemzése

🐞 Peti beszámolója

„A kategóriatáblát =SZUMHA(C2:C13;H2;F2:F13) képlettel írtam, és lemásoltam: a második sortól rossz lett. A pékárukat =SZUMHATÖBB(C2:C13;"pékáru";D2:D13;">=18";F2:F13) képlettel kérdeztem le. A dátumfeltételt így írtam: ">=J1". A „Csok” kezdetű termékekhez "Csok" feltételt használtam. A szűrt lista alatti SZUM-ra vártam, hogy változzon. Az ÁTLAGHA-ban 0 jelent meg ott, ahol nem volt adat, mert HAHIBA(…;0)-t írtam.”
Hány hibát találtál? Mit tanácsolnál Petinek?
HibaHelyesenLecke
rögzítetlen tartományok a kategóriatáblában$C$2:$C$13 és $F$2:$F$139.1, 9.2
összegtartomány a SZUMHATÖBB végénaz összegtartomány az első argumentum9.4
cellanév idézőjelben">="&$J$19.5
csillag nélküli minta"Csok*"9.6
SZUM szűrt listánRÉSZÖSSZEG(109;…)9.7
0 a hiányzó átlag helyén"nincs adat" vagy üres9.3, 8.6
Hat hibát találhattál. Ha mindet megtaláltad, a feltételes összesítés mestere vagy!

Mit tudsz már? A 9. fejezet összefoglalása

  • DARABTELI, SZUMHA, ÁTLAGHA: feltételes darabszám, összeg, átlag egy függvénnyel.
  • A TÖBB változatok több ÉS feltétellel; a VAGY-ot függvények összegével oldod meg.
  • A feltételt cellából veszed, összefűzéssel; vezérlőpultot építesz.
  • Helyettesítő karakterekkel mintára keresel.
  • Szűrt listán a RÉSZÖSSZEG (vagy ÖSSZESÍT) adja a látható sorok összesítését.
👩‍🏫 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, szűrés, diagram), adatelemzés és -csoportosítás; kapcsolódás a gazdasági ismeretekhez (bevétel, kategóriák) és az adatbázis-kezeléshez (csoportosítás, összesítés).

Módszertan

A mini projekt a 9. fejezet összes függvényét egy ellenőrizhető, kis méretű adatsoron gyakoroltatja: a megadott megoldások és a vezérlőpult „kategóriatáblával egyezik” ellenőrzése önálló hibakeresést tesz lehetővé. A kis adatsor tudatos választás: a diák az eredményt fejben vagy szűréssel is ellenőrizheti, így a függvények működését érti, nem csak alkalmazza. Peti beszámolója leckénként egy tipikus hibát ismétel.

Időterv (45 perc)

Az adatlista: 7 perc. Kérdések: 18 perc. Vezérlőpult és diagram: 13 perc. Hibavadászat: 5 perc. Önellenőrzés: 2 perc.

Következő fejezet: 10.1 Keresési érték, tábla és találat

Kezdődik a 10. fejezet: keresőfüggvényekkel két táblázat adatait kapcsolod össze.

Részösszegek és szűrt adatok: RÉSZÖSSZEG és ÖSSZESÍT

Excel táblázatkezelés kezdőknek › 9.7 lecke

Excel tanfolyam · 9.7 lecke

Részösszegek és szűrt adatok

A 7.3-ban megláttuk: ha leszűröd a listát, a SZUM továbbra is az összes sort adja össze. Mi van, ha éppen azt szeretnéd, hogy az összeg a szűréshez igazodjon? A RÉSZÖSSZEG függvény csak a látható sorokkal számol. Ebben a leckében megismered, és mellette az Adatok lap Részösszeg parancsát is, amely egy listát csoportonként összesít, akár egy kattintással.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 10. osztálytól

A lecke végére

  • RÉSZÖSSZEG-gel csak a látható sorokat összesíted;
  • a függvénykódok közül a megfelelőt választod;
  • megkülönbözteted a kiszűrt és a kézzel elrejtett sorokat;
  • ÖSSZESÍT-tel a hibaértékeket is mellőzöd;
  • a Részösszeg paranccsal csoportonként összesítesz.

Amit már tudsz

  • a szűrést és a SZUM viselkedését szűrt listán (7.3);
  • a rendezést (7.2);
  • a sorok elrejtését (2.5);
  • a SZUMHA-t (9.2).

A RÉSZÖSSZEG

RÉSZÖSSZEG(függv_szám;hiv1;…): az első argumentum egy kód, amely megmondja, milyen számítás kell; a második a tartomány.
KódKód (rejtett sorok nélkül)Számítás
1101ÁTLAG
2102DARAB
3103DARAB2
4104MAX
5105MIN
9109SZUM
  • A kiszűrt sorokat a RÉSZÖSSZEG mindig kihagyja, akár 9-es, akár 109-es kódot használsz.
  • Ha egy tartományban más RÉSZÖSSZEG képletek is vannak, azokat nem számolja bele: így nem keletkezik kétszeres összeg.
  • Gyorsan: ha egy szűrt lista alá az AutoSzum gombbal (5.2) összeget szúrsz be, az Excel magától RÉSZÖSSZEG(9;…) képletet ír.

Kiszűrt vagy elrejtett?

🔮 Jósold meg!

A büfélista bevétel oszlopa (D2:D100) összesen 48 200 Ft. (a) Leszűröd a listát a „csokoládé” termékre (bevétel: 6300 Ft). (b) Szűrés nélkül kézzel elrejtesz néhány sort (jobb kattintás → Elrejtés), amelyek bevétele 5000 Ft. Mit mutat a két esetben a SZUM(D2:D100), a RÉSZÖSSZEG(9;D2:D100) és a RÉSZÖSSZEG(109;D2:D100)?
Megoldás
(a) Szűrés: SZUM: 48 200 (mindent összead); RÉSZÖSSZEG(9): 6300; RÉSZÖSSZEG(109): 6300. A kiszűrt sorokat mindkét kód kihagyja. (b) Kézi elrejtés: SZUM: 48 200; RÉSZÖSSZEG(9): 48 200 (a kézzel elrejtett sorokat a 9-es kód beleszámolja!); RÉSZÖSSZEG(109): 43 200 (a 100 feletti kód a kézzel elrejtett sorokat is kihagyja). Ha azt szeretnéd, hogy az összeg mindig csak a látható sorokat mutassa, a 109 a biztos választás.

Az ÖSSZESÍT

Az ÖSSZESÍT(függv_szám;beállítások;tartomány) a RÉSZÖSSZEG „nagytestvére”: több számítást tud (például NAGY, KICSI, MEDIÁN), és a beállítások argumentummal azt is megadhatod, mit hagyjon ki:
BeállításMit hagy ki?
5a rejtett sorokat
6a hibaértékeket
7a rejtett sorokat és a hibaértékeket
Például: =ÖSSZESÍT(9;6;D2:D100) összeadja a bevételeket akkor is, ha néhány cellában #ÉRTÉK! hiba van (a SZUM ilyenkor maga is hibát adna). A függvényszámok a SZUM esetén ugyanazok, mint a RÉSZÖSSZEG-nél (9 = SZUM, 1 = ÁTLAG, 4 = MAX…).

A Részösszeg parancs

  1. Rendezd a listát a csoportosító mező szerint (például Osztály, 7.2). Rendezés nélkül minden „váltásnál” új csoport kezdődik!
  2. Kattints a listába → Adatok → Tagolás → Részösszeg.
  3. Állítsd be: Csoportosítási alap: Osztály; Függvény: Összeg (vagy Átlag, Darab…); Összegzendő oszlopok: például Kilogramm. OK.
  4. Az Excel minden csoport után egy „… Összeg” sort szúr be (RÉSZÖSSZEG képlettel), a végére pedig egy „Végösszeg” sort.
  5. A bal szélen megjelennek a tagolási gombok: 1 = csak a végösszeg, 2 = csoportösszegek, 3 = minden sor.
  6. Eltávolítás: ugyanebben az ablakban az Összes eltávolítása gomb.

💡 Részösszeg parancs vagy SZUMHA?

A Részösszeg parancs gyors áttekintést ad egy rendezett listán, de beleírja a sorokat a listába. Ha az összesítésnek a lista mellett, külön kell állnia, vagy a lista gyakran változik, a SZUMHA-s kategóriatábla (9.2) vagy a kimutatás (14.6) a jobb választás.

Kihívás: a büfélista többféle összesítése

🧾 Szűrés, rejtés, csoportosítás

  1. Készíts egy kitalált, legalább 30 soros büfénaplót: Dátum, Termék, Darab, Bevétel (Ft).
  2. A lista alá írj három összeget: SZUM, RÉSZÖSSZEG(9;…), RÉSZÖSSZEG(109;…).
  3. Szűrd a listát egy termékre: melyik összeg változik? Rejts el kézzel két sort: most melyik?
  4. Számold ki RÉSZÖSSZEG-gel a látható tételek átlagát (1 vagy 101) és darabszámát (2 vagy 102) is.
  5. Az egyik bevételcellába írj szándékosan hibás képletet (például =1/0). Mit mutat a SZUM, és mit az ÖSSZESÍT(9;6;…)? Utána javítsd a hibát!
  6. Másolatot készíts a lapról, rendezd a listát Termék szerint, és használd a Részösszeg parancsot a bevételek termékenkénti összegére. Kattints a 2-es tagolási gombra: mi látszik?
  7. Hasonlítsd össze az eredményt egy SZUMHA-s kategóriatáblával (9.2): ugyanazok az összegek?

Hibavadászat

🐞 1. Peti végtelen részösszegei

Peti a büfénaplón a Részösszeg parancsot használta termékenként, de a lista nem volt rendezve. Az eredmény: majdnem minden sor után egy újabb „… Összeg” sor.
Mi a megoldás?
A Részösszeg parancs minden olyan helyen új csoportot kezd, ahol a csoportosító mező értéke megváltozik (4. rész, 1. lépés). Peti távolítsa el a részösszegeket (Összes eltávolítása), rendezze a listát Termék szerint (7.2), és csak utána futtassa újra a parancsot.

🐞 2. Lili kétszeres végösszege

Lili a Részösszeg parancs után a Bevétel oszlop aljára még egy saját =SZUM(D2:D45) képletet írt. A kapott összeg pontosan kétszerese a valódinak.
Mit tegyen?
A SZUM a csoportösszeg-sorokat is összeadja, így minden tétel kétszer kerül bele: egyszer önmagában, egyszer a csoportösszegben. A RÉSZÖSSZEG viszont kihagyja a többi RÉSZÖSSZEG-et (1. rész): Lili használja a =RÉSZÖSSZEG(9;D2:D45) képletet, vagy egyszerűen a parancs által létrehozott Végösszeg sort.

Összefoglalás

  • RÉSZÖSSZEG(kód;tartomány): csak a látható (nem kiszűrt) sorokkal számol.
  • Kódok: 9 = SZUM, 1 = ÁTLAG, 2 = DARAB, 3 = DARAB2, 4 = MAX, 5 = MIN; +100: a kézzel elrejtett sorokat is kihagyja.
  • A RÉSZÖSSZEG nem számolja kétszer a többi RÉSZÖSSZEG-et; szűrt listán az AutoSzum magától ezt szúrja be.
  • ÖSSZESÍT: a hibaértékeket is mellőzheti (6, 7) – óvatosan.
  • Adatok → Részösszeg: rendezés után, csoportonkénti összeg, tagolási szintek.

Ellenőrizd magad!

Melyik kód adja a látható sorok átlagát, a kézzel elrejtetteket is kihagyva?
101
Mit kell tenni a Részösszeg parancs előtt?
Rendezni a listát a csoportosító mező szerint.
Miért kétszeres a SZUM egy részösszegekkel tagolt oszlopon?
Mert a csoportösszeg-sorokat is összeadja.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés és Adatbázis-kezelés táblázatkezelővel (szűrés, csoportosítás, összesítés), nagy adathalmazok kezelése; előkészíti az adatbázis-kezelés csoportosító lekérdezéseit (GROUP BY).

Módszertan

A lecke lezárja a 7.3-ban nyitott kérdést (a SZUM szűrés utáni viselkedése). A jóslás a kiszűrt és a kézzel elrejtett sorok különbségét, valamint a kódok (9 és 109) szerepét számszerű példán mutatja meg. A Részösszeg parancs és a SZUMHA-s kategóriatábla összevetése a megfelelő eszköz kiválasztására nevel. Lili esete a kétszeres számolás veszélyét és a RÉSZÖSSZEG „önkizáró” tulajdonságát teszi tudatossá.

Időterv

A RÉSZÖSSZEG: 7 perc. Kiszűrt vagy elrejtett és jóslás: 8 perc. Az ÖSSZESÍT: 5 perc. A Részösszeg parancs: 8 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 9.8 Mini projekt: büféforgalom elemzése

A 9. fejezet zárása: egy havi büfénapló elemzése feltételes összesítéssel, vezérlőpulttal és diagrammal.

Helyettesítő karakterek a feltételekben: * és ?

Excel táblázatkezelés kezdőknek › 9.6 lecke

Excel tanfolyam · 9.6 lecke

Helyettesítő karakterek a feltételekben

Hány olyan szakkör van, amelynek a nevében szerepel a „robot” szó? Hány diák neve kezdődik K-val? Mennyi a „Csoki”-val kezdődő termékek bevétele? Ha nem pontos egyezést keresel, hanem egy mintát, a helyettesítő karakterek segítenek: a csillag és a kérdőjel. Ezek nemcsak a függvényekben, hanem a keresésben és a szűrésben is működnek.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 10. osztálytól

A lecke végére

  • a * és a ? karakterrel mintát adsz meg a feltételben;
  • „kezdete”, „tartalmazza”, „végződése” típusú feltételt írsz;
  • pontos karakterszámra szűrsz;
  • valódi * vagy ? karaktert keresel a ~ jellel;
  • tudod, hol működnek és hol nem a helyettesítő karakterek.

Amit már tudsz

  • a feltételes összesítő függvényeket (9.1–9.4);
  • a feltételt cellából összefűzve (9.5);
  • a szövegszűrőket és a feltételtartományt (7.3, 7.4);
  • a Keresés és cserét (2.7).

A két karakter

KarakterJelentésPéldaTalálat
* (csillag)bármennyi (akár nulla) tetszőleges karakter"K*"Kiss, Kovács, K
? (kérdőjel)pontosan egy tetszőleges karakter"9.?"9.a, 9.b, 9.c (de nem 9.ab)
~ (hullámvonal)a következő * vagy ? valódi karakter, nem helyettesítő"*~?"kérdőjelre végződő szövegek
A helyettesítő karakteres feltételeknél sem számít a kis- és nagybetű: a "*robot*" a „Robotika” szót is megtalálja.

Minták

KérdésFeltételPélda képlet
… kezdődik"Csoki*"=SZUMHA(B2:B200;"Csoki*";D2:D200): a „Csoki”-val kezdődő termékek bevétele
… tartalmazza"*robot*"=DARABTELI(A2:A30;"*robot*")
… végződik"*né"a „-né” végű nevek (például Kovácsné)
pontosan 4 karakter"????"a négybetűs szavak (Rajz, Sakk)
nem tartalmazza"<>*robot*"a „robot” szót nem tartalmazók
cellából, tartalmazza"*"&$H$1&"*"=DARABTELI(A2:A30;"*"&$H$1&"*"): amit a H1-be írsz (9.5)

🔮 Jósold meg!

Egy raktárlistában a cikkszámok számként vannak tárolva: 1001, 1002, 1150, 2001, 2002. Mennyi lesz a =DARABTELI(A2:A6;"1*") eredménye? És ha a cikkszámok szövegként vannak tárolva (2.8)?
Megoldás
Számként tárolva: 0. A helyettesítő karakterek csak szövegre működnek; a számokat a DARABTELI számként hasonlítja, és a „1*” mintát nem alkalmazza rájuk. Szövegként tárolva: 3 (1001, 1002, 1150). Ha számokat akarsz „kezdete” szerint vizsgálni, használj intervallumot: =DARABHATÖBB(A2:A6;">=1000";A2:A6;"<2000") (9.4), vagy szövegfüggvényt (11.2).

Hol működik?

MűködikNem működik
DARABTELI, SZUMHA, ÁTLAGHA és a TÖBB változatokegyszerű összehasonlítás képletben: =A2="K*" csak a szó szerinti „K*” szövegre igaz
Keresés és csere (2.7)a HA feltétele önmagában (helyette: =HA(DARABTELI(A2;"K*");…) vagy szövegfüggvény, 11.2)
AutoSzűrő keresőmező, Irányított szűrés feltételtartománya (7.3, 7.4)számként tárolt értékek
keresőfüggvények pontos egyezéssel (FKERES, HOL.VAN, 10. fejezet)
adatbázisfüggvények feltételtartománya (12. fejezet)

💡 Egy cella vizsgálata mintára

Ha egyetlen cellát kell megvizsgálnod, hogy illeszkedik-e egy mintára, használd a DARABTELI-t egycellás tartománnyal: =DARABTELI(A2;"*robot*")>0 IGAZ-at ad, ha az A2 tartalmazza a „robot” szót. Ez a HA feltételeként vagy feltételes formázásban (8.8) is használható.

Kihívás: szakkörök és termékek mintára

🔍 Mintákkal válaszolj!

Készíts egy kitalált jelentkezési listát (Név, Osztály, Szakkör) legalább 25 sorral, és egy büfétermék-listát (Termék, Eladott db, Bevétel). Legyenek köztük hasonló nevűek: „Robotika”, „Robotfoci”, „Programozás és robotépítés”; „Csokis kifli”, „Csokoládé”, „Kakaós csiga”.
  1. Hányan jelentkeztek olyan szakkörre, amelynek nevében szerepel a „robot” szó?
  2. Hány diák vezetékneve kezdődik „K”-val, és hány utóneve végződik „a”-ra? (Ha a név egy cellában van, gondold végig, melyik mintát használod!)
  3. Hány jelentkező jár bármelyik 9. osztályba ("9.?"), és hány a 10.-be?
  4. Mennyi a „Csok”-kal kezdődő termékek összes bevétele (SZUMHA)?
  5. Készíts kereső cellát (H1): amit beírsz (például „kifli”), annak a tartalmazó termékeknek a darabszámát és bevételét mutassa (9.5).
  6. Emeld ki feltételes formázással (8.8) azokat a sorokat, amelyekben a szakkör neve tartalmazza a H1 szövegét.

Hibavadászat

🐞 1. Peti kevés robotosa

Peti képlete: =DARABTELI(C2:C60;"robot"). Az eredmény 0, pedig a listán sokan járnak „Robotika” és „Robotfoci” szakkörre.
Mi a megoldás?
Csillag nélkül a feltétel pontos egyezést jelent: csak a pontosan „robot” értékű cellákat számolja (1. rész). A „tartalmazza” mintához mindkét oldalra csillag kell: "*robot*"; a „kezdődik” mintához a végére: "robot*".

🐞 2. Lili mindent tudó kérdőjele

Lili egy kérdőív szöveges válaszai között azokat akarta megszámolni, amelyek kérdőjellel végződnek: =DARABTELI(D2:D80;"*?"). Az eredmény az összes nem üres válasz száma lett.
Mit tegyen?
A ? helyettesítő karakter (bármelyik egy karakter), így a "*?" minden legalább egy karakteres szövegre illeszkedik. A valódi kérdőjelhez elé kell tenni a hullámvonalat: "*~?" (1. rész).

Összefoglalás

  • * = bármennyi karakter; ? = pontosan egy karakter; ~ = a következő * vagy ? valódi.
  • Kezdete: "K*"; tartalmazza: "*robot*"; végződése: "*né"; pontos hossz: "????".
  • Cellából: "*"&$H$1&"*".
  • Működik a feltételes összesítő, kereső- és adatbázisfüggvényekben, a szűrésben és a keresésben; nem működik egyszerű összehasonlításban és számokon.
  • Egy cella mintára vizsgálata: =DARABTELI(A2;"minta")>0.

Ellenőrizd magad!

Mit jelent a "*ka" feltétel?
„ka”-ra végződő szöveg.
Hogyan keresel valódi csillagot egy szövegben?
~* formában, például "*~**".
Miért nem működik a "1*" a számként tárolt cikkszámokon?
Mert a helyettesítő karakterek csak szövegre alkalmazhatók.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (feltételtől függő számítások, szövegkezelés), információkeresés mintával; kapcsolódás az adatbázis-kezeléshez (SQL LIKE operátor: % és _) és a programozáshoz (mintaillesztés).

Módszertan

A lecke a helyettesítő karaktereket mintaillesztésként tanítja, és táblázatba gyűjti, hol működnek és hol nem, mert ez a leggyakoribb tévedés forrása. A jóslás az adattípus szerepét (szám vagy szöveg) a 2.8 tudására építve mutatja meg. Az SQL LIKE operátorral való párhuzam az adatbázis-kezelés témakörét is előkészíti. Lili esete a ~ feloldó jel szükségességét tapasztalati úton mutatja be.

Időterv

A két karakter: 6 perc. Minták és jóslás: 10 perc. Hol működik: 7 perc. Kihívás: 18 perc. Hibavadászat: 4 perc.

Következő lecke: 9.7 Részösszegek és szűrt adatok

Összeg, amely a szűréshez igazodik: a RÉSZÖSSZEG és az ÖSSZESÍT függvény, és az Adatok lap Részösszeg parancsa.

Feltételek cellahivatkozással: rugalmas feltételes összesítés

Excel táblázatkezelés kezdőknek › 9.5 lecke

Excel tanfolyam · 9.5 lecke

Feltételek cellahivatkozással

Az osztályfőnök minden héten más kérdést tesz fel: „Hányan értek el legalább 40 pontot?”, a következő héten „legalább 45-öt”, máskor „a 9. a-ból hányan?”. Ha a feltétel a képletben van, minden kérdésnél át kell írni. Ha egy cellában, akkor csak a cellát. Ebben a leckében megtanulod a feltételt cellából venni, és egy kis „vezérlőpultot” építesz, amely azonnal válaszol bármelyik kérdésre.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 10. osztálytól

A lecke végére

  • a feltételt egy cellából veszed;
  • összehasonlító jelet fűzöl össze egy cellával;
  • dátumot használsz feltételként;
  • interaktív vezérlőpultot építesz bemeneti cellákkal;
  • legördülő listát készítesz a bemeneti cellába.

Amit már tudsz

  • a feltételes összesítő függvényeket (9.1–9.4);
  • a szövegösszefűzést az & jellel (4.1);
  • a bemeneti cellát és a dollárjelet (4.3, 4.5);
  • a dátumot mint számot (3.7).

Feltétel cellából

Ha a feltétel egyenlőség (pontosan ez az érték), egyszerűen a cellára hivatkozol, idézőjel nélkül:
H1 tartalmaKépletJelentés
9.b=DARABTELI(B2:B60;$H$1)a 9. b-sek száma
angol=ÁTLAGHA(C2:C60;$H$1;D2:D60)az angolosok átlaga
>=40 (a cellába írva)=DARABTELI(D2:D60;$H$1)a legalább 40 pontosak; a cellában álló teljes feltétel is működik

💡 Miért dollárjel?

A feltételcella egy rögzített „bemenet” (4.3): ha a képletet másolod (például több eredménycellába), ne vándoroljon el. Ugyanígy a tartományokat is rögzítsd. A kategóriatáblában (9.1) viszont a feltételcella szándékosan relatív, mert soronként másik kategóriára mutat.

Jel és cella összefűzése

Ha a cellában csak a szám áll (H2 = 40), a műveleti jelet a képletben kell elé fűzni az & jellel (4.1):
FeltételKéplet
legalább a határ=DARABTELI(D2:D60;">="&$H$2)
a határ alatt=DARABTELI(D2:D60;"<"&$H$2)
nem egyenlő a H1 osztállyal=DARABTELI(B2:B60;"<>"&$H$1)
a H3 dátum után (H3-ban valódi dátum)=DARABTELI(A2:A60;">"&$H$3)
két határ között (H2 és H4)=DARABHATÖBB(D2:D60;">="&$H$2;D2:D60;"<="&$H$4)

🔮 Jósold meg!

A H2 cellában 40 áll. Mit ad (a) =DARABTELI(D2:D60;">=H2") (b) =DARABTELI(D2:D60;">="&H2)?
Megoldás
(a) 0 (vagy értelmetlen eredmény): az idézőjelek között a „H2” csak két betű, nem cellahivatkozás. Az Excel a „H2” szöveghez hasonlít, és egyik pontszám sem „nagyobb vagy egyenlő” vele számként. (b) Helyes: az Excel előbb összefűzi a ">=" szöveget és a H2 értékét, így a feltétel ">=40" lesz. A szabály: a jel idézőjelben, a cella kívül, közöttük &.

✅ Dátumfeltételnél

A dátumot a legbiztosabb egy cellába írni (valódi dátumként, 3.7), és összefűzéssel hivatkozni rá, mint fent. A képletbe írt dátumszöveg (például ">2026.10.01") a gép területi beállításaitól függően értelmeződik. Egy képletben előállított dátumhoz a DÁTUM függvény jó (11.6): ">="&DÁTUM(2026;10;1).

Vezérlőpult

  1. A munkalap tetején (vagy egy külön lapon, 4.7) készíts bemeneti cellákat felirattal, kiemelve (Bevitel cellastílus, 4.3): Osztály (H1), Ponthatár (H2).
  2. Alatta eredménycellák: Létszám: =DARABTELI($B$2:$B$60;$H$1); Legalább a határ: =DARABHATÖBB($B$2:$B$60;$H$1;$D$2:$D$60;">="&$H$2); Arány: a kettő hányadosa; Átlag: =HAHIBA(ÁTLAGHA($B$2:$B$60;$H$1;$D$2:$D$60);"nincs adat").
  3. Írd át a H1 vagy a H2 értékét: minden eredmény azonnal frissül.
  4. Legördülő lista a H1-be: jelöld ki a cellát → Adatok → Érvényesítés (Adatérvényesítés) → Engedélyezve: Lista → Forrás: 9.a;9.b;9.c (vagy egy tartomány, amelyben az osztálynevek állnak). Így a felhasználó nem gépelhet el egy osztálynevet.

💡 A „mi lenne, ha” elve

A vezérlőpult ugyanazt az elvet követi, mint a 4.7 paraméterlapja és a 4.10 bevásárlólistája: a kérdés (bemenet) és a számolás (képlet) külön van. Az érettségi feladatokban is gyakori, hogy egy megadott cellában változtatható érték alapján kell számolni.

Kihívás: az osztályfőnök vezérlőpultja

🎛️ Egy pult, sok kérdés

A 9.1 osztálystatisztikai listáján (vagy egy több osztályt tartalmazó listán) dolgozz.
  1. Készíts vezérlőpultot három bemeneti cellával: Nem (legördülő lista: lány; fiú), Nyelv (legördülő lista: angol; német), Ponthatár (szám).
  2. Eredmények: a kiválasztott nemű és nyelvű diákok létszáma; közülük hányan érték el a ponthatárt; az arányuk; az átlagpontszámuk (hiány esetén „nincs adat”).
  3. Adj hozzá egy negyedik bemenetet, „Legfeljebb ennyi mulasztás” néven, és vedd be a feltételek közé (SZUMHATÖBB, DARABHATÖBB, ÁTLAGHATÖBB).
  4. Próbálj ki legalább négy beállítást, és írd le az eredményeket egy kis táblázatba.
  5. Bónusz: egy cellában mondatként is írd ki az eredményt, például: „A németes lányok közül 5-en érték el a 40 pontot.” (& jel, 4.1.)

Hibavadászat

🐞 1. Peti szöveges cellája

Peti így írta a feltételt: =DARABTELI(D2:D60;"<H2"), hogy megszámolja a határ alatti dolgozatokat. Az eredmény mindig 0, bármit ír a H2-be.
Mi a megoldás?
Az idézőjelek között a H2 nem hivatkozás, hanem szöveg (2. rész). Helyesen: =DARABTELI(D2:D60;"<"&H2), és ha a képletet másolja, dollárjellel: "<"&$H$2.

🐞 2. Lili elcsúszott vezérlőpultja

Lili egy eredménycellában megírta a képletet: =DARABHATÖBB(B2:B60;H1;D2:D60;">="&H2), majd átmásolta a mellette lévő cellába, hogy egy másik határral is kipróbálja. Az új cella hibás vagy 0 eredményt ad.
Mit tegyen?
Dollárjelek nélkül másoláskor minden hivatkozás eggyel jobbra csúszott: a tartományok a C és az E oszlopra, a bemenetek az I1-re és az I2-re (4.4). Lili rögzítse a tartományokat és a bemeneti cellákat: =DARABHATÖBB($B$2:$B$60;$H$1;$D$2:$D$60;">="&$H$2). Ha szándékosan egy másik határral akar számolni, azt egy másik bemeneti cellára hivatkozva tegye.

Összefoglalás

  • Egyenlőségnél a feltétel egyszerűen egy cella: =DARABTELI(…;$H$1).
  • Összehasonlításnál a jel idézőjelben, a cella kívül: ">="&$H$2.
  • Az idézőjel közötti cellanév csak szöveg, nem hivatkozás.
  • Dátumfeltétel: valódi dátum egy cellában, összefűzéssel; vagy DÁTUM függvény.
  • Vezérlőpult: bemeneti cellák (legördülő listával), rögzített hivatkozások, azonnal frissülő eredmények.

Ellenőrizd magad!

Hogyan számolod meg a H5 értékénél nagyobb számokat a C2:C40 tartományban?
=DARABTELI(C2:C40;">"&H5)
Miért nem működik a ">H5" feltétel?
Mert az idézőjelek között a H5 szöveg, nem cellahivatkozás.
Hol készítesz legördülő listát egy cellába?
Adatok → Érvényesítés → Engedélyezve: Lista.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (képletek és függvények paraméterezése, cellahivatkozások, adatbevitel ellenőrzése), modellezés és szimuláció („mi lenne, ha” vizsgálat); kapcsolódás a programozáshoz (paraméteres eljárások, bemenet és feldolgozás szétválasztása).

Módszertan

A lecke a feltételt paraméterré teszi, így a diák a „varázsszám nélkül” elvét (4.3) a feltételes összesítésre is átviszi. A jóslás a leggyakoribb szintaktikai hibát (cellanév idézőjelben) teszi tudatossá. A vezérlőpult egy kicsi, de valódi interaktív alkalmazás, amely a bemenet és a feldolgozás szétválasztását gyakoroltatja; a legördülő lista az adatbeviteli hibák megelőzésére is példa. Lili esete a hivatkozások rögzítésének fontosságát ismétli.

Időterv

Feltétel cellából: 6 perc. Jel és cella összefűzése és jóslás: 10 perc. Vezérlőpult: 10 perc. Kihívás: 15 perc. Hibavadászat: 4 perc.

Következő lecke: 9.6 Helyettesítő karakterek a feltételekben

A * és a ? a feltételekben: minden „K”-val kezdődő név, minden „robot” szót tartalmazó szakkör egy függvénnyel.

Többfeltételes darabszám, összeg és átlag: DARABHATÖBB, SZUMHATÖBB, ÁTLAGHATÖBB

Excel táblázatkezelés kezdőknek › 9.4 lecke

Excel tanfolyam · 9.4 lecke

Többfeltételes darabszám, összeg és átlag

Hány lány tanul angolt? Mennyi az angolos fiúk átlaga? Hány diák ért el 40 és 50 pont között? Ha a kérdésben két (vagy több) feltétel szerepel egyszerre, a TÖBB végződésű függvények segítenek: DARABHATÖBB, SZUMHATÖBB és ÁTLAGHATÖBB. Ezek a táblázatkezelési érettségi feladatok kedvenc eszközei, de van egy apró, gyakran elrontott különbség a SZUMHA-hoz képest.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 10. osztálytól

A lecke végére

  • két vagy több feltétellel számolsz, számlálsz és átlagolsz;
  • helyes sorrendben adod meg az argumentumokat;
  • ugyanazon a mezőn intervallumot adsz meg;
  • VAGY kapcsolatot több függvény összegével oldasz meg;
  • összetett szöveges kérdéseket képletté alakítasz.

Amit már tudsz

  • a DARABTELI, SZUMHA, ÁTLAGHA függvényt (9.1–9.3);
  • az ÉS és a VAGY különbségét (8.3, 7.4);
  • az intervallumot és a határokat (8.5).

A három függvény

FüggvénySzerkezetPélda (B: nem, C: nyelv, D: pontszám, E: mulasztás)
DARABHATÖBB(tartomány1; feltétel1; tartomány2; feltétel2; …)=DARABHATÖBB(B2:B26;"lány";C2:C26;"angol"): angolos lányok száma
SZUMHATÖBB(összeg_tartomány; tartomány1; feltétel1; …)=SZUMHATÖBB(E2:E26;B2:B26;"fiú";C2:C26;"német"): a németes fiúk összes mulasztása
ÁTLAGHATÖBB(átlag_tartomány; tartomány1; feltétel1; …)=ÁTLAGHATÖBB(D2:D26;B2:B26;"fiú";C2:C26;"angol"): az angolos fiúk átlaga
  • A feltételpárok (tartomány; feltétel) tetszőleges számban követhetik egymást.
  • A feltételek mindig ÉS kapcsolatban vannak: a sornak minden feltételnek egyszerre meg kell felelnie.
  • Minden tartománynak ugyanolyan méretűnek kell lennie, és ugyanabban a sorban kell kezdődnie (9.2), különben #ÉRTÉK! hibát kapsz.
  • A feltételek ugyanúgy írhatók, mint a DARABTELI-nél: "lány", ">=40", "<>" (9.1).

💡 Egy feltétellel is működik

A DARABHATÖBB, a SZUMHATÖBB és az ÁTLAGHATÖBB egyetlen feltétellel is használható, így sokan csak ezeket használják. A régebbi DARABTELI, SZUMHA, ÁTLAGHA ismerete mégis fontos, mert a feladatokban és mások munkáiban gyakran találkozol velük.

A sorrend csapdája

FüggvényHol van az összegtartomány?
SZUMHA(C2:C26;"angol";D2:D26)a végén (harmadik)
SZUMHATÖBB(D2:D26;C2:C26;"angol")az elején (első)

🔮 Jósold meg!

Peti a SZUMHA megszokott sorrendjében írta: =SZUMHATÖBB(C2:C26;"angol";D2:D26). Mi lesz az eredmény?
Megoldás
Az Excel hibát jelez (vagy kijavítást ajánl), mert a SZUMHATÖBB az első argumentumot összegtartománynak, a következőket pedig (tartomány; feltétel) pároknak veszi: az „angol” itt tartomány helyére került, a D2:D26 pedig feltétel nélkül maradt. Ha mégis elfogadná egy hasonló szerkezetet, a szövegeket próbálná összeadni, és 0 lenne az eredmény. A szabály: a TÖBB függvényeknél előbb azt add meg, amit összegzel vagy átlagolsz, utána a feltételpárokat.

Intervallum és VAGY

  • Intervallum ugyanazon a mezőn: ugyanazt a tartományt kétszer adod meg két feltétellel: =DARABHATÖBB(D2:D26;">=40";D2:D26;"<=50"): a 40 és 50 pont közötti dolgozatok (8.5).
  • VAGY kapcsolat: a TÖBB függvények nem tudnak VAGY-ot. Ha a két feltétel nem fedi át egymást (egy diák nem lehet egyszerre 9. a-s és 9. b-s), egyszerűen add össze a két eredményt: =DARABTELI(B2:B60;"9.a")+DARABTELI(B2:B60;"9.b").
  • Átfedő VAGY („lány VAGY angolos”): az összegből vond le a közöset, különben kétszer számolsz: =DARABTELI(B2:B26;"lány")+DARABTELI(C2:C26;"angol")-DARABHATÖBB(B2:B26;"lány";C2:C26;"angol") (a halmazok uniója, 7.4).

✅ Átfogalmazás feltételpárokra

Egy összetett kérdést bonts feltételpárokra: „Mennyi azoknak az angolos lányoknak az átlaga, akik legfeljebb 10 órát mulasztottak?” → átlagtartomány: D; párok: (B; "lány"), (C; "angol"), (E; "<=10"). Utána a képlet szinte magától megíródik.

Kihívás: összetett kérdések az osztályról

🧩 Feltételpárokkal

A 9.1 osztálystatisztikai listáján (Név, Nem, Nyelv, Dolgozat, Mulasztás) válaszolj egy-egy képlettel. Minden kérdésnél előbb írd fel a feltételpárokat (3. rész).
  1. Hány lány tanul angolt? Hány fiú németet?
  2. Mennyi az angolos fiúk és az angolos lányok átlaga a dolgozaton? (Egy tizedesre.)
  3. Hányan értek el 40 és 60 pont között (a határokat is beleértve)?
  4. Mennyi a németesek összes mulasztása? És a németes lányoké?
  5. Mennyi azoknak az angolos lányoknak az átlaga, akik legfeljebb 10 órát mulasztottak?
  6. Hányan tanulnak angolt vagy mulasztottak 20 óránál többet? (Átfedő VAGY!)
  7. Készíts egy kétdimenziós táblát: sorokban a nem (lány, fiú), oszlopokban a nyelv (angol, német), a cellákban a létszám. Egyetlen képlet töltse ki vegyes hivatkozással (4.6): =DARABHATÖBB($B$2:$B$26;$G2;$C$2:$C$26;H$1).

Hibavadászat

🐞 1. Peti makacs képlete

Peti a németesek mulasztásának összegét így akarta kiszámolni: =SZUMHATÖBB(C2:C26;"német";E2:E26). Az Excel nem fogadja el a képletet.
Mi a megoldás?
A SZUMHATÖBB-nél az összegtartomány az első argumentum (2. rész): =SZUMHATÖBB(E2:E26;C2:C26;"német"). Egy feltételnél a =SZUMHA(C2:C26;"német";E2:E26) is jó.

🐞 2. Lili üres eredménye

Lili a 9. a-s és a 9. b-s diákokat akarta megszámolni: =DARABHATÖBB(B2:B60;"9.a";B2:B60;"9.b"). Az eredmény 0.
Mit tegyen?
A TÖBB függvények feltételei ÉS kapcsolatban vannak: Lili azokat számolta, akik egyszerre 9. a-sok és 9. b-sek, ilyen nincs (3. rész). A VAGY-hoz két DARABTELI összege kell: =DARABTELI(B2:B60;"9.a")+DARABTELI(B2:B60;"9.b").

Összefoglalás

  • DARABHATÖBB, SZUMHATÖBB, ÁTLAGHATÖBB: több feltétel, mindig ÉS kapcsolatban.
  • SZUMHATÖBB és ÁTLAGHATÖBB: az összeg- és az átlagtartomány az első argumentum.
  • Minden tartomány ugyanakkora, ugyanabban a sorban kezdődik.
  • Intervallum: ugyanaz a tartomány kétszer, két feltétellel.
  • VAGY: függvények összege; átfedés esetén a közös rész levonásával.

Ellenőrizd magad!

Hol áll az összegtartomány a SZUMHATÖBB függvényben?
Az első helyen.
Hogyan számolod meg a 10 és 20 közötti értékeket a C2:C50 tartományban?
=DARABHATÖBB(C2:C50;">=10";C2:C50;"<=20")
Milyen kapcsolatban vannak a TÖBB függvények feltételei?
ÉS kapcsolatban.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (statisztikai függvények, összetett feltételtől függő számítások); kapcsolódás a matematikához (halmazműveletek, unió, metszet) és a programozáshoz (összetett feltételű megszámlálás, összegzés). Az NKP-okostankönyvre épülő emelt szóbeli tétel DARABHATÖBB, SZUMHATÖBB, ÁTLAGHATÖBB elemét közvetlenül lefedi; az érettségi feladatok egyik leggyakoribb függvénycsaládja.

Módszertan

A lecke a TÖBB függvényeket a már ismert egyfeltételes változatokra építi, és tudatosan kiemeli a leggyakoribb hibát (az összegtartomány helye). A „feltételpárokra bontás” stratégia a szöveges kérdések formalizálását segíti. A VAGY kapcsolat és az átfedés kezelése a halmazműveletekhez (7.4) kapcsolódik. A kétdimenziós kereszttábla a vegyes hivatkozás (4.6) újabb alkalmazása, és előkészíti a kimutatásokat (14.6).

Időterv

A három függvény: 10 perc. A sorrend csapdája és jóslás: 6 perc. Intervallum és VAGY: 8 perc. Kihívás: 17 perc. Hibavadászat: 4 perc.

Következő lecke: 9.5 Feltételek cellahivatkozással

A feltétel ne a képletben, hanem egy cellában legyen: összefűzés az & jellel, és rugalmas, „mi lenne, ha” kimutatások.

ÁTLAGHA: feltételes átlag az Excelben

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.