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.