Todos los secretos de la función BUSCARV de Excel para buscar datos en una tabla y extraerlos en otra

2024 Autor: Malcolm Clapton | [email protected]. Última modificación: 2023-12-17 03:53

Después de leer el artículo, no solo aprenderá cómo buscar datos en una hoja de cálculo de Excel y extraerlos en otra, sino también técnicas que se pueden usar junto con la función BUSCARV.

Cuando se trabaja en Excel, a menudo es necesario buscar datos en una tabla y extraerlos en otra. Si aún no sabe cómo hacer esto, luego de leer el artículo, no solo aprenderá cómo hacerlo, sino que también descubrirá en qué condiciones puede obtener el máximo rendimiento del sistema. Se consideran la mayoría de las técnicas muy efectivas que deben usarse junto con la función BUSCARV.

Incluso si ha estado utilizando la función BUSCARV durante años, entonces con un alto grado de probabilidad este artículo le será útil y no le dejará indiferente. Por ejemplo, siendo un especialista en TI y luego un director en TI, he estado usando VLOOKUP durante 15 años, pero solo logré lidiar con todos los matices ahora, cuando comencé a enseñar Excel a las personas de manera profesional.

BUSCARV es una abreviatura de vvertical NSinspección. Del mismo modo, VLOOKUP - Vertical LOOKUP. El mismo nombre de la función nos sugiere que busca en las filas de la tabla (verticalmente - iterando sobre las filas y arreglando la columna), y no en las columnas (horizontalmente - iterando sobre las columnas y arreglando la fila). Cabe señalar que BUSCARV tiene una hermana, un patito feo que nunca se convertirá en cisne, esta es la función BUSCARH. HLOOKUP, a diferencia de VLOOKUP, realiza búsquedas horizontales, pero el concepto de Excel (y de hecho el concepto de organización de datos) implica que sus tablas tienen menos columnas y muchas más filas. Es por eso que necesitamos buscar por filas muchas veces más a menudo que por columnas. Si usa la función HLO con demasiada frecuencia en Excel, es probable que no haya entendido algo en esta vida.

Sintaxis

La función BUSCARV tiene cuatro parámetros:

= BUSCARV (;; [;]), aquí:

- el valor deseado (raramente) o una referencia a una celda que contiene el valor deseado (la gran mayoría de los casos);

- referencia a un rango de celdas (matriz bidimensional), en la columna FIRST (!) de la cual se buscará el valor del parámetro;

- número de columna en el rango desde el que se devolverá el valor;

- este es un parámetro muy importante que responde a la pregunta de si la primera columna del rango está ordenada en orden ascendente. Si la matriz está ordenada, especificamos el valor VERDADERO o 1; de lo contrario, FALSO o 0. Si se omite este parámetro, el valor predeterminado es 1.

Apuesto a que muchos de los que saben que la función BUSCARV funciona como inestable, después de leer la descripción del cuarto parámetro, pueden sentirse incómodos, ya que están acostumbrados a verlo de una forma ligeramente diferente: generalmente están hablando de una coincidencia exacta cuando búsqueda (FALSO o 0) o un escaneo de rango (VERDADERO o 1).

Ahora debe esforzarse y leer el siguiente párrafo varias veces hasta que sienta el significado de lo que se dijo hasta el final. Cada palabra es importante allí. Los ejemplos te ayudarán a resolverlo.

¿Cómo funciona exactamente la fórmula BUSCARV?

• Tipo de fórmula I. Si el último parámetro se omite o se especifica igual a 1, entonces BUSCARV asume que la primera columna está ordenada en orden ascendente, por lo que la búsqueda se detiene en la fila que precede inmediatamente a la línea que contiene el valor mayor que el deseado … Si no se encuentra dicha cadena, se devuelve la última fila del rango.

  

• Fórmula II. Si el último parámetro se especifica como 0, VLOOKUP escanea la primera columna de la matriz secuencialmente e inmediatamente detiene la búsqueda cuando se encuentra la primera coincidencia exacta con el parámetro; de lo contrario, el código de error # N / A (# N / A) es regresado.

  

Flujos de trabajo de fórmulas

VPR tipo I

VPR tipo II

Corolarios para fórmulas de la forma I

1. Se pueden utilizar fórmulas para distribuir valores entre rangos.
2. Si la primera columna contiene valores duplicados y está ordenada correctamente, se devolverá la última de las filas con valores duplicados.
3. Si busca un valor que obviamente es mayor que el que puede contener la primera columna, puede encontrar fácilmente la última fila de la tabla, que puede ser bastante valiosa.
4. Esta vista devolverá el error # N / A solo si no encuentra un valor menor o igual al deseado.
5. Es bastante difícil entender que la fórmula devuelve los valores incorrectos si su matriz no está ordenada.

Corolarios para fórmulas de tipo II

Si el valor deseado aparece varias veces en la primera columna de la matriz, la fórmula seleccionará la primera fila para la recuperación de datos posterior.

Rendimiento VLOOKUP

Ha llegado al clímax del artículo. Parecería, bueno, ¿cuál es la diferencia si especifico cero o uno como último parámetro? Básicamente, todos indican, por supuesto, cero, ya que esto es bastante práctico: no necesita preocuparse por ordenar la primera columna de la matriz, puede ver inmediatamente si el valor se encontró o no. Pero si tiene varios miles de fórmulas VLOOKUP en su hoja, notará que VLOOKUP II es lento. Al mismo tiempo, generalmente todos comienzan a pensar:

• Necesito una computadora más potente;
• Necesito una fórmula más rápida, por ejemplo, muchas personas conocen INDEX + MATCH, que supuestamente es más rápido en un mísero 5-10%.

Y pocas personas piensan que tan pronto como comience a usar BUSCARV del tipo I y se asegure de que la primera columna esté ordenada por cualquier medio, la velocidad de BUSCARV aumentará 57 veces. Estoy escribiendo con palabras: ¡CINCUENTA Y SIETE VECES! No el 57%, sino el 5.700%. Verifiqué este hecho de manera bastante confiable.

El secreto de un trabajo tan rápido radica en el hecho de que se puede aplicar un algoritmo de búsqueda extremadamente eficiente en una matriz ordenada, que se llama búsqueda binaria (método de reducción a la mitad, método de dicotomía). Entonces VLOOKUP de tipo I lo aplica, y VLOOKUP de tipo II busca sin ninguna optimización en absoluto. Lo mismo ocurre con la función COINCIDIR, que incluye un parámetro similar, y la función BUSCAR, que solo funciona en matrices ordenadas y se incluye en Excel para compatibilidad con Lotus 1-2-3.

Desventajas de la fórmula

Las desventajas de BUSCARV son obvias: en primer lugar, busca solo en la primera columna de la matriz especificada y, en segundo lugar, solo a la derecha de esta columna. Y como comprenderá, bien puede suceder que la columna que contiene la información necesaria esté a la izquierda de la columna en la que estaremos mirando. La combinación de fórmulas INDEX + MATCH ya mencionada carece de este inconveniente, lo que la convierte en la solución más flexible para extraer datos de tablas en comparación con VLOOKUP (VLOOKUP).

Algunos aspectos de la aplicación de la fórmula en la vida real

Búsqueda de rango

Una ilustración clásica de una búsqueda de rango es la tarea de determinar un descuento por tamaño de pedido.

Buscar cadenas de texto

Por supuesto, BUSCARV busca no solo números, sino también texto. Hay que tener en cuenta que la fórmula no distingue entre el caso de los personajes. Si usa comodines, puede organizar una búsqueda aproximada. Hay dos comodines: "?" - reemplaza cualquier carácter en una cadena de texto, "*" - reemplaza cualquier número de caracteres.

Espacios de lucha

A menudo se plantea la cuestión de cómo resolver el problema de los espacios adicionales al realizar búsquedas. Si aún se pueden borrar de la tabla de búsqueda, entonces el primer parámetro de la fórmula VLOOKUP no siempre depende de usted. Por lo tanto, si existe el riesgo de obstruir las celdas con espacios adicionales, puede usar la función TRIM para eliminarlo.

Formato de datos diferente

Si el primer parámetro de la función BUSCARV se refiere a una celda que contiene un número, pero que se almacena en la celda como texto, y la primera columna de la matriz contiene números en el formato correcto, la búsqueda fallará. También es posible la situación opuesta. El problema se puede resolver fácilmente traduciendo el parámetro 1 al formato requerido:

= BUSCARV (−− D7; Productos! $ A $ 2: $ C $ 5; 3; 0) - si D7 contiene texto y la tabla contiene números;

= BUSCARV (D7 & ""); Productos! $ A $ 2: $ C $ 5; 3; 0) - y viceversa.

Por cierto, puede traducir texto a un número de varias formas a la vez, elija:

• Doble negación -D7.
• Multiplicación por uno D7 * 1.
• Suma cero D7 + 0.
• Elevando a la primera potencia D7 ^ 1.

La conversión de un número a texto se realiza mediante la concatenación con una cadena vacía, lo que obliga a Excel a convertir el tipo de datos.

Cómo suprimir # N / A

Es muy conveniente hacerlo con la función IFERROR.

Por ejemplo: = SI. ERROR (BUSCARV (D7; Productos! $ A $ 2: $ C $ 5; 3; 0); "").

Si BUSCARV devuelve el código de error # N / A, entonces IFERROR lo interceptará y sustituirá el parámetro 2 (en este caso, una cadena vacía), y si no ocurre ningún error, esta función pretenderá que no existe en absoluto, pero solo hay BUSCARV que devolvió un resultado normal.

Formación

A menudo se olvidan de hacer que la referencia de la matriz sea absoluta y, al estirar la matriz, "flota". Recuerde usar $ A $ 2: $ C $ 5 en lugar de A2: C5.

Es una buena idea colocar la matriz de referencia en una hoja separada del libro de trabajo. No se mete bajo los pies y será más seguro.

Una idea aún mejor sería declarar esta matriz como un rango con nombre.

Muchos usuarios, al especificar una matriz, utilizan una construcción como A: C, especificando columnas enteras. Este enfoque tiene derecho a existir, ya que se evita tener que realizar un seguimiento del hecho de que su matriz incluye todas las cadenas necesarias. Si agrega filas a la hoja con la matriz original, no es necesario ajustar el rango especificado como A: C. Por supuesto, esta construcción sintáctica obliga a Excel a hacer un poco más de trabajo que especificar el rango exactamente, pero esta sobrecarga puede pasarse por alto. Estamos hablando de centésimas de segundo.

Bueno, al borde de la genialidad: organizar la matriz en la forma.

Usando la función COLUMNA para especificar la columna a extraer

Si la tabla en la que está recuperando datos usando BUSCARV tiene la misma estructura que la tabla de búsqueda, pero simplemente contiene menos filas, entonces puede usar la función COLUMN () en BUSCARV para calcular automáticamente el número de columnas que se recuperarán. En este caso, todas las fórmulas BUSCARV serán las mismas (ajustadas para el primer parámetro, que cambia automáticamente). Tenga en cuenta que el primer parámetro tiene una coordenada de columna absoluta.

Crear una clave compuesta con & "|" &

Si es necesario buscar por varias columnas al mismo tiempo, entonces es necesario hacer una clave compuesta para la búsqueda. Si el valor de retorno no fuera textual (como es el caso del campo "Código"), sino numérico, entonces la fórmula SUMIFS más conveniente sería adecuada para esto y la clave de columna compuesta no sería necesaria en absoluto.

Este es mi primer artículo para un Lifehacker. Si te gustó, te invito a que me visites, y con mucho gusto también leas en los comentarios sobre tus secretos para usar la función BUSCARV y similares. Gracias.:)