martes, 24 de enero de 2012

Una variante de suma acumulada en Excel.

Recientemente he contestado a una cuestión de sumas acumuladas en nuestras hojas de Excel, que en sí no tiene nada de complicado, salvo por lo particular en la forma de contruir un rango dinámico asociado a una celda validada. La cuestión que se planteó fue:
...Tengo una tabla de 5 proveedores con sus compras por cada mes del año, quiero que en una columna me sume para el mes que le establezca el importe acumulado de lo comprado hasta ese mes y que se actualice de forma automática para todos ellos. ¿Cómo podría hacerlo?
Por ejemplo:
enero febrero marzo abril mayo ... diciembre Total acumulado
Proveedor 1 12 15 10 5 10 23
Proveedor 2 5 10 3 0 2 14

Si quiero que me dé el acumulado de lo comprado en NOVIEMBRE o en FEBRERO ...


Veamos la tabla en nuestra hoja de cálculo:



Como vemos el trabajo es sencillo, debemos acumular las cantidades para cada proveedor desde enero hasta el mes desplegado en la celda A1. La fórmula final será entonces:
=SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)))

La explicación o la clave de esta fórmula es la construcción de un rango dinámico en función al valor de una celda.
Fijémosnos que con COINCIDIR($A$1;$B$3:$M$3;0)+1 obtendríamos el número de la columna donde se ha encontrado el mes buscado, por eso sumanos 1 a COINCIDIR, para salvar que en la primera columna A se encuentra el nombre del proveedor.
Con este número de columna, y la función FILA() que identifica en qué número de fila nos encontramos, obtenemos la referencia de la celda hasta donde queremos acumular:
DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)
devolvería, para el caso del ejemplo la referencia: $F$4.
Finalmente, sólo nos queda que Exccel reconozca ese valor como una referencia, asi que le aplicamos INDIRECTO, que unido a la celda $B4, tenemos el rango buscado $B4:$F$4.
Sumamos dicho rango, y resolvemos nuestro problema:
=SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)))

30 comentarios:

  1. Y si tuviésemos en diferentes hojas los datos de las compras efectuadas en cada uno de los años anteriores, ¿cómo podríamos obtener al lado de la columna acumulado, el acumulado de cada uno de esos años referidos al mes solicitado? Por ejemplo acumulado hasta mayo del año -1, año -2. Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que introducir para cada año una variante en la fórmula DIRECCION indicándole de qué hoja quieres tener el acumulado.
      La función DIRECCIÓN tiene los siguiente argumentos:
      DIRECCION(fila; columna; [abs]; [a1]; [hoja])
      Asi que quedaría algo así:
      =SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1;;"Hoja2")))
      Espero te sirva... un saludo

      Eliminar
    2. Algo debo de hacer mal porque no me sale.
      En el ejemplo que tu has puesto, al lado de la columna "acumulado" he creado una columna "acumulado n-1", he copiado tal cual tu fórmula y donde pones "hoja2" selecciono la hoja de los datos del año anterior, pero me da error:

      =SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1;;Hoja2!)))

      ¿La selección de los datos de la hoja2 estarían bien? Son los 2 punto y coma? Muchas gracias por tu ayuda

      Eliminar
    3. Perdón, al escribir la fórmula me 'comí' un punto y coma...
      Fijate en los argumentos de la función DIRECCION, el quinto argumento es donde indicas la Hoja.
      Un saludo

      Eliminar
  2. lo copié todo exacto y no me sale, al parecer hay un error en la función FILA() tienen alguna otra alternativa?
    gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      asegúrate que la fórmula la has copiado tal cual, y que los datos se encuentran en la misma posición; es importante para el cálculo.

      De todas formas ya te digo que la fórmula está probada y funciona perfectamente.

      Comentas.
      Slds

      Eliminar
  3. Hola; tengo que actualizar constantemente una hoja con resultado, deseo se guarde el acumulado, para cuando indique nuevo resulado vuelva sumara la cantidad de veces al acumulado. como manejo la formula.

    ResponderEliminar
    Respuestas
    1. Hola,
      discúlpame pero no comprendo el problema.
      Si quieres obtener siempre un resultado acumulado actualizado, según se introducen nuevos sumandos, es suficiente una función SUMA sobre el rango de los valores,
      Si quieres hacerlo 'dinámico' contruye y convierte el rango a sumar como Tabla.
      Slds

      Eliminar
  4. Tengo la siguiente formula que va a buscar a otra hoja y por mucho que lo intento no funciona:

    +SUMA(HOJA2!$B$2:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;HOJA2!B1:F1)+1;;;"HOJA2!")))

    Sin embargo si hago el ejercicio con todo sobre la misma hoja SI que me funciona.

    Lo malo es que necesito obtener el resultado en una hoja diferente de donde esta la tabla con los valores.

    me puedes ayudar?

    gracias!

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      bueno, necesitaría segurarme pero creo que el problema viene de incluir INDIRECTO (DIRECCION(...))
      ya que obtienes el vlaor de dicha celda, cuano a ti te interesa en enste caso sóla la referencia.
      Prueba con
      =SUMA(INDIRECTO("Hoja2!$B$2:"&DIRECCION(FILA();COINCIDIR($A$1;Hoja2!B1:F1)+1)))

      Slds cordiales

      Eliminar
  5. Hola Ismael,
    Así funciona!
    Lo único malo que tiene es que lo que está entre comillas: "Hoja2!$B2:" cuando lo tiro sobre las celdas de las filas inferiores se queda siempre con el valor B2 y no cambia en función de la fila en la que está...

    Tendría que ir celda por celda a cambiar ese valor para que el resultado sea correcto.

    Hay alguna solución?

    Un millón de gracias

    saludos

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      si la fórmula estuviera en alguna celda de la fila 2 podrías probar con
      =SUMA(INDIRECTO("Hoja2!$B"&FILA()&":"&DIRECCION(FILA();COINCIDIR($A$1;Hoja2!B1:F1)+1)))

      Slds

      Eliminar
  6. Hola Ismael,
    Funciona perfectamente.
    Muchas gracias.

    ResponderEliminar
  7. Buenas tardes, requiero ayudar para lo siguiente:
    Tengo una serie de datos por mes en una hoja, en la segunda hoja quiero que me traiga el valor acumulado del mes que yo le indique en la hoja 2, pero ademas por referencia (producto). Es parecido al primero de este ejemplo, solo que quiero que ese acumulado me lo deje en la hoja para compararlo con otros datos. Me podrían ayudar por favor. Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      daría igual que estuviera en una hoja que otra... lo que importa es que si además de por Fecha quieres concretar por 'Producto' tendrás que adecuar la fórmula dada:
      =SUMA($B4:INDIRECTO(DIRECCION(FILA();COINCIDIR($A$1;$B$3:$M$3;0)+1)))
      y en lugar de donde pone FILA() sustituirlo por otro COINCIDIR que localice la fila correspondiente al producto que requieras...

      Espero te sirva, con los datos aportados poco más puedo decirte.
      Slds

      Eliminar
    2. Buenas tardes, gracias por tu colaboración, pero definitivamente no me da el resultado, me da error en la formula

      Eliminar
    3. Puedes enviarme a
      excelforo@gmail.com
      lo que tengas, le echaré un vistazo.

      No olvides poner una explicación de qué necesitas.

      Saludos

      Eliminar
  8. Tengo una duda que te he enviado por mail pero considero que será mejor plantearla por aquí y así si a alguien le sucede lo mismo tendrá la duda y espero que también la solución.
    En una hoja llamada DATOS tengo una cuenta de perdidas y ganancias por meses, de Enero a Diciembre. En otra hoja quiero que cuando en la celda E1 le pongo un mes concreto, me indique para cada partida de la cuenta de perdidas y ganancias el valor acumulado a ese mes. Empezando por las ventas, que es la primera, la formula que he puesto es la siguiente:
    =SUMA(INDIRECTO("DATOS!$B$2:"&DIRECCION(FILA();COINCIDIR($E$1;DATOS!$B$1:$M$1;0)+1)))
    Esta formula está en la celda F4, y no me calcula correctamente el importe (si la pongo en la casilla F1 si me lo calcula bien).
    Desglosando la formula veo que el error esta en la segunda parte de la formula:
    =DIRECCION(FILA();COINCIDIR($E$1;DATOS!$B$1:$M$1;0)+1)
    Ya que si esa formula la pongo en cualquier celda de la fila 1, me da $B$1, si la pongo en cualquier celda de la fila 2, me da $B$2 y así sucesivamente. Podrías decirme donde está el error? Muchas gracias de antemano!!!

    ResponderEliminar
    Respuestas
    1. Hola Amadeo,
      efectivamente el 'fallo' está en la función DIRECCION ya que no estás ajustando la FILA.
      Ten en cuenta que FILA te devuelve la fila donde se encuentre la fórmula en ese momento.. en el fichero que tu me has enviado por mail, tienes la fórmula en la hoja CMD en la fila 4 !!, así que deberías ajustarla restándole 2, para que el rango resultante quede referido a la fila 2.

      En tu caso
      =SUMA(INDIRECTO("DATOS!$B$2:"&DIRECCION(FILA()-2;COINCIDIR($E$1;DATOS!$B$1:$M$1;0)+1)))

      Saludos

      Eliminar
    2. Muchisimas gracias Ismael, está claro...no había entendido la funcion de "FILA()" en la formula

      Eliminar
  9. Buenas tardes, primero que todo gracias por compartir tú conocimiento. Segundo, espero me puedas ayudar, me encuentro realizando una pequeña aplicación de inventario en excel, donde quiero que en existencias lleve un acumulativo, es decir:
    A1 es 20 en entradas
    B1 es 4 en salidas
    C1 quedaría 16 en existencias
    Necesito que al volver a ingresar un dato en entradas en A1(por ejemplo 4), me sume sobre el valor final que es 16, dando como resultado 20, el cual quedaría en existencias.
    Espero me puedan asesorar.
    Gracias

    ResponderEliminar
    Respuestas
    1. Gracias Carolina,
      bien, creo que lo explicado en esta entrada es lo que necesitas:
      http://excelforo.blogspot.com.es/2011/01/vba-macro-para-acumular-cantidades.html

      Pienso responde exactamente a lo que planteas... tendrás que usar algo de programación...
      Saludos cordiales

      Eliminar
  10. Necesito realizar una formula similar solo que mi. Datos estan en columna hacia abajo

    Seria tan amable de ayudarne

    ResponderEliminar
    Respuestas
    1. Hola,
      la idea sería similar... cambiando la variable columna por fila:
      =SUMA(B$4:INDIRECTO(DIRECCION(COINCIDIR($A$1;$B$3:$B$14;0)+1;COLUMNA()))

      pero habría que ajustarlo a tu caso concreto...
      Saludos

      Eliminar
  11. necesito el valor acumulativo de dos hojas de excel donde en una columna tengo PLACA DE VEHICULO y en otra VALOR
    una de octubre y otra de noviembre.
    pero en ambas hojas no estan las misma placas como hago para hacer un acumulativo de oct a nov por placas

    ResponderEliminar
    Respuestas
    1. Hola Aleja,
      un placer saludarte y poder ayudarte.
      Lo que yo haría sería utilizar la herramienta Consolidar o bien los Rangos de consolidación múltiples del asistente de tablas dinámicas de 2003..
      Si buscas en las categorías del blog verás ejemplos de esto
      Saludos

      Eliminar
  12. Realmente me sirvió mucho...lo que has publicado ene esta pagina.....te mando mis felicitaciones y agradecimneto por comprtir tus conocimentos y tu ayuda desinteresada......un saludo desde Argentina y desde Córdoba!!!!!!

    ResponderEliminar