Las funciones de Excel pueden ser muy poderosas si te acostumbras a combinar diferentes fórmulas.Lo que antes parecía imposible de repente puede convertirse en un juego de niños.
Un ejemplo de ello es encontrar el valor más cercano en un conjunto de datos de Excel.
Hay varias funciones de búsqueda útiles en Excel (como BUSCARV e ÍNDICE COINCIDIR) que pueden encontrar el más cercano.Valor(como se muestra en el siguiente ejemplo).
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 a un valor de búsqueda en una lista desordenada).
En este tutorial, le mostraré cómo usar una fórmula de búsqueda para encontrar la coincidencia más cercana a un valor de búsqueda en Excel.
Hay muchos escenarios diferentes en los que necesita encontrar la coincidencia más cercana (o el valor de coincidencia más cercano).
Aquí hay un ejemplo que cubriré en este artículo:
- Encuentre tasas de comisión basadas en ventas
- Encuentre al mejor candidato (basado en la experiencia más cercana)
- Encuentra la fecha del próximo evento
¡Empecemos!
Contenido
Encuentre tasas de comisión (busque el valor de venta más cercano)
Suponga que tiene un conjunto de datos como el que se muestra a continuación donde desea encontrar las tasas de comisión para todos los vendedores.
Las comisiones se distribuyen en función del valor de la venta.Esto se calcula utilizando la tabla de la derecha.
Por ejemplo, si las ventas totales del vendedor son 5000, la comisión es del 0 % y si sus ventas totales son 15000 5, la comisión es del XNUMX %.
Para obtener una tasa de comisión, debe encontrar el rango de ventas más cercano que esté justo debajo del valor de las ventas.Por ejemplo, para 15000 10,000 ventas, la comisión es de 5 25000 (o 20,000 %) y para 7 XNUMX ventas, la tasa de comisión es de XNUMX XNUMX (o XNUMX %).
Para encontrar el valor de venta más cercano y obtener una tasa de comisión, puede usar la coincidencia aproximada en BUSCARV.
La siguiente fórmula puede hacer esto:
= BUSCARV (B2, $ E $ 2: $ F $ 6,2,1)
Tenga en cuenta que en esta fórmula, el último parámetro es 1, lo que le indica a la fórmula que use una búsqueda aproximada.Esto significa que la fórmula pasará por el valor de ventas en la columna E y encontrará el valor justo debajo del valor de búsqueda.
La fórmula BUSCARV le dará la tasa de comisión para ese valor.
注意: Para hacer esto, necesita ordenar los datos en orden ascendente.
Encuentre al mejor candidato (basado en la experiencia más cercana)
En el ejemplo anterior, los datos deben ordenarse en orden ascendente.Pero puede haber casos en los que los datos no estén ordenados.
Entonces, tomemos un ejemplo y veamos cómo podemos usar combinaciones de fórmulas para encontrar la coincidencia más cercana en Excel.
A continuación se muestra un conjunto de datos de ejemplo en el que necesito encontrar los nombres de los empleados cuya experiencia laboral se acerque más al valor deseado.El valor esperado en este caso es de 2.5 años.
Tenga en cuenta que los datos no están ordenados.Además, las experiencias más cercanas pueden ser más o menos que una experiencia dada.Por ejemplo, 2 años y 3 años están igualmente cerca (diferencia de 0.5 años).
Aquí está la fórmula que nos da el resultado:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
El truco en esta fórmula es cambiar la matriz de búsqueda y los valores de búsqueda para encontrar la diferencia empírica más pequeña entre los valores deseados y reales.
Primero comprendamos cómo lo haría manualmente (y luego explicaré cómo funciona esta fórmula).
Cuando hace esto manualmente, revisa cada celda en la columna B y encuentra la diferencia entre la experiencia que desea y la experiencia que tiene.Una vez que tenga todas las diferencias, encuentre la más pequeña y obtenga el nombre de esa persona.
Eso es exactamente lo que hacemos con esta fórmula.
Dejame explicar.
El valor de búsqueda en la fórmula MATCH es MIN(ABS(D2-B2:B15)).
Esta sección le brinda la diferencia mínima entre la experiencia dada (es decir, 2.5 años) y todas las demás experiencias.En este caso devuelve 0.3
Tenga en cuenta que uso ABS para asegurarme de que estoy buscando lo más cercano (probablemente 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 resta 2.5 (experiencia requerida).
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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 es la posición del nombre de la persona con la experiencia más cercana.
La función ÍNDICE luego usa el número de trabajo para devolver el nombre de la persona.
Nota: Si hay varios candidatos con la misma experiencia mínima, la fórmula anterior dará el nombre del primer empleado que coincida.
Encuentra la fecha del próximo evento
Aquí hay otro ejemplo en el que puede usar una fórmula 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 fechas de eventos.
Lo que quiero es el nombre del próximo evento y la fecha del evento de este próximo evento.
Aquí está la fórmula que da el nombre del próximo evento:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
La siguiente fórmula dará las próximas fechas de eventos:
=INDEX($B$2:$B$11,MATCH(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 MATCH busca en la columna B la fecha actual.En este caso, no buscamos una coincidencia exacta, sino una coincidencia aproximada.Por lo tanto, el último parámetro de la función COINCIDIR es 1 (encuentra el valor más grande menor o igual que el valor de búsqueda).
Entonces, la función COINCIDIR devolverá la posición de la celda cuya fecha es menor o igual a la fecha actual.Entonces, en este caso, el próximo evento estará en la siguiente celda (ya que la lista está ordenada en orden ascendente).
Entonces, para obtener la próxima fecha del evento, simplemente agregue uno a la posición de la celda devuelta por la función MATCH y le dará la posición de la celda de la próxima fecha del evento.
Este valor viene dado por la función ÍNDICE.
Para obtener el nombre del evento, use la misma fórmula y cambie el rango en la función ÍNDICE de la columna B a la columna A.
Este ejemplo me vino a la mente cuando un amigo hizo una solicitud.Enumera los cumpleaños de todos sus amigos/familiares en una columna y quiere saber cuál es el próximo cumpleaños (y el nombre de la persona).
Los tres ejemplos anteriores muestran cómo usar la fórmula de búsqueda para encontrar el valor más cercano en Excel.
