Ingresar los valores de celdas que poseen funciones en un arreglo
La macro que deseo crear debe insertar los valores de varias celdas con funciones en un arreglo. Quiero que las celdas (las cuales poseen una fórmula) formen parten de un arreglo y de esa forma hacer mucho más rápido el proceso de calculo. Puedo enviarle las macros, estaría muy agradecida
2 Respuestas
Sube la información a un servicio de nube y por el link aquí, y se facilitan más las cosas si los archivos no tienen password
https://drive.google.com/file/d/1lJ4mTTWmOgTtqucXiYdb7Z-Mi4_pxfuY/view?usp=sharing
Hola, muchas gracias, ahí se encuentra el archivo, no sé si la explicación que di anteriormente fue clara, he borrado los datos que no se utilizan por confidencialidad, pero en resumen necesito que los valores calculados de la columan AQ en adelante se calculen de forma más rápida, se me ha ocurrido hacer un arreglo. Sin embargo, si tiene otra recomendación es bienvenida
Ya vi tu información y no entiendo el problema mira la imagen, colocar poco más de 20000 fórmulas en un rango de 3347 filas por 6 columnas toma 14 segundos y la mayor parte de ese tiempo es por que el Excel hace su propio proceso de calculo, una opción es no formular y que la macro haga los cálculos colocando solo el resultado en las celdas solo que tendrías que activar la macro cada que llenaras las celdas.
De hecho las mismas fórmulas se realizan en 16 columnas que fueron borradas, solo quería saber el funcionamiento de las seis primeras. En la computadora en que se ejucata se tarda horas, sé que son problemas de la pc, pero dado que no se le puede dar mantenimiento quería saber si podía optimizarlo por medio de macros.
¿Cómo se realiza lo que recomiendas? ¿Disminuiría el tiempo? ¿O en el caso ya comentado que más sugieres?
No, no es problema de mantenimiento de la pc, se hace lento porque cuando creas una columna con fórmula y añades una segunda columna Excel hace un recalculo de la primera columna y un calculo de la 2a columna si añades una tercer recalculara las 2 primeras más la tercera y así sucesivamente, es decir que si tienes 17 columna de 100,000 y cambias una sola de la celdas va a recalcular 1,700,000 fórmulas y esto cada vez que hagas un cambio además de que va a a pasar cada que cambies una celda y aun cuando se mejore la macro esto va a seguir pasando por que el recalculo lo hace excel incluso puedes poner en manual para eficientizar la velocidad pero cuando des f9 o cambies el modo a calculo automático tendrás el mismo problema, en la imagen veras el resultado de la macro en colocar 600,000 fórmulas (100,000 filas * 6 columnas) tarda 29 segundos solo que tienen un truco pone la fórmula calcula el resultado y luego lo pone como valor, esto impide que Excel ejecute el calculo automático cada que agregues una columna, al final te quedan solo los resultados
y esta es la macro
Sub RELLENA()
Range("X5:X100005").Formula = "=RAND()*100"
Range("X5:X100005").Value = Range("X5:X100005").Value
Range("AG5") = 1
Range("AG5").AutoFill Destination:=Range("AG5:AG100005"), Type:=xlFillSeries
Range("T2:T26").FormulaArray = "=INT(RAND()*101)"
End Sub
Sub FORMULAR_CELDAS()
N = Range("AV4").Formula
INICIO = Time
Set HD = Worksheets("DATA")
Set H2 = Worksheets("HOJA2")
With HD
FILAS = .Range("AG5").CurrentRegion.Rows.Count - 1
Set DATOS = .Range("AQ5").Resize(FILAS, 6)
End With
With H2.Range("B2")
FILAS2 = .CurrentRegion.Rows.Count - 1
Set TABLA = .Resize(FILAS2, 8)
End With
With DATOS
C = .Columns.Count
For I = 1 To C
.Columns(I).Formula = "=IFERROR(IF($AI" & I + 4 & "=" & """Y""" & _
",VLOOKUP($AG5,Hoja2!" & TABLA.Address & "," & I + 3 & ",0)*$X5,),)"
.Columns(I).Value = .Columns(I).Value
Next I
.Columns(6).Formula = "=IF($AI5=" & """Y""" & ",SUM($AQ5:$AT5)-$X5," & "" & ")"
End With
FIN = Time
TIEMPO = (FIN - INICIO)
MsgBox (Format(FILAS * C, "0,0") & " FORMULAS COLOCADAS EN " & _
Second(TIEMPO) & " SEGUNDOS"), vbInformation, "AVISO"
set datos=nothing: set tabla=nothing: set hd=nothing: set tabla=nothing
End Sub
Muchisimas gracias por tu tiempo, mereces todos los puntos, solo una ultima pregunta antes de cerrar, si no depende de la pc entonces porque el proceso es más rápido en tu compu y más lento en el mio (tarda al rededor de tres horas).
Y con lo anterior significa que no hay solución para ese problema aun con la macro?
El equipo que corro la macro es un modelo 2008, con una memoria de menos de 1 gb en RAM, office 2007 y sistema XP, desconozco el modelo y características de tu de tu equipo, al mio el único mantenimiento que le doy es por medio de software gratis de internet como el CCleaner mucho depende del volumen de información que manejes si tienes tablas dinamics, gráficas, miles de registros con fórmulas eso también hace lentos los cálculos y por tanto hacen lentas las actualizaciones de las fórmulas, la macro que te proporcione como dije fórmula las celdas y luego las convierte a valor fijo con eso Excel ya solo hace un recalculo de la columna que va formulando, la otra es que tengas más hojas formuladas que se activaran en cuanto corras la macro, la otra es que tengas la hoja corrompida prueba copiando tu información a otro libro y a ese libro le instalas la macro.
https://drive.google.com/file/d/1lJ4mTTWmOgTtqucXiYdb7Z-Mi4_pxfuY/view?usp=sharing
Hola, muchas gracias, ahí se encuentra el archivo, no sé si la explicación que di anteriormente fue clara, he borrado los datos que no se utilizan por confidencialidad, pero en resumen necesito que los valores calculados de la columna AQ en adelante se calculen de forma más rápida, se me ha ocurrido hacer un arreglo. Sin embargo, si tiene otra recomendación es bienvenida.
El rango de filas es variable, el de columnas es fijo. Los valores calculados de las columnas que necesito van desde la AQ en adelante
-Quiero que guarde las fórmulas
-Quiero que los valores calculados (obtenidos a través de las fórmulas) contenidos en los arreglos se impriman en las celdas correspondientes
[Hola
Disculpa por la demora, se me "pasó" la pregunta ¿pudiste resolver tu dilema con las sugerencias que te dieron?
Abraham Valencia
Hola, fue de mucha ayuda, pero me gustaría saber si tienes otra sugerencia al respecto
[Hola
Gran parte de la lentitud de los procesos en tu archivo es porque el tamaño es un exceso, tiene más de 14 megas; sé que borraste cosas privadas de él, pero así tengas muchos datos, debería de tener menor tamaño. Ah, obvio las fórmulas incrementan dicho tamaño. Mira, lee esto:
https://abrahamexcel.blogspot.com/2018/01/el-gran-problema-de-los-archivos-lentos.html
Sobre tu archivo, prueba así, así tu archivo y tu PC estén un poco "lentos", esto no demorará mucho:
Sub Resultados()
Dim UltimaFila As Long
Dim x As Integer, col1 As Integer, col2 As Integer, off1 As Integer, off2 As Integer
Dim Celda As Range
Dim Vuno$, Vdos$, letra1$, letra2$
Application.ScreenUpdating = False
Let UltimaFila = Cells(Rows.Count, 35).End(xlUp).Row
Let col1 = 43: Let col2 = 5
Let off1 = -8: off2 = -10
For x = 4 To 8
Let letra1 = Mid(Cells(1, col1).Address, 2, (InStr(2, Cells(1, col1).Address, "$")) - 2)
Let letra2 = Mid(Cells(1, col2).Address, 2, (InStr(2, Cells(1, col2).Address, "$")) - 2)
For Each Celda In Range(letra1 & "5:" & letra1 & UltimaFila)
Let Vuno = Range(letra1 & Celda.Row).Offset(0, off1).Address
Let Vdos = Range(letra1 & Celda.Row).Offset(0, off2).Address
Celda.Value = Evaluate("=IFERROR(IF(" & Vuno & "=""Y"",VLOOKUP(" & Vdos & ",Hoja2!$B:" & letra2 & "," & x & ",0)*$X" & Celda.Row & ",""""),"""")")
Next Celda
Let col1 = col1 + 1: Let col2 = col2 + 1
Let off1 = off1 - 1: off2 = off2 - 1
Next x
Application.ScreenUpdating = True
End SubComentas
Abraham Valencia
Hola, Abraham. Por alguna extraña razón la macro congela el excel :(, he leído tu blog y consideraba migrarlos a access pero las fórmulas que te he mostrado no sé de qué manera las puedo calcular ahí. Ya he tomado las medidas que muestras y no se soluciona el problema.
Probé la macro del otro comentario ajeno al tuyo y funciona, pero extrañamente no con todas las celdas algunas no las calcula
Tu propio archivo:
https://drive.google.com/file/d/119ktMBMDeTgSeyOuIAmYanoHh3KPlPyF/view?usp=sharing
Por cierto, compara el tamaño ahora con el que enviaste.
Saludos]
Abraham Valencia
Hiciste algo además de borrar filas y columnas para reducir su tamaño?
Pues seleccione los datos de la "Hoja2" y lo pegue en una hoja nueva, luego borré esa "Hoja2" y a la de los datos pegados le coloqué ese mismo nombre. OJO con algo, para copiar los datos seleccioné solo el área con ellos, NO todas las columnas y/o filas, que en realidad es una mala costumbre.
Abraham Valencia
Muchas gracias, Abraham. Disculpa tanta preguntadera, pero tu código me ha resultado más lento. Estoy intentando con el código del archivo original solo pegar los valores y no las fórmulas, ¿cómo podría hacer eso?
La macro que me pasaron como te había comentado me deja algunas celdas sin calcular y por eso no he podido utilizarla.
Podrías ayudarme, por favor :(
¿Probaste en el archivo que puse en el "Drive"? A mí me demoró 0.3 segundo, o sea ¡Ni un segundoi ¿lo probaste? ¿O copiaste/pegaste en otro archivo? Otra cosa, justamente mi código NO deja fórmulas en las celdas, solo resultados. Me da la impresión que no lo probaste en el archivo que te dejé.
Abraham Valencia