martes, 18 de enero de 2011

VBA: macro para acumular cantidades.

Contestaré hoy a una petición curiosa que me ha llegado a través del mail. Un usuario quería que en una tabla definida en un rango concreto (A2:C5) se fueran acumulando cantidades según introducía entradas de datos:

...Quiero que en cada fila de concepto, pueda meter una cantidad y que se sume en acumulado, luego, en una 2ª entrada meter en otra cantidad y que acumule la de la 1ª entrada y la de la 2ª y as'ísucesivamentre. Y todo esto que se pueda hacer en cada fila de conceptos. Como puedes ver en el siguiente ejemplo en la fila leche en la primera entrada meto 20 y acumula 20. En la segunda entrada en la misma casilla borro 20 y meto 30 y en acumulado debe quedar 50. En la tercera entrada en el concepto leche, en la misma casilla borro 30 y meto 40, acumulándose en lqa misma fila de acumulado 90. Así con el resto de filas: pan, tomate...
entrada 1
concepto cantidad acumulado
leche 20 20
pan 10 10
tomate 40 40

entrada 2
concepto cantidad acumulado
leche 30 50
pan 15 25
tomate 25 65

entrada 3
concepto cantidad acumulado
leche 40 90
pan 25 50
tomate 5 70


Veamos cuál es la plantilla sobre la que volcaremos nuestra programación:


La idea es que cada vez que el usuario introduzca los nuevos valores para la Leche, Pan y Tomate, podamos ejecutar nuestra macro, para que automáticamente genere la suma acumulada de esta entrada con todas las anteriores.
Para ello he construido una macro muy sencilla, que ejecutaremos tras la introducción de los nuevos datos para los distintos conceptos de cada entrada.
Abrimos el Editor de VBA (Alt+F11) e insertamos un nuevo Módulo, en el cuál incluiremos el siguiente código:

'macro que calcula el acumulado de todas las entradas de datos.

Sub acumular()
Dim iAs Integer
x = 1
For i = 3 to 5
'realiza la suma acumulada del valor anterior mas el nuevo
'para cada concepto (Leche, Pan, Tomate).

Cells(i, 3).Formula = Cells(i, 3).Value + Cells(i, 2).Value
'limpia los campos para poder introducir los nuevos valores
'para cada concepto (Leche, Pan, Tomate).

Cells(i, 2).ClearContents
Next i
'Da formato personalizado, añadiendo el texto Entrada
Range("A1").NumberFormat = """Entrada ""0"
'genera un autonumérico en la celda A1
'que nos indicará qué entrada es la última introducida.

Range("A1").Value = Range("A1").Value + x

End Sub


Para ejecutar de manera cómoda nuestra macro, la asignaremos a un botón.
Al ejecutar nuestra macro acumular conseguimos identificar la última entrada editada (celda A1), así como el último valor acumulado (rango C3:C5).


Tras 5 entradas de valores hemos obtenido un acumulado de Leche 50, Pan 100 y Tomate 150, quedando lista la tabla para la edición de los valores de la siguiente entrada.

26 comentarios:

  1. Me sirvió de mucho la solución que propones. Para una situación similar, pero con 17 columnas hice lo siguiente.

    For c = 8 To 17
    For f = 6 To 100

    Hoja4.Cells(f, c) = Hoja4.Cells(f, c) + Hoja3.Cells(f, c)

    Next f

    Next c

    Pero tengo el problema de que el contador de f (filas) no se detiene hasta que hace la iteración 100 aunque, por ejemplo, a partir de la fila 10 ya no hayan más celdas que contengan datos.

    Me podrías ayudar a que la operación se detenga en el momento en que llegue a una fila en blanco o sin datos.

    Saludos,

    Roberto

    ResponderEliminar
  2. Hola Roberto,
    entiendo que tus registros empiezan en la fila 6 pero no sabes hasta qué fila llegan (no que tengas saltos entres esas 94 filas que has marcado).
    Si es así, entonces yo cambiaría el 100 por una variable definida por ti...
    echa un vistazo a
    http://excelforo.blogspot.com/2010/05/macro-para-convertir-en-numero-valores.html
    en este post puedes ver un ejemplo de cómo definir la variable que necesitarías.
    Basicamente con la variable localizamos la última fila con datos de tu hoja de trabajo.
    Espero te sirva.
    Un saludo

    ResponderEliminar
  3. Hola,
    He seguido los ejemplos anteriores porque busco la solución para acumular en una celda, el valor acumulado de varias celdas alternadas en una misma fila, dentro de un rango definido, pero que solo contengan el valor 100. conoces una macro que lo haga?

    Saludos y muchas gracias desde ya.

    ResponderEliminar
    Respuestas
    1. Hola Fernando,
      has pensado emplear una función SUMAR.SI para acumular esas cantidades.
      Por ejemplo, el rango definido es A1:A1000 en el que hay diferentes celdas con el valor 100; entonces C1 escribes:
      =SUMAR.SI(A1:A1000;100)

      En principio no hace una macro para eso.
      Slds

      Eliminar
  4. Hola todos.

    A mi, la primera solucion me funcionaria perfectamente pero el problema es que yo tengo una cantidad enorme de articulos. (Mas de 1000).
    Como tendria que hacer en ese caso? porque asi solo me funcionan las primeras 3

    Agradecido

    ResponderEliminar
    Respuestas
    1. Hola Joao,
      sólo cambia el límite del FOR:
      For i = 3 to 5
      por
      For i = 3 to 1003

      Ese 1003 representa la última fila donde se encuentren tus artículos.

      Slds cordiales

      Eliminar
  5. Hola a todos los foristas.
    Mi problema es que tengo varios articulos en Excel que quiero acumular(restar del stock) a medida que voy vendiendo. Hice la macro en VB y me funciona para una celda pero necesito hacer para la totalidad de artículos. Me podrían explicar como hacerlo? Gracias.

    ResponderEliminar
    Respuestas
    1. Hola José,
      la macro, como puedes ver en el ejemplo, está pensada para un listado de artículos...
      Sigue los pasos y lógicamente ajusta los rangos a tu caso concreto.

      También podrías gestionar tu Stock con funciones tipo SUMAR.SI.CONJUNTO

      Saludos

      Eliminar
  6. Hola, he probado esta macro y la he ajustado a mis necesidades y me funciona de maravilla, mil gracias, pero tengo una situación, tengo un archivo con 6 hojas diferentes que se llaman "lunes, martes, miércoles, jueves, viernes, sábado", estas hojas tienen el mismo formato (las mismas filas y columnas). Cada una de estas hojas las voy llenando con datos numéricos conforme al dia que se le corresponde, o sea la que dice lunes la lleno el lunes y asi sucesivamente hasta el sábado. Lo que quiero es que en una hoja adicional que se llama "resumen" me valla acumulando todos los datos que voy introduciendo en las otras, o sea que lo que introdusco el lunes se me acumule en la hoja resumen, lo que introduzco el martes se sume a lo que lo del lunes pero en la hoja resumen y asi sucesivamente hasta el día sábado, pero que no se borre lo que tengo en las diferentes hojas.
    Imagino que esta misma macro se puede pero hay que hacer los ajustes para trabaje con varias hojas.

    ResponderEliminar
    Respuestas
    1. Hola Rolando,
      no hace falta macro para esto, simplemente aplica una fórmula SUMA en 3D, aquí puedes ver un ejemplo:
      http://excelforo.blogspot.com.es/2012/02/formulas-3d-tridimensionales-en-excel.html
      y listo

      Slds

      Eliminar
  7. hola mira y para hacer sumatoria de 105 datos como aplicaria no comprendo agradezco resouesta

    ResponderEliminar
    Respuestas
    1. Hola Lina,
      el ejemplo está pensado para introducir manual y secuencialmente cada grupo de datos... si tienes 105 datos, tendrás que introducirlos uno tras otro.
      Salvo que ya los tengas en alguna parte de la hoja, en cuyo caso podrías recorrerlos con algún loop y replicar la acción.
      Saludos

      Eliminar
  8. Hola, me han ayudado mucho tus aportes, gracias.
    Ahora tengo un problemita, tal vez puedas ayudarme. Tengo un archivo en el cual una macro crea una copia de una hoja modelo con un formato establecido. Mi duda es si se puede hacer que los datos ingresados a estas hojas nuevas se copien a una hoja llamada "informe" en el mismo libro. Se debe tomar en cuenta que las hojas se crean constantemente y los datos deben copiarse no sumarse y colocarse en columnas, una por cada hoja nueva. No se si me logre dar a entender o si se puede. De ante mano muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola,
      entiendo la idea es ir copiando sobre una hoja 'Informe' los nuevos datos añadidos en hojas (nuevas y viejas ya existentes).
      Si es así, este post te puede ayudar:
      http://excelforo.blogspot.com.es/2014/04/vba-select-case-para-distribuir-datos.html

      La cosa sería recorrer siempre todas las hojas (menos la de informe) para ir llevando los registros a 'Informe'...
      En caso necesario habría que añadir un control , por ejemplo con un IF..THEN...), para verificar si existe ya pegado en Informe el registro...

      Espero te sirva la pista.
      Saludos

      Eliminar
    2. Gracias...!!!
      Me has sacado de un apuro.
      Mil gracias por tus aportes.
      Te felicito y te animo a seguir siempre ayudando.

      Eliminar
  9. necesito registrar los abonos a un columna de celdas acumulativa ,me borre la informacion y reciba nuevamente un nuevo abono y me lo descuente del saldo,y asi sucesivamente para llevar elestado de cuenta en un hoja de excel 2007

    ResponderEliminar
    Respuestas
    1. Hola Jaki,
      espero estés bien, un gusto poder saludarte igualmente.

      Mejor lee las Normas de uso del blog.

      Un cordial saludo

      Eliminar
  10. Buenas noches, excelente tu publicacion. Necesito acumular valores en una base de datos de clientes pero el cliente 001 hoy puede estar en al fila 1 y mañana en la 3, poderlo llamar por un buscarv y acumularle puntos a ese cliente.
    mil gracias.

    ResponderEliminar
    Respuestas
    1. Hola Fabio,
      creo que lo que necesitas se encuentra en este post.. necesitando algo de programación:
      http://excelforo.blogspot.com.es/2015/02/vba-trasladar-datos-de-inventario.html

      Espero te resulte de utilidad
      Slds

      Eliminar
  11. Buenos dias,
    Me ha funcionado genial tu publicación, la he adaptado a mi excel de esta manera: Sub limpiar()

    ' sumatorio Macro

    'macro que calcula el acumulado de todas las entradas de datos.

    Dim i As Integer
    x = 1
    For i = 5 To 100
    'realiza la suma acumulada del valor anterior mas el nuevo
    'para cada ref.
    Cells(i, 19).Formula = Cells(i, 17).Value - Cells(i, 18).Value
    Cells(i, 17).Formula = Cells(i, 17).Value - Cells(i, 18).Value

    Next i
    x = 1
    For i = 5 To 100
    Cells(i, 20).Formula = Cells(i, 20).Value + Cells(i, 18).Value
    'limpia los campos para poder introducir los nuevos valores
    'para cada ref.

    Cells(i, 18).ClearContents

    Next i
    'Da formato personalizado, añadiendo el texto Entrada
    Range("Q1").NumberFormat = """Entrada""0"
    'genera un autonumérico en la celda A1
    'que nos indicará qué entrada es la última introducida.
    Range("Q1").Value = Range("Q1").Value + x

    End Sub

    El problema que yo tengo es que cuando hago:
    Cells(i, 17).Formula = Cells(i, 17).Value - Cells(i, 18).Value
    y la resta me da negativa, a mi me gustaria que me devolviese un 0 en vez de un valor negativo y no se como escribirlo en la macro.

    Espero que me haya podido explicar bien.

    Muchas gracias y un saludo de antemano

    ResponderEliminar
    Respuestas
    1. Hola,
      aplica un condicional
      IF Cells(i, 17).Value - Cells(i, 18).Value<0 THEN
      Cells(i, 17).Formula = 0
      ELSE
      Cells(i, 17).Formula = Cells(i, 17).Value - Cells(i, 18).Value
      END IF

      Saludos

      Eliminar
  12. Buenas. Excelente foro. Me viene ayudando mucho para resolver algunos problemas en el trabajo. Me preguntaba cómo hacer para que en vez de que en la celda de resultados me devuelva el total del acumulado, me devuelva el mismo pero en formato 1er número + 2do número + ... + Nesimo número. Es decir como si en vez de hacer usado una macro parecía que sólo hice =número 1 + número 2 etc. En decir como si hice una suma manual y que se vean los sumandos. Se entiende? Gracias por su tiempo. Enrique

    ResponderEliminar
  13. Buenas es q quiero que en una serie de celdas no se borre el valor de un promedio que sale de de meter valores en otra hoja que cuando borre los valores de la hoja quede el promedio para seguir mes a mes metiendo las compras pero tamboen cuando quiera bajarlo a cero lo haga con facilidad

    ResponderEliminar
    Respuestas
    1. Hola,
      modifica la macro del ejemplo empleando la función PROMEDIO (AVERAGE en VBA)...
      al ser valores calculados en la macro en cualquier momento podrás sobreescribir a cero

      Espero haberte entendido

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.