EXCEL: Suma n valores inferiores con criterios

Para sumar los valores de n inferiores en un criterio de coincidencia de rango, puede usar una fórmula de matriz basada en la función K.ESIMO.MENOR, envuelta dentro de la función SUMA. En la forma genérica de la fórmula (arriba), rango1 representa el rango de celdas en comparación con los criterios, rango2 contiene valores numéricos de los que se recuperan los valores inferiores y N representa "enésimo".

En el ejemplo, la celda activa contiene esta fórmula:

=SUMA(K.ESIMO.MENOR(SI(color=E5,value),{1,2,3}))

Donde el color es el rango nombrado B5: B12 y el valor es el rango nombrado C5: C12.

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

Así es como funciona la fórmula

En su forma más simple, K.ESIMO.MENOR devuelve el valor "enésimo más pequeño" en un rango con esta construcción:

=K.ESIMO.MENOR (range,N)

Así por ejemplo:

=K.ESIMO.MENOR (C5:C12,2)

devolverá el segundo valor más pequeño en el rango C5: C12, que es 5 en el ejemplo que se muestra.

Sin embargo, si proporciona una "constante de matriz" (por ejemplo, una constante en la forma {1,2,3}) a K.ESIMO.MENOR como segundo argumento, K.ESIMO.MENOR devolverá una matriz de resultados en lugar de un solo resultado Entonces, la fórmula:

=K.ESIMO.MENOR (C5:C12, {1,2,3})

devolverá el primer, segundo y tercer valor más pequeño C5: C12 en una matriz como esta: {4,5,7}.

Entonces, el truco aquí es filtrar los valores según el color antes de que se ejecute K.ESIMO.MENOR. Hacemos esto con una expresión basada en la función SI:

SI(color=E5,value)

Esto construye la matriz de valores alimentados a K.ESIMO.MENOR. Esencialmente, solo los valores asociados con el color rojo entran en la matriz. Donde el color es igual a "rojo", la matriz contiene un número, y donde el color no es rojo, la matriz contiene FALSO:

K.ESIMO.MENOR({12;FALSO;10;FALSO;8;4;FALSO;FALSO},{1,2,3}))

La función K.ESIMO.MENOR ignora los valores FALSO y devuelve los 3 valores más pequeños en la matriz: {4,8,10}. La función SUMA devuelve el resultado final, 22.