Contidos
Esta lección explica como xestionar rapidamente unha situación na que unha función VPR (VLOOKUP) non quere traballar en Excel 2013, 2010, 2007 e 2003, e como identificar e corrixir erros comúns e superar as limitacións VPR.
En varios artigos anteriores, exploramos as distintas facetas da función VPR en Excel. Se os leches con atención, agora deberías ser un experto neste campo. Non obstante, non é sen razón que moitos expertos en Excel cren VPR unha das características máis complexas. Ten unha morea de limitacións e funcións que se converten na fonte de moitos problemas e erros.
Neste artigo atoparás explicacións sinxelas dos erros #AT (#N / A), # Nome? (#NOME?) e #VALUE! (#VALOR!) que aparecen cando se traballa coa función VPR, así como técnicas e métodos para tratar con eles. Comezaremos cos casos máis comúns e os motivos máis obvios. VPR non funciona, polo que é mellor estudar os exemplos na orde en que se dan no artigo.
Corrixindo o erro #N/A na función BUSCAR V en Excel
En fórmulas con VPR mensaxe de erro #AT (#N/A) significa non está dispoñible (sen datos): aparece cando Excel non pode atopar o valor que busca. Isto pode ocorrer por varias razóns.
1. O valor desexado está mal escrito
Boa idea comprobar este artigo primeiro! Os erros tipográficos adoitan ocorrer cando traballas con cantidades moi grandes de datos, que consisten en miles de liñas, ou cando o valor que buscas está escrito nunha fórmula.
2. Erro #N/A ao buscar unha coincidencia aproximada con BUSCAR V
Se usa unha fórmula cunha condición de busca de coincidencia aproximada, é dicir, un argumento intervalo_lookup (range_lookup) é VERDADEIRO ou non se especifica, a túa fórmula pode informar dun erro #N / A en dous casos:
- O valor a buscar é menor que o valor máis pequeno da matriz que se busca.
- A columna de busca non está ordenada en orde ascendente.
3. Erro #N/A ao buscar unha coincidencia exacta con BUSCARV
Se estás buscando unha coincidencia exacta, é dicir, argumento intervalo_lookup (range_lookup) é FALSO e non se atopou o valor exacto, a fórmula tamén informará dun erro #N / A. Obtén máis información sobre como buscar coincidencias exactas e aproximadas cunha función VPR.
4. A columna de busca non está máis á esquerda
Como probablemente sabes, unha das limitacións máis importantes VPR é que non pode mirar cara á esquerda, polo que a columna de busca da túa táboa debe estar máis á esquerda. Na práctica, moitas veces esquecémonos disto, o que leva a unha fórmula que non funciona e un erro. #N / A.
Decisión: Se non é posible cambiar a estrutura de datos para que a columna de busca estea máis á esquerda, pode usar unha combinación de funcións Index (ÍNDICE) e MÁIS EXPOÑA (MATCH) como alternativa máis flexible para VPR.
5. Os números teñen formato de texto
Outra fonte de erro #N / A en fórmulas con VPR son números en formato de texto na táboa principal ou na táboa de busca.
Isto adoita ocorrer cando importa información de bases de datos externas ou cando escribe un apóstrofo antes dun número para manter o cero inicial.
Os signos máis obvios dun número en formato de texto móstranse na seguinte figura:
Ademais, pódense almacenar números no formato xeral (Xerais). Neste caso, só hai unha característica notable: os números están aliñados ao bordo esquerdo da cela, mentres que por defecto están aliñados ao bordo dereito.
Decisión: Se é un valor único, só tes que facer clic na icona de erro e seleccionar Converter en número (Converter a número) desde o menú contextual.
Se esta é a situación con moitos números, selecciónaos e fai clic co botón dereito na área seleccionada. No menú contextual que aparece, seleccione Formatear celas (Formato de celas) > separador Número (Número) > formato Número (Numérica) e prema OK.
6. Hai un espazo ao principio ou ao final
Esta é a razón menos obvia do erro. #N / A en función VPR, xa que é visualmente difícil ver estes espazos adicionais, especialmente cando se traballa con táboas grandes, cando a maioría dos datos están fóra da pantalla.
Solución 1: espazos adicionais na táboa principal (onde está a función BUSCAR V)
Se aparecen espazos adicionais na táboa principal, pode asegurarse de que as fórmulas funcionan correctamente encerrando o argumento valor_valor (lookup_value) nunha función TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solución 2: espazos adicionais na táboa de busca (na columna de busca)
Se hai espazos adicionais na columna de busca, de xeito sinxelo #N / A na fórmula con VPR non se pode evitar. En vez de VPR Podes usar unha fórmula matricial cunha combinación de funcións Index (ÍNDICE), MÁIS EXPOÑA (CORRIDO) e TRIM (TRIM):
=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))
Xa que esta é unha fórmula matricial, non esquezas premer Ctrl + Maiús + Entrar en lugar do habitual Entrarpara introducir a fórmula correctamente.
Erro #VALUE! en fórmulas con BUSCAR V
Na maioría dos casos, Microsoft Excel informa dun erro #VALUE! (#VALOR!) cando o valor utilizado na fórmula non coincide co tipo de datos. Respecto VPR, entón normalmente hai dúas razóns para o erro #VALUE!.
1. O valor que buscas supera os 255 caracteres
Coidado: función VPR non pode buscar valores que conteñan máis de 255 caracteres. Se o valor que busca supera este límite, recibirá unha mensaxe de erro. #VALUE!.
Decisión: Use unha morea de funcións ÍNDICE+ COINCIDENCIA (ÍNDICE + COINCIDENCIA). A continuación móstrase unha fórmula que funcionará ben para esta tarefa:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Non se especifica a ruta completa ao libro de busca
Se está a recuperar datos doutro libro de traballo, debe especificar a ruta completa a ese ficheiro. Máis concretamente, debe incluír o nome do libro de traballo (incluída a extensión) entre corchetes [ ], seguido do nome da folla, seguido dun signo de exclamación. Toda esta construción debe ir encerrada en apóstrofos, no caso de que o nome do libro ou da folla conteña espazos.
Aquí está a estrutura completa da función VPR para buscar noutro libro:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
A fórmula real pode verse así:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Esta fórmula buscará o valor da cela A2 nunha columna B na folla Sheet1 no caderno de traballo Novos Prezos e extrae o valor correspondente da columna D.
Se se omite algunha parte da ruta da táboa, a súa función VPR non funcionará e informará dun erro #VALUE! (aínda que o libro de traballo coa táboa de busca estea actualmente aberto).
Para máis información sobre a función VPRfacendo referencia a outro ficheiro de Excel, consulte a lección: Busca noutro libro de traballo usando BUSCAR V.
3. Argumento Column_num é menor que 1
É difícil imaxinar unha situación na que alguén ingrese un valor inferior a 1para indicar a columna da que extraer o valor. Aínda que é posible se o valor deste argumento é calculado por outra función de Excel aniñada dentro VPR.
Entón, se ocorre que o argumento col_index_num (número_columna) menor que 1función VPR tamén informará dun erro #VALUE!.
Se o argumento col_index_num (número_columna) é maior que o número de columnas da matriz dada, VPR informará dun erro # Ref! (#SSIL!).
Erro #NAME? en BUSCAR V
O caso máis sinxelo é un erro # Nome? (#NAME?) – aparecerá se accidentalmente escribe un nome de función cun erro.
A solución é obvia: revisa a túa ortografía!
VLOOKUP non funciona (limitacións, advertencias e decisións)
Ademais da sintaxe bastante complicada, VPR ten máis limitacións que calquera outra función de Excel. Debido a estas limitacións, fórmulas aparentemente sinxelas con VPR moitas veces leva a resultados inesperados. A continuación atoparás solucións para varios escenarios comúns onde VPR está mal.
1. VLOOKUP non distingue entre maiúsculas e minúsculas
función VPR non distingue entre maiúsculas e minúsculas e acepta caracteres en minúsculas e maiúsculas como iguais. Polo tanto, se hai varios elementos na táboa que só difiren por maiúsculas e minúsculas, a función BUSCAR V devolverá o primeiro elemento atopado, independentemente do caso.
Decisión: Use outra función de Excel que poida realizar unha busca vertical (BUSCAR, SUMAPRODUTO, ÍNDICE e COINCIDIR) en combinación con EXACTA que distingue caso. Para obter máis detalles, podes aprender da lección: 4 formas de facer que VLOOKUP distinga entre maiúsculas e minúsculas en Excel.
2. BUSCARV devolve o primeiro valor atopado
Como xa sabes, VPR devolve o valor da columna indicada correspondente á primeira coincidencia atopada. Non obstante, podes facer que extraia o 2º, 3º, 4º ou calquera outra repetición do valor que desexes. Se precisa extraer todos os valores duplicados, necesitará unha combinación de funcións Index (ÍNDICE), MENOS (PEQUENO) e LIÑA (FILA).
3. Engadiuse ou eliminouse unha columna á táboa
Por desgraza, as fórmulas VPR deixar de funcionar cada vez que se engade ou elimina unha nova columna á táboa de busca. Isto ocorre porque a sintaxe VPR require que especifique o rango completo da busca e o número de columna específico para a extracción de datos. Por suposto, tanto o intervalo indicado como o número de columna cambian cando eliminas unha columna ou insiras unha nova.
Decisión: E de novo as funcións teñen présa por axudar Index (ÍNDICE) e MÁIS EXPOÑA (PARTIDO). Na fórmula ÍNDICE+ COINCIDENCIA Defines por separado as columnas de busca e recuperación e, como resultado, podes eliminar ou inserir tantas columnas como queiras sen preocuparte de ter que actualizar todas as fórmulas de busca relacionadas.
4. As referencias das celas son confusas ao copiar unha fórmula
Este título explica exhaustivamente a esencia do problema, non?
Decisión: Use sempre referencias de cela absolutas (co símbolo $) nos rexistros do intervalo, por exemplo $A$2:$C$100 or $A:$C. Na barra de fórmulas, pode cambiar rapidamente o tipo de ligazón facendo clic F4.
VLOOKUP: traballa coas funcións IFERROR e ISERROR
Se non queres asustar aos usuarios con mensaxes de erro #N / A, #VALUE! or # Nome?, pode mostrar unha cela baleira ou a súa propia mensaxe. Podes facelo colocando VPR nunha función SE ERRO (IFERROR) en Excel 2013, 2010 e 2007 ou use unha morea de funcións SE+ISERROR (IF+ISERROR) en versións anteriores.
VLOOKUP: traballando coa función IFERROR
Sintaxe da función SE ERRO (IFERROR) é sinxelo e fala por si só:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
É dicir, para o primeiro argumento insire o valor que se vai comprobar para detectar un erro, e para o segundo argumento especifica o que devolver se se atopa un erro.
Por exemplo, esta fórmula devolve unha cela baleira se non se atopa o valor que está a buscar:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Se quere mostrar a súa propia mensaxe en lugar da mensaxe de erro estándar da función VPR, ponlo entre comiñas, así:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
BUSCAR V: traballando coa función ISERROR
Xa que a función SE ERRO apareceu en Excel 2007, cando traballes en versións anteriores terás que usar a combinación IF (IF) e EOSHIBKA (ISERROR) así:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Por exemplo, a fórmula SE+ÉERRO+BUSCARV, semellante á fórmula SE ERRO+BUSCAR Vmostrado arriba:
=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;ЛОЖЬ))
Iso é todo por hoxe. Espero que este breve tutorial che axude a xestionar todos os posibles erros. VPR e fai que as túas fórmulas funcionen correctamente.