Excel táblázatkezelés kezdőknek › 12.5 lecke

Excel tanfolyam · 12.5 lecke · emelt szint

AB.DARAB, AB.DARAB2, AB.MIN, AB.MAX és AB.MEZŐ

Hányan vannak a 9. b-ben? Mennyi a legjobb és a leggyengébb eredményük? És ki az, aki a legjobban teljesített? Az első három kérdés egy-egy számot vár, az utolsó egy nevet. Ebben a leckében megismered a többi adatbázisfüggvényt: a darabszámot, a minimumot és a maximumot adókat, és az AB.MEZŐ-t, amely egyetlen rekord egy adatát adja vissza. Ugyanazon a 8 soros adatsoron dolgozunk, mint a 12.3–12.4-ben.

  • ⏱ kb. 45 perc
  • 🎓 emelt szint
  • 🎒 11. osztálytól

A lecke végére

  • megkülönbözteted az AB.DARAB-ot és az AB.DARAB2-t;
  • AB.MIN-nel és AB.MAX-szal szélsőértéket keresel feltétellel;
  • AB.MEZŐ-vel egyetlen rekord adatát kérdezed le;
  • értelmezed az AB.MEZŐ hibaüzeneteit;
  • két lépésben megkeresed a csoport legjobbjának nevét.

Amit már tudsz

  • az AB függvények szerkezetét (12.3);
  • az összetett feltételtartományokat (12.4);
  • a DARAB, DARAB2, MIN, MAX függvényt (5.4, 5.3);
  • a „legjobb neve” INDEX + HOL.VAN-nal (10.7).

💡 A kiinduló adatok

A 12.3 listája (adatok, A1:D9): Kiss Anna 9.a lány 42; Nagy Bence 9.b fiú 38; Tóth Lili 9.b lány 45; Szabó Máté 9.a fiú 29; Varga Zsófi 9.b lány 50; Kovács Péter 9.c fiú 47; Horváth Dóra 9.c lány 33; Molnár Ádám 9.b fiú 41. Feltételtartomány az F1:F2-ben: Osztály | 9.b.

AB.DARAB és AB.DARAB2

FüggvényMit számol meg?Rokona
AB.DARAB(adatbázis;mező;kritérium)a feltételnek megfelelő rekordok közül azokat, amelyekben a mező számDARAB (5.4)
AB.DARAB2(adatbázis;mező;kritérium)azokat, amelyekben a mező nem üres (szám vagy szöveg)DARAB2 (5.4)

🔮 Jósold meg!

Mit ad (a) =AB.DARAB(adatok;"Pontszám";F1:F2) (b) =AB.DARAB(adatok;"Név";F1:F2) (c) =AB.DARAB2(adatok;"Név";F1:F2)?

Megoldás

(a) 4: a négy 9. b-s rekord pontszáma szám. (b) 0: a Név mező szöveg, az AB.DARAB csak számokat számol. (c) 4: az AB.DARAB2 a nem üres cellákat számolja. A „hány rekord felel meg a feltételnek?” kérdésre a legbiztosabb az AB.DARAB2 egy mindig kitöltött mezővel (például a Névvel); az AB.DARAB akkor jó, ha egy szám típusú mezőt választasz, és azt is tudni akarod, hány értékes adat van.

AB.MIN és AB.MAX

  • =AB.MIN(adatok;"Pontszám";F1:F2) → 38 (a 9. b leggyengébb eredménye: Nagy Bence)
  • =AB.MAX(adatok;"Pontszám";F1:F2) → 50 (a legjobb: Varga Zsófi)
  • A feltételtartomány bármilyen lehet, amit a 12.4-ben tanultál: például a lányok legjobbja (Nem | lány) → 50, a fiúk leggyengébbje (Nem | fiú) → 29.

AB.MEZŐ

AB.MEZŐ(adatbázis;mező;kritérium): ha pontosan egy rekord felel meg a feltételnek, annak a mezőjét adja vissza. Így nemcsak számot, hanem szöveget (nevet) is kérdezhetsz.

Hány rekord felel meg?Eredmény
pontosan 1a mező értéke (például „Varga Zsófi”)
egy sem#ÉRTÉK!
több mint 1#SZÁM!

Példa: a 9. c-s lányok neve. Feltételtartomány:

OsztályNem
9.clány
→ =AB.MEZŐ(adatok;"Név";H1:I2) → Horváth Dóra (egyetlen ilyen rekord).

A legjobb neve

  1. Az AB.MAX megadja a 9. b legjobb pontszámát egy segédcellában (például K2): =AB.MAX(adatok;"Pontszám";F1:F2) → 50.
  2. Készíts egy új feltételtartományt két mezővel: Osztály | Pontszám, alattuk: 9.b és =K2 (képlettel, hogy kövesse a maximumot).
  3. Az AB.MEZŐ ezzel a feltétellel a nevet adja: =AB.MEZŐ(adatok;"Név";M1:N2) → Varga Zsófi.
  4. Ha két 9. b-s diák is 50 pontot ért volna el, az AB.MEZŐ #SZÁM! hibát adna: holtverseny. Ez nem hiba a képletben, hanem egy valódi információ.

🎓 Két út ugyanahhoz

Ugyanezt a 10.7 INDEX + HOL.VAN párosa is megadja, de ott egy feltételhez (osztály) már bonyolultabb képlet kell. Az AB.MEZŐ előnye, hogy a feltétel a feltételtartományban van, és a holtversenyt is jelzi. Érettségin mindkettő elfogadható, ha helyes eredményt ad.

Kihívás: osztályonkénti összesítő

📋 Egy feltételcella, sok eredmény

  1. Készíts egy osztályválasztó feltételtartományt (Osztály | legördülő lista: 9.a; 9.b; 9.c, 9.5).
  2. Alá egy kis összesítőt: létszám (AB.DARAB2 a Név mezőn), átlag, legjobb és leggyengébb pontszám, a legjobb neve (a 4. rész két lépésével).
  3. Válts végig a három osztályon, és írd le az eredményeket.
  4. Készíts egy második összesítőt nemenként: a lányok legjobbjának neve, a fiúk leggyengébbjének neve.
  5. Írd a feltételbe a 9.d osztályt: mit ad az AB.MEZŐ, és mit az AB.DARAB2? Kezeld a hibát HAHIBA-val „nincs ilyen osztály” üzenettel (8.6).
Megoldások
  • 9. a: létszám 2, átlag 35,5, legjobb 42 (Kiss Anna), leggyengébb 29.
  • 9. b: létszám 4, átlag 43,5, legjobb 50 (Varga Zsófi), leggyengébb 38.
  • 9. c: létszám 2, átlag 40, legjobb 47 (Kovács Péter), leggyengébb 33.
  • A lányok legjobbja: Varga Zsófi (50); a fiúk leggyengébbje: Szabó Máté (29).
  • 9. d: az AB.MEZŐ #ÉRTÉK! (nincs egyező rekord), az AB.DARAB2 0.

Hibavadászat

🐞 1. Peti üres osztálya

Peti a 9. b létszámát így számolta: =AB.DARAB(adatok;"Név";F1:F2). Az eredmény 0.

Mi a megoldás?

Az AB.DARAB csak a számot tartalmazó mezőket számolja, a Név pedig szöveg (1. rész). Peti használja az AB.DARAB2-t a Név mezőn, vagy az AB.DARAB-ot a Pontszám mezőn.

🐞 2. Lili #SZÁM! hibája

Lili a 9. b-s diák nevét akarta lekérdezni: =AB.MEZŐ(adatok;"Név";F1:F2) (Osztály | 9.b). Az eredmény #SZÁM!.

Mit tegyen?

A 9. b-ben négy diák van, az AB.MEZŐ viszont csak egyetlen megfelelő rekordnál ad eredményt (3. rész). Lili szűkítse a feltételt úgy, hogy egy rekordra illeszkedjen (például a legjobb pontszámmal is, 4. rész). Ha az összes nevet akarja látni, az Irányított szűrés (7.4) a megfelelő eszköz.

Összefoglalás

  • AB.DARAB: a megfelelő rekordok közül a számot tartalmazók; AB.DARAB2: a nem üresek.
  • AB.MIN, AB.MAX: a legkisebb és a legnagyobb érték a feltételnek megfelelő rekordokból.
  • AB.MEZŐ: pontosan egy megfelelő rekord mezője; egy sem: #ÉRTÉK!; több: #SZÁM!
  • A csoport legjobbjának neve: AB.MAX segédcellában, majd AB.MEZŐ a maximummal kiegészített feltétellel.
  • Egy feltételtartomány (például legördülő listával) egy egész összesítőt vezérelhet.

Ellenőrizd magad!

Mit ad az AB.DARAB egy szöveges mezőn?

0, mert csak számokat számol.

Mit jelent az AB.MEZŐ #SZÁM! hibája?

Hogy több rekord is megfelel a feltételnek.

Mennyi a 9. c leggyengébb eredménye?

33 (Horváth Dóra).

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Adatbázis-kezelés táblázatkezelővel (függvényhasználat adatok összesítésére és lekérdezésére). Az emelt szintű érettségi adatbázisfüggvényes követelményének teljes lefedése; kapcsolódás az SQL-hez (COUNT, MIN, MAX; egyetlen rekord lekérdezése).

Módszertan

A lecke a már ismert DARAB, DARAB2, MIN, MAX függvények „feltételes” párjait mutatja be ugyanazon a kis adatsoron, így az eredmények fejben ellenőrizhetők. A jóslás a DARAB és a DARAB2 különbségét mezőválasztással teszi tudatossá. Az AB.MEZŐ hibaüzenetei (#ÉRTÉK!, #SZÁM!) információt hordoznak (nincs találat, holtverseny), ami a hibák értelmezésére nevel. A „legjobb neve” két lépésben a 10.7 megoldásával is összevethető.

Időterv

AB.DARAB és jóslás: 8 perc. AB.MIN és AB.MAX: 5 perc. AB.MEZŐ: 7 perc. A legjobb neve: 8 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 12.6 ÉS és VAGY feltételek az adatbázisfüggvényekben

Több sor, több oszlop: összetett logikai feltételek a feltételtartományban.