La funció VLOOKUP no funciona: resolució de problemes N/A, NAME i VALUE

Aquesta lliçó explica com afrontar ràpidament una situació en què una funció VPR (VLOOKUP) no vol funcionar a Excel 2013, 2010, 2007 i 2003, i com identificar i corregir errors comuns i superar les limitacions VPR.

En diversos articles anteriors, hem explorat les diferents facetes de la funció VPR en Excel. Si els heu llegit amb atenció, ara hauríeu de ser un expert en aquest camp. Tanmateix, no és sense raó que molts experts d'Excel creuen VPR una de les característiques més complexes. Té un munt de limitacions i característiques que es converteixen en la font de molts problemes i errors.

En aquest article trobareu explicacions senzilles dels errors #AT (#N/A), # ¿NOM? (#NOM?) i #VALUE! (#VALOR!) que apareixen quan es treballa amb la funció VPR, així com les tècniques i mètodes per tractar-los. Començarem pels casos més habituals i els motius més evidents. VPR no funciona, així que és millor estudiar els exemples en l'ordre en què es donen a l'article.

• Correcció de l'error #N/A
• Arreglant el #VALOR! en fórmules amb BUSCAR V
• Error #NAME? a BUSCAR V
• VLOOKUP no funciona (problemes, limitacions i solucions)
• VLOOKUP: treballant amb les funcions SIERROR i ISERROR

S'estan solucionant l'error #N/A a la funció VLOOKUP a Excel

En fórmules amb VPR missatge d'error #AT (#N/A) vol dir no disponible (sense dades): apareix quan Excel no pot trobar el valor que busqueu. Això pot passar per diversos motius.

1. El valor desitjat està escrit malament

Bona idea comprovar aquest article primer! Sovint es produeixen errors ortogràfics quan treballeu amb quantitats molt grans de dades, que consisteixen en milers de línies, o quan el valor que busqueu s'escriu en una fórmula.

2. Error #N/A en cercar una coincidència aproximada amb BUSCARV

Si utilitzeu una fórmula amb una condició de cerca de coincidència aproximada, és a dir, un argument range_lookup (range_lookup) és TRUE o no s'especifica, la fórmula pot informar d'un error # N / A en dos casos:

• El valor a buscar és inferior al valor més petit de la matriu que s'està buscant.
• La columna de cerca no està ordenada en ordre ascendent.

3. Error #N/A en cercar una coincidència exacta amb BUSCARV

Si busqueu una coincidència exacta, és a dir, argument range_lookup (range_lookup) és FAL i no s'ha trobat el valor exacte, la fórmula també informarà d'un error # N / A. Obteniu més informació sobre com cercar coincidències exactes i aproximades amb una funció VPR.

4. La columna de cerca no està més a l'esquerra

Com probablement sabreu, una de les limitacions més importants VPR és que no pot mirar cap a l'esquerra, per tant, la columna de cerca de la taula ha d'estar més a l'esquerra. A la pràctica, sovint ens oblidem d'això, cosa que condueix a una fórmula que no funciona i a un error. # N / A.

Decisió: Si no és possible canviar l'estructura de dades de manera que la columna de cerca estigui més a l'esquerra, podeu utilitzar una combinació de funcions ÍNDEX (ÍNDEX) i MÉS EXPOSADA (MATCH) com a alternativa més flexible per VPR.

5. Els números tenen el format de text

Una altra font d'error # N / A en fórmules amb VPR són números en format de text a la taula principal o a la taula de cerca.

Això sol passar quan importeu informació de bases de dades externes o quan escriviu un apòstrof abans d'un número per mantenir el zero inicial.

Els signes més evidents d'un nombre en format de text es mostren a la figura següent:

A més, els números es poden emmagatzemar en el format General (General). En aquest cas, només hi ha una característica notable: els números estan alineats a la vora esquerra de la cel·la, mentre que per defecte estan alineats a la vora dreta.

Decisió: Si és un valor únic, només cal que feu clic a la icona d'error i seleccioneu Convertir en nombre (Convertir a número) des del menú contextual.

Si aquesta és la situació amb molts números, seleccioneu-los i feu clic amb el botó dret a l'àrea seleccionada. Al menú contextual que apareix, seleccioneu Format de cel·les (Format cel·les) > pestanya Nombre (Número) > format Nombre (Numèric) i premeu OK.

6. Hi ha un espai al principi o al final

Aquesta és la raó menys òbvia de l'error. # N / A en funció VPR, ja que visualment és difícil veure aquests espais addicionals, especialment quan es treballa amb taules grans, quan la majoria de les dades estan fora de la pantalla.

Solució 1: espais addicionals a la taula principal (on hi ha la funció BUSCAR V)

Si apareixen espais addicionals a la taula principal, podeu assegurar-vos que les fórmules funcionen correctament adjuntant l'argument valor_cerca (lookup_value) en una funció TRIM (TALLADA):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)

=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Solució 2: espais addicionals a la taula de cerca (a la columna de cerca)

Si hi ha espais addicionals a la columna de cerca, de maneres senzilles # N / A a la fórmula amb VPR no es pot evitar. En lloc de VPR Podeu utilitzar una fórmula matricial amb una combinació de funcions ÍNDEX (ÍNDEX), MÉS EXPOSADA (COMPARAT) i TRIM (TALLADA):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))

=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Com que es tracta d'una fórmula matricial, no us oblideu de prémer Ctrl + Maj + Retorn en lloc de l'habitual Enterper introduir la fórmula correctament.

Error #VALUE! en fórmules amb BUSCAR V

En la majoria dels casos, Microsoft Excel informa d'un error #VALUE! (#VALOR!) quan el valor utilitzat a la fórmula no coincideix amb el tipus de dades. Referent VPR, llavors normalment hi ha dues raons per a l'error #VALUE!.

1. El valor que cerqueu supera els 255 caràcters

Aneu amb compte: funció VPR no pot cercar valors que continguin més de 255 caràcters. Si el valor que cerqueu supera aquest límit, rebreu un missatge d'error. #VALUE!.

Decisió: Utilitzeu un munt de funcions ÍNDEX+CONFIG (ÍNDEX + PARTIDA). A continuació es mostra una fórmula que anirà bé per a aquesta tasca:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))

=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

2. No s'especifica el camí complet al llibre de cerca

Si esteu recuperant dades d'un altre llibre de treball, heu d'especificar el camí complet a aquest fitxer. Més concretament, heu d'incloure el nom del llibre de treball (inclosa l'extensió) entre claudàtors [ ], seguit del nom del full, seguit d'un signe d'exclamació. Tota aquesta construcció s'ha de tancar entre apòstrofs, en cas que el nom del llibre o del full contingui espais.

Aquí teniu l'estructura completa de la funció VPR per buscar en un altre llibre:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)

=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

La fórmula real podria semblar així:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)

=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Aquesta fórmula buscarà el valor de la cel·la A2 en una columna B al full Sheet1 al llibre de treball Nous Preus i extreu el valor corresponent de la columna D.

Si s'omet alguna part del camí de la taula, la vostra funció VPR no funcionarà i informarà d'un error #VALUE! (encara que el llibre de treball amb la taula de cerca estigui obert actualment).

Per a més informació sobre la funció VPRfent referència a un altre fitxer d'Excel, vegeu la lliçó: Cercar un altre llibre de treball mitjançant VLOOKUP.

3. Argument Column_num és menor que 1

És difícil imaginar una situació en què algú introdueix un valor inferior a 1per indicar la columna de la qual extreure el valor. Tot i que és possible si el valor d'aquest argument es calcula mitjançant una altra funció d'Excel imbricada dins VPR.

Per tant, si passa que l'argument col_index_num (número_columna) inferior a 1function VPR també informarà d'un error #VALUE!.

Si l'argument col_index_num (número_columna) és més gran que el nombre de columnes de la matriu donada, VPR informarà d'un error REF! (#SSIL!).

Error #NAME? a BUSCAR V

El cas més senzill és un error # ¿NOM? (#NAME?): apareixerà si escrius accidentalment un nom de funció amb un error.

La solució és òbvia: comproveu l'ortografia!

VLOOKUP no funciona (limitacions, advertències i decisions)

A més de la sintaxi força complicada, VPR té més limitacions que qualsevol altra funció d'Excel. A causa d'aquestes limitacions, fórmules aparentment senzilles amb VPR sovint condueixen a resultats inesperats. A continuació trobareu solucions per a diversos escenaris comuns on VPR està malament.

1. VLOOKUP no distingeix entre majúscules i minúscules

function VPR no distingeix entre majúscules i minúscules i accepta el mateix caràcters en majúscules i minúscules. Per tant, si hi ha diversos elements a la taula que només difereixen per majúscules i minúscules, la funció BUSCARV retornarà el primer element trobat, independentment de les majúscules i minúscules.

Decisió: Utilitzeu una altra funció d'Excel que pugui realitzar una cerca vertical (CERCA, SUMA PRODUCTE, ÍNDEX i COINCIDENCIA) en combinació amb EXACTA que distingeix cas. Per obtenir més detalls, podeu aprendre de la lliçó: 4 maneres de fer que VLOOKUP distingeix entre majúscules i minúscules a Excel.

2. BUSCARV retorna el primer valor trobat

Com ja sabeu, VPR retorna el valor de la columna donada corresponent a la primera coincidència trobada. Tanmateix, podeu fer que extreu el 2n, 3r, 4t o qualsevol altra repetició del valor que vulgueu. Si necessiteu extreure tots els valors duplicats, necessitareu una combinació de funcions ÍNDEX (ÍNDEX), MENYS (PETIT) i LÍNIA (FILERA).

3. S'ha afegit o eliminat una columna a la taula

Malauradament, les fórmules VPR deixa de funcionar cada vegada que s'afegeix o s'elimina una columna nova a la taula de cerca. Això passa perquè la sintaxi VPR requereix que especifiqueu l'interval complet de la cerca i el número de columna específic per a l'extracció de dades. Naturalment, tant l'interval donat com el número de columna canvien quan suprimiu una columna o n'introduïu una de nova.

Decisió: I de nou les funcions tenen pressa per ajudar ÍNDEX (ÍNDEX) i MÉS EXPOSADA (PARTIDA). A la fórmula ÍNDEX+CONFIG Definiu les columnes de cerca i recuperació per separat i, com a resultat, podeu suprimir o inserir tantes columnes com vulgueu sense preocupar-vos d'haver d'actualitzar totes les fórmules de cerca relacionades.

4. Les referències de cel·les es confonen quan es copia una fórmula

Aquest encapçalament explica l'essència del problema de manera exhaustiva, oi?

Decisió: Utilitzeu sempre referències de cel·les absolutes (amb el símbol $) en registres l'interval, per exemple 2 $A$: 100 $C$ or $A:$C. A la barra de fórmules, podeu canviar ràpidament el tipus d'enllaç fent clic F4.

VLOOKUP: treballant amb les funcions SIERROR i ISERROR

Si no voleu espantar els usuaris amb missatges d'error # N / A, #VALUE! or # ¿NOM?, podeu mostrar una cel·la buida o el vostre propi missatge. Podeu fer-ho col·locant VPR en una funció SI ERROR (IFERROR) a Excel 2013, 2010 i 2007 o utilitzeu un munt de funcions SI+ÉSERROR (IF+ISERROR) en versions anteriors.

VLOOKUP: treballant amb la funció IFERROR

Sintaxi de la funció SI ERROR (IFERROR) és senzill i parla per si mateix:

IFERROR(value,value_if_error)

ЕСЛИОШИБКА(значение;значение_если_ошибка)

És a dir, per al primer argument inseriu el valor que s'ha de comprovar si hi ha un error, i per al segon argument especifiqueu què voleu tornar si es troba un error.

Per exemple, aquesta fórmula retorna una cel·la buida si no es troba el valor que busqueu:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

Si voleu mostrar el vostre propi missatge en lloc del missatge d'error estàndard de la funció VPR, posa-ho entre cometes, així:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")

=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

VLOOKUP: treballant amb la funció ISERROR

Des de la funció SI ERROR aparegut a Excel 2007, quan treballeu en versions anteriors haureu d'utilitzar la combinació IF (SI) i EOSHIBKA (ISERROR) així:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)

=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Per exemple, la fórmula SI+ÉSERROR+VLOOKUP, semblant a la fórmula IFERROR+VLOOKUPmostrat a dalt:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

Això és tot per avui. Espero que aquest breu tutorial us ajudi a fer front a tots els possibles errors. VPR i fes que les teves fórmules funcionin correctament.