«

»

jul 03

Nem működik az FKERES (VLOOKUP) – amit az adatformátumról tudnod kell

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

 

adatformatum_2

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:

  1. 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.
    adatformatum_4
  2. A menüben is van egy pont a konvertálásra. Jadatformatum_5elö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.
    adatformatum_6
  3. 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!


5 comments

Skip to comment form

  1. charlyl

    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?

    1. Kris
      Kris

      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

      1. charlyl

        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.

        1. Kris
          Kris

          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.

  2. charlyl

    Köszönöm Kris, ez a MEGOLDÁS! :)

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöljük.

Az alábbi HTML kódok használhatóak: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

css.php