Buscar coincidencia más cercana
Para buscar la coincidencia más cercana de un valor de búsqueda en un conjunto de datos numéricos en Excel, puede usar el ÍNDICE, PARTIDO, ABS y MIN funcionan juntos.
¿Cómo encontrar la coincidencia más cercana en Excel?
Para saber cual El vendedor hizo las ventas más cercanas al objetivo de $ 20,000 Como se muestra arriba, una fórmula que combina las funciones INDEX, MATCH, ABS y MIN lo ayudará de esta manera: La función ABS hará que todos los valores de diferencia entre las ventas realizadas por cada vendedor y el objetivo de ventas sean positivos, luego MIN encontrará el la diferencia más pequeña, lo que significa que el armario coincide. Ahora podemos usar la función COINCIDIR para localizar la posición de la coincidencia del armario y tener ÍNDICE para recuperar el valor en la posición 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 de matriz que requiere que ingrese con Ctrl + cambio + Enviar.
- rango_retorno: El rango desde el que desea que la fórmula de combinación devuelva el vendedor. Aquí se refiere al rango de nombres.
- matriz_buscada: El rango de celdas con los valores para comparar con el valor de búsqueda. Aquí se refiere al rango de ventas.
- valor de búsqueda: El valor con el que comparar para encontrar su coincidencia más cercana. Aquí se refiere al objetivo de ventas.
Para saber cual El vendedor hizo las ventas más cercanas al objetivo de $ 20,000, copie o ingrese la fórmula a continuación en la celda F5 y presione Ctrl + cambio + Enviar para obtener el resultado:
= ÍNDICE (B5: B10, PARTIDO (MIN (ABS (C5: C10-20000)),ABDOMINALES(C5: C10-20000), 0))
O use una referencia de celda para hacer que la fórmula sea dinámica:
= ÍNDICE (B5: B10, PARTIDO (MIN (ABS (C5: C10-F4)),ABDOMINALES(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 obtendrá todos los valores de diferencia entre cada venta en el rango C5: C10 y las ventas objetivo $20,000 en la celda F4 en una matriz como esta: {-4322; 2451; 6931; -1113; 6591; -4782}. La función ABS convertirá todos los números negativos en positivos de esta manera: {4322;2451;6931;1113;6591;4782}.
- MIN (ABS (C5: C10-F4)) = MIN ({4322;2451;6931;1113;6591;4782}): La función MIN encontrará el número más pequeño de la matriz {4322;2451;6931;1113;6591;4782}, lo que significa la diferencia más pequeña, o decimos que coinciden con el armario. Entonces la función volverá 1113.
- PARTIDO(MIN (ABS (C5: C10-F4)),ABS (C5: C10-F4), 0) = PARTIDO(1113,{4322;2451;6931;1113;6591;4782}, 0): El tipo_de_coincidencia 0 fuerza a la función COINCIDIR a encontrar la posición del número exacto 1113 en la matriz {4322;2451;6931;1113;6591;4782}. La función volverá 4 ya que el número está en el 4posición.
- ÍNDICE(B5: B10,PARTIDO(MIN (ABS (C5: C10-F4)),ABS (C5: C10-F4), 0)) = ÍNDICE (B5: B10,4): La función INDICE devuelve el 4th valor en el rango de nombres B5: B10, cual es Bala.
Funciones relacionadas
La función ÍNDICE de Excel devuelve el valor mostrado en función de una posición determinada 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 del valor.
La función ABS devuelve el valor absoluto de un número. Los números negativos se convertirán en positivos con esta función, pero los números positivos y el número cero no se verán afectados.
Fórmulas relacionadas
Busque el valor de coincidencia más cercano con varios criterios
En algunos casos, es posible que deba buscar el valor de coincidencia más cercano o aproximado en función de más de un criterio. Con la combinación de las funciones INDICE, MATCH e IF, puede hacerlo rápidamente en Excel.
Coincidencia aproximada con INDEX y MATCH
Hay ocasiones en las que necesitamos encontrar coincidencias aproximadas en Excel para evaluar el desempeño de los empleados, calificar los puntajes de los estudiantes, calcular el franqueo en función del peso, etc. En este tutorial, hablaremos sobre cómo usar las funciones INDICE y MATCH para recuperar el resultados que necesitamos.
Busque el valor de coincidencia más cercano con varios criterios
En algunos casos, es posible que deba buscar el valor de coincidencia más cercano o aproximado en función de más de un criterio. Con la combinación de las funciones INDICE, MATCH e IF, puede hacerlo rápidamente en Excel.
Las mejores herramientas de productividad de oficina
Kutools para Excel: te ayuda a destacar entre la multitud
¿Le gustaría completar su trabajo diario de forma rápida y perfecta? Kutools for Excel ofrece 300 potentes funciones avanzadas (combinar libros de trabajo, sumar por color, dividir contenido de celdas, convertir fecha, etc.) y ahorrarle un 80% de tiempo.
- Diseñado para 1500 escenarios de trabajo, te ayuda a resolver el 80% de problemas de Excel.
- Reduzca miles de clics de teclado y mouse todos los días, alivie sus ojos y manos cansados.
- Conviértete en un experto en Excel en 3 minutos. Ya no es necesario recordar fórmulas dolorosas y códigos VBA.
- Prueba gratuita ilimitada de 30 días. Garantía de devolución de dinero de 60 días. Actualización y soporte gratuitos durante 2 años.
Ficha Office: habilite la lectura y edición con pestañas en Microsoft Office (incluya Excel)
- ¡Un segundo para cambiar entre docenas de documentos abiertos!
- Reduzca cientos de clics del mouse para usted todos los días, diga adiós a la mano del mouse.
- Aumenta su productividad en un 50% al ver y editar varios documentos.
- Trae pestañas eficientes a Office (incluye Excel), al igual que Chrome, Firefox y el nuevo Internet Explorer.
