Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-vyhledavaci » SVYHLEDAT-text-cislo-reseni-problemu
Microsoft Excel logo

SVYHLEDAT (VLOOKUP) - číslo vs text Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Trápí vás funkce SVYHLEDAT (VLOOKUP), kde správně nehledá ač ve zdrojové tabulce, hodnotu přes klasické hledání najdete? Problém často je v rozdílech číslo a text v danné buňce.

Pokud pominu mezery na konci textu.

Úvodem

Často se setkávám s problémem při hledání hodnot s využitím funkce SVYHLEDAT (VLOOKUP). Ač podle návodu v prohledávané tabulce máte jedinečné hodnoty v prvním sloupci, ve správné tabulce, hledáte přesnou shodu, přes argument NEPRAVDA (FALSE). Přesto funkce hledanou hodnotu nenajde. Vyhledáte-li ručně (využitím Najít - Ctrl + F) a hodnotu v tabulce vidíte.

Co může být příčinou, jak problém najít a jak vyhledávací funkci upravit (aniž musíte upravovat zdrojová data) je popsáno v tomto článku. Z důvodu přehlednosti je článek rozdělen na kapitoly:


Trocha teorie

Příčinou může být (kromě duplicitních názvu, opomenutí přesné shody, spravné označení tabulky ...) nekonzistence mezi textem a číslem (myslíte si, že v prvním sloupci prohledávané tabulky je číslo, ale opak je pravdou, ve skutečnosti může jít o text, případně opačně). Nebo-li například hodnota 1 (kdy vy vydíte číslo, jde přece o "jedničku"), Excel ve skutečnosti buď nemusí vidět číslo, ale vidí text. Což je pro vyhledávací funkci rozdíl (text není číslo). Jak na to? Jak opravit:

  • Opravit ve zdrojových datech - zajistit shodný formát (většinou zvládne správce databáze a máte vyřešeno)
  • Opravit v Excel - využitím textových funkcí, Power Query...
  • Napsat lépe funkci SVYHLEDAT - využít HODNOTA (VALUE), případně HODNOTA.NA.TEXT (TEXT)

Další komplikace

U vyhledávací funkce SVYHLEDAT se můžete setkat s dalšími komplikacemi (což většinou odhalíte a vyřešíte sami):

  • duplicitní hodnoty - ve zdrojové (prohledávané tabulce) máte duplicitní hodnoty
  • neseřazeno - pokud používáte funkci v přibližné shodě nemáte zdrojovou - prohledávanou tabulku správně seřazenou
  • Přesná shoda - opomenutý čtvrtý argument NEPRAVDA (FALSE) pro hledaní na přesnou shodu
  • "Nauzamčena" tabulka - Zapomenete přidat absolutní odkazy neboli $, tím pádem dochází k posouvaní oblastí a opět funkce SVYLHEDAT nemusí fungovat správně.
  • Více znaků - mezery, které nevidíté, ale třeba zdrojová data, nebo hledaný text je obsahuji speciálmní znaky (mezery, tvrdá mezera). Vy mezeru vidět nemusíte, ale Excel ji vidí. Pes vs Pes_ je rozdíl (druhé slovo obsahuje mezeru). Tip využít funkci DÉLKA (LEN) na zjištění počtu znaku.
  • Text vs číslo - tohle si probereme v tomto článku

Zdrojová tabulka

Pro ukázku potřebujeme data. Připravíme si data, obsahují v prvním sloupci (prohledávaném) čísla i text a ve druhém informaci ať vím co je v kterém řádku a využijeme při hledání.

  • Číslo jedna, je zapsáno nejprve jako číslo a podruhé jako text.
  • Číslo dvě je zapsáno jako text.
  • Číslo tři je zapsáno jako číslo.

Co budeme hledat?

Abych ukázal jak se funkce SVYHLEDAT chová. Předvedu na hledání hodnot jedna až tři. Kdy každá hodnota je zapsána jednou jako číslo a jednou jako text (tj. budeme hledat 6 hodnot, teda sedm, jednou jsou číslu přiřazeny desetinná místa, ať je pochopení jak funkce SVYHLEDAT pracuje dokonalé).

Excel zdrojová data pro SVYHLEDAT

Jak zjistit o co jde

To, že vidíte na kartě Domů v sekci číslo, Číslo, ještě se nemusí jednat o číslo. Aneb jak zjistit co se v buňce nachází.

Jak co nejefektivněji zjistit, zda v buňce je text nebo číslo (že si něco myslíte je jedná věc, ale vy to musíte vědět přesně). Několik možností zjištění, co je v buňce text/číslo:

  • Přidat odebrat desetinná místa - nejrychlejší pokud se jedná o jednu buňku ve které vidíte číslo a chcete si být jisti na 100%, stačí na pásu karet Domů přidat nebo ubrat desetinná místa.
  • Využít funkci JE.ČÍSLO (ISNUMBER) - funkce vrátí PRAVDA (TRUE) pokud v buňce je číslo, jinak vrátí NEPRAVDA (FALSE) - pak víte, že v buňce je text
  • Využít funkci JE.TEXT (ISTEXT)
Excel zdrojová data kontrola

SVYHLEDAT jak zapsat

Teď když víme, že ve zdrdojové tabulce se některé hodnoty vyskytují jako číslo a některé jako text. A já hledám hodnoty ať už jako text, nebo číslo, ne vždy dojde k správnému propojení, ale pokud funkci SVYHLEDAT upravíte, doplníte o funkce HODNOTA (VALUE), případně HODNOTA.NA.TEXT (TEXT), můžete dostat úplně jiné výsledky. Případně pokud doplníte o funkci IFERROR ;)

Excel zdrojová data kontrola

Pokud jen vyhledáte přes SVYHLEDAT, funkce některé hodnoty nenajde. Nebo-li pokud hledáte číslo a v zdrojové tabulce je text, tak pochopitelně nedojde k nalezení.

=SVYHLEDAT(B5;$B$22:$C$25;2;NEPRAVDA)

Využitím funkci HODNOTA (VALUE), případně HODNOTA.NA.TEXT (TEXT), můžete dohledat patřičné hodnoty (většinou v praxi stačí), pokud už jde o zmatek v datech.

=SVYHLEDAT(HODNOTA.NA.TEXT(B5;"@");$B$22:$C$25;2;NEPRAVDA) =SVYHLEDAT(HODNOTA(B5);$B$22:$C$25;2;NEPRAVDA)

Pokud ale jsou různá data ať už na straně zdrojové tabulky tak i na straně vyhledávání, můžete využít kombinace, kdy se pokusíte nejprve najít text a posléze číslo (případně opačně). Navíc můžete ještě obalit funkci IFERROR a místo #NENÍ_K_DISPOZICI zobrazit, třeba nenalezeno.

=IFERROR(SVYHLEDAT(HODNOTA(B5);$B$22:$C$25;2;NEPRAVDA);SVYHLEDAT(HODNOTA.NA.TEXT(B5;"@");$B$22:$C$25;2;NEPRAVDA)) =IFERROR(SVYHLEDAT(HODNOTA.NA.TEXT(B5;"@");$B$22:$C$25;2;NEPRAVDA);SVYHLEDAT(HODNOTA(B5);$B$22:$C$25;2;NEPRAVDA))

Poznámka: Vím, nejjednodušší je vyřešit ve zdrojové tabulce (jen někdy ta možnost není a potřebujete rychle propojit tak i některou z výše uvedených možností můžete použít).

Další problémy

Pro řešení dalších problémů (více znaků v datech), můžete využít funkci DÉLKA (LEN).

Související články

Další související články:

Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor ke stažení zdarma je v přípravě. Patreon a váš příspěvek zajistí rychlejší publikaci ;).


Závěrem

Setkali jste se i s jiným problémem u funkce SVYHLEDAT? Můžete zmínit v komentářích.

Článek byl aktualizován: 16.06.2019 17:20

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 367

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.