Ez a témakör a függvény hibás eredményének leggyakoribb FKERES okait ismerteti, és javaslatokat nyújt az INDEX és a HOL.VAN függvény használatára.
Tipp: Emellett tekintse meg a Rövid összefoglalás: Hibaelhárítási tippek az FKERES függvényhez című szakaszt, amely egy kényelmes PDF-fájlban írja le a #HIÁNYZIK problémák gyakori okait. A PDF-et megoszthatja másokkal, vagy kinyomtathatja saját használatra.
Probléma: A keresési érték nem az első oszlopban szerepel a tábla argumentumban
Az FKERES függvény egyik korlátozása, hogy csak a táblázattömb bal szélső oszlopában képes értékeket keresni. Ha a keresett érték nem a tömb első oszlopában szerepel, #HIÁNYZIK hiba fog megjelenni.
Az alábbi táblázatban azt szeretnénk kideríteni, hogy hány egységnyi kelkáposzta lett értékesítve.
A #HIÁNYZIK hiba azért jelenik meg, mert a „Kelkáposzta” keresési érték jelenik meg az A2:C10 table_array argumentum második oszlopában (Termény). Ebben az esetben az Excel az A oszlopban keresi, nem a B oszlopban.
Megoldás: Ennek kijavításához módosítsa az FKERES függvényt úgy, hogy az a megfelelő oszlopra hivatkozzon. Ha ez nem lehetséges, akkor próbálkozzon az oszlopok áthelyezésével. Ez azonban nagyon nem praktikus az olyan nagy méretű vagy összetett számolótáblák esetén, amelyekben a cellaértékek más számítások eredményei, illetve más logikus okai is lehetnek annak, hogy nem érdemes egyszerűen áthelyeznie az oszlopokat. A megoldás az INDEX és a HOL.VAN függvény kombinációjának használata, amivel a keresési táblában elfoglalt helyzetétől függetlenül lehet keresni egy oszlopban. Lásd a következő szakaszt.
Érdemes lehet inkább az INDEX/HOL.VAN függvényt használni
Az INDEX és a HOL.VAN számos olyan esetben jó választás, ha az FKERES függvény nem felel meg az igényeinek. Az INDEX/HOL.VAN függvény legnagyobb előnye, hogy a keresési táblában található oszlopok bármelyikében kereshet értékeket. Az INDEX egy megadott táblából/tartományból ad vissza értéket – a pozíciója alapján. A HOL.VAN egy táblázatban/tartományban lévő érték relatív pozícióját adja vissza. Az INDEX és a HOL.VAN függvényt egy képletben együtt használva úgy kereshet meg egy értéket egy táblázatban/tömbben, hogy megadja az érték relatív pozícióját a táblázatban/tömbben.
Számos előnnyel jár, ha az INDEX/HOL.VAN függvényt használja az FKERES helyett:
-
Az INDEX és a HOL.VAN függvény esetén a visszatérési értéknek nem kell a keresési oszlopban lennie. Ez eltér az FKERES függvénytől, amelyben a visszatérési értéknek a megadott tartományban kell lennie. Miért fontos mindez? Az FKERES függvény használata esetén ismernie kell a visszatérési értéket tartalmazó oszlop számát. Habár ez nem tűnik kihívásnak, kényelmetlen lehet, ha nagy méretű táblázatot kezel, és meg kell számolnia az oszlopokat. Ha pedig oszlopot szeretne hozzáadni/eltávolítani a táblázatban, ismét meg kell számolnia az oszlopokat, és frissítenie kell az oszlop_szám argumentumot. Az INDEX és a HOL.VAN függvénnyel nincs szükség számolásra, mivel a keresőoszlop különbözik attól az oszloptól, amelyben a visszatérési érték található.
-
Az INDEX és a HOL.VAN függvény esetén egy tömb sorát vagy oszlopát, vagy akár mindkettőt megadhatja. Ez azt jelenti, hogy függőlegesen és vízszintesen egyaránt képes értékeket keresni.
-
Az INDEX és a HOL.VAN függvénnyel bármely oszlopban kereshetők értékek. Az FKERES függvénytől eltérően – amelyben csak a táblázat első oszlopában lehet értéket keresni –, az INDEX és a HOL.VAN függvénnyel akkor is működik a keresés, ha a keresési érték az első vagy az utolsó oszlopban, illetve valahol a kettő között található.
-
Az INDEX és a HOL.VAN függvény rugalmasságot biztosít a tekintetben, hogy használatukkal dinamikusan hivatkozhat arra az oszlopra, amely a visszatérési értéket tartalmazza.Ez azt jelenti, hogy az INDEX és a HOL.VAN függvény megsértése nélkül adhat oszlopokat a táblázathoz. Ezzel ellentétben az FKERES függvény megsérül abban az esetben, ha oszlopot vesz fel a táblázatba – mivel a függvény statikusan hivatkozik a táblázatra.
-
Az INDEX és a HOL.VAN függvény nagyobb rugalmasságot biztosít a találatokat illetően.Az INDEX és a HOL.VAN függvénnyel pontos egyezésre, illetve a keresési értéknél nagyobb vagy kisebb értékre is kereshet. Az FKERES csupán a legközelebbi egyezésre (alapértelmezett beállítás) vagy pontos értékre keres. Az FKERES ezenkívül alapértelmezés szerint azt feltételezi, hogy a táblázattömb első oszlopa betűrendbe van rendezve, ha pedig a táblázat nem így lett létrehozva, az FKERES a táblázatban talált első legközelebb eső egyezést adja vissza, amely nem feltétlenül az Ön által keresett adat.
Szintaxis
Ha az INDEX/HOL.VAN függvényhez szintaxist szeretne létrehozni, a tömb/hivatkozás argumentumot kell használnia az INDEX függvényből, és azon belül be kell ágyaznia a HOL.VAN függvény szintaxisát. Ez a következő formában jelenik meg:
=INDEX(tömb vagy hivatkozás; HOL.VAN(keresési_érték;tábla;[egyezés_típusa])
A fenti példában az FKERES függvény helyett használhatja az INDEX/HOL.VAN függvényt. A szintaxis a következőképpen néz ki:
=INDEX(C2:C10;HOL.VAN(B13;B2:B10;0))
Szavakkal megfogalmazva ez a következőt jelenti:
=INDEX(az a visszatérési érték a C2:C10 tartományból, amely a következővel egyezik: HOL.VAN(Kelkáposzta, amely valahol a B2:B10 tömbben található, amelyben a visszatérési érték az első érték, amely megfelel a Kelkáposzta értéknek))
A képlet a C2:C10 tömbben azt az első értéket keresi, amely megfelel a Kelkáposzta értéknek (a B7 cellában), és azt az értéket adja vissza a C7 cellában (100), amely elsőként egyezik a Kelkáposzta értékkel.
Probléma: Nem található pontos egyezés
Ha a tartományban_keres argumentum HAMIS – és az FKERES függvény nem tud pontos egyezést találni az adatokban –, a #HIÁNYZIK hibát adja vissza.
Megoldás: Ha biztos abban, hogy a releváns adat szerepel a számolótáblában, de az FKERES függvény mégse találja azt, szánjon időt annak ellenőrzésére, hogy a hivatkozott cellák ne tartalmazzanak rejtett szóközöket vagy nem nyomtatható karaktereket. Ezenkívül bizonyosodjon meg arról is, hogy a cellák a helyes adattípusokat követik. A számokat tartalmazó cellákat például számként, nem pedig szövegként kell formáznia.
Érdemes lehet a TISZTÍT vagy a KIMETSZ függvénnyel meg is tisztítania az adatokat a cellákban.
Probléma: A keresési érték kisebb a tömb legkisebb értékénél
Ha a tartományban_keres argumentum értéke IGAZ – és a keresési érték kisebb a tömb legkisebb értékénél –, megjelenik a #HIÁNYZIK hiba. Az IGAZ értékű argumentum közelítő egyezést keres a tömbben, és a keresési értéknél kisebb legközelebbi értéket adja vissza.
A következő példában a keresési érték 100, a B2:C10 tartományban azonban nincs 100-nál kisebb érték, ezért jelenik meg a hiba.
Megoldás:
-
Végezze el a szükséges javítást a keresési értéken.
-
Ha nem tudja módosítani a keresési értéket, és nagyobb rugalmasságra van szüksége az egyező értékeket illetően, vegye fontolóra az INDEX/HOL.VAN függvény használatát az FKERES helyett – lásd a cikkben feljebb. Az INDEX/HOL.VAN függvénnyel a keresési értéknél nagyobb vagy kisebb, illetve azzal egyenlő értékek között is kereshet. Az INDEX/HOL.VAN függvény FKERES függvény helyett történő használatáról a jelen témakör előző szakaszában olvashat bővebben.
Probléma: A keresőoszlop nem növekvő sorrendbe van rendezve
Ha a tartományban_keres argumentum értéke IGAZ – és a keresőoszlopok egyike nem növekvő (A–Z) sorrendbe van rendezve –, megjelenik a #HIÁNYZIK hiba.
Megoldás:
-
Módosítsa az FKERES függvényt úgy, hogy pontos egyezésre keressen. Ehhez a tartományban_keres argumentumnak adja a HAMIS értéket. HAMIS érték esetén nincs szükség rendezésre.
-
A rendezetlen táblázatokban az INDEX/HOL.VAN függvény használatával kereshet értéket.
Probléma: Az érték egy nagy méretű lebegőpontos szám.
Ha a cellák időértékeket vagy nagyméretű tizedes törteket tartalmaznak, az Excel a lebegőpontos számok pontossága miatt a #HIÁNYZIK! hibaértéket adja eredményül. A lebegőpontos számok a tizedesvessző után sorakozó számok. (Az Excel az időértékeket lebegőpontos számokként tárolja.) Az Excel a nagyon nagyméretű lebegőpontos számokat nem tudja tárolni, ezért a függvény helyes működéséhez a lebegőpontos számokat 5 tizedesjegyre kell kerekíteni.
Megoldás: Rövidítse le a számokat úgy, hogy felfelé kerekíti őket öt tizedeshelyig a KEREKÍTÉS függvénnyel.
További segítségre van szüksége?
Kérdéseivel mindig felkeresheti az Excel technikai közösség egyik szakértőjét, vagy segítséget kérhet a közösségekben.
