EXCEL: Suma los valores superiores de n con criterios

Para sumar los valores n superiores en un criterio de coincidencia de rango, puede usar una fórmula basada en la función K.ESIMO.MAYOR, envuelta dentro de la función SUMAPRODUCTO. En la forma genérica de la fórmula (arriba), el rango representa un rango de celdas que se comparan con los criterios, los valores representan valores numéricos de los que se recuperan los valores superiores y N representa la idea del enésimo valor.

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

=SUMAPRODUCTO(K.ESIMO.MAYOR((color=E5)*(value),{1,2,3}))

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

Así es como funciona la fórmula

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

=K.ESIMO.MAYOR (range,N)

Así por ejemplo:

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

devolverá el segundo valor más grande en el rango C5: C12, que es 12 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.MAYOR como segundo argumento, K.ESIMO.MAYOR devolverá una matriz de resultados en lugar de un solo resultado Entonces, la fórmula:

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

devolverá el primer, segundo y tercer valor más grande C5: C12 en una matriz como esta: {12,12,10}

Entonces, el truco aquí es filtrar los valores basados ​​en el color antes de que se ejecute K.ESIMO.MAYOR. Hacemos esto con la expresión:

(color=E5)

Lo que da como resultado una matriz de valores VERDADERO / FALSO. Durante la operación de multiplicación, estos valores se convierten en unos y ceros:

=K.ESIMO.MAYOR({1;0;1;0;1;1;0;0}*{12;12;10;9;8;8;7;5},{1,2,3})

Entonces, el resultado final es que solo los valores asociados con el color "rojo" sobreviven a la operación:

=SUMAPRODUCTO(K.ESIMO.MAYOR({12;0;10;0;8;8;0;0},{1,2,3}))

y los otros valores son forzados a cero.

Nota: esta fórmula no manejará texto en el rango de valores. Vea abajo.

Manejo de texto en valores

Si tiene texto en cualquier lugar de los rangos de valores, la función K.ESIMO.MAYOR generará un error #VALOR y dejará de funcionar la fórmula.

Para manejar texto en el rango de valores, puede agregar la función SI.ERROR de esta manera:

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

Aquí, atrapamos errores de K.ESIMO.MAYOR causados ​​por valores de texto y los reemplazamos por cero. El uso de SI dentro de K.ESIMO.MAYOR requiere que la fórmula se ingrese con control + shift + enter, por lo que cambiamos a SUMA en lugar de SUMAPRODUCT.

Nota: Me encontré con esta fórmula publicada por el increíble Barry Houdini en stackoverflow.