Contenido
Esta lección explica cómo lidiar rápidamente con una situación en la que una función VPR (BUSCARV) no quiere trabajar en Excel 2013, 2010, 2007 y 2003, y cómo identificar y corregir errores comunes y superar limitaciones VPR.
En varios artículos anteriores, hemos explorado las diversas facetas de la función. VPR en Excel. Si los ha leído detenidamente, ahora debería ser un experto en este campo. Sin embargo, no es sin razón que muchos expertos en Excel creen VPR una de las características más complejas. Tiene un montón de limitaciones y características que se convierten en la fuente de muchos problemas y errores.
En este artículo encontrarás explicaciones sencillas de los errores. #at (#N / A), # ¿NOMBRE? (#Nombre y #¡VALOR! (#¡VALOR!) que aparecen al trabajar con la función VPR, así como técnicas y métodos para tratarlos. Comenzaremos con los casos más comunes y las razones más obvias. VPR no funciona, por lo que es mejor estudiar los ejemplos en el orden en que se dan en el artículo.
Corrección del error #N/A en la función BUSCARV en Excel
En fórmulas con VPR mensaje de error #at (#N/A) significa no disponible (sin datos): aparece cuando Excel no puede encontrar el valor que está buscando. Esto puede suceder por varias razones.
1. El valor deseado está mal escrito
¡Buena idea revisar este artículo primero! Los errores tipográficos suelen ocurrir cuando trabaja con grandes cantidades de datos, que consisten en miles de líneas, o cuando el valor que está buscando se escribe en una fórmula.
2. Error #N/A al buscar una coincidencia aproximada con BUSCARV
Si usa una fórmula con una condición de búsqueda de coincidencia aproximada, es decir, argumento rango de búsqueda (range_lookup) es VERDADERO o no se especifica, su fórmula puede informar un error #N / A en dos casos:
- El valor a buscar es menor que el valor más pequeño de la matriz que se busca.
- La columna de búsqueda no está ordenada en orden ascendente.
3. Error #N/A al buscar una coincidencia exacta con BUSCARV
Si está buscando una coincidencia exacta, es decir, un argumento rango de búsqueda (range_lookup) es FALSO y no se encontró el valor exacto, la fórmula también informará un error #N / A. Más información sobre cómo buscar coincidencias exactas y aproximadas con una función VPR.
4. La columna de búsqueda no está más a la izquierda
Como usted probablemente sabe, una de las limitaciones más significativas VPR es que no puede mirar hacia la izquierda, por lo tanto, la columna de búsqueda en su tabla debe estar más a la izquierda. En la práctica, a menudo nos olvidamos de esto, lo que conduce a una fórmula que no funciona y a un error. #N / A.
Decisión: Si no es posible cambiar la estructura de datos para que la columna de búsqueda esté más a la izquierda, puede usar una combinación de funciones ÍNDICE (ÍNDICE) y MÁS EXPUESTO (MATCH) como una alternativa más flexible para VPR.
5. Los números tienen formato de texto
Otra fuente de error #N / A en fórmulas con VPR son números en formato de texto en la tabla principal o tabla de búsqueda.
Esto suele suceder cuando importa información de bases de datos externas o cuando escribe un apóstrofo antes de un número para mantener el cero inicial.
Los signos más evidentes de un número en formato de texto se muestran en la siguiente figura:
Además, los números se pueden almacenar en el formato General (General). En este caso, solo hay una característica notable: los números están alineados en el borde izquierdo de la celda, mientras que, de forma predeterminada, están alineados en el borde derecho.
Decisión: Si es un valor único, simplemente haga clic en el icono de error y seleccione Convertir a Número (Convertir a número) en el menú contextual.
Si esta es la situación con muchos números, selecciónelos y haga clic derecho en el área seleccionada. En el menú contextual que aparece, seleccione Formato de celdas (Formato de celdas) > pestaña Número (Número) > formato Número (Numérico) y presione OK.
6. Hay un espacio al principio o al final
Esta es la razón menos obvia del error. #N / A en función VPR, ya que es visualmente difícil ver estos espacios adicionales, especialmente cuando se trabaja con tablas grandes, cuando la mayoría de los datos están fuera de la pantalla.
Solución 1: espacios adicionales en la tabla principal (donde está la función BUSCARV)
Si aparecen espacios adicionales en la tabla principal, puede asegurarse de que las fórmulas funcionen correctamente encerrando el argumento valor de búsqueda (lookup_value) en una función TRIM (RECORTAR):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solución 2: espacios adicionales en la tabla de búsqueda (en la columna de búsqueda)
Si hay espacios adicionales en la columna de búsqueda: formas simples #N / A en la fórmula con VPR no se puede evitar En vez de VPR Puede usar una fórmula matricial con una combinación de funciones ÍNDICE (ÍNDICE), MÁS EXPUESTO (PARTIDO) y TRIM (RECORTAR):
=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))
Dado que esta es una fórmula matricial, no olvide presionar Ctrl + Shift + Enter en lugar de lo habitual Participarpara introducir la fórmula correctamente.
¡Error #VALOR! en fórmulas con BUSCARV
En la mayoría de los casos, Microsoft Excel informa de un error #¡VALOR! (¡#VALOR!) cuando el valor utilizado en la fórmula no coincide con el tipo de datos. Sobre VPR, entonces generalmente hay dos razones para el error #¡VALOR!.
1. El valor que busca tiene más de 255 caracteres
Tenga cuidado: función VPR no puede buscar valores que contengan más de 255 caracteres. Si el valor que está buscando supera este límite, recibirá un mensaje de error. #¡VALOR!.
Decisión: Usa un montón de características ÍNDICE+COINCIDIR (ÍNDICE + PARTIDO). A continuación se muestra una fórmula que funcionará bien para esta tarea:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. No se especifica la ruta completa al libro de trabajo de búsqueda
Si está recuperando datos de otro libro de trabajo, debe especificar la ruta completa a ese archivo. Más específicamente, debe incluir el nombre del libro de trabajo (incluida la extensión) entre corchetes [ ], seguido del nombre de la hoja, seguido de un signo de exclamación. Toda esta construcción debe estar encerrada entre apóstrofes, en caso de que el nombre del libro u hoja contenga espacios.
Aquí está la estructura completa de la función. VPR para buscar en otro libro:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
La fórmula real podría 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á el valor de la celda. A2 en una columna B en la hoja Sheet1 en el libro de trabajo Nuevos precios y extraer el valor correspondiente de la columna D.
Si se omite alguna parte de la ruta de la tabla, su función VPR no funcionará y reportará un error #¡VALOR! (incluso si el libro de trabajo con la tabla de búsqueda está abierto actualmente).
Para más información sobre la función VPRhaciendo referencia a otro archivo de Excel, consulte la lección: Buscar en otro libro de trabajo usando BUSCARV.
3. El argumento Column_num es menor que 1
Es difícil imaginar una situación en la que alguien ingrese un valor menor que 1para indicar la columna de la que extraer el valor. Aunque es posible si el valor de este argumento es calculado por otra función de Excel anidada dentro VPR.
Entonces, si sucede que el argumento col_index_num (número_columna) menor que 1función VPR también reportará un error #¡VALOR!.
Si el argumento col_index_num (column_number) es mayor que el número de columnas en la matriz dada, VPR informará de un error #¡ÁRBITRO! (¡#SSYL!).
¿Error #NOMBRE? en BUSCARV
El caso más simple es un error. # ¿NOMBRE? (¿#NOMBRE?): aparecerá si accidentalmente escribe un nombre de función con un error.
La solución es obvia: ¡comprueba tu ortografía!
BUSCARV no funciona (limitaciones, advertencias y decisiones)
Además de la sintaxis bastante complicada, VPR tiene más limitaciones que cualquier otra función de Excel. Debido a estas limitaciones, fórmulas aparentemente simples con VPR a menudo conducen a resultados inesperados. A continuación encontrará soluciones para varios escenarios comunes donde VPR Está Mal.
1. BUSCARV no distingue entre mayúsculas y minúsculas
Función VPR no distingue entre mayúsculas y minúsculas y acepta caracteres en minúsculas y mayúsculas como iguales. Por lo tanto, si hay varios elementos en la tabla que difieren solo en mayúsculas y minúsculas, la función BUSCARV devolverá el primer elemento encontrado, independientemente de las mayúsculas y minúsculas.
Decisión: Use otra función de Excel que pueda realizar una búsqueda vertical (BUSCAR, SUMAPRODUCTO, ÍNDICE y COINCIDIR) en combinación con EXACTUn caso que distingue. Para obtener más detalles, puede aprender de la lección: 4 formas de hacer que BUSCARV distinga entre mayúsculas y minúsculas en Excel.
2. BUSCARV devuelve el primer valor encontrado
Como tu ya sabes, VPR devuelve el valor de la columna dada correspondiente a la primera coincidencia encontrada. Sin embargo, puede hacer que extraiga la segunda, la tercera, la cuarta o cualquier otra repetición del valor que desee. Si necesita extraer todos los valores duplicados, necesitará una combinación de funciones ÍNDICE (ÍNDICE), MENOS (Pequeño y LÍNEA (FILA).
3. Se agregó o eliminó una columna a la tabla
Desafortunadamente, las fórmulas VPR deja de funcionar cada vez que se agrega o elimina una nueva columna de la tabla de búsqueda. Esto sucede porque la sintaxis VPR requiere que especifique el rango completo de la búsqueda y el número de columna específico para la extracción de datos. Naturalmente, tanto el rango dado como el número de columna cambian cuando elimina una columna o inserta una nueva.
Decisión: Y nuevamente las funciones tienen prisa por ayudar. ÍNDICE (ÍNDICE) y MÁS EXPUESTO (JUEGO). en la fórmula ÍNDICE+COINCIDIR Usted define por separado las columnas de búsqueda y recuperación y, como resultado, puede eliminar o insertar tantas columnas como desee sin preocuparse por tener que actualizar todas las fórmulas de búsqueda relacionadas.
4. Las referencias de celda se confunden al copiar una fórmula
Este encabezado explica exhaustivamente la esencia del problema, ¿verdad?
Decisión: Utilice siempre referencias de celdas absolutas (con el símbolo $) en los registros del rango, por ejemplo $ A $ 2: $ C $ 100 or $ A: $ C. En la barra de fórmulas, puede cambiar rápidamente el tipo de vínculo haciendo clic en F4.
BUSCARV: trabajar con las funciones IFERROR y ISERROR
Si no quieres asustar a los usuarios con mensajes de error #N / A, #¡VALOR! or # ¿NOMBRE?, puede mostrar una celda vacía o su propio mensaje. Puede hacerlo colocando VPR en una función IFERROR (IFERROR) en Excel 2013, 2010 y 2007 o use un montón de funciones SI+ESERROR (IF+ISERROR) en versiones anteriores.
BUSCARV: trabajando con la función IFERROR
Sintaxis de la función IFERROR (IFERROR) es simple y habla por sí mismo:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Es decir, para el primer argumento, inserta el valor que se va a verificar en busca de un error, y para el segundo argumento, especifica qué devolver si se encuentra un error.
Por ejemplo, esta fórmula devuelve una celda vacía si no se encuentra el valor que está buscando:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Si desea mostrar su propio mensaje en lugar del mensaje de error estándar de la función VPR, ponlo entre comillas, así:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
BUSCARV: trabajar con la función ESERROR
Desde la función IFERROR apareció en Excel 2007, cuando trabaje en versiones anteriores tendrá que usar la combinación IF (Si y EOSIBKA (ESERROR) así:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Por ejemplo, la fórmula SI+ESERROR+BUSCARV, similar a la fórmula SI.ERROR+BUSCARVmostrado anteriormente:
=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;ЛОЖЬ))
Eso es todo por hoy. Espero que este breve tutorial te ayude a lidiar con todos los posibles errores. VPR y haz que tus fórmulas funcionen correctamente.