Excel táblázatkezelés kezdőknek › 12.10 lecke
Excel tanfolyam · 12.10 lecke · emelt szint
Feltételes összesítés vagy adatbázisfüggvény?
Ugyanarra a kérdésre („mennyi a 9. b-s lányok összpontszáma?”) legalább három jó megoldás van: SZUMHATÖBB, AB.SZUM, vagy szűrés és RÉSZÖSSZEG. Az emelt szintű feladatokban és a szóbelin gyakran nemcsak a helyes eredmény számít, hanem az is, hogy meg tudod-e indokolni, miért éppen azt a megoldást választottad. A 12. fejezet záró leckéje ehhez ad szempontokat, egy indoklás-sablont és helyzetfeladatokat.
- ⏱ kb. 45 perc
- 🎓 emelt szint
- 🎒 11. osztálytól
A lecke végére
- ugyanazt a kérdést háromféle módon oldod meg;
- ismered a megoldásválasztás szempontjait;
- VAGY feltételnél a helyes utat választod;
- megindokolod a választásodat szóban és írásban;
- felismered a rosszul választott eszközt.
Amit már tudsz
Három megoldás ugyanarra
A 12.3 listáján (adatok, nevek: osztaly, nem, pontszam): „Mennyi a 9. b-s lányok összpontszáma?”
| Megoldás | Hogyan? | Eredmény |
|---|---|---|
| SZUMHATÖBB (9.4) | =SZUMHATÖBB(pontszam;osztaly;"9.b";nem;"lány") | 95 |
| AB.SZUM (12.4) | feltételtartomány: Osztály | Nem → 9.b | lány; =AB.SZUM(adatok;"Pontszám";F1:G2) | 95 |
| Szűrés + RÉSZÖSSZEG (7.3, 9.7) | AutoSzűrő: Osztály = 9.b, Nem = lány; a lista alatt =RÉSZÖSSZEG(109;D2:D9) | 95 (a szűrés közben) |
Mindhárom helyes. A különbség abban van, mennyire könnyű módosítani, másolni, ellenőrizni, és mit kér a feladat.
Szempontok
| Szempont | SZUMHA család | AB függvények | Szűrés + RÉSZÖSSZEG |
|---|---|---|---|
| Mit kér a feladat? | „függvénnyel határozza meg” | „adatbázisfüggvénnyel”, „feltételtartománnyal” | „szűrje”, „jelenítse meg” |
| Hol a feltétel? | a képletben vagy cellából (9.5) | feltételtartományban | a szűrőben |
| Sok kategória, másolható tábla (kategóriatábla, kereszttábla) | ✅ egy képlet, másolva (9.2, 12.8) | ❌ minden cellához külön feltételtartomány kellene | ❌ |
| VAGY különböző mezőkön | ⚠️ körülményes, kétszeres számolás veszélye | ✅ külön sorok | ✅ Irányított szűréssel |
| Több eredmény ugyanarra a feltételre | minden képletben újra le kell írni | ✅ egy feltételtartomány, több AB függvény | RÉSZÖSSZEG több kóddal |
| Állandóan látható eredmény | ✅ | ✅ | ❌ csak szűrés közben |
| Az eredményt a szűrt sorok listája is kell | ❌ | ❌ | ✅ |
✅ Egy mondatban
Másolható táblához a SZUMHA család, összetett (VAGY) feltételhez és feltételtartományos utasításhoz az AB függvények, a megfelelő sorok megnézéséhez a szűrés.
A VAGY két útja
🔮 Jósold meg!
„Mennyi a 9. a-sok VAGY a lányok összpontszáma?” Melyik képlet ad helyes eredményt? (a) =SZUMHA(osztaly;"9.a";pontszam)+SZUMHA(nem;"lány";pontszam) (b) =SZUMHA(osztaly;"9.a";pontszam)+SZUMHA(nem;"lány";pontszam)-SZUMHATÖBB(pontszam;osztaly;"9.a";nem;"lány") (c) AB.SZUM az „átlós” feltételtartománnyal (12.6)
Megoldás
(a) 241 – hibás: Kiss Anna 9. a-s és lány is, ezért kétszer kerül bele (71 + 170). (b) 199 – helyes: a matematikából ismert logikai szita (A vagy B = A + B − A és B): 71 + 170 − 42. (c) 199 – helyes, és nem kell külön gondolni a közös részre. Két feltételnél a (b) is jó, háromnál már bonyolult; az AB függvény bármennyi VAGY sorra ugyanolyan egyszerű.
Az indoklás
🎓 Indoklás-sablon (szóbeli és írásbeli magyarázat)
„A(z) [függvény / eszköz] megoldást választottam, mert
1) a feladat [mit kért: egy számot / egy táblát / feltételtartományt / a sorok listáját];
2) a feltétel [egyszerű ÉS / VAGY különböző mezőkön / változtatható];
3) az eredményt [másolni kell / több mutatóhoz is fel kell használni / látni kell a sorokat].
Ellenőrzésként [a másik módszerrel / sor- és oszlopösszeggel / szűréssel] is kiszámoltam, és ugyanaz jött ki.”
💡 Minta
„Az osztályonkénti átlagot ÁTLAGHA-val számoltam, mert egy háromsoros kategóriatáblát kellett kitölteni, és így egyetlen képletet másolhattam le, amelyben a feltétel a sor elején álló osztálynévre hivatkozik. Adatbázisfüggvénnyel minden osztályhoz külön feltételtartomány kellett volna. Ellenőrzésként a három átlag létszámmal súlyozott átlaga megegyezik a teljes átlaggal.”
Helyzetfeladatok
🧭 Válassz, és indokold!
Minden helyzetnél döntsd el, melyik eszközt választod, és írj egy két-három mondatos indoklást a sablon szerint. Utána oldd meg a 12.3 listáján.
- Táblázat a három osztály összpontszámáról.
- „Hány versenyző 9. b-s VAGY fiú?”
- A feladat szövege: „Hozzon létre feltételtartományt az M1 cellától kezdődően, és ennek felhasználásával határozza meg…”
- Kereszttábla: osztály × nem szerinti átlag.
- „Másolja ki a 40 pontnál többet elért lányok adatait a Lista munkalapra!”
- Egy legördülő listában kiválasztható osztályhoz egyszerre jelenjen meg a létszám, az átlag, a legjobb és a leggyengébb eredmény.
Mintaválaszok
- SZUMHA kategóriatábla: egy képlet, lefelé másolva (9.2). Eredmény: 71, 174, 80.
- AB.DARAB2 „átlós” feltételtartománnyal: VAGY különböző mezőkön, kétszeres számolás nélkül (12.6). Eredmény: 6.
- AB függvény: a feladat kifejezetten feltételtartományt kér, így a SZUMHA nem felelne meg az utasításnak.
- ÁTLAGHATÖBB vegyes hivatkozással (12.8): egy képlet tölti ki a táblát; AB függvénnyel hat feltételtartomány kellene.
- Irányított szűrés, másolás más helyre (7.4): a feladat a sorokat kéri, nem egy számot. (Eredmény: Kiss Anna, Tóth Lili, Varga Zsófi.)
- Mindkettő jó, de az AB függvények előnye, hogy egyetlen feltételtartomány (benne a legördülő listával) négy mutatót is vezérel (12.5). A SZUMHA családdal is megoldható, ha minden képletben ugyanarra a választócellára hivatkozol (9.5). A lényeg az indoklás.
Hibavadászat
🐞 1. Peti hat feltételtartománya
Peti az osztály × nem kereszttáblát AB.SZUM-mal töltötte ki. Hat külön feltételtartományt készített, és minden cellába más képletet írt. Egy osztálynév átírása után három cellát elfelejtett frissíteni.
Mi a jobb megoldás?
Kereszttáblához a SZUMHATÖBB és a vegyes hivatkozás való (12.8): egyetlen képlet, amely a fejlécekre hivatkozik, így egy fejléc átírása után minden cella magától frissül. Az AB függvény akkor jobb, ha egy összetett feltételhez több eredmény kell, nem ha sok egyszerű feltételhez egy-egy (2. rész).
🐞 2. Lili túl nagy összege
Lili a „9. b-s VAGY fiú” versenyzők összpontszámát így számolta: =SZUMHA(osztaly;"9.b";pontszam)+SZUMHA(nem;"fiú";pontszam). Az eredmény 329, pedig a teljes lista is csak 325 pont.
Mit tegyen?
A 9. b-s fiúk (Nagy Bence és Molnár Ádám, 79 pont) mindkét SZUMHA-ba bekerültek (3. rész). Javítás: a logikai szitával levonni a közös részt (…-SZUMHATÖBB(pontszam;osztaly;"9.b";nem;"fiú")) → 250, vagy AB.SZUM-ot használni átlós feltételtartománnyal → 250. Jó ellenőrzés: egy részösszeg sosem lehet nagyobb a teljes összegnél.
Összefoglalás
- Ugyanarra a kérdésre több jó megoldás van; a választást a feladat, a feltétel és a felhasználás dönti el.
- Másolható kategória- és kereszttábla: SZUMHA család, vegyes hivatkozással.
- VAGY különböző mezőkön, feltételtartományos utasítás, több eredmény egy feltételhez: AB függvények.
- A sorok listája kell: szűrés (Irányított szűrés, RÉSZÖSSZEG).
- Indoklás: mit kért a feladat, milyen a feltétel, mire kell az eredmény, hogyan ellenőrizted.
Ellenőrizd magad!
Melyik eszköz a jobb egy osztály × nem kereszttáblához?
SZUMHATÖBB (vagy DARABHATÖBB, ÁTLAGHATÖBB) vegyes hivatkozással.
Mit jelent a logikai szita két feltételnél?
A vagy B = A + B − (A és B).
Mikor nem elég egy szám, hanem szűrés kell?
Ha a feltételnek megfelelő sorokat (rekordokat) is látni vagy másolni kell.
👩🏫 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; a problémamegoldás eszközeinek tudatos megválasztása és indoklása; kapcsolódás a matematikához (logikai szita, halmazműveletek). A szóbeli vizsga és az írásbeli feladatok „indokolja” típusú elvárásaihoz és a megoldási stratégiák megvitatásához ad keretet.
Módszertan
A fejezetzáró lecke a „melyik függvény?” kérdést szempontrendszerré és indoklási sablonná alakítja, így a metakognitív tudatosságot fejleszti. Az ugyanarra a kérdésre adott három megoldás és a logikai szita a 9. és a 12. fejezetet köti össze. A helyzetfeladatok között szándékosan van olyan, amelyre több jó válasz adható: itt az indoklás minősége a cél. A két hibavadászat a rossz eszközválasztás és a kétszeres számolás következményeit mutatja meg.
Időterv
Három megoldás: 7 perc. Szempontok: 7 perc. A VAGY két útja és jóslás: 7 perc. Az indoklás: 5 perc. Helyzetfeladatok: 15 perc. Hibavadászat: 4 perc.
Következő fejezet: 13.1 Nyomtatási terület és oldaltörések
Kezdődik a 13. fejezet: nyomtatás és munkafüzet-ellenőrzés, hogy az eredmény pontos és átadható legyen.
