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 keresés fogalmait (10.1);
  • az FKERES-t és az oszlopszámot (10.2);
  • a közelítő egyezést (10.3);
  • a SZÁM függvényt (7.6).

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épletEredményJelentés
=HOL.VAN("Tóth Lili";B2:B26;0)3a lista 3. eleme (a B4 cellában)
=HOL.VAN("Ár";A1:D1;0)3a fejléc 3. cellája (C1)
=HOL.VAN("C09";A2:A20;0)#HIÁNYZIKnincs 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ípusJelentésFeltétel
0pontos egyezésbármilyen sorrend; helyettesítő karakterek is használhatók (9.6)
1 (vagy elhagyva)a legnagyobb érték, amely kisebb vagy egyenlő a keresettelnövekvő sorrend (mint a közelítő FKERES)
−1a legkisebb érték, amely nagyobb vagy egyenlő a keresettelcsö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ó?

FeladatKé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?

  1. 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).
  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.
  3. Írd ki mondatban is: „Tóth Lili a 3. helyen végzett.” (& jel, 4.1.)
  4. 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

  1. A 10.2 rendelésében egy új oszlopban ellenőrizd minden kódról, hogy szerepel-e az árlistában („van” / „nincs”).
  2. Írd át az ár-FKERES-t úgy, hogy az oszlopszámot HOL.VAN számolja az árlista fejlécéből.
  3. 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.