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 közös szerkezet
Minden adatbázisfüggvény ugyanúgy épül fel: AB.függvény(adatbázis;mező;kritérium).
| Argumentum | Jelentés | Példa |
|---|---|---|
| adatbázis | a 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érium | a 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év | B: Osztály | C: Nem | D: Pontszám |
|---|---|---|---|
| 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:
| Osztály |
|---|
| 9.b |
=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ény | Mit ad a feltételnek megfelelő rekordokból? | Lecke |
|---|---|---|
AB.SZUM | a mező értékeinek összege | 12.4 |
AB.ÁTLAG | a mező értékeinek átlaga | 12.4 |
AB.DARAB | a mező számot tartalmazó celláinak száma | 12.5 |
AB.DARAB2 | a mező nem üres celláinak száma | 12.5 |
AB.MIN, AB.MAX | a mező legkisebb, legnagyobb értéke | 12.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ányban | feltételtartományban |
| VAGY kapcsolat | csak függvények összegével | ✅ külön sorokkal | ✅ külön sorokkal |
| Eredmény | egy szám, automatikusan frissül | szűrt lista, újra kell futtatni | egy 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!
- Gépeld be a 2. rész listáját, és nevezd el
adatoknéven (A1:D9, a fejléccel együtt, 12.1). - 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?
- Írd át a feltételt 9.c-re: mennyi az összeg, a darabszám és az átlag?
- Készíts egy második feltételtartományt a H1:H2 cellákba: Nem | lány. Mennyi a lányok átlaga?
- Ellenőrizd a 9.b eredményét SZUMHA-val és DARABTELI-vel is (9.1, 9.2). Ugyanaz?
- 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.
