Codigo VBA para procesar archivo Excel

Buenos días KL.

Por favor me podrías enviar el archivo que simulaste a la siguiente
direccion



Muchas gracias.

Hernan Gutierrez



KL wrote:

Hernan,

Algo no esta funcionando bien. Mi macro me funciona bien y devuelve 900
valores (30 dias por 30 datos) y no uno tal como tu describes
abajo.Asegurate que la fecha de validacion se introduce en la celda AG2 de
la misma hoja q la base de datos o bien cambia la variable MiFecha en el
codigo. Tambien comprueba las siguientes constantes y variables:

Const NumeroDatos = 30
Const NumeroDias = 30

Set MiFecha = Range("AG2")
Set Datos = Range("B1:AE1")
Set Registros = Range("A1:A4000")

Y por supuesto, si quieres te mando mi hoja para q puedas comprobar como
funciona o me mandas la tuya y te lo hago yo. Dime algo.

Saludos,
KL


""Hernán Gutiérrez" M." wrote in message
news:exjh$Y%
>
> Buenas tardes KL.
>
> Muchas gracias por responder.
>
>
> Al parecer tu macro muestra un valor. Para el caso de los últimos 30 días
> móviles debe de mostrar 90 valores, o sea 30 días por 3 datos de cada día.
> Para el caso de los meses del año, debe mostrar los valores de los meses
> que van hasta esa fecha que digito el usuario. Si digito 2004/11/26 debe
> calcular valores de enero hasta noviembre.
>
> El archivo original está así.
> 2003/01/02 dato1 dato2 dato3 mas otros campos informativos
> 2004/01/01 dato1 dato2 dato3
> 2004/01/01 dato1 dato2 dato3 existen varios registros para un mismo
> día
> .
> 2004/04/05 dato1 dato2 dato3
> .
> hoy existen 3764 registros
>
> Los resultados para la fecha digitada x usuario : 2004/12/15 (incluso en
> un momento dado se quiero ver la gráfica del comportamiento de otro
> periodo, por ejemplo podría digitar 2004/07/01 para analizar todo el mes
> de junio del 2004)
>
> 1) Ultimos 30 días a la fecha digitada : La macro me debe de calcular las
> fechas con sus promedios para cada día
> 2004/11/15 prom-dato1 prom-dato2 prom-dato3
> 2004/11/16 prom-dato1 prom-dato2 prom-dato3
> 2004/11/17 prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> 2004/12/14 prom-dato1 prom-dato2 prom-dato3
> 2004/12/15 prom-dato1 prom-dato2 prom-dato3
>
> 2) Los meses del año con los mismos promedios
> Enero prom-dato1 prom-dato2 prom-dato3
> Febrero prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> Diciembre prom-dato1 prom-dato2 prom-dato3
>
> Se garantiza que el archivo original esta clasificado por fecha.
>
> Con los datos anteriores se generaría un gráfico automático en cualquier
> momento que lo requiera un ingeniero de planta , para cualquier fecha
> digitada y la macro debe estar disponible para que se pueda ejecutar desde
> cualquier equipo de la red. Actualmente este libro es compartido por 3
> equipos ubicados en diferentes lugares de la planta de producción.
>
> Muy extenso mi cuento ... pero la pregunta para KL ... Si se podrá hacer
> esto con una macro. Repito que con fórmulas matriciales {} esto se me
> salio de las manos, porque aparte de los datos el registro lleva otra
> informacion que no es importante para este cálculo pero que si ocupa
> espacio en cada fila.
>
> Un saludo y te reitero mis agradecimientos.
>
> Hernán
>
>
>
>
>
> L wrote:
>
>> Hernan,
>
>> He probado realizar la primera de tus tareas por medio de la siguiente
>> formula (30 fechas x 30 datos = 900 celdas):
>
>>
>

=SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000))/SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000<>""))

>
>> y el macro q he escrito (mas abajo) y me resultan las formulas bastante
>> mas
>> rapidas q el macro. La unica ventaja del macro es q no se recalcula cada
>> dos
>> por tres.
>
>> Saludos,
>> KL
>
>> Const NumeroDatos = 30
>> Const NumeroDias = 30
>
>> Sub Promedios30Dias()
>> Dim Dato As Integer
>> Dim Registros As Range
>> Dim Registro As Range
>> Dim Dia As Single
>> Dim Contador As Single
>> Dim MiFecha As Range
>> Dim MiNumero()
>
>> Set MiFecha = Range("AG2")
>> Set Datos = Range("B1:AE1")
>> Set Registros = Range("A1:A4000")
>
>> Application.ScreenUpdating = False
>> For Dato = 1 To NumeroDatos
>> Dia = MiFecha.Value - NumeroDias
>> Contador = 1
>> While Dia < MiFecha.Value
>> ReDim MiNumero(0)
>> For Each Registro In Registros
>> If Registro.Value = Dia Then
>> 'Idea de Leo Heuser
>> MiNumero(UBound(MiNumero)) _
>> = Registro.Offset(0, Dato).Value
>> ReDim Preserve MiNumero _
>> (UBound(MiNumero) + 1)
>> End If
>> Next Registro
>> MiFecha.Offset(Contador, 0).Value = Dia
>> If WorksheetFunction.Sum(MiNumero) = 0 Then
>> MiFecha.Offset(Contador, Dato).Value = 0
>> Else
>> MiFecha.Offset(Contador, Dato).Value = _
>> WorksheetFunction.Average(MiNumero)
>> End If
>> Dia = Dia + 1
>> Contador = Contador + 1
>> Wend
>> Next Dato
>> Application.ScreenUpdating = True
>> End Sub
>> -Fin Codigo
>
>
>> ""Hernán Gutiérrez"" wrote in message
>> news:
>> >
>> > KL : Muchas gracias por responder
>> > Faltó explicar que actualmente/ lo hago con fórmulas matriciales y los
>> > datos se obtienen, pero a medida que ha crecido el archivo la lentidud
>> > es
>> > abismal a pesar de tener equipos bien configurados. Los usuarios ya no
>> > soportan esto.
>> >
>> > El asunto es que si el usuario digito 2004/11/25 obtenga los siguientes
>> > datos en forma automática para poder graficar:
>> > 1) Promedio (o Desviacion Estandar) por día , para la fecha ejemplo
>> > sería
>> > :
>> > de Nov 25, Nov 24, Nov 23 Oct/25,Oct /24 (ültimos 30 días),
>> > y para cada día existen muchos registros (se digitan cada 2 horas)
>> > 2) Total por cada mes , en este caso del año 2004
>> > Ene, Feb, Mar ... Nov. (tener en cuenta que la fecha se digita con
>> > formato aa/mm/dd
>> > 3) Tttal del año en curso.
>> >
>> > Recuerda que el archivo es un histórico que puede tener hasta 2 años en
>> > línea.
>> >
>> > Cómo se podrá hacer esto desde VBA, cómo VBA puede almacenar el total
>> > de
>> > los 30 días calendarios y de los meses del años en curso (en un vector
>> > o
>> > arreglo).
>> >
>> > Espero haberme hecho entender ... necesito una luz a este problema con
>> > carácter urgente.
>> >
>> >
>> > Muchas gracias
>> >
>> >
>> > KL wrote:
>> >
>> >> Me parece q todo esto se puede conseguir mediante unas formulas
>> >> relativamente simples. Solo una aclaracion: cuando hablas de calcular
>> >> 30
>> >> dias anteriores y sacar el promedio por dia para dato1, dato2, etc...
>> >> se
>> >> tiene q entender como por ejemplo:
>> >
>> >> 1) para el Dato1 se suman todas las cantidades registradas entre la
>> >> fecha
>> >> introducida y 30 dias atras y luego la suma se divide por 30 (o por el
>> >> numero de dias reales)? Y asi para el Dato2, Dato3, etc? De manera q
>> >> si
>> >> hay
>> >> 3 datos el resultado seria 3 numeros.
>> >
>> >> o bien
>> >
>> >> 2) para el Dato1 se suman todas las cantidadeds registradas en el dia
>> >> X y
>> >> se
>> >> saca el promedio? Y asi para cada dia dentro del periodo entre la
>> >> fecha
>> >> introducida y 30 dias atras y luego para el Dato2, Dato3, etc? De
>> >> manera
>> >> q
>> >> si hay 3 datos el resultado podria ser 90 numeros.
>> >
>> >> Dime algo,
>> >> KL
>> >
>> >
>> >> ""Hernán Gutiérrez"" wrote in message
>> >> news:O4g$
>> >> > Buenos días.
>> >> >
>> >> > Requiero un código en VBA que pemita hacer lo siguiente. Actualmente
>> >> > lo
>> >> > hacemos con tablas dinámicas pero no hace todo lo que queremos.
>> >> >
>> >> > Tengo un archivo excel con más de 3000 registros, tiene información
>> >> > de
>> >> > varios años.
>> >> > Cada registro (fila) tiene lo siguientes campos:
>> >> > Fecha(aa-mm-dd),dato1,dato2,dato3,dato4,dato5 . dato30.
>> >> >
>> >> > Necesito que con base en una fecha que yo digite en una celda
>> >> > especifica,
>> >> > calcule lo siguiente:
>> >> > 1) Calcule los 30 días anteriores y me saque un promedio por día del
>> >> > dato1,dato2,dato3.Para un mismo día puedo tener varios registros
>> >> >
>> >> > 2) Saque el promedio de cada mes del año presente (con base a la
>> >> > fecha
>> >> > digitada).
>> >> >
>> >> > 3) Saque el total del año.
>> >> >
>> >> > Muchas gracias por su colaboración
>> >> >
>> >> > Hernán Gutiérrez
>> >> >
>> >> >
>> >> >
>> >
>> >
>
>

Hernan,

Para el beneficio de todos te pongo mi respuesta en el grupo. He modificado
el codigo y he anadido comentarios q deberian contestar la mayoria de tus
preguntas (sustituye el codigo anterior por completo). El codigo para la
pregunta #2 te pondre mas tarde - es q estoy de viaje esta semana. La
pregunta #5 no la he entendido, sobre todo eso de "el archivo ni siquiera
está clasificado". Ya me explicaras lo q quisiste decir.

Un saludo,
KL
'--Inicio Codigo--
'Aqui estableces el numero de dias con
'anterioridad a la fecha introducida
'q se tienen q evaluar.
Const NumeroDias = 30

Sub Promedios30Dias()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Dia As Single
Dim Contador As Single
Dim MiFecha As Range
Dim ColDatos()
Dim MiNumero()

'Aqui estableces el rango donde se introducira la
'fecha a validar y a partir del cual se introduciran
'los resultados.
Set MiFecha = ActiveSheet.Range("AG2")

'?Como controlar para que el proceso lo ejecute hasta
'que encuentre una celda vacia? Yo diria mas bien hasta
'q llegue al final de la lista. Asi. Antes de empezar
'detectas el rango usado de la hoja:
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'?Como hago para que me saque solamente el promedio de 3
'columnas, por ejemplo para la columnas K,N y Q (dato10,
'dato13 y dato16? Asi:
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Dia = MiFecha.Value - NumeroDias
Contador = 1
While Dia < MiFecha.Value
ReDim MiNumero(0)
For Each Registro In Registros
If Registro.Value = Dia Then
'Idea de Leo Heuser
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
Next Registro
MiFecha.Offset(Contador, 0).Value = Dia

'?Como hago para que cuando el valor sea zero,
'lo deje en espacios, esto con el fin de no
'dajar la grafica? Asi...:
If WorksheetFunction.Sum(MiNumero) = 0 Then
'...y asi:
MiFecha.Offset(Contador, i + 1).Value = ""
Else
MiFecha.Offset(Contador, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Dia = Dia + 1
Contador = Contador + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub
'--Fin Codigo--



""Hern?n Guti?rrez" M." wrote in message

Hola KL.

Muy bien funciona perfecto lo de los días . Pero como apenas estoy
estudiando lo referente a VBA aprovecho tu generosidad para preguntarte
algo.

1) Como hago para que me saque solamente el promedio de 3 columnas, por
ejemplo para la columnas K,N y Q (dato10,dato13 y dato16).
2) Como obtengo el mismo resultado en los meses de ese año.
3) Como hago para que cuando el valor sea zero , lo deje en espacios, esto
con el fin de no dañar la gráfica.
4) Cómo controlar para que el proceso lo ejecute hasta que encuentre una
celda vacía.
5) Como lo lograste hacer, sabiendo que el archivo ni siquiera está
clasificado.

Mejor dicho como hago para entender esta macro tan excelente que hiciste.

Muchos saludos , y muchas gracias porque veo que se me van a acabar los
problemas con el manejo de ese archivo.

Hernán Gutiérrez

""Hernán Gutiérrez"" wrote in message
news:

Buenos días KL.

Por favor me podrías enviar el archivo que simulaste a la siguiente
direccion



Muchas gracias.

Hernan Gutierrez



KL wrote:

Hernan,

Algo no esta funcionando bien. Mi macro me funciona bien y devuelve 900
valores (30 dias por 30 datos) y no uno tal como tu describes
abajo.Asegurate que la fecha de validacion se introduce en la celda AG2
de
la misma hoja q la base de datos o bien cambia la variable MiFecha en el
codigo. Tambien comprueba las siguientes constantes y variables:

Const NumeroDatos = 30
Const NumeroDias = 30

Set MiFecha = Range("AG2")
Set Datos = Range("B1:AE1")
Set Registros = Range("A1:A4000")

Y por supuesto, si quieres te mando mi hoja para q puedas comprobar como
funciona o me mandas la tuya y te lo hago yo. Dime algo.

Saludos,
KL

""Hernán Gutiérrez" M." wrote in message
news:exjh$Y%
>
> Buenas tardes KL.
>
> Muchas gracias por responder.
>
>
> Al parecer tu macro muestra un valor. Para el caso de los últimos 30
> días
> móviles debe de mostrar 90 valores, o sea 30 días por 3 datos de cada
> día.
> Para el caso de los meses del año, debe mostrar los valores de los
> meses
> que van hasta esa fecha que digito el usuario. Si digito 2004/11/26
> debe
> calcular valores de enero hasta noviembre.
>
> El archivo original está así.
> 2003/01/02 dato1 dato2 dato3 mas otros campos informativos
> 2004/01/01 dato1 dato2 dato3
> 2004/01/01 dato1 dato2 dato3 existen varios registros para un
> mismo
> día
> .
> 2004/04/05 dato1 dato2 dato3
> .
> hoy existen 3764 registros
>
> Los resultados para la fecha digitada x usuario : 2004/12/15 (incluso
> en
> un momento dado se quiero ver la gráfica del comportamiento de otro
> periodo, por ejemplo podría digitar 2004/07/01 para analizar todo el
> mes
> de junio del 2004)
>
> 1) Ultimos 30 días a la fecha digitada : La macro me debe de calcular
> las
> fechas con sus promedios para cada día
> 2004/11/15 prom-dato1 prom-dato2 prom-dato3
> 2004/11/16 prom-dato1 prom-dato2 prom-dato3
> 2004/11/17 prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> 2004/12/14 prom-dato1 prom-dato2 prom-dato3
> 2004/12/15 prom-dato1 prom-dato2 prom-dato3
>
> 2) Los meses del año con los mismos promedios
> Enero prom-dato1 prom-dato2 prom-dato3
> Febrero prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> Diciembre prom-dato1 prom-dato2 prom-dato3
>
> Se garantiza que el archivo original esta clasificado por fecha.
>
> Con los datos anteriores se generaría un gráfico automático en
> cualquier
> momento que lo requiera un ingeniero de planta , para cualquier fecha
> digitada y la macro debe estar disponible para que se pueda ejecutar
> desde
> cualquier equipo de la red. Actualmente este libro es compartido por 3
> equipos ubicados en diferentes lugares de la planta de producción.
>
> Muy extenso mi cuento ... pero la pregunta para KL ... Si se podrá
> hacer
> esto con una macro. Repito que con fórmulas matriciales {} esto se me
> salio de las manos, porque aparte de los datos el registro lleva otra
> informacion que no es importante para este cálculo pero que si ocupa
> espacio en cada fila.
>
> Un saludo y te reitero mis agradecimientos.
>
> Hernán
>
>
>
>
>
> L wrote:
>
>> Hernan,
>
>> He probado realizar la primera de tus tareas por medio de la siguiente
>> formula (30 fechas x 30 datos = 900 celdas):
>
>>
>

=SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000))/SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000<>""))

>
>> y el macro q he escrito (mas abajo) y me resultan las formulas
>> bastante
>> mas
>> rapidas q el macro. La unica ventaja del macro es q no se recalcula
>> cada
>> dos
>> por tres.
>
>> Saludos,
>> KL
>
>> Const NumeroDatos = 30
>> Const NumeroDias = 30
>
>> Sub Promedios30Dias()
>> Dim Dato As Integer
>> Dim Registros As Range
>> Dim Registro As Range
>> Dim Dia As Single
>> Dim Contador As Single
>> Dim MiFecha As Range
>> Dim MiNumero()
>
>> Set MiFecha = Range("AG2")
>> Set Datos = Range("B1:AE1")
>> Set Registros = Range("A1:A4000")
>
>> Application.ScreenUpdating = False
>> For Dato = 1 To NumeroDatos
>> Dia = MiFecha.Value - NumeroDias
>> Contador = 1
>> While Dia < MiFecha.Value
>> ReDim MiNumero(0)
>> For Each Registro In Registros
>> If Registro.Value = Dia Then
>> 'Idea de Leo Heuser
>> MiNumero(UBound(MiNumero)) _
>> = Registro.Offset(0, Dato).Value
>> ReDim Preserve MiNumero _
>> (UBound(MiNumero) + 1)
>> End If
>> Next Registro
>> MiFecha.Offset(Contador, 0).Value = Dia
>> If WorksheetFunction.Sum(MiNumero) = 0 Then
>> MiFecha.Offset(Contador, Dato).Value = 0
>> Else
>> MiFecha.Offset(Contador, Dato).Value = _
>> WorksheetFunction.Average(MiNumero)
>> End If
>> Dia = Dia + 1
>> Contador = Contador + 1
>> Wend
>> Next Dato
>> Application.ScreenUpdating = True
>> End Sub
>> -Fin Codigo
>
>
>> ""Hernán Gutiérrez"" wrote in message
>> news:
>> >
>> > KL : Muchas gracias por responder
>> > Faltó explicar que actualmente/ lo hago con fórmulas matriciales y
>> > los
>> > datos se obtienen, pero a medida que ha crecido el archivo la
>> > lentidud
>> > es
>> > abismal a pesar de tener equipos bien configurados. Los usuarios ya
>> > no
>> > soportan esto.
>> >
>> > El asunto es que si el usuario digito 2004/11/25 obtenga los
>> > siguientes
>> > datos en forma automática para poder graficar:
>> > 1) Promedio (o Desviacion Estandar) por día , para la fecha ejemplo
>> > sería
>> > :
>> > de Nov 25, Nov 24, Nov 23 Oct/25,Oct /24 (ültimos 30
>> > días),
>> > y para cada día existen muchos registros (se digitan cada 2 horas)
>> > 2) Total por cada mes , en este caso del año 2004
>> > Ene, Feb, Mar ... Nov. (tener en cuenta que la fecha se digita con
>> > formato aa/mm/dd
>> > 3) Tttal del año en curso.
>> >
>> > Recuerda que el archivo es un histórico que puede tener hasta 2 años
>> > en
>> > línea.
>> >
>> > Cómo se podrá hacer esto desde VBA, cómo VBA puede almacenar el
>> > total
>> > de
>> > los 30 días calendarios y de los meses del años en curso (en un
>> > vector
>> > o
>> > arreglo).
>> >
>> > Espero haberme hecho entender ... necesito una luz a este problema
>> > con
>> > carácter urgente.
>> >
>> >
>> > Muchas gracias
>> >
>> >
>> > KL wrote:
>> >
>> >> Me parece q todo esto se puede conseguir mediante unas formulas
>> >> relativamente simples. Solo una aclaracion: cuando hablas de
>> >> calcular
>> >> 30
>> >> dias anteriores y sacar el promedio por dia para dato1, dato2,
>> >> etc...
>> >> se
>> >> tiene q entender como por ejemplo:
>> >
>> >> 1) para el Dato1 se suman todas las cantidades registradas entre la
>> >> fecha
>> >> introducida y 30 dias atras y luego la suma se divide por 30 (o por
>> >> el
>> >> numero de dias reales)? Y asi para el Dato2, Dato3, etc? De manera
>> >> q
>> >> si
>> >> hay
>> >> 3 datos el resultado seria 3 numeros.
>> >
>> >> o bien
>> >
>> >> 2) para el Dato1 se suman todas las cantidadeds registradas en el
>> >> dia
>> >> X y
>> >> se
>> >> saca el promedio? Y asi para cada dia dentro del periodo entre la
>> >> fecha
>> >> introducida y 30 dias atras y luego para el Dato2, Dato3, etc? De
>> >> manera
>> >> q
>> >> si hay 3 datos el resultado podria ser 90 numeros.
>> >
>> >> Dime algo,
>> >> KL
>> >
>> >
>> >> ""Hernán Gutiérrez"" wrote in message
>> >> news:O4g$
>> >> > Buenos días.
>> >> >
>> >> > Requiero un código en VBA que pemita hacer lo siguiente.
>> >> > Actualmente
>> >> > lo
>> >> > hacemos con tablas dinámicas pero no hace todo lo que queremos.
>> >> >
>> >> > Tengo un archivo excel con más de 3000 registros, tiene
>> >> > información
>> >> > de
>> >> > varios años.
>> >> > Cada registro (fila) tiene lo siguientes campos:
>> >> > Fecha(aa-mm-dd),dato1,dato2,dato3,dato4,dato5 . dato30.
>> >> >
>> >> > Necesito que con base en una fecha que yo digite en una celda
>> >> > especifica,
>> >> > calcule lo siguiente:
>> >> > 1) Calcule los 30 días anteriores y me saque un promedio por día
>> >> > del
>> >> > dato1,dato2,dato3.Para un mismo día puedo tener varios registros
>> >> >
>> >> > 2) Saque el promedio de cada mes del año presente (con base a la
>> >> > fecha
>> >> > digitada).
>> >> >
>> >> > 3) Saque el total del año.
>> >> >
>> >> > Muchas gracias por su colaboración
>> >> >
>> >> > Hernán Gutiérrez
>> >> >
>> >> >
>> >> >
>> >
>> >
>
>

Hernan,

Aqui esta el codigo final incluyendo la respuesta a la pregunta #2 (total
dos macros). Tambien despues de pensar un poco y analizar tus postings
anteriores, llego a la conclusion de q en la pregunta #5 al decir
"clasificado" te referias a "ordenado". Si es asi, entonces te digo q no es
necesario q esten los datos ordenados sugun la fecha ya q el codigo escanea
todas las celdas q no esten vacias en la columna A y solo saca los valores q
corresponden a una fecha en concreto. Tambien te envio el archivo a tu
direccion de correo electronico.

Saludos,
KL
'--Inicio Codigo--
'Establece el # de dias con anterioridad a
'la fecha introducida q se tienen q evaluar.
Const NumeroDias = 30

Sub Promedios30Dias()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Dia As Single
Dim Contador As Single
Dim MiFecha As Range
Dim ColDatos()
Dim MiNumero()

'Establece el rango donde se introducira la fecha
'a evaluar y partir del cual se introduciran los
'resultados.
Set MiFecha = ActiveSheet.Range("AG2")

'Comprueba q la celda para la fecha no este vacia.
If MiFecha.Value = "" Then Exit Sub

'Comprueba q el valor de la celda para la fecha
'tenga formato de fecha.
If Not IsDate(MiFecha) Then Exit Sub

'Detecta el rango usado de la hoja.
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'Saca datos de las columnas K,N y Q.
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Dia = MiFecha.Value - 1
Contador = 1
While Dia >= MiFecha.Value - NumeroDias
ReDim MiNumero(0)
For Each Registro In Registros
If Registro.Value = Dia Then
'Idea de Leo Heuser.
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
Next Registro
'Crea los encabezados de filas con las fechas
'como nombres.
MiFecha.Offset(Contador, 0).Value = Dia

'Si el promedio es igual a 0...
If WorksheetFunction.Sum(MiNumero) = 0 Then
'se devolvera vacio.
MiFecha.Offset(Contador, i + 1).Value = ""
Else
MiFecha.Offset(Contador, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Dia = Dia - 1
Contador = Contador + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub

Sub PromediosMes()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Mes As Single
Dim Inicio As Range
Dim ColDatos()
Dim MiNumero()

'Establece el rango partir del cual se
'introduciran los resultados.
Set Inicio = ActiveSheet.Range("AL2")

'Detecta el rango usado de la hoja.
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'Saca datos de las columnas K,N y Q.
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Mes = 1
While Mes <= 12
ReDim MiNumero(0)
For Each Registro In Registros
If IsDate(Registro) Then
If Month(Registro) = Mes Then
'Idea de Leo Heuser.
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
End If
Next Registro

'Crea los encabezados de filas con los nombres
'de los meses.
Inicio.Offset(Mes, 0).Value = Choose(Mes, "Enero", _
"Febrero", "Marzo", "Abril", "Mayo", "Junio", _
"Julio", "Agosto", "Septiembre", "Octubre", _
"Noviembre", "Diciembre")

'Si el promedio es igual a 0...
If WorksheetFunction.Sum(MiNumero) = 0 Then
'se devolvera vacio.
Inicio.Offset(Mes, i + 1).Value = ""
Else
Inicio.Offset(Mes, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Mes = Mes + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub
'--Fin Codigo--

"KL" <lapink2000(at)hotmail.com (former )> wrote in
message news:

Hernan,

Para el beneficio de todos te pongo mi respuesta en el grupo. He
modificado el codigo y he anadido comentarios q deberian contestar la
mayoria de tus preguntas (sustituye el codigo anterior por completo). El
codigo para la pregunta #2 te pondre mas tarde - es q estoy de viaje esta
semana. La pregunta #5 no la he entendido, sobre todo eso de "el archivo
ni siquiera está clasificado". Ya me explicaras lo q quisiste decir.

Un saludo,
KL
'--Inicio Codigo--
'Aqui estableces el numero de dias con
'anterioridad a la fecha introducida
'q se tienen q evaluar.
Const NumeroDias = 30

Sub Promedios30Dias()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Dia As Single
Dim Contador As Single
Dim MiFecha As Range
Dim ColDatos()
Dim MiNumero()

'Aqui estableces el rango donde se introducira la
'fecha a validar y a partir del cual se introduciran
'los resultados.
Set MiFecha = ActiveSheet.Range("AG2")

'?Como controlar para que el proceso lo ejecute hasta
'que encuentre una celda vacia? Yo diria mas bien hasta
'q llegue al final de la lista. Asi. Antes de empezar
'detectas el rango usado de la hoja:
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'?Como hago para que me saque solamente el promedio de 3
'columnas, por ejemplo para la columnas K,N y Q (dato10,
'dato13 y dato16? Asi:
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Dia = MiFecha.Value - NumeroDias
Contador = 1
While Dia < MiFecha.Value
ReDim MiNumero(0)
For Each Registro In Registros
If Registro.Value = Dia Then
'Idea de Leo Heuser
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
Next Registro
MiFecha.Offset(Contador, 0).Value = Dia

'?Como hago para que cuando el valor sea zero,
'lo deje en espacios, esto con el fin de no
'dajar la grafica? Asi...:
If WorksheetFunction.Sum(MiNumero) = 0 Then
'...y asi:
MiFecha.Offset(Contador, i + 1).Value = ""
Else
MiFecha.Offset(Contador, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Dia = Dia + 1
Contador = Contador + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub
'--Fin Codigo--



""Hern?n Guti?rrez" M." wrote in message

Hola KL.

Muy bien funciona perfecto lo de los días . Pero como apenas estoy
estudiando lo referente a VBA aprovecho tu generosidad para preguntarte
algo.

1) Como hago para que me saque solamente el promedio de 3 columnas, por
ejemplo para la columnas K,N y Q (dato10,dato13 y dato16).
2) Como obtengo el mismo resultado en los meses de ese año.
3) Como hago para que cuando el valor sea zero , lo deje en espacios, esto
con el fin de no dañar la gráfica.
4) Cómo controlar para que el proceso lo ejecute hasta que encuentre una
celda vacía.
5) Como lo lograste hacer, sabiendo que el archivo ni siquiera está
clasificado.

Mejor dicho como hago para entender esta macro tan excelente que hiciste.

Muchos saludos , y muchas gracias porque veo que se me van a acabar los
problemas con el manejo de ese archivo.

Hernán Gutiérrez

""Hernán Gutiérrez"" wrote in message
news:

Buenos días KL.

Por favor me podrías enviar el archivo que simulaste a la siguiente
direccion



Muchas gracias.

Hernan Gutierrez



KL wrote:

Hernan,

Algo no esta funcionando bien. Mi macro me funciona bien y devuelve 900
valores (30 dias por 30 datos) y no uno tal como tu describes
abajo.Asegurate que la fecha de validacion se introduce en la celda AG2
de
la misma hoja q la base de datos o bien cambia la variable MiFecha en el
codigo. Tambien comprueba las siguientes constantes y variables:

Const NumeroDatos = 30
Const NumeroDias = 30

Set MiFecha = Range("AG2")
Set Datos = Range("B1:AE1")
Set Registros = Range("A1:A4000")

Y por supuesto, si quieres te mando mi hoja para q puedas comprobar como
funciona o me mandas la tuya y te lo hago yo. Dime algo.

Saludos,
KL

""Hernán Gutiérrez" M." wrote in message
news:exjh$Y%
>
> Buenas tardes KL.
>
> Muchas gracias por responder.
>
>
> Al parecer tu macro muestra un valor. Para el caso de los últimos 30
> días
> móviles debe de mostrar 90 valores, o sea 30 días por 3 datos de cada
> día.
> Para el caso de los meses del año, debe mostrar los valores de los
> meses
> que van hasta esa fecha que digito el usuario. Si digito 2004/11/26
> debe
> calcular valores de enero hasta noviembre.
>
> El archivo original está así.
> 2003/01/02 dato1 dato2 dato3 mas otros campos informativos
> 2004/01/01 dato1 dato2 dato3
> 2004/01/01 dato1 dato2 dato3 existen varios registros para un
> mismo
> día
> .
> 2004/04/05 dato1 dato2 dato3
> .
> hoy existen 3764 registros
>
> Los resultados para la fecha digitada x usuario : 2004/12/15 (incluso
> en
> un momento dado se quiero ver la gráfica del comportamiento de otro
> periodo, por ejemplo podría digitar 2004/07/01 para analizar todo el
> mes
> de junio del 2004)
>
> 1) Ultimos 30 días a la fecha digitada : La macro me debe de calcular
> las
> fechas con sus promedios para cada día
> 2004/11/15 prom-dato1 prom-dato2 prom-dato3
> 2004/11/16 prom-dato1 prom-dato2 prom-dato3
> 2004/11/17 prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> 2004/12/14 prom-dato1 prom-dato2 prom-dato3
> 2004/12/15 prom-dato1 prom-dato2 prom-dato3
>
> 2) Los meses del año con los mismos promedios
> Enero prom-dato1 prom-dato2 prom-dato3
> Febrero prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> Diciembre prom-dato1 prom-dato2 prom-dato3
>
> Se garantiza que el archivo original esta clasificado por fecha.
>
> Con los datos anteriores se generaría un gráfico automático en
> cualquier
> momento que lo requiera un ingeniero de planta , para cualquier fecha
> digitada y la macro debe estar disponible para que se pueda ejecutar
> desde
> cualquier equipo de la red. Actualmente este libro es compartido por 3
> equipos ubicados en diferentes lugares de la planta de producción.
>
> Muy extenso mi cuento ... pero la pregunta para KL ... Si se podrá
> hacer
> esto con una macro. Repito que con fórmulas matriciales {} esto se me
> salio de las manos, porque aparte de los datos el registro lleva otra
> informacion que no es importante para este cálculo pero que si ocupa
> espacio en cada fila.
>
> Un saludo y te reitero mis agradecimientos.
>
> Hernán
>
>
>
>
>
> L wrote:
>
>> Hernan,
>
>> He probado realizar la primera de tus tareas por medio de la
>> siguiente
>> formula (30 fechas x 30 datos = 900 celdas):
>
>>
>

=SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000))/SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000<>""))

>
>> y el macro q he escrito (mas abajo) y me resultan las formulas
>> bastante
>> mas
>> rapidas q el macro. La unica ventaja del macro es q no se recalcula
>> cada
>> dos
>> por tres.
>
>> Saludos,
>> KL
>
>> Const NumeroDatos = 30
>> Const NumeroDias = 30
>
>> Sub Promedios30Dias()
>> Dim Dato As Integer
>> Dim Registros As Range
>> Dim Registro As Range
>> Dim Dia As Single
>> Dim Contador As Single
>> Dim MiFecha As Range
>> Dim MiNumero()
>
>> Set MiFecha = Range("AG2")
>> Set Datos = Range("B1:AE1")
>> Set Registros = Range("A1:A4000")
>
>> Application.ScreenUpdating = False
>> For Dato = 1 To NumeroDatos
>> Dia = MiFecha.Value - NumeroDias
>> Contador = 1
>> While Dia < MiFecha.Value
>> ReDim MiNumero(0)
>> For Each Registro In Registros
>> If Registro.Value = Dia Then
>> 'Idea de Leo Heuser
>> MiNumero(UBound(MiNumero)) _
>> = Registro.Offset(0, Dato).Value
>> ReDim Preserve MiNumero _
>> (UBound(MiNumero) + 1)
>> End If
>> Next Registro
>> MiFecha.Offset(Contador, 0).Value = Dia
>> If WorksheetFunction.Sum(MiNumero) = 0 Then
>> MiFecha.Offset(Contador, Dato).Value = 0
>> Else
>> MiFecha.Offset(Contador, Dato).Value = _
>> WorksheetFunction.Average(MiNumero)
>> End If
>> Dia = Dia + 1
>> Contador = Contador + 1
>> Wend
>> Next Dato
>> Application.ScreenUpdating = True
>> End Sub
>> -Fin Codigo
>
>
>> ""Hernán Gutiérrez"" wrote in message
>> news:
>> >
>> > KL : Muchas gracias por responder
>> > Faltó explicar que actualmente/ lo hago con fórmulas matriciales y
>> > los
>> > datos se obtienen, pero a medida que ha crecido el archivo la
>> > lentidud
>> > es
>> > abismal a pesar de tener equipos bien configurados. Los usuarios ya
>> > no
>> > soportan esto.
>> >
>> > El asunto es que si el usuario digito 2004/11/25 obtenga los
>> > siguientes
>> > datos en forma automática para poder graficar:
>> > 1) Promedio (o Desviacion Estandar) por día , para la fecha
>> > ejemplo
>> > sería
>> > :
>> > de Nov 25, Nov 24, Nov 23 Oct/25,Oct /24 (ültimos 30
>> > días),
>> > y para cada día existen muchos registros (se digitan cada 2
>> > horas)
>> > 2) Total por cada mes , en este caso del año 2004
>> > Ene, Feb, Mar ... Nov. (tener en cuenta que la fecha se digita
>> > con
>> > formato aa/mm/dd
>> > 3) Tttal del año en curso.
>> >
>> > Recuerda que el archivo es un histórico que puede tener hasta 2
>> > años en
>> > línea.
>> >
>> > Cómo se podrá hacer esto desde VBA, cómo VBA puede almacenar el
>> > total
>> > de
>> > los 30 días calendarios y de los meses del años en curso (en un
>> > vector
>> > o
>> > arreglo).
>> >
>> > Espero haberme hecho entender ... necesito una luz a este problema
>> > con
>> > carácter urgente.
>> >
>> >
>> > Muchas gracias
>> >
>> >
>> > KL wrote:
>> >
>> >> Me parece q todo esto se puede conseguir mediante unas formulas
>> >> relativamente simples. Solo una aclaracion: cuando hablas de
>> >> calcular
>> >> 30
>> >> dias anteriores y sacar el promedio por dia para dato1, dato2,
>> >> etc...
>> >> se
>> >> tiene q entender como por ejemplo:
>> >
>> >> 1) para el Dato1 se suman todas las cantidades registradas entre
>> >> la
>> >> fecha
>> >> introducida y 30 dias atras y luego la suma se divide por 30 (o
>> >> por el
>> >> numero de dias reales)? Y asi para el Dato2, Dato3, etc? De manera
>> >> q
>> >> si
>> >> hay
>> >> 3 datos el resultado seria 3 numeros.
>> >
>> >> o bien
>> >
>> >> 2) para el Dato1 se suman todas las cantidadeds registradas en el
>> >> dia
>> >> X y
>> >> se
>> >> saca el promedio? Y asi para cada dia dentro del periodo entre la
>> >> fecha
>> >> introducida y 30 dias atras y luego para el Dato2, Dato3, etc? De
>> >> manera
>> >> q
>> >> si hay 3 datos el resultado podria ser 90 numeros.
>> >
>> >> Dime algo,
>> >> KL
>> >
>> >
>> >> ""Hernán Gutiérrez"" wrote in message
>> >> news:O4g$
>> >> > Buenos días.
>> >> >
>> >> > Requiero un código en VBA que pemita hacer lo siguiente.
>> >> > Actualmente
>> >> > lo
>> >> > hacemos con tablas dinámicas pero no hace todo lo que queremos.
>> >> >
>> >> > Tengo un archivo excel con más de 3000 registros, tiene
>> >> > información
>> >> > de
>> >> > varios años.
>> >> > Cada registro (fila) tiene lo siguientes campos:
>> >> > Fecha(aa-mm-dd),dato1,dato2,dato3,dato4,dato5 . dato30.
>> >> >
>> >> > Necesito que con base en una fecha que yo digite en una celda
>> >> > especifica,
>> >> > calcule lo siguiente:
>> >> > 1) Calcule los 30 días anteriores y me saque un promedio por día
>> >> > del
>> >> > dato1,dato2,dato3.Para un mismo día puedo tener varios registros
>> >> >
>> >> > 2) Saque el promedio de cada mes del año presente (con base a la
>> >> > fecha
>> >> > digitada).
>> >> >
>> >> > 3) Saque el total del año.
>> >> >
>> >> > Muchas gracias por su colaboración
>> >> >
>> >> > Hernán Gutiérrez
>> >> >
>> >> >
>> >> >
>> >
>> >
>
>

Mi segundo macro tenia un fallo importante - calculaba las medias diarias
para cada mes sin hacer distincion de ano. Lo he corregido y ademas he
quitado algunos elementos redundantes.

Saludos,
KL
'--Inicio Codigo--
'Establece el # de dias con anterioridad a
'la fecha introducida q se tienen q evaluar.
Const NumeroDias = 30

Sub Promedios30Dias()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Dia As Single
Dim Contador As Single
Dim MiFecha As Range
Dim ColDatos()
Dim MiNumero()

'Establece el rango donde se introducira la fecha
'a evaluar y partir del cual se introduciran los
'resultados.
Set MiFecha = ActiveSheet.Range("AG2")

'Comprueba q la celda para la fecha no este vacia.
If MiFecha = "" Then Exit Sub

'Comprueba q el valor de la celda para la fecha
'tenga formato de fecha.
If Not IsDate(MiFecha) Then Exit Sub

'Detecta el rango usado de la hoja.
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'Saca datos de las columnas K,N y Q.
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Dia = MiFecha - 1
Contador = 1
While Dia >= MiFecha - NumeroDias
ReDim MiNumero(0)
For Each Registro In Registros
If Registro = Dia Then
'Idea de Leo Heuser.
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i))
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
Next Registro
'Crea los encabezados de filas con las fechas
'como nombres.
MiFecha.Offset(Contador, 0) = Dia

'Si el promedio es igual a 0...
If WorksheetFunction.Sum(MiNumero) = 0 Then
'se devolvera vacio.
MiFecha.Offset(Contador, i + 1) = ""
Else
MiFecha.Offset(Contador, i + 1) = _
WorksheetFunction.Average(MiNumero)
End If
Dia = Dia - 1
Contador = Contador + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub

Sub PromediosMes()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Mes As Single
Dim MiValorAnual As Range
Dim ColDatos()
Dim MiNumero()

'Establece el rango donde se introducira el valor
'anual a evaluar y partir del cual se introduciran
'los resultados.
Set MiValorAnual = ActiveSheet.Range("AL2")

'Comprueba q la celda para el valor anual no este vacia.
If MiValorAnual = "" Then Exit Sub

'Detecta el rango usado de la hoja.
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'Saca datos de las columnas K,N y Q.
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Mes = 1
While Mes <= 12
ReDim MiNumero(0)
For Each Registro In Registros
If IsDate(Registro) Then
If Month(Registro) = Mes And _
Year(Registro) = MiValorAnual Then
'Idea de Leo Heuser.
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i))
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
End If
Next Registro

'Crea los encabezados de filas con los nombres
'de los meses.
MiValorAnual.Offset(Mes, 0) = Choose(Mes, "Enero", _
"Febrero", "Marzo", "Abril", "Mayo", "Junio", _
"Julio", "Agosto", "Septiembre", "Octubre", _
"Noviembre", "Diciembre")

'Si el promedio es igual a 0...
If WorksheetFunction.Sum(MiNumero) = 0 Then
'se devolvera vacio.
MiValorAnual.Offset(Mes, i + 1) = ""
Else
MiValorAnual.Offset(Mes, i + 1) = _
WorksheetFunction.Average(MiNumero)
End If
Mes = Mes + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub
'--Fin Codigo-


"KL" <lapink2000(at)hotmail.com (former )> wrote in
message news:%

Hernan,

Aqui esta el codigo final incluyendo la respuesta a la pregunta #2 (total
dos macros). Tambien despues de pensar un poco y analizar tus postings
anteriores, llego a la conclusion de q en la pregunta #5 al decir
"clasificado" te referias a "ordenado". Si es asi, entonces te digo q no
es necesario q esten los datos ordenados sugun la fecha ya q el codigo
escanea todas las celdas q no esten vacias en la columna A y solo saca los
valores q corresponden a una fecha en concreto. Tambien te envio el
archivo a tu direccion de correo electronico.

Saludos,
KL
'--Inicio Codigo--
'Establece el # de dias con anterioridad a
'la fecha introducida q se tienen q evaluar.
Const NumeroDias = 30

Sub Promedios30Dias()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Dia As Single
Dim Contador As Single
Dim MiFecha As Range
Dim ColDatos()
Dim MiNumero()

'Establece el rango donde se introducira la fecha
'a evaluar y partir del cual se introduciran los
'resultados.
Set MiFecha = ActiveSheet.Range("AG2")

'Comprueba q la celda para la fecha no este vacia.
If MiFecha.Value = "" Then Exit Sub

'Comprueba q el valor de la celda para la fecha
'tenga formato de fecha.
If Not IsDate(MiFecha) Then Exit Sub

'Detecta el rango usado de la hoja.
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'Saca datos de las columnas K,N y Q.
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Dia = MiFecha.Value - 1
Contador = 1
While Dia >= MiFecha.Value - NumeroDias
ReDim MiNumero(0)
For Each Registro In Registros
If Registro.Value = Dia Then
'Idea de Leo Heuser.
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
Next Registro
'Crea los encabezados de filas con las fechas
'como nombres.
MiFecha.Offset(Contador, 0).Value = Dia

'Si el promedio es igual a 0...
If WorksheetFunction.Sum(MiNumero) = 0 Then
'se devolvera vacio.
MiFecha.Offset(Contador, i + 1).Value = ""
Else
MiFecha.Offset(Contador, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Dia = Dia - 1
Contador = Contador + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub

Sub PromediosMes()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Mes As Single
Dim Inicio As Range
Dim ColDatos()
Dim MiNumero()

'Establece el rango partir del cual se
'introduciran los resultados.
Set Inicio = ActiveSheet.Range("AL2")

'Detecta el rango usado de la hoja.
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'Saca datos de las columnas K,N y Q.
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Mes = 1
While Mes <= 12
ReDim MiNumero(0)
For Each Registro In Registros
If IsDate(Registro) Then
If Month(Registro) = Mes Then
'Idea de Leo Heuser.
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
End If
Next Registro

'Crea los encabezados de filas con los nombres
'de los meses.
Inicio.Offset(Mes, 0).Value = Choose(Mes, "Enero", _
"Febrero", "Marzo", "Abril", "Mayo", "Junio", _
"Julio", "Agosto", "Septiembre", "Octubre", _
"Noviembre", "Diciembre")

'Si el promedio es igual a 0...
If WorksheetFunction.Sum(MiNumero) = 0 Then
'se devolvera vacio.
Inicio.Offset(Mes, i + 1).Value = ""
Else
Inicio.Offset(Mes, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Mes = Mes + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub
'--Fin Codigo--

"KL" <lapink2000(at)hotmail.com (former )> wrote in
message news:

Hernan,

Para el beneficio de todos te pongo mi respuesta en el grupo. He
modificado el codigo y he anadido comentarios q deberian contestar la
mayoria de tus preguntas (sustituye el codigo anterior por completo). El
codigo para la pregunta #2 te pondre mas tarde - es q estoy de viaje esta
semana. La pregunta #5 no la he entendido, sobre todo eso de "el archivo
ni siquiera está clasificado". Ya me explicaras lo q quisiste decir.

Un saludo,
KL
'--Inicio Codigo--
'Aqui estableces el numero de dias con
'anterioridad a la fecha introducida
'q se tienen q evaluar.
Const NumeroDias = 30

Sub Promedios30Dias()
Dim i As Integer
Dim Registros As Range
Dim Registro As Range
Dim Dia As Single
Dim Contador As Single
Dim MiFecha As Range
Dim ColDatos()
Dim MiNumero()

'Aqui estableces el rango donde se introducira la
'fecha a validar y a partir del cual se introduciran
'los resultados.
Set MiFecha = ActiveSheet.Range("AG2")

'?Como controlar para que el proceso lo ejecute hasta
'que encuentre una celda vacia? Yo diria mas bien hasta
'q llegue al final de la lista. Asi. Antes de empezar
'detectas el rango usado de la hoja:
Set Registros = Sheets("Hoja1").UsedRange.Columns(1).Cells

'?Como hago para que me saque solamente el promedio de 3
'columnas, por ejemplo para la columnas K,N y Q (dato10,
'dato13 y dato16? Asi:
ColDatos = Array(Range("K:K").Column, Range("N:N").Column, _
Range("Q:Q").Column)

Application.ScreenUpdating = False
For i = 0 To UBound(ColDatos)
Dia = MiFecha.Value - NumeroDias
Contador = 1
While Dia < MiFecha.Value
ReDim MiNumero(0)
For Each Registro In Registros
If Registro.Value = Dia Then
'Idea de Leo Heuser
MiNumero(UBound(MiNumero)) _
= Registro.Offset(0, ColDatos(i)).Value
ReDim Preserve MiNumero _
(UBound(MiNumero) + 1)
End If
Next Registro
MiFecha.Offset(Contador, 0).Value = Dia

'?Como hago para que cuando el valor sea zero,
'lo deje en espacios, esto con el fin de no
'dajar la grafica? Asi...:
If WorksheetFunction.Sum(MiNumero) = 0 Then
'...y asi:
MiFecha.Offset(Contador, i + 1).Value = ""
Else
MiFecha.Offset(Contador, i + 1).Value = _
WorksheetFunction.Average(MiNumero)
End If
Dia = Dia + 1
Contador = Contador + 1
Wend
Next i
Application.ScreenUpdating = True
End Sub
'--Fin Codigo--



""Hern?n Guti?rrez" M." wrote in message

Hola KL.

Muy bien funciona perfecto lo de los días . Pero como apenas estoy
estudiando lo referente a VBA aprovecho tu generosidad para preguntarte
algo.

1) Como hago para que me saque solamente el promedio de 3 columnas, por
ejemplo para la columnas K,N y Q (dato10,dato13 y dato16).
2) Como obtengo el mismo resultado en los meses de ese año.
3) Como hago para que cuando el valor sea zero , lo deje en espacios,
esto con el fin de no dañar la gráfica.
4) Cómo controlar para que el proceso lo ejecute hasta que encuentre una
celda vacía.
5) Como lo lograste hacer, sabiendo que el archivo ni siquiera está
clasificado.

Mejor dicho como hago para entender esta macro tan excelente que hiciste.

Muchos saludos , y muchas gracias porque veo que se me van a acabar los
problemas con el manejo de ese archivo.

Hernán Gutiérrez

""Hernán Gutiérrez"" wrote in message
news:

Buenos días KL.

Por favor me podrías enviar el archivo que simulaste a la siguiente
direccion



Muchas gracias.

Hernan Gutierrez



KL wrote:

Hernan,

Algo no esta funcionando bien. Mi macro me funciona bien y devuelve 900
valores (30 dias por 30 datos) y no uno tal como tu describes
abajo.Asegurate que la fecha de validacion se introduce en la celda AG2
de
la misma hoja q la base de datos o bien cambia la variable MiFecha en
el
codigo. Tambien comprueba las siguientes constantes y variables:

Const NumeroDatos = 30
Const NumeroDias = 30

Set MiFecha = Range("AG2")
Set Datos = Range("B1:AE1")
Set Registros = Range("A1:A4000")

Y por supuesto, si quieres te mando mi hoja para q puedas comprobar
como
funciona o me mandas la tuya y te lo hago yo. Dime algo.

Saludos,
KL

""Hernán Gutiérrez" M." wrote in message
news:exjh$Y%
>
> Buenas tardes KL.
>
> Muchas gracias por responder.
>
>
> Al parecer tu macro muestra un valor. Para el caso de los últimos 30
> días
> móviles debe de mostrar 90 valores, o sea 30 días por 3 datos de cada
> día.
> Para el caso de los meses del año, debe mostrar los valores de los
> meses
> que van hasta esa fecha que digito el usuario. Si digito 2004/11/26
> debe
> calcular valores de enero hasta noviembre.
>
> El archivo original está así.
> 2003/01/02 dato1 dato2 dato3 mas otros campos informativos
> 2004/01/01 dato1 dato2 dato3
> 2004/01/01 dato1 dato2 dato3 existen varios registros para un
> mismo
> día
> .
> 2004/04/05 dato1 dato2 dato3
> .
> hoy existen 3764 registros
>
> Los resultados para la fecha digitada x usuario : 2004/12/15 (incluso
> en
> un momento dado se quiero ver la gráfica del comportamiento de otro
> periodo, por ejemplo podría digitar 2004/07/01 para analizar todo el
> mes
> de junio del 2004)
>
> 1) Ultimos 30 días a la fecha digitada : La macro me debe de calcular
> las
> fechas con sus promedios para cada día
> 2004/11/15 prom-dato1 prom-dato2 prom-dato3
> 2004/11/16 prom-dato1 prom-dato2 prom-dato3
> 2004/11/17 prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> 2004/12/14 prom-dato1 prom-dato2 prom-dato3
> 2004/12/15 prom-dato1 prom-dato2 prom-dato3
>
> 2) Los meses del año con los mismos promedios
> Enero prom-dato1 prom-dato2 prom-dato3
> Febrero prom-dato1 prom-dato2 prom-dato3
> y asi sucesivamente hasta
> Diciembre prom-dato1 prom-dato2 prom-dato3
>
> Se garantiza que el archivo original esta clasificado por fecha.
>
> Con los datos anteriores se generaría un gráfico automático en
> cualquier
> momento que lo requiera un ingeniero de planta , para cualquier fecha
> digitada y la macro debe estar disponible para que se pueda ejecutar
> desde
> cualquier equipo de la red. Actualmente este libro es compartido por
> 3
> equipos ubicados en diferentes lugares de la planta de producción.
>
> Muy extenso mi cuento ... pero la pregunta para KL ... Si se podrá
> hacer
> esto con una macro. Repito que con fórmulas matriciales {} esto se me
> salio de las manos, porque aparte de los datos el registro lleva otra
> informacion que no es importante para este cálculo pero que si ocupa
> espacio en cada fila.
>
> Un saludo y te reitero mis agradecimientos.
>
> Hernán
>
>
>
>
>
> L wrote:
>
>> Hernan,
>
>> He probado realizar la primera de tus tareas por medio de la
>> siguiente
>> formula (30 fechas x 30 datos = 900 celdas):
>
>>
>

=SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000))/SUMPRODUCT(($A$2:$A$4000=$BM3)*(B$2:B$4000<>""))

>
>> y el macro q he escrito (mas abajo) y me resultan las formulas
>> bastante
>> mas
>> rapidas q el macro. La unica ventaja del macro es q no se recalcula
>> cada
>> dos
>> por tres.
>
>> Saludos,
>> KL
>
>> Const NumeroDatos = 30
>> Const NumeroDias = 30
>
>> Sub Promedios30Dias()
>> Dim Dato As Integer
>> Dim Registros As Range
>> Dim Registro As Range
>> Dim Dia As Single
>> Dim Contador As Single
>> Dim MiFecha As Range
>> Dim MiNumero()
>
>> Set MiFecha = Range("AG2")
>> Set Datos = Range("B1:AE1")
>> Set Registros = Range("A1:A4000")
>
>> Application.ScreenUpdating = False
>> For Dato = 1 To NumeroDatos
>> Dia = MiFecha.Value - NumeroDias
>> Contador = 1
>> While Dia < MiFecha.Value
>> ReDim MiNumero(0)
>> For Each Registro In Registros
>> If Registro.Value = Dia Then
>> 'Idea de Leo Heuser
>> MiNumero(UBound(MiNumero)) _
>> = Registro.Offset(0, Dato).Value
>> ReDim Preserve MiNumero _
>> (UBound(MiNumero) + 1)
>> End If
>> Next Registro
>> MiFecha.Offset(Contador, 0).Value = Dia
>> If WorksheetFunction.Sum(MiNumero) = 0 Then
>> MiFecha.Offset(Contador, Dato).Value = 0
>> Else
>> MiFecha.Offset(Contador, Dato).Value = _
>> WorksheetFunction.Average(MiNumero)
>> End If
>> Dia = Dia + 1
>> Contador = Contador + 1
>> Wend
>> Next Dato
>> Application.ScreenUpdating = True
>> End Sub
>> -Fin Codigo
>
>
>> ""Hernán Gutiérrez"" wrote in message
>> news:
>> >
>> > KL : Muchas gracias por responder
>> > Faltó explicar que actualmente/ lo hago con fórmulas matriciales y
>> > los
>> > datos se obtienen, pero a medida que ha crecido el archivo la
>> > lentidud
>> > es
>> > abismal a pesar de tener equipos bien configurados. Los usuarios
>> > ya no
>> > soportan esto.
>> >
>> > El asunto es que si el usuario digito 2004/11/25 obtenga los
>> > siguientes
>> > datos en forma automática para poder graficar:
>> > 1) Promedio (o Desviacion Estandar) por día , para la fecha
>> > ejemplo
>> > sería
>> > :
>> > de Nov 25, Nov 24, Nov 23 Oct/25,Oct /24 (ültimos 30
>> > días),
>> > y para cada día existen muchos registros (se digitan cada 2
>> > horas)
>> > 2) Total por cada mes , en este caso del año 2004
>> > Ene, Feb, Mar ... Nov. (tener en cuenta que la fecha se digita
>> > con
>> > formato aa/mm/dd
>> > 3) Tttal del año en curso.
>> >
>> > Recuerda que el archivo es un histórico que puede tener hasta 2
>> > años en
>> > línea.
>> >
>> > Cómo se podrá hacer esto desde VBA, cómo VBA puede almacenar el
>> > total
>> > de
>> > los 30 días calendarios y de los meses del años en curso (en un
>> > vector
>> > o
>> > arreglo).
>> >
>> > Espero haberme hecho entender ... necesito una luz a este problema
>> > con
>> > carácter urgente.
>> >
>> >
>> > Muchas gracias
>> >
>> >
>> > KL wrote:
>> >
>> >> Me parece q todo esto se puede conseguir mediante unas formulas
>> >> relativamente simples. Solo una aclaracion: cuando hablas de
>> >> calcular
>> >> 30
>> >> dias anteriores y sacar el promedio por dia para dato1, dato2,
>> >> etc...
>> >> se
>> >> tiene q entender como por ejemplo:
>> >
>> >> 1) para el Dato1 se suman todas las cantidades registradas entre
>> >> la
>> >> fecha
>> >> introducida y 30 dias atras y luego la suma se divide por 30 (o
>> >> por el
>> >> numero de dias reales)? Y asi para el Dato2, Dato3, etc? De
>> >> manera q
>> >> si
>> >> hay
>> >> 3 datos el resultado seria 3 numeros.
>> >
>> >> o bien
>> >
>> >> 2) para el Dato1 se suman todas las cantidadeds registradas en el
>> >> dia
>> >> X y
>> >> se
>> >> saca el promedio? Y asi para cada dia dentro del periodo entre la
>> >> fecha
>> >> introducida y 30 dias atras y luego para el Dato2, Dato3, etc? De
>> >> manera
>> >> q
>> >> si hay 3 datos el resultado podria ser 90 numeros.
>> >
>> >> Dime algo,
>> >> KL
>> >
>> >
>> >> ""Hernán Gutiérrez"" wrote in message
>> >> news:O4g$
>> >> > Buenos días.
>> >> >
>> >> > Requiero un código en VBA que pemita hacer lo siguiente.
>> >> > Actualmente
>> >> > lo
>> >> > hacemos con tablas dinámicas pero no hace todo lo que queremos.
>> >> >
>> >> > Tengo un archivo excel con más de 3000 registros, tiene
>> >> > información
>> >> > de
>> >> > varios años.
>> >> > Cada registro (fila) tiene lo siguientes campos:
>> >> > Fecha(aa-mm-dd),dato1,dato2,dato3,dato4,dato5 . dato30.
>> >> >
>> >> > Necesito que con base en una fecha que yo digite en una celda
>> >> > especifica,
>> >> > calcule lo siguiente:
>> >> > 1) Calcule los 30 días anteriores y me saque un promedio por
>> >> > día del
>> >> > dato1,dato2,dato3.Para un mismo día puedo tener varios
>> >> > registros
>> >> >
>> >> > 2) Saque el promedio de cada mes del año presente (con base a
>> >> > la
>> >> > fecha
>> >> > digitada).
>> >> >
>> >> > 3) Saque el total del año.
>> >> >
>> >> > Muchas gracias por su colaboración
>> >> >
>> >> > Hernán Gutiérrez
>> >> >
>> >> >
>> >> >
>> >
>> >
>
>