Skip to main content

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

Author: Xiaoyang Last Modified: 2025-07-21

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

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))
Nota: En esta fórmula de matriz {=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.

Kutools para Excel ofrece más de 300 funciones avanzadas para simplificar tareas complejas, potenciando la creatividad y la eficiencia. Integrado con capacidades de inteligencia artificial, Kutools automatiza las tareas con precisión, haciendo que la gestión de datos sea sencilla. Información detallada de Kutools para Excel...  Prueba gratuita...

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.

set options in the Select Specific Cells dialog box

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:
all closest values of the given value are selected

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.

Nota: En esta fórmula, B3:B22 define el rango de datos, y E2 contiene el valor objetivo utilizado para encontrar la coincidencia más cercana.

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

🤖 Kutools AI Aide: Revoluciona el análisis de datos basado en: Ejecución inteligente | Generar código | Crear fórmulas personalizadas | Analizar datos y generar gráficos | Invocar Funciones mejoradas
Funciones populares: Buscar, resaltar o marcar duplicados | Eliminar filas en blanco | Combinar columnas o celdas sin perder datos | Redondear...
Super BUSCARV: Búsqueda por varios criterios | Búsqueda de varios valores | Búsqueda en varias hojas | Coincidencia difusa....
Lista desplegable avanzada: Crea rápidamente una lista desplegable | Lista desplegable dependiente | Lista desplegable de selección múltiple....
Administrador de columnas: Agregar un número específico de columnas | Mover columnas | Alternar el estado de visibilidad de columnas ocultas | Comparar rangos y columnas...
Funciones destacadas: Cuadrícula de enfoque | Vista de diseño | Barra de fórmulas mejorada | Administrador de Libro de trabajo y Hoja de cálculo | Biblioteca de AutoTexto | Selector de fechas | Combinar Datos | Cifrar/Descifrar celdas | Enviar correo electrónico por lista | Super Filtro | Filtro especial (filtrar celdas con fuente en negrita/cursiva/tachado...)...
Top15 conjuntos de herramientas:12 herramientas de texto (Agregar texto, Eliminar caracteres específicos, ...) | Más de50 tipos de gráficos (Diagrama de Gantt, ...) | Más de40 fórmulas prácticas (Calcular edad basada en la fecha de nacimiento, ...) |19 herramientas de inserción (Insertar código QR, Insertar imagen desde ruta, ...) |12 herramientas de conversión (Convertir a palabras, Conversión de moneda, ...) |7 herramientas para combinar y dividir (Combinar filas avanzado, Dividir celdas, ...) | ... y más

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!