Bosszantó… hogy miért ad vissza #HIÁNYZIK! (#N/A) hibát az FKERES, amikor biztos vagy benne, hogy a keresett adat szerepel a keresési táblában. Látod a szemeddel, hogy ott van, akkor miért nem találja meg?
Leellenőrizted a tartományt, a hivatkozásokat? Igen, jók. Ledollároztad? Igen, ebben nincs hiba. Mégse működik.
Nos, akkor itt az ideje pár dolgot megtanulni az adatformátumról.
Dióhéjban: akkor kell adatformátum problémára gondolnod, ha valamilyen számot keresel. Olyan számot, amit nem számolásra használunk: kódszám, cikkszám, ilyesmi. Ilyenkor előfordul, hogy az Excel úgy érzékeli, hogy ez a szám valójában szöveg – úgy mondjuk:szövegként tárolt szám. Általában ez okoz “miért nem működik???” problémát…
A gyanús jelek:
- Balra igazított számok
- Nem adja össze az Excel a számokat a jobb alsó sarokban
- Kis zöld háromszög a cella sarkában
- Hibajelzés, mikor a cellára kattintasz, és szövegként tárolt szám hibaleírás
Az FKERES esetében akkor fog ez problémát okozni, ha a keresett érték és a keresési tartomány adatformátuma eltér. A szövegként tárolt számot az Excel szövegként értelmezi, és úgy gondolja, hogy ez semmiképpen nem lehet “egyenlő” egy számmal – tehát hiába van ott a kód, nem fogja megtalálni, ha más az adatformátum.
Mi NEM megoldás? Látjátok, hogy következetesen adatformátumról beszélek, nem cellaformátumról. Sőt, ezt illene is inkább cellaformázásnak nevezni. Az adatformátum probléma nem oldható meg cellaformázással. Hiába állítod át a cellát szám formátumúra, ettől még szöveg marad benne. Ezzel tehát felesleges próbálkozni.
Az adatformátum változtatáshoz mindenképpen konvertálni kell.
Nem kell megijedni, egyszerű! Több lehetőség is van:
- Használhatod a hibajelzés legördülő opciói közül az átalakítás számmá (Convert to number) lehetőséget. Ha kijelölöd a tartományt, akkor az összes cellára elvégzi a konverziót.
- A menüben is van egy pont a konvertálásra. J
elöld ki a tartományt. Az Adatok (Data) menüben találsz egy Szövegből oszlopok gombot (Text to column) – a megjelenő ablakban csak nyomd meg a Befejezést.
- Haladók elvégezhetik a konverziót az FKERES formulában is. Ez akkor lesz praktikus, ha gyakran kell felülírnod az adatokat, és nem akarod minden alkalommal kézzel konvertálni a tartományt. A keresési érték adatformátumát fogjuk a keresési tartományéhoz igazítani.
Szövegből szám: bármilyen, az értéket nem változtató matematikai műveletet használhatsz.
Például:
=FKERES( A2+0 ; <tábla> ; <oszlop szám> ; <tartományban keres> )
Vagy az én kedvencem:
=FKERES( —A2 ; <tábla> ; <oszlop szám> ; <tartományban keres> ) (Itt két mínusz jel van a keresési érték előtt.)
Számból szöveg: A karaktersort nem megváltoztató szöveg műveletre van szükség. A számhoz hozzáfűzünk egy üres karaktersort, hogy szöveg legyen belőle:
=FKERES (A2&”” ; <tábla> ; <oszlop szám> ; <tartományban keres> ) (Az & jel után két idézőjel van.)
Mostmár működik, ugye? 🙂
Ez a korábbi cikkünk az adattisztításról még érdekes lehet a témában!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
9 comments
Skip to comment form
Sziasztok!
VLOOKUP esetén miért nem kezeli egy másik függvény eredményére történő keresést.
Ha egy pl MID függvénnyel kiveszek egy darabot egy cellaértékből, akkor azt aposztrófok közé teszi így sem a VLOOKUP, sem az INDEX/MATCH páros nem talál semmit az aposztrófok miatt.
Hogyan lehet eltüntetni az aposztrófokat az eredmény elejéről és végéről, hogy a keresés eredményes legyen?
Author
Szia!
Két különböző dolgot kérdezel. Hogy miért nem kezeli a pontos egyezéses keresést (negyedik paraméter HAMIS eset) – mert így rakta össze a Microsoft.
A másik kérdésedet nem értem: miért tenne a MID bármit is aposztrófok közé? Semilyen formula nem csinál ilyet. A MID mindig szöveget ad vissza – akkor is, ha szám karakterekből áll, ez szövet típusú adat. A cikkben a 3. pontban leírtakat lehet rá alkalmazni, ha szám típusú adatra van szükséged.
Üdv:
Kris
Szia Kris!
A2 rovat=HTOP210000001707709/1
Ha ezt keresem:
=VLOOKUP(MID(A2;13;7);Export!$K$1:$M$64938;2;0)
akkor a Lookup_value értékét így adja vissza: ‘1707709’, tehát a keresés eredménye #N/A lesz mivel nem talál a megadott helyen ilyen értéket csak 1707709-re adna találatot, de nem azt keresi!
Lehet igazad lesz, hogy a szöveg formátumú értékre nem tud a számtípusú keresni.
Author
Mindenféle formátumot tud keresni, csak megfelelően kell konvertálni. 🙂
Tehát a MID szövegesen adja vissza a számsort, az Export munkalapodon viszont sejtésem szerint szám formátumban vannak az adatok.
Próbáld meg így: =VLOOKUP(MID(A2;13;7)+0;Export!$K$1:$M$64938;2;0)
a +0-val számmá tudod konvertálni a MID eredményét.
Köszönöm Kris, ez a MEGOLDÁS! 🙂
Üdv!
Egy kis segítséget szeretnék kérni, mert egyszerűen már nem bírok rájönni, hogy mi lehet a hiba, és félek, rövidesen a gépem egészségi állapota fogja bánni (szétverem):
Van egy 3 oszlopból és 20 sorból álló táblázatom (B414-D433). Az első oszlop adatai megfelelően rendezettek. (Egymás alatt: P001, P065, P066, P067, P068, P069, P075, P076, P078, P080, P083, P101, P103).
Van egy második oszlopom, aminek értékei (4, 1, 1, 2, 2, 6, 2, 2, 7, 1, 2, 0, 0), és van egy harmadik oszlopom, ami a második oszlop kumulált értékeit adja (D414=C414, majd D415-ös cellától kezdve úgy van képletezve, hogy =HA(B415=””;””;D414+C415) és D416 cella, valamint attól lefelé a képlet másolásával lett kitöltve).
Van alatta egy másik táblázatom, (C437-V487), amelynek legelső (437-es sorában egymás mellett vannak (transzponálva) az első táblázat első oszlopának adatai (a P-vel kezdődőek). A C438-as cellában ez a képlet szerepel: =FKERES(C437;$B$414:$D$433;3), majd a 438-as sorban egészen a V oszlopig ez a képlet került másolásra.
Ennek megfelelően, a C438-as cellába 4 kerül, a D438-asba 5, az E438-asba 6, és így tovább az L438-asig, melynek értéke így 28. És itt jön a bazinagy probléma: az M438-as cellától kezdődően ezzel a képlettel “” értéket vesz fel a cella. Nem hibaértéket ad, nem is 0-t, hanem “” értéket. De mi a retektől, amikor a korábbi celláknál ugyanez a képet rendesen működik?! És akkor most jön a csűrcsavar: Ha az FKERES-sel érintett tábla valamennyi celláját képlettel átmásolom egy teljesen üres területre (simán csak =B414, stb…), és arra képletezem ugyanígy az FKERES függvényt, akkor érdekes módon, ott működik rendesen és a megfelelő értéket (30) adja.
Van erre valami értelmes magyarázat? Mi lehet vajon a hiba?
Előre is köszönöm a választ!
Tompy
Szia
A problémám a következö lenne. Készítettem két adadbázist amiből ki szeretném nyerni az adatokat.
Egyszerűnek gondoltam elsőre, az a lényeg hogy a B3 cellába ha megadok egy valós adatot akkor a C3 cellába
kiírja a másik adatbázisból(Oracle adatbázissal össze kapcsolt excel file) a hozzá tartozó adatot.
=VLOOKUP($B$3,’D:\mUNKA\New folder\[IOSLORD.xlsx]Sheet1′!$B2:$N999999,2,FALSE)
Ezt a függvényt használom, az az érdekes hogy csináltam mostanában legalább 10 hasonló dolgot és ott működik tökéletesen. Itt N/A hibát kapok. A konvertálást is megcsináltam már.
Egyszerűen nem tudok rájönni már agyérgörcsött kaptam. Eloslvastam mindent szerintem erről és mégsem jó.
Próbálkoztam az INDEX+MATCH függvényel is de az se jött be.
Emiatt fordulok hozzád segítségért, tanácstalan vagyok.
Előre is köszönöm.
Author
Szia! Akkor se működik, ha nyitva van a másik file, amire a VLOOKUP mutat?
Nem lehet, hogy valahol valami láthatatlan karakter van a keresett adat végén?
Kedves Kris!
Köszönöm szépen. Ezzel a megoldással nagyon segítettél.