VLOOKUP med tekst
Et af de grundlæggende kriterier for at arbejde med VLOOKUP er "opslagsværdien" skal være den samme i resultatcellen og også i hoveddatatabellen, men nogle gange, selvom opslagsværdien ser den samme ud i begge celler, får vi stadig fejlværdi som # N / A !. Dette skyldes, at formatet for vlookup-værdien skal være forskelligt i begge celler. Så i denne artikel viser vi dig, hvordan du arbejder med tekstformatet for Vlookup-værdien.
Eksempel på VLOOKUP for Text
Nedenfor er et eksempel på excel VLOOKUP til tekst.
Du kan downloade denne VLOOKUP til tekst Excel-skabelon her - VLOOKUP til tekst Excel-skabelonNogle gange gemmes tal som tekstværdier, og i sådanne tilfælde kan vi ikke behandle dem som tal på grund af funktionaliteten i excel. Se for eksempel nedenstående data.
I ovenstående data ser alt ud som tal, men når vi opsummerer det, skal vi få en samlet værdi på 3712054, men når vi anvender SUM excel-funktion, får vi nedenstående nummer.
Årsagen bag dette er, at nogle tal er gemt som tekstværdier. Så hvordan identificerer vi tekstværdier?
Vi kan identificere tekstværdier ved hjælp af ISNUMBER excel-funktionen. ISNUMBER-funktionen returnerer SAND, hvis den valgte celleværdi er nummer, ellers returnerer den FALSK.
Så i celle B5 & B6 fik vi resultatet som FALSE, hvilket betyder, at A5 & A6-cellenumre er gemt som tekstværdier.
VLOOKUP kræver nøjagtigt nummerformat
Se f.eks. Nedenstående data for at anvende VLOOKUP-funktionen.
Fra tabel 1 skal vi anvende VLOOKUP-funktionen for at få indtægtskolonnen til at resultere i tabel 2.
Så anvend VLOOKUP-funktionen i tabel 2.
Vi får følgende resultat.
Resultatet af VLOOKUP-funktionen i cellerne E6 & E7 viser # N / A !.
Lad os se på opslagsværdierne.
Dette sker normalt i antal opslagsværdier, hovedårsagen skal være formatet på tallene i begge tabeller ikke det samme. Så i sådanne tilfælde skal vi identificere, i hvilke tabelnumre der er gemt som tekst.
Anvend ISNUMBER-funktionen til at identificere ikke-numeriske værdier.
Som vi kan se, identificerede ISNUMBER ikke-numerisk værdi i tabel 2.
Når hovedtabeldataene er korrekte og resultattabellnumre er gemt som tekst, skal vi først konvertere de tekstformaterede tal til numeriske værdier og derefter anvende VLOOKUP. Der er flere måder, vi kan gøre dette på, nedenfor er metoder.
Metode 1: Konverter tekstformaterede tal til numeriske værdier gennem Indsæt specielt
Indtast først nummer 1 i en hvilken som helst af cellerne i regnearket, og kopier den celle.
Vælg nu udgangs-ID-værdier i tabel 2 og åbn dialogboksen Indsæt speciel.
For at åbne indsæt specialdialogboksen, tryk ALT + E + S.
Vi får følgende dialogboks.
Vælg "multiplicer" som valgmulighed i det specielle vindue.
Tryk på ok, det konverterer alle de tekstformaterede tal til numeriske værdier, og VLOOKUP henter nu automatisk dataene fra tabel 1.
Metode 2: Konverter ved hjælp af VALUE-funktion
VALUE-funktion bruges til at konvertere tekstformaterede tal til numeriske værdier. Som vi kan se, hentede vores VLOOKUP ikke dataene på grund af formatet for opslagsværdien.
For at overvinde dette problem, som anvender opslagsfunktionen, skal vi vedlægge VALUE-funktionen.
Se på ovenstående formel, jeg har lukket opslagsfunktionen med VÆRDI-funktionen. Da vi har anvendt VALUE-funktion inden i VLOOKUP-funktionen, konverterer den først de ikke-numeriske værdier til numeriske værdier, og derefter behandler VLOOKUP dem kun som numeriske værdier.
Metode 3: Hvad hvis numre er gemt som tekst i hovedtabellen
Vi har set hvordan man konverterer tekstværdier til tal i resultattabellen, men hvad der er tal gemmes som tekstværdier i selve hovedtabellen.
Som vi kan i ovenstående billede i hovedtabellen (tabel 1) gemmes værdier som tekst. I sådanne tilfælde er vi nødt til at vedlægge TEKST-funktionen for opslagsværdien i VLOOKUP-funktionen.
Problemet her er TEX-funktion konverterer selv de numeriske værdier til tekstværdier, og nogle af de værdier, der er gemt som numeriske værdier, fungerer derfor ikke i denne funktion. Til dette er vi nødt til at vedlægge IFERROR-tilstanden i excel.
IFERROR-tilstand tester, om opslagsværdien er numerisk eller ej, hvis den er numerisk, anvender vi normal LOOKUP, ellers anvender vi TEKST-funktionen i excel.
Så som dette har vi brug for omfattende viden om formler til at arbejde med VLOOKUP på avanceret niveau og i forskellige scenarier.
Ting at huske
- TRIM-funktionen konverterer også tekstværdier til numeriske værdier.
- Først skal vi teste, hvilke tabelnumre der er gemt som tekst.
- ISNUMBER returnerer SAND, hvis den valgte celleværdi er nummer, ellers returnerer den FALSK.