Estado detallado del documento de Excel lento

Question 1

Para responder a su pregunta, no, no hay registro de rendimiento ni resultados de depuración en Excel. Puede ejecutar la función de temporizador en código VBA para cronometrar la ejecución de varias subfunciones o macros de VBA (pero no recálculos de Excel directamente).

Mis principales consejos para optimizar la velocidad de su libro de Excel:

Comparar el tamaño de los datos del libro de trabajo(columnas, filas, hojas) y tamaño de archivo. Un archivo desproporcionadamente grande puede tener algunos diagramas o dibujos ocultos que ocupan grandes cantidades de memoria. Puedes cambiar el nombre de .xlsx a .zip, luego descomprimirlo en una carpeta y buscar el archivo más grande para darte una pista.
Guardar como diferentes tipos de archivos, por ejemplo, el antiguo .xls se guarda en el nuevo .xlsx, el nuevo .xlsx en el binario .xlsb. Esto es especialmente útil con tiempos prolongados de carga y guardado de grandes conjuntos de datos.
Verifique el uso de CPU y memoria. El uso elevado de memoria sin mucho movimiento de la CPU indicaría que se trata principalmente de una gran cantidad de datos, pero que en realidad no requiere un procesamiento intensivo. El formato binario (.xlsb) junto con actualizaciones de RAM (el doble de RAM) y de disco (SSD) ayudarán. También verifique la configuración de guardado automático/copia de seguridad para reducir la cantidad de veces que se guardan/respaldan los archivos mientras se trabaja en ellos.
SiUso de CPU y memoriason altos, es probable que tengas que recurrir a las fórmulas para optimizarlos. Otra indicación de cálculos intensivos de fórmulas sería una desaceleración repentina y masiva después de, por ejemplo, editar una de las primeras celdas de entrada, mientras que es relativamente rápida solo durante la visualización (excepto el tiempo de carga inicial).
Funciones VOLÁTILES(OFFSET, INDIRECT, NOW, RAND, etc.) es la muerte de cualquier hoja de cálculo grande. Puede verificarlos editando una celda solitaria, en blanco y sin referencias en la parte inferior. Si esta edición provoca varios retrasos, es probable que las funciones volátiles sean las culpables. Ah y no lo olvidesFORMATO CONDICIONALES VOLÁTIL también.
Disparadores VOLÁTILESincluir acciones que provocan un recálculo en toda la hoja (de cálculo), como cambiar los filtros automáticos, ajustar el ancho y alto de las celdas, buscar objetivos, ocultar/mostrar/inserciones/eliminaciones/movimientos/cambios de nombre (de celdas y hojas), actualizar conexiones, por ejemplo, a CSV. archivos o bases de datos, cambiando celdas y rangos con nombre. Si alguno de estos también se ejecuta en macros de VBA, lo tienes.
AJUSTES DE RECÁLCULOpuede influir en gran medida en el rendimiento. Si es posible, desactive el recálculo automático y vuelva a calcular manualmente después de las actualizaciones/edición. Evite cálculos iterativos.
Seguimiento de dependientes/antecedentesusando las opciones en la cinta Fórmulas, o usando CTRL+[ y CTRL+]. Esto último es bastante útil ya que puede presionar CTRL+[varias veces y puede determinar los dependientes de los dependientes de los dependientes. En realidad, se seleccionan a medida que avanza (en contraste con las flechas agregadas por los botones en la cinta) y se pueden codificar/etiquetar con colores como desee.
Mostrar o encontrar fórmulas problemáticas.Puede alternar la cinta Fórmulas >Mostrar fórmulas(CTRL+~), para detectar fácilmente fórmulas problemáticas. También puedes hacer un libro de trabajoencontrarpara funciones volátiles, por ejemplo buscar OFFSET.
No olvide los rangos nombrados.Consulte el administrador de nombres para ver los rangos con nombres que contienen funciones volátiles.
IrNO VOLÁTIL. Reemplace INDIRECT con INDEX(..MATCH(..)) y OFFSET con rangos con nombres relativos (como un rango con nombre "CellAbove")
Optimización de la memoria de datos.Reducir elGAMA USADA(no hay filas ni columnas en blanco dentro del rango utilizado). Evite hacer referencias cruzadas a otros libros de trabajo o incluso a otras hojas. Evite almacenar datos como texto. Asegúrese de que sean datos numerados. Evite los números imaginarios, ya que en realidad se almacenan como texto. Si tiene Office 365, use LET para evitar volver a calcular la misma fórmula una y otra vez. Por ejemplo , =IF(LOG(A1)>B1,LOG(A1),B1)puede ser más bien =LET(x, LOG(A1), IF(x > B1, x, B1)). Además, si diferentes celdas hacen la misma parte de una ecuación una y otra vez, es mejor calcularla en una celda separada o definir con nombre.
Optimice sus macros/código VBA/javascript. Desactive el recálculo/actualizaciones de pantalla/eventos al inicio de cada subllamada o llamada de función. Ejecute el resto del código y vuelva a encenderlos.
Usarbloquear lectura y escritura de celdas. Esto es especialmente cierto en código VBA/macro/javascript. El acceso individual a los celulares es lento, especialmente si las actualizaciones de pantalla están activadas. Intente acumular sus respuestas en una matriz y bloquee escríbalas nuevamente en las celdas. En relación con esto, puede usar fórmulas de matriz (y derrames en Excel 365) para hacer el equivalente de bloques de lectura/escritura en fórmulas de Excel. No he verificado si esto es realmente más rápido pero mi instinto dice que sí.
Verifique las conexiones de datos.Algunas conexiones están configuradas para actualizarse automáticamente si abre el libro, lo que puede aumentar drásticamente los tiempos de carga generales, especialmente al actualizar datos en línea/de red.

Answer

Para responder a su pregunta, no, no hay registro de rendimiento ni resultados de depuración en Excel. Puede ejecutar la función de temporizador en código VBA para cronometrar la ejecución de varias subfunciones o macros de VBA (pero no recálculos de Excel directamente).

Mis principales consejos para optimizar la velocidad de su libro de Excel:

Comparar el tamaño de los datos del libro de trabajo(columnas, filas, hojas) y tamaño de archivo. Un archivo desproporcionadamente grande puede tener algunos diagramas o dibujos ocultos que ocupan grandes cantidades de memoria. Puedes cambiar el nombre de .xlsx a .zip, luego descomprimirlo en una carpeta y buscar el archivo más grande para darte una pista.
Guardar como diferentes tipos de archivos, por ejemplo, el antiguo .xls se guarda en el nuevo .xlsx, el nuevo .xlsx en el binario .xlsb. Esto es especialmente útil con tiempos prolongados de carga y guardado de grandes conjuntos de datos.
Verifique el uso de CPU y memoria. El uso elevado de memoria sin mucho movimiento de la CPU indicaría que se trata principalmente de una gran cantidad de datos, pero que en realidad no requiere un procesamiento intensivo. El formato binario (.xlsb) junto con actualizaciones de RAM (el doble de RAM) y de disco (SSD) ayudarán. También verifique la configuración de guardado automático/copia de seguridad para reducir la cantidad de veces que se guardan/respaldan los archivos mientras se trabaja en ellos.
SiUso de CPU y memoriason altos, es probable que tengas que recurrir a las fórmulas para optimizarlos. Otra indicación de cálculos intensivos de fórmulas sería una desaceleración repentina y masiva después de, por ejemplo, editar una de las primeras celdas de entrada, mientras que es relativamente rápida solo durante la visualización (excepto el tiempo de carga inicial).
Funciones VOLÁTILES(OFFSET, INDIRECT, NOW, RAND, etc.) es la muerte de cualquier hoja de cálculo grande. Puede verificarlos editando una celda solitaria, en blanco y sin referencias en la parte inferior. Si esta edición provoca varios retrasos, es probable que las funciones volátiles sean las culpables. Ah y no lo olvidesFORMATO CONDICIONALES VOLÁTIL también.
Disparadores VOLÁTILESincluir acciones que provocan un recálculo en toda la hoja (de cálculo), como cambiar los filtros automáticos, ajustar el ancho y alto de las celdas, buscar objetivos, ocultar/mostrar/inserciones/eliminaciones/movimientos/cambios de nombre (de celdas y hojas), actualizar conexiones, por ejemplo, a CSV. archivos o bases de datos, cambiando celdas y rangos con nombre. Si alguno de estos también se ejecuta en macros de VBA, lo tienes.
AJUSTES DE RECÁLCULOpuede influir en gran medida en el rendimiento. Si es posible, desactive el recálculo automático y vuelva a calcular manualmente después de las actualizaciones/edición. Evite cálculos iterativos.
Seguimiento de dependientes/antecedentesusando las opciones en la cinta Fórmulas, o usando CTRL+[ y CTRL+]. Esto último es bastante útil ya que puede presionar CTRL+[varias veces y puede determinar los dependientes de los dependientes de los dependientes. En realidad, se seleccionan a medida que avanza (en contraste con las flechas agregadas por los botones en la cinta) y se pueden codificar/etiquetar con colores como desee.
Mostrar o encontrar fórmulas problemáticas.Puede alternar la cinta Fórmulas >Mostrar fórmulas(CTRL+~), para detectar fácilmente fórmulas problemáticas. También puedes hacer un libro de trabajoencontrarpara funciones volátiles, por ejemplo buscar OFFSET.
No olvide los rangos nombrados.Consulte el administrador de nombres para ver los rangos con nombres que contienen funciones volátiles.
IrNO VOLÁTIL. Reemplace INDIRECT con INDEX(..MATCH(..)) y OFFSET con rangos con nombres relativos (como un rango con nombre "CellAbove")
Optimización de la memoria de datos.Reducir elGAMA USADA(no hay filas ni columnas en blanco dentro del rango utilizado). Evite hacer referencias cruzadas a otros libros de trabajo o incluso a otras hojas. Evite almacenar datos como texto. Asegúrese de que sean datos numerados. Evite los números imaginarios, ya que en realidad se almacenan como texto. Si tiene Office 365, use LET para evitar volver a calcular la misma fórmula una y otra vez. Por ejemplo , =IF(LOG(A1)>B1,LOG(A1),B1)puede ser más bien =LET(x, LOG(A1), IF(x > B1, x, B1)). Además, si diferentes celdas hacen la misma parte de una ecuación una y otra vez, es mejor calcularla en una celda separada o definir con nombre.
Optimice sus macros/código VBA/javascript. Desactive el recálculo/actualizaciones de pantalla/eventos al inicio de cada subllamada o llamada de función. Ejecute el resto del código y vuelva a encenderlos.
Usarbloquear lectura y escritura de celdas. Esto es especialmente cierto en código VBA/macro/javascript. El acceso individual a los celulares es lento, especialmente si las actualizaciones de pantalla están activadas. Intente acumular sus respuestas en una matriz y bloquee escríbalas nuevamente en las celdas. En relación con esto, puede usar fórmulas de matriz (y derrames en Excel 365) para hacer el equivalente de bloques de lectura/escritura en fórmulas de Excel. No he verificado si esto es realmente más rápido pero mi instinto dice que sí.
Verifique las conexiones de datos.Algunas conexiones están configuradas para actualizarse automáticamente si abre el libro, lo que puede aumentar drásticamente los tiempos de carga generales, especialmente al actualizar datos en línea/de red.

Question 2

Voy a asumir que el Libro de trabajo usa VBA y publicaré una respuesta basada en esta suposición.

La mayor parte del costo será actualizar la pantalla y/o escribir el archivo en el disco/un recurso compartido de red.

Para actualizar la pantalla, envuelva el módulo que hace el trabajo pesado en

Application.ScreenUpdating = True
    'Do Something
Application.ScreenUpdating = False

Estas configuraciones hacen que parezca que no sucede nada, pero el código subyacente aún se estará ejecutando. Puede agregar algunos debug.printy monitorear su ventana inmediata para confirmar dónde está.

También podrías considerar deshabilitarlo Application.EnableEventsy usarlo solo Application.Calculation = xlAutomaticcuando sea absolutamente necesario.

Con respecto al costo del disco. Le recomiendo (si aún no lo ha hecho) que coloque el archivo en su máquina local y, si es posible, los datos de origen de MatLab. La lectura/escritura en/desde recursos compartidos de red es intrínsecamente lenta en comparación con el disco local.

Answer