Ya hemos mostrado en este blog cómo hacer sumas condicionales con más de una condición
.
En esta nota veremos como enfrentarnos con situaciones especiales del tipo, por ejemplo, cómo sumar los cinco mayores números de una lista.
Por ejemplo, supongamos esta lista de números
Los cinco números mayores de la lista ( 94, 98, 93, 88, 87) suman 454. No hay forma en la cual podamos usar SUMAR.SI para sumar los números mayores de la lista. Pero si podemos hacerlo con SUMAPRODUCTO (o con fórmulas matriciales). EN nuestro caso
=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)<=5)) donde lista es un nombre que define el rango A2:A21 SUMAPRODUCTO crea dos matrices. Una contiene todos los números del rango "lista" (A2:A21); la otra matriz contiene valores VERDADERO y FALSO producidos por la función JERARQUIA. Cuando el valor calculado es menor o igual a 5, es VERDADERO; en caso contrario, FALSO. Como ya hemos visto, Excel interpreta VERDADERO como 1 al emplearlo en una operación y FALSO como 0.
Para calcular los 5 menores podemos recurrir a la función CONTAR, para determinar cuantos miembros hay en el rango y así poder determinar cuales son los últimos 5 en orden decreciente
=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista)>CONTAR(lista)-5))
o esta mas sencilla, sin CONTAR
=SUMAPRODUCTO(lista*(JERARQUIA(lista;lista;1)<=5))
Si queremos sumar todos los números pares usamos la fórmula
=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=0))
o
=SUMAPRODUCTO(lista*(RESIDUO(lista;2)<>1))
Para los impares usamos
=SUMAPRODUCTO(lista*RESIDUO(lista;2))
que es el equivalente a
=SUMAPRODUCTO(lista*(RESIDUO(lista;2)=1))
pero nos ahorra dos paréntesis.
La función RESIDUO(número;2) da 0 si el número es par. Por eso la expresión RESIDUO(lista;2)=0 es VERDADERO si el número en la "lista" es par.
Para sumar los múltiplos de un determinado número, también podemos usarla función RESIDUO. Por ejemplo, para sumar todos los números que son múltiplos de 3 en nuestra lista, usamos la fórmula
=SUMAPRODUCTO(lista*(RESIDUO(lista;3)=0))
Otro uso de RESIDUO es sumar todos los valores que de las filas pares o impares. Por ejemplo, para sumar todos los valores del rango "lista" que están en filas pares, usamos
=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)=0))
De la misma manera, para sumar los valores en filas impares podemos usar:
=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);2)<>0))
Si queremos sumar cada tres filas, a partir de la fila 3 podemos usar esta fórmula
=SUMAPRODUCTO(lista*(RESIDUO(FILA(lista);3)=0))
Si queremos sumar cada 3 filas, pero empezando de la primer fila del rango, usamos esta fórmula
=SUMAPRODUCTO((RESIDUO(FILA(lista);3)=2)*lista)
donde "2" es el número de la primer fila del rango.
La fórmula general es =SUMAPRODUCTO((RESIDUO(FILA(lista);n)=m)*lista)
Donde n es el "escalón" (número de filas que queremos saltear en la cuenta) y m es la fila de donde comenzamos a sumar.
El archivo con las fórmulas se puede descargar aquí.
Technorati Tags: MS Excel
Saludos.
ResponderBorrarEspero me puedas ayudar con lo siguiente:
La sumatoria de los siguientes dígitos , 07- 11 -13 – 23 – 25 es 79. Si quisiera reunir como un conjunto todas las combinaciones de 5 dígitos del 1 al 31 que su sumatoria este en un rango de 50 a 120 como podría hacerlo en excel?
En excel existe alguna formula o una manera lógica de saber cuantas combinaciones de 5 dígitos estarían en este rango?
Gracias por tu respuesta
Raúl Rivera
Raúl
ResponderBorrartodo lo que requiera cálculos puede hacerse con Excel. Pero te sugiero que pongas to consulta en un foro de Excel. El marco de los comemtarios no da para semejante explicación.
Gracias Jorge.
ResponderBorrarVeré donde puedo hacer la pregunta
Raúl Rivera
Enhorabuena por tu blog, siempre me resuelve las dudas que tengo en Excel.
ResponderBorrar¿Cómo variaría la primera fórmula, la de sumar los 5 mayores números, en caso de que en la lista hubiera celdas vacías?
Es que en tengo dos columnas (A y B), y en la columna C he calculado el cociente A/B, de forma que la celda quede vacía si B es 0. Y quiero sumar los valores más altos de la columna C. En ese caso, la fórmula de suma.producto me da error, porque en la columna C hay valores vacíos.
Muchas gracias!
Hola,
ResponderBorrarla celda en la columna C no está vacía ya que contiene una formula. Supongo que habrás usado un fórmula como esta:
=SI(B2=0,"",A2/B2)
Así que el error proviene de otro lado, tal vez alguna de las celdas en el rango tiene un valor de error.
Pues la verdad es que como dices, he usado esa fórmula, pero no encuentro ningún error. Es más, me he creado en una hoja nueva un caso más simples con menos celdas, y me ocurre el mismo error.
ResponderBorrarEste es mi ejemplo:
*En la columna A, una serie de números.
*En la columna B, la siguiente fórmula: =SI(A1=2,"",A1*2) en la celda B1, =SI(A2=2,"",A2*2) en la celda B2...
*En la celda C1, la siguiente fórmula: =SUMAPRODUCTO(B1:B5*(JERARQUIA(B1:B5,B1:B5)<=2))
Pues bien, si algún valor de la columna A es 2 (es decir, que en la columna B no se escriba nada), me da error la fórmula. Si no, me lo hace bien.
Lo he conseguido "solucionar" haciendo que en vez de celdas vacías me ponga un valor negativo muy alto (-1000000) y que me lo escriba en blanco para que no se vea. Así, como quiero que me sume los valores más elevados, la fórmula funciona.
Muchas gracias de todas formas por tu pronta respuesta, la fórmula que he aprendido aquí me ha venido muy bien, aunque todavía no la entienda del todo.
Buen día Jorge
ResponderBorrarSoy fan de su blog, realmente he aprendido mucho mas de esta herramienta tan útil en estos días, sin embargo quiero consultarle, ya que tengo una base con un numero de filas de 340,000 y 31 columnas con esta información hago un pequeño scorecard que tiene ventas de clientes, mismas que por medio de una validación de datos en forma de lista, puedo jugar con los diferentes rangos que tengo por dia, de acuerdo a los años que tengo cargados en la base, pero me he topado con un problema, necesito comparar las ventas del mes de Enero 2014 vs mismo mes año anterior, pero me arroja ceros, será que la función ha alcanzado su rango maximo de filas? o que sera el problema?
Supongo que se trata de algún error lógico en la fórmula que resulta en un conjunto vacío. Si se tratara de algún error verías #N/A o #REF!
ResponderBorrarDe todas maneras, dado el tamaño de la base de datos SUMAPRODUCTO no me parece la fórma más eficiente para analizar los datos. Te sugiero la posibilidad de usar tablas dinámicas para el sumario y luego GETPIVOTDATA para extraer los datos relevantes.