photosdematures.com

BÚSQUEDA V amb text

Un dels criteris bàsics per treballar amb VLOOKUP és que el valor de "cerca" hauria de ser el mateix a la cel·la de resultats i també a la taula de dades principal, però, de vegades, tot i que el valor de cerca té el mateix aspecte en qualsevol de les cel·les, acabem obtenint un valor d'error. com a # N / A !. Això es deu al fet que el format del valor vlookup ha de ser diferent a qualsevol de les cel·les. Així doncs, en aquest article us mostrarem com treballar amb el format de text del valor Vlookup.

Exemple de VLOOKUP per a text

A continuació es mostra un exemple d’excel VLOOKUP per a text.

Podeu descarregar aquesta plantilla VLOOKUP for Text Excel aquí - VLOOKUP for Text Excel Template

De vegades, els números s’emmagatzemen com a valors de text i, en aquests casos, no podem tractar-los com a números a causa de la funcionalitat d’excel. Per exemple, mireu les dades següents.

A les dades anteriors, tot sembla un número, però quan el resumim hauríem d'obtenir un valor total de 3712054, però quan apliquem la funció SUM excel obtenim el número següent.

El motiu d’aquest fet és que alguns números s’emmagatzemen com a valors de text. Llavors, com identifiquem els valors del text?

Podem identificar valors de text mitjançant la funció excel ISNUMBER. La funció ISNUMBER retornarà TRUE si el valor de la cel·la seleccionada és número o en cas contrari retornarà FALS.

Així doncs, a les cel·les B5 i B6, obtenim el resultat com FALS que significa que els números de cel·les A5 i A6 s’emmagatzemen com a valors de text.

VLOOKUP requereix un format de número exacte

Per exemple, mireu les dades següents per aplicar la funció VLOOKUP.

A la Taula 1 hem d'aplicar la funció VLOOKUP per aconseguir que la columna d'ingressos resulti a la Taula 2.

Per tant, apliqueu la funció VLOOKUP a la taula 2.

Obtenim el següent resultat.

El resultat de la funció VLOOKUP a les cel·les E6 i E7 mostra # N / A !.

Vegem els valors de cerca.

Això sol passar en els números de valors de cerca; el motiu principal ha de ser el format dels números de les dues taules que no són el mateix. Per tant, en aquests casos, hem d’identificar en quins números de taula s’emmagatzemen com a text.

Apliqueu la funció ISNUMBER per identificar valors no numèrics.

Com podem veure, ISNUMBER ha identificat un valor no numèric a la taula 2.

Quan les dades de la taula principal són correctes i els números de la taula de resultats s’emmagatzemen com a text, primer hem de convertir els números formatats de text a valors numèrics i després aplicar VLOOKUP. Hi ha diverses maneres de fer-ho, a continuació es mostren mètodes.

Mètode 1: converteix els números formatats de text en valors numèrics mitjançant Enganxa especial

En primer lloc, introduïu el número 1 a qualsevol de les cel·les del full de treball i copieu aquesta cel·la.

Ara seleccioneu els valors de l'identificador de sortida a la taula 2 i obriu el quadre de diàleg Enganxa especial.

Per obrir i enganxar el quadre de diàleg especial, premeu ALT + E + S.

Tenim el quadre de diàleg següent.

A la finestra especial d'enganxar, seleccioneu "multiplicar" com a opció.

Premeu OK, convertirà tots els números amb format de text a valors numèrics i VLOOKUP ara recuperarà automàticament les dades de la taula 1.

Mètode 2: converteix mitjançant la funció VALUE

La funció VALUE s’utilitza per convertir els números formatats de text a valors numèrics. Com podem veure, el nostre VLOOKUP no va obtenir les dades a causa del format del valor de cerca.

Per superar aquest problema que aplica la funció de cerca, hem d’incloure la funció VALUE.

Mireu la fórmula anterior, he inclòs la funció de cerca amb la funció VALOR. Com que hem aplicat la funció VALOR dins de la funció VLOOKUP, primer convertirà els valors no numèrics a valors numèrics, després VLOOKUP els tracta només com a valors numèrics.

Mètode 3: Què passa si els números s’emmagatzemen com a text a la taula principal?

Hem vist com convertir els valors del text en números a la taula de resultats, però el que són els números s’emmagatzema com a valors de text a la mateixa taula principal.

Com podem fer a la imatge anterior de la taula principal (Taula 1), els valors s’emmagatzemen com a text. En aquests casos, hem d’incloure la funció TEXT per al valor de cerca a la funció VLOOKUP.

El problema aquí és que la funció TEX converteix fins i tot els valors numèrics en valors de text, de manera que alguns dels valors emmagatzemats com a valors numèrics no funcionaran en aquesta funció. Per a això, hem d’incloure la condició IFERROR en excel.

La condició IFERROR prova si el valor de cerca és numèric o no, si és numèric aplicarem LOOKUP normal o bé aplicarem la funció TEXT a excel.

Així, doncs, necessitem un ampli coneixement sobre les fórmules per treballar amb VLOOKUP a nivell avançat i en diferents escenaris.

Coses que cal recordar

• La funció TRIM també converteix els valors del text en valors numèrics.
• En primer lloc, hem de provar en quins números de taula s’emmagatzemen com a text.
• ISNUMBER retorna TRUE si el valor de la cel·la seleccionada és número o en cas contrari retornarà FALS.