VYHLEDÁVÁNÍ s textem
Jedním ze základních kritérií pro práci s VLOOKUP je hodnota „vyhledávání“ by měla být stejná ve výsledné buňce i v hlavní tabulce dat, ale někdy, přestože vyhledávací hodnota vypadá stejně v každé buňce, nakonec dostaneme chybová hodnota jako # N / A !. Je to proto, že formát hodnoty vlookup musí být v každé buňce odlišný. V tomto článku vám tedy ukážeme, jak pracovat s textovým formátem hodnoty Vlookup.
Příklad VLOOKUP pro text
Níže je uveden příklad aplikace Excel VLOOKUP pro text.
Někdy jsou čísla uložena jako textové hodnoty a v takových případech je nemůžeme považovat za čísla kvůli funkcím aplikace Excel. Podívejte se například na níže uvedená data.
Ve výše uvedených datech vše vypadá jako čísla, ale když to sčítáme, měli bychom získat celkovou hodnotu 3712054, ale když použijeme funkci SUM excel, dostaneme níže uvedené číslo.
Důvodem je to, že některá čísla jsou uložena jako textové hodnoty. Jak tedy identifikujeme textové hodnoty?
Můžeme identifikovat textové hodnoty pomocí funkce ISNUMBER excel. Funkce ISNUMBER vrátí TRUE, pokud je vybranou hodnotou buňky číslo, jinak vrátí FALSE.
Takže v buňce B5 a B6 jsme dostali výsledek jako FALSE, což znamená, že čísla buněk A5 a A6 jsou uložena jako textové hodnoty.
VLOOKUP vyžaduje přesný formát čísla
Podívejte se například na níže uvedená data pro použití funkce VLOOKUP.
Z tabulky 1 musíme použít funkci VLOOKUP, aby sloupec tržeb vedl k tabulce 2.
Použijte tedy funkci VLOOKUP v tabulce 2.
Dostaneme následující výsledek.
Výsledek funkce VLOOKUP v buňkách E6 a E7 ukazuje # N / A !.
Podívejme se na vyhledávací hodnoty.
K tomu obvykle dochází v počtech vyhledávacích hodnot. Hlavním důvodem by měl být formát čísel v obou tabulkách, které nejsou stejné. V takových případech tedy musíme zjistit, ve kterých číslech tabulek jsou uložena jako text.
Použijte funkci ISNUMBER k identifikaci nečíselných hodnot.
Jak vidíme, ISNUMBER identifikoval nečíselnou hodnotu v tabulce 2.
Když jsou data hlavní tabulky správná a čísla výsledných tabulek jsou uložena jako text, musíme nejprve převést textem formátovaná čísla na číselné hodnoty a poté použít VLOOKUP. Existuje několik způsobů, jak toho dosáhnout, níže uvádíme metody.
Metoda 1: Převést čísla formátovaná textem na číselné hodnoty pomocí Vložit jinak
Nejprve zadejte číslo 1 do kterékoli z buněk v listu a zkopírujte tuto buňku.
Nyní vyberte hodnoty ID zásuvky v tabulce 2 a otevřete dialogové okno Vložit jinak.
Chcete-li otevřít speciální dialogové okno pro vložení, stiskněte alt = "" + E + S.
Dostaneme následující dialogové okno.
Ve speciálním okně pro vložení vyberte jako možnost „znásobit“.
Stiskněte ok, převede všechna textově formátovaná čísla na číselné hodnoty a VLOOKUP nyní automaticky načte data z tabulky 1.
Metoda 2: Převod pomocí funkce HODNOTA
Funkce VALUE se používá k převodu čísel ve formátu textu na číselné hodnoty. Jak vidíme, náš VLOOKUP nenačetl data kvůli formátu vyhledávací hodnoty.
K překonání tohoto problému, který pomocí vyhledávací funkce musíme přiložit funkci HODNOTA.
Podívejte se na výše uvedený vzorec. Uzavřel jsem vyhledávací funkci s funkcí HODNOTA. Protože jsme uvnitř funkce VLOOKUP použili funkci HODNOTA, nejprve převede nečíselné hodnoty na číselné hodnoty, pak je VLOOKUP považuje pouze za číselné hodnoty.
Metoda 3: Co když jsou čísla uložena jako text v hlavní tabulce
Viděli jsme, jak převést textové hodnoty na čísla ve výsledkové tabulce, ale jaká jsou jeho čísla uložena jako textové hodnoty v samotné hlavní tabulce.
Jak můžeme na výše uvedeném obrázku v hlavní tabulce (tabulka 1), hodnoty se ukládají jako text. V takových případech musíme uzavřít funkci TEXT pro vyhledávací hodnotu ve funkci VLOOKUP.
Problém je v tom, že funkce TEX převádí i číselné hodnoty na textové hodnoty, takže některé z hodnot uložených jako číselné hodnoty v této funkci nebudou fungovat. Za tímto účelem musíme uzavřít podmínku IFERROR v aplikaci Excel.
Podmínka IFERROR testuje, zda je vyhledávací hodnota číselná nebo ne. Pokud je to číselné, použijeme normální VYHLEDÁVÁNÍ, jinak použijeme funkci TEXT v aplikaci Excel.
Abychom mohli pracovat s VLOOKUP na pokročilé úrovni a v různých scénářích, potřebujeme rozsáhlé znalosti o vzorcích.
Věci k zapamatování
- Funkce TRIM také převádí textové hodnoty na číselné hodnoty.
- Nejprve musíme otestovat, ve kterých číslech tabulky jsou uložena jako text.
- ISNUMBER vrací TRUE, pokud je vybranou hodnotou buňky číslo, jinak vrátí FALSE.
