Tipp:
Próbálja ki az új XKERES és XHOL.VAN függvényt, a jelen 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 adnak vissza, így könnyebben és kényelmesebben használhatók, mint elődeik.
Tegyük fel, hogy van egy irodaszámokat tartalmazó listája, és szeretné kikeresni, hogy kik dolgoznak az egyes irodákban. A számolótábla hatalmas, ezért úgy tűnhet, hogy kihívást jelentő feladat. Ez valójában elég könnyen elvégezhető egy keresési függvénnyel.
Az FKERES és a VKERES függvény, valamint az INDEX és a HOL.VAN függvény az Excel leghasznosabb függvényei közé tartozik.
Megjegyzés
A Keresés varázsló 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 – egy olyan adatelem, amelyre a függvénynek szüksége van a működéshez. Az FKERES függvény esetén az első argumentum a megkeresni kívánt érték. Ez az argumentum lehet cellahivatkozás vagy rögzített érték, például mint "Lukács" vagy 21 000. A második argumentum az a cellatartomány (C2-:E7), amelyben a keresett értéket keresi. A harmadik argumentum a cellatartománynak azt az oszlopát adja meg, amely a keresett é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 fogja adni az első argumentum által megadott értéket. Más szóval ha nem adja meg a negyedik argumentumot, vagy IGAZ értéket ad meg, akkor rugalmasabban kereshet.
Az alábbi példa bemutatja, hogyan működik a függvény. Amikor megad egy értéket a B2 cellában (első argumentum), az FKERES keres a C2:E7 tartomány celláiban (2. argumentum), majd visszaadja a legközelebb eső találatot a tartomány harmadik oszlopából, az E oszlopból (3. argumentum).
A negyedik argumentum üres, így a függvény hozzávetőleges találatot 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 magabiztosan használja az FKERES függvényt, a VKERES függvény használata is ugyanolyan egyszerű lehet. Ugyanezeket az argumentumokat kell megadnia, de a keresés nem oszlopokban, hanem sorokban történik.
Az INDEX és a HOL.VAN használata az FKERES helyett
Az FKERES függvénynek vannak bizonyos korlátai: az FKERES függvény csak balról jobbra tud értéket keresni. Ez azt jelenti, hogy a keresett értéket tartalmazó oszlopnak mindig a visszatérési értéket tartalmazó oszloptól balra kell lennie. Ha a számolótábla nem így készült, akkor ne használja az FKERES függvényt. Helyettük használja az INDEX és a HOL.VAN függvény kombinációját.
Ez a példa egy kis listát mutat be, amelyben a keresett érték nem a bal szélső oszlopban szerepel. Ezért nem használhatjuk az FKERES függvényt. Helyette a HOL.VAN függvénnyel keressük meg a települést 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 található.
Tegyen egy próbát!
Ha ki szeretné próbálni a keresési függvényeket, mielőtt a saját adatain kipróbálná őket, használja az alábbi mintaadatokat.
Példa az FKERES függvényre
Másolja az alábbi adatokat egy üres számolótáblába.
Tipp:
Mielőtt beillesztené az adatokat az Excelben, állítsa az A, a C oszlop szélességét 250 képpontra, és kattintson a Sortöréssel több sorba 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 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 beillesztené az adatokat az Excelben, állítsa az A, a C oszlop szélességét 250 képpontra, és kattintson a Sortöréssel több sorba 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) | A "B" érték keresése az 1. sorban, majd az ugyanazon oszlop 3. sorában lévő érték visszaadása Mivel nem található pontos találat a "D" értékre, a "D"-nél kisebb legnagyobb értéket ("Tengelyek") használja a varázsló 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 érték keresése a háromsoros tömbállandóban, és ugyanazon oszlop (ebben az esetben a harmadik oszlop) 2. sorában lévő érték visszaadása. A tömbállandóban három, egymástól pontosvesszővel (;) elválasztott értéksor található. Mivel a "c" megtalálható a 2. sorban és ugyanabban az oszlopban, mint a 3., a visszaadott érték a "c". | c |
Példák az INDEX és a HOL.VAN függvényre
Ez utóbbi példa az INDEX és a HOL.VAN függvényt együtt alkalmazza, hogy a legkorábbi számlaszámot és a hozzá tartozó dátumot adja vissza mind az öt településhez. Mivel a dátum számként jelenik meg, 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 Excelben, állítsa az A–D oszlop szélességét 250 képpontra, és kattintson a Sortöréssel több sorba gombra (Kezdőlap lap, Igazítás csoport).
| Számla | Város | 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. |
Lásd még
Rövid összefoglaló: FKERES emlékeztető
Keresési és hivatkozási függvények (segédlet)