Archivo lento y grande
Estoy haciendo un libro excel con fórmulas y macros, tras ir desarrollándolo me encuentro con que voy por 24 megas. Creo que el subidón (de menos de 1 mega a 24) se debió a la creación de dos hojas en las que realizo informes mediante búsquedas con fórmulas, rellenando estas celdas: de A10 hasta AJ5000, con estas fórmulas:
=SI(ESERROR(BUSCARV("NO";Capturas!AH2:AI1998;2;0));"";BUSCARV("NO";Capturas!AH2:AI1998;2;0))
=INDIRECTO(DIRECCION($A10;B$8;;;"Capturas"))
=SI(ESERROR(INDIRECTO(DIRECCION($A11;B$8;;;"Capturas")));""; SI($A11=$A10;"";INDIRECTO(DIRECCION($A11;B$8;;;"Capturas"))))
=SI(ESERROR(BUSCARV("NO";INDIRECTO("Capturas!"&"AH"&A10+1&":AI2000"); 2;0)); ""; BUSCARV("NO";INDIRECTO("Capturas!"&"AH"&A10+1&":AI2000"); 2;0))
No se si se debe a las propias fórmulas o a la cantidad de ellas o en realidad a cualquier otra cuestión.
El caso es que necesitaría reducir su tamaño y no se como. He pensado quitar todas estas fórmulas y hacer el informe con comandos while, pero ya lo uso para otra hoja y me parece lentísimo en su ejecución.
¿Alguna idea?
=SI(ESERROR(BUSCARV("NO";Capturas!AH2:AI1998;2;0));"";BUSCARV("NO";Capturas!AH2:AI1998;2;0))
=INDIRECTO(DIRECCION($A10;B$8;;;"Capturas"))
=SI(ESERROR(INDIRECTO(DIRECCION($A11;B$8;;;"Capturas")));""; SI($A11=$A10;"";INDIRECTO(DIRECCION($A11;B$8;;;"Capturas"))))
=SI(ESERROR(BUSCARV("NO";INDIRECTO("Capturas!"&"AH"&A10+1&":AI2000"); 2;0)); ""; BUSCARV("NO";INDIRECTO("Capturas!"&"AH"&A10+1&":AI2000"); 2;0))
No se si se debe a las propias fórmulas o a la cantidad de ellas o en realidad a cualquier otra cuestión.
El caso es que necesitaría reducir su tamaño y no se como. He pensado quitar todas estas fórmulas y hacer el informe con comandos while, pero ya lo uso para otra hoja y me parece lentísimo en su ejecución.
¿Alguna idea?
1 Respuesta
Respuesta de Elsa Matilde
1
Además del exceso de fórmulas (lo que hará que sea lentísima su apertura y actualización) quizás también al crear esas 2 hojas copiaste formatos, etc.
Lo que hacemos cuando nos es inevitable el uso de fórmulas, es irlas cargarndo, por código, a medida que rellenamos los registros.
Es decir, tu hoja tendrá la primer fila con las fórmulas.
Cuando copias o grabas un registro, se autorellena (como lo haces manualmente arrastrando desde la de arriba).
Y si fuera posible reemplazar fórmula por valor, mejor, porque en algún momento tendrás + de 1000 fórmulas, lo que hará que sea pesado también.
Las instrucciones las podes obtener haciendo los pasos manualmente, con la grabadora de macros encendida. Luego en un módulo encontrarás la rutina.
Sdos
Elsa
http://aplicaexcel.galeon.com/manual_VBA.htm
Lo que hacemos cuando nos es inevitable el uso de fórmulas, es irlas cargarndo, por código, a medida que rellenamos los registros.
Es decir, tu hoja tendrá la primer fila con las fórmulas.
Cuando copias o grabas un registro, se autorellena (como lo haces manualmente arrastrando desde la de arriba).
Y si fuera posible reemplazar fórmula por valor, mejor, porque en algún momento tendrás + de 1000 fórmulas, lo que hará que sea pesado también.
Las instrucciones las podes obtener haciendo los pasos manualmente, con la grabadora de macros encendida. Luego en un módulo encontrarás la rutina.
Sdos
Elsa
http://aplicaexcel.galeon.com/manual_VBA.htm
Gracias por tu respuesta Elsa.
Veo cual es el problema pero no entiendo muy bien la solución que me propones.
Entiendo que el objetivo es eliminar esa cantidad de fórmulas haciendo que aparezcan unicamente aquellas necesarias. El caso es que no se como hacer ese autorrelleno.
Me explico:
. Los datos los tengo en una hoja "capturas" que voy rellenando periódicamente.
. Donde tengo las fórmulas, lo que quiero es que me saque informes según condiciones que introduzco.
. Por ello nunca se cuantas filas debo autorrellenar. Si hago una macro que rellena hasta la fila 10 y tengo 8 registros que cumplen la condición "bien", pero si hay 11 o más me los perdería.
No se si me he explicado bien, si no es así me comentas.
Gracias y un saludo.
Veo cual es el problema pero no entiendo muy bien la solución que me propones.
Entiendo que el objetivo es eliminar esa cantidad de fórmulas haciendo que aparezcan unicamente aquellas necesarias. El caso es que no se como hacer ese autorrelleno.
Me explico:
. Los datos los tengo en una hoja "capturas" que voy rellenando periódicamente.
. Donde tengo las fórmulas, lo que quiero es que me saque informes según condiciones que introduzco.
. Por ello nunca se cuantas filas debo autorrellenar. Si hago una macro que rellena hasta la fila 10 y tengo 8 registros que cumplen la condición "bien", pero si hay 11 o más me los perdería.
No se si me he explicado bien, si no es así me comentas.
Gracias y un saludo.
No comprendo del todo el proceso. Voy a tratar de tomar cada uno de tus puntos:
. Los datos los tengo en una hoja "capturas" que voy rellenando periódicamente.
En este momento no parece que necesitaras todavía rellenar fórmulas. Pero si así fuese, tu rutina debe rellenar las celdas con la fórmula que tendrá la fila de arriba. Esto se logra grabando los pasos, de tomar la celda y arrastrar hacia abajo manualmente. (*)
. Donde tengo las fórmulas, lo que quiero es que me saque informes según condiciones que introduzco.
Aquí interpreto que tendrás una rutina para generar el informe. En este momento podes utilizar funciones desde una macro. Ej de una fórmula con Indice y Coincidir:
ActiveCell.Formula = "=+INDEX(C:C,MATCH(F2,E:E,0))"
. Por ello nunca se cuantas filas debo autorrellenar. Si hago una macro que rellena hasta la fila 10 y tengo 8 registros que cumplen la condición "bien", pero si hay 11 o más me los perdería.
Esto también se logra por macro:
final=Range("A65536"). end(xlup). Row 'última fila con datos según col A, puede ser cualquier otra
Tu primer fórmula será:
ActiveCell.Formula = _
"=IF(ISERROR(VLOOKUP(""NO"",Capturas!AH2:AI" & final & ",2,0)),"""",VLOOKUP(""NO"",Capturas!AH2:AI" & final & ",2,0))"
En este sitio están saliendo las respuestas todas amontonadas, espero se entienda la idea. Sino podes escribirme al correo que encontrarás en mi sitio.
. Los datos los tengo en una hoja "capturas" que voy rellenando periódicamente.
En este momento no parece que necesitaras todavía rellenar fórmulas. Pero si así fuese, tu rutina debe rellenar las celdas con la fórmula que tendrá la fila de arriba. Esto se logra grabando los pasos, de tomar la celda y arrastrar hacia abajo manualmente. (*)
. Donde tengo las fórmulas, lo que quiero es que me saque informes según condiciones que introduzco.
Aquí interpreto que tendrás una rutina para generar el informe. En este momento podes utilizar funciones desde una macro. Ej de una fórmula con Indice y Coincidir:
ActiveCell.Formula = "=+INDEX(C:C,MATCH(F2,E:E,0))"
. Por ello nunca se cuantas filas debo autorrellenar. Si hago una macro que rellena hasta la fila 10 y tengo 8 registros que cumplen la condición "bien", pero si hay 11 o más me los perdería.
Esto también se logra por macro:
final=Range("A65536"). end(xlup). Row 'última fila con datos según col A, puede ser cualquier otra
Tu primer fórmula será:
ActiveCell.Formula = _
"=IF(ISERROR(VLOOKUP(""NO"",Capturas!AH2:AI" & final & ",2,0)),"""",VLOOKUP(""NO"",Capturas!AH2:AI" & final & ",2,0))"
En este sitio están saliendo las respuestas todas amontonadas, espero se entienda la idea. Sino podes escribirme al correo que encontrarás en mi sitio.
