Coincidencia más cercana a Excel mediante fórmulas

Las funciones de Excel pueden ser extremadamente potentes si os gusta combinar diferentes fórmulas. Las cosas que podrían parecer imposibles de pronto empezarían a parecer un juego infantil.

Uno de estos ejemplos es encontrar la concordancia más cercana de un valor de búsqueda en un conjunto de datos a Excel.

Hay un par de funciones de búsqueda útiles a Excel (como VLOOKUP & INDEX MATCH), que pueden encontrar la coincidencia más cercana en algunos casos simples (como mostraré con ejemplos a continuación).

Pero la mejor parte es que puede combinar estas funciones de búsqueda con otras funciones de Excel para hacer mucho más (incluida la búsqueda de la coincidencia más cercana de un valor de búsqueda en una lista sin clasificar).

En este tutorial, te mostraré cómo encuentra la concordancia más cercana de un valor de búsqueda en Excel con fórmulas de búsqueda.

Busque la coincidencia más cercana a Excel

Puede haber muchos escenarios diferentes en los que tendrá que buscar la concordancia más cercana (o el valor de concordancia más cercano).

A continuación se muestran los ejemplos que trataré en este artículo:

  1. Busque la tasa de comisión en función de las ventas
  2. Encuentre el mejor candidato (según la experiencia más cercana)
  3. Buscando la próxima fecha del evento

Empezamos!

Encuentra una tasa de comisión (busque el valor de ventas más cercano)

Supongamos que tiene un conjunto de datos como se muestra a continuación, donde desea encontrar las tasas de comisiones de todo el personal de ventas.

Busque Match Match más cercano a Excel: datos de ventas

La comisión se asigna en función del valor de venta. Y esto se calcula mediante la tabla de la derecha.

Por ejemplo, si un vendedor realiza las ventas totales de 5.000, la comisión es del 0% y si hace las ventas totales de 15.000, la comisión es del 5%.

Para obtener la tasa de comisión, debe encontrar el intervalo de venta más cercano justo inferior al valor de venta. Por ejemplo, para el valor de venta de 15.000, la comisión sería de 10.000 (que es el 5%) y, para el valor de venta de 25.000, la tasa de comisión sería de 20.000 (que es del 7%).

Para encontrar el valor de venta más cercano y obtener la tasa de comisión, puede utilizar la concordancia aproximada a VLOOKUP.

La fórmula siguiente haría esto:

=VLOOKUP(B2,$E$2:$F$6,2,1)

Fórmula VLOOKUP para obtener el tipo de comisión

Tenga en cuenta que en esta fórmula, el último argumento es 1, que indica a la fórmula que utilice una búsqueda aproximada. Esto significa que la fórmula pasaría por los valores de venta de la columna E y encontraría el valor que es justo inferior al valor de búsqueda.

A continuación, la fórmula VLOOKUP dará la tasa de comisión por este valor.

Nota: Para que esto funcione, es necesario que los datos se ordenen en orden ascendente.

Encuentre el mejor candidato (según la experiencia más cercana)

En el ejemplo anterior, había que ordenar los datos en orden ascendente. Pero puede haber casos en que los datos no estén ordenadas.

Demos un ejemplo y veamos cómo podemos encontrar la coincidencia más cercana a Excel mediante una combinación de fórmulas.

A continuación se muestra un conjunto de datos de muestra donde tengo que encontrar el nombre del empleado que tenga la experiencia laboral más cercana al valor deseado. El valor deseado en este caso en 2,5 años.

Encuentra la coincidencia más cercana: conjunto de datos

Tenga en cuenta que los datos no están ordenadas. Además, la experiencia más cercana puede ser menor o más que la experiencia de dar. Por ejemplo, 2 años y 3 años son igualmente cercanos (diferencia de 0,5 años).

A continuación se muestra 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))

fórmula para encontrar la coincidencia más cercana según la experiencia

El truco de esta fórmula es cambiar la matriz de búsqueda y el valor de búsqueda para encontrar la diferencia mínima de experiencia en los valores reales y requeridos.

Primero entendemos como lo haría manualmente (y después explicaré cómo funciona esta fórmula).

Al hacerlo manualmente, pasará por cada celda de la columna B y encontrará la diferencia en la experiencia entre lo que hay y lo que tiene una persona. Una vez que tenga todas las diferencias, encontrará la mínima y obtendrá el nombre de esta persona.

Esto es exactamente lo que estamos haciendo con esta fórmula.

Déjame explicar.

El valor de búsqueda en la fórmula MATCH es MIN (ABS (D2-B2: B15)).

Esta parte proporciona la diferencia mínima entre la experiencia dada (que es de 2,5 años) y el resto de experiencias. En este ejemplo, devuelve 0,3

Tenga en cuenta que he utilizado el ABS para asegurarme de que estoy buscando el 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 de la función MATCH es ABS (D2- $ B $ 2: $ B $ 15).

Esto nos proporciona una serie de números de los que se ha restado 2,5 (la experiencia necesaria).

Por tanto, 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 también es la posición del nombre de la persona que tiene la experiencia más cercana.

La función INDEX utiliza este número de posición 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 coincidente.

Busque la próxima fecha del evento

Este es otro ejemplo donde puede utilizar fórmulas de búsqueda para encontrar la próxima fecha de un evento en función de la fecha actual.

A continuación se muestra el conjunto de datos donde tengo nombres de eventos y las fechas del evento.

Fecha y nombre del evento final: conjunto de datos

Lo que quiero es el nombre del próximo evento y la fecha del evento para el próximo evento.

A continuación se muestra la fórmula que dará el nombre del próximo evento:

=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)

Y a continuación, la fórmula indicará la próxima fecha del evento:

=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)

Última fecha y nombre del evento: fórmula

Dejadme explicar cómo funciona esta fórmula.

Para obtener la fecha del evento, la función MATCH busca la fecha actual en la columna B. En este caso, no buscamos una coincidencia exacta, sino aproximada. Y, por tanto, el último argumento de la función MATCH es 1 (que encuentra el mayor valor que es inferior o igual al valor de búsqueda).

Por lo tanto, la función MATCH volvería la posición de la celda que tiene la fecha que es igual o inferior a la fecha actual. Por lo tanto, el siguiente evento, en este caso, sería la siguiente celda (ya que la lista está ordenada por orden ascendente).

Así, para obtener la próxima fecha del evento, sólo tenéis que añadir una a la posición de la celda devuelta por la función MATCH y proporciona la posición de la celda de la próxima fecha de la evento.

Este valor es dado por la función INDEX.

Para obtener el nombre del evento, se utiliza la misma fórmula y el intervalo de la función INDEX se cambia de la columna B en la columna A.

La idea de este ejemplo me vino cuando un amigo se acercó con una solicitud. Tenía una lista de todos sus amigos / parientes en una columna y quería saber el próximo cumpleaños (y el nombre de la persona).

Estos son tres ejemplos que muestran cómo encontrar el valor de coincidencia más cercano a Excel mediante fórmulas de búsqueda.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *