En Excel, si tiene una lista de valores y no puede encontrar una coincidencia exacta, entonces necesita encontrar el valor más cercano más grande o más pequeño más cercano. Por ejemplo, si está comparando números y no encuentra una coincidencia exacta en el rango, puede utilizar el siguiente número mayor o menor y continuar.

Podemos usar una combinación de las fórmulas Countif, Large y Small para obtener nuestro resultado.

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, criterio)

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

Sintaxis = LARGE (matriz, k)

Pequeño: 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 pequeño o el más grande más cercano a la celda B2 que contiene 46. Este número no está disponible en nuestro rango. Vea la captura de pantalla a continuación

img1

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

Podemos comprobar 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 las funciones LARGE & COUNTIF * para generar el número mayor más cercano.

El criterio es mayor que el signo «>» con el valor de búsqueda, es decir, la 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 Conde, obtendremos 54. Consulte la fórmula en las celdas G2 y G3 en la instantánea a continuación.

img2

PD: B2 en Countifformula es el valor de búsqueda, es decir, 46 En la celda C2, la fórmula utilizada para encontrar el número más cercano * es

= GRANDE ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, «>» & B2))

img3

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 las funciones SMALL & COUNTIF * para generar el número más pequeño más cercano.

El criterio es menor que «<» con el valor de búsqueda, es decir, la celda B2 El signo menor que combinado con el valor de búsqueda devolverá 2 valores, es decir, 16 y 28, que son menores 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:

img4

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 el número más pequeño * más cercano es

= PEQUEÑO ($ A $ 2: $ A $ 8, COUNTIF ($ A $ 2: $ A $ 8, «<» & B2))

img5

Así es como podemos devolver el número más grande más cercano y el número más pequeño del armario.