EXCEL: Valor máximo con columna variable

Para recuperar el valor máximo en un conjunto de datos, donde la columna es variable, puede usar INDICE y COINCIDIR junto con la función MAX. En el ejemplo que se muestra, la fórmula en J5 es:

=MAX(INDICE(data,0,COINCIDIR(J4,header,0)))

donde los datos (B5: F15) y el encabezado (B4: F4) se denominan rangos.

Cómo funciona esta fórmula

Nota: Si es nuevo en INDICE y COINCIDIR, consulte: Cómo usar INDICE y COINCIDIR

En una configuración estándar, la función INDICE recupera un valor en una fila y columna determinadas. Por ejemplo, para obtener el valor en la fila 2 y columna 3 en un rango dado:

=INDICE(range,2,3) // get value at row 2, column 3

Sin embargo, INDICE tiene un truco especial: la capacidad de recuperar columnas y filas enteras. La sintaxis implica suministrar cero para el argumento "otro". Si desea una columna completa, proporcione la fila como cero. Si desea una fila completa, proporcione la columna como cero:

=INDICE(data,0,n) // retrieve column n
=INDICE(data,n,0) // retrieve row n

En el ejemplo que se muestra, queremos encontrar el valor máximo en una columna dada. El giro es que la columna debe ser variable para que pueda cambiarse fácilmente. En F5, la fórmula es:

=MAX(INDICE(data,0,COINCIDIR(J4,header,0)))

Trabajando de adentro hacia afuera, primero usamos la función COINCIDIR para obtener el "índice" de la columna solicitada en la celda J4:

COINCIDIR(J4,header,0) // get column index

Con "Verde" en J4, la función COINCIDIR devuelve 3, ya que Verde es el tercer valor en el encabezado del rango con nombre. Después de que COINCIDIR devuelve un resultado, la fórmula se puede simplificar a esto:

=MAX(INDICE(data,0,3))

Con cero proporcionado como el número de fila, INDICE devuelve todos los valores en la columna 3 de los datos del rango con nombre. El resultado se devuelve a la función MAX en una matriz como esta:

=MAX({83;54;35;17;85;16;70;72;65;93;91})

Y MAX devuelve el resultado final, 93.

Valor mínimo

Para obtener el valor mínimo con una columna variable, simplemente puede reemplazar la función MAX con la función MIN. La fórmula en J6 es:

=MIN(INDICE(data,0,COINCIDIR(J4,header,0)))

Con FILTRAR

La nueva función FILTRAR también se puede utilizar para resolver este problema, ya que FILTRAR puede filtrar datos por fila o por columna. El truco es construir un filtro lógico que excluya otras columnas. CONTAR.SI funciona bien en este caso, pero debe configurarse "al revés", con J4 como rango y encabezado para los criterios:

=MAX(FILTRAR(data,CONTAR.SI(J4,header)))

Después de que se ejecuta CONTAR.SI, tenemos:

=MAX(FILTRAR(data,{0,0,1,0,0}))

Y FILTRAR entrega la tercera columna a MAX, igual que la función INDICE anterior.

Como alternativa a CONTAR.SI, puede usar ESNUMERO + COINCIDIR en su lugar:

=MAX(FILTRAR(data,ESNUMERO(COINCIDIR(header,J4,0))))

La función COINCIDIR se configura nuevamente "hacia atrás", de modo que obtenemos una matriz con 5 valores que servirán como filtro lógico. Después de ejecutar ESNUMERO y COINCIDIR, tenemos:

=MAX(FILTRAR(data,{FALSO,FALSO,VERDADERO,FALSO,FALSO}))

Y FILTRAR nuevamente entrega la tercera columna a MAX.