Las funciones de Excel pueden ser extremadamente poderosas si se le coge el truco a la combinación de diferentes fórmulas. Cosas que podrían haber parecido imposibles de repente empezarían a parecer un juego de niños.
Un ejemplo de ello es encontrar la coincidencia más cercana de un valor de búsqueda en un conjunto de datos de Excel.
Hay un par de útiles funciones de búsqueda en Excel (como VLOOKUP & INDEX MATCH), que pueden encontrar la coincidencia más cercana en unos pocos casos sencillos (como mostraré con ejemplos a continuación).
Pero lo mejor es que puede combinar estas funciones de búsqueda con otras funciones de Excel para hacer mucho más (incluyendo la búsqueda de la coincidencia más cercana de un valor de búsqueda en una lista no ordenada).
En este tutorial, te mostraré cómo encontrar la correspondencia más cercana de un valor de búsqueda en Excel con las fórmulas de búsqueda.
Glosario
Encuentra la coincidencia más cercana en Excel
Puede haber muchos escenarios diferentes en los que hay que buscar la coincidencia más cercana (o el valor de coincidencia más cercano).
A continuación, los ejemplos que trataré en este artículo:
- Encuentra la tasa de comisión basada en las ventas
- Encuentra el mejor candidato (basado en la experiencia más cercana)
- Encontrar la fecha del próximo evento
¡Empecemos!
Haga clic aquí para descargar el archivo de ejemplo
Encuentra la tasa de comisión (Buscando el valor de venta más cercano)
Suponga que tiene un conjunto de datos como el que se muestra a continuación donde quiere encontrar las tasas de comisión de todo el personal de ventas.
La comisión se asigna en base al valor de venta. Y esto se calcula usando la tabla de la derecha.
Por ejemplo, si un vendedor hace el total de las ventas de 5000, entonces la comisión es del 0% y si hace el total de las ventas de 15000 entonces la comisión es del 5%.
Para obtener la tasa de comisión, necesitas encontrar el rango de venta más cercano justo por debajo del valor de venta. Por ejemplo, para un valor de venta de 15000, la comisión sería de 10.000 (que es el 5%) y para un valor de venta de 25000, la tasa de comisión sería de 20.000 (que es el 7%).
Para encontrar el valor de venta más cercano y obtener la tasa de comisión, puedes usar la coincidencia aproximada en VLOOKUP.
La siguiente fórmula haría esto:
=VLOOKUP(B2,$E$2:$F$6,2,1)
Nótese que en esta fórmula, el último argumento es 1, lo que le dice a la fórmula que use una búsqueda aproximada. Esto significa que la fórmula revisaría los valores de venta de la columna E y encontraría el valor que es justo menor que el valor de búsqueda.
Entonces la fórmula de VLOOKUP dará la tasa de comisión para este valor.
Nota : Para que esto funcione, es necesario tener los datos ordenados en orden ascendente.
Haga clic aquí para descargar el archivo de ejemplo
Encuentra el mejor candidato (basado en la experiencia más cercana)
En el ejemplo anterior, los datos debían clasificarse en orden ascendente. Pero podría haber casos en los que los datos no se clasifican.
Así que vamos a cubrir un ejemplo y ver cómo podemos encontrar la coincidencia más cercana en Excel usando una combinación de fórmulas.
A continuación se muestra un conjunto de datos de muestra donde necesito encontrar el nombre del empleado que tiene la experiencia laboral más cercana al valor deseado. El valor deseado en este caso en 2,5 años.
Tenga en cuenta que los datos no están ordenados. Además, la experiencia más cercana puede ser menor o mayor que la experiencia dada. Por ejemplo, 2 años y 3 años son ambos igualmente cercanos (diferencia de 0,5 años).
A continuación la fórmula que nos dará el resultado:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
El truco de esta fórmula es cambiar el conjunto de búsqueda y el valor de búsqueda para encontrar la mínima diferencia de experiencia entre los valores requeridos y los reales.
Primero entendamos cómo lo harías manualmente (y luego explicaré cómo funciona esta fórmula).
Al hacer esto manualmente, se revisará cada celda de la columna B y se encontrará la diferencia de experiencia entre lo que se requiere y lo que tiene una persona. Una vez que tengas todas las diferencias, encontrarás la mínima y buscarás el nombre de esa persona.
Esto es exactamente lo que estamos haciendo con esta fórmula.
Déjeme explicarle.
El valor de búsqueda en la fórmula de MATCH es MIN(ABS(D2-B2:B15)).
Esta parte le da la diferencia mínima entre la experiencia dada (que es de 2,5 años) y todas las demás experiencias. En este ejemplo, devuelve 0,3
Tenga en cuenta que he utilizado el ABS para asegurarme de que estoy buscando lo más cercano (que puede ser más o menos que la experiencia dada).
Ahora, este valor mínimo se convierte en nuestro valor de búsqueda.
La matriz de búsqueda en la función MATCH es ABS(D2-$B$2:$B$15).
Esto nos da una serie de números de los que se ha restado 2,5 (la experiencia requerida).
Así que ahora tenemos un valor de búsqueda (0,3) y una matriz de búsqueda ({6,8;0,8;19,5;21,8;14,5;11,2;0,3;9,2;2;9,8;14,8;0,4;23,8;2,9})
La función MATCH encuentra la posición de 0,3 en esta matriz, que es también la posición del nombre de la persona que tiene la experiencia más cercana.
Este número de posición es usado por la función de ÍNDICE para devolver el nombre de la persona.
Nota: En caso de que haya varios candidatos que tengan la misma experiencia mínima, la fórmula anterior dará el nombre del primer empleado que coincida.
Encuentra la fecha del próximo evento
Este es otro ejemplo en el que se pueden utilizar fórmulas de búsqueda para encontrar la próxima fecha de un evento basado en la fecha actual.
Abajo está el conjunto de datos donde tengo los nombres de los eventos y las fechas de los mismos.
656q0-1.92 0.672-3.616t1.856-2.976 2.72-2.208 3.296-1.408 3.616-0.448q2.816 0 5.248 1.184t3.936 3.456 1.504 5.12q0 1. 728-0,32 3,36t-1,088 3,168-1,792 2,656-2,56 1,856-3,392 0,672q-1,216 0-2,4-0,576t-1,728-1,568q-0,16 0,704-0,48 2,016t-0. 448 1.696-0.352 1.28-0.48 1.248-0.544 1.12-0.832 1.408-1.12 1.536l-0.224 0.096-0.16-0.192q-0.288-2.816-0.288-3.36 0-1.632 0. 384-3,68t1,184-5,152 0,928-3,616q-0,576-1,152-0,576-3,008 0-1,504 0,928-2,784t2,368-1,312q1,088 0 1,696 0,736t0,608 1,824q0 1.