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

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

Puede usar funciones de Excel para:

• Encuentre valores en un rango de datos: BUSCARV y BUSCARH
• Obtener valor o referencia al valor de la tabla o rango – ÍNDICE
• Obtener la posición relativa del elemento especificado en un rango de celdas – BUSCAR

También puede combinar estas funciones para obtener los resultados que desea según la entrada que tenga.

Uso de la función BUSCARV

Sintaxis de la función BUSCARV:

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

Dónde

• valor de búsqueda es el valor que desea encontrar. Lookup_value puede ser un valor o una referencia de celda. Lookup_value debe estar en la primera columna del rango de celdas que especifique en table_array.

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

  • lookup_value en la primera columna y

  • el valor de retorno que desea encontrar

    Nota – La primera columna que contiene lookup_value se puede ordenar en orden ascendente o no. Sin embargo, el resultado dependerá del orden en esa columna.

• col_index_num es el número de la columna en table_array que contiene el valor de retorno. Los números comienzan con 1 en la columna más a la izquierda de la matriz de tablas.

• rango de búsqueda : un valor booleano opcional que indica si desea que la función 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 alto, que es menor que lookup_value.

  • FALSO, en este caso la función BUSCARV intenta encontrar una coincidencia exacta.

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

  • 0, en este caso se asume que es FALSO y BUSCARV intenta encontrar una coincidencia exacta.

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

Usando VLOOKUP con range_lookup TRUE

Revise la lista de calificaciones de los estudiantes. Puede utilizar BUSCARV para recuperar las puntuaciones correspondientes de una matriz que contiene los intervalos de puntuación y la categoría de aprobación.

matriz de tabla –

Tenga en cuenta que las etiquetas de la primera columna, de la que se derivan las puntuaciones, están ordenadas en orden ascendente. Por lo tanto, al usar TRUE para el argumento range_lookup, puede obtener una coincidencia aproximada, que es exactamente lo que desea.

Llame a esta matriz como Evaluaciones…

Se recomienda nombrar las matrices de tal manera que no recuerde los rangos de celdas. Ahora está listo para encontrar la calificación en la lista de calificaciones que tiene:

Como se puede ver,

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

• en rango de búsqueda cierto

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

  • También puede obtener el valor de retorno de coincidencias aproximadas. aquellos. VLOOKUP se calcula de la siguiente manera:

Obtendrá los siguientes resultados:

Usando VLOOKUP con range_lookup FALSE

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

matriz de tabla –

Denomine esta matriz ProductInfo.

Puede obtener el precio de un producto por ID de producto utilizando BUSCARV, ya que la ID de producto está en la primera columna. El precio está en la columna 3, por lo que col_index_ num debe ser 3.

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

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

Te das cuenta de que tienes…

• Resultado correcto cuando range_lookup es FALSE y
• Resultado no válido 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 si 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

Tome la información del producto, por ejemplo. Supongamos que la matriz se parece a la siguiente:

• Denomine esta matriz ProductRange. Puede averiguar el precio de un producto por el ID del producto utilizando la función HLOOKUP.

La sintaxis de la función HLOOKUP es:

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

Dónde

• valor de búsqueda – el valor que se encuentra en la primera fila de la tabla

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

• row_index_num – número de línea en table_array desde el que se devolverá el valor coincidente

• rango de búsqueda – un valor booleano que indica si desea que BUSCARH encuentre una coincidencia exacta o aproximada.

• rango de búsqueda posiblemente

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

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

  • FALSE, en cuyo caso BUSCARH intenta encontrar una coincidencia exacta.

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

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

Nota – Si se omite range_lookup, TRUE o 1, HLOOKUP solo funciona correctamente cuando la primera columna de table_array está ordenada en orden ascendente. De lo contrario, puede dar lugar a 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 a partir de un ID de producto utilizando la función HLOOKUP ya que el ID del producto está en la primera línea. El precio está en la línea 3, por lo que row_index_ num debe ser 3.

• Utilice la función HLOOKUP con range_lookup 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.

Ves que, al igual que con BUSCARV, tienes

• Resultado correcto cuando range_lookup es FALSE y

• Resultado no válido cuando range_lookup es TRUE.

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

Usando la función HLOOKUP con range_lookup TRUE

Considere un ejemplo de las calificaciones de los estudiantes utilizadas en BUSCARV. Suponga que tiene datos en filas y no en columnas como se muestra en la siguiente tabla:

matriz de tabla –

Nombra esta matriz GradesRange.

Tenga en cuenta que las marcas de la primera línea de las que se derivan las puntuaciones se ordenan en orden ascendente. Por lo tanto, al usar HLOOKUP con TRUE para el argumento range_lookup, puede obtener estimaciones con una coincidencia aproximada, que es lo que se requiere.

Como se puede ver,

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

• en rango de búsqueda cierto

  • 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 de coincidencias aproximadas. aquellos. HLOOKUP calcula de la siguiente manera:

Obtendrá los siguientes resultados:

Uso de la función INDICE

Una vez que tenga una matriz de datos, puede obtener el 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, en los que encontrará las ventas en cada una de las regiones Norte, Sur, Este y Oeste de los vendedores enumerados.

• Denomine la matriz SalesData.

Usando la función ÍNDICE, puede encontrar:

• Ventas de cualquiera de los Vendedores en una región específica.
• Ventas totales en la región por todos los vendedores.
• Las ventas totales del vendedor en todas las regiones.

Obtendrá los siguientes resultados:

Supongamos que no conoce los números de línea 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 usando la función de índice.

Puede hacer esto usando la función COINCIDIR como se describe en la siguiente sección.

Usar 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 de BÚSQUEDA e ÍNDICE de la siguiente manera:

Obtendrá los siguientes resultados: