jueves, diciembre 30, 2010

Sumar cada enésimo valor de una serie con Excel

Recibí esta consulta: dado un rango de valores, ¿cómo hacer para sumar los valores de cada quinta fila con Excel?

Supongamos estos valores en el rango A1:A20



Esta fórmula suma cada quinto valor en el rango

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),5)=0)*A1:A20)

Podemos generalizar la fórmula reemplazando la constante 5 de la función RESIDUO por una referencia a una celda

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),D1)=0)*A1:A20)



Podemos cambiar el valor de D1 para ver el resultado de sumar cada n filas



Esta fórmula funciona de la siguiente manera:

RESIDUO(FILA(A1:A20),D1)=0 da como resultado VERDADERO sólo si el número de fila es múltiplo de 5 (o el número que hayamos ingresado en D1)



Luego al multiplicar los valores de la serie por los correspondientes valores VERDADERO o FALSO obtenemos

14 comentarios:

  1. Excelente explicacion y gracias por tu labor de querer ayudar a los que lo necesitan
    muchas gracias

    ResponderBorrar
  2. Por las dudas, al menos en office 2003, da error la fórmula pues el separador de parámetros es el ";" no la coma. (Antes del 5 o antes de la referencia D1)
    Excelente blog, Felicitaciones y Feliz 2011!!

    ResponderBorrar
  3. El separador de parámetros puede ser la coma (,) o punto y coma (;) dependiendo de las deficiones regionales del sistema, no de la versión de Excel.

    ResponderBorrar
  4. Una pregunta:
    Por qué en la función SUMAPRODUCTO le pones como multiplicador el "*"; es que por ejemplo, en la ayuda de Excel para dicha fórmula te separa los argumentos mediante ";". Sin embargo si tengo:
    1 FALSO
    2 FALSO
    3 FALSO
    4 FALSO
    5 VERDADERO
    SUMAPRODUCTO(A1:A5;B1:B5)=0
    SUMAPRODUCTO(A1:A5*B1:B5)=5
    Es decir, con el símbolo que te dicen en la ayuda de Excel (";"), no sale pero sile pones "*", si que sale...

    ResponderBorrar
  5. Así es, hay que usar el operador * cuando usamos una vector de VERDADERO/FALSO en SUMAPRODUCTO para forzar a Excel a reconocer al VERDADERO como 1 y al FALSO como 0.
    También podrías usar
    SUMAPRODUCTO(A1:A5;--B1:B5)
    El doble símbolo - multiplica a todos los miembros de B1:B5 por 1 (como nos enseñaron en la escuela, menos por menos es posiitivo o sea que multiplicamos por 1).

    ResponderBorrar
  6. Gracias Jorge,
    Lo comprobé y se cumple: también podrías hacer para que te saliera utilizando el símbolo ";":

    SUMAPRODUCTO(A1:A5;(B1:B5)*1), que es prácticamente lo mismo que has comentado en el comentario precedente, pero quizá (para mi) más intuitivo.

    Un Saludo y Feliz Año

    ResponderBorrar
  7. Sr: Dulkenman
    En relacion con su nota Sumar cada enésimo valor de una serie con Excel, le planteo la siguiente duda:
    Dispongo de un listado de datos y necesito determinar el valor promedio de cada, por ejemplo 5 valores, pero de forma tal que el algoritmo me permita listar en columna aparte todos estos valores promedio.

    Le Saluda

    Jesus Perez

    ResponderBorrar
  8. Hola Jesús,
    en principio necesitarías una fórmula para cada grupo de valores. Ten en cuenta que en una celda sólo puede haber una fórmula.
    Si quieres una solución que haga el cálculo en forma dinámica, tu alternativa sería usar tablas dinámicas (pivot tables), pero la explicación excede el marco de este comentario.
    Puedes ponerte en contacto conmigo por mail privado (fijate en el enlace Ayuda).

    ResponderBorrar
  9. Hola Jorge. Mi nombre es Augusto
    Tengo una serie de registros y quiero contar los registros según 2 criterios. En 2 columnas tengos los criterios que utilizaré para contar los registros que cumplan uno u otro criterio (no ambos criterios), pero sucede que si un registro cumple con los 2 criterios simultaneamente, me lo cuenta doble porque lo que hace es =sumaproducto(--(1 + 1)) = 2 Tengo la siguiente funcion:SUMAPRODUCTO(--(LTV1>0.8)+(DTI1>0.5)), deberia arrojar 16 registros pero me envia 18, es porque hay 2 registros que cumplen ambos criterios. Ya probe con la siguiente variante :=SUMAPRODUCTO(--O((LTV1>0.8),(DTI1>0.5))) y lo que me arroja es 1.
    LTV1 y DTI1 son nombres de rangos.
    Agradeciendote tu ayuda

    ResponderBorrar
  10. Augusto,
    podrías restar los valores que cumplen con ambas condiciones. La fórmula sería

    SUMAPRODUCTO((LTV1>0.8)+(DTI1>0.5)-((LTV1>0.8)*(DTI1>0.5))

    No hace falta usar el doble "--" (tampoco en tu fórmula).

    ResponderBorrar
  11. Gracias Jorge, tu formula me funciono bien.
    Porqué no habra funcionado la formula =SUMAPRODUCTO(--O((LTV1>0.8),(DTI1>0.5))) ? al menos me hubiese arroja el #18 y no el #1 como lo venia haciendo.
    Nuevamente Gracias.

    ResponderBorrar
  12. Excelente aporte Don Jorge, mejores páginas que he visto, porque adicional a dar excelente material, se explica como se hacen. Muchas Gracias.

    ResponderBorrar
  13. como hago para sumar cada enésimo valor de una serie de celdas no continuas pero q se encuentran en una misma columna con Excel

    ResponderBorrar
  14. Tendrás que crear una columna auxiliar con algún indice que indique qué valores queremos sumar.
    No puedo darte una solución concreta porque ésta depende de la estructura de tus datos.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.