Optimizar filtros libro binario excel 2010
Una consulta tengo un libro de excel el cual tiene entre datos y formular desde la columna A hasta la columna CS, y de filas puede tener hasta 40.000, el problema es que a la hora de filtrar es demasiado lento, por lo cual me toca abrir un segundo archivo el cual arroja el mensaje de " recursos insuficientes para completar esta tarea. Elija menos datos o cierre otras aplicaciones", en ese momento ya los filtros funcionan con normalidad (rápidos), ¿existe alguna forma de solucionar esto con macro o así? Sin tener que abrir otros libros.
1 Respuesta
Ese tipo de mensajes puede salir por varias razones, siendo las más comunes las siguientes:
1- Tu equipo tiene poco RAM (o RAM destinado a "ciertos" programas), un procesador no muy rápido y/o te queda poco espacio en el disco duro.
2- Se ha alcanzado el límite de formatos, y otros, permitidos para un archivo de Excel
3- El archivo/libro, a pesar de ser del tipo binario de Excel, tiene un exceso (sin haber alcanzado necesariamente el límite) no de datos, sino de formatos/fórmulas ¿cómo es eso? En general muchas personas tienen la "mala"costumbre de dar formatos en exceso; por ejemplo, si tiene datos en el rango "A1:A1000", en lugar de dar color de fondo (trama) a ese rango, sombrean/eligen toda la columna "A" y le dan formato. Multiplica eso por varias columnas y celdas. Eso "engorda" el archivo ("pesa" más megas) y o vuelve lento y consume más memoria. Y no solo ocurre con los formatos. Por ejemplo ( y no digo que necesariamente sea tu caso) tienes 40,000 filas y has comentado que con fórmulas en varias columnas, entonces, quizá tienes esa cantidad de filas pero como esperas seguir llenando datos, quizá tus fórmulas están hasta la fila 100,000. El exceso de vínculos también da el error mencionado. Y así una cantidad de ejemplo. Esas cosas, como ya comenté, vuelen lento el archivo y se necesita más memoria.
Por lo que comentaste tienes cerca de 4 millones de celdas llenas, pero a pesar de eso, sería muy raro que te ocurra el problema solo por dichos datos. Igual no olvidemos que Excel es una hoja de cálculo y no un sistema gestor de base de datos.
Comentas
Abraham Valencia
Bueno partiendo de lo principal tengo 2 archivos los cuales denomino así:
- Archivo A (este es el que utilizo, este no genera ningún tipo de error, con respecto a las formulas si agrego datos a la fila siguiente voy arrastrando las formulas a medida que avanzo para no tener filas formuladas sin utilizar)
- Archivo B (Este es el que debo abrir siempre para forzar el error " recursos insuficientes para completar esta tarea. Elija menos datos o cierre otras aplicaciones", después de este error lo cierro y continuo trabajando con el archivo A)
El "archivo A" al abrirlo inicialmente tiene el inconveniente que a la hora de filtrar, eliminar o agregar filas se torna demasiado lento, incluso generando que excel "no responda" por un corto tiempo haciendo estas labores, por lo tanto para solucionar esto abro el "archivo B", el cual es otro muy diferente que no utilizo y esta muy cargado, generando el error " recursos insuficientes para completar esta tarea. Elija menos datos o cierre otras aplicaciones", luego de este error simplemente cierro este "archivo B" y el archivo A ya queda funcionando perfectamente, es como si al generarse este error el excel destinara mas recursos para el "archivo A".
Por lo que mi pregunta es si existe alguna forma para solucionar esto, para que no sea necesario abrir el "archivo B", lo de las celdas sombreadas no es el caso, pues ya elimine todo tipo de formatos en el libro y pasa igual, es mas bien las celdas con formulas las cuales son necesarias, gracias.
Entonces, descartado el exceso de formatos y, supongo que las cuestiones de memoria. Pero me quedan algunas duda ¿hay vínculos del archivo "A" hacia el archivo "B"? ¿Por algo en especial eliges abrir ese archivo "B" (quizá los vínculos)?
Mientras comentas eso, van algunos consejos para (tratar de) evitar el dichos problema:
- Antes de filtrar (o incluso de modo permanente) cambia el calculo a "manual", cuando hay muchos datos y está en automático, eso vuelve más lentos a los archivos/libros
- Si tienes fórmulas con resultados que ya no van a cambiar/variar, convierte dichos resultados a valores. Entre meno fórmulas, menos memoria requerida.
- Si tienes fórmulas con referencia a columnas y/o filas completas (Tipo SUMA(A:A)) cámbialas por los rangos necesarios solamente (Tipo SUMA(A2:A4567))
También (dicen) puede ayudar a "aliviar" el tema de memoria el borrar los archivos Excel14.xlb y/o Excel15.xlb, que están en:
C:\Users\Abraham\AppData\Roaming\Microsoft\Excel
Son carpetas ocultas por si acaso(Reemplazar "Abraham" por el usuario correspondiente). OJO, yo nunca he usado ese método por si acaso.
Abraham Valencia
