Tipp: Próbálkozzon a jelen cikkben ismertetett függvények új XOOKUP és XMATCH függvényének, továbbfejlesztett verzióinak használatával. Ezek az új függvények bármilyen irányban működnek, és alapértelmezés szerint pontos egyezéseket ad vissza, megkönnyítve és kényelmesebben használhatók, mint a megelőzőjük.

Tegyük fel, hogy van egy iroda helyszámai listája, és meg kell tudni, hogy mely alkalmazottak vannak az egyes irodában. A számolótábla nagyon nagy, ezért nehéz feladatnak gondolhatja. A keresési függvényekkel ez valóban nagyon egyszerű.

Az FKERES és a VKERES függvény az INDEX és a SZÖVEG.VAN függvényekkel együtt az Excel egyik leghasznosabb függvénye.

Megjegyzés: A Keresés varázsló funkció már nem érhető el az Excelben.

Az alábbi példa bemutatja az FKERES használatát.

=FKERES(B2;C2:E7;3;IGAZ)

Ebben a példában a B2 az első argumentum az adatok olyan eleme, amely a függvény működéséhez szükséges. Az FKERES függvényben az első argumentum a keresni kívánt érték. Ez az argumentum lehet egy cellahivatkozás vagy egy rögzített érték, például "Kovács" vagy 21 000. A második argumentum a C2-:E7 cellatartomány, amelyben a keresett értéket keresi. A harmadik argumentum a cellatartománynak azt az oszlopát jelenti, amely a keresni kívánt értéket tartalmazza.

A negyedik argumentumot nem kötelező megadni. Írja be az IGAZ vagy a HAMIS értéket. Ha IGAZ értéket ad meg, vagy üresen hagyja az argumentumot, akkor a függvény az első argumentumként megadott értékhez közelítő találatot ad eredményül. Ha a HAMIS értéket adja meg, a függvény meg fog egyezni az első argumentum által megadott értékkel. Más szóval a negyedik argumentum üresen hagyása – vagy az IGAZ érték megadása – nagyobb rugalmasságot biztosít.

Az alábbi példa bemutatja, hogyan működik a függvény. Amikor beír egy értéket a B2 cellába (első argumentum), az FKERES keresést keres a C2:E7 tartományban (2. argumentum), és visszaadja a legközelebb eső találatot a tartomány harmadik oszlopából, az E oszlopból (3. argumentum).

Az FKERES függvény jellemző használata

A negyedik argumentum üres, ezért a függvény közelítő egyezést ad eredményül. Ha nem adna eredményt, akkor a C vagy D oszlop értékeinek egyikét kell megadnia, hogy a keresésnek egyáltalán legyen eredménye.

Ha már jól tudja használni az FKERES függvényt, a VKERES függvény használata is ugyanilyen egyszerű. Ugyanezeket az argumentumokat kell megadnia, de az oszlopok helyett sorokban keres.

Az INDEX és a MATCH használata az FKERES helyett

Az FKERES függvény használata bizonyos korlátozások miatt csak balról jobbra tud értékeket keresni. Ez azt jelenti, hogy a kinézni képes értéket tartalmazó oszlopnak mindig a visszatérési értéket tartalmazó oszloptól balra kell lennie. Ha a számolótábla nem ilyen módon van felépítve, akkor ne használja az FKERES függvényt. Ehelyett használja az INDEX és a VAN függvény kombinációját.

Az alábbi példa egy kis listát mutat be, amelyben a keresett érték nem a bal szélső oszlopban található. Ezért az FKERES nem használható. Ehelyett a HOL.VAN függvényt használva megkeresjük Chicago tartományát a B1:B11 tartományban. A 4. sorban található. Ezután az INDEX függvény ezt az értéket használja keresési argumentumként, és megkeresi Chicago népességét a 4. oszlopban (D oszlop). A használt képlet az A14 cellában látható.

Érték megkeresése az INDEX és a HOL.VAN függvénnyel

Az INDEX és a MATCH függvény FKERES helyett való használatára további példákat Bill Jelen, Microsoft MVP https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ olvashat.

Tegyen egy próbát!

Ha a saját adataival való kipróbálás előtt kísérletezni szeretne a keresési függvényekkel, az itt néhány mintaadatot is kipróbálhatja.

Példa az FKERES munka közben

Másolja az alábbi adatokat egy üres számolótáblába.

Tipp: Mielőtt beilleszti az adatokat az Excelbe, állítsa az A és a C oszlop szélességét 250 képpontra, majd kattintson a Sortöréssel több sortöréssel (Kezdőlaplap, Igazítás csoport).

Sűrűség

Viszkozitás

Hőmérséklet

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Képlet

Leírás

Eredmény

=FKERES(1;A2:C10;2)

Közelítő egyezést használva megkeresi az A oszlopban az 1 értéket, majd ugyanebben az oszlopban az egynél nem nagyobb legnagyobb értéket (0,946), és ugyanebben a sorban megjeleníti a B oszlopbeli értéket.

2,17

=FKERES(1;A2:C10;3;IGAZ)

Közelítő egyezést használva megkeresi az A oszlopban az 1 értéket, majd ugyanebben az oszlopban az egynél nem nagyobb legnagyobb értéket (0,946), és ugyanebben a sorban megjeleníti a C oszlopbeli értéket.

100

=FKERES(0,7;A2:C10;3;HAMIS)

Pontos egyezést használva megkeresi a 0,7 értéket az A oszlopban. Mivel ez az oszlop nem tartalmaz pontos egyezést, a művelet hibát ad vissza.

#HIÁNYZIK

=FKERES(0,1;A2:C10;2;IGAZ)

Közelítő egyezést használva megkeresi a 0,1 értéket az A oszlopban. Mivel ez az érték kisebb az A oszlop legkisebb értékénél, a művelet hibát ad vissza.

#HIÁNYZIK

=FKERES(2;A2:C10;2;IGAZ)

Közelítő egyezést használva megkeresi az A oszlopban a 2 értéket, majd ugyanebben az oszlopban a kettőnél nem nagyobb legnagyobb értéket (1,29), és ugyanebben a sorban megjeleníti a B oszlopbeli értéket.

1,71

Példa VKERES függvényre

Másolja a táblázat összes celláját a vágólapra, és illessze be egy üres Excel-munkalapra az A1 cellába.

Tipp: Mielőtt beilleszti az adatokat az Excelbe, állítsa az A és a C oszlop szélességét 250 képpontra, majd kattintson a Sortöréssel több sortöréssel (Kezdőlaplap, Igazítás csoport).

Tengelyek

Csapágyak

Csapszegek

4

4

9

5

7

10

6

8

11

Képlet

Leírás

Eredmény

=VKERES("Tengelyek";A1:C4;2;IGAZ)

A „Tengelyek” szó megkeresése az 1. sorban, majd az ugyanazon oszlop (A oszlop) 2. sorában lévő érték visszaadása

4

=VKERES("Csapágyak";A1:C4;3;HAMIS)

A „Csapágyak” szó megkeresése az 1. sorban, majd az ugyanazon oszlop (B oszlop) 3. sorában lévő érték visszaadása

7

=VKERES("D";A1:C4;3;IGAZ)

A "B" szó kiása az 1. sorban, és az ugyanazon oszlop 3. sorában lévő érték visszaadása Mivel nem található pontos egyezés a "B" értékhez, az 1. sorban a "B" értéknél kisebb legnagyobb értéket használja a képlet: "Tengelyek", az A oszlopban.

5

=VKERES("Csapszegek";A1:C4;4)

A „Csapszegek” szó megkeresése az 1. sorban, majd az ugyanazon oszlop (C oszlop) 4. sorában lévő érték visszaadása

11

=VKERES(3; {1;2;3;"a";"b";"c";"d";"e";"f"};2;IGAZ)

A 3-as szám kiása a háromsoros tömbállandóban, és ugyanazon oszlop (ebben az esetben a harmadik) 2. sorában lévő érték visszaadása. A tömbállandóban három értéksor van, mindegyik sor pontosvesszővel (pontosvesszővel ;). Mivel a "c" megtalálható a 2. sorban és ugyanabban az oszlopban, mint a 3, a visszaadott érték "c".

c

Példák az INDEX és a MATCH használatára

Ez az utolsó példa az INDEX és a MATCH függvényt együtt alkalmazza a legrégebbi számlaszám és a hozzá tartozó dátum visszaadott értékére mind az öt városra. Mivel a dátum számként ad vissza, a SZÖVEG függvény használatával dátumként formázza azt. Az INDEX függvény a HOL.VAN függvény eredményét használja argumentumként. Az INDEX és a HOL.VAN függvény kombinációját mindegyik képletben kétszer használjuk: először a számlaszám megjelenítéséhez, másodszor pedig a dátum kiszámításához.

Másolja a táblázat összes celláját a vágólapra, és illessze be egy üres Excel-munkalapra az A1 cellába.

Tipp: Mielőtt beilleszti az adatokat az Excelbe, állítsa az A–D oszlop szélességét 250 képpontra, majd kattintson a Sortöréssel több sortöréssel (Kezdőlaplap, Igazítás csoport).

Számla

Település

Számla dátuma

Legrégebbi számla település szerint, dátummal

3115

Ajka

2012. április 7.

="Ajka = "&INDEX($A$2:$C$33;HOL.VAN("Ajka";$B$2:$B$33;0);1)& "; Számla dátuma: " & SZÖVEG(INDEX($A$2:$C$33;HOL.VAN("Ajka";$B$2:$B$33;0);3);"yyyy/mm/d")

3137

Ajka

2012. április 9.

="Kőszeg = "&INDEX($A$2:$C$33;HOL.VAN("Kőszeg";$B$2:$B$33;0);1)& "; Számla dátuma: " & SZÖVEG(INDEX($A$2:$C$33;HOL.VAN("Kőszeg";$B$2:$B$33;0);3);"yyyy/mm/d")

3154

Ajka

2012. április 11.

="Hatvan = "&INDEX($A$2:$C$33;HOL.VAN("Hatvan";$B$2:$B$33;0);1)& "; Számla dátuma: " & SZÖVEG(INDEX($A$2:$C$33;HOL.VAN("Hatvan";$B$2:$B$33;0);3);"yyyy/mm/d")

3191

Ajka

2012. április 21.

="Tiszacsege = "&INDEX($A$2:$C$33;HOL.VAN("Tiszacsege";$B$2:$B$33;0);1)& "; Számla dátuma: " & SZÖVEG(INDEX($A$2:$C$33;HOL.VAN("Tiszacsege";$B$2:$B$33;0);3);"yyyy/mm/d")

3293

Ajka

2012. április 25.

="Gyula = "&INDEX($A$2:$C$33;HOL.VAN("Gyula";$B$2:$B$33;0);1)& "; Számla dátuma: " & SZÖVEG(INDEX($A$2:$C$33;HOL.VAN("Gyula";$B$2:$B$33;0);3);"yyyy/mm/d")

3331

Ajka

2012. április 27.

3350

Ajka

2012. április 28.

3390

Ajka

2012. május 1.

3441

Ajka

2012. május 2.

3517

Ajka

2012. május 8.

3124

Kőszeg

2012. április 9.

3155

Kőszeg

2012. április 11.

3177

Kőszeg

2012. április 19.

3357

Kőszeg

2012. április 28.

3492

Kőszeg

2012. május 6.

3316

Hatvan

2012. április 25.

3346

Hatvan

2012. április 28.

3372

Hatvan

2012. május 1.

3414

Hatvan

2012. május 1.

3451

Hatvan

2012. május 2.

3467

Hatvan

2012. május 2.

3474

Hatvan

2012. május 4.

3490

Hatvan

2012. május 5.

3503

Hatvan

2012. május 8.

3151

Tiszacsege

2012. április 9.

3438

Tiszacsege

2012. május 2.

3471

Tiszacsege

2012. május 4.

3160

Gyula

2012. április 18.

3328

Gyula

2012. április 26.

3368

Gyula

2012. április 29.

3420

Gyula

2012. május 1.

3501

Gyula

2012. május 6.

Rövid összefoglalás: Az FKERES

(referencia)

FKERES függvényben

További segítségre van szüksége?

Ismeretek bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Csatlakozás Microsoft Office Insiderekhez

Hasznos volt az információ?

Mennyire elégedett a fordítás minőségével?
Mi befolyásolta a felhasználói élményét?

Köszönjük a visszajelzését!

×