Análisis de datos de Excel: funciones de búsqueda

Puede utilizar las funciones de Excel para:

  • Encuentre valores en un rango de datos: VLOOKUP y HLOOKUP
  • Obtener un valor o la referencia a un valor dentro de una tabla o rango: ÍNDICE
  • Obtenga la posición relativa de un elemento especificado en un rango de celdas - COINCIDIR

También puede combinar estas funciones para obtener los resultados requeridos según las entradas que tenga.

Uso de la función BUSCARV

La sintaxis de la función BUSCARV es

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Dónde

  • lookup_value- es el valor que desea buscar. Lookup_value puede ser un valor o una referencia a una celda. Lookup_value debe estar en la primera columna del rango de celdas que especifique en table_array

  • table_array- es el rango de celdas en el que BUSCARV buscará el valor_buscado y el valor de retorno. table_array debe contener

    • el lookup_value en la primera columna, y

    • el valor de retorno que desea encontrar

      Note- La primera columna que contiene lookup_value se puede ordenar en orden ascendente o no. Sin embargo, el resultado se basará en el orden de esta columna.

  • col_index_num- es el número de columna de table_array que contiene el valor de retorno. Los números comienzan con 1 para la columna más a la izquierda de table-array

  • range_lookup- es un valor lógico opcional que especifica si desea que BUSCARV encuentre una coincidencia exacta o aproximada. range_lookup puede ser

    • omitido, en cuyo caso se supone que es TRUE y BUSCARV intenta encontrar una coincidencia aproximada

    • TRUE, en cuyo caso BUSCARV intenta encontrar una coincidencia aproximada. En otras palabras, si no se encuentra una coincidencia exacta, se devuelve el siguiente valor más grande que sea menor que lookup_value

    • FALSO, en cuyo caso BUSCARV intenta encontrar una coincidencia exacta

    • 1, en cuyo caso se asume que es TRUE y BUSCARV intenta encontrar una coincidencia aproximada

    • 0, en cuyo caso se supone que es FALSO y BUSCARV intenta encontrar una coincidencia exacta

Note- Si se omite range_lookup o TRUE o 1, VLOOKUP funciona correctamente solo cuando la primera columna de table_array está ordenada en orden ascendente. De lo contrario, puede resultar en valores incorrectos. En tal caso, use FALSE para range_lookup.

Usando la función BUSCARV con range_lookup TRUE

Considere una lista de calificaciones de los estudiantes. Puede obtener las calificaciones correspondientes con BUSCARV de una matriz que contiene los intervalos de calificaciones y la categoría de aprobación.

table_array -

Tenga en cuenta que las marcas de la primera columna en función de las cuales se obtienen las calificaciones se clasifican en orden ascendente. Por lo tanto, al usar TRUE para el argumento range_lookup, puede obtener una coincidencia aproximada que es lo que se requiere.

Nombra esta matriz como Grades.

Es una buena práctica nombrar las matrices de esta manera para que no necesite recordar los rangos de celdas. Ahora, está listo para buscar la calificación de la lista de calificaciones que tiene de la siguiente manera:

Como puedes observar,

  • col_index_num - indica que la columna del valor de retorno en table_array es 2

  • la range_lookup es verdad

    • La primera columna que contiene el valor de búsqueda en las calificaciones table_array está en orden ascendente. Por tanto, los resultados serán correctos.

    • También puede obtener el valor de retorno para coincidencias aproximadas. es decir, VLOOKUP calcula de la siguiente manera:

Marcas Categoría de pase
<35 Fallar
> = 35 y <50 Tercera clase
> = 50 y <60 Segunda clase
> = 60 y <75 Primera clase
> = 75 Primera clase con distinción

Obtendrá los siguientes resultados:

Usando la función VLOOKUP con range_lookup FALSE

Considere una lista de productos que contenga el ID de producto y el precio de cada uno de los productos. El ID del producto y el precio se agregarán al final de la lista cada vez que se lance un nuevo producto. Esto significaría que los ID de producto no necesitan estar en orden ascendente. La lista de productos puede ser la que se muestra a continuación:

table_array -

Nombra esta matriz como ProductInfo.

Puede obtener el precio de un producto dado el ID del producto con la función BUSCARV, ya que el ID del producto está en la primera columna. El precio está en la columna 3 y, por lo tanto, col_index_ num debería ser 3.

  • Utilice la función VLOOKUP con range_lookup como TRUE
  • Utilice la función VLOOKUP con range_lookup como FALSE

La respuesta correcta es de la matriz ProductInfo es 171,65. Puedes comprobar los resultados.

Observas que tienes ...

  • El resultado correcto cuando range_lookup es FALSE, y
  • Un resultado incorrecto cuando range_lookup es TRUE.

Esto se debe a que la primera columna de la matriz ProductInfo no está ordenada en orden ascendente. Por lo tanto, recuerde usar FALSE siempre que los datos no estén ordenados.

Uso de la función HLOOKUP

Puedes usar HLOOKUP función si los datos están en filas en lugar de columnas.

Ejemplo

Tomemos el ejemplo de la información del producto. Supongamos que la matriz tiene el siguiente aspecto:

  • Denomine este Array ProductRange. Puede encontrar el precio de un producto dado el ID del producto con la función HLOOKUP.

La sintaxis de la función HLOOKUP es

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Dónde

  • lookup_value - es el valor que se encuentra en la primera fila de la tabla

  • table_array - es una tabla de información en la que se buscan datos

  • row_index_num - es el número de fila en table_array desde el que se devolverá el valor coincidente

  • range_lookup - es un valor lógico que especifica si desea que BUSCARH encuentre una coincidencia exacta o aproximada

  • range_lookup puede ser

    • omitido, en cuyo caso se supone que es TRUE y HLOOKUP intenta encontrar una coincidencia aproximada

    • TRUE, en cuyo caso HLOOKUP intenta encontrar una coincidencia aproximada. En otras palabras, si no se encuentra una coincidencia exacta, se devuelve el siguiente valor más grande que sea menor que lookup_value

    • FALSE, en cuyo caso HLOOKUP intenta encontrar una coincidencia exacta

    • 1, en cuyo caso se supone que es TRUE y HLOOKUP intenta encontrar una coincidencia aproximada

    • 0, en cuyo caso se supone que es FALSE y HLOOKUP intenta encontrar una coincidencia exacta

Note- Si range_lookup se omite o es TRUE o 1, HLOOKUP funciona correctamente solo cuando la primera columna de table_array se ordena en orden ascendente. De lo contrario, puede resultar en valores incorrectos. En tal caso, use FALSE para range_lookup.

Usando la función HLOOKUP con range_lookup FALSE

Puede obtener el precio de un producto dado el ID del producto con la función HLOOKUP ya que el ID del producto está en la primera fila. El precio está en la fila 3 y, por lo tanto, row_index_ num debería ser 3.

  • Utilice la función HLOOKUP con range_lookup como TRUE.
  • Utilice la función HLOOKUP con range_lookup como FALSE.

La respuesta correcta de la matriz ProductRange es 171,65. Puedes comprobar los resultados.

Observa que, como en el caso de VLOOKUP, tiene

  • El resultado correcto cuando range_lookup es FALSE, y

  • Un resultado incorrecto cuando range_lookup es TRUE.

Esto se debe a que la primera fila de la matriz ProductRange no está ordenada en orden ascendente. Por lo tanto, recuerde usar FALSE siempre que los datos no estén ordenados.

Usando la función HLOOKUP con range_lookup TRUE

Considere el ejemplo de las calificaciones de los estudiantes que se usan en BUSCARV. Suponga que tiene los datos en filas en lugar de columnas como se muestra en la tabla que se muestra a continuación:

table_array -

Nombra esta matriz como GradesRange.

Tenga en cuenta que las marcas de la primera fila en función de las cuales se obtienen las calificaciones se clasifican en orden ascendente. Por lo tanto, usando HLOOKUP con TRUE para el argumento range_lookup, puede obtener las calificaciones con coincidencia aproximada y eso es lo que se requiere.

Como puedes observar,

  • row_index_num - indica que la columna del valor de retorno en table_array es 2

  • la range_lookup es verdad

    • La primera columna que contiene el valor de búsqueda en table_array Grades está en orden ascendente. Por tanto, los resultados serán correctos.

    • También puede obtener el valor de retorno para coincidencias aproximadas. es decir, HLOOKUP calcula de la siguiente manera:

Marcas <35 > = 35 y <50 > = 50 y <60 > = 60 y <75 > = 75
Categoría de pase Fallar Tercera clase Segunda clase Primera clase Primera clase con distinción

Obtendrá los siguientes resultados:

Uso de la función INDICE

Cuando tiene una matriz de datos, puede recuperar un valor en la matriz especificando el número de fila y el número de columna de ese valor en la matriz.

Considere los siguientes datos de ventas, donde encontrará las ventas en cada una de las regiones Norte, Sur, Este y Oeste por los vendedores que se enumeran.

  • Nombra la matriz como SalesData.

Usando la función INDICE, puede encontrar -

  • Las ventas de cualquiera de los vendedores en una determinada región.
  • Ventas Totales en una Región por todos los Vendedores.
  • Ventas totales de un vendedor en todas las regiones.

Obtendrá los siguientes resultados:

Suponga que no conoce los números de fila de los vendedores y los números de columna de las regiones. Luego, primero debe encontrar el número de fila y el número de columna antes de recuperar el valor con la función de índice.

Puede hacerlo con la función COINCIDIR como se explica en la siguiente sección.

Uso de la función COINCIDIR

Si necesita la posición de un elemento en un rango, puede usar la función COINCIDIR. Puede combinar las funciones COINCIDIR e INDICE de la siguiente manera:

Obtendrá los siguientes resultados: