¿Qué fórmula encontrará el valor más cercano en Excel? Varias situaciones, y por tanto varias fórmulas, se presentan en este artículo.
Encuentra el valor más cercano
Considere un rango de valores sin ordenar en la columna A. Si desea conocer el valor más cercano, siga el siguiente ejemplo.
La fórmula es un poco compleja y sobre todo es una función matricial.
- Si no trabaja con Excel 365, la validación de las siguientes fórmulas debe realizarse con las claves Ctrl + Mayús + Intro al mismo tiempo.
- Pero si trabajas con Excel 365, valida con Entrada es suficiente porque las funciones matriciales se interpretan automáticamente.
=MIN(SI(ABS(A3:A11-D2)=MIN(ABS(A3:A11-D2));A3:A11))
Entendiendo la fórmula
Para entender la fórmula, debes usar el atajo de teclado F9 analizar cada parte de la fórmula.
El resultado de la instrucción. ABS(A3:A11-D2) hace la diferencia entre todos los valores de la columna A con el valor a buscar en D2.
Entonces, el valor más cercano a 0 es necesariamente el valor más cercano de nuestra lista. Y este valor se obtiene con la instrucción. MÍN.(ABS(A3:A11-D2))
Finalmente, la fórmula devolverá el valor correspondiente a la posición de este valor en la lista inicial.
Posición del valor en el rango de datos
Para encontrar la posición donde se encuentra el valor obtenido por la fórmula anterior simplemente usaremos la Función COINCIDIR.
=COINCIDIR(D4;A3:A11;0)
ADVERTENCIA: Es el resultado de la fórmula anterior el que se debe pasar como primer parámetro.
Número de línea de este valor
Si ahora quieres saber la fila de Excel en la que se encuentra este valor, escribiremos la siguiente fórmula.
=ÍNDICE(LÍNEA(A3:A11);D5)
Prueba con otro valor
Si ahora queremos buscar otro valor, automáticamente las fórmulas se adaptarán.
Fórmula para encontrar el valor superior más cercano
Para encontrar el valor superior más cercano, usaremos la función EQUIVALENTE. Solo se puede acceder a esta función con Excel 365 y Excel Online.
Aquí, además estableceremos el tercer parámetro de la función EQUIVX en 1 para encontrar el valor superior más cercano.
=INDEX(A3:A11;EQUIVX(D2;A3:A11;1))
Fórmula para encontrar el valor inferior más cercano
Y para encontrar el valor inferior más cercano, simplemente establezca el tercer parámetro de la función EQUIVX en -1.
=INDEX(A3:A11;EQUIVX(D2;A3:A11;1))
24/11/2023 a las 16:43
Hola Federico,
Gracias por este ejercicio tan interesante.
¿Cómo debería verse si dos valores son idénticos o si tienen la misma diferencia que el valor deseado?
Por ejemplo si el valor buscado es 14 como en el ejercicio pero tenemos 15 en la línea 2 y en la línea 7 o 15 en la línea 2 y 13 en la línea 7.
cordialmente
07/02/2023 a las 20:16
Bonsoir,
Tengo un valor de referencia por fila, en una columna y dos valores por fila en dos columnas. Necesito encontrar cuál de los dos valores es el más cercano a mi valor de referencia.
¿Podrías ayudarme con este tema?
Gracias tenga un buen día.
09/01/2022 a las 10:50
Hola, estoy buscando algo como esto, pero con un valor justo arriba del número que busco. Gracias
09/01/2022 a las 12:42
Acabo de actualizar el artículo con la respuesta a tu pregunta.