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 három függvény
| Függvény | Szerkezet | Pé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ény | Hol 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).
- Hány lány tanul angolt? Hány fiú németet?
- Mennyi az angolos fiúk és az angolos lányok átlaga a dolgozaton? (Egy tizedesre.)
- Hányan értek el 40 és 60 pont között (a határokat is beleértve)?
- Mennyi a németesek összes mulasztása? És a németes lányoké?
- Mennyi azoknak az angolos lányoknak az átlaga, akik legfeljebb 10 órát mulasztottak?
- Hányan tanulnak angolt vagy mulasztottak 20 óránál többet? (Átfedő VAGY!)
- 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.
