PREHLIADKA s textom
Jedným zo základných kritérií pre prácu s funkciou VLOOKUP je hodnota „vyhľadania“, ktorá by mala byť rovnaká vo výslednej bunke aj v hlavnej údajovej tabuľke, ale niekedy aj napriek tomu, že vyhľadávaná hodnota vyzerá v každej bunke rovnako, nakoniec dostaneme chybová hodnota ako # N / A !. Je to tak preto, lebo formát hodnoty vlookup musí byť v každej bunke iný. V tomto článku si teda ukážeme, ako pracovať s textovým formátom hodnoty Vlookup.
Príklad VÝHĽADU pre text
Nižšie uvádzame príklad excelového VHLEDÁVANIA pre text.
Čísla sa niekedy ukladajú ako textové hodnoty a v takýchto prípadoch ich nemôžeme považovať za čísla kvôli funkcii programu Excel. Napríklad si pozrite nasledujúce údaje.
Vo vyššie uvedených údajoch všetko vyzerá ako čísla, ale keď to spočítame, mali by sme dostať celkovú hodnotu 3712054, ale keď použijeme funkciu SUM excel, dostaneme nižšie uvedené číslo.
Dôvodom je to, že niektoré čísla sú uložené ako textové hodnoty. Ako teda identifikujeme textové hodnoty?
Textové hodnoty môžeme identifikovať pomocou funkcie ISNUMBER excel. Funkcia ISNUMBER vráti hodnotu TRUE, ak je vybratou hodnotou bunky číslo, inak vráti hodnotu FALSE.
Takže v bunke B5 a B6 sme dostali výsledok ako FALSE, čo znamená, že čísla buniek A5 a A6 sú uložené ako textové hodnoty.
VLOOKUP vyžaduje presný formát čísla
Napríklad pozrite sa na nasledujúce údaje, aby ste mohli použiť funkciu VLOOKUP.
Z tabuľky 1 musíme použiť funkciu VLOOKUP, aby sme zo stĺpca výnosov dostali tabuľku 2.
Aplikujte teda funkciu VLOOKUP v tabuľke 2.
Získame nasledujúci výsledok.
Výsledok funkcie VLOOKUP v bunkách E6 a E7 ukazuje # N / A !.
Pozrime sa na vyhľadávacie hodnoty.
Zvyčajne sa to deje v počtoch vyhľadávacích hodnôt. Hlavným dôvodom by mal byť formát čísel v oboch tabuľkách, ktoré nie sú rovnaké. Takže v takýchto prípadoch musíme zistiť, v ktorých číslach tabuľky sú uložené ako text.
Použite funkciu ISNUMBER na identifikáciu nečíselných hodnôt.
Ako vidíme, ISNUMBER identifikoval nečíselnú hodnotu v tabuľke 2.
Keď sú údaje hlavnej tabuľky správne a čísla výsledných tabuliek sa uložia ako text, potom musíme najskôr previesť textovo formátované čísla na číselné hodnoty a potom použiť VLOOKUP. Existuje niekoľko spôsobov, ako to môžeme urobiť, nižšie uvádzame metódy.
Metóda 1: Prevod čísel formátovaných do textu na číselné hodnoty pomocou funkcie Prilepiť špeciálne
Najskôr zadajte číslo 1 do ktorejkoľvek z buniek v hárku a skopírujte túto bunku.
Teraz vyberte hodnoty ID zásuvky v tabuľke 2 a otvorte dialógové okno Prilepiť špeciálne.
Ak chcete otvoriť prilepenie špeciálneho dialógového okna, stlačte alt = "" + E + S.
Dostaneme nasledujúce dialógové okno.
V špeciálnom okne vloženia vyberte možnosť „vynásobiť“.
Stlačte ok, prevedie sa všetky čísla vo formáte textu na číselné hodnoty a VLOOKUP teraz automaticky načíta údaje z tabuľky 1.
Metóda 2: Konverzia pomocou funkcie HODNOTA
Funkcia VALUE sa používa na prevod číselných formátovaných čísel na číselné hodnoty. Ako vidíme, náš VLOOKUP nenačítal údaje kvôli formátu vyhľadávacej hodnoty.
Na prekonanie tohto problému, ktorý aplikuje vyhľadávaciu funkciu, musíme priložiť funkciu VALUE.
Pozrite sa na vyššie uvedený vzorec. Priložil som vyhľadávaciu funkciu k funkcii VALUE. Pretože sme vo funkcii VLOOKUP použili funkciu VALUE, najskôr prevedie nečíselné hodnoty na číselné hodnoty, potom s nimi funkcia VLOOKUP zaobchádza iba ako s číselnými hodnotami.
Metóda 3: Čo ak sú čísla v hlavnej tabuľke uložené ako text
Videli sme, ako prevádzať textové hodnoty na čísla vo výsledkovej tabuľke, ale aké sú jeho čísla uložené ako textové hodnoty v samotnej hlavnej tabuľke.
Ako môžeme na vyššie uvedenom obrázku v hlavnej tabuľke (tabuľka 1), hodnoty sa ukladajú ako text. V takýchto prípadoch musíme uzavrieť funkciu TEXT pre vyhľadávaciu hodnotu vo funkcii VLOOKUP.
Problém je v tom, že funkcia TEX prevádza aj číselné hodnoty na textové hodnoty, takže niektoré z hodnôt uložených ako číselné hodnoty v tejto funkcii nebudú fungovať. Za týmto účelom musíme uzavrieť podmienku IFERROR v programe Excel.
Podmienka IFERROR testuje, či je vyhľadávacia hodnota číselná alebo nie. Ak je číselný, použijeme bežné VYHĽADÁVANIE, inak použijeme funkciu TEXT v programe Excel.
Takže takto potrebujeme rozsiahle znalosti o vzorcoch, aby sme mohli pracovať s VLOOKUPom na pokročilej úrovni a v rôznych scenároch.
Na čo treba pamätať
- Funkcia TRIM tiež prevádza textové hodnoty na číselné hodnoty.
- Najskôr si musíme vyskúšať, v ktorých číslach tabuliek sú uložené ako text.
- ISNUMBER vráti TRUE, ak je vybratá hodnota bunky číslo, inak vráti hodnotu FALSE.
