EXCEL: Max de cada enésima columna

Para obtener el máximo de cada enésima columna, puede usar una fórmula de matriz basada en las funciones MAX, RESIDUO y COLUMNA. En el ejemplo que se muestra, la fórmula en M5 es:

=MAX(SI(RESIDUO(COLUMNA(B5:K5)-COLUMNA(B5)+1,L5)=0,B5:K5))

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.

Cómo funciona esta fórmula

Esta fórmula usa las funciones RESIDUO y COLUMNA para filtrar valores que no están en la enésima columna, luego ejecuta MAX en el resultado.

La clave es este fragmento:

RESIDUO(COLUMNA(B5:K5)-COLUMNA(B5)+1,L5)=0

Aquí, la fórmula usa la función COLUMNA para obtener un conjunto de números de columna "relativos" para el rango (explicado en detalle aquí) que se ve así:

{1,2,3,4,5,6,7,8,9,10}

Esta matriz entra en la función RESIDUO como argumento de número:

RESIDUO({1,2,3,4,5,6,7,8,9,10},K5)=0

donde L5 es el valor a usar para "enésimo". La función RESIDUO devuelve el resto para cada número de columna dividido por N. Entonces, por ejemplo, cuando N = 3, RESIDUO devolverá una matriz como esta:

{1,2,0,1,2,0,1,2,0,1}

Tenga en cuenta que aparecen ceros para las columnas 3, 6, 9, etc., correspondientes a cada 3a columna. Esta matriz se compara con cero con la expresión lógica = 0 para forzar un VERDADERO cuando el resto es cero y un FALSO cuando no. Estos valores entran en la función SI como prueba lógica. La función SI se filtra en consecuencia, por lo que solo los valores en el rango original en la enésima columna llegan al conjunto final. Los otros valores se convierten en FALSO.

Cuando n = 3, la matriz final dentro de MAX se ve así:

MAX({FALSO,FALSO,11,FALSO,FALSO,8,FALSO,FALSO,19,FALSO})

Finalmente, la función MAX ignora los valores FALSO y devuelve el máximo de valores restantes, 19.

Max de cada otra columna

Si desea obtener el máximo de cada otra columna, simplemente adapte esta fórmula según sea necesario, entendiendo que la fórmula asigna automáticamente 1 a la primera columna del rango. Para obtener el máximo de columnas REDONDEA.PAR, use:

{=MAX(SI(RESIDUO(COLUMNA(A1:Z1)-COLUMNA(A1)+1,2)=0,rng))}

Para obtener el máximo de columnas REDONDEA.IMPAR, use:

{=MAX(SI(RESIDUO(COLUMNA(A1:Z1)-COLUMNA(A1)+1,2)=1,rng))}