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

  • a SZUMHA családot és a vezérlőpultot (9.4, 9.5);
  • a szűrést és a RÉSZÖSSZEG-et (7.4, 9.7);
  • az adatbázisfüggvényeket és a VAGY feltételt (12.3, 12.6);
  • a kereszttáblát (12.8).

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ásHogyan?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

SzempontSZUMHA családAB függvényekSzű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ánybana 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ételreminden képletben újra le kell írni✅ egy feltételtartomány, több AB függvényRÉ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.

  1. Táblázat a három osztály összpontszámáról.
  2. „Hány versenyző 9. b-s VAGY fiú?”
  3. 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…”
  4. Kereszttábla: osztály × nem szerinti átlag.
  5. „Másolja ki a 40 pontnál többet elért lányok adatait a Lista munkalapra!”
  6. 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
  1. SZUMHA kategóriatábla: egy képlet, lefelé másolva (9.2). Eredmény: 71, 174, 80.
  2. 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.
  3. AB függvény: a feladat kifejezetten feltételtartományt kér, így a SZUMHA nem felelne meg az utasításnak.
  4. Á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.
  5. 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.)
  6. 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.