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 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és | Jelentés |
|---|---|
| feltételek egy sorban (különböző oszlopokban) | ÉS: mindegyiknek teljesülnie kell |
| feltételek külön sorokban | VAGY: elég, ha valamelyik sor teljesül |
| üres cella egy sorban | arra a mezőre abban a sorban nincs feltétel |
| teljesen üres sor a kritérium tartományában | minden 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és | Feltételtartomány | AB.SZUM (Pontszám) | ||||||
|---|---|---|---|---|---|---|---|---|
| 9. a vagy 9. c (ugyanaz a mező) |
| 151 (42 + 29 + 47 + 33) | ||||||
| 9. a-s vagy lány (különböző mezők, „átlósan”) |
| 199 (Kiss Anna, Szabó Máté, Tóth Lili, Varga Zsófi, Horváth Dóra) |
⚠️ Átlós elrendezés
Különböző mezők VAGY kapcsolatánál a feltételek különböző sorokba és különböző oszlopokba kerülnek, a többi cella üres marad. Ha a két feltétel egy sorba kerül, ÉS lesz belőle (hibavadászat).
Összetett feltételek
| Kérdés | Feltételtartomány | AB.SZUM | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| (9. b és lány) vagy (9. c és fiú) |
| 142 (Tóth Lili 45, Varga Zsófi 50, Kovács Péter 47) | |||||||||
| legalább 45 pont vagy 30 alatt |
| 171 (45 + 50 + 47 + 29) | |||||||||
| a lányok közül 40 és 45 között, vagy bárki 50 felett |
| 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).
- A 9. b-sek vagy a fiúk.
- A 9. a-s fiúk vagy a 9. c-s lányok.
- Akik 35 pont alatt vagy 48 pont felett teljesítettek.
- A 9. a-s vagy 9. c-s lányok. (Tipp: a „lány” mindkét sorba kell!)
- Ellenőrizd az 1. kérdés darabszámát DARABTELI-vel is: miért más az eredmény?
Megoldások
- Nagy Bence, Tóth Lili, Varga Zsófi, Molnár Ádám, Szabó Máté, Kovács Péter: összeg 250, darab 6.
- Szabó Máté, Horváth Dóra: összeg 62, darab 2.
- 29, 33, 50: összeg 112, darab 3.
- Feltételtartomány: Osztály | Nem → 9.a | lány; 9.c | lány. Kiss Anna, Horváth Dóra: összeg 75, darab 2.
- 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ő.
