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ód | Termék | Ár (Ft) |
|---|---|---|
| C01 | Kakaós csiga | 290 |
| C02 | Pogácsa | 120 |
| C03 | Csokoládé | 350 |
| C04 | Ásványvíz | 200 |
📖 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és | Mit keres? | Példa |
|---|---|---|
| Pontos | pontosan 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ő sorrendben | pontszá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ény | Mit csinál? | Lecke |
|---|---|---|
FKERES | a 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 |
VKERES | ugyanez vízszintesen: az első sorban keres, és egy lejjebb lévő sorból ad vissza | 10.4 |
HOL.VAN | megmondja, hányadik helyen van egy érték egy sorban vagy oszlopban | 10.5 |
INDEX | visszaadja egy tartomány megadott sorában és oszlopában álló értéket | 10.6 |
INDEX + HOL.VAN | bármelyik irányban keres, akár balra is | 10.7 |
XKERES | az újabb Excel-verziók (2021-től, Microsoft 365) „mindentudó” keresőfüggvénye: bármelyik irányban, beépített hibakezeléssel | 10.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!
- Í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).
- Í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.
- „Futtasd” az algoritmusodat mind a négy kódra. Mi történik a C05-tel?
- Számold ki kézzel a rendelés végösszegét a megtalált árakkal.
- 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?
- 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.
