Ausführlicher Status des langsamen Excel-Dokuments

Question 1

Um Ihre Frage zu beantworten: Nein, es gibt in Excel keine Leistungsprotokollierung oder Debug-Ausgabe. Sie können die Timerfunktion im VBA-Code ausführen, um die Ausführung verschiedener VBA-Subs oder Funktionen oder Makros zu messen (aber nicht direkte Excel-Neuberechnungen).

Meine Top-Tipps zur Optimierung der Geschwindigkeit Ihrer Excel-Arbeitsmappe:

Vergleichen der Datengröße der Arbeitsmappe(Spalten, Zeilen, Blätter) und Dateigröße. Eine unverhältnismäßig große Datei kann einige versteckte Diagramme oder Zeichnungen enthalten, die große Teile des Speichers beanspruchen. Sie können .xlsx in .zip umbenennen, dann in einen Ordner entpacken und nach der größten Datei suchen, um einen Hinweis zu erhalten.
Als verschiedene Dateitypen speichern, zB alte .xls in neue .xlsx speichern, neue .xlsx in binäre .xlsb. Dies ist besonders hilfreich bei langen Lade- und Speicherzeiten großer Datensätze.
Überprüfen Sie die CPU- und Speicherauslastung. Hoher Speicherverbrauch ohne viel CPU-Bewegung deutet darauf hin, dass es sich meist nur um eine große Datenmenge handelt, die aber nicht wirklich verarbeitungsintensiv ist. Binärformat (.xlsb) in Verbindung mit RAM- (verdoppeln Sie Ihren RAM) und Festplatten- (SSD-)Upgrades können hilfreich sein. Überprüfen Sie auch Ihre Einstellungen für automatisches Speichern/Sicherungskopien, um die Anzahl der Speicherungen/Sicherungen der Dateien während der Arbeit zu reduzieren.
WennCPU- und Speichernutzunghoch sind, müssen Sie wahrscheinlich in die Formeln einsteigen, um sie zu optimieren. Ein weiteres Anzeichen für intensive Formelberechnungen wäre eine plötzliche und massive Verlangsamung, beispielsweise nach dem Bearbeiten einer der frühen Eingabezellen, während sie beim bloßen Anzeigen relativ schnell ist (abgesehen von der anfänglichen Ladezeit).
VOLATILE-Funktionen(OFFSET, INDIRECT, NOW, RAND usw.) ist der Tod jeder großen Tabellenkalkulation. Sie können sie überprüfen, indem Sie eine leere, nicht referenzierte, einsame Zelle unten bearbeiten. Wenn diese Bearbeitung mehrere Verzögerungen auslöst, sind wahrscheinlich volatile Funktionen schuld. Oh, und vergessen Sie nichtBEDINGTE FORMATIERUNGIST auch VOLATIL.
VOLATILE-TriggerDazu gehören Aktionen, die eine (Tabellen-)Blatt-weite Neuberechnung verursachen, wie z. B. das Ändern von Autofiltern, das Anpassen von Zellbreiten und -höhen, das Suchen von Zielen, das Ausblenden/Einblenden/Einfügen/Löschen/Verschieben/Umbenennen (von Zellen und Blättern), das Aktualisieren von Verbindungen zu z. B. CSV-Dateien oder Datenbanken, das Ändern benannter Zellen und Bereiche. Wenn eine dieser Aktionen auch in VBA-Makros ausgeführt wird, haben Sie es verstanden.
Neuberechnungseinstellungenkann die Leistung erheblich beeinflussen. Wenn möglich, schalten Sie die automatische Neuberechnung aus und berechnen Sie nach Aktualisierungen/Bearbeitungen manuell neu. Vermeiden Sie iterative Berechnungen.
Abhängige Objekte/Präzedenzfälle verfolgenmithilfe der Optionen auf der Multifunktionsleiste „Formeln“ oder mithilfe von STRG+[ und STRG+]. Letzteres ist recht nützlich, da Sie STRG+[ mehrmals drücken und abhängige Elemente von abhängigen Elementen von abhängigen Elementen bestimmen können. Sie werden tatsächlich während des Vorgangs ausgewählt (im Gegensatz zu den Pfeilen, die durch die Schaltflächen auf der Multifunktionsleiste hinzugefügt werden) und können nach Belieben farbcodiert/markiert werden.
Zeigen oder finden Sie problematische Formeln.Sie können das Menüband „Formeln“ >Formeln anzeigen(STRG+~), um problematische Formeln leicht zu erkennen. Sie können auch eine Arbeitsmappe weitfindenfür flüchtige Funktionen, zB Suche nach OFFSET.
Vergessen Sie nicht die benannten Bereiche.Suchen Sie im Namensmanager nach benannten Bereichen, die volatile Funktionen enthalten.
GehenNICHT FLÜCHTIG. Ersetzen Sie INDIRECT durch INDEX(..MATCH(..)) und OFFSET durch relative benannte Bereiche (wie z. B. einen benannten Bereich „CellAbove“).
Datenspeicheroptimierung.Reduzieren Sie dieGEBRAUCHTES ANGEBOT(keine leeren Zeilen und Spalten innerhalb Ihres verwendeten Bereichs). Vermeiden Sie Querverweise zu anderen Arbeitsmappen oder sogar anderen Blättern. Vermeiden Sie das Speichern von Daten als Text. Stellen Sie sicher, dass es sich um nummerierte Daten handelt. Vermeiden Sie imaginäre Zahlen, da diese tatsächlich als Text gespeichert werden. Wenn Sie Office 365 haben, verwenden Sie LET, um zu vermeiden, dass Sie denselben Teil einer Formel immer wieder neu berechnen müssen. Beispielsweise =IF(LOG(A1)>B1,LOG(A1),B1)kann eher sein =LET(x, LOG(A1), IF(x > B1, x, B1)). Wenn verschiedene Zellen denselben Teil einer Gleichung immer wieder ausführen, berechnen Sie dies lieber in einer separaten Zelle oder definieren Sie eine benannte.
Optimieren Sie Ihre Makros/VBA-Code/Javascript. Schalten Sie Neuberechnungen/Bildschirmaktualisierungen/Ereignisse zu Beginn jedes Sub- oder Funktionsaufrufs aus. Führen Sie den Rest des Codes aus und schalten Sie diese wieder ein.
VerwendenBlockzellen lesen und schreiben. Dies gilt insbesondere für VBA-/Makro-/JavaScript-Code. Der Zugriff auf einzelne Zellen ist langsam, insbesondere wenn Bildschirmaktualisierungen aktiviert sind. Versuchen Sie, Ihre Antworten in einem Array zu sammeln und dieses blockweise in die Zellen zurückzuschreiben. In diesem Zusammenhang können Sie Array-Formeln (und Spilling in Excel 365) verwenden, um das Äquivalent von Block-R/W-Vorgängen in Excel-Formeln durchzuführen. Ich habe nicht überprüft, ob dies tatsächlich schneller ist, aber mein Bauchgefühl sagt ja.
Überprüfen Sie die Datenverbindungen.Einige Verbindungen sind so eingestellt, dass sie beim Öffnen der Arbeitsmappe automatisch aktualisiert werden. Dies kann die Gesamtladezeiten erheblich verlängern, insbesondere beim Aktualisieren von Online-/Netzwerkdaten.

Answer

Um Ihre Frage zu beantworten: Nein, es gibt in Excel keine Leistungsprotokollierung oder Debug-Ausgabe. Sie können die Timerfunktion im VBA-Code ausführen, um die Ausführung verschiedener VBA-Subs oder Funktionen oder Makros zu messen (aber nicht direkte Excel-Neuberechnungen).

Meine Top-Tipps zur Optimierung der Geschwindigkeit Ihrer Excel-Arbeitsmappe:

Vergleichen der Datengröße der Arbeitsmappe(Spalten, Zeilen, Blätter) und Dateigröße. Eine unverhältnismäßig große Datei kann einige versteckte Diagramme oder Zeichnungen enthalten, die große Teile des Speichers beanspruchen. Sie können .xlsx in .zip umbenennen, dann in einen Ordner entpacken und nach der größten Datei suchen, um einen Hinweis zu erhalten.
Als verschiedene Dateitypen speichern, zB alte .xls in neue .xlsx speichern, neue .xlsx in binäre .xlsb. Dies ist besonders hilfreich bei langen Lade- und Speicherzeiten großer Datensätze.
Überprüfen Sie die CPU- und Speicherauslastung. Hoher Speicherverbrauch ohne viel CPU-Bewegung deutet darauf hin, dass es sich meist nur um eine große Datenmenge handelt, die aber nicht wirklich verarbeitungsintensiv ist. Binärformat (.xlsb) in Verbindung mit RAM- (verdoppeln Sie Ihren RAM) und Festplatten- (SSD-)Upgrades können hilfreich sein. Überprüfen Sie auch Ihre Einstellungen für automatisches Speichern/Sicherungskopien, um die Anzahl der Speicherungen/Sicherungen der Dateien während der Arbeit zu reduzieren.
WennCPU- und Speichernutzunghoch sind, müssen Sie wahrscheinlich in die Formeln einsteigen, um sie zu optimieren. Ein weiteres Anzeichen für intensive Formelberechnungen wäre eine plötzliche und massive Verlangsamung, beispielsweise nach dem Bearbeiten einer der frühen Eingabezellen, während sie beim bloßen Anzeigen relativ schnell ist (abgesehen von der anfänglichen Ladezeit).
VOLATILE-Funktionen(OFFSET, INDIRECT, NOW, RAND usw.) ist der Tod jeder großen Tabellenkalkulation. Sie können sie überprüfen, indem Sie eine leere, nicht referenzierte, einsame Zelle unten bearbeiten. Wenn diese Bearbeitung mehrere Verzögerungen auslöst, sind wahrscheinlich volatile Funktionen schuld. Oh, und vergessen Sie nichtBEDINGTE FORMATIERUNGIST auch VOLATIL.
VOLATILE-TriggerDazu gehören Aktionen, die eine (Tabellen-)Blatt-weite Neuberechnung verursachen, wie z. B. das Ändern von Autofiltern, das Anpassen von Zellbreiten und -höhen, das Suchen von Zielen, das Ausblenden/Einblenden/Einfügen/Löschen/Verschieben/Umbenennen (von Zellen und Blättern), das Aktualisieren von Verbindungen zu z. B. CSV-Dateien oder Datenbanken, das Ändern benannter Zellen und Bereiche. Wenn eine dieser Aktionen auch in VBA-Makros ausgeführt wird, haben Sie es verstanden.
Neuberechnungseinstellungenkann die Leistung erheblich beeinflussen. Wenn möglich, schalten Sie die automatische Neuberechnung aus und berechnen Sie nach Aktualisierungen/Bearbeitungen manuell neu. Vermeiden Sie iterative Berechnungen.
Abhängige Objekte/Präzedenzfälle verfolgenmithilfe der Optionen auf der Multifunktionsleiste „Formeln“ oder mithilfe von STRG+[ und STRG+]. Letzteres ist recht nützlich, da Sie STRG+[ mehrmals drücken und abhängige Elemente von abhängigen Elementen von abhängigen Elementen bestimmen können. Sie werden tatsächlich während des Vorgangs ausgewählt (im Gegensatz zu den Pfeilen, die durch die Schaltflächen auf der Multifunktionsleiste hinzugefügt werden) und können nach Belieben farbcodiert/markiert werden.
Zeigen oder finden Sie problematische Formeln.Sie können das Menüband „Formeln“ >Formeln anzeigen(STRG+~), um problematische Formeln leicht zu erkennen. Sie können auch eine Arbeitsmappe weitfindenfür flüchtige Funktionen, zB Suche nach OFFSET.
Vergessen Sie nicht die benannten Bereiche.Suchen Sie im Namensmanager nach benannten Bereichen, die volatile Funktionen enthalten.
GehenNICHT FLÜCHTIG. Ersetzen Sie INDIRECT durch INDEX(..MATCH(..)) und OFFSET durch relative benannte Bereiche (wie z. B. einen benannten Bereich „CellAbove“).
Datenspeicheroptimierung.Reduzieren Sie dieGEBRAUCHTES ANGEBOT(keine leeren Zeilen und Spalten innerhalb Ihres verwendeten Bereichs). Vermeiden Sie Querverweise zu anderen Arbeitsmappen oder sogar anderen Blättern. Vermeiden Sie das Speichern von Daten als Text. Stellen Sie sicher, dass es sich um nummerierte Daten handelt. Vermeiden Sie imaginäre Zahlen, da diese tatsächlich als Text gespeichert werden. Wenn Sie Office 365 haben, verwenden Sie LET, um zu vermeiden, dass Sie denselben Teil einer Formel immer wieder neu berechnen müssen. Beispielsweise =IF(LOG(A1)>B1,LOG(A1),B1)kann eher sein =LET(x, LOG(A1), IF(x > B1, x, B1)). Wenn verschiedene Zellen denselben Teil einer Gleichung immer wieder ausführen, berechnen Sie dies lieber in einer separaten Zelle oder definieren Sie eine benannte.
Optimieren Sie Ihre Makros/VBA-Code/Javascript. Schalten Sie Neuberechnungen/Bildschirmaktualisierungen/Ereignisse zu Beginn jedes Sub- oder Funktionsaufrufs aus. Führen Sie den Rest des Codes aus und schalten Sie diese wieder ein.
VerwendenBlockzellen lesen und schreiben. Dies gilt insbesondere für VBA-/Makro-/JavaScript-Code. Der Zugriff auf einzelne Zellen ist langsam, insbesondere wenn Bildschirmaktualisierungen aktiviert sind. Versuchen Sie, Ihre Antworten in einem Array zu sammeln und dieses blockweise in die Zellen zurückzuschreiben. In diesem Zusammenhang können Sie Array-Formeln (und Spilling in Excel 365) verwenden, um das Äquivalent von Block-R/W-Vorgängen in Excel-Formeln durchzuführen. Ich habe nicht überprüft, ob dies tatsächlich schneller ist, aber mein Bauchgefühl sagt ja.
Überprüfen Sie die Datenverbindungen.Einige Verbindungen sind so eingestellt, dass sie beim Öffnen der Arbeitsmappe automatisch aktualisiert werden. Dies kann die Gesamtladezeiten erheblich verlängern, insbesondere beim Aktualisieren von Online-/Netzwerkdaten.

Question 2

Ich gehe davon aus, dass das Arbeitsbuch VBA verwendet, und poste eine Antwort, die auf dieser Annahme basiert.

Der Großteil der Kosten entsteht durch das Aktualisieren des Bildschirms und/oder das Schreiben der Datei auf die Festplatte/eine Netzwerkfreigabe.

Für die Bildschirmaktualisierung packen Sie das Modul, das die Hauptarbeit erledigt, in

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

Mit diesen Einstellungen sieht es so aus, als ob nichts passiert, aber der Code dahinter wird trotzdem ausgeführt. Sie können einige debug.print's hinzufügen und Ihr unmittelbares Fenster überwachen, um zu bestätigen, wo Sie gerade sind.

Sie können auch die Deaktivierung in Betracht ziehen Application.EnableEventsund die Verwendung nur dort vornehmen Application.Calculation = xlAutomatic, wo es unbedingt nötig ist.

In Bezug auf die Festplattenkosten. Ich würde Ihnen empfehlen (falls Sie das nicht bereits tun), die Datei auf Ihrem lokalen Computer zu speichern, und wenn möglich auch die MatLab-Quelldaten. Das Lesen/Schreiben von/auf Netzwerkfreigaben ist im Vergleich zur lokalen Festplatte von Natur aus langsam.

Answer