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.