FÓRMULAS DE EXCEL

Sumar los N números más altos o bajos de un rango

Sumar los N números más altos o bajos de un rango

Funciones utilizadas: SUMAPRODUCTO, SUMA, K.ESIMO.MAYOR, K.ESIMO.MENOR, FILA, INDIRECTO

 

Para sumar los «N» valores más altos o más bajos de un rango podemos emplear el uso de las funciones SUMAPRODUCTO o SUMA y combinarlas con las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR dependiendo si requerimos sumar aquellos valores más altos o más bajos.

Existen dos métodos para lograr este resultado, uno más simple que puede ser utilizado cuando el rango de valores no es muy elevado, y otro más complejo que nos permite definir un rango de forma directa y así reducir al mínimo la sintaxis de la fórmula final. Veamos cada caso en particular:

 

SUMAR LOS «N» VALORES MÁS ALTOS DE UN RANGO

Suponiendo que nuestro rango se encuentra en A2:A21 y queremos sumar los 3 valores más altos del rango, podemos emplear la siguiente fórmula:

=SUMAPRODUCTO(K.ESIMO.MAYOR(A2:A21;{1;2;3}))

Donde el argumento {1;2;3} corresponde a la numeración consecutiva de la cantidad de valores que requerimos sumar partiendo de los valores más altos del rango.

Ahora bien, si el rango de valores fuese muy grande (por ej. 1000), este argumento sería muy extenso, donde {1;2;3;4;………;1000} sería poco práctico y demoraría mucho tiempo en desarrollarse. Por este motivo es que existe una solución más práctica que se resuelve con dos fórmulas posibles:

=SUMAPRODUCTO(K.ESIMO.MAYOR(A2:A21;FILA(INDIRECTO("1:1000"))))

Donde el rango de valores se define como un rango precisamente («1:1000»), y no como una numeración consecutiva o relación de valores individuales. De esta forma podemos definir el rango menor y mayor de forma resumida y práctica, obteniendo el resultado deseado.

Por otro lado, podemos utilizar una fórmula similar que reemplaza la función SUMAPRODUCTO por la función SUMA con aplicación matricial, y se resume de la siguiente forma:

{=SUMA(K.ESIMO.MAYOR(A2:A21;FILA(INDIRECTO("1:1000"))))}

 

SUMAR LOS «N» VALORES MÁS BAJOS DE UN RANGO

Suponiendo que nuestro rango se encuentra en A2:A21 y queremos sumar los 3 valores más bajos del rango, podemos emplear la siguiente fórmula:

=SUMAPRODUCTO(K.ESIMO.MENOR(A2:A21;{1;2;3}))

Donde el argumento {1;2;3} corresponde a la numeración consecutiva de la cantidad de valores que requerimos sumar partiendo de los valores más bajos del rango.

Ahora bien, si el rango de valores fuese muy grande (por ej. 1000), este argumento sería muy extenso, donde {1;2;3;4;………;1000} sería poco práctico y demoraría mucho tiempo en desarrollarse. Por este motivo es que existe una solución más práctica que se resuelve con dos fórmulas posibles:

=SUMAPRODUCTO(K.ESIMO.MENOR(A2:A21;FILA(INDIRECTO("1:1000"))))

Donde el rango de valores se define como un rango precisamente («1:1000»), y no como una numeración consecutiva o relación de valores individuales. De esta forma podemos definir el rango menor y mayor de forma resumida y práctica, obteniendo el resultado deseado.

Por otro lado, podemos utilizar una fórmula similar que reemplaza la función SUMAPRODUCTO por la función SUMA con aplicación matricial, y se resume de la siguiente forma:

{=SUMA(K.ESIMO.MENOR(A2:A21;FILA(INDIRECTO("1:1000"))))}

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.