Jump to content

Como hacer que las iteraciones de calculo sean mas rápidas


javierc

Recommended Posts

Hola, 

Tengo un excel en el trabajo y cada vez que copio y pego valores tarda más de 3 minutos en desaparecer el círculo azul de Microsoft. 

¿Sabeis como hacer para que la velocidad de cálculo aumente?

He leido que borrar el exceso de formato ayuda. Estoy utilizando la opcion de borrar el exceso de formato. Sin embargo, selecciono Control + Fin y la celda activa es XFD430. La última celda con contenido es la GJ426.  Por ello, creo que no está funcionando el borrado de exceso de formato.  ¿Sabéis a que se debe?

 

 

Link to comment
Share on other sites

Hola @javierc

Revisa bien el archivo, estoy casi seguro que notarás un tamaño excesivamente grande (en megabytes), son muchas las causas y los efectos son archivos grandes y lentos.

Cosas a revisar:

  • Eliminar formatos condicionales excesivos pues son tratados como cálculos volátiles
  • Minimizar el uso de fórmulas volátiles (o activar el cálculo manual)
  • Revisar que no haya estilos excesivos en el archivo
  • Revisar que no hayan "imágenes" excesivas. He visto muchos que tienen miles de imágenes prácticamente invisibles de 1 pixel x 1 pixel y no saben que las tienen (no sé como duplican miles de imágenes sin darse cuenta)
  • Eliminar filas/columnas más allá de los límites de tu cuadro, si no usas hasta la columna XFD sino solo hasta GJ, entonces elimina las columnas desde GK hasta XFD.
Link to comment
Share on other sites

Gracias @DiegoPC

 

Elimino las columnas y tras ello hago control + Fin para ver la última celda activa. Me sigue llevando a la columna XFD.

¿Qué otra forma hay para eliminar las celdas fuera del cuadro de trabajo? Parece que no funciona.

Por otra parte, el archivo tiene contraseña, ¿afecta al archivo?

El archivo lo he heredado y no veo que haya imágenes. 

 ¿Hay alguna herramienta que las localice, similar a buscar?

Gracias de antemano.

Link to comment
Share on other sites

Gracias @DiegoPC

 

Elimino las columnas y tras ello hago control + Fin para ver la última celda activa. Me sigue llevando a la columna XFD.

He probado con una hoja nueva. He escrito en la celda L9 y después he eliminado fila y celda. Sin embargo, al hacer control+fin me sigue mostrado la celda L9 en lugar de I6. 

image.thumb.png.dd9434f987e3847c6ad6f4745696095d.png

 

¿Qué otra forma hay para eliminar las celdas fuera del cuadro de trabajo? Parece que no funciona.

Por otra parte, el archivo tiene contraseña, ¿afecta al archivo?

El archivo lo he heredado y no veo que haya imágenes. 

 ¿Hay alguna herramienta que las localice, similar a buscar?

Gracias de antemano.

Link to comment
Share on other sites

Haz tu proceso de Copiado y pegado pero primero ponlo en Formato - Recalculo Manual y al terminar de hacer sub cambios o correcciones vuelve a dar

Formato - Recalculo Automático o con la Tecla F9

Link to comment
Share on other sites

Hola

La combinación CTRL + FIN te lleva a la última celda usada (aunque borres el contenido), para que se "refresque" la última celda usada es necesario guardar el archivo.

Para que no te lleve a la XFD elimina las columnas desde la GK hasta XFD (asegúrate de seleccionar las columnas completas) y luego debes guardar el archivo, eso te llevará solo hasta la GJ cuando presiones CTRL + FIN. Si aun así no funciona, primero borra formatos de todas esas columas y luego guarda (podría ser que tu antecesor aplicó formato a absolutamente toda la hoja)

Las imágenes las puedes ver gracias al panel de selección. He visto muchas veces archivos con cientos de imágenes, que prácticamente son imágenes diminutas y de color blanco, lo que hace que no sean detectadas a simple vista pero que allí están. Los autores de esos archivos no se habían dado cuenta de eso y no sabían cómo podrían haberlo logrado.

image.thumb.png.f83ab17b60d4119c3fd470be743f97d4.png

No importa si tu archivo tiene contraseña, eso no afecta.

Dale una revisada también a los estilos del libro.

Link to comment
Share on other sites

21 hours ago, DiegoPC said:

Hola

La combinación CTRL + FIN te lleva a la última celda usada (aunque borres el contenido), para que se "refresque" la última celda usada es necesario guardar el archivo.

Para que no te lleve a la XFD elimina las columnas desde la GK hasta XFD (asegúrate de seleccionar las columnas completas) y luego debes guardar el archivo, eso te llevará solo hasta la GJ cuando presiones CTRL + FIN. Si aun así no funciona, primero borra formatos de todas esas columas y luego guarda (podría ser que tu antecesor aplicó formato a absolutamente toda la hoja)

Las imágenes las puedes ver gracias al panel de selección. He visto muchas veces archivos con cientos de imágenes, que prácticamente son imágenes diminutas y de color blanco, lo que hace que no sean detectadas a simple vista pero que allí están. Los autores de esos archivos no se habían dado cuenta de eso y no sabían cómo podrían haberlo logrado.

image.thumb.png.f83ab17b60d4119c3fd470be743f97d4.png

No importa si tu archivo tiene contraseña, eso no afecta.

Dale una revisada también a los estilos del libro.

Muchas gracias.

He borrado las columnas más de 3 veces en varias hojas y me sigue llevando al final.

Respecto al tema de las fotos, he seguido los pasos  que me indicastey me sale un comentario 1, pero no sé como borrarlo. Agradecería que me guiaras con ello.

Por último, me gustaría saber cómo reviso los estilos de libro

 

Gracias de antemano

image.thumb.png.b41ba773bbec7800b9ef3f0f024229b7.png

Edited by javierc
Link to comment
Share on other sites

Es extraño lo que te sucede con las columnas, asegúrate de seleccionar absolutamente toda la columna usando los encabezados (las letras del área ploma donde están las letras)

Si solo hay un comentario entonces no creo que eso lo esté lenteando.

Los estilos los revisas en Inicio, en el grupo Estilos. También puedes hacerlo en la ventana de inmediato del editor VBE con el comando 

?Thisworkbook.Styles.Count

Si te salen demasiados (más de 150) podría ser eso lo que falla.

Link to comment
Share on other sites

Muchas gracias, revisaré los estilos.

Respecto a borrar el exceso de columnas, selecciono los encabezados. 

Probé con un archivo nuevo y escribí en las celdas J8 y X13. Borré las columna posteriores a J y al hacer control +fin, me seleccionaba la celda X13, incluso tras guardarlo. Por ello, no es algo que suceda solo en mi archivo. 

Link to comment
Share on other sites

Hola Javier

Te he preparado una pequeña macro que analizará qué estilos tienes y te dirá en cuántas celdas se está usando cada estilo. La macro podría ser demasiado lenta si tu archivo tiene muchísimas hojas y celdas usadas pero podría ser de utilidad.

Copia el código en el editor de macros y a ejecutar... no importa en qué libro pegues el código porque siempre trabajará con el libro activo.

Sub Estilos()
    On Error Resume Next
    
    Dim Hoja As Worksheet
    Dim H As Worksheet
    Dim C As Range
    Dim Estilo As Style
    Dim Fila As LongPtr
    Dim Contador As LongPtr
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ActiveWorkbook.Worksheets("Estilos").Delete
    Set Hoja = ActiveWorkbook.Worksheets.Add
    Hoja.Name = "Estilos"
    Hoja.[A1].Value = "Nombre"
    Hoja.[B1].Value = "Tipo"
    Hoja.[C1].Value = "Celdas"
    
    Fila = 2
    For Each Estilo In ActiveWorkbook.Styles
        Hoja.Range("A" & Fila).Value = Estilo.Name
        Hoja.Range("B" & Fila).Value = IIf(Estilo.BuiltIn, "Integrado", "Personalizado")
        Contador = 0
        For Each H In ActiveWorkbook.Worksheets
            For Each C In H.UsedRange
                If C.Style = Estilo.Name Then Contador = Contador + 1
            Next
        Next
        Hoja.Range("C" & Fila).Value = Contador
        Fila = Fila + 1
    Next
    
    Hoja.[A1].CurrentRegion.Columns.AutoFit
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

Link to comment
Share on other sites

Hola Diego,

 

He  dejado la Excel trabajando durante toda la noche y no se ha terminado. 

Quería preguntarte si es posible segmentar la macro para reducir el tiempo. 

Tengo unas 20 hojas.

 

Gracias de antemano,

Link to comment
Share on other sites

Hola Javier

Tus archivos dan miedo!!! jajajaja. Bueno, hice una modificación para que tengas un mayor control y calcules solo la parte que quieras calcular. Obviamente que requiere más cosas de tu parte. El funcionamiento es el siguiente:

  1. Abre el archivo del terror que contiene tus 20 hojas y los tres millones de estilos. 😂
  2. Descarga el adjunto y ábrelo también.
  3. Haces click en el primer botón de arriba a la izquierda y seleccionas tu libro.
  4. Haces click en el segundo botón y se cargarán los estilos existentes en tu libro del terror.
  5. Haces click en el tercer botón y se cargarán las hojas existentes en tu libro del terror. Hasta este paso todo es rapidito.
  6. Ahora viene la parte demorona y que puedes hacer por partes: Selecciona únicamente las celdas que quieras calcular (intersecciones de nombre de estilo VS nombre de hoja) y con esas celdas seleccionadas simplemente haces un CLICK DERECHO, eso calculará cuántas celdas de esa hoja tienen ese estilo.

Felicidades! Ya tienes tarea para las siguientes dos semanas 😁

Revisa Estilos.xlsm

Link to comment
Share on other sites

Como comenta Janlui evita que con cada dato que introduzcas recalcule todo. eso se puede hacer incluyendo

        Application.Calculation = xlCalculationManual

pero tendrás que incluir

        Calculate

cada vez que quieras utilizar los valores modificados. Si tu macro trabaja con varias hojas  y muchos datos en ella cada vez que incluyes un nuevo dato recalcula todo, lo que lleva cierto tiempo, por ello solo utilices"Calculate" solo cuando tu macro utilice un valor modificado por los uktimos datos , esto reduce el tiempo de cálculo dráasticamente

Si quiere que al final quede en forma de cálculo automático deberças incluir

       Application.Calculation = xlCalculationAutomatic  

Link to comment
Share on other sites

Hola Diego,

 

He intentado hacer tu guía pero me sale este mensaje tras seleccionar cargar estilos:

image.png.655a65e3029cf9f8963568f5ab3cd1fc.png

 

Si selecciono debug, me sale esta flecha en la macro:

image.thumb.png.f2c6ff360c770a2312b8badfae85e467.png

 

Qué puedo hacer?

Gracias de antemano.

Link to comment
Share on other sites

Hace 51 minutos , javierc dijo:

Hola Diego,

 

He intentado hacer tu guía pero me sale este mensaje tras seleccionar cargar estilos:

image.png.655a65e3029cf9f8963568f5ab3cd1fc.png

 

Si selecciono debug, me sale esta flecha en la macro:

image.thumb.png.f2c6ff360c770a2312b8badfae85e467.png

 

Qué puedo hacer?

Gracias de antemano.

Hola Javier

Veo que tu archivo tiene 1085 estilos, el nombre de algunos de ellos empieza con el signo igual o con el signo de adición y cuando la macro intenta poner el nombre del estilo en las celdas de la columna A salta el error. Para evitarlo, modifica la línea amarilla por esta otra

 

Range("A10").Offset(Contador, 0).Value = Chr(39) & Estilo.Name

Eso evitará que los estilos con nombre extravagante causen error en la macro.

Otra cosa muy importante es que veo que la última celda usada en varias hojas está en la XFD... eso hace que tu archivo sea de 37 MB estando vacío. Siempre ten mucho cuidado de asignar formatos únicamente a las celdas que usarás y no a todas.

Link to comment
Share on other sites

Para eliminar filas/columnas no usadas y que probablemente tienen formatos aplicados y están haciendo que el libro sea demasiado grande se sigue el siguiente procedimiento:

  1. Vas a la última celda que REALMENTE estés usando.
  2. Eliminas todas las columnas que estén a su derecha.
  3. Otra vez te ubicas en la última celda que REALMENTE estes usando.
  4. Eliminas todas las filas que estén debajo.
  5. Repites los pasos del 1 al 4 para cada hoja
  6. Guardas tu libro

Yo lo hice solo en una hoja de tu archivo (la que se llama 2I) y el archivo se redujo en 8MB (de 37 a 29)

Edited by DiegoPC
Completar la idea
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

Privacy Policy