☰

Encontrar el siguiente valor más alto en una columna

tag-icon • tag-icon microsoft-excel worksheet-function lookup
Encontrar el siguiente valor más alto en una columna

Tengo dos columnas Ay B. La columna Btiene valores que necesito buscar en Column A. Sin embargo, no necesito encontrar el valor correspondiente exacto, necesito el siguiente valor superior.

Por ejemplo:

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

Entonces, para el valor 5en la columna B, quiero regresar 7de la columna A.

Supongo que probablemente necesite algún tipo de función de búsqueda/coincidencia de índice, pero no he podido escribir la fórmula yo mismo.

Respuesta1

Ordenado

La fórmula más simple es para el caso en que la columna Aestá ordenada en orden ascendente:

Captura de pantalla de la hoja de trabajo

Ingrese la siguiente fórmula C1y presione Ctrl-Intro/copiar-pegar/rellenar/completar automáticamente en el resto de la columna de la tabla:

=INDEX(A:A,1+MATCH(B1,A:A,1))

Explicación:

Como 1tercer argumento de MATCH()significa que encuentra el valor más grande que sea menor o igual que el primer argumento. Agregar 1a ese índice da como resultado el índice del siguiente número superior. Luego, la INDEX()función extrae el número.

Tenga en cuenta que agregué un valor adicional al final de la columna A. Esto es para el caso especial en el que no existe un siguiente valor superior.

sin clasificar

Para el caso en el que la columna Ano está ordenada (también funciona si está ordenada), la fórmula es un poco más complicada:

Introduzca ( Ctrl+ Shift+ Enter) la siguiente fórmula C1y copie, pegue/rellénela en el resto de la columna de la tabla (no olvide eliminar y {) }:

{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}

Explicación:

La función devuelve el enésimo valor más pequeño de la matriz,SMALL(array,n)ignorando valores booleanos. Como el valor predeterminado para el tercer argumento de la IF()función es , solo se verifican FALSElos valores mayores que el valor en la columna , lo que da como resultado el siguiente valor más alto.B

ATenga en cuenta que no se requiere un valor final especial para la columna , ya que #NUM!se produce un error si no hay valores en la columna Amayores que el valor en la columna B.

Finalmente, como ha señalado la aventurina, existe una fórmula alternativa similar que funciona independientemente de la clasificación (pero con una salvedad importante).

Para Excel 2016+:

=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)

Esto funciona porque la MINIFS()función filtra los valores que no coinciden con los criterios antes de extraer el valor mínimo.

Para versiones anteriores de Excel:

{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}

Esto funciona por la misma razón que la SMALL()función: ignora los valores booleanos generados por la IF()función.

Advertencia:

Ambas fórmulas =MINIFS()y {=MIN(IF())}no funcionarán correctamente si un cero puede ser el siguiente valor superior correcto, ya que también se devuelve cero cuando hayesno hay siguiente valor superior. (Esta es la misma razón para agregar un valor adicional al final de la columna Apara la primera fórmula; esa fórmula también devuelve un cero si no hay valores más altos).

Respuesta2

Puede utilizar, por ejemplo, la función de matriz {=MIN(IF(A1:A6 > B1; A1:A6))}o {=MIN(IF(A1:A6 > B1; A1:A6; 1000))}(con 1000 como valor alternativo).

Toma el mínimo de todos los valores de la columna A que sean mayores que el valor de la celda actual de la columna B (aquí B1). Por lo tanto, ninguna de las columnas debe estar ordenada.

Con Excel >= 2016 también puedes usar la MINIFSfunción.

Tenga en cuenta que las funciones de matriz deben insertarse presionando Ctrl+Shift+Enter.

información relacionada