¿Cómo encontrar el valor más cercano en Excel?
En el análisis de datos o la elaboración de informes, a menudo es necesario encontrar, dentro de una columna o un conjunto de valores, el elemento que está más cerca de un valor objetivo dado. Aunque Excel no ofrece una función integrada de 'buscar el valor más cercano', podemos lograr esto utilizando fórmulas, VBA, Formato Condicional u otras herramientas de terceros. Este artículo examinará varios enfoques comunes, desglosando los principios subyacentes, los pasos de implementación y las ventajas y desventajas de cada método para ayudarte a elegir la mejor solución.
- Encuentra el número más cercano con una fórmula de matriz
- Selecciona fácilmente todos los números más cercanos dentro del rango de desviación de un valor dado
- Macro VBA para encontrar el valor más cercano a un objetivo
- Usa Formato Condicional para resaltar visualmente los valores más cercanos
Encuentra el número más cercano con una fórmula de matriz
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 de manera eficiente 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))
- B3:B22 se refiere al rango que contiene los datos que deseas examinar.
- E2 es la celda donde has ingresado tu valor objetivo (por ejemplo, 18).
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 celda y asegúrate de presionar Ctrl + Shift + Enter correctamente.
Selecciona fácilmente todos los números más cercanos dentro del rango de desviación de un valor dado con Kutools para Excel
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 Específicas, permitiendo una rápida selección de todos los valores dentro de una diferencia especificada 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 al manejar 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 perder datos relevantes o incluir valores no deseados.
Macro VBA para encontrar el valor más cercano a un objetivo
Para usuarios que buscan automatización o que necesitan realizar búsquedas personalizadas de valores más cercanos, 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 próxima según la distancia textual.
Este enfoque es ventajoso cuando se requiere integración de automatización, especialmente en rangos demasiado grandes para métodos manuales o al aplicar 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 involuntarias.
1. Haz clic en Desarrollador > Visual Basic. En la ventana 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.
Usa Formato Condicional para resaltar visualmente los valores más cercanos
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 integrada de Excel te permite resaltar las celdas que están más cerca de tu valor objetivo, facilitando su identificación a simple vista. Aunque este método no devuelve el valor exacto en sí, es efectivo para un análisis rápido de datos y para dar é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 un procesamiento posterior.
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órmula, 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 estás trabajando con rangos grandes o experimentas resultados inesperados, verifica que tus referencias sean correctas y que las referencias absolutas/relativas estén configuradas como se pretende (usa $ para bloquear la celda objetivo y las referencias de rango).
Demostración: selecciona todos los valores más cercanos dentro del rango de desviación de un valor dado
Las mejores herramientas de productividad para Office
Potencia tus habilidades en Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab lleva la interfaz de pestañas a Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de nuevas ventanas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!