Buscar coincidencia más cercana
Para buscar la coincidencia más cercana de un valor en un conjunto de datos numéricos en Excel, puede combinar las funciones INDICE, COINCIDIR, ABS y MIN.
¿Cómo encontrar la coincidencia más cercana en Excel?
Para identificar al vendedor cuyas ventas estén más cercanas al objetivo de $20,000, como se muestra arriba, puede usar una fórmula que combina las funciones INDICE, COINCIDIR, ABS y MIN de la siguiente manera: la función ABS convierte en valores positivos todas las diferencias entre las ventas de cada vendedor y el objetivo; a continuación, MIN identifica la diferencia más pequeña, es decir, la coincidencia más cercana. Con esa información, la función COINCIDIR localiza la posición de dicha coincidencia y permite que INDICE recupere el valor correspondiente.
Sintaxis genérica
=INDEX()return_range,MATCH(MIN(ABS()))lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))
√ Nota: Esta es una fórmula matricial que requiere pulsar Ctrl+Mayús+Intro.
- rango_de_devolución: El rango en el que desea que la fórmula de combinación devuelva al vendedor. Aquí se refiere al rango de nombres.
- matriz_de_búsqueda: El rango de celdas que contiene los valores con los que comparar el valor_de_búsqueda. En este caso, se refiere al rango de ventas.
- valor_de_búsqueda: El valor con el que se compara para encontrar su coincidencia más cercana. En este caso, se refiere al objetivo de ventas.
Para saber qué vendedor realizó las ventas más cercanas al objetivo de $20,000, copie o introduzca la fórmula siguiente en la celda F5 y pulse Ctrl+Mayús+Intropara obtener el resultado:
=INDICE()B5:B10,COINCIDIR(MIN(ABS()))C5:C10-20000)),ABS(C5:C10-20000),0))
O bien, use Una referencia de celda para hacer la fórmula dinámica:
=INDICE()B5:B10,COINCIDIR(MIN(ABS()))C5:C10-F4)),ABS(C5:C10-F4),0))
Explicación de la fórmula
=INDEX()B5:B10,MATCH()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4),0))
- ABS(C5:C10-F4): La parte C5:C10-F4 obtiene todos los valores de diferencia entre cada venta del rango C5:C10 y las ventas objetivo $20,000 en la celda F4, generando una matriz como esta: {-4322;2451;6931;-1113;6591;-4782}. La función ABS convierte todos los números negativos en positivos, quedando así: {4322;2451;6931;1113;6591;4782}.
- MIN()ABS(C5:C10-F4))=MIN(){4322;2451;6931;1113;6591;4782}):La función MIN identifica el número más pequeño de la matriz {4322;2451;6931;1113;6591;4782}, es decir, la diferencia más pequeña o, como solemos llamarla, la coincidencia más cercana. Por tanto, la función devuelve 1113.
- COINCIDIR()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)El tipo_de_coincidencia 0 obliga a la función COINCIDIR a encontrar la posición exacta del número 1113 en la matriz {4322;2451;6931;1113;6591;4782}. La función devolverá 4, ya que el número se encuentra en la 4ª posición.
- INDICE()B5:B10,COINCIDIR()MIN()ABS(C5:C10-F4)),ABS(C5:C10-F4)La función INDICE devuelve el 4.º valor del rango de nombres B5:B10, que es Bale.
Funciones relacionadas
La función INDICE de Excel devuelve el Valor Mostrado en función de una posición dada dentro de un rango o una matriz.
La función COINCIDIR de Excel busca un valor específico en un rango de celdas y devuelve la posición relativa de dicho valor.
La función ABS devuelve el Valor absoluto de un número. Los números negativos se convierten en positivos con esta función, pero los números positivos y el cero no se ven afectados.
Fórmulas relacionadas
Buscar el valor de coincidencia más cercana con varios criterios
En algunos casos, puede necesitar buscar el valor de coincidencia más cercana o aproximada en función de más de un criterio. Con la combinación de las funciones INDICE, COINCIDIR y SI, puede lograrlo rápidamente en Excel.
Coincidencia aproximada con INDICE y COINCIDIR
Hay ocasiones en las que necesitamos encontrar coincidencias aproximadas en Excel para evaluar el rendimiento de los empleados, calificar las puntuaciones de los estudiantes, calcular gastos de envío según el peso, etc. En este tutorial explicaremos cómo utilizar las funciones INDICE y COINCIDIR para obtener los resultados deseados.
Buscar el valor de coincidencia más cercana con varios criterios
En algunos casos, puede necesitar buscar el valor de coincidencia más cercana o aproximada en función de más de un criterio. Con la combinación de las funciones INDICE, COINCIDIR y SI, puede lograrlo rápidamente en Excel.
Las mejores herramientas de productividad para Office
Kutools para Excel - Le ayuda a destacar entre la multitud
Kutools para Excel Cuenta con más de 300 funciones,asegurando que lo que necesita esté siempre a un clic...
Office Tab - Habilita la lectura y edición con pestañas en Microsoft Office (incluido Excel)
- ¡Alterne entre decenas de documentos abiertos en un segundo!
- Reduzca cientos de clics del ratón cada día y despídase del síndrome del ratón.
- Aumente su productividad en un 50 % al ver y editar varios documentos simultáneamente.
- Lleva una navegación eficiente con pestañas a Office (incluido Excel), al estilo de Chrome, Edge y Firefox.