Recuperar los valores más grandes y más pequeños más cercanos de una lista en Microsoft Excel

En Excel, si tiene una lista de valores y no puede encontrar una coincidencia exacta, debe buscar el Más cercano más grande o Más cercano más pequeño valor. Por ejemplo, si está haciendo una comparación de números y no encuentra una coincidencia exacta en el rango, puede usar el siguiente número mayor o menor y continuar.

Podemos utilizar una combinación de Countif, grande y Pequeña fórmula para obtener nuestra salida.

Countif se usa para contar el número de veces que se repiten datos específicos en un rango de celdas.

Sintaxis = CONTAR.SI (rango, criterios)

Grande: Devuelve el k-ésimo valor más grande en un conjunto de datos.

Sintaxis = LARGE (matriz, k)

Pequeña: Devuelve el k-ésimo valor más pequeño de un conjunto de datos.

Sintaxis = PEQUEÑO (matriz, k)

Entendamos cuál es el número más grande y más pequeño más cercano

Tenemos una lista con números aleatorios 1, 2, 3, 5, 8, 10 y necesitamos una fórmula para evaluar lo siguiente:

- Si k está en la lista, devuelve k

- Si k no está en la lista, devuelve el siguiente valor más alto. Por ejemplo, si k = 7, devuelve 8 (en el caso del número más grande más cercano)

- Si k no está en la lista, devuelve el siguiente valor más pequeño. Por ejemplo, si k = 7, devuelve 5

(en el caso del número más pequeño más cercano)

Tomemos un ejemplo, tenemos algunos números aleatorios en la columna A y necesitamos encontrar el valor en este rango, que es el Más cercano más pequeño o Más cercano más grande a la celda B2 que contiene 46. Este número no está disponible en nuestra gama. Ver la captura de pantalla a continuación

Veamos cómo calculamos el Número más cercano más grande primero:

Podemos verificar manualmente el siguiente valor más grande a 46, que es 54.

Ahora intentemos obtener el mismo resultado usando la fórmula:

Usaremos una combinación de GRANDE Y CONTADA funciones para generar el número mayor más cercano.
El criterio es mayor que ">" firmar con Valor de búsqueda, es decir, celda B2
El signo mayor que combinado con el valor de búsqueda devolverá 5 valores, es decir, 54, 57, 61, 74, 83 que son mayores que 46.
Cuando fusionamos las funciones Grande y Cuenta, obtendremos 54. Consulte la fórmula en las celdas G2 y G3 en la instantánea a continuación.

PD: B2 en Countifformula es el valor de búsqueda, es decir, 46

En la celda C2, la fórmula utilizada para encontrar Más cercano más grande número es = GRANDE ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, ">" & B2))

Ahora veamos cómo calculamos el Número más pequeño más cercano -

Podemos verificar manualmente el siguiente valor más pequeño a 46, que es 28.

Obtengamos el mismo resultado usando la fórmula -

Usaremos una combinación de PEQUEÑO Y CONTADOfunciones para generar el número más pequeño más cercano.
El criterio es menos que "<" con Valor de búsqueda, es decir, celda B2
El signo menor que combinado con el valor de búsqueda devolverá 2 valores, es decir, 16 y 28, que son más pequeños que 46.
Cuando fusionamos las funciones Small & Count, obtenemos 28. Consulte la fórmula en las celdas G5 y G6 en la instantánea a continuación.

Vea la siguiente captura de pantalla con la fórmula:

Nota: la celda B2 en CountifFormula es el valor de búsqueda, es decir, 46

En la celda D2, la fórmula utilizada para encontrar Más cercano más pequeño número es = PEQUEÑO ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, "<" & B2))