¿Cómo encontrar el valor más cercano en Excel?

Supongamos que tienes una lista de números en la columna B y necesitas determinar qué valor es el más cercano a un número dado — por ejemplo, 18. Usar una fórmula de matriz en Excel te permite identificar esto eficientemente sin tener que revisar manualmente la lista.

Para comenzar, selecciona una celda en blanco e introduce la siguiente fórmula. Una vez que hayas escrito la fórmula, asegúrate de presionar Ctrl + Shift + Enter en lugar de solo Enter. Esto garantizará que la fórmula se ejecute como una fórmula de matriz, lo cual es necesario para que funcione correctamente:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Este enfoque es más adecuado cuando necesitas recuperar el único número más cercano de un rango continuo. Funciona bien en la mayoría de los casos donde la precisión numérica y las coincidencias exactas son cruciales. Sin embargo, ten en cuenta que las fórmulas de matriz pueden ser intensivas en recursos en conjuntos de datos muy grandes. Si experimentas problemas de rendimiento o recibes mensajes de error como #VALOR!, verifica tus referencias de celdas y asegúrate de presionar Ctrl + Shift + Enter correctamente.

Hay ocasiones en las que puede que no necesites solo el valor más cercano, sino que quieras seleccionar todos los números que caen dentro de un cierto rango de tu valor objetivo, a menudo llamado rango de desviación. Kutools para Excel ofrece una solución práctica a través de su función Seleccionar Celdas Especiales, permitiendo una rápida selección de todos los valores dentro de una diferencia específica desde tu objetivo.

Por ejemplo, supongamos que tu valor objetivo es 18 y has determinado un valor de desviación de 2. Esto significa que quieres seleccionar todos los valores en tu rango que están entre 16 (18-2) y 20 (18+2). Aquí te mostramos cómo puedes lograrlo paso a paso:

1. Selecciona el rango que deseas buscar (por ejemplo, B3:B22), luego ve a Kutools > Seleccionar > Seleccionar Celdas Específicas.

2. En el cuadro de diálogo Seleccionar Celdas Específicas:

• En Tipo de selección, elige Celda.
• En Tipo específico:
  - Establece la primera lista desplegable en Mayor o igual a e ingresa 16 en el cuadro.
  - Establece la segunda lista desplegable en Menor o igual a e ingresa 20.

3. Haz clic en Aceptar para ejecutar. Kutools te notificará cuántas celdas cumplieron con tus criterios y resaltará todos los valores más cercanos dentro de la desviación especificada como se muestra a continuación:

Esta solución es ideal para identificar rápidamente todos los valores cercanos en masa, especialmente cuando se manejan rangos amplios con tolerancias variables. Ten en cuenta que la precisión de tu selección depende de establecer claramente tu desviación; si tu desviación es demasiado estrecha o ancha, podrías omitir datos relevantes o incluir valores no deseados.

Para usuarios que buscan automatización o que necesitan realizar búsquedas personalizadas del valor más cercano, ya sea para datos numéricos o de texto, en varias hojas o grandes conjuntos de datos, una macro VBA puede ser una solución eficiente y flexible. Al programar Excel para verificar sistemáticamente la diferencia entre tu objetivo y todos los candidatos, puedes recuperar no solo el número más cercano, sino también la cadena de texto más cercana por distancia textual.

Este enfoque es ventajoso cuando se requiere integración automatizada, especialmente en rangos demasiado grandes para métodos manuales o cuando se aplican tareas recurrentes. Sin embargo, ten en cuenta que las macros VBA requieren habilitar macros y tener un conocimiento básico del entorno VBA. Antes de ejecutar cualquier macro, siempre haz una copia de seguridad de tus datos para evitar pérdidas no deseadas.

1. Haz clic en Desarrollador > Visual Basic. En la ventana de Microsoft Visual Basic para Aplicaciones, haz clic en Insertar > Módulo, y copia el siguiente código en el módulo:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. Luego, ve a tu hoja de cálculo, e introduce esta fórmula: =FindClosest(B3:B22, E2) en una celda en blanco. Presiona la tecla Enter para obtener el valor más cercano.

Al revisar o presentar datos, a menudo es útil identificar visualmente los valores más cercanos a un objetivo sin filtrar ni reorganizar tus datos. La función Formato Condicional incorporada de Excel te permite resaltar las celdas que están más cerca de tu valor objetivo, haciéndolas fáciles de detectar a simple vista. Aunque este método no devuelve el valor exacto en sí, es efectivo para análisis de datos rápidos y énfasis visual.

La principal ventaja de este método es el resaltado dinámico y no destructivo que puede adaptarse a medida que cambian los datos o los valores objetivo. Es especialmente adecuado para tableros, presentaciones y escenarios de revisión donde la visibilidad es clave. Puede ser menos preciso si más de un valor comparte la misma 'cercanía', y no genera el valor en sí para su posterior procesamiento.

1. Selecciona el rango de celdas que deseas analizar (por ejemplo, B3:B22).

2. En la pestaña Inicio, haz clic en Formato Condicional > Nueva Regla.

3. Elige Usar una fórmula para determinar qué celdas formatear en el cuadro de diálogo. Luego, en el cuadro de fórmulas, introduce la siguiente fórmula:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Haz clic en Formato y elige un color de resaltado, luego haz clic en Aceptar y nuevamente en Aceptar para aplicar la regla.

Esto resaltará todas las celdas en tu rango seleccionado cuyos valores sean igualmente los más cercanos al valor objetivo en E2.

Si trabajas con rangos grandes o experimentas resultados inesperados, verifica que tus referencias estén correctas y que las referencias absolutas/relativas estén configuradas según lo previsto (usa $ para bloquear la celda objetivo y las referencias de rango).