Seis formas rápidas y confiables de hacer la suma de los valores N más altos en Excel.
Sumar es una de las actividades más populares tanto en el mundo empresarial como en la vida cotidiana. No es de extrañar que Microsoft Excel venga equipado con una serie de funciones integradas, como SUM, SUMIF y SUMIFS.
En algunas situaciones, sin embargo, es posible que deba sumar solo números específicos en un rango, digamos 3, 5, 10 o n. Eso podría ser un desafío porque Excel no tiene una función incorporada para esto. Pero como siempre, no hay nada que te impida construir tus propias fórmulas 🙂
SUMA los 2, 3, 5 o n números más grandes de un rango
Para sumar los n números principales en una matriz dada, la fórmula genérica es:
SUMA(GRANDE(rango, {1,2,3, …, n}))
Por ejemplo, para obtener la suma de los 2 números más grandes en el rango B2:B15, la fórmula es:
=SUM(LARGE(B2:B15, {1,2}))
Para sumar los 3 números más grandes:
=SUM(LARGE(B2:B15, {1,2,3}))
Para hacer una suma de 5 números más grandes:
=SUM(LARGE(B2:B15, {1,2,3,4,5}))
La siguiente captura de pantalla muestra todas las fórmulas en acción:
Si los rangos de los valores se ingresan en celdas predefinidas, deberá usar una referencia de rango para el argumento k de GRANDE. En este caso, la solución debe ingresarse como una fórmula de matriz presionando las teclas Ctrl + Shift + Enter juntas. En Dynamic Array Excel (365 y 2021), esto también funcionará como una fórmula regular.
Por ejemplo, para obtener la suma de los 3 números adyacentes o no adyacentes más grandes cuyos rangos están en D2: D4, la fórmula es:
=SUM(LARGE(B2:B15, D2:D4))
Nota. Si hay dos o más números que están empatados en el último lugar, solo se sumará el primer número. Por ejemplo, si los 3 primeros números son 10, 9 y 7, y el número 7 aparece en más de una celda, solo se sumará la primera aparición de 7.
Cómo funciona esta fórmula:
El núcleo de la fórmula es la función GRANDE que determina el enésimo valor más grande en una matriz determinada. Para obtener los n valores superiores, se proporciona una constante de matriz como {1,2,3} al segundo argumento:
LARGE(B2:B15, {1,2,3})
Como resultado, GRANDE devuelve los 3 valores principales en el rango B2:B15, que son 30, 28 y 27.
La función SUMA lo toma desde allí, suma los 3 números y genera el total:
=SUM({30,28,27})
Encuentra la suma de los valores más altos con SUMPRODUCT
La fórmula SUMAPRODUCTO para devolver una suma de los primeros N números es muy parecida:
SUMAPRODUCTO(rango, {1,2,3, …, n}))
La belleza de SUMPRODUCT es que funciona igualmente bien con constantes de matriz y referencias de celda. Es decir, independientemente de si usa {1,2,3} o D2:D4 para k dentro de GRANDE, una fórmula normal funcionará bien en todas las versiones de Excel:
=SUMPRODUCT(LARGE(B2:B15, {1,2,3}))
O
=SUMPRODUCT(LARGE(B2:B15, D2:D4))
Cómo sumar muchos números superiores
Si está buscando sumar los 10 o 20 números principales en un rango, enumerar manualmente sus posiciones en una matriz constante puede ser bastante tedioso. En este caso, puede construir una matriz automáticamente utilizando las funciones FILA e INDIRECTO:
SUMAPRODUCTO(GRANDE(rango, FILA(INDIRECTO(«1:n»))))
SUMA (GRANDE (rango, FILA (INDIRECTO («1: n»))))
Recuerde que la combinación SUMA + GRANDE solo funciona como fórmula matricial en Excel 2019 y versiones anteriores, así que use el atajo Ctrl + Shift + Enter para completarlo correctamente.
Por ejemplo, para sumar los 10 números más altos del rango, use una de estas fórmulas:
=SUM(LARGE(B2:B15, ROW(INDIRECT("1:10"))))
O
=SUMPRODUCT(LARGE(B2:B15, ROW(INDIRECT("1:10"))))
Para que la fórmula sea más flexible, puede ingresar la cantidad de elementos en alguna celda, digamos E2, y concatenar la referencia de celda dentro de INDIRECTO:
=SUM(LARGE(B2:B15, ROW(INDIRECT("1:"&E2))))
O
=SUMPRODUCT(LARGE(B2:B15, ROW(INDIRECT("1:"&E2))))
En Excel 365 y Excel 2021, donde el comportamiento de la matriz dinámica es nativo para todas las funciones, tanto SUM como SUMPRODUCT funcionan perfectamente como fórmulas regulares:
Cómo sumar un número variable de valores más grandes
Cuando no hay datos suficientes en el rango de origen, es decir, n es mayor que el número total de elementos, las fórmulas discutidas anteriormente generarían un error:
Para manejar este escenario, elija una de estas soluciones:
1. Devuelve la suma de los n más grandes o todos los elementos
Con este enfoque, genera una suma de todos los valores existentes en caso de que el n especificado sea mayor que el número total de elementos en el rango.
Primero, obtenemos un conteo de todos los números con la ayuda de COUNT, y luego usamos MIN para devolver n o el conteo, el que sea menor:
=SUMPRODUCT(LARGE(range, ROW(INDIRECT("1:"&MIN(n, COUNT(range))))))
Para nuestro conjunto de datos de muestra, la fórmula toma esta forma:
=SUMPRODUCT(LARGE(B2:B15, ROW(INDIRECT("1:"&MIN(E2, COUNT(B2:B15))))))
Debido a que el n especificado (15) es mayor que el conteo (14), todos los números en B2:B15 se suman:
2. Mostrar un mensaje personalizado si no hay suficientes datos
Para dejar explícitamente claro que n excede el número total de elementos en la lista, puede mostrar un mensaje personalizado usando la función IFERROR:
=IFERROR(SUMPRODUCT(LARGE(B2:B15, ROW(INDIRECT("1:"&E2)))), "Insufficient data")
Sume los números más grandes en Excel 365 y Excel 2021
En lugar de construir una engorrosa combinación FILA + INDIRECTO, los usuarios de Excel 365 y Excel 2021 pueden emplear la función SECUENCIA para generar una matriz numérica sobre la marcha:
SUMAPRODUCTO(GRANDE(rango, SECUENCIA(n)))
Debido a que SEQUENCE solo está disponible en el nuevo Dynamic Array Excel, donde el comportamiento de la matriz es nativo para todas las funciones, puede reemplazar de forma segura SUMPRODUCT con una SUM más concisa:
SUMA(GRANDE(rango, SECUENCIA(n)))
Por ejemplo, para hacer la suma de los 5 números más grandes, use esta fórmula compacta y elegante:
=SUM(LARGE(B2:B15, SEQUENCE(E2)))
Cómo sumar los n valores principales en la tabla de Excel
Las tablas de Excel vienen equipadas con una serie de características increíbles, que le permiten obtener una suma de los valores N principales en 4 sencillos pasos:
- Convierta un rango habitual en una tabla presionando las teclas Ctrl + T juntas.
- En la pestaña Diseño de tabla, en el grupo Opciones de estilo de tabla, habilite la función Total de filas.
- En el encabezado de la columna que contiene sus números, haga clic en el ícono de filtro y luego haga clic en Filtros de números > 10 principales.
- En un cuadro de diálogo emergente, especifique cuántos elementos principales mostrar:
¡Eso es todo! Excel filtrará inmediatamente los 10 elementos principales (o lo que elija) y calculará su total automáticamente.
Para verificar el resultado, comparemos el total de la tabla con el resultado de nuestra fórmula y asegurémonos de que estén completamente en línea:
Estas son las 6 formas efectivas de sumar los valores más altos en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
(archivo .xlsx)