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ú hodnotu 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 je uvedený príklad excelového VHLEDÁVANIA pre text.
Tento VLOOKUP pre textovú šablónu Excel si môžete stiahnuť tu - VLOOKUP pre textovú šablónu ExcelČí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ôvod je ten, ž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 identifikovanú nečíselnú hodnotu v tabuľke 2.
Keď sú údaje hlavnej tabuľky správne a čísla tabuliek výsledkov uložené ako text, musíme najskôr previesť textom 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.
Špeciálne dialógové okno otvoríte stlačením kombinácie klávesov ALT + E + S.
Dostaneme nasledujúce dialógové okno.
V špeciálnom okne prilepenia vyberte možnosť „vynásobiť“.
Stlačte ok, prevedie sa všetky čísla formátované na text 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ý pri použití vyhľadávacej funkcie 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 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 to, čo sú čísla, sa ukladajú ako textové hodnoty v samotnej hlavnej tabuľke.
Ako môžeme na obrázku vyššie v hlavnej tabuľke (tabuľka 1), hodnoty sú uložené 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 normálne LOOKUP alebo 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.