Excel táblázatkezelés kezdőknek › 10.5 lecke
Excel tanfolyam · 10.5 lecke
HOL.VAN: egy adat helyének megkeresése
Az FKERES egy értéket ad vissza. Néha viszont nem maga az érték kell, hanem a helye: hányadik a rangsorban Tóth Lili? Szerepel-e egyáltalán egy kód az árlistában? Hányadik oszlopban van az „Ár” fejléc? A HOL.VAN pontosan ezt mondja meg: egy szám, hogy a keresett érték hányadik a sorban vagy oszlopban. Önmagában is hasznos, de igazi ereje a következő leckékben bontakozik ki, az INDEX-szel párban.
- ⏱ kb. 45 perc
- 🎓 haladóbb
- 🎒 10. osztálytól
A lecke végére
- megkeresed egy érték helyét egy sorban vagy oszlopban;
- helyesen választod meg az egyezés típusát;
- tudod, hogy a pozíció a tartományhoz képest értendő;
- megvizsgálod, létezik-e egy érték;
- a HOL.VAN-nal kiszámolod az FKERES oszlopszámát.
Amit már tudsz
A HOL.VAN
HOL.VAN(keresési_érték;tábla;[egyezés_típus]): megkeresi az értéket egy egysoros vagy egyoszlopos tartományban, és visszaadja, hányadik helyen van.
| Képlet | Eredmény | Jelentés |
|---|---|---|
=HOL.VAN("Tóth Lili";B2:B26;0) | 3 | a lista 3. eleme (a B4 cellában) |
=HOL.VAN("Ár";A1:D1;0) | 3 | a fejléc 3. cellája (C1) |
=HOL.VAN("C09";A2:A20;0) | #HIÁNYZIK | nincs ilyen kód |
🔮 Jósold meg!
A névsor a B2:B26 tartományban van, Tóth Lili a B4 cellában. Mit ad (a) =HOL.VAN("Tóth Lili";B2:B26;0) (b) =HOL.VAN("Tóth Lili";B1:B26;0) (c) =HOL.VAN("Tóth Lili";B2:B26), ha a névsor nincs ábécérendben?
Megoldás
(a) 3: a B2 az 1., a B3 a 2., a B4 a 3. hely. (b) 4: a tartomány most a B1-től indul, így ugyanaz a cella a 4. A pozíció mindig a tartomány elejéhez képest értendő, nem a munkalap sorszáma. (c) Kiszámíthatatlan, hibaüzenet nélkül: a harmadik argumentum elhagyásakor az alapérték 1 (közelítő egyezés), amely rendezett listát feltételez (10.3). Pontos kereséshez mindig írd ki a 0-t.
Az egyezés típusa
| Típus | Jelentés | Feltétel |
|---|---|---|
| 0 | pontos egyezés | bármilyen sorrend; helyettesítő karakterek is használhatók (9.6) |
| 1 (vagy elhagyva) | a legnagyobb érték, amely kisebb vagy egyenlő a keresettel | növekvő sorrend (mint a közelítő FKERES) |
| −1 | a legkisebb érték, amely nagyobb vagy egyenlő a keresettel | csökkenő sorrend |
💡 Közelítő HOL.VAN a határtáblán
A 10.3 határtáblájának alsó határai (0, 25, 40, 55, 70) a H2:H6-ban: =HOL.VAN(B2;$H$2:$H$6;1) a 63 pontra 4-et ad, vagyis a 4. sávot. Ugyanez a „sávszám” a jegy is. A HOL.VAN tehát a sávot, az INDEX (10.6) pedig a sávhoz tartozó bármilyen adatot adhatja.
Mire jó?
| Feladat | Képlet |
|---|---|
| Hányadik a rangsorban? (a lista pontszám szerint rendezve, 7.2) | =HOL.VAN($H$1;$B$2:$B$26;0) |
| Szerepel-e a kód az árlistában? | =SZÁM(HOL.VAN(A2;Árlista!$A$2:$A$20;0)) → IGAZ/HAMIS (a HOL.VAN számot vagy hibát ad) |
| Szerepel-e, szövegesen | =HA(SZÁM(HOL.VAN(A2;Árlista!$A$2:$A$20;0));"van";"nincs") |
| Hányadik oszlop az „Ár”? (dinamikus FKERES) | =FKERES(A2;Árlista!$A$2:$D$20;HOL.VAN("Ár";Árlista!$A$1:$D$1;0);HAMIS) |
✅ Dinamikus oszlopszám
A 10.2-ben láttuk: ha az árlistába egy új oszlopot szúrsz be, a beírt oszlopszám elcsúszik. Ha az oszlopszámot a HOL.VAN számolja ki a fejlécből, a képlet mindig az „Ár” feliratú oszlopot találja meg, akárhová kerül. Ez az ötlet vezet el az INDEX + HOL.VAN párosig (10.7).
Kihívás: helyezések és ellenőrzések
🏆 1. Hányadik lettem?
- Vedd elő a sportnapi (5.8) vagy a versenyeredmény-listát (8.9), és rendezd az eredmény szerint csökkenő sorrendbe (7.2).
- Egy keresőcellába (legördülő lista a nevekből, 9.5) írd be egy versenyző nevét, és HOL.VAN-nal írd ki a helyezését.
- Írd ki mondatban is: „Tóth Lili a 3. helyen végzett.” (& jel, 4.1.)
- Mi történik, ha a listát átrendezed név szerint? Miért nem jó ilyenkor a HOL.VAN helyezésnek? (Kitekintés: a SORSZÁM függvény rendezés nélkül is helyezést ad.)
🔎 2. Kódellenőrzés és dinamikus oszlop
- A 10.2 rendelésében egy új oszlopban ellenőrizd minden kódról, hogy szerepel-e az árlistában („van” / „nincs”).
- Írd át az ár-FKERES-t úgy, hogy az oszlopszámot HOL.VAN számolja az árlista fejlécéből.
- Szúrj be egy új oszlopot az árlistába a Termék és az Ár közé (például „Kategória”): az új képlet még mindig az árat adja? És a régi, beírt oszlopszámos?
Hibavadászat
🐞 1. Peti furcsa helyezése
Peti képlete: =HOL.VAN("Kiss Anna";B2:B26). A névsor nincs rendezve, és Kiss Anna helyezése 17 lett, pedig a listában a 2. helyen áll.
Mi a megoldás?
A harmadik argumentum elmaradt, ezért közelítő egyezés történt (alapérték: 1), amely rendezetlen listán értelmetlen eredményt ad (1. rész). Pontos kereséshez mindig: =HOL.VAN("Kiss Anna";B2:B26;0).
🐞 2. Lili „rossz” sorszáma
Lili a =HOL.VAN("C05";A5:A30;0) eredményét (3) a munkalap sorszámának hitte, és a 3. sorban kereste a terméket. Ott a fejléc van.
Mit tegyen?
A HOL.VAN a tartományon belüli pozíciót adja: a 3 az A5:A30 harmadik cellája, vagyis az A7 (1. rész). Ha a munkalap sorszáma kell, add hozzá az eltolást: =HOL.VAN(…)+4, de általában nincs rá szükség: az INDEX (10.6) épp a pozíciót várja.
Összefoglalás
- =HOL.VAN(érték;tartomány;0): az érték pozíciója egy sorban vagy oszlopban.
- A pozíció a tartomány első cellájától számít.
- Egyezés: 0 = pontos (mindig írd ki!), 1 = növekvő közelítő, −1 = csökkenő közelítő.
- Ha nem találja: #HIÁNYZIK; létezés: =SZÁM(HOL.VAN(…;0)).
- Dinamikus oszlopszám az FKERES-hez a fejléc alapján.
Ellenőrizd magad!
Mit ad a =HOL.VAN("kedd";B1:F1;0), ha B1:F1 = hétfő…péntek?
2
Mi az egyezéstípus alapértéke?
1 (közelítő, növekvő sorrendet feltételez).
Hogyan vizsgálod meg, szerepel-e egy érték egy listában?
=SZÁM(HOL.VAN(érték;lista;0))
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények); kapcsolódás a programozáshoz (keresés típusalgoritmus, az eredmény az elem indexe; eldöntés). Az NKP-okostankönyvre épülő emelt szóbeli tétel HOL.VAN és INDEX…HOL.VAN elemének első része.
Módszertan
A lecke a HOL.VAN-t a programozás „keresés: hányadik elem?” típusalgoritmusához kapcsolja, és kiemeli a pozíció relatív voltát. A jóslás egyszerre három gyakori hibát mutat meg (tartomány eleje, elhagyott egyezéstípus). A dinamikus oszlopszám a 10.2 korlátjára ad választ, és motiválja az INDEX + HOL.VAN párost.
Időterv
A HOL.VAN és jóslás: 11 perc. Az egyezés típusa: 6 perc. Mire jó: 8 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.
Következő lecke: 10.6 INDEX: érték kiválasztása hely alapján
A HOL.VAN fordítottja: ha tudod a helyet, az INDEX megmondja, mi van ott.
