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

Excel tanfolyam · 10.1 lecke

Keresési érték, tábla és találat

A büfé rendelési listáján csak a termékkódok szerepelnek, az árak egy külön árlistában vannak. A tanár a pontszámokból jegyet ad egy ponthatár-táblázat szerint. A könyvtár a kölcsönzésekhez a könyvek adatait egy másik táblából veszi. Mindhárom esetben két táblázatot kell összekapcsolni: egy adat alapján meg kell keresni egy másikat. Kezdődik a 10. fejezet: a keresőfüggvények. Ebben az első leckében a fogalmakat és a logikát ismered meg, mielőtt függvényt írnál.

  • ⏱ kb. 45 perc
  • 🎯 kezdőknek
  • 🎒 10. osztálytól

A lecke végére

  • megnevezed a keresés részeit;
  • megkülönbözteted a pontos és a közelítő egyezést;
  • tudod, mitől jó egy keresési kulcs;
  • áttekinted a keresőfüggvények családját;
  • a keresést algoritmusként is leírod.

Amit már tudsz

  • az azonosító szerepét egy adatlistában (7.1);
  • a kulcsot és az idegen kulcsot (Relációs adatmodell);
  • a szövegként tárolt számokat és a szóközöket (2.8, 7.6);
  • a határtáblázatot (8.5).

A keresés részei

KódTermékÁr (Ft)
C01Kakaós csiga290
C02Pogácsa120
C03Csokoládé350
C04Ásványvíz200

📖 Fogalomtár

Keresési érték
Amit keresel, például a rendelésben álló „C03” kód.
Keresési tábla
A táblázat, amelyben keresel (az árlista). Első oszlopa (FKERES-nél) a kulcsoszlop, amelyben a keresési értéket keresed.
Visszaadott oszlop
Az az oszlop, amelynek értékére szükséged van (az Ár, vagy a Termék neve).
Találat
A visszaadott érték abban a sorban, ahol a keresési értéket megtaláltad: C03 → 350 Ft.

Mondd ki szavakkal: „Keresd meg a C03 kódot az árlista első oszlopában, és add vissza ugyanabból a sorból az Ár oszlop értékét.” Minden keresőfüggvény ennek a mondatnak egy-egy változata.

Pontos vagy közelítő?

EgyezésMit keres?Példa
Pontospontosan a keresési értéket; ha nincs, nincs találat (#HIÁNYZIK)termékkód → ár; tanulókód → név (10.2)
Közelítőa legnagyobb értéket, amely még nem nagyobb a keresettnél (a sávot, amelybe beleesik); a tábla legyen növekvő sorrendbenpontszám → jegy a határtáblázatból (8.5, 10.3); vásárlási összeg → kedvezménysáv

💡 Melyik kell?

Ha a keresési érték egy azonosító (kód, név, e-mail), mindig pontos egyezés kell. Ha egy mennyiség, amely egy sávba esik (pontszám, összeg, életkor), akkor közelítő. A rossz választás a leggyakoribb keresési hiba.

A jó kulcs

  • Egyedi: a keresési tábla kulcsoszlopában minden érték csak egyszer szerepeljen (7.1, 7.5). Ha egy kód kétszer szerepel, a függvény az elsőt találja meg, a másodikról nem is tudsz.
  • Egységes típusú: a keresési érték és a kulcsoszlop ugyanolyan típusú legyen (mindkettő szám, vagy mindkettő szöveg, 2.8).
  • Tiszta: felesleges szóköz nélkül, egységes írásmóddal (7.6). A kis- és nagybetű a keresésnél nem számít.
  • Az adatbázis-kezelés nyelvén: a keresési tábla kulcsa az elsődleges kulcs, a rendelésben álló kód pedig az idegen kulcs (Relációs adatmodell). A keresőfüggvény a két táblát a kulcs mentén kapcsolja össze, mint egy lekérdezés.

🔮 Jósold meg!

Az árlista kulcsoszlopában a kódok szövegként vannak tárolva: „007”, „008”, „009”. A rendelésben a kódot számként írták be: 7. Megtalálja a keresés?

Megoldás

Nem. A szöveg „007” és a szám 7 két különböző érték (8.1): a pontos egyezés nem talál, és #HIÁNYZIK hibát kapsz. Ugyanez fordítva is igaz (szöveges keresési érték, számos kulcsoszlop). Megoldás: egységesítsd a típust (7.6), és a vezető nullás kódokat mindkét táblában szövegként tárold (2.8).

A keresőfüggvények családja

FüggvényMit csinál?Lecke
FKERESa tábla első oszlopában keres, és egy jobbra lévő oszlopból ad vissza értéket (függőleges keresés)10.2, 10.3
VKERESugyanez vízszintesen: az első sorban keres, és egy lejjebb lévő sorból ad vissza10.4
HOL.VANmegmondja, hányadik helyen van egy érték egy sorban vagy oszlopban10.5
INDEXvisszaadja egy tartomány megadott sorában és oszlopában álló értéket10.6
INDEX + HOL.VANbármelyik irányban keres, akár balra is10.7
XKERESaz újabb Excel-verziók (2021-től, Microsoft 365) „mindentudó” keresőfüggvénye: bármelyik irányban, beépített hibakezeléssel10.7

✅ Melyiket tanuld meg?

Mindegyiket érdemes ismerni. Az FKERES a legelterjedtebb, a régebbi fájlokban és sok feladatban ezzel találkozol. Az INDEX és a HOL.VAN együtt mindenhol működik, és rugalmasabb. Az XKERES a legkényelmesebb, de csak az újabb verziókban érhető el. Az érettségin bármelyik helyes megoldás elfogadható, ha a vizsgán használt programban működik.

Kihívás: keresés papíron, algoritmussal

✏️ Légy te a keresőfüggvény!

  1. Írd fel papírra (vagy egy Excel-lapra) az 1. rész árlistáját, és mellé egy rendelést: C03 (2 db), C01 (5 db), C05 (1 db), C02 (10 db).
  2. Írd le lépésenként, hogyan keresed meg egy kód árát: „Kezdd az első sornál; ha a kód egyezik, …; ha nem, …; ha elfogyott a lista, …”. Ez a programozásból ismert lineáris keresés típusalgoritmusa.
  3. „Futtasd” az algoritmusodat mind a négy kódra. Mi történik a C05-tel?
  4. Számold ki kézzel a rendelés végösszegét a megtalált árakkal.
  5. Gondold végig: mi történne, ha az árlistában a C03 kétszer szerepelne különböző árral? És ha a rendelésben „c03” vagy „C03 ” állna?
  6. Melyik keresés lenne „közelítő”? Írj egy példát a saját életedből (például mozijegy ára életkor szerint).

Hibavadászat

🐞 1. Peti kétarcú kódja

Peti árlistájában a C03 kód kétszer szerepel: egyszer 350 Ft-tal (régi ár), egyszer 380 Ft-tal (új ár, a lista végén). A keresés mindig a régi árat adja.

Mi a megoldás?

A keresőfüggvények az első találatnál megállnak (3. rész). A kulcsnak egyedinek kell lennie: Peti törölje a régi sort (vagy tegye egy külön „Archív” lapra), és ellenőrizze az ismétlődéseket (7.5).

🐞 2. Lili néhány „nem létező” kódja

Lili rendelésében néhány kódhoz nem talál árat, pedig az árlistában szemmel látja őket. Kiderül, hogy az importált rendelésben ezek a kódok így szerepelnek: „C02 ”.

Mit tegyen?

A kód végén szóköz van, ezért nem egyezik a „C02” értékkel (7.6). Lili tisztítsa meg a rendelés kódoszlopát KIMETSZ-szel, és a keresés működni fog.

Összefoglalás

  • Keresés: keresési érték + keresési tábla (kulcsoszlop) + visszaadott oszlop → találat.
  • Pontos egyezés azonosítókhoz, közelítő egyezés sávokhoz (rendezett tábla).
  • A jó kulcs egyedi, egységes típusú és tiszta.
  • Család: FKERES, VKERES, HOL.VAN, INDEX, INDEX + HOL.VAN, XKERES.
  • A keresőfüggvény a lineáris keresés algoritmusát hajtja végre.

Ellenőrizd magad!

Mi a keresési érték?

Az az adat, amelyet a keresési tábla kulcsoszlopában keresel.

Mikor kell közelítő egyezés?

Ha egy mennyiség sávját keresed (például pontszám → jegy), rendezett határtáblázatban.

Mi történik, ha a kulcs kétszer szerepel a keresési táblában?

A keresés az első előfordulást adja vissza.

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények használata), Adatbázis-kezelés (kulcs, idegen kulcs, táblakapcsolatok); kapcsolódás a programozáshoz (lineáris keresés típusalgoritmus). Az NKP-okostankönyvre épülő emelt szóbeli tétel keresőfüggvényes elemének (HOL.VAN, INDEX…HOL.VAN, FKERES, XKERES) bevezetése.

Módszertan

A fejezet nyitó leckéje függvényírás nélkül, fogalmi szinten alapozza meg a keresést: a fogalomtár és a „mondatba foglalás” minden későbbi keresőfüggvényhez közös keretet ad. A papíros kihívás a keresést algoritmusként írja le, így a táblázatkezelést a programozás típusalgoritmusaihoz kapcsolja (unplugged tevékenység). A kulcs minőségi követelményei (egyediség, típus, tisztaság) az adatbázis-kezelés kulcsfogalmait és a 7. fejezet adattisztítását kapcsolják össze.

Időterv

A keresés részei: 8 perc. Pontos vagy közelítő: 6 perc. A jó kulcs és jóslás: 8 perc. A család: 5 perc. Kihívás: 14 perc. Hibavadászat: 4 perc.

Következő lecke: 10.2 FKERES pontos egyezéssel

Az első keresőfüggvény: termékkódból ár, tanulókódból név, egyetlen képlettel.