Las funciones de Excel pueden ser extremadamente poderosas si aprendes a combinar diferentes fórmulas. Cosas que podrían haber parecido imposibles de repente empezarían a parecer un juego de niños.
Un ejemplo es encontrar la coincidencia más cercana de un valor de búsqueda en un conjunto de datos en Excel.
Hay un par de funciones de búsqueda útiles en Excel (como VLOOKUP & INDEX MATCH), que pueden encontrar la coincidencia más cercana en unos pocos casos simples (como mostraré con los 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, le mostraré cómo encontrar la coincidencia más cercana de un valor de búsqueda en Excel con fórmulas de búsqueda.
Encuentre la coincidencia más cercana en Excel
Puede haber muchos escenarios diferentes en los que necesite buscar la coincidencia más cercana (o el valor coincidente más cercano).
A continuación se muestran los ejemplos que cubriré en este artículo:
- Encuentre la tasa de comisión basada en las ventas
- Encuentre 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
Encuentre la tasa de comisión (buscando el valor de venta más cercano)
Suponga que tiene un conjunto de datos como se muestra a continuación donde desea encontrar las tasas de comisión de todo el personal de ventas.
La comisión se asigna en función del valor de venta. Y esto se calcula usando la tabla de la derecha.
Por ejemplo, si un vendedor realiza las ventas totales de 5000, entonces la comisión es del 0% y si realiza las ventas totales de 15000, la comisión es del 5%.
Para obtener la tasa de comisión, debe encontrar el rango de venta más cercano justo por debajo del valor de venta. Por ejemplo, para el valor de venta de 15000, la comisión sería de 10,000 (que es el 5%) y para el 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, puede usar la coincidencia aproximada en BUSCARV.
La siguiente fórmula haría esto:
= BUSCARV (B2, $ E $ 2: $ F $ 6,2,1)
Tenga en cuenta que en esta fórmula, el último argumento es 1, que le dice a la fórmula que utilice una búsqueda aproximada. Esto significa que la fórmula pasaría por los valores de venta en la columna E y encontraría el valor que es un poco más bajo que el valor de búsqueda.
Luego, la fórmula VLOOKUP le dará la tasa de comisión para este valor.
Nota: Para que esto funcione, debe ordenar los datos en orden ascendente.Haga clic aquí para descargar el archivo de ejemplo
Encuentre el mejor candidato (basado en la experiencia más cercana)
En el ejemplo anterior, los datos debían ordenarse en orden ascendente. Pero puede haber casos en los que los datos no estén ordenados.
Así que cubramos un ejemplo y veamos 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 otorgada. Por ejemplo, 2 años y 3 años son ambos igualmente cercanos (diferencia de 0,5 años).
A continuación se muestra la fórmula que nos dará el resultado:
= ÍNDICE ($ A $ 2: $ A $ 15, COINCIDIR (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
El truco de esta fórmula es cambiar la matriz de búsqueda y el valor de búsqueda para encontrar la diferencia de experiencia mínima en los valores requeridos y reales.
Primero entendamos cómo lo haría manualmente (y luego explicaré cómo funciona esta fórmula).
Al hacer esto manualmente, pasará por cada celda de la columna B y encontrará la diferencia en la experiencia entre lo que se requiere y la que tiene una persona. Una vez que tenga todas las diferencias, encontrará la mínima y buscará el nombre de esa persona.
Esto es exactamente lo que estamos haciendo con esta fórmula.
Dejame explicar.
El valor de búsqueda en la fórmula COINCIDIR 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 usado 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 en la función COINCIDIR es ABS (D2- $ B $ 2: $ B $ 15).
Esto nos da una matriz de números de los que se ha restado 2.5 (la experiencia requerida).
Entonces 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 COINCIDIR 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.
Este número de posición es luego usado por la función INDICE 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 le dará el nombre del primer empleado coincidente.
Encuentre la fecha del próximo evento
Este es otro ejemplo en el que puede usar 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 los nombres de los eventos y las fechas de los eventos.
Lo que quiero es el nombre del próximo evento y la fecha del evento para este próximo evento.
A continuación se muestra la fórmula que dará el nombre del próximo evento:
= ÍNDICE ($ A $ 2: $ A $ 11, PARTIDA (E1, $ B $ 2: $ B $ 11,1) +1)
Y la fórmula a continuación le dará la fecha del próximo evento:
= ÍNDICE ($ B $ 2: $ B $ 11, PARTIDA (E1, $ B $ 2: $ B $ 11,1) +1)
Déjame explicarte cómo funciona esta fórmula.
Para obtener la fecha del evento, la función COINCIDIR busca la fecha actual en la columna B. En este caso, no buscamos una coincidencia exacta, sino aproximada. Y por lo tanto, el último argumento de la función COINCIDIR es 1 (que encuentra el valor más grande que es menor o igual al valor de búsqueda).
Entonces, la función COINCIDIR devolvería la posición de la celda que tiene la fecha que es menor o igual a la fecha actual. Entonces, el siguiente evento, en este caso, estaría en la siguiente celda (ya que la lista está ordenada en orden ascendente).
Entonces, para obtener la fecha del próximo evento, simplemente agregue uno a la posición de la celda devuelta por la función COINCIDIR, y le dará la posición de la celda de la fecha del próximo evento.
Este valor viene dado por la función INDICE.
Para obtener el nombre del evento, se usa la misma fórmula y el rango en la función INDICE se cambia de la columna B a la columna A.
Haga clic aquí para descargar el archivo de ejemplo
La idea de este ejemplo se me ocurrió cuando un amigo se acercó con una solicitud. Tenía una lista de todos los cumpleaños de sus amigos / familiares en una columna y quería saber el próximo cumpleaños que se aproximaba (y el nombre de la persona).Estos son tres ejemplos que muestran cómo encontrar el valor coincidente más cercano en Excel usando fórmulas de búsqueda.
También le pueden gustar los siguientes consejos / tutoriales de Excel
- Obtenga el último número de una lista usando la función BUSCARV.
- Obtenga múltiples valores de búsqueda sin repetición en una sola celda.
- VLOOKUP vs. ÍNDICE / PARTIDO
- PARTIDO DE ÍNDICE DE Excel
- Encuentre la última aparición de un valor de búsqueda en una lista en Excel
- Buscar y eliminar duplicados en Excel
- Formato condicional.
