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

Excel tanfolyam · 12.9 mini projekt · emelt szint

Versenyeredmények összetett elemzése

Az iskolai matematikaversenyt két fordulóban rendezték meg a 9. évfolyamon. A szervező tanár teljes körű kiértékelést kér: minősítést, javulást, továbbjutókat, osztály és nem szerinti összesítést, és néhány célzott lekérdezést. Ez a mini projekt az emelt szintű érettségi táblázatkezelési feladataihoz hasonló felépítésű: a 12. fejezet minden eszközét felhasználod benne, és minden eredményt ellenőrizhetsz.

  • ⏱ kb. 45–60 perc
  • 🧩 mini projekt
  • 🎓 emelt szint

A projekt végére

  • elnevezett tartományokkal írsz olvasható képleteket;
  • határtáblából, kereséssel minősítesz;
  • összetett, egymásba ágyazott képletet építesz fel;
  • kereszttáblát töltesz ki egyetlen képlettel;
  • AB függvényekkel összetett feltétel szerint kérdezel le.

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

  • 12.1–12.2 tartománynevek;
  • 12.3–12.6 adatbázisfüggvények;
  • 12.7 összetett képletek; 12.8 kereszttábla; és a 10.3 határtáblás keresés.

Az adatok

Gépeld be a kitalált versenylistát a Verseny munkalap A1 cellájától, és mellé (az M oszloptól) a minősítési határtáblát.

A: NévB: OsztályC: NemD: 1. fordulóE: 2. forduló
Kiss Anna9.alány2220
Nagy Bence9.bfiú1820
Tóth Lili9.blány2520
Szabó Máté9.afiú1415
Varga Zsófi9.blány2426
Kovács Péter9.cfiú2324
Horváth Dóra9.clány1617
Molnár Ádám9.bfiú2120
Balogh Eszter9.alány1925
Farkas Dániel9.cfiú2016
M: Alsó határN: Minősítés
0részvétel
35bronz
42ezüst
48arany

Feladatok

Az érettségi feladatok mintájára dolgozz: pontosan a kért helyre, a kért eszközzel.

  1. Összesen (F oszlop): a két forduló pontszámának összege.
  2. Nevek: nevezd el az A2:A11 tartományt nev, a B2:B11-et osztaly, a C2:C11-et nem, a D2:D11-et f1, az F2:F11-et osszes néven, a teljes listát (A1:F11) verseny néven, a határtáblát (M2:N5) hatarok néven. A további képletekben ezekre hivatkozz (12.1, 12.2).
  3. Minősítés (G oszlop): az összpontszám alapján, a határtáblából, közelítő kereséssel (10.3 vagy 10.7).
  4. Javulás (H oszlop): a 2. és az 1. forduló különbsége. Egy cellában: ki javított a legtöbbet? (INDEX + HOL.VAN vagy AB.MEZŐ.)
  5. Továbbjut (I oszlop): „tovább” jelenjen meg, ha az összpontszám eléri a saját osztályának átlagát, és legalább 40 pont; különben üres. Egyetlen képlettel (12.7).
  6. Helyezés az osztályon belül (J oszlop): DARABHATÖBB + 1 (12.7 kihívása).
  7. Kereszttábla (a P2 cellától): osztály (sorok) × nem (oszlopok) összpontszám, egyetlen, jobbra és lefelé másolt képlettel, sor- és oszlopösszeggel (12.8).
  8. Összetett lekérdezés: feltételtartománnyal és AB függvényekkel: „a 9. b-s lányok vagy a 9. c-s fiúk” összpontszáma (AB.SZUM) és létszáma (AB.DARAB2) (12.6).
  9. Egyetlen rekord: AB.MEZŐ-vel: melyik lány érte el az 1. fordulóban a legtöbb pontot? (12.5, két lépésben.)
  10. Összesítő: hány arany, ezüst, bronz és részvétel minősítés született (DARABTELI)? Hány versenyző jut tovább?

Ellenőrzés

Megoldások: a lista
NévÖssz.MinősítésJavulásTovábbjutHelyezés
Kiss Anna42ezüst−2tovább2.
Nagy Bence38bronz24.
Tóth Lili45ezüst−5tovább2.
Szabó Máté29részvétel13.
Varga Zsófi50arany2tovább1.
Kovács Péter47ezüst1tovább1.
Horváth Dóra33részvétel13.
Molnár Ádám41bronz−13.
Balogh Eszter44ezüst6tovább1.
Farkas Dániel36bronz−42.

Osztályátlagok: 9.a ≈ 38,33; 9.b 43,5; 9.c ≈ 38,67. Molnár Ádám (41) azért nem jut tovább, mert a 9. b átlaga 43,5.

Megoldások: összesítések és lekérdezések
  • Összpontszám: 405.
  • Legnagyobb javulás: Balogh Eszter (+6).
  • Minősítések: arany 1, ezüst 4, bronz 3, részvétel 2 (összesen 10).
  • Továbbjutók: 5 (Kiss Anna, Tóth Lili, Varga Zsófi, Kovács Péter, Balogh Eszter).
  • Kereszttábla: 9.a: lány 86, fiú 29 (115); 9.b: lány 95, fiú 79 (174); 9.c: lány 33, fiú 83 (116); lány összesen 214, fiú 191; végösszeg 405.
  • Összetett lekérdezés (Osztály | Nem → 9.b | lány; 9.c | fiú): AB.SZUM = 178, AB.DARAB2 = 4 (Tóth Lili, Varga Zsófi, Kovács Péter, Farkas Dániel).
  • A lányok legjobbja az 1. fordulóban: AB.MAX → 25, majd AB.MEZŐ (Nem | 1. forduló → lány | 25) → Tóth Lili.

💡 Belső ellenőrzések

  • A kereszttábla végösszege = SZUM(osszes) = 405.
  • A minősítések darabszámának összege = a versenyzők száma (10).
  • Minden osztályban van pontosan egy 1. helyezett (ha nincs holtverseny).

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

✅ Önellenőrző lista

  • A neveket pontosan a megadott alakban hoztam létre, és a képletekben használom őket.
  • A minősítés közelítő kereséssel, a határtáblából jön (nem egymásba ágyazott HA-val).
  • A továbbjutás egyetlen képlet, és Molnár Ádámnál üres.
  • A kereszttábla egyetlen, két irányba másolt képlet, a végösszeg 405.
  • A feltételtartomány két sorból áll (VAGY), üres sor nélkül.
  • Az AB.MEZŐ egyetlen rekordot talál (nem ad #SZÁM! hibát).
  • Minden eredmény egyezik a megoldásokkal.
Szempont⭐ Elindultam⭐⭐ Sikerült⭐⭐⭐ Profi vagyok
KépletekAz alapszámítások megvannak.Minősítés, továbbjutás, helyezés helyes.Egyetlen képletek, nevekkel, ellenőrizve.
AdatbázisfüggvényekEgyszerű feltétel.ÉS–VAGY feltétel helyesen.AB.MEZŐ két lépésben, belső ellenőrzéssel.
KereszttáblaKézzel kitöltve.Egy képlettel, néhány hibával.Hibátlan vegyes hivatkozás, sor- és oszlopösszeg.

Hibavadászat: Peti versenyértékelése

🐞 Peti beszámolója

„A határtáblát név nélkül, M2:N5-tel írtam az FKERES-be, és lefelé másoltam. A továbbjutáshoz az osztályátlagot F9-cel ellenőriztem, aztán Entert nyomtam. A kereszttáblában $P$3-at írtam a sorfejlécre. A feltételtartományba a 9.b | lány és a 9.c | fiú közé egy üres sort hagytam, hogy szebb legyen. Az AB.MEZŐ-vel a 9. b legjobbját kerestem, de csak az Osztály mezőre adtam feltételt. A verseny nevet a fejléc nélkül hoztam létre.”

Hány hibát találtál? Mit tanácsolnál Petinek?
HibaKövetkezményHelyesenLecke
rögzítetlen határtábla, név helyettlefelé elcsúszik, #HIÁNYZIKhatarok név (vagy $M$2:$N$5)10.2, 12.2
F9 + Enteraz átlag számmá „fagyott”F9 után Esc12.7
$P$3 a sorfejlécreminden sor ugyanaz$P312.8
üres sor a feltételtartománybanminden rekord megfelelüres sor nélkül12.3, 12.6
AB.MEZŐ csak osztályfeltétellel#SZÁM! (több rekord)a maximummal kiegészített feltétel12.5
adatbázis fejléc nélkülaz AB függvények nem találják a mezőketA1:F11, fejléccel12.3

Hat hibát találhattál. Ha mindet megtaláltad, készen állsz az emelt szintű táblázatkezelési feladatokra!

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

  • Tartományokat nevezel el, és a nevekkel olvasható, biztos képleteket írsz.
  • Adatbázisfüggvényekkel (AB.SZUM, AB.ÁTLAG, AB.DARAB, AB.DARAB2, AB.MIN, AB.MAX, AB.MEZŐ) feltételtartomány szerint összesítesz.
  • Összetett ÉS–VAGY feltételeket rendezel el a feltételtartományban.
  • Összetett képleteket építesz fel lépésenként, és ellenőrzöd őket.
  • Kereszttáblát töltesz ki egyetlen, vegyes hivatkozású képlettel.
👩‍🏫 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, emelt szinten. A mini projekt a 2024-től érvényes emelt szintű érettségi táblázatkezelési feladatának jellemző elemeit (elnevezett tartomány, adatbázisfüggvény, összetett képlet, kereszttábla, keresés segédtáblából) egyesíti; az NKP-okostankönyvre épülő emelt szóbeli tételek táblázatkezelési elemeit is lefedi.

Módszertan

A feladatsor az érettségi feladatok utasításainak stílusát követi (pontos hely, pontos eszköz). A kis adatsor lehetővé teszi a fejben vagy szűréssel történő ellenőrzést, a megadott megoldások és a belső ellenőrzések pedig az önálló hibakeresést. Peti beszámolója a fejezet hat tipikus hibáját ismétli. Javasolt 60 perces vizsgagyakorlatként is kiadni, a megoldások elrejtésével.

Időterv (45–60 perc)

Az adatok és a nevek: 8 perc. Feladatok: 32–45 perc. Ellenőrzés: 5 perc. Hibavadászat: 5 perc.

Következő lecke: 12.10 Feltételes összesítés vagy adatbázisfüggvény

A fejezet zárása: mikor melyik megoldást válaszd, és hogyan indokold a döntésedet.