Excel táblázatkezelés kezdőknek › 10.7 lecke
Excel tanfolyam · 10.7 lecke
INDEX és HOL.VAN együtt, valamint az XKERES
Az FKERES csak jobbra tud visszaadni, és ha egy oszlopot beszúrsz, elcsúszik az oszlopszáma. A HOL.VAN megmondja, hányadik sorban van a keresett érték, az INDEX pedig kiolvassa, mi van egy oszlop ugyanazon sorában. Együtt egy rugalmas kereső párost alkotnak, amely bármelyik irányban keres, akár sorra és oszlopra egyszerre. Az újabb Excel-verziókban mindezt egyetlen függvény, az XKERES is tudja: a lecke végére mindkettőt használni fogod.
- ⏱ kb. 45 perc
- 🎓 haladóbb
- 🎒 10. osztálytól
A lecke végére
- INDEX és HOL.VAN párral keresel bármelyik irányban;
- sorra és oszlopra egyszerre keresel;
- közelítő keresést végzel határtáblával;
- XKERES-sel keresel, üzenettel a hiányzó találatra;
- kiválasztod a helyzethez illő keresőmódszert.
Amit már tudsz
A kereső páros
Mondd ki szavakkal: „Keresd meg az A2 értékét a kulcsoszlopban (HOL.VAN → hányadik sor), és add vissza a visszaadott oszlop ugyanannyiadik elemét (INDEX).” A két oszlop egymástól független: lehet egymás mellett, távol, akár más munkalapon is, és a visszaadott oszlop balra is lehet a kulcsoszloptól.
| Feladat | Képlet (A: termék, B: kód, C: ár) |
|---|---|
| kódból ár (FKERES is tudná) | =INDEX($C$2:$C$20;HOL.VAN(H1;$B$2:$B$20;0)) |
| kódból terméknév – balra! (FKERES nem tudja) | =INDEX($A$2:$A$20;HOL.VAN(H1;$B$2:$B$20;0)) |
| a legdrágább termék neve | =INDEX($A$2:$A$20;HOL.VAN(MAX($C$2:$C$20);$C$2:$C$20;0)) |
🔮 Jósold meg!
Peti képlete: =INDEX($A$3:$A$21;HOL.VAN(H1;$B$2:$B$20;0)). Az INDEX tartománya a 3., a HOL.VAN tartománya a 2. sorban kezdődik. A keresett kód a B5-ben van. Melyik termék nevét kapja?
Megoldás
A HOL.VAN eredménye 4 (B2 = 1., B3 = 2., B4 = 3., B5 = 4.). Az INDEX a saját tartományának 4. elemét adja: A3 = 1., A4 = 2., A5 = 3., A6 = 4. → az A6, vagyis a következő sor termékét, hibaüzenet nélkül. A két tartománynak ugyanabban a sorban kell kezdődnie és ugyanolyan hosszúnak kell lennie (mint a SZUMHA-nál, 9.2).
Kétirányú és közelítő keresés
- Sorra és oszlopra egyszerre: két HOL.VAN, az egyik a sort, a másik az oszlopot adja. Az órarendben (sorok: órák, oszlopok: napok):
=INDEX($B$2:$F$8;HOL.VAN($H$2;$A$2:$A$8;0);HOL.VAN($H$1;$B$1:$F$1;0)), ahol H1 a nap neve, H2 az óra (az A oszlopban az órák sorszáma áll). - Közelítő keresés határtáblával (10.3, 10.5):
=INDEX($I$2:$I$6;HOL.VAN(B2;$H$2:$H$6;1))→ a pontszámhoz tartozó jegy. Ez az NKP-feladatokban is szereplő „INDEX…HOL.VAN” megoldás.
✅ Miért szeretik a profik?
Az INDEX + HOL.VAN minden Excel-verzióban (és a LibreOffice Calcban is) működik, bármelyik irányban keres, és ha oszlopot szúrsz be, nem csúszik el, mert nincs benne beírt oszlopszám. Hátránya, hogy hosszabb és elsőre nehezebben olvasható.
Az XKERES
Az újabb Excel-verziókban (Excel 2021, 2024 és Microsoft 365; az Excel 2016-ban és 2019-ben nem érhető el) az XKERES egyetlen függvényben adja mindezt:
XKERES(keresett_érték;keresési_tömb;visszaadott_tömb;[ha_nincs_találat];[illesztési_mód];[keresési_mód])
| Feladat | Képlet |
|---|---|
| kódból terméknév (balra is) | =XKERES(H1;$B$2:$B$20;$A$2:$A$20) |
| üzenet, ha nincs találat | =XKERES(H1;$B$2:$B$20;$A$2:$A$20;"ismeretlen kód") |
| pontszámból jegy (határtábla) | =XKERES(B2;$H$2:$H$6;$I$2:$I$6;;-1) |
- Az alapértelmezés a pontos egyezés: nem kell HAMIS-t vagy 0-t írni (a leggyakoribb FKERES-hiba így nem fordulhat elő).
- Illesztési mód: 0 = pontos (alap); −1 = pontos, vagy ha nincs, a következő kisebb (ez a határtáblás sávkeresés, a tábla rendezése nélkül is működik); 1 = pontos, vagy a következő nagyobb; 2 = helyettesítő karakterekkel (9.6).
- Keresési mód: 1 = elölről (alap); −1 = hátulról (például a legutolsó előfordulás).
⚠️ Ellenőrizd a verziót!
Ha egy XKERES-t tartalmazó fájlt régebbi Excelben nyitsz meg, vagy egy régebbi Excelben próbálod beírni, #NÉV? hibát kapsz (4.9). Érettségin a vizsgán használt program dönt: ha nincs benne XKERES, az FKERES vagy az INDEX + HOL.VAN a biztos választás.
Melyiket válaszd?
| FKERES | INDEX + HOL.VAN | XKERES | |
|---|---|---|---|
| Keresés balra | ❌ | ✅ | ✅ |
| Oszlopbeszúrás után is jó | ❌ (beírt oszlopszám) | ✅ | ✅ |
| Pontos egyezés alapból | ❌ (HAMIS kell) | ❌ (0 kell) | ✅ |
| Üzenet hiányzó találatra | HAHIBA-val (10.8) | HAHIBA-val | ✅ beépítve |
| Sorra és oszlopra egyszerre | ❌ | ✅ | ✅ (egymásba ágyazva) |
| Minden Excel-verzióban | ✅ | ✅ | ❌ (2021-től) |
Kihívás: egy feladat, három megoldás
🔁 Hasonlítsd össze a módszereket!
- Készíts egy árlistát úgy, hogy a Termék az A oszlopban, a Kód a B-ben, az Ár a C-ben legyen.
- Egy keresőcellába (H1) írj egy kódot. Írd ki a hozzá tartozó árat háromféleképpen: FKERES-sel, INDEX + HOL.VAN-nal és (ha a verziód engedi) XKERES-sel.
- Írd ki a kódhoz tartozó termék nevét is. Melyik módszerrel nem megy, és miért?
- Szúrj be egy új oszlopot a Kód és az Ár közé. Melyik képlet ad továbbra is helyes árat?
- Írj be nem létező kódot. Melyik ad beszédes üzenetet hibaérték helyett?
- Kétirányú keresés: az órarendben INDEX-szel és két HOL.VAN-nal írd ki a kiválasztott nap kiválasztott óráját.
- Határtábla: a 10.3 pontszámlistáján a jegyet INDEX + HOL.VAN (1-es egyezés) és XKERES (−1-es illesztés) képlettel is számold ki. Ugyanazt adják, mint a közelítő FKERES?
Hibavadászat
🐞 1. Peti szomszéd terméke
Peti képlete: =INDEX($A$3:$A$21;HOL.VAN(H1;$B$2:$B$20;0)). Minden kódhoz a következő sor termékének nevét kapja.
Mi a megoldás?
A két tartomány nem ugyanabban a sorban kezdődik (1. rész, jóslás). Peti igazítsa össze: =INDEX($A$2:$A$20;HOL.VAN(H1;$B$2:$B$20;0)). Ellenőrzésként kattintson a képletre: a szerkesztőlécen a két színes keret ugyanabban a sorban kezdődik és végződik.
🐞 2. Lili #NÉV? hibája
Lili az iskolai gépen megnyitotta az otthon készített fájlját. Minden XKERES-es cellában #NÉV? áll.
Mit tegyen?
Az iskolai gépen régebbi Excel fut (például 2016 vagy 2019), amely nem ismeri az XKERES függvényt (3. rész). Lili írja át a képleteket INDEX + HOL.VAN-ra vagy FKERES-re, amelyek minden verzióban működnek. Tanulság: ha egy fájlt más gépen is használni fognak (vagy vizsgán), előre derítsd ki, milyen verzió fut rajta.
Összefoglalás
- =INDEX(visszaadott_oszlop;HOL.VAN(keresett;kulcsoszlop;0)): bármelyik irányban keres.
- A két tartomány ugyanabban a sorban kezdődjön, ugyanolyan hosszú legyen.
- Kétirányú keresés: INDEX(tábla;HOL.VAN(sor);HOL.VAN(oszlop)); közelítő: HOL.VAN(…;1).
- XKERES: pontos egyezés alapból, üzenet hiányzó találatra, −1-es illesztés sávokhoz; csak Excel 2021-től.
- Válassz a helyzet szerint: régi fájl, vizsgagép, irány, karbantarthatóság.
Ellenőrizd magad!
Miért tud az INDEX + HOL.VAN balra keresni?
Mert a kulcsoszlop és a visszaadott oszlop két külön tartomány, bármelyik oldalon lehetnek.
Mi az XKERES alapértelmezett illesztési módja?
Pontos egyezés (0).
Melyik módszer működik biztosan egy Excel 2016-os gépen: XKERES vagy INDEX + HOL.VAN?
Az INDEX + HOL.VAN.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények, egymásba ágyazott függvények), digitális eszközök tudatos választása (szoftververziók). Az NKP-okostankönyvre épülő emelt szóbeli tétel keresőfüggvényes elemét („=INDEX…HOL.VAN vagy FKERES vagy XKERES”) teljes egészében lefedi.
Módszertan
A lecke a 10.5 és a 10.6 két „fél” függvényét egy egésszé rakja össze, és mondatba foglalva teszi érthetővé. A jóslás a leggyakoribb hibát (eltérő kezdősorú tartományok) mutatja meg. Az XKERES a Microsoft hivatalos magyar súgója szerinti argumentumnevekkel és illesztési módokkal szerepel, a verziófüggőség hangsúlyozásával. A „három megoldás egy feladatra” kihívás és az összevető táblázat a módszerválasztás tudatosságát fejleszti.
Időterv
A kereső páros és jóslás: 10 perc. Kétirányú és közelítő keresés: 7 perc. Az XKERES: 9 perc. Melyiket válaszd: 3 perc. Kihívás: 12 perc. Hibavadászat: 4 perc.
Következő lecke: 10.8 Hiányzó találatok és hibák kezelése
#HIÁNYZIK helyett beszédes üzenet, és a hiányzó találatok okainak felderítése.
