ÉS és VAGY feltételek az adatbázisfüggvényekben (emelt szint)

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

Excel tanfolyam · 12.6 lecke · emelt szint

ÉS és VAGY feltételek az adatbázisfüggvényekben

„A 9. a-sok vagy a lányok.” „A 9. b-s lányok vagy a 9. c-s fiúk.” A 9.4-ben láttuk, hogy a VAGY kapcsolat a SZUMHA családnál körülményes, és könnyen kétszer számolunk egy rekordot. A feltételtartomány ezt elegánsan megoldja: a sorok és az oszlopok elrendezése adja meg a logikát. Ebben a leckében a feltételtartomány logikai szerkezetét tanulod meg, a 12.3–12.5 ellenőrizhető adatsorán.

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

A lecke végére

  • elmagyarázod a feltételtartomány logikai szerkezetét;
  • VAGY feltételt adsz meg ugyanazon és különböző mezőkön;
  • (ÉS) VAGY (ÉS) típusú összetett feltételt készítesz;
  • két intervallumot VAGY-gyal kapcsolsz össze;
  • tudod, miért nem számol kétszer egy rekordot az AB függvény.

Amit már tudsz

  • a feltételtartomány alapszabályait (7.4);
  • az ÉS, VAGY, NEM logikáját (8.3);
  • a VAGY csapdáját a TÖBB függvényeknél (9.4);
  • az AB függvényeket (12.4, 12.5).

💡 A kiinduló adatok

A 12.3 listája (adatok): 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. Összesen 325 pont.

A szabály

ElrendezésJelentés
feltételek egy sorban (különböző oszlopokban)ÉS: mindegyiknek teljesülnie kell
feltételek külön sorokbanVAGY: elég, ha valamelyik sor teljesül
üres cella egy sorbanarra a mezőre abban a sorban nincs feltétel
teljesen üres sor a kritérium tartományábanminden rekord megfelel (12.3)
A feltételtartomány tehát mindig „ÉS-ek VAGY-a”: (sor1 feltételei ÉS-sel) VAGY (sor2 feltételei ÉS-sel) VAGY … A logikában és a programozásban ezt diszjunktív normálformának nevezik: bármilyen logikai feltétel átírható erre az alakra.

VAGY feltételek

KérdésFeltételtartományAB.SZUM (Pontszám)
9. a vagy 9. c (ugyanaz a mező)
Osztály
9.a
9.c
151 (42 + 29 + 47 + 33)
9. a-s vagy lány (különböző mezők, „átlósan”)
OsztályNem
9.a
lány
199 (Kiss Anna, Szabó Máté, Tóth Lili, Varga Zsófi, Horváth Dóra)

Összetett feltételek

KérdésFeltételtartományAB.SZUM
(9. b és lány) vagy (9. c és fiú)
OsztályNem
9.blány
9.cfiú
142 (Tóth Lili 45, Varga Zsófi 50, Kovács Péter 47)
legalább 45 pont vagy 30 alatt
Pontszám
>=45
<30
171 (45 + 50 + 47 + 29)
a lányok közül 40 és 45 között, vagy bárki 50 felett
NemPontszámPontszám
lány>=40<=45
>50
87 (Kiss Anna 42, Tóth Lili 45; 50 felett senki)

✅ Írd le szavakkal!

Mielőtt összetett feltételtartományt készítesz, írd le a kérdést „(… és …) vagy (… és …)” alakban. Minden zárójel egy sor lesz, minden „és” egy oszlop abban a sorban. Utána csak át kell másolni a táblázatba.

Egy rekord egyszer

🔮 Jósold meg!

Hány versenyző „9. a-s vagy lány”? Mit ad (a) az AB.DARAB2 a 2. rész „átlós” feltételtartományával a Név mezőn (b) a =DARABTELI(osztaly;"9.a")+DARABTELI(nem;"lány")?
Megoldás
(a) 5: az AB függvény minden rekordot egyszer vizsgál meg, és ha bármelyik sor feltétele teljesül, egyszer számolja. (b) 6: Kiss Anna 9. a-s és lány is, ezért a két DARABTELI mindkettőben megszámolja. Ugyanez az összegnél: két SZUMHA összege 71 + 170 = 241, a helyes 199 (Kiss Anna 42 pontja kétszer került bele). A különböző mezőkre vonatkozó VAGY feltételnél az AB függvény a biztos megoldás (9.4).

Kihívás: írd le, rendezd el, számold ki

🧩 Logikai feltételek feltételtartományba

Minden kérdést előbb írj fel „(… és …) vagy (…)” alakban, készíts hozzá feltételtartományt, és számold ki az AB.SZUM-ot és az AB.DARAB2-t (a Név mezőn).
  1. A 9. b-sek vagy a fiúk.
  2. A 9. a-s fiúk vagy a 9. c-s lányok.
  3. Akik 35 pont alatt vagy 48 pont felett teljesítettek.
  4. A 9. a-s vagy 9. c-s lányok. (Tipp: a „lány” mindkét sorba kell!)
  5. Ellenőrizd az 1. kérdés darabszámát DARABTELI-vel is: miért más az eredmény?
Megoldások
  1. Nagy Bence, Tóth Lili, Varga Zsófi, Molnár Ádám, Szabó Máté, Kovács Péter: összeg 250, darab 6.
  2. Szabó Máté, Horváth Dóra: összeg 62, darab 2.
  3. 29, 33, 50: összeg 112, darab 3.
  4. Feltételtartomány: Osztály | Nem → 9.a | lány; 9.c | lány. Kiss Anna, Horváth Dóra: összeg 75, darab 2.
  5. DARABTELI-vel 4 + 4 = 8, mert Nagy Bence és Molnár Ádám 9. b-s és fiú is, így kétszer számolódnak; a helyes 6.

Hibavadászat

🐞 1. Peti egyetlen találata

Peti a „9. a-s vagy lány” kérdéshez ezt a feltételtartományt készítette: Osztály | Nem, alatta egy sorban: 9.a | lány. Az AB.DARAB2 1-et ad.
Mi a megoldás?
Egy sorban a feltételek ÉS kapcsolatban vannak (1. rész): Peti a „9. a-s lányokat” kérdezte, és az csak Kiss Anna. A VAGY-hoz a két feltételt két különböző sorba kell írni, „átlósan” (2. rész).

🐞 2. Lili elfelejtett sora

Lili a feltételtartományt kiegészítette egy új VAGY sorral (9.c a harmadik sorban), de a képlete továbbra is F1:F2-re hivatkozik. Az eredmény nem változott.
Mit tegyen?
A kritérium argumentuma csak az első két sort tartalmazza, így az új sort a függvény nem látja. Lili bővítse F1:F3-ra, vagy nevezze el a feltételtartományt (12.1), és a Névkezelőben bővítse. (Vigyázat: üres sor ne kerüljön bele, 12.3!)

Összefoglalás

  • Egy sorban: ÉS; külön sorokban: VAGY; a feltételtartomány „ÉS-ek VAGY-a”.
  • VAGY ugyanazon a mezőn: egy oszlop, több sor.
  • VAGY különböző mezőkön: „átlós” elrendezés, a többi cella üres.
  • Összetett feltétel: írd le „(… és …) vagy (… és …)” alakban; minden zárójel egy sor.
  • Az AB függvény minden rekordot egyszer számol, a DARABTELI-ek összege nem.

Ellenőrizd magad!

Hány sor kell a „9.a vagy 9.b vagy 9.c” feltételhez?
Három sor a fejléc alatt (egy oszlopban).
Milyen kapcsolatban vannak egy sor feltételei?
ÉS kapcsolatban.
Miért ad két DARABTELI összege többet, mint az AB.DARAB2 egy VAGY feltétellel?
Mert a mindkét feltételnek megfelelő rekordokat kétszer számolja.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Adatbázis-kezelés táblázatkezelővel (összetett feltételek), logikai műveletek; kapcsolódás a matematikához (halmazok uniója és metszete, logikai műveletek), a programozáshoz (összetett logikai kifejezések, diszjunktív normálforma) és az SQL-hez (WHERE … AND … OR …). Az emelt szintű érettségi adatbázisfüggvényes követelményének összetett feltételes része.

Módszertan

A lecke a feltételtartomány elrendezését egyetlen logikai szabályra („ÉS-ek VAGY-a”) vezeti vissza, és a „(… és …) vagy (…)” átírást ajánlja tervezési módszerként. A jóslás a halmazok uniójának kétszeres számolási csapdáját (9.4) teszi tapasztalattá, és megmutatja az AB függvények előnyét. Minden eredmény a kis adatsoron fejben is ellenőrizhető.

Időterv

A szabály: 6 perc. VAGY feltételek: 8 perc. Összetett feltételek: 8 perc. Egy rekord egyszer, jóslás: 6 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 12.7 Összetett függvények lépésenkénti felépítése

Hogyan írj meg egy több függvényből álló képletet hiba nélkül: segédoszlopok, belülről kifelé, képletkiértékelő.

AB.DARAB, AB.DARAB2, AB.MIN, AB.MAX és AB.MEZŐ (emelt szint)

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.

AB.SZUM és AB.ÁTLAG: összeg és átlag feltételtartománnyal (emelt szint)

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

Excel tanfolyam · 12.4 lecke · emelt szint

AB.SZUM és AB.ÁTLAG

A 12.3-ban egy egyszerű feltétellel (Osztály = 9.b) számoltunk. Az adatbázisfüggvények ereje azonban az összetett feltételekben van: több mező egyszerre, intervallum ugyanazon a mezőn, szöveg eleje vagy pontos egyezés, és olyan határ, amely egy bemeneti cellából jön. Ebben a leckében az AB.SZUM és az AB.ÁTLAG függvénnyel gyakorolod a feltételtartomány minden fajtáját, a 12.3 ellenőrizhető adatsorán.

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

A lecke végére

  • több mezőre vonatkozó ÉS feltételt adsz meg;
  • intervallumot adsz meg ugyanazon a mezőn;
  • szöveges feltételt adsz meg „kezdete” és pontos egyezéssel;
  • a feltételtartományba bemeneti cellából veszed a határt;
  • megismered a számított feltételt.

Amit már tudsz

  • az AB függvények szerkezetét és a példalistát (12.3);
  • a feltételtartomány szabályait (7.4);
  • a helyettesítő karaktereket (9.6);
  • a feltétel összefűzését cellával (9.5).

💡 A kiinduló adatok

A 12.3 listája: A1:D9 (Név, Osztály, Nem, Pontszám), elnevezve adatok néven. A pontszámok: Kiss Anna 42, Nagy Bence 38, Tóth Lili 45, Szabó Máté 29, Varga Zsófi 50, Kovács Péter 47, Horváth Dóra 33, Molnár Ádám 41 (összesen 325). A feltételtartományokat a lista mellé, az F oszloptól írd.

Több mező: ÉS

A 9. b-s lányok: két mezőnév egymás mellett, alattuk egy sorban a két feltétel (egy sor = ÉS, 7.4):
OsztályNem
9.blány
  • =AB.SZUM(adatok;"Pontszám";F1:G2) → 95 (Tóth Lili 45 + Varga Zsófi 50)
  • =AB.ÁTLAG(adatok;"Pontszám";F1:G2) → 47,5

Intervallum ugyanazon a mezőn

A 40 és 50 pont közöttiek (a határokat is beleértve): ugyanaz a mezőnév kétszer, egymás mellett, alattuk egy sorban a két határ:
PontszámPontszám
>=40<=50
  • =AB.SZUM(adatok;"Pontszám";I1:J2) → 225 (42 + 45 + 50 + 47 + 41)
  • =AB.ÁTLAG(adatok;"Pontszám";I1:J2) → 45

Szöveges feltételek

🔮 Jósold meg!

A feltételtartomány:
Név
K
Mennyi az AB.SZUM(adatok;"Pontszám";…)?
Megoldás
89: Kiss Anna (42) és Kovács Péter (47). A feltételtartományban a szöveges feltétel „ezzel kezdődik” jelentésű (7.4), ezért a „K” minden K-val kezdődő nevet megtalál. Ha a név pontosan „K” lenne, és csak azt keresnéd, pontos egyezés kellene.
Feltétel a Név alattJelentésAB.SZUM
KK-val kezdődik89
="=Kiss Anna" (a cellában így látszik: =Kiss Anna)pontosan egyezik42
*a„a”-ra végződik (9.6)75 (Kiss Anna 42 + Horváth Dóra 33)
<>9.b az Osztály alattnem 9. b-s151 (325 − 174)

💡 Miért fontos a pontos egyezés?

Ha egy osztály neve „9.b”, egy másiké „9.bny” (nyelvi előkészítő), a 9.b feltétel mindkettőt megtalálja, mert mindkettő „9.b”-vel kezdődik. Ilyenkor a pontos egyezés (="=9.b") a biztos.

Feltétel cellából

  • Határ bemeneti cellából: a H1 cellában a ponthatár (például 45). A feltételtartomány Pontszám alatti cellájába ne >=45-öt írj, hanem egy képletet: =">="&$H$1 (9.5). A cellában „>=45” jelenik meg, és ha a H1 változik, a feltétel is. Eredmény 45-nél: AB.SZUM = 142 (45 + 50 + 47), AB.ÁTLAG ≈ 47,33.
  • Legördülő lista a feltételben: az Osztály alatti feltételcellába adatérvényesítési listát tehetsz (9.5): így a felhasználó egy kattintással választhat osztályt, és az AB függvények azonnal frissülnek.

🎓 Kitekintés: számított feltétel

Néha a feltétel nem egy érték, hanem egy számítás: „az átlagnál többet elérők”. Ilyenkor a feltételtartomány fejléce ne egyezzen egyik mezőnévvel sem (például „Átlag felett”, vagy üres), alatta pedig egy képlet álljon, amely a lista első adatsorára hivatkozik relatívan: =D2>ÁTLAG($D$2:$D$9). Az Excel ezt minden rekordra kiértékeli. Eredmény: AB.SZUM = 225 (az átlag 40,625). Ez haladó technika; ha bizonytalan vagy, használj segédoszlopot (8.2).

Kihívás: feltételtartományok gyakorlása

🧮 Minden kérdéshez egy feltételtartomány

A 12.3 listáján (adatok) készíts minden kérdéshez külön feltételtartományt, és számold ki az AB.SZUM és az AB.ÁTLAG értékét. Utána ellenőrizd a megoldásokkal.
  1. A 9. a-s fiúk.
  2. A lányok közül azok, akik 40 pontnál többet értek el.
  3. A fiúk közül azok, akik 30 és 45 pont között teljesítettek (a határokat is beleértve).
  4. Egy bemeneti cellában (H1) állítsd a ponthatárt 40-re, egy legördülő listában (H2) az osztályt 9.b-re, és a feltételtartomány mindkettőt cellából vegye. Mennyi az eredmény? Változtasd meg a H2-t 9.c-re!
  5. Bónusz: számított feltétellel azok, akik az átlagnál kevesebbet értek el.
Megoldások
  1. Szabó Máté: összeg 29, átlag 29.
  2. Kiss Anna, Tóth Lili, Varga Zsófi: összeg 137, átlag ≈ 45,67.
  3. Nagy Bence, Molnár Ádám: összeg 79, átlag 39,5.
  4. 9. b, legalább 40: Tóth Lili, Varga Zsófi, Molnár Ádám: összeg 136, átlag ≈ 45,33. 9. c, legalább 40: Kovács Péter: 47.
  5. Az átlag (40,625) alattiak: Nagy Bence, Szabó Máté, Horváth Dóra: összeg 100, átlag ≈ 33,33.

Hibavadászat

🐞 1. Peti „mindenki” intervalluma

Peti a 40 és 50 közöttieket így kereste: a Pontszám alatt az első sorban >=40, a második sorban <=50. Az AB.SZUM 325 lett, minden versenyző pontszámának összege.
Mi a megoldás?
A két sor VAGY kapcsolatot jelent (7.4): „legalább 40 VAGY legfeljebb 50”, ami minden számra igaz. Az ÉS-hez a mezőnevet kétszer kell egymás mellé írni, és a két határt egy sorba (2. rész).

🐞 2. Lili nulla eredménye

Lili a feltételtartományba a Pontszám alá ezt gépelte: >=H1. Az AB.SZUM 0 lett.
Mit tegyen?
A „>=H1” szövegként került a cellába: az Excel a pontszámokat a „H1” szöveghez hasonlította (9.5). A határt képlettel kell összefűzni: =">="&$H$1 (4. rész). Ellenőrzés: a cellában „>=40” (a H1 aktuális értékével) jelenjen meg.

Összefoglalás

  • Több mező ÉS: mezőnevek egymás mellett, feltételek egy sorban.
  • Intervallum: ugyanaz a mezőnév kétszer, a két határ egy sorban.
  • Szöveges feltétel: „ezzel kezdődik”; pontos egyezés: ="=szöveg"; helyettesítő karakter: *, ?.
  • Határ cellából: =">="&$H$1 a feltételtartományban.
  • Számított feltétel: nem egyező fejléc, képlet az első adatsorra hivatkozva.

Ellenőrizd magad!

Hogyan adod meg, hogy a pontszám 30 és 40 között legyen?
Pontszám | Pontszám fejléc egymás mellett, alattuk egy sorban: >=30 | <=40.
Mit talál meg a „Sz” feltétel a Név mezőben?
Minden „Sz”-szel kezdődő nevet (például Szabó Máté).
Mit kell a feltételtartományba írni, hogy a határ a H1 cellából jöjjön?
=">="&$H$1 (vagy a megfelelő összehasonlító jellel).
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Adatbázis-kezelés táblázatkezelővel (összetett feltételek, összesítés). Az emelt szintű érettségi adatbázisfüggvényes követelményének és az NKP-tétel „AB függvények használata” elemének részletes gyakorlása; kapcsolódás az SQL WHERE feltételeihez (AND, BETWEEN, LIKE).

Módszertan

A lecke a feltételtartomány minden jellemző formáját (több mező, intervallum, szöveg, cellából vett határ, számított feltétel) egy állandó, kis adatsoron gyakoroltatja, így minden eredmény fejben is ellenőrizhető. A jóslás a szöveges feltétel „kezdete” jelentését mutatja meg, a hibavadászat a két leggyakoribb hibát (intervallum két sorban, cellanév szövegként). A számított feltétel kitekintésként szerepel, a haladóbb tanulóknak.

Időterv

Több mező: 6 perc. Intervallum: 6 perc. Szöveges feltételek és jóslás: 8 perc. Feltétel cellából, kitekintés: 8 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 12.5 AB.DARAB, AB.DARAB2, AB.MIN és AB.MAX

Darabszám, legkisebb és legnagyobb érték feltétellel, és az AB.MEZŐ, amely egyetlen rekord adatát adja vissza.

Adatbázisfüggvények: lista és feltételtartomány (emelt szint)

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

Excel tanfolyam · 12.3 lecke · emelt szint

Adatbázisfüggvények: lista és feltételtartomány

A 7.4-ben feltételtartománnyal szűrtünk, és akkor megígértük: ugyanez a feltételtartomány az emelt szintű adatbázisfüggvények alapja lesz. Itt az idő. Az AB kezdetű függvények (AB.SZUM, AB.ÁTLAG, AB.DARAB…) egy fejléces adatlistából a feltételtartomány szerint kiválogatott rekordokkal számolnak. A digitális kultúra emelt szintű érettségijén kifejezett követelmény, hogy a vizsgázó ismerje az adatbázis-kezelő függvények használatát.

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

A lecke végére

  • megnevezed az AB függvények három argumentumát;
  • feltételtartományt készítesz az AB függvényekhez;
  • a mezőt háromféleképpen adod meg;
  • ismered az AB függvénycsaládot;
  • összeveted az AB függvényeket a SZUMHA-val és a szűréssel.

Amit már tudsz

  • a feltételtartományt és az Irányított szűrést (7.4);
  • a szabályos adatlistát (7.1);
  • a SZUMHA-t és a TÖBB függvényeket (9.2, 9.4);
  • az elnevezett tartományokat (12.2).

A közös szerkezet

Minden adatbázisfüggvény ugyanúgy épül fel: AB.függvény(adatbázis;mező;kritérium).
ArgumentumJelentésPélda
adatbázisa teljes lista a fejlécsorral együtt (7.1)A1:D9 vagy egy név (12.1)
mezőmelyik oszloppal számoljon: a fejléc szövege idézőjelben, a fejlécet tartalmazó cella, vagy az oszlop sorszáma az adatbázison belül"Pontszám", D1 vagy 4
kritériuma feltételtartomány: fejléc (a lista mezőnevével betűre egyezően) és alatta a feltételek (7.4)F1:F2

💡 A feltételtartomány szabályai (7.4)

  • Az első sor a mezőnevek pontos másolata.
  • Egy sorban lévő feltételek: ÉS; külön sorban lévők: VAGY (12.6).
  • Számra: >40; szövegre: 9.b (a szöveges feltétel „ezzel kezdődik” jelentésű, pontos egyezéshez ="=9.b"); helyettesítő karakterek is (9.6).
  • Ne legyen benne üres sor (lásd a jóslást).

Első példa

Gépeld be a kitalált listát az A1 cellától, és mellé (az F oszlopba) a feltételtartományt:
A: NévB: OsztályC: NemD: Pontszám
Kiss Anna9.alány42
Nagy Bence9.bfiú38
Tóth Lili9.blány45
Szabó Máté9.afiú29
Varga Zsófi9.blány50
Kovács Péter9.cfiú47
Horváth Dóra9.clány33
Molnár Ádám9.bfiú41
Feltételtartomány:
Osztály
9.b
(az F1:F2 cellákban)
  • =AB.SZUM(A1:D9;"Pontszám";F1:F2) → 174 (38 + 45 + 50 + 41)
  • =AB.DARAB(A1:D9;"Pontszám";F1:F2) → 4
  • =AB.ÁTLAG(A1:D9;"Pontszám";F1:F2) → 43,5
Írd át az F2 cellát 9.a-ra: mindhárom eredmény azonnal frissül (71; 2; 35,5). Ez a nagy előnye a feltételtartománynak: a kérdés a cellákban van, a képlet változatlan.

🔮 Jósold meg!

A kritérium argumentumban F1:F3 szerepel, de az F3 üres. Mennyi lesz az AB.SZUM eredménye?
Megoldás
325: az összes pontszám összege. Az üres sor egy „feltétel nélküli” VAGY-ág (7.4): bármelyik rekord megfelel neki, így a „9.b VAGY bármi” mindent kiválaszt. A kritérium tartománya pontosan a feltételek utolsó soráig tartson.

A család

FüggvényMit ad a feltételnek megfelelő rekordokból?Lecke
AB.SZUMa mező értékeinek összege12.4
AB.ÁTLAGa mező értékeinek átlaga12.4
AB.DARABa mező számot tartalmazó celláinak száma12.5
AB.DARAB2a mező nem üres celláinak száma12.5
AB.MIN, AB.MAXa mező legkisebb, legnagyobb értéke12.5
AB.MEZŐa mező értéke, ha pontosan egy rekord felel meg (különben hibát ad)12.5
A nevük a már ismert függvényekre emlékeztet (SZUM, ÁTLAG, DARAB, DARAB2, MIN, MAX, 5. fejezet): ugyanazt számolják, csak a feltételtartomány által kiválogatott rekordokon.

AB, SZUMHA vagy szűrés?

SZUMHA / SZUMHATÖBB (9.2, 9.4)Irányított szűrés (7.4)AB függvények
Hol a feltétel?a képletben (vagy cellából összefűzve, 9.5)feltételtartománybanfeltételtartományban
VAGY kapcsolatcsak függvények összegével✅ külön sorokkal✅ külön sorokkal
Eredményegy szám, automatikusan frissülszűrt lista, újra kell futtatniegy szám, automatikusan frissül
Több eredmény egy feltételtartománnyal——✅ ugyanaz a tartomány több függvényhez

🎓 Érettségin

Ha a feladat kifejezetten adatbázisfüggvényt kér, vagy feltételtartományt kell készíteni, AB függvényt használj. Ha csak egy feltételes összesítés a kérdés, a SZUMHA család is helyes. A két megoldás közötti választásról és annak indoklásáról a 12.10 lecke szól.

Kihívás: első lépések AB függvényekkel

🗃️ Kérdezd a listát!

  1. Gépeld be a 2. rész listáját, és nevezd el adatok néven (A1:D9, a fejléccel együtt, 12.1).
  2. Készíts feltételtartományt az F1:F2 cellákba (Osztály | 9.b), és számold ki az AB.SZUM, AB.DARAB, AB.ÁTLAG értékét. Az eredmények: 174, 4, 43,5?
  3. Írd át a feltételt 9.c-re: mennyi az összeg, a darabszám és az átlag?
  4. Készíts egy második feltételtartományt a H1:H2 cellákba: Nem | lány. Mennyi a lányok átlaga?
  5. Ellenőrizd a 9.b eredményét SZUMHA-val és DARABTELI-vel is (9.1, 9.2). Ugyanaz?
  6. Használd a mezőt háromféleképpen: "Pontszám", D1 és 4. Mindhárom ugyanazt adja?
Megoldások
9.c: összeg 80 (47 + 33), darabszám 2, átlag 40. Lányok átlaga: (42 + 45 + 50 + 33) ÷ 4 = 42,5.

Hibavadászat

🐞 1. Peti nem létező mezője

Peti képlete: =AB.SZUM(A1:D9;"Pont";F1:F2). Az eredmény #ÉRTÉK!.
Mi a megoldás?
A mezőnek pontosan egyeznie kell a fejléccel (1. rész): a lista fejléce „Pontszám”, nem „Pont”. A legbiztosabb, ha a mező helyére a fejléc celláját írod (D1), így nem lehet elírni.

🐞 2. Lili „mindenki” eredménye

Lili a feltételtartományt nagyobbra jelölte ki, „hogy legyen hely új feltételeknek”: F1:F5. Csak az F2-ben van feltétel (9.b), az AB.DARAB mégis 8-at mutat.
Mit tegyen?
Az üres sorok „bármi” feltételként viselkednek (2. rész, jóslás), ezért minden rekord megfelel. Lili a kritérium tartományát pontosan a feltételek utolsó soráig adja meg: F1:F2. Ha később több sor kell, akkor bővítse.

Összefoglalás

  • AB.függvény(adatbázis;mező;kritérium): számolás a feltételnek megfelelő rekordokkal.
  • Adatbázis: a lista a fejléccel; mező: fejléc szövege, fejléc-cella vagy oszlopszám.
  • Kritérium: feltételtartomány pontos fejléccel; egy sor = ÉS, külön sor = VAGY, üres sor = minden.
  • Család: AB.SZUM, AB.ÁTLAG, AB.DARAB, AB.DARAB2, AB.MIN, AB.MAX, AB.MEZŐ.
  • Előny: a feltétel cellákban van, egy feltételtartomány több függvényhez is használható.

Ellenőrizd magad!

Kell-e a fejlécsor az AB függvény adatbázis argumentumába?
Igen, a mezőket a fejléc alapján azonosítja.
Milyen kapcsolatban vannak az egy sorba írt feltételek?
ÉS kapcsolatban.
Mit ad az AB függvény, ha a kritérium tartománya egy üres sort is tartalmaz?
Az összes rekordra számol, mert az üres sor minden rekordnak megfelel.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Adatbázis-kezelés táblázatkezelővel (szűrés összetett feltételekkel, függvényhasználat adatok összesítésére). A digitális kultúra emelt szintű érettségi követelménye: „Ismerje az adatbáziskezelő függvények használatát”. Az NKP-okostankönyvre épülő emelt szóbeli tétel „AB függvények használata” elemének bevezetése; kapcsolódás az SQL-hez (WHERE feltétel, aggregáló függvények).

Módszertan

A lecke a 7.4-ben megalapozott feltételtartományra építve vezeti be az adatbázisfüggvényeket, így az új fogalom egy ismert szerkezetre ül rá. A kis, ellenőrizhető adatsor és a feltétel módosítása az automatikus frissülést teszi tapasztalattá. A jóslás az üres sor csapdáját ismétli. Az összevető táblázat előkészíti a módszerválasztás indoklását (12.10).

Időterv

A közös szerkezet: 8 perc. Első példa és jóslás: 10 perc. A család: 4 perc. Összevetés: 5 perc. Kihívás: 14 perc. Hibavadászat: 4 perc.

Következő lecke: 12.4 AB.SZUM és AB.ÁTLAG

Összeg és átlag összetett feltételekkel, intervallummal, helyettesítő karakterrel és cellából vett feltétellel.

Elnevezett tartományok használata képletekben (emelt szint)

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

Excel tanfolyam · 12.2 lecke · emelt szint

Elnevezett tartományok használata képletekben

A nevek akkor érik meg igazán, amikor a képletekben szerepelnek: a =SZUMHATÖBB(pontszam;osztaly;"9.b";nem;"lány") szinte mondatként olvasható. Ebben a leckében a 9. és a 10. fejezet ismert függvényeit írod át elnevezett tartományokkal, megtanulod a nevek gyors beillesztését, és azt is, mire figyelj, hogy a nevekkel írt képletek ugyanolyan biztonságosak legyenek, mint a hivatkozásokkal írtak.

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

A lecke végére

  • neveket írsz és illesztesz be képletekbe;
  • a feltételes és keresőfüggvényeket nevekkel írod meg;
  • tudod, hogyan viselkedik a név másoláskor;
  • meglévő képletekre alkalmazod a neveket;
  • elkerülöd a nevek használatának tipikus hibáit.

Amit már tudsz

  • a tartományok elnevezését (12.1);
  • a feltételes összesítést (9.4);
  • az FKERES-t és az INDEX + HOL.VAN-t (10.2, 10.7);
  • a relatív és az abszolút hivatkozást (4.4, 4.5).

Név a képletben

  • Beírás: gépeld be a név első betűit a képletben: az Excel felajánlja a hasonló nevű függvényeket és neveket (a neveket címke ikon jelöli). Válaszd ki, és nyomj Tab-ot.
  • Beillesztés listából: Képletek → Definiált nevek → Használat képletben, vagy F3 (Név beillesztése): az összes név listája.
  • Kijelöléssel: ha a képlet írása közben kijelölsz egy elnevezett tartományt, az Excel magától a nevét írja be a cellacím helyett.
  • A név bármely munkalapról használható (munkafüzet hatókörnél, 12.1), munkalapnév nélkül: =SZUM(pontszam) a Statisztika lapon is működik, ha a pontszám a Versenyzők lapon van.

Nevek a függvényekben

Nevek (a Versenyzők lap oszlopai): nev, osztaly, nem, pontszam; az árlista teljes tartománya: arlista; bemeneti cella: hatar.
KérdésHivatkozássalNévvel
összpontszám=SZUM($D$2:$D$61)=SZUM(pontszam)
a 9. b létszáma=DARABTELI($B$2:$B$61;"9.b")=DARABTELI(osztaly;"9.b")
a határt elérők száma=DARABTELI($D$2:$D$61;">="&$H$1)=DARABTELI(pontszam;">="&hatar)
a 9. b-s lányok átlaga=ÁTLAGHATÖBB($D$2:$D$61;$B$2:$B$61;"9.b";$C$2:$C$61;"lány")=ÁTLAGHATÖBB(pontszam;osztaly;"9.b";nem;"lány")
ár a kódból=FKERES(A2;Árlista!$A$2:$C$50;3;HAMIS)=FKERES(A2;arlista;3;HAMIS)
a legjobb neve=INDEX($A$2:$A$61;HOL.VAN(MAX($D$2:$D$61);$D$2:$D$61;0))=INDEX(nev;HOL.VAN(MAX(pontszam);pontszam;0))

✅ Olvasd fel!

A nevekkel írt képlet szinte felolvasható: „az index a névből, ahol a pontszám a pontszámok maximuma”. Ha egy képlet hangosan nehezen olvasható, valószínűleg jobb neveket érdemes adni.

Másolás és rögzítés

🔮 Jósold meg!

Az E2 cellában: =HA(D2>=hatar;"továbbjut";""), ahol a hatar a Beállítások!B1 cella neve. Kitöltöd lefelé az E61-ig. Mi lesz az E10 képlete?
Megoldás
=HA(D10>=hatar;"továbbjut";""). A D2 relatív hivatkozás, ezért lefelé vándorol (4.4), a név viszont mindig ugyanarra a cellára mutat, úgy, mint egy abszolút hivatkozás (4.5). A névvel tehát nem kell dollárjelet írni, és nem lehet elfelejteni.
  • Nevek alkalmazása meglévő képletekre: ha már vannak hivatkozásos képleteid, és utólag nevezel el tartományokat: Képletek → Név megadása nyila → Nevek alkalmazása. Az Excel a megfelelő hivatkozásokat a nevekre cseréli.

Mire figyelj?

  • Egyforma méretű nevek: a feltételes összesítő függvényekben (9.2, 9.4) a neveknek ugyanakkora tartományra kell mutatniuk, ugyanabban a sorban kezdődve. Ellenőrizd a Névkezelőben.
  • Elírt név: ha a név nem létezik, a képlet #NÉV? hibát ad (4.9), mint egy elírt függvénynév.
  • Teljes név egy soros számításban: egy sorhoz tartozó képletben (például „ennek a diáknak a pontszáma × 2”) a cellára hivatkozz (=D2*2), ne az egész névre. Az újabb Excel-verziókban a =pontszam*2 az egész oszlop eredményét „kiönti” a cellák alá, és ha ott már adat van, #KIÖMLÉS! hibát ad (14.8). A név a függvények tartományaként való igazán.

🎓 Érettségin

Ha a feladat kéri, hogy „a számításokban a pontszam névre hivatkozzon”, akkor a megfelelő képletekben a nevet kell használni, nem a cellatartományt. A javítók ezt a képletben ellenőrzik.

Kihívás: statisztikai lap nevekkel

📊 Minden képlet nevekkel

  1. A 12.1-ben elnevezett versenylistához készíts egy külön Statisztika munkalapot.
  2. Nevekkel számold ki: a versenyzők számát, az összpontszámot, az átlagot, a legjobb és a leggyengébb eredményt, és a legjobb versenyző nevét.
  3. Osztályonként (9.a, 9.b, 9.c, egy oszlopban) a létszámot, az átlagot és a határt elérők számát (DARABTELI, ÁTLAGHA, DARABHATÖBB nevekkel).
  4. Írd át a hatar értékét a Beállítások lapon: frissül minden?
  5. Egy régebbi, hivatkozásos képleteket tartalmazó lapon próbáld ki a Nevek alkalmazása parancsot. Melyik képletek változtak meg?
  6. Bónusz: hasonlítsd össze egy osztálytársad megoldásával: melyik képletet volt könnyebb megérteni, a nevest vagy a hivatkozásost?

Hibavadászat

🐞 1. Peti eltérő nevei

Peti képlete: =SZUMHATÖBB(pontszam;osztaly;"9.b"). Az eredmény #ÉRTÉK!. A Névkezelőben: pontszam = D2:D61, osztaly = B2:B60.
Mi a megoldás?
A két név különböző méretű tartományra mutat (4. rész), ezért a SZUMHATÖBB nem tudja párba állítani a sorokat (9.4). Peti javítsa az osztaly hivatkozását B2:B61-re a Névkezelőben.

🐞 2. Lili ismeretlen neve

Lili képlete: =ÁTLAG(pontszám), az eredmény #NÉV?. A Névkezelőben a név így szerepel: pontszam.
Mit tegyen?
A „pontszám” (ékezettel) és a „pontszam” (ékezet nélkül) két különböző név (12.1): a képletben használt név nem létezik. Lili használja a beillesztési listát (F3) vagy az automatikus kiegészítést, így nem írhatja el a nevet.

Összefoglalás

  • Név beírása: automatikus kiegészítés (Tab), F3 (Név beillesztése), vagy kijelöléssel.
  • A név bármely lapról, lapnév nélkül használható (munkafüzet hatókörnél).
  • Másoláskor a név nem vándorol: úgy viselkedik, mint egy abszolút hivatkozás.
  • Nevek alkalmazása: a meglévő képletek hivatkozásait nevekre cseréli.
  • Figyelj: egyforma méretű nevek, elírt név (#NÉV?), egész név egy soros képletben (#KIÖMLÉS!).

Ellenőrizd magad!

Hogyan illeszted be gyorsan egy név nevét a képletbe?
F3-mal (Név beillesztése) vagy a gépelés közbeni automatikus kiegészítéssel.
Kell-e dollárjelet tenni egy név elé, ha a képletet másolod?
Nem, a név mindig ugyanarra a tartományra mutat.
Mit jelez a #NÉV? hiba egy névvel írt képletben?
Hogy a képletben szereplő név nem létezik (például el van írva).
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (képletek és függvények paraméterezése, cellahivatkozások). A digitális kultúra emelt szintű érettségi új követelménye (képletekben tartományokra nevük segítségével hivatkozni) közvetlen gyakorlása; kapcsolódás a programozáshoz (beszédes változónevek, olvasható kód).

Módszertan

A lecke a már ismert függvényeket „fordítja le” nevekre, így az új tudás a meglévőre épül, a táblázatos összevetés a két írásmód egyenértékűségét mutatja meg. A jóslás a név abszolút viselkedését tapasztalati úton mutatja be. A „mire figyelj” rész a három jellegzetes hibát (méreteltérés, elírás, kiömlés) előre jelzi. A beszédes nevek és a „felolvasható képlet” a programozásból ismert olvashatósági elvet viszi át a táblázatkezelésre.

Időterv

Név a képletben: 6 perc. Nevek a függvényekben: 10 perc. Másolás és jóslás: 6 perc. Mire figyelj: 5 perc. Kihívás: 14 perc. Hibavadászat: 4 perc.

Következő lecke: 12.3 Adatbázisfüggvények: lista és feltételtartomány

Az emelt szint másik különlegessége: az AB függvények, a 7.4 feltételtartományára építve.

Tartományok elnevezése az Excelben (emelt szint)

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

Excel tanfolyam · 12.1 lecke · emelt szint

Tartományok elnevezése

A =SZUM($D$2:$D$61) pontosan működik, de nem mondja meg, mit adunk össze. A =SZUM(pontszam) első ránézésre érthető. Kezdődik a 12. fejezet, az emelt szintű táblázatkezelés. Az első téma a tartományok elnevezése, amely 2024-től az emelt szintű érettségi követelményei között szerepel: a vizsgázónak tudnia kell képletekben tartományokra a nevük segítségével hivatkozni. Ebben a leckében a nevek létrehozását és kezelését tanulod meg, a következőben a használatukat.

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

A lecke végére

  • háromféleképpen nevezel el egy tartományt;
  • ismered a névadás szabályait;
  • beállítod és megérted a név hatókörét;
  • a Névkezelőben szerkeszted és törlöd a neveket;
  • elnevezett állandót hozol létre.

Amit már tudsz

  • az abszolút hivatkozást (4.5);
  • a bemeneti cellát, „varázsszám” nélkül (4.3);
  • a hivatkozást másik munkalapra (4.7);
  • a szabályos adatlistát (7.1).

Miért nevezünk el?

  • Olvashatóság: a =ÁTLAG(pontszam) érthetőbb, mint a =ÁTLAG($D$2:$D$61).
  • Biztos hivatkozás: a név mindig ugyanarra a tartományra mutat, akárhová másolod a képletet: úgy viselkedik, mint egy abszolút hivatkozás (4.5), dollárjel nélkül.
  • Egy helyen módosítható: ha a lista bővül, a nevet egyszer kell átállítani a Névkezelőben, és minden képlet követi.
  • Gyors navigáció: a Névmezőben a névre kattintva az Excel azonnal kijelöli a tartományt, akár másik munkalapon is.

Három módszer

MódszerLépésekMikor jó?
Névmezőjelöld ki a tartományt (például D2:D61, fejléc nélkül), kattints a szerkesztőléc bal oldalán lévő Névmezőbe (ahol a cellacím látszik), írd be a nevet (pontszam), és nyomj Enter-ta leggyorsabb, egy-egy névhez
Név megadásaKépletek lap → Definiált nevek → Név megadása: név, hatókör (munkafüzet vagy egy munkalap), megjegyzés, hivatkozásha a hatókört is be kell állítani, vagy állandót nevezel el
Létrehozás a kijelölésbőljelöld ki a listát a fejléccel együtt → Képletek → Létrehozás a kijelölésből → „Első sor”egy listán minden oszlopnak egy lépésben, a fejlécből vett névvel

💡 Elnevezett állandó

Nemcsak tartomány, hanem egy érték is kaphat nevet: Név megadása → Név: afa, Hivatkozás: =0,27. Utána bármely képletben: =C2*(1+afa). Ez a „varázsszám nélkül” elv (4.3) egy cella nélküli változata. Átláthatóbb azonban, ha az állandó egy felirattal ellátott cellában áll, és a cellát nevezed el (4.7 paraméterlapja).

Névadási szabályok

  • Betűvel vagy aláhúzásjellel (_) kezdődjön; utána betű, szám, pont, aláhúzás.
  • Nem lehet benne szóköz: helyette aláhúzás vagy pont (pont_szam, pont.szam).
  • Nem lehet olyan, mint egy cellacím (például AB12, AR2026), és nem lehet egyetlen C, R betű.
  • A kis- és nagybetű nem számít (Pontszam = pontszam); az ékezetes betű megengedett, de az ékezet nélküli név biztonságosabb (más gépen, más programban is).
  • Rövid, beszédes, egyedi legyen.

🔮 Jósold meg!

Melyik név érvényes? (a) Pont szám (b) 2026_adatok (c) ar2026 (d) _pont (e) pontszám (f) adatok_2026
Megoldás
(a) ❌ szóköz van benne. (b) ❌ számmal kezdődik. (c) ❌ meglepő, de cellacím: az AR oszlop 2026. sora! (d) ✅ aláhúzással kezdődhet. (e) ✅ az ékezet megengedett. (f) ✅ betűvel kezdődik, a szám a végén van, és nem cellacím. A (c) a leggyakoribb meglepetés: a betű + szám alakú rövid nevek (például ev2026 is!) cellacímnek számíthatnak.

Névkezelő és hatókör

  • Névkezelő: Képletek → Névkezelő (Ctrl + F3). Itt látod az összes nevet, a hivatkozásukat és a hatókörüket; szerkesztheted (például bővítheted a tartományt) és törölheted őket.
  • Hatókör: a Munkafüzet hatókörű név minden munkalapon használható; a munkalap hatókörű csak azon a lapon. Ha nincs különleges okod, a munkafüzet hatókör a jobb.
  • Elnevezett tartományok megjelenítése: a Névmező legördülő nyila listázza az összes nevet; egy névre kattintva a tartomány kijelölődik.

🎓 Érettségi típusú utasítás

„Nevezze el a Versenyzők munkalap D2:D61 tartományát pontszam néven, és a további számításokban erre a névre hivatkozzon!” Pontosan a megadott nevet használd (betűre egyezően), csak a megadott tartományt jelöld ki (fejléc nélkül, ha az utasítás nem mondja másként), és ellenőrizd a Névkezelőben.

Kihívás: nevek egy versenylistán

🏷️ Nevezd el, és ellenőrizd!

  1. Vedd elő a 8.9 versenyeredményeit (vagy a 9.1 osztálystatisztikáját) egy Versenyzők nevű munkalapon.
  2. A Névmezővel nevezd el a pontszámok oszlopát pontszam néven (fejléc nélkül).
  3. A Létrehozás a kijelölésből paranccsal nevezd el egyszerre a többi oszlopot a fejlécük alapján. Milyen neveket kapott az Excel a szóközös fejlécekből (például „Első idegen nyelv”)?
  4. Hozz létre egy hatar nevű cellát a ponthatárral, és egy afa nevű állandót 27%-kal.
  5. Nyisd meg a Névkezelőt: hány név van? Mi a hatókörük? Töröld az egyiket, majd hozd létre újra.
  6. Egy másik munkalapról a Névmező listájával ugorj a pontszam tartományra.
  7. Próbáld ki a jóslás érvénytelen neveit: milyen hibaüzenetet kapsz?

Hibavadászat

🐞 1. Peti fejléces neve

Peti a pontszam nevet a D1:D61 tartományra adta, a „Pontszám” fejléccel együtt. A =DARAB2(pontszam) eggyel több versenyzőt mutat, mint ahányan vannak.
Mi a megoldás?
A fejléc is a tartományba került, és a DARAB2 a szöveget is megszámolja (5.4). Peti a Névkezelőben javítsa a hivatkozást D2:D61-re (4. rész). Egy név tartománya általában csak az adatokat tartalmazza, ugyanúgy, mint a függvények tartományai (9.1).

🐞 2. Lili „eltűnt” neve

Lili a Név megadása ablakban a hatar nevet hozta létre a Beállítások munkalap hatókörével. Az Eredmények lapon a =pontszam>=hatar képlet #NÉV? hibát ad.
Mit tegyen?
A munkalap hatókörű név csak azon a lapon érhető el (4. rész). Lili a Névkezelőben törölje, és hozza létre újra Munkafüzet hatókörrel (a meglévő név hatóköre utólag nem módosítható).

Összefoglalás

  • Elnevezés: Névmező, Név megadása, Létrehozás a kijelölésből.
  • Szabályok: betűvel vagy _ jellel kezdődik, nincs benne szóköz, nem lehet cellacím (vigyázz: ar2026, ev2026!).
  • Hatókör: munkafüzet (minden lapon) vagy munkalap (csak ott).
  • Névkezelő (Ctrl + F3): szerkesztés, bővítés, törlés.
  • Állandó is elnevezhető (afa = 0,27), de átláthatóbb egy elnevezett bemeneti cella.

Ellenőrizd magad!

Hol adhatsz meg a leggyorsabban nevet egy kijelölt tartománynak?
A szerkesztőléc bal oldalán lévő Névmezőben.
Érvényes név-e az „ev2026”?
Nem, mert ez az EV oszlop 2026. sorának cellacíme. Helyette például: ev_2026 vagy adatok2026.
Mi a különbség a munkafüzet és a munkalap hatókör között?
A munkafüzet hatókörű név minden lapon használható, a munkalap hatókörű csak azon a lapon.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (cellahivatkozások, több munkalap használata). A 2020-as Nat-ra épülő digitális kultúra érettségi új emelt szintű követelménye: a vizsgázó legyen képes képletekben tartományokra a nevük segítségével hivatkozni (vö. a BGE elemzése a 2024-es vizsgakövetelményekről; az OH mintafeladatai is tartalmazzák).

Módszertan

A fejezet nyitó leckéje a nevek létrehozását és kezelését választja külön a használatuktól (12.2), hogy a technikai lépések és a névadási szabályok biztosak legyenek. A jóslás a cellacímnek tűnő nevek meglepő csapdáját (ar2026) mutatja meg. Az érettségi típusú utasítás a pontos feladatolvasást erősíti. A hatókör és a Névkezelő a hibakeresést segíti.

Időterv

Miért nevezünk el: 4 perc. Három módszer: 10 perc. Névadási szabályok és jóslás: 8 perc. Névkezelő és hatókör: 6 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 12.2 Elnevezett tartományok használata képletekben

SZUM(pontszam), DARABTELI(osztaly;"9.b"), FKERES(kod;arlista;3;HAMIS): olvasható, biztos képletek nevekkel.