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ő.