lunes, 16 de abril de 2012

Más sumas acumuladas en Excel.

Parece que algo muy habitual en nuestras hojas de cálculo de Excl es trabajar, en diferentes formas, con importes a acumular... nuestros problemas aparecen en función de distribuciones concretas en la hoja de trabajo.
Veamos el planteamiento de un lector, que tiene una dificultad añadida a la suma acumulada, y es que los importes a acumular están en columnas alternas:

...quisiera sumar las celdas b6,d6 y f6 , asi sucesivamente, en la celda $p$6 la suma obviamente con relacion a la celda $b$3.
Y que me coloree con formato condicional las celdas de acuerdo a los meses sumados...

Veamos la imagen del informe:

Más sumas acumuladas en Excel.


Antes de explicar nuestras fórmulas, analizaremos la colocación de los importes a sumar en base al mes desplegado (utilizando un Control de número - control de formulario); se ve claramente como alternan por columnas los importes y un porcentaje (que no nos interesa), particularmente los importes a sumar están situados en columnas pares.
Este control desplegará un valor que hemos asociada en la celda C3, valor en nuestro ejemplo que va de 1 a 6, que luego convertiremos, en la celda B3, en FECHA con un sencillo =BUSCARV(C3;$V$5:$W$10;2;0) que buscará en una tabla anexa que relaciona 1 a ene-12, 2 a feb-12, 3 a mar-12, etc
Bajo estas premisas trabajaremos matricialmente en primer lugar, en P6 escribiremos:
{=SUMA(SI(B5:M5<=$B$3;($B$6:$M$6)*ES.PAR(COLUMNA($B$6:$M$6))))}
que ejecutada matricialmente nos proporciona la suma acumulada de Ventas hasta la fecha desplegada, el valor de la celda B3. Esta matricial sólo sumará aquellos valores de B6:M6 colocados en una columna PAR, siempre que la fecha correspondiente de B5:M5 sea menor o igual a la desplegada en B3.
Vemos en la imagen el resultado obtenido desplegando otra fecha:

Más sumas acumuladas en Excel.



A continuación obtendremos el mismo resultado pero con una fórmula directa, esto es, sin actuar matricialmente. Pero para conseguir el mismo resultado no tendremos más remedio que aplicar ciertos valores complementarios en el rango B4:M4 con la siguiente fórmula:
=SI(Y(ES.PAR(COLUMNA());COLUMNA()<=(2*$C$3));1;0)
con la que conoceremos si la columna es candidata a ser sumada o no, esto es, si es una columna PAR y además es inferior o igual a la fecha desplegada...

Más sumas acumuladas en Excel.


Apoyándonos en esta fila auxiliar (que es la que verfica qué importes hay que sumar), sólo nos queda aplicar un sencillo SUMAPRODUCTO en la celda P7:
=SUMAPRODUCTO($B$4:$M$4;$B$6:$M$6)
que nos lleva, logicamente, al mismo resultado:

Más sumas acumuladas en Excel.



Hemos visto, en definitiva, en esta entrada dos formas de trabajar con Excel, una matricial, normalmente más directa pero más compleja, y una forma 'normal' que habitualmente requiere de filas/columnas auxiliares.

4 comentarios:

  1. Buenas tardes, para solucionar este problema yo utilizo la función ELEGIR, donde el número índice sería el mes acumulado que quiero sumar ( 1 enero, 2 marzo...).
    Por poner un ejemplo, la celda B1 la utilizaría para indicar el nº índice (valores del 1 al 12) y las celdas B5, D5, F5.... X5, como los valores que quiero sumar acumulados.
    La fórmula sería:
    =ELEGIR($B$1;B5;B5+D5;B5+D5+F5;B5+D5+F5+H5;B5+D5+F5+H5+J5;B5+D5+F5+H5+J5+L5;B5+D5+F5+H5+J5+L5+N5;B5+D5+F5+H5+J5+L5+N5+P5;B5+D5+F5+H5+J5+L5+N5+P5+R5;B5+D5+F5+H5+J5+L5+N5+P5+R5+T5;B5+D5+F5+H5+J5+L5+N5+P5+R5+T5+V5;B5+D5+F5+H5+J5+L5+N5+P5+R5+T5+V5+X5)

    Para conseguir que las celdas sumadas aparecieran coloreadas utilizaría el formato condicional en cada una de ellas, así para la celda de enero B5, la fórmula sería =($B$1>=1), para la de febrero D5, la fórmula sería =($B$1>=2) y así sucesivamente hasta diciembre que sería =($B$1>=12)

    Un cordial saludo
    Blas

    ResponderEliminar
    Respuestas
    1. Hola Blas,
      muchas gracias por tu comentario y por tu propuesta de solución... un poco más larga, pero igual de efectiva, desde luego.
      Los problemas al hacerlo con tu forma es si se necesitara trabajar con 5 años (72 meses), por ejemplo, tu fórmula requeriría 72 argumentos, lo que complicaría su elaboración.
      Un cordial saludo!!!

      Eliminar
  2. Gracias Ismael por tu rápida respuesta, me temía la complejidad....ahora reduzco..tengo este código, y con ayuda del formato condicional, marco en este horario o celda, con un clik, amarillo y valor 1, aunque he de cambiar de objetivo para conseguir, al volver a este y con otro clik transparente y valor 0. Es posible siguiendo estas pautas añadir un condicionante al codigo para que marque en el paso siguiente al 1 el 0.5.

    codigo: Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("D8:L39,P8:X39,AB8:AJ39,D47:L78,P47:X78,AB47:AJ78")) Is Nothing Then
    If Target.Interior.Color = RGB(255, 255, 0) Then
    Target.Interior.ColorIndex = xlNone
    Target.Value = 0
    Else
    Target.Interior.Color = RGB(255, 255, 0)
    Target.Value = 1
    End If
    End If
    End Sub

    Gracias otra vez por tu dedicación y tiempo, un saludo.

    ResponderEliminar
    Respuestas
    1. Hola Sergio,
      creo que este comentario sigue el hilo de otro que colgaste en algún otro sitio del blog...

      Si quieres ir restando -0.5 al valor de la celda marcada, solo inserta:
      Target.Value=Target.Value - 0.5

      con eso te debería funcionar...
      Saludos

      Eliminar