Excel-Leistung: Tipps für das Entfernen von Leistungshindernissen

Artikel
04/07/2023

Gilt für: Excel | Excel 2013 | Office 2016 | VBA

Befolgen Sie diese Tipps, um viele häufig auftretende Leistungshindernisse in Excel zu optimieren.

Optimieren von Verweisen und Links

Erfahren Sie, wie Sie die Leistung im Zusammenhang mit Verweis- und Linktypen verbessern.

Verwenden Sie keine Vorwärtsvreferenzierung und keine Rückwärtsreferenzierung.

Um die Übersichtlichkeit zu erhöhen und Fehler zu vermeiden, entwerfen Sie Ihre Formeln so, dass sie nicht vorwärts (rechts oder unten) auf andere Formeln oder Zellen verweisen. Vorwärtsverweise wirken sich in der Regel nicht auf die Berechnungsleistung aus, außer in Extremfällen für die erste Berechnung einer Arbeitsmappe, bei der es länger dauern kann, eine vernünftige Berechnungssequenz einzurichten, wenn viele Formeln vorhanden sind, deren Berechnung verzögert werden muss.

Minimieren der Verwendung von Zirkelbezügen mit Iteration

Das Berechnen von Zirkelbezügen mit Iterationen ist langsam, da mehrere Berechnungen erforderlich sind und diese Berechnungen singlethreaded sind. Häufig können Sie die Zirkelbezüge mithilfe von Algebra "entrollen", sodass keine iterative Berechnung mehr erforderlich ist. Versuchen Sie beispielsweise bei Cashflow- und Zinsberechnungen, den Cashflow vor Zinsen zu berechnen, die Zinsen zu berechnen und dann den Cashflow einschließlich der Zinsen zu berechnen.

Excel berechnet Zirkelverweise Blatt für Blatt, ohne Abhängigkeiten zu berücksichtigen. Demzufolge erfolgt die Berechnung meist langsam, wenn sich Ihre Zirkelverweise über mehrere Arbeitsblätter erstrecken. Versuchen Sie, die Zirkelverweise auf ein einzelnes Arbeitsblatt zu verschieben, oder optimieren Sie die Arbeitsblattberechnungsfolge zum Vermeiden unnötiger Berechnungen.

Vor Beginn der iterativen Berechnungen muss Excel die Arbeitsmappe neu berechnen, um alle Zirkelverweise und deren Abhängige zu bestimmen. Dieser Vorgang entspricht zwei oder drei Iterationen der Berechnung.

Nach Bestimmen der Zirkelverweise und Abhängigen erfordert jede Iteration, dass Excel nicht nur alle Zellen im Zirkelverweis, sondern auch sämtliche Zellen, die von den Zellen in der Zirkelverweiskette abhängen, sowie veränderliche Zellen und deren Abhängige berechnet. Wenn Sie eine komplexe Berechnung haben, die von Zellen im Zirkelverweis abhängt, kann es schneller sein, diesen in einer gesonderten geschlossenen Arbeitsmappe zu isolieren und für die Neuberechnung zu öffnen, nachdem die Zirkelberechnung konvergiert ist.

Es ist wichtig, die Anzahl der Zellen in der zirkulären Berechnung und die Berechnungszeit zu reduzieren, die von diesen Zellen in Anspruch genommen wird.

Vermeiden von Verknüpfungen zwischen Arbeitsmappen

Vermeiden Sie Arbeitsmappenverknüpfung zwischen Arbeitsmappen, wenn dies möglich ist. sie können langsam, leicht beschädigt und nicht immer leicht zu finden und zu beheben.

Das Arbeiten mit wenigen größeren Arbeitsmappen ist normalerweise, aber nicht immer, besser als das Arbeiten mit vielen kleineren Arbeitsmappen. Einige Ausnahmen können sein, wenn Sie viele Front-End-Berechnungen haben, die so selten neu berechnet werden, dass es sinnvoll ist, sie in einer separaten Arbeitsmappe zu platzieren, oder wenn Sie nicht über genügend ARBEITSSPEICHER verfügen.

Arbeiten Sie am besten mit einfachen direkten Zellbezügen, die bei geschlossenen Arbeitsmappen funktionieren. Auf diese Weise können Sie die Neuberechnung aller Ihrer verknüpften Arbeitsmappen vermeiden, wenn Sie eine beliebige Arbeitsmappe neu berechnen. Außerdem können Sie die Werte sehen, die Excel aus der geschlossenen Arbeitsmappe gelesen hat. Dies ist häufig wichtig für das Debuggen und Überwachen der Arbeitsmappe.

Wenn verknüpfte Arbeitsmappen nicht vermieden werden können, öffnen Sie alle, anstatt sie geschlossen zu halten, und öffnen Sie die Arbeitsmappen, die die Verknüpfung herstellen, bevor Sie die Arbeitsmappen öffnen, mit denen die Verknüpfung hergestellt wird.

Minimieren von Links zwischen Arbeitsblättern

Das Verwenden zahlreicher Arbeitsblätter kann die Verwendung Ihrer Arbeitsmappe vereinfachen, doch im Allgemeinen erfolgt die Berechnung von Verweisen auf andere Arbeitsblätter langsamer als die von Verweisen innerhalb von Arbeitsblättern.

Minimieren des verwendeten Bereichs

Um Arbeitsspeicher zu sparen und die Dateigröße zu reduzieren, versucht Excel, nur Informationen zu dem Bereich auf einem Arbeitsblatt zu speichern, der verwendet wurde. Dieser Bereich wird als verwendeter Bereich bezeichnet. Mitunter wird bei verschiedenen Bearbeitungs- und Formatierungsvorgängen der verwendete Bereich beträchtlich über den Bereich hinaus ausgedehnt, der eigentlich als verwendet betrachtet wird. Dies kann Hindernisse bei Leistung und Dateigröße verursachen.

Sie können den sichtbaren verwendeten Bereich auf einem Arbeitsblatt mit STRG+Ende überprüfen. Wenn dies zu groß ist, sollten Sie erwägen, alle Zeilen und Spalten unterhalb und rechts neben der tatsächlichen zuletzt verwendeten Zelle zu löschen und dann die Arbeitsmappe zu speichern. Erstellen Sie zuerst eine Sicherungskopie. Wenn Sie Über Formeln mit Bereichen verfügen, die sich in den gelöschten Bereich erstrecken oder auf diesen verweisen, werden diese Bereiche verkleinert oder in #N/A geändert.

Zusätzliche Daten zulassen

Wenn Sie Ihren Arbeitsblättern häufig Datenzeilen oder -spalten hinzufügen, benötigen Sie eine Möglichkeit, über die Ihre Excel-Formeln automatisch auf den neuen Datenbereich verweisen, anstatt die Formeln jedes Mal suchen und ändern zu müssen.

Verwenden Sie hierzu einen großen Bereich in Ihren Formeln, der relativ weit über die aktuellen Datengrenzen hinausgeht. Dies kann jedoch unter bestimmten Umständen für eine ineffiziente Berechnung und schwierige Datenpflege sorgen, da das Löschen von Zeilen und Spalten den Bereich verkleinern kann, ohne dass Sie es bemerken.

Verwenden strukturierter Tabellenverweise (empfohlen)

Ab Excel 2007 können Sie strukturierte Tabellenverweise verwenden, die automatisch erweitert und verkleinert werden, wenn die Größe der Tabelle, auf die verwiesen wird, zunimmt oder abnimmt.

Diese Lösung hat mehrere Vorteile:

Es gibt weniger Leistungsnachteile als die Alternativen zum Verweisen auf ganze Spalten und dynamische Bereiche.
Es ist einfach, mehrere Datentabellen auf einem einzelnen Arbeitsblatt zu haben.
Formeln, die in die Tabelle eingebettet sind, werden auch entsprechend den Daten vergrößert bzw. verkleinert.

Alternativ können Sie verweise auf ganze Spalten und Zeilen verwenden.

Ein alternativer Ansatz besteht darin, einen Verweis auf eine ganze Spalte zu verwenden, z. B. $A:$A. Dieser Verweis gibt alle Zeilen in Spalte A zurück. Daher können Sie so viele Daten hinzufügen, wie Sie möchten, und der Verweis enthält sie immer.

Diese Lösung hat sowohl Vor- als auch Nachteile:

Viele vordefinierte Excel-Funktionen (SUM, SUMIF) berechnen Verweise auf ganze Spalten effizient, da sie automatisch die letzte verwendete Spalte in der Zeile erkennen. Arrayberechnungsfunktionen wie SUMPRODUCT können entweder keine Verweise auf ganze Spalten verarbeiten oder berechnen alle Zellen in der Spalte.
Benutzerdefinierte Funktionen erkennen nicht automatisch die zuletzt verwendete Zeile in der Spalte und berechnen daher häufig verweise auf ganze Spalten ineffizient. Es ist jedoch einfach, benutzerdefinierte Funktionen so zu programmieren, dass die letzte verwendete Zeile erkannt wird.
Es ist schwierig, Verweise auf ganze Spalten zu verwenden, wenn sie mehrere Tabellen mit Daten in einem einzelnen Arbeitsblatt haben.
In Excel 2007 und höheren Versionen können Arrayformeln verweise auf ganze Spalten verarbeiten, dies erzwingt jedoch die Berechnung für alle Zellen in der Spalte, einschließlich leerer Zellen. Dies kann langsam berechnet werden, insbesondere für 1 Million Zeilen.

Alternativ können Sie dynamische Bereiche verwenden.

Mithilfe der Funktionen OFFSET oder INDEX und COUNTA in der Definition eines benannten Bereichs können Sie den Bereich, auf den der benannte Bereich verweist, dynamisch erweitern und kontrahieren. Erstellen Sie beispielsweise einen definierten Namen mit einer der folgenden Formeln:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

Wenn Sie den Namen des dynamischen Bereichs in einer Formel verwenden, wird dieser automatisch zum Aufnehmen neuer Einträge vergrößert.

Die Verwendung der INDEX-Formel für einen dynamischen Bereich ist der OFFSET-Formel im Allgemeinen vorzuziehen, da OFFSET den Nachteil hat, dass es sich um eine flüchtige Funktion handelt, die bei jeder Neuberechnung berechnet wird.

Die Leistung nimmt ab, da die COUNTA-Funktion innerhalb der Dynamischen Bereichsformel viele Zeilen untersuchen muss. Sie können diese Leistungsminderung minimieren, indem Sie den COUNTA-Teil der Formel in einer separaten Zelle oder einem definierten Namen speichern und dann auf die Zelle oder den Namen im dynamischen Bereich verweisen:

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

Sie können auch Funktionen wie INDIRECT verwenden, um dynamische Bereiche zu erstellen, aber INDIRECT ist flüchtig und berechnet immer Singlethreads.

Dynamische Bereiche haben die folgenden Vor- und Nachteile:

Dynamische Bereiche eignen sich gut zum Einschränken der Anzahl der Berechnungen, die von Arrayformeln durchgeführt werden.
Die Verwendung mehrerer dynamischer Bereiche innerhalb einer einzelnen Spalte erfordert spezielle Zählfunktionen.
Das Arbeiten mit vielen dynamischen Bereichen kann die Leistung verringern.

Verbessern der Nachschlageberechnungszeit

In Office 365 Version 1809 und höher werden die Excel-Funktionen SVERWEIS, WVERWEIS und VERGLEICH zur Ermittlung genauer Übereinstimmungen in nicht sortierten Daten jetzt wesentlich schneller als je zuvor ausgeführt, wenn mehrere Spalten (oder Zeilen mit WVERWEIS) aus demselben Tabellenbereich durchsucht werden.

Für frühere Excel-Versionen stellen Lookups jedoch weiterhin häufig erhebliche Berechnungshindernisse dar. Glücklicherweise gibt es zahlreiche Möglichkeiten zum Beschleunigen der Berechnungszeit von Nachschlagevorgängen. Bei Wahl der Option Genaue Übereinstimmung entspricht die Berechnungszeit für die Funktion proportional der Anzahl der Zellen, die durchsucht wurden, bevor eine Übereinstimmung gefunden wird. Bei Nachschlagevorgängen in großen Bereichen kann dieser Zeitraum beträchtlich sein.

Die Nachschlagezeit bei Verwenden der Optionen für eine ungefähre Übereinstimmung von VLOOKUP, HLOOKUP und MATCH bei sortierten Daten ist kurz und verlängert sich nicht wesentlich durch die Länge des Bereichs, der durchsucht wird. Die Merkmale entsprechen denen der binären Suche.

Grundlegendes zu Nachschlageoptionen

Stellen Sie sicher, dass Sie die Optionen match-type und range-lookup in MATCH, VLOOKUP und HLOOKUP verstehen.

Das folgende Codebeispiel zeigt die Syntax der MATCH-Funktion. Weitere Informationen finden Sie unter der Match-Methode des WorksheetFunction-Objekts.

  MATCH(lookup value, lookup array, matchtype)

Matchtype=1 gibt die größte Übereinstimmung zurück, die kleiner oder gleich dem Nachschlagewert ist, wenn das Sucharray aufsteigend sortiert ist (ungefähre Übereinstimmung). Wenn das Nachschlagearray nicht aufsteigend sortiert ist, gibt MATCH eine falsche Antwort zurück. Die Standardoption ist aufsteigend sortierte ungefähre Übereinstimmung.
matchtype=0 fordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.
matchtype=-1 gibt die größte Übereinstimmung größer gleich dem Nachschlagewert zurück, wenn das Nachschlagearray absteigend sortiert ist (ungefähre Übereinstimmung).

Das folgende Codebeispiel zeigt die Syntax der Funktionen VLOOKUP und HLOOKUP. Weitere Informationen finden Sie unter den Methoden VLOOKUP (SVERWEIS) und HLOOKUP (WVERWEIS) des WorksheetFunction-Objekts.

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

range-lookup=TRUE gibt die größte Übereinstimmung kleiner gleich dem Nachschlagewert (ungefähre Übereinstimmung) zurück. Dies ist die Standardoption. Das Tabellenarray muss aufsteigend sortiert sein.
range-lookup=FALSE fordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.

Vermeiden Sie möglichst das Anwenden von Nachschlagevorgängen auf unsortierte Daten, da es langsam ist. Wenn Ihre Daten sortiert sind, Sie aber eine genaue Übereinstimmung wünschen, lesen Sie Verwenden von zwei Nachschlagevorgängen für sortierte Daten mit fehlenden Werten.

Verwenden Von INDEX und MATCH oder OFFSET anstelle von SVERWEIS

Versuchen Sie, die Funktionen INDEX und MATCH anstelle von SVERWEIS zu verwenden. Obwohl SVERWEIS etwas schneller ist (etwa 5 Prozent schneller), einfacher ist und weniger Arbeitsspeicher verwendet als eine Kombination aus MATCH und INDEX oder OFFSET, können Sie durch die zusätzliche Flexibilität, die MATCH und INDEX bieten, häufig erheblich Zeit sparen. Beispielsweise können Sie das Ergebnis einer genauen ÜBEREINSTIMMUNG in einer Zelle speichern und in mehreren INDEX-Anweisungen wiederverwenden.

Die INDEX-Funktion ist schnell und unveränderlich, wodurch die Neuberechnung beschleunigt wird. Die OFFSET-Funktion ist ebenfalls schnell; Es handelt sich jedoch um eine veränderliche Funktion, die manchmal die Zeit für die Verarbeitung der Berechnungskette erheblich verlängert.

Es ist einfach, SVERWEIS in INDEX und VERGLEICH zu konvertieren. Die folgenden beiden Anweisungen geben dieselbe Antwort zurück:

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

Beschleunigen von Suchvorgängen

Da Nachschlagevorgänge nach genauen Übereinstimmungen langsam sein können, erwägen Sie die folgenden Optionen zur Verbesserung der Leistung:

Verwenden Sie ein Arbeitsblatt. Es ist schneller, Nachschlagevorgänge und Daten auf demselben Blatt zu speichern.
Wenden Sie möglichst zuerst die SORT-Funktion auf die Daten an (SORT ist schnell), und suchen Sie nach ungefähren Übereinstimmungen.
Wenn Sie mit einem Nachschlagevorgang nach einer genauen Überstimmung arbeiten müssen, beschränken Sie den Bereich der zu durchsuchenden Zellen auf ein Minimum. Verwenden Sie Tabellen und strukturierte Verweise oder dynamische Bereichsnamen, anstatt auf eine große Anzahl von Zeilen oder Spalten zu verweisen. Mitunter können Sie einen Grenzwert für den oberen und unteren Bereich für den Nachschlagevorgang vorab berechnen.

Verwenden von zwei Nachschlagevorgängen für sortierte Daten mit fehlenden Werten

Zwei ungefähre Übereinstimmungen sind bei einer Suche über mehr als nur einige wenige Zeilen wesentlich schneller als eine genaue Übereinstimmung zu finden. (Die Grenze liegt bei etwa 10-20 Zeilen.)

Wenn Sie Ihre Daten sortieren können, aber trotzdem keine ungefähre Übereinstimmung verwenden können, weil Sie nicht sicher sein können, dass der gesuchte Wert im Nachschlagebereich vorhanden ist, können Sie diese Formel verwenden:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

Im ersten Teil der Formel wird eine Suche nach einer ungefähren Übereinstimmung in der Nachschlagespalte selbst durchgeführt.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Sie können mithilfe der folgenden Formel überprüfen, ob die Antwort aus der Nachschlagespalte mit dem Nachschlagewert übereinstimmt (in diesem Fall haben Sie eine genaue Übereinstimmung):

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

Wenn diese Formel True zurückgibt, haben Sie eine genaue Übereinstimmung gefunden, sodass Sie die ungefähre Suche erneut durchführen können, aber dieses Mal geben Sie die Antwort aus der gewünschten Spalte zurück.

  VLOOKUP(lookup_val, lookup_array, column, True)

Wenn die Antwort aus der Nachschlagespalte nicht mit dem Nachschlagewert übereinstimmt, fehlt ein Wert, und die Formel gibt "notexist" zurück.

Wenn Sie einen Wert nachschlagen, der kleiner als der kleinste Wert in der Liste ist, erhalten Sie eine Fehlermeldung. Sie können diesen Fehler vermeiden, indem Sie IFERROR verwenden oder der Liste einen kleinen Testwert hinzufügen.

Verwenden der IFERROR-Funktion für unsortierte Daten mit fehlenden Werten

Wenn Sie die genaue Übereinstimmungssuche für unsortierte Daten verwenden müssen und Sie nicht sicher sein können, ob der Nachschlagewert vorhanden ist, müssen Sie häufig die #N/A behandeln, die zurückgegeben wird, wenn keine Übereinstimmung gefunden wird. Ab Excel 2007 können Sie die FUNKTION IFERROR verwenden, die sowohl einfach als auch schnell ist.

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

In früheren Versionen ist eine einfache, aber langsame Möglichkeit, das Verwenden einer IF-Funktion mit zwei Nachschlagevorgängen.

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

Sie können den doppelten Nachschlagevorgang nach einer genauen Übereinstimmung vermeiden, wenn Sie die MATCH-Funktion für eine genaue Übereinstimmung einmal verwenden, das Ergebnis in einer Zelle speichern und anschließend das Ergebnis testen, bevor Sie die INDEX-Funktion ausführen.

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

Wenn Sie keine zwei Zellen verwenden können, verwenden Sie COUNTIF. Dies ist in der Regel schneller als eine exakte Übereinstimmungssuche.

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

Verwenden von MATCH und INDEX für Suche nach exakten Übereinstimmungen in mehreren Spalten

Sie können den gespeicherten Wert einer MATCH-Funktion zum Auffinden einer genauen Übereinstimmung mehrmals wiederverwenden. Wenn Sie solche Nachschlagevorgänge auf mehrere Ergebnisspalten anwenden, können Sie Zeit sparen, indem Sie eine MATCH-Funktion und zahlreiche INDEX-Anweisungen anstelle vieler VLOOKUP-Anweisungen verwenden.

Fügen Sie eine zusätzliche Spalte für match hinzu , um das Ergebnis zu speichern (stored_row), und verwenden Sie für jede Ergebnisspalte Folgendes:

  INDEX(Lookup_Range,stored_row,column_number)

Alternativ können Sie die VLOOKUP-Funktion in einer Arrayformel verwenden. (Arrayformeln müssen mit STRG+UMSCHALT+EINGABETASTE eingegeben werden. Excel fügt die Werte {und } hinzu, um ihnen zu zeigen, dass es sich um eine Arrayformel handelt).)

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

Verwenden von INDEX für eine Reihe zusammenhängender Zeilen oder Spalten

Sie können auch mithilfe eines einzelnen Nachschlagevorgangs viele Zeilen zurückgeben. Zum Nachschlagen mehrerer zusammenhängender Spalten können Sie die INDEX-Funktion in einer Arrayformel verwenden, um mehrere Spalten auf einmal zurückzugeben (wählen Sie 0 als Spaltennummer). Mithilfe der INDEX-Funktion können Sie auch mehrere Zeilen auf einmal zurückgeben.

  {INDEX($A$1:$J$1000,stored_row,0)}

Hiermit wird Spalte A bis Spalte J aus der gespeicherten Zeile zurückgegeben, die von einer vorherigen MATCH-Anweisung erstellt wurde.

Verwenden von MATCH zum Zurückgeben eines rechteckigen Zellblocks

Verwenden Sie die Funktionen MATCH und OFFSET , um einen rechteckigen Zellblock zurückzugeben.

Verwenden von MATCH und INDEX für zweidimensionale Suche

Sie können eine zweidimensionale Tabellensuche effizient durchführen, indem Sie separate Nachschlagevorgänge für die Zeilen und Spalten einer Tabelle verwenden, indem Sie eine INDEX-Funktion mit zwei eingebetteten MATCH-Funktionen verwenden, eine für die Zeile und eine für die Spalte.

Verwenden eines Teilmengenbereichs für suche mit mehreren Indexen

In großen Arbeitsblättern müssen Sie möglicherweise häufig anhand mehrerer Indizes nachschlagen, z. B. nach Produktvolumen in einem Land/einer Region. Dazu können Sie die Indizes verketten und die Suche mithilfe verketteter Nachschlagewerte durchführen. Dies ist jedoch aus zwei Gründen ineffizient:

Das Verketten von Zeichenfolgen ist ein berechnungsintensiver Vorgang.
Der Nachschlagevorgang bezieht sich auf einen großen Bereich.

Es ist oft effizienter, einen Teilmengenbereich für die Suche zu berechnen (z. B. durch Suchen der ersten und letzten Zeile für das Land/die Region und anschließendes Nachschlagen des Produkts innerhalb dieses Teilmengenbereichs).

Erwägen von Optionen für die dreidimensionale Suche

Zum Nachschlagen der zu verwendenden Tabelle zusätzlich zu der Zeile und der Spalte können Sie die folgenden Verfahren mit dem Schwerpunkt darauf verwenden, wie Excel zum Nachschlagen oder Auswählen der Tabelle veranlasst werden kann.

Wenn jede Tabelle, die Sie suchen möchten (die dritte Dimension), als Gruppe benannter strukturierter Tabellen, Bereichsnamen oder als Tabelle mit Textzeichenfolgen gespeichert wird, die Bereiche darstellen, können Sie möglicherweise die Funktionen CHOOSE oder INDIRECT verwenden.

Das Verwenden von CHOOSE und Bereichsnamen kann eine effiziente Methode sein. CHOOSE ist nicht veränderlich, eignet sich jedoch am besten für eine relativ kleine Anzahl von Tabellen. In diesem Beispiel wird dynamisch verwendet TableLookup_Value , um den Bereichsnamen (TableName1, TableName2, ...) auszuwählen, der für die Nachschlagetabelle verwendet werden soll.
```
  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
Im folgenden Beispiel wird die INDIRECT-Funktion und TableLookup_Value verwendet, um den Blattnamen dynamisch zu erstellen, der für die Nachschlagetabelle verwendet werden soll. Diese Methode hat den Vorteil, dass sie einfach ist und damit eine große Anzahl von Tabellen verarbeitet werden kann. Da INDIRECT eine flüchtige Singlethreadfunktion ist, wird die Suche bei jeder Berechnung mit einem einzelnen Thread berechnet, auch wenn sich keine Daten geändert haben. Die Verwendung dieser Methode ist langsam.
```
  INDEX(INDIRECT("Sheet" &amp; TableLookup_Value &amp; "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
Sie können auch die VLOOKUP-Funktion zum Auffinden des Blattnamens oder der Textzeichenfolge nutzen, der/das für die Tabelle verwendet werden soll, und anschließend die INDIRECT-Funktion zum Umwandeln des resultierenden Texts in einen Bereich verwenden.
```
  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```

Ein weiteres Verfahren ist das Zusammenfassen aller Ihrer Tabellen in einer Riesentabelle, die eine zusätzliche Spalte enthält, in der die einzelnen Tabellen identifiziert werden. Sie können anschließend die Verfahren für Nachschlagevorgänge unter Verwendung mehrerer Indizes aus den vorherigen Beispielen anwenden.

Verwenden von Wildcard-Nachschlagevorgängen

Mit den Funktionen MATCH, SLOOKUP und HLOOKUP können Sie die Platzhalterzeichen ? (ein beliebiges einzelnes Zeichen) und * (kein Zeichen oder eine beliebige Anzahl von Zeichen) für alphabetische exakte Übereinstimmungen verwenden. Mitunter können Sie diese Methode verwenden, um mehrere Übereinstimmungen zu vermeiden.

Optimieren von Arrayformeln und SUMMENPRODUKT

Arrayformeln und die SUMPRODUCT-Funktion sind leistungsstark, aber Sie müssen sie sorgfältig behandeln. Eine einzelne Arrayformel kann viele Berechnungen erfordern.

Der Schlüssel zur Beschleunigung der Berechnung von Arrayformeln ist das Sicherstellen, dass die Anzahl der in der Arrayformel ausgewerteten Zellen und Ausdrücke so klein wie möglich ist. Denken Sie daran, dass eine Arrayformel ein wenig wie eine flüchtige Formel ist: Wenn eine der Zellen, auf die sie verweist, geändert wurde, flüchtig ist oder neu berechnet wurde, berechnet die Arrayformel alle Zellen in der Formel und wertet alle virtuellen Zellen aus, die sie für die Berechnung benötigt.

So beschleunigen Sie die Berechnung von Arrayformeln:

Verschieben Sie Ausdrücke und Bereichsbezüge aus den Arrayformeln in gesonderte Hilfsspalten und -zeilen. Auf diese Weise kann der intelligente Neuberechnungsprozess in Excel wesentlich besser zum Tragen kommen.
Verweisen Sie nicht auf vollständige Zeilen oder mehr Zeilen und Spalten, als Sie benötigen. Arrayformeln sind gezwungen, alle Zellbezüge in der Formel zu berechnen, auch wenn die Zellen leer oder nicht verwendet werden. Da ab Excel 2007 1 Million Zeilen verfügbar sind, ist die Berechnung einer Arrayformel, die auf eine ganze Spalte verweist, extrem langsam.
Ab Excel 2007 können strukturierte Verweise genutzt werden, bei denen Sie die Anzahl der Zellen, die von der Arrayformel ausgewertet werden, auf ein Minimum begrenzen können.
Verwenden Sie in Früheren Versionen als Excel 2007 nach Möglichkeit dynamische Bereichsnamen. Wenngleich diese veränderlich sind, lohnt sich ihr Einsatz, da sie die Größe von Bereichen minimieren.
Seien Sie vorsichtig bei Arrayformeln, die sowohl auf eine Zeile als auch eine Spalte verweisen, da dadurch die Berechnung eines rechteckigen Bereichs erzwungen wird.
Verwenden Sie möglichst SUMPRODUCT, da diese Funktion schneller als die entsprechende Arrayformel ist.

Erwägen von Optionen für die Verwendung von SUMME für Arrayformeln mit mehreren Bedingungen

Sie sollten stets nach Möglichkeit die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS anstatt Arrayformeln verwenden, da deren Berechnung wesentlich schneller erfolgt. Excel 2016 führt schnelle MAXIFS- und MINIFS-Funktionen ein.

In früheren Versionen als Excel 2007 werden Arrayformeln häufig verwendet, um eine Summe mit mehreren Bedingungen zu berechnen. Dies ist relativ einfach, insbesondere wenn Sie den Assistenten für bedingte Summen in Excel verwenden, aber er ist oft langsam. In der Regel gibt es viel schnellere Möglichkeiten, dasselbe Ergebnis zu erzielen. Wenn Sie nur über wenige SUMs mit mehreren Bedingungen verfügen, können Sie möglicherweise die DSUM-Funktion verwenden, die viel schneller ist als die entsprechende Arrayformel.

Wenn Sie Arrayformeln verwenden müssen, können Sie einige der folgenden Methoden zu deren Beschleunigung einsetzen:

Verwenden Sie dynamische Bereichsnamen oder strukturierte Tabellenverweise, um die Anzahl der Zellen zu minimieren.
Teilen Sie die verschiedenen Bedingungen in eine Spalte von Hilfsformeln auf, die für jede Zeile True oder False zurückgeben, und verweisen Sie dann in einer SUMMEN- oder Arrayformel auf die Hilfsspalte. Dies scheint die Anzahl der Berechnungen für eine einzelne Arrayformel möglicherweise nicht zu reduzieren. In den meisten Fällen ermöglicht es dem intelligenten Neuberechnungsprozess jedoch, nur die Formeln in der Hilfsspalte neu zu berechnen, die neu berechnet werden müssen.
Erwägen Sie das Verketten aller Bedingungen zu einer einzelnen Bedingung und das anschließend Verwenden von SUMIF.
Wenn die Daten sortiert werden können, zählen Sie Zeilengruppen, und beschränken Sie die Arrayformeln auf die Teilmengengruppen.

Priorisieren mehrerer Bedingungen SUMIFS, COUNTIFS und anderen IFS-Familienfunktionen

Diese Funktionen werten jede der Bedingungen von links nach rechts nacheinander aus. Daher ist es effizienter, die restriktivste Bedingung zuerst zu setzen, sodass nachfolgende Bedingungen nur die kleinste Anzahl von Zeilen betrachten müssen.

Erwägen von Optionen für die Verwendung von SUMPRODUCT für Arrayformeln mit mehreren Bedingungen

Ab Excel 2007 sollten Sie immer die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS sowie in Excel 2016 MAXIFS- und MINIFS-Funktionen anstelle von SUMMENPRODUKT-Formeln verwenden, wenn möglich.

In früheren Versionen gibt es einige Vorteile der Verwendung von SUMPRODUCT anstelle von SUM-Arrayformeln:

SUMPRODUCT muss nicht mit STRG+UMSCHALT+EINGABETASTE in ein Array eingegeben werden.
SUMPRODUCT ist in der Regel etwas schneller (5 bis 10 %).

Verwenden Sie SUMPRODUCT für Arrayformeln mit mehreren Bedingungen wie folgt:

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

In diesem Beispiel Condition1 sind und Condition2 bedingte Ausdrücke wie $A$1:$A$10000<=$Z4. Da bedingte Ausdrücke True oder False anstelle von Zahlen zurückgeben, müssen Sie in der SUMPRODUCT-Funktion in Zahlen umgewandelt werden. Dazu können Sie zwei Minuszeichen (--) verwenden oder 0 (+0) hinzufügen oder mit 1 (x1) multiplizieren. Die Verwendung von -- ist etwas schneller als +0 oder x1.

Beachten Sie, dass Größe und Form der Bereiche oder Arrays, die in den bedingten Ausdrücken und dem zu summierenden Bereich verwendet werden, identisch sein müssen und diese keine ganzen Spalten enthalten dürfen.

Sie können die Begriffe auch direkt in SUMPRODUCT multiplizieren, anstatt sie durch Kommas zu trennen:

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

Dies ist in der Regel etwas langsamer als die Verwendung der Kommasyntax und gibt einen Fehler aus, wenn der zu summierende Bereich einen Textwert enthält. Es ist jedoch etwas flexibler, da der zu summierende Bereich z. B. mehrere Spalten aufweisen kann, wenn die Bedingungen nur eine Spalte aufweisen.

Verwenden von SUMPRODUCT zum Multiplizieren und Hinzufügen von Bereichen und Arrays

Bei Berechnungen des gewichteten Mittelwerts, bei denen Sie einen Zahlenbereich mit einem anderen Zahlenbereich multiplizieren und die Ergebnisse addieren, kann das Verwenden der Syntax mit Kommas für SUMPRODUCT 20-25 % schneller als eine als Array eingegebene SUM-Funktion sein.

  {=SUM($D$2:$D$10301*$E$2:$E$10301)}
  =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
  =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

Diese drei Formeln liefern alle dasselbe Ergebnis, aber die dritte Formel, die die Kommasyntax für SUMPRODUCT verwendet, benötigt nur etwa 77 Prozent der Berechnungszeit, die die anderen beiden Formeln benötigen.

Berücksichtigen potenzieller Hindernisse bei Array- und Funktionsberechnungen

Das Berechnungsmodul in Excel ist für das Arbeiten mit Arrayformeln und Funktionen optimiert, die auf Bereiche verweisen. Doch eine bestimmte ungewöhnliche Anordnung dieser Formeln und Funktionen kann mitunter, jedoch nicht immer, zu einer beträchtlich längeren Berechnungszeit führen.

Wenn Sie ein Berechnungshindernis finden, das Arrayformeln und Bereichsfunktionen einschließt, sollten Sie Folgendes suchen:

Teilweise überlappende Verweise.
Arrayformeln und Bereichsfunktionen, die auf einen Teil eines Blocks mit Zellen verweisen, die in einer anderen Arrayformel oder Bereichsfunktion berechnet werden. Dies kommt häufig bei Zeitreihenanalysen vor.
Eine Gruppe mit Formeln, die mit Zeilenbezügen arbeitet, und eine zweite Gruppen mit Formeln, die auf die erste Gruppe nach Spalte verweist.
Eine große Gruppe von Arrayformeln mit einer Zeile, die einen Block mit Spalten abdeckt, mit SUM-Funktionen am Fuß jeder Spalte.

Effizientes Verwenden von Funktionen

Funktionen erweitern die Leistungsfähigkeit von Excel erheblich, aber die Art und Weise, wie Sie sie verwenden, kann sich häufig auf die Berechnungszeit auswirken.

Vermeiden von Singlethreadfunktionen

Die meisten nativen Excel-Funktionen funktionieren gut mit Multithreadberechnungen. Vermeiden Sie jedoch nach Möglichkeit die verwendung der folgenden Singlethreadfunktionen:

Benutzerdefinierte VBA- und Automation-Funktionen (UDFs), aber XLL-basierte UDFs können Multithreading sein.
PHONETIC
ZELLE mit Argument „format“ oder Argument „address“
INDIREKT
PIVOTDATENZUORDNEN
CUBEELEMENT
CUBEWERT
CUBEELEMENTEIGENSCHAFT
CUBEMENGE
CUBERANGELEMENT
CUBEKPIELEMENT
CUBEMENGENANZAHL
ADDRESS, wobei der fünfte Parameter (der sheet_name) angegeben ist
Jede Datenbankfunktion (DBSUMME, DBMITTELWERT usw.), die auf eine PivotTable verweist
FEHLER.TYP
HYPERLINK

Verwenden von Tabellen für Funktionen, die Bereiche behandeln

Für Funktionen wie SUM, SUMIF und SUMIFS , die Bereiche behandeln, ist die Berechnungszeit proportional zur Anzahl der verwendeten Zellen, die Sie summieren oder zählen. Nicht verwendete Zellen werden nicht untersucht, sodass verweise auf ganze Spalten relativ effizient sind, aber es ist besser, sicherzustellen, dass Sie nicht mehr verwendete Zellen einschließen, als Sie benötigen. Verwenden Sie Tabellen, oder berechnen Sie Teilmengenbereiche oder dynamische Bereiche.

Reduzieren von flüchtigen Funktionen

Veränderliche Funktionen können Neuberechnungen verlangsamen, da sie die Anzahl der Formeln erhöhen, die bei jeder Berechnung neu berechnet werden müssen.

Sie können häufig die Anzahl veränderlicher Funktionen verringern, indem Sie INDEX anstatt OFFSET und CHOOSE anstatt INDIRECT verwenden. OFFSET ist jedoch eine schnelle Funktion und kann häufig auf kreative Weise verwendet werden, die eine schnelle Berechnung ermöglicht.

Verwenden von benutzerdefinierten C- oder C++-Funktionen

Benutzerdefinierte Funktionen, die in C oder C++ programmiert sind und die C-API (XLL-Add-In-Funktionen) verwenden, sind im Allgemeinen schneller als benutzerdefinierte Funktionen, die mit VBA oder Automation (XLA- oder Automatisierungs-Add-Ins) entwickelt werden. Weitere Informationen finden Sie unter Developing Excel 2010 XLLs.

Die Leistung benutzerdefinierter VBA-Funktionen hängt sehr von ihrer Programmierung und dem Aufruf ab.

Verwenden schnellerer benutzerdefinierter VBA-Funktionen

Es ist in der Regel schneller, die Excel-Formelberechnungen und Arbeitsblattfunktionen zu verwenden, als benutzerdefinierte VBA-Funktionen zu verwenden. Der Grund ist zum einen der erhöhte Verarbeitungsaufwand für jeden Aufruf einer benutzerdefinierten Funktion und zum anderen der beträchtliche Verarbeitungsaufwand beim Übertragen von Informationen aus Excel in die benutzerdefinierte Funktion. Dennoch können überlegt entworfene und aufgerufene benutzerdefinierte Funktionen wesentlich schneller als komplexe Arrayformeln sein.

Vergewissern Sie sich, dass Sie alle Verweise auf Arbeitsblattzellen in die Eingabeparameter der benutzerdefinierten Funktion und nicht in den Hauptteil der benutzerdefinierten Funktion eingegeben haben, damit Sie Application.Volatile nicht unnötigerweise hinzufügen müssen.

Wenn Sie über viele Formeln verfügen müssen, die benutzerdefinierte Funktionen verwenden, stellen Sie sicher, dass Sie sich im manuellen Berechnungsmodus befinden und dass die Berechnung von VBA initiiert wird. Benutzerdefinierte VBA-Funktionen werden wesentlich langsamer berechnet, wenn die Berechnung nicht aus VBA aufgerufen wird (z. B. im automatischen Modus oder bei Drücken von F9 im manuellen Modus). Dies gilt insbesondere, wenn der Visual Basic-Editor (ALT+F11) in der aktuellen Excel-Sitzung geöffnet ist oder geöffnet wurde.

Sie können F9 abfangen und wie folgt an eine VBA-Berechnungsunterroutine weiterleiten. Fügen Sie diese Unterroutine dem Thisworkbook-Modul hinzu.

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

Fügen Sie diese Unterroutine einem Standardmodul hinzu.

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

Benutzerdefinierte Funktionen in Automation-Add-Ins (Excel 2002 und höher) verursachen keinen Visual Basic-Editor-Mehraufwand, da sie den integrierten Editor nicht verwenden. Die weiteren Leistungsmerkmale benutzerdefinierter Visual Basic 6-Funktionen in Automatisierungs-Add-Ins entsprechen denen von VBA-Funktionen.

Wenn Ihre benutzerdefinierte Funktion alle Zellen in einem Bereich verarbeitet, deklarieren Sie die Eingabe als Bereich, weisen Sie sie einer Variante zu, die ein Array enthält, und fügen Sie eine Schleife hinzu. Wenn Sie Verweise auf ganze Spalten effizient verarbeiten möchten, müssen Sie einen Teilbereich des Eingabebereichs erstellen und diesen wie in diesem Beispiel am seinem Schnittpunkt mit dem verwendeten Bereich teilen.

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

Wenn Ihre benutzerdefinierte Funktion Arbeitsblattfunktionen oder Methoden des Excel-Objektmodells zum Verarbeiten eines Bereichs verwendet, ist es meist effizienter, den Bereich als Objektvariable beizubehalten, anstatt alle Daten aus Excel in die benutzerdefinierte Funktion zu übertragen.

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

Wenn Ihre benutzerdefinierte Funktion zu einem frühen Zeitpunkt in der Berechnungskette aufgerufen wird, kann sie als nicht berechnete Argumente übergeben werden. Innerhalb einer benutzerdefinierten Funktion können Sie nicht berechnete Zellen mithilfe des folgenden Tests für leere Zellen erkennen, die eine Formel enthalten:

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

Für jeden Aufruf einer benutzerdefinierten Funktion und für jede Übertragung von Daten von Excel nach VBA entsteht ein Zeitaufwand. Mitunter kann mithilfe einer benutzerdefinierten Funktion mit einer sich auf mehrere Zellen beziehenden Arrayformel dieser Verarbeitungsaufwand verringert werden, indem mehrere Funktionsaufrufe zu einer einzelnen Funktion mit einem Eingabebereich mit mehreren Zellen zusammengefasst werden, der einen Bereich von Ergebnissen liefert.

Minimieren des Zellbereichs, auf den SUM und SUMIF verweisen

Die Excel-Funktionen SUM und SUMIF werden häufig auf eine große Anzahl von Zellen angewendet. Die Berechnungszeit dieser Funktionen ist proportional zur Anzahl der abgedeckten Zellen, weshalb Sie den Bereich der Zellen minimieren sollten, auf die die Funktionen verweisen.

Verwenden von Wildcardfunktionen SUMIF, COUNTIF, SUMIFS, COUNTIFS und anderen IFS-Funktionen

Verwenden Sie die Platzhalterzeichen ? (beliebiges einzelnes Zeichen) und * (kein Zeichen oder eine beliebige Anzahl von Zeichen) in den Kriterien für alphabetische Bereiche als Teil der Funktionen SUMIF, COUNTIFS, SUMIFS, COUNTIFS und anderen IFS-Funktionen .

Wählen Sie die Methode für zeitraumbasierte und kumulative SUMs aus.

Es gibt zwei Methoden zum Ausführen von zeitraumbasierten oder kumulativen SUMs. Angenommen, die Zahlen, die kumulativ summiert werden sollen, befinden sich in Spalte A, und Spalte B soll die kumulierte Summe enthalten. Sie können eine der folgenden Aktionen ausführen:

Sie können eine Formel in Spalte B erstellen, z =SUM($A$1:$A2) . B. und ziehen Sie sie so weit nach unten, wie Sie es benötigen. Die Anfangszelle der SUMME-Funktion ist in A1 verankert, doch da die Abschlusszelle einen relativen Zeilenbezug aufweist, wird sie automatisch für jede Zeile erhöht.
Sie können eine Formel wie =$A1 in Zelle B1 und =$B1+$A2 in Zelle B2 erstellen und nach unten ziehen, soweit Sie es benötigen. Die kumulative Zelle wird berechnet, indem der Wert dieser Zeile der vorherigen kumulativen SUM-Funktion hinzugefügt wird.

Bei 1000 Zeilen muss Excel bei der ersten Methode ca. 500.000 Berechnungen, bei der zweiten Methode nur ca. 2000 Berechnungen durchführen.

Berechnen von Teilmengensummen

Wenn Sie über mehrere sortierte Indizes für eine Tabelle verfügen (z. B. Website im Bereich), können Sie häufig erhebliche Berechnungszeit sparen, indem Sie die Adresse eines Teilbereichs von Zeilen (oder Spalten) dynamisch berechnen, der in der SUM - oder SUMIF-Funktion verwendet werden soll.

So berechnen Sie die Adresse eines Teilmengenbereichs von Zeilen oder Spalten:

Zählen Sie die Anzahl der Zeilen jedes Teilblocks.
Addieren Sie die Anzahlen für jeden Block kumulativ, um dessen Startzeile zu bestimmen.
Geben Sie die OFFSET-Funktion mit der Startzeile und Anzahl an, um einen Teilbereich an die Funktion SUM oder SUMIF zurückzugeben, der nur den Teilblock der Zeilen abdeckt.

Verwenden von TEILERGEBNISSEn für gefilterte Listen

Wählen Sie die SUBTOTAL-Funktion zum Anwenden von SUM auf gefilterte Listen. Die SUBTOTAL-Funktion ist sinnvoll, da diese Funktion im Gegensatz zu SUM Folgendes ignoriert:

Ausgeblendete Zeilen, die aus dem Filtern einer Liste resultieren. Ab Excel 2003 können Sie auch SUBTOTAL veranlassen, alle ausgeblendeten und nicht bloß gefilterte Zeilen zu ignorieren.
Weitere SUBTOTAL-Funktionen.

Verwenden der AGGREGAT-Funktion

Die AGGREGATE-Funktion ist eine leistungsstarke und effiziente Möglichkeit, 19 verschiedene Methoden zum Aggregieren von Daten zu berechnen (z. B. SUMME, MEDIAN, QUAN und LARGE). AGGREGATE verfügt über Optionen zum Ignorieren ausgeblendeter oder gefilterter Zeilen, Fehlerwerte und geschachtelter TEILSUMME - und AGGREGATfunktionen .

Vermeiden der Verwendung von DFunctions

DFunctions DSUM, DCOUNT, DAVERAGE usw. sind deutlich schneller als gleichwertige Arrayformeln. Der Nachteil der DFunctions besteht darin, dass die Kriterien in einem separaten Bereich liegen müssen, was ihre Verwendung und Aufrechterhaltung unter vielen Umständen unpraktisch macht. Ab Excel 2007 sollten Sie die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS anstelle der DFunctions verwenden.

Erstellen schnellerer VBA-Makros

Verwenden Sie die folgenden Tipps, um schnellere VBA-Makros zu erstellen.

Deaktivieren Sie alles außer den Wesentlichen, während Code ausgeführt wird

Um die Leistung für VBA-Makros zu verbessern, deaktivieren Sie explizit die Funktionalität, die während der Ausführung des Codes nicht erforderlich ist. Häufig ist eine Neuberechnung oder eine Neuberechnung nach der Codeausführung alles, was erforderlich ist und die Leistung verbessern kann. Nachdem Ihr Code ausgeführt wurde, stellen Sie die Funktionalität in ihren ursprünglichen Zustand wieder her.

Die folgende Funktionalität kann während der Ausführung Ihres VBA-Makros im Allgemeinen deaktiviert sein:

Application.ScreenUpdating Bildschirmaktualisierung deaktivieren. Wenn Application.ScreenUpdating auf False festgelegt ist, zeichnet Excel den Bildschirm nicht neu. Während Der Code ausgeführt wird, wird der Bildschirm schnell aktualisiert, und es ist normalerweise nicht erforderlich, dass der Benutzer jedes Update sehen muss. Eine einmalige Aktualisierung nach Ausführung des Codes kann die Leistung verbessern.
Application.DisplayStatusBar Deaktivieren Sie die status leiste. Wenn Application.DisplayStatusBar auf False festgelegt ist, zeigt Excel die status Leiste nicht an. Die Statusleiste ist von der Bildschirmaktualisierungseinstellung getrennt, weshalb Sie weiter den Status des aktuellen Vorgangs sehen können, auch während der Bildschirm nicht aktualisiert wird. Wenn Sie jedoch nicht die status jedes Vorgangs anzeigen müssen, verbessert das Deaktivieren der status Leiste während der Ausführung des Codes auch die Leistung.
Application.Calculation Wechselt zur manuellen Berechnung. Wenn Application.Calculation auf xlCalculationManual festgelegt ist, berechnet Excel die Arbeitsmappe nur, wenn der Benutzer die Berechnung explizit initiiert. Im automatischen Berechnungsmodus bestimmt Excel den Zeitpunkt der Berechnung. Wenn sich beispielsweise ein Zellwert ändert, der sich auf eine Formel bezieht, berechnet Excel die Formel neu. Wenn Sie in den manuellen Berechnungsmodus wechseln, können Sie abwarten, bis alle zu der Formel gehörenden Zellen geändert wurden, ehe Sie die Arbeitsmappe neu berechnen. Indem Sie die Arbeitsmappe nur bei Bedarf neu berechnen, während Der Code ausgeführt wird, können Sie die Leistung verbessern.
Application.EnableEvents Ereignisse deaktivieren. Wenn Application.EnableEvents auf False festgelegt ist, löst Excel keine Ereignisse aus. Wenn Add-Ins auf Excel-Ereignisse lauschen, nutzen diese Add-Ins Ressourcen auf dem Computer, während sie die Ereignisse aufzeichnen. Wenn es nicht erforderlich ist, dass das Add-In die Ereignisse aufzeichnen muss, die während der Codeausführung auftreten, verbessert das Deaktivieren von Ereignissen die Leistung.
ActiveSheet.DisplayPageBreaks Seitenumbrüche deaktivieren. Wenn ActiveSheet.DisplayPageBreaks auf False festgelegt ist, zeigt Excel keine Seitenumbrüche an. Es ist nicht erforderlich, Seitenumbrüche neu zu berechnen, während der Code ausgeführt wird, und die Berechnung der Seitenumbrüche nach der Ausführung des Codes verbessert die Leistung.

Wichtig

Vergessen Sie nicht, diese Funktionalität nach der Ausführung Ihres Codes wieder in den Originalzustand zurückzuversetzen.

Das folgende Beispiel zeigt die Funktionalität, die während der Ausführung Ihres VBA-Makros deaktiviert sein kann.

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Lesen und Schreiben großer Datenblöcke in einem einzelnen Vorgang

Optimieren Sie Ihren Code, indem Sie explizit die Häufigkeit verringern, mit der Daten zwischen Excel und Ihrem Code übertragen werden. Anstatt die Zellen nacheinander in einer Schleife zu durchlaufen, um einen Wert abzurufen oder festzulegen, können Sie die Werte im gesamten Zellenbereich in einer Zeile abrufen oder festlegen, und zwar mithilfe einer Variante mit einem zweidimensionalen Array zum Speichern der Werte den Anforderungen entsprechend. In den folgenden Codebeispielen werden diese beiden Methoden verglichen.

Das folgende Codebeispiel zeigt nicht optimierten Code, der die Zellen nacheinander in einer Schleife durchläuft, um die Werte der Zellen A1:C10000 abzurufen und festzulegen. Diese Zellen enthalten keine Formeln.

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

Das folgende Codebeispiel zeigt optimierten Code, der ein Array verwendet, um die Werte der Zellen A1:C10000 gleichzeitig abzurufen und festzulegen. Diese Zellen enthalten keine Formeln.

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange

Verwenden. Wert2 anstelle von . Wert oder . Text beim Lesen von Daten aus einem Excel-Bereich

. Text gibt den formatierten Wert einer Zelle zurück. Dies ist langsam, kann ### zurückgeben, wenn der Benutzer zoomt, und kann an Genauigkeit verlieren.
. Value gibt eine VBA-Währung oder VBA-Datumsvariable zurück, wenn der Bereich als Datum oder Währung formatiert wurde. Dies ist langsam, kann an Genauigkeit verlieren und beim Aufrufen von Arbeitsblattfunktionen zu Fehlern führen.
. Wert2 ist schnell und ändert nicht die Daten, die aus Excel abgerufen werden.

Vermeiden der Auswahl und Aktivierung von Objekten

Das Auswählen und Aktivieren von Objekten ist verarbeitungsintensiver als das direkte Verweisen auf Objekte. Indem Sie direkt auf ein Objekt wie einen Bereich oder ein Shape verweisen, können Sie die Leistung verbessern. In den folgenden Codebeispielen werden die beiden Methoden verglichen.

Das folgende Codebeispiel zeigt nicht optimierten Code, der jedes Shape auf dem aktiven Blatt auswählt und den Text in "Hello" ändert.

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

Das folgende Codebeispiel zeigt optimierten Code, der direkt auf jedes Shape verweist und den Text in "Hello" ändert.

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

Verwenden Sie diese zusätzlichen VBA-Leistungsoptimierungen.

Es folgt eine Liste mit weiteren möglichen Leistungsoptimierungen für Ihren VBA-Code:

Geben Sie Ergebnisse zurück, indem Sie ein Array direkt einem Range-Objekt zuweisen.
Deklarieren Sie Variablen mit expliziten Typen zum Vermeiden des Verarbeitungsaufwands zum Bestimmen des Datentyps (möglicherweise mehrfach in einer Schleife) während der Codeausführung.
Implementieren Sie für einfache Funktionen, die Sie häufig in Ihrem Code verwenden, die Funktionen selbst in VBA, anstatt mit dem WorksheetFunction-Objekt zu arbeiten. Weitere Informationen finden Sie unter Verwenden schnellerer benutzerdefinierter VBA-Funktionen.
Verwenden Sie die Range.SpecialCells-Methode zum Verringern der Anzahl der Zellen, mit denen Ihr Code interagiert.
Berücksichtigen Sie die Leistungssteigerungen, wenn Sie Ihre Funktionalität mithilfe der C-API im XLL SDK implementiert haben. Weitere Informationen finden Sie in der Dokumentation zum Excel 2010-XLL-SDK.

Berücksichtigen der Leistung und Größe von Excel-Dateiformaten

Beginnend mit Excel 2007 bietet Excel im Vergleich zu früheren Formaten eine große Vielfalt von Dateiformaten. Die drei Standard Formate sind XLS, XLSB und XLS, wenn die Dateiformatvariationen Makro, Vorlage, Add-In, PDF und XPS ignoriert werden.

XLS-Format

Das XLS-Format ist das gleiche Format wie frühere Versionen. Wenn Sie dieses Format verwenden, sind Sie auf 256 Spalten und 65.536 Zeilen beschränkt. Wenn Sie eine Excel 2007- oder Excel 2010-Arbeitsmappe im XLS-Format speichern, führt Excel eine Kompatibilitätsprüfung aus. Die Dateigröße ist fast identisch mit früheren Versionen (einige zusätzliche Informationen können gespeichert werden), und die Leistung ist etwas langsamer als frühere Versionen. Jede Multithreadoptimierung, die Excel in Bezug auf die Zellenberechnungsreihenfolge ausführt, wird nicht im XLS-Format gespeichert. Daher kann die Berechnung einer Arbeitsmappe nach dem Speichern der Arbeitsmappe im XLS-Format, dem Schließen und dem erneuten Öffnen der Arbeitsmappe langsamer sein.
XLSB-Format

XLSB ist das Binärformat ab Excel 2007. Es ist als komprimierter Ordner strukturiert, der viele Binärdateien enthält. Es ist viel kompakter als das XLS-Format, aber der Umfang der Komprimierung hängt vom Inhalt der Arbeitsmappe ab. Bei zehn Arbeitsmappen reicht der Reduzierungsfaktor von zwei bis acht, wobei die durchschnittliche Reduzierung den Faktor vier hat. Ab Excel 2007 ist die Leistung zum Öffnen und Speichern nur geringfügig langsamer als das XLS-Format.
XLSX-Format

XLSX ist das in Excel 2007 eingeführte XML-Format, das ab dieser Version zugleich das Standardformat ist. Das XLSX-Format ist ein komprimierter Ordner, der viele XML-Dateien enthält (wenn Sie die Dateinamenerweiterung in .zip ändern, können Sie den komprimierten Ordner öffnen und seinen Inhalt untersuchen). In der Regel sind im XLSX-Format erstellte Dateien größer als Dateien im XLSB-Format (durchschnittlich 1,5-mal), allerdings immer noch wesentlich kleiner als XLS-Dateien. Das Öffnen und Schließen dauert etwas länger als bei XLSB-Dateien.

Öffnen, Schließen und Speichern von Arbeitsmappen

Möglicherweise stellen Sie fest, dass das Öffnen, Schließen und Speichern von Arbeitsmappen viel langsamer ist als die Berechnung. Manchmal liegt dies nur daran, dass Sie über eine große Arbeitsmappe verfügen, aber es kann auch andere Gründe geben.

Wenn eine oder mehrere Ihrer Arbeitsmappen langsamer geöffnet und geschlossen werden als gewohnt, kann eines der folgenden Probleme vorliegen.

Temporäre Dateien

Temporäre Dateien können sich in Ihrem Verzeichnis \Windows\Temp (in Windows 95, Windows 98 und Windows ME) oder im Verzeichnis \Dokumente und Einstellungen\Benutzername\Lokale Einstellungen\Temp (in Windows 2000 und Windows XP) ansammeln. Excel erstellt diese Dateien für die Arbeitsmappe und für Steuerelemente, die von geöffneten Arbeitsmappen verwendet werden. Softwareinstallationsprogramme erstellen auch temporäre Dateien. Wenn Excel aus irgendeinem Grund nicht mehr reagiert, müssen Sie diese Dateien möglicherweise löschen.

Zu viele temporäre Dateien können Probleme verursachen, daher sollten Sie sie gelegentlich sauber. Wenn Sie jedoch Software installiert haben, die einen Neustart des Computers erfordert, und sie dies noch nicht getan haben, sollten Sie einen Neustart durchführen, bevor Sie die temporären Dateien löschen.

Eine einfache Möglichkeit zum Öffnen Ihres temporären Verzeichnisses ist das Windows-Startmenü : Klicken Sie auf Start, und klicken Sie dann auf Ausführen. Geben Sie im Textfeld %temp% ein, und klicken Sie dann auf OK.

Nachverfolgen von Änderungen in einer freigegebenen Arbeitsmappe

Das Nachverfolgen von Änderungen in einer freigegeben Arbeitsmappe sorgt für ein rapides Anwachsen der Dateigröße der Arbeitsmappe.
Fragmentierte Auslagerungsdatei

Vergewissern Sie sich, dass sich Ihre Windows-Auslagerungsdatei auf einem Datenträger mit viel Speicherplatz befindet, und defragmentieren Sie den Datenträger regelmäßig.
Arbeitsmappe mit kennwortgeschützter Struktur

Eine Arbeitsmappe, deren Struktur durch ein Kennwort geschützt ist (Menü Extras , >Schutz>Arbeitsmappe> schützen, geben Sie das optionale Kennwort ein), wird viel langsamer geöffnet und geschlossen als eine Arbeitsmappe, die ohne das optionale Kennwort geschützt ist.
Probleme mit dem verwendeten Bereich

Übergroße verwendete Bereiche können für ein langsames Öffnen und eine hohe Dateigröße sorgen, insbesondere wenn sie durch ausgeblendete Zeilen oder Spalten mit nicht standardmäßiger Höhe oder Breite verursacht werden. Weitere Informationen zu Problemen mit dem verwendeten Bereich finden Sie unter Minimieren des verwendeten Bereichs.
Große Anzahl von Steuerelementen auf Arbeitsblättern

Eine große Anzahl von Steuerelementen (Kontrollkästchen, Hyperlinks usw.) auf Arbeitsblättern kann das Öffnen einer Arbeitsmappe aufgrund der Anzahl der verwendeten temporären Dateien verlangsamen. Dies kann auch Probleme beim Öffnen oder Speichern einer Arbeitsmappe in einem WAN (oder sogar in einem LAN) verursachen. Wenn sie dieses Problem haben, sollten Sie die Arbeitsmappe neu entwerfen.
Große Anzahl von Links zu anderen Arbeitsmappen

Öffnen Sie möglichst die Arbeitsmappen, zu denen eine Verknüpfung erstellt wird, bevor Sie die Arbeitsmappe öffnen, die die Verknüpfungen enthält. Häufig ist es schneller eine Arbeitsmappe zu öffnen als die Verknüpfungen aus einer geschlossenen Arbeitsmappe zu lesen.
Einstellungen des Virenscanners

Einige Virenscannereinstellungen können Probleme oder Verlangsamungen beim Öffnen, Schließen oder Speichern verursachen, insbesondere auf einem Server. Wenn Sie meinen, dass dies das Problem sein könnte, deaktivieren Sie den Virenscanner vorübergehend.
Langsame Berechnung verursacht langsames Öffnen und Speichern

Unter bestimmten Umständen berechnet Excel Ihre Arbeitsmappe beim Öffnen oder Schließen neu. Wenn die Berechnungszeit für Die Arbeitsmappe lang ist und ein Problem verursacht, stellen Sie sicher, dass die Berechnung auf manuell festgelegt ist, und deaktivieren Sie die Option "Vor dem Speichern berechnen" (Berechnungder Extras-Optionen>>).
Symbolleistendateien (XLB)

Überprüfen Sie die Größe der Symbolleistendatei. Die Größe einer typischen Symbolleistendatei ist 10-20 KB. Sie können Ihre XLB-Dateien finden, indem Sie mithilfe der Windows-Suche nach suchen *.xlb . Jeder Benutzer hat eine eindeutige XLB-Datei. Durch Hinzufügen, Ändern oder Anpassen von Symbolleisten erhöht sich der Größe Ihrer Datei toolbar.xlb. Durch Löschen der Datei werden alle Symbolleistenanpassungen entfernt (sicherer ist das Umbenennen in "toolbar.OLD"). Eine neue XLB-Datei wird beim nächsten Öffnen von Excel erstellt.

Durchführen zusätzlicher Leistungsoptimierungen

In den folgenden Bereichen können Sie Leistungsverbesserungen vornehmen.

PivotTables

PivotTables bieten eine effiziente Möglichkeit zum Zusammenfassen großer Datenmengen.
- Summen als Endergebnisse. Wenn Sie Summen und Teilergebnisse als Endergebnisse Ihrer Arbeitsmappe erstellen müssen, erwägen Sie das Arbeiten mit PivotTables.
- Summen als Zwischenergebnisse. PivotTables eignen sich sehr gut zum Erstellen von Zusammenfassungsberichten. Versuchen Sie jedoch das Erstellen von Formeln zu vermeiden, die PivotTable-Ergebnisse als Zwischen- oder Teilergebnisse in Ihrer Berechnungskette verwenden, es sei denn, Sie gewährleisten Folgendes:
- Die PivotTable wurde während der Berechnung ordnungsgemäß aktualisiert.
- Die PivotTable wurde nicht geändert, sodass die Informationen weiter angezeigt werden.
Wenn Sie PivotTables dennoch als Zwischenergebnisse nutzen möchten, verwenden Sie die GETPIVOTDATA-Funktion.
Bedingte Formate und Datenüberprüfung

Bedingte Formate und Datengültigkeitsprüfungen sind sinnvoll, können jedoch bei verstärkter Nutzung Berechnungen beträchtlich verlangsamen. Wenn die Zelle angezeigt wird, wird jede Formel für das bedingte Format bei jeder Berechnung ausgewertet und wenn die Anzeige der Zelle, die das bedingte Format enthält, aktualisiert wird. Das Excel-Objektmodell bietet die Worksheet.EnableFormatConditionsCalculation-Eigenschaft, sodass Sie die Berechnung bedingter Formate aktivieren und deaktivieren können.
Definierte Namen

Definierte Namen sind eines der leistungsstärksten Features in Excel, aber sie nehmen zusätzliche Berechnungszeit in Anspruch. Die Verwendung von Namen, die auf andere Arbeitsblätter verweisen, erhöht die Komplexität des Berechnungsprozesses. Außerdem sollten Sie versuchen, geschachtelte Namen (Namen, die auf andere Namen verweisen) zu vermeiden.

Da Namen immer dann berechnet werden, wenn eine Formel, die auf sie verweist, berechnet wird, sollten Sie berechnungsintensive Formeln und Funktionen nicht in definierten Namen ablegen. Die Berechnung wird wesentlich beschleunigt, indem Sie die berechnungsintensive Formel oder Funktion in einer gesonderten Zelle ablegen und auf diese Zelle verweisen, entweder direkt oder mithilfe eines Namens.
Formeln, die nur gelegentlich verwendet werden

Viele Arbeitsmappen enthalten oft sehr viele Formeln und Nachschlagevorgänge mit dem Zweck, die Eingabedaten für Berechnungen in das entsprechende Format umzuwandeln oder werden als Schutzmaßnahmen gegen Änderungen von Größe oder Form der Daten eingesetzt. Wenn Sie über Formelblöcke verfügen, die nur gelegentlich verwendet werden, können Sie Spezialwerte kopieren und einfügen, um die Formeln vorübergehend zu deaktivieren, oder Sie können sie in einer gesonderten, nur selten geöffneten Arbeitsmappe ablegen. Da Arbeitsblattfehler oft dadurch verursacht werden, dass nicht bemerkt wird, dass Formeln in Werte umgewandelt wurden, kann das Arbeiten mit einer gesonderten Arbeitsmappe vorteilhafter sein.
Genügend Arbeitsspeicher verwenden

Die 32-Bit-Version von Excel kann bis zu 2 GB RAM oder bis zu 4 GB RAM für 32-Bit-Versionen von Excel 2013 und 2016 für große Adressen verwenden. Doch auch der Computer, auf dem Excel ausgeführt wird, benötigt Arbeitsspeicherressourcen. Wenn Ihr Computer nur 2 GB Arbeitsspeicher hat, kann Excel nicht die gesamten 2 GB nutzen, da ein Teil des Arbeitsspeichers dem Betriebssystem und anderen ausgeführten Programmen zugeordnet wird. Um die Leistung von Excel auf einem 32-Bit-Computer zu optimieren, wird empfohlen, dass der Computer mindestens 3 GB RAM hat.

Die 64-Bit-Version von Excel hat keinen Grenzwert von 2 GB oder bis zu 4 GB. Weitere Informationen finden Sie im Abschnitt "Große Datasets und die 64-Bit-Version von Excel" unter Excel-Leistung: Leistungs- und Limitverbesserungen.

Zusammenfassung

In diesem Artikel wurden Möglichkeiten zum Optimieren von Excel-Funktionen wie Links, Lookups, Formeln, Funktionen und VBA-Code behandelt, um häufige Hindernisse zu vermeiden und die Leistung zu verbessern.