Tipp: Próbálja ki az új XKERES és XMATCH függvényeket, a cikkben ismertetett függvények továbbfejlesztett verzióit. Ezek az új függvények bármilyen irányban működnek, és alapértelmezés szerint pontos egyezéseket ad vissza, így egyszerűbben és kényelmesebben használhatók, mint elődjeik.
Tegyük fel, hogy rendelkezik az irodai helyszámok listájával, és tudnia kell, hogy mely alkalmazottak vannak az egyes irodákban. A számolótábla hatalmas, ezért azt hiheti, hogy ez kihívást jelentő feladat. Ez valójában elég egyszerű egy keresési függvényhez.
Az FKERES és az FKERES függvény, valamint az INDEX és a HOL.VAN függvény az Excel néhány leg-hasznosabb függvénye.
Megjegyzés: A Keresés varázsló funkció már nem érhető el az Excelben.
Íme egy példa az FKERES függvény használatára.
=FKERES(B2;C2:E7;3;IGAZ)
Ebben a példában a B2 az első argumentum – az adatok azon eleme, amelyet a függvénynek működnie kell. Az FKERES függvény esetében ez az első argumentum a keresendő érték. Ez az argumentum lehet cellahivatkozás, vagy 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ékre kell keresni. A harmadik argumentum a cellatartomány azon oszlopa, amely a keresett értéket tartalmazza.
A negyedik argumentumot nem kötelező megadni. Adja meg 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 HAMIS értéket ad meg, a függvény az első argumentum által megadott értékkel fog megegyezni. 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 értéket ad meg a B2 cellában (az első argumentumban), az FKERES függvény a C2:E7 tartomány celláiban keres (2. argumentum), és a tartomány harmadik oszlopának legközelebbi közelítő egyezését adja vissza, az E oszlopot (3. argumentum).
A negyedik argumentum üres, ezért a függvény hozzávetőleges egyezést ad vissza. 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 jól ismeri az FKERES függvényt, a HKERES függvény használata is ugyanilyen egyszerű. Ugyanazokat az argumentumokat adja meg, de az oszlopok helyett sorokban keres.
INDEX és HOL.VAN függvény használata az FKERES helyett
Bizonyos korlátozások vonatkoznak az FKERES függvény használatára – az FKERES függvény csak balról jobbra tud értékeket keresni. Ez azt jelenti, hogy a keresendő értéket tartalmazó oszlopnak mindig a visszatérési értéket tartalmazó oszlop bal oldalán kell lennie. Ha a számolótábla nem így van felépítve, akkor ne használja az FKERES függvényt. Használja inkább az INDEX és a HOL.VAN függvény kombinációját.
Ebben a példában egy kis lista látható, ahol a keresendő érték( Chicago) nem a bal szélső oszlopban van. Ezért nem használhatjuk az FKERES függvényt. Ehelyett a HOL.VAN függvénnyel fogjuk megkeresni Chicago-t a B1:B11 tartományban. A 4. sorban található. Ezután az INDEX ezt az értéket használja keresési argumentumként, és megkeresi a Békéscsaba lakosságát a 4. oszlopban (D oszlop). A használt képlet az A14 cellában látható.
Az INDEX és a HOL.VAN 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/ című cikkben talál.
Tegyen egy próbát!
Ha a keresési függvényekkel szeretne kísérletezni, mielőtt kipróbálná őket a saját adataival, íme néhány mintaadat.
Példa az FKERES függvényre a munkahelyen
Másolja az alábbi adatokat egy üres számolótáblába.
Tipp: Mielőtt beillesztené az adatokat az Excelbe, állítsa az A–C oszlop szélességét 250 képpontra, majd kattintson a Szöveg körbefuttatása gombra (Kezdőlap lap , 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 az FKERES 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 beillesztené az adatokat az Excelbe, állítsa az A–C oszlop szélességét 250 képpontra, majd kattintson a Szöveg körbefuttatása gombra (Kezdőlap lap , 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) |
Megkeresi a "B" szót az 1. sorban, és visszaadja az ugyanabban az oszlopban lévő 3. sor értékét. Mivel a "B" pontos egyezése nem található, az 1. sor legnagyobb értéke, amely kisebb, mint a "B", a rendszer a "Tengelyek" értéket használja 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) |
Megkeresi a 3-at a háromsoros tömbállandóban, és visszaadja a 2. sor értékét ugyanabban a (ebben az esetben a harmadik) oszlopban. A tömbállandóban három sornyi érték található, mindegyik sor pontosvesszővel (;) elválasztva. Mivel a "c" a 2. sorban és ugyanabban az oszlopban található, mint a 3, a rendszer a "c" értéket adja vissza. |
c |
INDEX és HOL.VAN példák
Ez az utolsó példa az INDEX és a HOL.VAN függvényt együtt használja a legkorábbi számlaszám és a hozzá tartozó dátum visszaadásához mind az öt város esetében. Mivel a dátumot számként adja vissza, a SZÖVEG függvénnyel dátumként formázzuk. 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 beillesztené az adatokat az Excelbe, állítsa az A–D oszlop szélességét 250 képpontra, majd kattintson a Szöveg körbefuttatása gombra (Kezdőlap lap, 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. |
