Excel - Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Pos: 247

Val: 11

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por hernan (5 intervenciones) el 02/08/2018 00:23:28

Hola a todos

tengo un archivo con varias hojas para llenar unos datos los cuales se ingresan con un formulario y tiene varias macros, archivo que descargue de Internet, pero le falta algo y necesito hacer para llenar el total de los datos,
como se observa en la imagen (CARGA), en la hoja WLC105 estarán los siguientes datos:
todos inician en la fila 6
columna A: fecha
columna B:No Fact
columna C:lleno
columna D:Kilometros/inicio
columna E:Kilometros/final
columna F:Kilometros/total

lo que deseo es poder colocar en la hoja WLC105_MANT (imagen CARGA1), en la celda J7 que corresponde al mes de enero y kilómetros recorridos en ese mes, la sumatoria de la columna F de la hoja WLC105 del mes de enero y hacer lo mismo cada mes, ¿como debería ser la macro para ese proceso?

CARGA

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 4.908

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Antoni Masana (2539 intervenciones) el 02/08/2018 20:37:05

Solo necesitas incluir una columna que puede ser la N que esta oculta con la formula: (Ejemplo celda N6)

=Mes(A6)

Y en la columna O

=Año(A6)

Y arrastrar a toda la columna. Seleccionar las columnas de la A a la S y crear una Tabla Dinámica. Que hace estas cosas y unas cuantas más.

Envíame el libro si no te sale y te la hago.

Saludos.
\\//_

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 247

Val: 11

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por hernan (5 intervenciones) el 03/08/2018 14:06:04

hola Antoni Masana,

gracias por tu colaboración, adjunto el archivo con el que estoy trabajando.

Sistema-en-Excel-para-controlar-vehiculos-Actualizado-19-mayo-2017-1.zip(191,6 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Antoni Masana (2539 intervenciones) el 03/08/2018 15:44:33

Visto como esta definida la hoja de datos no permite hacer una Tabla Dinámica.

Iremos por otro camino.

Utiliza dos columnas vacías de la hoja WLC105 que puedes ocultar una vez funcione.
Podrías añadirlas entre la A y la B pero si tienes macros con referencias a esta hoja utiliza dos de las derecha, yo he usado la X y la Y.

En la columna de la X desde la línea 6 hasta la 306 pon esta fórmula:

=SI(ESNUMERO(A6);MES(A6);"")

En la columna de la Y desde la línea 6 hasta la 306 pon esta fórmula:

=SI(ESNUMERO(A6);AÑO(A6);"")

Ahora en la Celda J7 de la hoja WLC105_MANTE pones esta Formula:

=SUMAR.SI.CONJUNTO('WLC105'!$F$6:$F$306 ; 'WLC105'!$X$6:$X$306 ; "=1" ; 'WLC105'!$Y$6:$Y$306 ; "="&$V$2)

Ahora en la Celda K7 de la hoja WLC105_MANTE pones esta Formula:

=SUMAR.SI.CONJUNTO('WLC105'!$F$6:$F$306 ; 'WLC105'!$X$6:$X$306 ; "=2" ; 'WLC105'!$Y$6:$Y$306 ; "="&$V$2)

Y asi hasta la columna U.

La formula filtra por Mes y Año que toma de la celda V2 de esta misma hoja.

Si no quieres el filtro de año sobra la formula de la columna Y y esta función se simplifica as:

=SUMAR.SI.CONJUNTO('WLC105'!$F$6:$F$306 ; 'WLC105'!$X$6:$X$306 ; "=1")

Las referencias de la formula son absolutas, pero las puedes hacer relativas quitando los $.

Saludos.
\\//_

PD. Con la solución de Milton no necesitas una columna Auxiliar, yo probaría su propuesta primero. A mi me gusta más porque es más elegante.

Valora esta respuesta

Comentar

Pos: 138

Val: 18

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Milton (9 intervenciones) el 03/08/2018 15:02:40

Creo que no necesitas una macro, la función sumar.si.conjunto() te resolveria tu problema, claro si entendí bien.
Ejemplo como deberías aplicarla en la j7:

SUMAR.SI.CONJUNTO('WLC105'!F:F,'WLC105'!A:A,">=" &FECHA(2018,1,1),'WLC105'!A:A,"<="& FECHA(2018,1,31)),

y solo cambias el rango de fechas en cada mes.

aquí el único problema es tomar en cuenta el formato de fecha de excel, tu columna fecha debe ser también fecha para que se pueda cumplir los criterios, saludos

Puedes visitar mi canal en youtube ahí voy colgando tutoriales en excel.

Valora esta respuesta

Comentar

Pos: 247

Val: 11

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por hernan (5 intervenciones) el 03/08/2018 17:28:48

gracias por tu aporte, la formula sirve claro que debe quedar así:

=SUMAR.SI.CONJUNTO('WLC105'!$F:$F;'WLC105'!$A:$A;">=" &FECHA(2018;1;1);'WLC105'!$A:$A;"<="& FECHA(2018;1;31)),

pero lo que sucede es que el archivo original no cuenta con las hojas WLC105 pues son creadas según la información que se ingrese en el menú principal, a medida que se van ingresando datos las macros van creando las hojas conforme a los vehículos ingresados y los conductores que se van digitando, por eso mi inquietud de tener los datos consolidados de kilometraje, valor del combustible y cantidad de galones consumidos por mes. adjunto el archivo original para que puedan ver como es sin las hojas WLC105 pues esas las cree para ver que tan efectivo es el archivo, y por lo tanto lo que deseo es que cuando se creen las hojas también quede la formula o la macro para traer los datos de forma automática en las casillas de los meses y con el consolidado general

Sistema-en-Excel-para-controlar-vehiculos.zip(143,5 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Antoni Masana (2539 intervenciones) el 03/08/2018 21:07:23

Tienes dos opciones y yo me decanto por las primera.

OPCIÓN - 1:

Cuando entras un vehículo copias las hojas Modelo y Modelo1 con el nombre de las hoja, que en el ejemplo anterior eran WLC105 y WLC105_MANTE y en este momento la misma macro que copia las hojas pone la Formula SUMAR.SI.CONJUNTO()en las celdas de la J7 a la U7

OPCIÓN - 2:

Cuando entra datos en la primera hoja hacer el sumatorio en la segunda.

Porque que me gusta la primera más que la segunda porque cualquier cambio en la primera hoja supondrá ir a la segunda y retocar la suma, mientras que si hay una formula la suma siempre estará correcta.

Voy a tomar la formula de Milton porque creo que es mejor que la mía.

Y ahora la pregunta del millón ¿Y como se pone la formula?

Solo hay que modificar esta parte del código al crear las hoja

Sub Formato1()

    Dim a As Byte, Hoja As String, Anio As Integer

    Worksheets("Modelo1").Select

    Cells.Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets(TextBox2.Text).Select

    Cells.Select

    ActiveSheet.Paste

    Range("C7").Select

    Sheets("Modelo1").Select

    Range("B11").Select

    Sheets(TextBox2.Text).Select

    Range("B7").Select

    Hoja = TextBox1.Text

    Anio = Range("V2")

    For a = 1 To 12

        Cells(7, 9 + a).Formula = "=SUMIFS('" & Hoja & "'!F:F," & _

                                          "'" & Hoja & "'!A:A,"">=""&DATE(" & Anio & "," & a & ",1)," & _

                                          "'" & Hoja & "'!A:A,""<=""&DATE(" & Anio & "," & a & ",31))"

    Next

    Sheets("INICIO").Select

    Range("A1").Select

    Application.CutCopyMode = False

End Sub

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Antoni Masana (2539 intervenciones) el 03/08/2018 21:59:46

Me acabo de dar cuenta de un error GRAVE.

Sub Formato1()

    Dim Desde as String, a As Byte, Hoja As String, Anio As Integer, Dias As Byte, _

        Hasta as String

    Worksheets("Modelo1").Select

    Cells.Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets(TextBox2.Text).Select

    Cells.Select

    ActiveSheet.Paste

    Range("C7").Select

    Sheets("Modelo1").Select

    Range("B11").Select

    Sheets(TextBox2.Text).Select

    Range("B7").Select

    Hoja = TextBox1.Text

    Anio = Range("V2")

    For a = 1 To 12

                      Dias = 31

        If a = 2 Then Dias = 28: If (Anio Mod 4) = 0 Then Dias = 29

        If a = 4 Then Dias = 30

        If a = 6 Then Dias = 30

        If a = 9 Then Dias = 30

        If a = 11 Then Dias = 30

        Desde = Anio & "," & a & ",1"

        Hasta = Anio & "," & a & "," & Dias

        Cells(7, 9 + a).Formula = "=SUMIFS('" & Hoja & "'!F:F," & _

                                          "'" & Hoja & "'!A:A,"">=""&DATE(" & Desde & ")," & _

                                          "'" & Hoja & "'!A:A,""<=""&DATE(" & Hasta & "))"

    Next

    Sheets("INICIO").Select

    Range("A1").Select

    Application.CutCopyMode = False

End Sub

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 247

Val: 11

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por hernan (5 intervenciones) el 08/08/2018 18:34:29

Gracias Antoni y Multon, he puesto en practica sus aportes, realizandole algunos pequeños cambios para que se apliquen al archivo de excel que estoy tratando de acomodar para lo que lo necesito, ya realiza las operaciones y todo tal como deseo, solo tengo una falla, al ingresar los datos no los suma como esperaba, pues debe darle F2 a la celda y depues enter para que la suma sea efectiva, pense que era el formato de fecha y lo modifique en el archivo de excel con formato de la celda y no funciono, luego hice lo mismo en la macro donde se asocia la fecha a cada una de las celdas de excel y tampoco sirve, lo modifique asi :

TextBox2 = Format(Date, "yyyy/mm/dd")

pero sigue presentando el mismo inconveniente, tengo que dar F2 y enter para que tome la operación, ¿me pueden ayudar?.

pido disculpas por las molestias, adjunto el archivo de excel.

Sistema-en-Excel-para-controlar-vehiculos-Actualizado-19-mayo-2017-1.rar(201,2 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Antoni Masana (2539 intervenciones) el 09/08/2018 11:33:38

Me he perdido.

Donde, cuando y como es esto de la celda que no suma y lo del TextBox2 ¿De que formulario es?.

Valora esta respuesta

Comentar

Pos: 247

Val: 11

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por hernan (5 intervenciones) el 10/08/2018 17:49:06

Antoni, es de este archivo adjunto es sobre la formula que me enviaste

Sub Formato1()

    Dim Desde as String, a As Byte, Hoja As String, Anio As Integer, Dias As Byte, _

        Hasta as String

    Worksheets("Modelo1").Select

    Cells.Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets(TextBox2.Text).Select

    Cells.Select

    ActiveSheet.Paste

    Range("C7").Select

    Sheets("Modelo1").Select

    Range("B11").Select

    Sheets(TextBox2.Text).Select

    Range("B7").Select

    Hoja = TextBox1.Text

    Anio = Range("V2")

    For a = 1 To 12

                      Dias = 31

        If a = 2 Then Dias = 28: If (Anio Mod 4) = 0 Then Dias = 29

        If a = 4 Then Dias = 30

        If a = 6 Then Dias = 30

        If a = 9 Then Dias = 30

        If a = 11 Then Dias = 30

        Desde = Anio & "," & a & ",1"

        Hasta = Anio & "," & a & "," & Dias

        Cells(7, 9 + a).Formula = "=SUMIFS('" & Hoja & "'!F:F," & _

                                          "'" & Hoja & "'!A:A,"">=""&DATE(" & Desde & ")," & _

                                          "'" & Hoja & "'!A:A,""<=""&DATE(" & Hasta & "))"

    Next

    Sheets("INICIO").Select

    Range("A1").Select

    Application.CutCopyMode = False

End Sub

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Macro para llenar, sumar datos según mes y colocarlos en una hoja diferente

Publicado por Antoni Masana (2539 intervenciones) el 13/08/2018 12:36:17

Ya se lo que pasa o creo saber lo que pasa. Tienes desactivado el calculo automatico de las formulas.

Tienes varias opciones:

- Ir al menú de opciones y marcar la opción de automático.

- Poner esta opcion al inicio de la macro o al final.

Application.Calculation = xlCalculationAutomatic

- Ejecutar este comando cuando finalice la entrada de datos para forzar que realice los cálculos.

Call Calculate

Saludos.
\\//_

Valora esta respuesta

Comentar

web - sumaproducto por condiciones de fechas

Query table con lista de URL