Excel-Leistung – Tipps zur Optimierung von Leistungshindernissen (2023)

Gilt für:Excel | Excel 2013 | Büro 2016 | VBA

Befolgen Sie diese Tipps zur Optimierung vieler häufig auftretender Leistungsbehinderungen in Excel.

Optimieren Sie Referenzen und Links

Erfahren Sie, wie Sie die Leistung in Bezug auf Referenz- und Linktypen verbessern können.

Verwenden Sie keine Vorwärts- und Rückwärtsreferenzierung

Um die Übersichtlichkeit zu erhöhen und Fehler zu vermeiden, gestalten Sie Ihre Formeln so, dass sie nicht nach vorne (nach rechts oder unten) auf andere Formeln oder Zellen verweisen. Die Vorwärtsreferenzierung wirkt sich normalerweise nicht auf die Berechnungsleistung aus, außer in extremen Fällen bei der ersten Berechnung einer Arbeitsmappe, wo es länger dauern kann, eine sinnvolle Berechnungssequenz festzulegen, wenn die Berechnung vieler Formeln verschoben werden muss.

Minimieren Sie die Verwendung von Zirkelverweisen durch Iteration

Das Berechnen von Zirkelverweisen mit Iterationen ist langsam, da mehrere Berechnungen erforderlich sind und diese Berechnungen Single-Threaded sind. Häufig können Sie die Zirkelbezüge mithilfe der Algebra „abwickeln“, sodass eine iterative Berechnung nicht 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 Zirkelbezüge Blatt für Blatt, ohne Abhängigkeiten zu berücksichtigen. Daher kommt es in der Regel zu langsamen Berechnungen, wenn sich Ihre Zirkelbezüge über mehr als ein Arbeitsblatt erstrecken. Versuchen Sie, die Kreisrechnungen auf ein einzelnes Arbeitsblatt zu verschieben oder die Reihenfolge der Arbeitsblattberechnungen zu optimieren, um unnötige Berechnungen zu vermeiden.

Bevor die iterativen Berechnungen beginnen, muss Excel die Arbeitsmappe neu berechnen, um alle Zirkelverweise und deren Abhängigkeiten zu identifizieren. Dieser Vorgang entspricht zwei oder drei Iterationen der Berechnung.

Nachdem die Zirkelverweise und ihre abhängigen Elemente identifiziert wurden, muss Excel bei jeder Iteration nicht nur alle Zellen im Zirkelverweis berechnen, sondern auch alle Zellen, die von den Zellen in der Zirkelverweiskette abhängen, sowie flüchtige Zellen und ihre abhängigen Elemente. Wenn Sie eine komplexe Berechnung haben, die von Zellen im Zirkelbezug abhängt, kann es schneller sein, diese in einer separaten geschlossenen Arbeitsmappe zu isolieren und sie zur Neuberechnung zu öffnen, nachdem die Zirkelberechnung konvergiert ist.

Es ist wichtig, die Anzahl der Zellen in der Kreisrechnung und die für diese Zellen benötigte Berechnungszeit zu reduzieren.

Vermeiden Sie Verknüpfungen zwischen Arbeitsmappen

Vermeiden Sie nach Möglichkeit Verknüpfungen zwischen Arbeitsmappen. Sie können langsam sein, leicht kaputt gehen und nicht immer leicht zu finden und zu reparieren sein.

Die Verwendung weniger größerer Arbeitsmappen ist normalerweise, aber nicht immer, besser als die Verwendung vieler kleinerer Arbeitsmappen. Einige Ausnahmen hiervon 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 abzulegen, oder wenn Sie nicht über ausreichend RAM verfügen.

Versuchen Sie, einfache direkte Zellbezüge zu verwenden, die bei geschlossenen Arbeitsmappen funktionieren. Dadurch können Sie eine Neuberechnung vermeidenalleIhre verknüpften Arbeitsmappen, wenn Sie eine Neuberechnung durchführenbeliebigArbeitsmappe. Außerdem können Sie die Werte sehen, die Excel aus der geschlossenen Arbeitsmappe gelesen hat, was häufig für das Debuggen und Überwachen der Arbeitsmappe wichtig ist.

Wenn Sie die Verwendung verknüpfter Arbeitsmappen nicht vermeiden können, versuchen Sie, sie alle zu öffnen statt zu schließen, und öffnen Sie die Arbeitsmappen, mit denen sie verknüpft sind, bevor Sie die Arbeitsmappen öffnen, von denen aus sie verknüpft sind.

Minimieren Sie Verknüpfungen zwischen Arbeitsblättern

Die Verwendung vieler Arbeitsblätter kann die Verwendung Ihrer Arbeitsmappe vereinfachen, aber im Allgemeinen ist die Berechnung von Verweisen auf andere Arbeitsblätter langsamer als die Berechnung von Verweisen innerhalb von Arbeitsblättern.

Minimieren Sie den genutzten Bereich

Um Speicherplatz zu sparen und die Dateigröße zu reduzieren, versucht Excel, Informationen nur über den Bereich auf einem Arbeitsblatt zu speichern, der verwendet wurde. Dies nennt manGebrauchtbereich. Manchmal erweitern verschiedene Bearbeitungs- und Formatierungsvorgänge den genutzten Bereich deutlich über den Bereich hinaus, den Sie derzeit als genutzt betrachten würden. Dies kann zu Leistungseinbußen und Einschränkungen der Dateigröße führen.

Sie können den sichtbaren verwendeten Bereich auf einem Arbeitsblatt überprüfen, indem Sie Strg+Ende drücken. Wenn dies zu viel ist, sollten Sie erwägen, alle Zeilen und Spalten unterhalb und rechts von Ihrer zuletzt verwendeten Zelle zu löschen und die Arbeitsmappe dann zu speichern. Erstellen Sie zunächst eine Sicherungskopie. Wenn Sie Formeln mit Bereichen haben, die sich in den gelöschten Bereich erstrecken oder auf diesen verweisen, werden diese Bereiche verkleinert oder in geändert#N / A.

Erlauben Sie zusätzliche Daten

Wenn Sie Ihren Arbeitsblättern häufig Zeilen oder Spalten mit Daten hinzufügen, müssen Sie eine Möglichkeit finden, dass Ihre Excel-Formeln automatisch auf den neuen Datenbereich verweisen, anstatt jedes Mal zu versuchen, Ihre Formeln zu suchen und zu ändern.

Sie können dies erreichen, indem Sie in Ihren Formeln einen großen Bereich verwenden, der weit über Ihre aktuellen Datengrenzen hinausgeht. Dies kann jedoch unter bestimmten Umständen zu einer ineffizienten Berechnung führen und ist schwierig beizubehalten, da das Löschen von Zeilen und Spalten den Bereich unbemerkt verkleinern kann.

Verwenden Sie strukturierte Tabellenverweise (empfohlen)

Ab Excel 2007 können Sie strukturierte Tabellenverweise verwenden, die sich automatisch vergrößern und verkleinern, wenn die Größe der referenzierten Tabelle zunimmt oder abnimmt.

Diese Lösung hat mehrere Vorteile:

• Es bestehen weniger Leistungsnachteile als bei den Alternativen der Referenzierung ganzer Spalten und dynamischer Bereiche.

• Es ist einfach, mehrere Datentabellen auf einem einzigen Arbeitsblatt zu haben.

• Formeln, die in die Tabelle eingebettet sind, erweitern und verkleinern sich ebenfalls mit den Daten.

Alternativ können Sie auch ganze Spalten- und Zeilenverweise verwenden

Ein alternativer Ansatz besteht beispielsweise darin, einen Verweis auf eine ganze Spalte zu verwenden$A:$A. Diese Referenz gibt alle Zeilen in Spalte A zurück. Daher können Sie so viele Daten hinzufügen, wie Sie möchten, und die Referenz wird diese immer enthalten.

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

• Viele in Excel integrierte Funktionen (SUMME,SUMIF) berechnen Verweise auf ganze Spalten effizient, da sie automatisch die zuletzt verwendete Zeile in der Spalte erkennen. Array-Berechnungsfunktionen mögen jedochSUMMENPRODUKTEntweder können sie keine ganzen Spaltenverweise verarbeiten oder alle Zellen in der Spalte berechnen.

• Benutzerdefinierte Funktionen erkennen nicht automatisch die zuletzt verwendete Zeile in der Spalte und berechnen daher häufig ganze Spaltenverweise ineffizient. Es ist jedoch einfach, benutzerdefinierte Funktionen so zu programmieren, dass sie die zuletzt verwendete Zeile erkennen.

• Es ist schwierig, Verweise auf ganze Spalten zu verwenden, wenn Sie mehrere Datentabellen in einem einzigen Arbeitsblatt haben.

• In Excel 2007 und späteren 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. Die Berechnung kann langsam sein, insbesondere bei 1 Million Zeilen.

Alternativ können Sie Dynamikbereiche verwenden

Durch die Verwendung derOFFSEToderINDEXUndCOUNTAMithilfe von Funktionen in der Definition eines benannten Bereichs können Sie den Bereich, auf den sich der benannte Bereich bezieht, dynamisch erweitern und verkleinern lassen. 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(Blatt1!$A$1) - 1,1)

Wenn Sie den Namen des dynamischen Bereichs in einer Formel verwenden, wird dieser automatisch um neue Einträge erweitert.

Verwendung derINDEXFormel für einen Dynamikbereich ist im Allgemeinen der vorzuziehenOFFSETFormel, weilOFFSEThat den Nachteil, dass es sich um eine volatile Funktion handelt, die bei jeder Neuberechnung berechnet wird.

Die Leistung nimmt ab, weil dieCOUNTADie Funktion innerhalb der Dynamikbereichsformel muss viele Zeilen untersuchen. Sie können diesen Leistungsabfall minimieren, indem Sie die speichernCOUNTATeil der Formel in einer separaten Zelle oder einem definierten Namen und dann Verweis auf die Zelle oder den Namen im dynamischen Bereich:

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 verwendenINDIREKTDynamikbereiche zu konstruieren, aberINDIREKTist volatil und berechnet immer Single-Threaded.

Dynamikbereiche haben folgende Vor- und Nachteile:

• Dynamische Bereiche eignen sich gut, um die Anzahl der von Array-Formeln durchgeführten Berechnungen zu begrenzen.

• Die Verwendung mehrerer dynamischer Bereiche innerhalb einer einzelnen Spalte erfordert spezielle Zählfunktionen.

• Die Verwendung vieler Dynamikbereiche kann die Leistung beeinträchtigen.

Verbessern Sie die Suchberechnungszeit

In Office 365 Version 1809 und höher sind VLOOKUP, HLOOKUP und MATCH von Excel für die exakte Übereinstimmung unsortierter Daten viel schneller als je zuvor, wenn mehrere Spalten (oder Zeilen mit HLOOKUP) aus demselben Tabellenbereich nachgeschlagen werden.

Allerdings stellen Lookups bei früheren Excel-Versionen weiterhin häufig erhebliche Berechnungshindernisse dar. Glücklicherweise gibt es viele Möglichkeiten, die Suchberechnungszeit zu verkürzen. Wenn Sie die Option „Genaue Übereinstimmung“ verwenden, ist die Berechnungszeit für die Funktion proportional zur Anzahl der gescannten Zellen, bevor eine Übereinstimmung gefunden wird. Bei Suchvorgängen über große Bereiche kann diese Zeit von Bedeutung sein.

Suchzeit unter Verwendung der ungefähren Übereinstimmungsoptionen vonSVERWEIS,HLOOKUP, UndPASSENDie Suche nach sortierten Daten ist schnell und wird durch die Länge des gesuchten Bereichs nicht wesentlich erhöht. Die Eigenschaften sind die gleichen wie bei der binären Suche.

Nachschlageoptionen verstehen

Stellen Sie sicher, dass Sie die Match-Type- und Range-Lookup-Optionen in verstehenPASSEN,SVERWEIS, UndHLOOKUP.

Das folgende Codebeispiel zeigt die Syntax fürPASSENFunktion. Weitere Informationen finden Sie unterPassenMethode derArbeitsblattfunktionObjekt.

MATCH(Suchwert, Sucharray, Matchtyp)

• Matchtype=1Gibt die größte Übereinstimmung zurück, die kleiner oder gleich dem Suchwert ist, wenn das Sucharray aufsteigend sortiert ist (ungefähre Übereinstimmung). Wenn das Sucharray nicht aufsteigend sortiert ist, gibt MATCH eine falsche Antwort zurück. Die Standardoption istungefähre Übereinstimmung, aufsteigend sortiert.

• MatchType=0fordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.

• MatchType=-1Gibt die kleinste Übereinstimmung zurück, die größer oder gleich dem Suchwert ist, wenn das Sucharray absteigend sortiert ist (ungefähre Übereinstimmung).

Das folgende Codebeispiel zeigt die Syntax fürSVERWEISUndHLOOKUPFunktionen. Weitere Informationen finden Sie unterSVERWEISUndHLOOKUPMethoden derArbeitsblattfunktionObjekt.

VLOOKUP(Suchwert, Tabellenarray, Spaltenindexnummer, Bereichssuche) HLOOKUP(Suchwert, Tabellenarray, Zeilenindexnummer, Bereichssuche)

• Bereichssuche=TRUEgibt die größte Übereinstimmung zurück, die kleiner oder gleich dem Suchwert ist (ungefähre Übereinstimmung). Dies ist die Standardoption. Tabellenarray muss aufsteigend sortiert sein.

• Bereichssuche=FALSEfordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.

Vermeiden Sie nach Möglichkeit die Suche nach unsortierten Daten, da diese langsam sind. Wenn Ihre Daten sortiert sind, Sie aber eine genaue Übereinstimmung wünschen, lesen SieVerwenden Sie zwei Suchvorgänge für sortierte Daten mit fehlenden Werten.

Verwenden Sie INDEX und MATCH oder OFFSET anstelle von VLOOKUP

Versuchen Sie es mit demINDEXUndPASSENFunktionen stattSVERWEIS. ObwohlSVERWEISist etwas schneller (ungefähr 5 Prozent schneller), einfacher und benötigt weniger Speicher als eine Kombination davonPASSENUndINDEX, oderOFFSET, die zusätzliche Flexibilität, diePASSENUndINDEXMit unserem Angebot können Sie oft deutlich Zeit sparen. Sie können beispielsweise das Ergebnis einer exakten Messung speichernPASSENin einer Zelle speichern und in mehreren wiederverwendenINDEXAussagen.

DerINDEXDie Funktion ist schnell und eine nichtflüchtige Funktion, was die Neuberechnung beschleunigt. DerOFFSETFunktion ist auch schnell; Es handelt sich jedoch um eine flüchtige Funktion, die manchmal die für die Verarbeitung der Berechnungskette benötigte Zeit erheblich verlängert.

Es ist einfach zu konvertierenSVERWEISZuINDEXUndPASSEN. Die folgenden beiden Anweisungen geben dieselbe Antwort zurück:

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

Beschleunigen Sie Suchvorgänge

Da die Suche nach genauen Übereinstimmungen langsam sein kann, sollten Sie die folgenden Optionen zur Verbesserung der Leistung in Betracht ziehen:

• Verwenden Sie ein Arbeitsblatt. Es ist schneller, Suchvorgänge und Daten auf demselben Blatt zu halten.

  See Also

  Zahlen als Prozentsätze formatieren – Microsoft-SupportSo berechnen Sie den Prozentsatz in Excel – FormelbeispieleSo erstellen Sie ein Marksheet in Excel mit BeispielenMarkenblatt in Excel

• Wenn du kannst,SORTIERENdie Daten zuerst (SORTIERENist schnell) und verwenden Sie eine ungefähre Übereinstimmung.

• Wenn Sie eine exakte Übereinstimmungssuche verwenden müssen, beschränken Sie den Bereich der zu scannenden Zellen auf ein Minimum. Verwenden Sie Tabellen und strukturierte Referenzen oder dynamische Bereichsnamen, anstatt auf eine große Anzahl von Zeilen oder Spalten zu verweisen. Manchmal können Sie für die Suche eine untere und obere Bereichsgrenze vorab berechnen.

Verwenden Sie zwei Suchvorgänge für sortierte Daten mit fehlenden Werten

Bei einer Suche über mehr als ein paar Zeilen sind zwei ungefähre Übereinstimmungen deutlich schneller als eine genaue Übereinstimmung.(Der Breakeven-Punkt liegt bei etwa 10–20 Zeilen.)

Wenn Sie Ihre Daten sortieren können, aber dennoch keine ungefähre Übereinstimmung verwenden können, weil Sie nicht sicher sein können, dass der gesuchte Wert im Suchbereich 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“)

Der erste Teil der Formel führt eine ungefähre Suche in der Suchspalte selbst durch.

VLOOKUP(lookup_val ,lookup_array,1,True)

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

IF(SVERWEIS(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, dieses Mal jedoch die Antwort aus der gewünschten Spalte zurückgeben.

VLOOKUP(lookup_val, lookup_array, Spalte, True)

Wenn die Antwort aus der Nachschlagespalte nicht mit dem Nachschlagewert übereinstimmt, liegt ein fehlender Wert vor und die Formel gibt „notexist“ zurück.

Beachten Sie, dass Sie eine Fehlermeldung erhalten, wenn Sie nach einem Wert suchen, der kleiner als der kleinste Wert in der Liste ist. Sie können diesen Fehler beheben, indem Sie verwendenIFERRORoder durch Hinzufügen eines kleinen Testwerts zur Liste.

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

Wenn Sie die Suche nach exakter Übereinstimmung für unsortierte Daten verwenden müssen und nicht sicher sein können, ob der Suchwert vorhanden ist, müssen Sie häufig das #N/A verarbeiten, das zurückgegeben wird, wenn keine Übereinstimmung gefunden wird. Ab Excel 2007 können Sie das verwendenIFERRORFunktion, die sowohl einfach als auch schnell ist.

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

In früheren Versionen war die Verwendung von eine einfache, aber langsame MöglichkeitWENNFunktion, die zwei Suchvorgänge enthält.

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

Sie können die doppelte exakte Suche vermeiden, wenn Sie „exact“ verwendenPASSENSpeichern Sie das Ergebnis einmal in einer Zelle und testen Sie das Ergebnis, bevor Sie eine durchführenINDEX.

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

Wenn Sie nicht zwei Zellen verwenden können, verwenden SieZÄHLENWENN. Es ist im Allgemeinen schneller als eine exakte Übereinstimmungssuche.

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

Verwenden Sie MATCH und INDEX für exakte Übereinstimmungssuchen in mehreren Spalten

Sie können ein gespeichertes Exemplar oft wiederverwendenPASSENviele Male. Wenn Sie beispielsweise genaue Suchvorgänge für mehrere Ergebnisspalten durchführen, können Sie durch die Verwendung einer einzigen Spalte Zeit sparenPASSENund vieleINDEXAussagen statt vieleSVERWEISAussagen.

Fügen Sie eine zusätzliche Spalte für die hinzuPASSENum das Ergebnis zu speichern (gespeicherte_Zeile) und verwenden Sie für jede Ergebnisspalte Folgendes:

INDEX(Lookup_Range,stored_row,column_number)

Alternativ können Sie verwendenSVERWEISin einer Arrayformel. (Array-Formeln müssen mit Strg+-Umschalt+Eingabe eingegeben werden. Excel fügt { und } hinzu, um Ihnen zu zeigen, dass es sich um eine Array-Formel handelt.)

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

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

Sie können auch viele Zellen aus einem Suchvorgang zurückgeben. Um mehrere zusammenhängende Spalten nachzuschlagen, können Sie die verwendenINDEXFunktion in einer Array-Formel, um mehrere Spalten gleichzeitig zurückzugeben (verwenden Sie 0 als Spaltennummer). Sie können auch die verwendenINDEXFunktion, um mehrere Zeilen gleichzeitig zurückzugeben.

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

Dadurch wird Spalte A von der gespeicherten Zeile, die von einem vorherigen erstellt wurde, in Spalte J zurückgeführtPASSENStellungnahme.

Verwenden Sie MATCH, um einen rechteckigen Zellenblock zurückzugeben

Benutzen Sie diePASSENUndOFFSETFunktionen zum Zurückgeben eines rechteckigen Zellblocks.

Verwenden Sie MATCH und INDEX für die zweidimensionale Suche

Sie können eine zweidimensionale Tabellensuche effizient durchführen, indem Sie separate Suchvorgänge für die Zeilen und Spalten einer Tabelle verwenden, indem Sie eine verwendenINDEXFunktion mit zwei eingebettetenPASSENFunktionen, eine für die Zeile und eine für die Spalte.

Verwenden Sie einen Teilmengenbereich für die Suche nach mehreren Indizes

In großen Arbeitsblättern müssen Sie möglicherweise häufig mithilfe mehrerer Indizes nachschlagen, beispielsweise um nach Produktmengen in einem Land zu suchen. Dazu können Sie die Indizes verketten und die Suche mithilfe verketteter Suchwerte durchführen. Dies ist jedoch aus zwei Gründen ineffizient:

• Das Verketten von Zeichenfolgen ist ein rechenintensiver Vorgang.

• Die Suche deckt einen großen Bereich ab.

Oft ist es effizienter, einen Teilmengenbereich für die Suche zu berechnen (z. B. indem man die erste und letzte Zeile für das Land sucht und dann das Produkt innerhalb dieses Teilmengenbereichs nachschlägt).

Erwägen Sie Optionen für die dreidimensionale Suche

Um die Tabelle nachzuschlagen, die zusätzlich zur Zeile und Spalte verwendet werden soll, können Sie die folgenden Techniken verwenden, wobei der Schwerpunkt darauf liegt, wie Excel die Tabelle nachschlagen oder auswählen kann.

Wenn jede Tabelle, die Sie nachschlagen möchten (die dritte Dimension), als Satz benannter strukturierter Tabellen, Bereichsnamen oder als Tabelle mit Textzeichenfolgen, die Bereiche darstellen, gespeichert ist, können Sie möglicherweise die verwendenWÄHLENoderINDIREKTFunktionen.

• BenutzenWÄHLENund Bereichsnamen können eine effiziente Methode sein.WÄHLENist nicht volatil, eignet sich jedoch am besten für eine relativ kleine Anzahl von Tabellen. Dieses Beispiel verwendet dynamischTableLookup_Valueum auszuwählen, welcher Bereichsname (Tabellenname1, Tabellenname2, ...), die 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))

• Das folgende Beispiel verwendet dieINDIREKTFunktion undTableLookup_Valueum den Blattnamen dynamisch zu erstellen, der für die Nachschlagetabelle verwendet werden soll. Diese Methode hat den Vorteil, dass sie einfach ist und eine große Anzahl von Tabellen verarbeiten kann. WeilINDIREKTist eine flüchtige Single-Thread-Funktion. Die Suche wird bei jeder Berechnung im Single-Thread berechnet, auch wenn sich keine Daten geändert haben. Die Verwendung dieser Methode ist langsam.

  INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

• Sie können auch die verwendenSVERWEISFunktion, um den Namen des Blattes oder die Textzeichenfolge zu finden, die für die Tabelle verwendet werden soll, und verwenden Sie dann dieINDIREKTFunktion, um den resultierenden Text in einen Bereich umzuwandeln.

  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTables,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

Eine andere Technik besteht darin, alle Ihre Tabellen in einer riesigen Tabelle zusammenzufassen, die über eine zusätzliche Spalte verfügt, die die einzelnen Tabellen identifiziert. Anschließend können Sie die in den vorherigen Beispielen gezeigten Techniken für die Suche nach mehreren Indizes verwenden.

Verwenden Sie die Platzhaltersuche

DerPASSEN,SVERWEIS, UndHLOOKUPMit den Funktionen können Sie Platzhalterzeichen verwenden?(beliebiges einzelnes Zeichen) und*(kein Zeichen oder eine beliebige Anzahl von Zeichen) bei alphabetischen exakten Übereinstimmungen. Manchmal können Sie diese Methode verwenden, um mehrere Übereinstimmungen zu vermeiden.

Optimieren Sie Arrayformeln und SUMPRODUCT

Array-Formeln und dieSUMMENPRODUKTDie Funktionen sind leistungsstark, Sie müssen jedoch vorsichtig damit umgehen. Eine einzelne Array-Formel erfordert möglicherweise viele Berechnungen.

Der Schlüssel zur Optimierung der Berechnungsgeschwindigkeit von Array-Formeln besteht darin, sicherzustellen, dass die Anzahl der Zellen und Ausdrücke, die in der Array-Formel ausgewertet werden, so gering wie möglich ist. Denken Sie daran, dass eine Array-Formel ein bisschen wie eine flüchtige Formel ist: Wenn sich eine der Zellen, auf die sie verweist, geändert hat, flüchtig ist oder neu berechnet wurde, berechnet die Array-Formel alle Zellen in der Formel und wertet alle darin enthaltenen virtuellen Zellen aus muss die Berechnung durchführen.

So optimieren Sie die Berechnungsgeschwindigkeit von Array-Formeln:

• Nehmen Sie Ausdrücke und Bereichsverweise aus den Array-Formeln in separate Hilfsspalten und -zeilen auf. Dadurch wird der intelligente Neuberechnungsprozess in Excel viel besser genutzt.

• Verweisen Sie nicht auf vollständige Zeilen oder auf mehr Zeilen und Spalten, als Sie benötigen. Array-Formeln werden gezwungen, alle Zellbezüge in der Formel zu berechnen, auch wenn die Zellen leer oder unbenutzt sind. Da ab Excel 2007 eine Million Zeilen verfügbar sind, ist die Berechnung einer Array-Formel, die auf eine ganze Spalte verweist, äußerst langsam.

• Ab Excel 2007 verwenden Sie nach Möglichkeit strukturierte Bezüge, um die Anzahl der Zellen, die von der Array-Formel ausgewertet werden, auf ein Minimum zu beschränken.

• Verwenden Sie in Versionen vor Excel 2007 nach Möglichkeit dynamische Bereichsnamen. Obwohl sie volatil sind, lohnt es sich, weil sie die Größe der Spannen minimieren.

• Seien Sie vorsichtig bei Array-Formeln, die sowohl auf eine Zeile als auch auf eine Spalte verweisen: Dies erzwingt die Berechnung eines rechteckigen Bereichs.

• VerwendenSUMMENPRODUKTwenn möglich; es ist etwas schneller als die entsprechende Array-Formel.

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

Sie sollten immer die verwendenSUMIFS,ZÄHLENWENN, UndDURCHSCHNITTWENNVerwenden Sie Funktionen anstelle von Array-Formeln, wo Sie können, da diese viel schneller zu berechnen sind. Excel 2016 lässt sich schnell einführenMAXIFSUndMINIFSFunktionen.

In Versionen vor Excel 2007 werden Arrayformeln häufig verwendet, um eine Summe mit mehreren Bedingungen zu berechnen. Dies ist relativ einfach zu bewerkstelligen, insbesondere wenn Sie das verwendenAssistent für bedingte Summenin Excel, aber es ist oft langsam. Normalerweise gibt es viel schnellere Möglichkeiten, das gleiche Ergebnis zu erzielen. Wenn Sie nur wenige SUMs mit mehreren Bedingungen haben, können Sie möglicherweise die verwendenDSUMFunktion, die viel schneller ist als die entsprechende Array-Formel.

Wenn Sie Arrayformeln verwenden müssen, gibt es einige gute Methoden, diese zu beschleunigen:

• Verwenden Sie dynamische Bereichsnamen oder strukturierte Tabellenverweise, um die Anzahl der Zellen zu minimieren.

• Teilen Sie die mehreren Bedingungen in eine Spalte mit Hilfsformeln auf, die zurückgegeben werdenWAHRoderFALSCHfür jede Zeile und verweisen Sie dann auf die Hilfsspalte in aSUMIFoder Arrayformel. Dies scheint die Anzahl der Berechnungen für eine einzelne Array-Formel nicht zu verringern; Meistens 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, alle Bedingungen zu einer einzigen Bedingung zusammenzufassen und diese dann zu verwendenSUMIF.

• Wenn die Daten sortiert werden können, zählen Sie Gruppen von Zeilen und beschränken Sie die Array-Formeln auf die Betrachtung der Teilmengengruppen.

Priorisieren Sie SUMIFS, COUNTIFS und andere Funktionen der IFS-Familie mit mehreren Bedingungen

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

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

Ab Excel 2007 sollten Sie immer das verwendenSUMIFS,ZÄHLENWENN, UndDURCHSCHNITTWENNFunktionen und in Excel 2016MAXIFSUndMINIFSFunktionen stattSUMMENPRODUKTFormeln, wenn möglich.

In früheren Versionen bietet die Verwendung einige VorteileSUMMENPRODUKTanstattSUMMEArray-Formeln:

• SUMMENPRODUKTmuss nicht mit Strg+Umschalt+Eingabe in ein Array eingegeben werden.

• SUMMENPRODUKTist normalerweise etwas schneller (5 bis 10 Prozent).

VerwendenSUMMENPRODUKTfür Arrayformeln mit mehreren Bedingungen wie folgt:

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

In diesem Beispiel,Bedingung1UndBedingung2sind bedingte Ausdrücke wie z$A$1:$A$10000<=$Z4. Weil bedingte Ausdrücke zurückkehrenWAHRoderFALSCHAnstelle von Zahlen müssen sie zu Zahlen innerhalb der gezwungen werdenSUMMENPRODUKTFunktion. Sie können dies tun, indem Sie zwei Minuszeichen verwenden (--), oder durch Hinzufügen von 0 (+0), oder durch Multiplikation mit 1 (x1). Benutzen--ist etwas schneller als+0oderx1.

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

Sie können die darin enthaltenen Terme auch direkt multiplizierenSUMMENPRODUKTanstatt sie durch Kommas zu trennen:

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

Dies ist normalerweise etwas langsamer als die Verwendung der Kommasyntax und führt zu einer Fehlermeldung, wenn der zu summierende Bereich einen Textwert enthält. Es ist jedoch etwas flexibler, da der zu summierende Bereich beispielsweise mehrere Spalten haben kann, wenn die Bedingungen nur eine Spalte haben.

Verwenden Sie SUMPRODUCT, um Bereiche und Arrays zu multiplizieren und zu addieren

In Fällen wie gewichteten Durchschnittsberechnungen, bei denen Sie einen Zahlenbereich mit einem anderen Zahlenbereich multiplizieren und die Ergebnisse summieren müssen, verwenden Sie die Kommasyntax fürSUMMENPRODUKTkann 20 bis 25 Prozent schneller sein als ein Array-EintragSUMME.

{=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 führen alle zum gleichen Ergebnis, mit Ausnahme der dritten Formel, die die Komma-Syntax für verwendetSUMMENPRODUKTbenötigt nur etwa 77 Prozent der Berechnungszeit, die die beiden anderen Formeln benötigen.

Beachten Sie mögliche Hindernisse bei der Array- und Funktionsberechnung

Die Berechnungs-Engine in Excel ist für die Nutzung von Array-Formeln und Funktionen optimiert, die auf Bereiche verweisen. Einige ungewöhnliche Anordnungen dieser Formeln und Funktionen können jedoch manchmal, aber nicht immer, zu einer deutlich längeren Berechnungszeit führen.

Wenn Sie ein Rechenhindernis finden, das Arrayformeln und Bereichsfunktionen betrifft, sollten Sie auf Folgendes achten:

• Teilweise überlappende Referenzen.

• Arrayformeln und Bereichsfunktionen, die auf einen Teil eines Zellblocks verweisen, der in einer anderen Arrayformel oder Bereichsfunktion berechnet wird. Diese Situation kann bei der Zeitreihenanalyse häufig auftreten.

• Ein Formelsatz, der zeilenweise referenziert, und ein zweiter Formelsatz, der spaltenweise auf den ersten Satz verweist.

• Ein großer Satz einzeiliger Arrayformeln, die einen Spaltenblock abdecken, mitSUMMEFunktionen am Fuß jeder Spalte.

Funktionen effizient nutzen

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

Vermeiden Sie Single-Thread-Funktionen

Die meisten nativen Excel-Funktionen funktionieren gut mit Multithread-Berechnungen. Vermeiden Sie jedoch nach Möglichkeit die Verwendung der folgenden Single-Thread-Funktionen:

• Benutzerdefinierte VBA- und Automatisierungsfunktionen (UDFs), aber XLL-basierte UDFs können Multithreading sein
• PHONETISCH
• CELL, wenn entweder das Argument „format“ oder „address“ verwendet wird
• INDIREKT
• GETPIVOTDATA
• CUBEMEMBER
• WÜRFELWERT
• CUBEMEMBERPROPERTY
• WÜRFELSET
• CUBERANKEDMEMBER
• CUBEKPIMEMBER
• CUBESETCOUNT
• ADRESSE, wobei der fünfte Parameter (derBlattname) gegeben ist
• Jede Datenbankfunktion (DSUM, DAVERAGE usw.), die auf eine PivotTable verweist
• FEHLERTYP
• HYPERLINK

Verwenden Sie Tabellen für Funktionen, die Bereiche verarbeiten

Für Funktionen wieSUMME,SUMIF, UndSUMIFSdie Bereiche verarbeiten, ist die Berechnungszeit proportional zur Anzahl der verwendeten Zellen, die Sie summieren oder zählen. Nicht verwendete Zellen werden nicht untersucht, daher sind Verweise auf ganze Spalten relativ effizient. Es ist jedoch besser sicherzustellen, dass Sie nicht mehr verwendete Zellen einbeziehen, als Sie benötigen. Verwenden Sie Tabellen oder berechnen Sie Teilmengenbereiche oder Dynamikbereiche.

Reduzieren Sie flüchtige Funktionen

Flüchtige Funktionen können die Neuberechnung verlangsamen, da sie die Anzahl der Formeln erhöhen, die bei jeder Berechnung neu berechnet werden müssen.

Sie können die Anzahl der flüchtigen Funktionen häufig reduzieren, indem Sie verwendenINDEXanstattOFFSET, UndWÄHLENanstattINDIREKT. Jedoch,OFFSETist eine schnelle Funktion und kann oft auf kreative Weise verwendet werden, um schnelle Berechnungen zu ermöglichen.

Verwenden Sie benutzerdefinierte C- oder C++-Funktionen

Benutzerdefinierte Funktionen, die in C oder C++ programmiert sind und die C-API verwenden (XLL-Add-In-Funktionen), sind im Allgemeinen schneller als benutzerdefinierte Funktionen, die mithilfe von VBA oder Automatisierung (XLA- oder Automatisierungs-Add-Ins) entwickelt wurden. Weitere Informationen finden Sie unterEntwickeln von Excel 2010 XLLs.

Die Leistung benutzerdefinierter VBA-Funktionen hängt davon ab, wie Sie sie programmieren und aufrufen.

Verwenden Sie schnellere benutzerdefinierte VBA-Funktionen

Normalerweise ist es schneller, die Excel-Formelberechnungen und Arbeitsblattfunktionen zu verwenden, als die benutzerdefinierten VBA-Funktionen zu verwenden. Dies liegt daran, dass für jeden benutzerdefinierten Funktionsaufruf ein geringer Mehraufwand und für die Übertragung von Informationen von Excel an die benutzerdefinierte Funktion ein erheblicher Mehraufwand anfällt. Aber gut gestaltete und aufgerufene benutzerdefinierte Funktionen können viel schneller sein als komplexe Array-Formeln.

Stellen Sie sicher, dass Sie alle Verweise auf Arbeitsblattzellen in die Eingabeparameter der benutzerdefinierten Funktion und nicht in den Hauptteil der benutzerdefinierten Funktion eingefügt haben, damit Sie das Hinzufügen vermeiden könnenAnwendung.Volatilunnötigerweise.

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 über VBA initiiert wird. Benutzerdefinierte VBA-Funktionen berechnen viel langsamer, wenn die Berechnung erfolgtnichtvon VBA aufgerufen (z. B. im automatischen Modus oder wenn Sie im manuellen Modus F9 drücken). Dies gilt insbesondere dann, wenn der Visual Basic-Editor (Alt+F11) geöffnet ist oder in der aktuellen Excel-Sitzung geöffnet wurde.

Sie können F9 wie folgt abfangen und an eine VBA-Berechnungsunterroutine umleiten. Fügen Sie diese Unterroutine zum hinzuDieses ArbeitsbuchModul.

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 Automatisierungs-Add-Ins (Excel 2002 und spätere Versionen) verursachen keinen Overhead für den Visual Basic-Editor, da sie nicht den integrierten Editor verwenden. Andere Leistungsmerkmale der benutzerdefinierten Visual Basic 6-Funktionen in Automatisierungs-Add-Ins ähneln denen von VBA-Funktionen.

Wenn Ihre benutzerdefinierte Funktion jede Zelle in einem Bereich verarbeitet, deklarieren Sie die Eingabe als Bereich, weisen Sie sie einer Variante zu, die ein Array enthält, und führen Sie eine Schleife darüber aus. Wenn Sie Verweise auf ganze Spalten effizient verarbeiten möchten, müssen Sie eine Teilmenge des Eingabebereichs erstellen und diese wie in diesem Beispiel an der Schnittstelle mit dem verwendeten Bereich teilen.

Öffentliche Funktion 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 Nächste vCell-Endfunktion

Wenn Ihre benutzerdefinierte Funktion Arbeitsblattfunktionen oder Excel-Objektmodellmethoden zur Verarbeitung eines Bereichs verwendet, ist es im Allgemeinen effizienter, den Bereich als Objektvariable beizubehalten, als alle Daten von Excel an die benutzerdefinierte Funktion zu übertragen.

Funktion 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) Dann 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 erkennen, indem Sie den folgenden Test für leere Zellen verwenden, die eine Formel enthalten:

Wenn ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 dann

Für jeden Aufruf einer benutzerdefinierten Funktion und für jede Übertragung von Daten von Excel nach VBA entsteht ein Zeitaufwand. Manchmal kann Ihnen eine benutzerdefinierte Funktion mit einer Array-Formel mit mehreren Zellen dabei helfen, diesen Aufwand zu minimieren, indem Sie mehrere Funktionsaufrufe in einer einzigen Funktion mit einem Eingabebereich mit mehreren Zellen kombinieren, der eine Reihe von Antworten zurückgibt.

Minimieren Sie den Bereich der Zellen, auf die SUM und SUMIF verweisen

Das ExcelSUMMEUndSUMIFFunktionen werden häufig über eine große Anzahl von Zellen hinweg verwendet. Die Berechnungszeit für diese Funktionen ist proportional zur Anzahl der abgedeckten Zellen. Versuchen Sie daher, den Bereich der Zellen, auf die die Funktionen verweisen, zu minimieren.

Verwenden Sie Platzhalterfunktionen für SUMIF, ZÄHLENWENN, SUMIFS, ZÄHLENWENN und andere 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 derSUMIF,ZÄHLENWENN,SUMIFS,ZÄHLENWENN, und andereIFSFunktionen.

Wählen Sie die Methode für laufende und kumulative Summen

Es gibt zwei Methoden, um laufende oder kumulative Summen zu erstellen. Angenommen, die Zahlen, die Sie kumulieren möchtenSUMMEbefinden sich in Spalte A und Sie möchten, dass Spalte B die kumulative Summe enthält; Sie können einen der folgenden Schritte ausführen:

• Sie können in Spalte B eine Formel erstellen, z=SUMME($A$1:$A2)und ziehen Sie es so weit nach unten, wie Sie es benötigen. Die Anfangszelle der SUM ist in A1 verankert, aber da die Endzelle einen relativen Zeilenbezug hat, erhöht sich dieser automatisch für jede Zeile.

• Sie können eine Formel erstellen, z=$A1in Zelle B1 und=$B1+$A2in Zelle B2 und ziehen Sie es so weit nach unten, wie Sie es benötigen. Dadurch wird die kumulative Zelle berechnet, indem die Nummer dieser Zeile zur vorherigen kumulativen Nummer addiert wirdSUMME.

Bei 1.000 Zeilen führt Excel mit der ersten Methode etwa 500.000 Berechnungen durch, mit der zweiten Methode führt Excel jedoch nur etwa 2.000 Berechnungen durch.

Teilmengensummen berechnen

Wenn Sie über mehrere sortierte Indizes für eine Tabelle verfügen (z. B. „Site innerhalb des Bereichs“), können Sie häufig erhebliche Berechnungszeit sparen, indem Sie die Adresse eines Teilsatzbereichs von Zeilen (oder Spalten) zur Verwendung in der dynamisch berechnenSUMMEoderSUMIFFunktion.

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

1. Zählen Sie die Anzahl der Zeilen für jeden Teilmengenblock.

2. Addieren Sie die Anzahlen für jeden Block kumulativ, um seine Startzeile zu bestimmen.

3. VerwendenOFFSETmit der Startzeile und count, um einen Teilmengenbereich an den zurückzugebenSUMMEoderSUMIFdas deckt nur den Teilmengenblock von Zeilen ab.

Verwenden Sie ZWISCHENSUMME für gefilterte Listen

Benutzen Sie dieZWISCHENSUMMEFunktion zuSUMMEgefilterte Listen. DerZWISCHENSUMMEFunktion ist nützlich, weil im GegensatzSUMME, ignoriert es Folgendes:

• Ausgeblendete Zeilen, die beim Filtern einer Liste entstehen. Ab Excel 2003 können Sie auch erstellenZWISCHENSUMMEIgnorieren Sie alle ausgeblendeten Zeilen, nicht nur gefilterte Zeilen.

• AndereZWISCHENSUMMEFunktionen.

Verwenden Sie die AGGREGATE-Funktion

Die AGGREGATE-Funktion ist eine leistungsstarke und effiziente Methode zur Berechnung von 19 verschiedenen Methoden zur Aggregation von Daten (z. BSUMME,MEDIAN,PERZENTILUndGROSS).AGGREGATverfügt über Optionen zum Ignorieren ausgeblendeter oder gefilterter Zeilen, Fehlerwerte und verschachtelter ZeilenZWISCHENSUMMEUndAGGREGATFunktionen.

Vermeiden Sie die Verwendung von DFunctions

Die DFunktionenDSUM,ANZAHL,DAVERAGEusw. sind deutlich schneller als entsprechende Arrayformeln. Der Nachteil der DFunctions besteht darin, dass die Kriterien in einem separaten Bereich liegen müssen, was ihre Verwendung und Wartung unter vielen Umständen unpraktisch macht. Ab Excel 2007 sollten Sie verwendenSUMIFS,ZÄHLENWENN, UndDURCHSCHNITTWENNFunktionen anstelle der DFunctions.

Erstellen Sie schnellere VBA-Makros

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

Schalten Sie alles außer dem Wesentlichen aus, während der Code ausgeführt wird

Um die Leistung von VBA-Makros zu verbessern, deaktivieren Sie explizit die Funktionalität, die während der Codeausführung nicht benötigt wird. Oft ist nur eine Neuberechnung oder ein Neuzeichnen nach der Ausführung Ihres Codes erforderlich und kann die Leistung verbessern. Nachdem Ihr Code ausgeführt wurde, stellen Sie die Funktionalität in ihren ursprünglichen Zustand zurück.

Die folgende Funktionalität kann normalerweise deaktiviert werden, während Ihr VBA-Makro ausgeführt wird:

• Application.ScreenUpdatingDeaktivieren Sie die Bildschirmaktualisierung. WennApplication.ScreenUpdatingist eingestellt aufFALSCH, Excel zeichnet den Bildschirm nicht neu. Während Ihr Code ausgeführt wird, wird der Bildschirm schnell aktualisiert und es ist normalerweise nicht erforderlich, dass der Benutzer jede Aktualisierung sieht. Durch einmaliges Aktualisieren des Bildschirms nach der Codeausführung wird die Leistung verbessert.

• Application.DisplayStatusBarSchalten Sie die Statusleiste aus. WennApplication.DisplayStatusBarist eingestellt aufFALSCH, Excel zeigt die Statusleiste nicht an. Die Statusleisteneinstellung ist von der Bildschirmaktualisierungseinstellung getrennt, sodass Sie den Status des aktuellen Vorgangs auch dann anzeigen können, wenn der Bildschirm nicht aktualisiert wird. Wenn Sie jedoch nicht den Status jedes Vorgangs anzeigen müssen, verbessert das Ausschalten der Statusleiste während der Codeausführung auch die Leistung.

• Anwendung.BerechnungWechseln Sie zur manuellen Berechnung. WennAnwendung.Berechnungist eingestellt aufxlBerechnungshandbuch, berechnet Excel die Arbeitsmappe nur, wenn der Benutzer die Berechnung explizit anstößt. Im automatischen Berechnungsmodus bestimmt Excel, wann berechnet werden soll. Jedes Mal, wenn sich beispielsweise ein Zellenwert ändert, der sich auf eine Formel bezieht, berechnet Excel die Formel neu. Wenn Sie den Berechnungsmodus auf „Manuell“ umstellen, können Sie warten, bis alle mit der Formel verknüpften Zellen aktualisiert sind, bevor Sie die Arbeitsmappe neu berechnen. Indem Sie die Arbeitsmappe nur bei Bedarf neu berechnen, während Ihr Code ausgeführt wird, können Sie die Leistung verbessern.

• Application.EnableEventsSchalten Sie Ereignisse aus. WennApplication.EnableEventsist eingestellt aufFALSCH, Excel löst keine Ereignisse aus. Wenn Add-Ins vorhanden sind, die auf Excel-Ereignisse warten, verbrauchen diese Add-Ins beim Aufzeichnen der Ereignisse Ressourcen auf dem Computer. Wenn es nicht erforderlich ist, dass das Add-In die Ereignisse aufzeichnet, die während der Ausführung Ihres Codes auftreten, verbessert das Deaktivieren von Ereignissen die Leistung.

• ActiveSheet.DisplayPageBreaksSeitenumbrüche deaktivieren. WennActiveSheet.DisplayPageBreaksist eingestellt aufFALSCH, Excel zeigt keine Seitenumbrüche an. Es ist nicht erforderlich, Seitenumbrüche während der Codeausführung neu zu berechnen, und die Berechnung der Seitenumbrüche nach der Codeausführung verbessert die Leistung.

Das folgende Beispiel zeigt die Funktionalität, die Sie während der Ausführung Ihres VBA-Makros deaktivieren können.

' Speichern Sie den aktuellen Stand der Excel-Einstellungen. screenUpdateState = Application.ScreenUpdating statusBarState = Application.DisplayStatusBar calcState = Application.Calculation eventsState = Application.EnableEvents ' Hinweis: Dies ist eine Einstellung auf Blattebene. displayPageBreakState = ActiveSheet.DisplayPageBreaks ' Deaktivieren Sie die Excel-Funktionalität, um die Leistung zu verbessern. Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' Hinweis: Dies ist eine Einstellung auf Blattebene. ActiveSheet.DisplayPageBreaks = False ' Fügen Sie hier Ihren Code ein. ' Excel-Einstellungen auf den ursprünglichen Zustand zurücksetzen. Application.ScreenUpdating = screenUpdateState Application.DisplayStatusBar = statusBarState Application.Calculation = calcState Application.EnableEvents = eventsState ' Hinweis: Dies ist eine Einstellung auf Blattebene ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Lesen und schreiben Sie große Datenblöcke in einem einzigen Vorgang

Optimieren Sie Ihren Code, indem Sie die Anzahl der Datenübertragungen zwischen Excel und Ihrem Code explizit reduzieren. Anstatt Zellen einzeln zu durchlaufen, um einen Wert abzurufen oder festzulegen, können Sie die Werte im gesamten Zellbereich in einer Zeile abrufen oder festlegen, indem Sie eine Variante verwenden, die ein zweidimensionales Array enthält, um Werte nach Bedarf zu speichern. Die folgenden Codebeispiele vergleichen diese beiden Methoden.

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

Dimmen Sie DataRange als Bereich. Dimmen Sie Irow als Lang. Dimmen Sie Icol als Ganzzahl. Dimmen Sie MyVar als Double. Set DataRange=Range("A1:C10000") Für Irow=1 bis 10000 Für icol=1 bis 3 ' Lesen Sie die Werte 30.000 Mal aus dem Excel-Raster. MyVar=DataRange(Irow,Icol) Wenn MyVar > 0, dann ' Wert ändern. MyVar=MyVar*Myvar ' Schreiben Sie die Werte 30.000 Mal zurück in das Excel-Raster. 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 ' Alle Werte auf einmal aus dem Excel-Raster lesen und in ein Array einfügen. DataRange = Range("A1:C10000").Value2 Für Irow = 1 bis 10000 für Icol = 1 bis 3 MyVar = DataRange(Irow, Icol) Wenn MyVar > 0, dann ' Ändern Sie die Werte im Array. MyVar=MyVar*Myvar DataRange(Irow, Icol) = MyVar End If Next Icol Next Irow ' Alle Werte auf einmal zurück in den Bereich schreiben. Range("A1:C10000").Value2 = DataRange

Verwenden Sie .Value2 anstelle von .Value oder .Text, wenn Sie Daten aus einem Excel-Bereich lesen

• .Textgibt den formatierten Wert einer Zelle zurück. Dies ist langsam, kann ### zurückgeben, wenn der Benutzer zoomt, und kann zu Präzisionsverlusten führen.
• .WertGibt eine VBA-Währungs- oder VBA-Datumsvariable zurück, wenn der Bereich als Datum oder Währung formatiert wurde. Dies ist langsam, kann an Präzision verlieren und beim Aufrufen von Arbeitsblattfunktionen zu Fehlern führen.
• .Wert2ist schnell und verändert die aus Excel abgerufenen Daten nicht.

Vermeiden Sie das Auswählen und Aktivieren von Objekten

Das Auswählen und Aktivieren von Objekten ist verarbeitungsintensiver als das direkte Referenzieren von Objekten. Durch Verweisen auf ein Objekt wie zBereichoder einFormDirekt können Sie die Leistung verbessern. Die folgenden Codebeispiele vergleichen die beiden Methoden.

Das folgende Codebeispiel zeigt nicht optimierten Code, der jede Form auf dem aktiven Blatt auswählt und den Text in „Hallo“ ändert.

Für i = 0 Zu ActiveSheet.Shapes.Count ActiveSheet.Shapes(i).Select Selection.Text = "Hallo" Weiter i

Das folgende Codebeispiel zeigt optimierten Code, der direkt auf jede Form verweist und den Text in „Hallo“ ändert.

Für i = 0 To ActiveSheet.Shapes.Count ActiveSheet.Shapes(i).TextEffect.Text = "Hallo" Weiter i

Nutzen Sie diese zusätzlichen VBA-Leistungsoptimierungen

Im Folgenden finden Sie eine Liste zusätzlicher Leistungsoptimierungen, die Sie in Ihrem VBA-Code verwenden können:

• Geben Sie Ergebnisse zurück, indem Sie a ein Array direkt zuweisenBereich.

• Deklarieren Sie Variablen mit expliziten Typen, um den Aufwand zu vermeiden, den Datentyp während der Codeausführung möglicherweise mehrmals in einer Schleife zu bestimmen.

• Für einfache Funktionen, die Sie häufig in Ihrem Code verwenden, implementieren Sie die Funktionen selbst in VBA, anstatt das zu verwendenArbeitsblattfunktionObjekt. Weitere Informationen finden Sie unterVerwenden Sie schnellere benutzerdefinierte VBA-Funktionen.

• Benutzen Sie dieRange.SpecialCells-Methode, um die Anzahl der Zellen zu verringern, 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 unterExcel 2010 XLL SDK-Dokumentation.

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

Ab Excel 2007 enthält Excel im Vergleich zu früheren Versionen eine große Vielfalt an Dateiformaten. Abgesehen von den Dateiformatvarianten Makro, Vorlage, Add-In, PDF und XPS sind die drei Hauptformate XLS, XLSB und XLSX.

• 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 durch. Die Dateigröße ist fast dieselbe wie bei früheren Versionen (einige zusätzliche Informationen können gespeichert werden) und die Leistung ist etwas langsamer als bei früheren Versionen. Jegliche Multithread-Optimierung, die Excel in Bezug auf die Reihenfolge der Zellenberechnung durchführt, wird nicht im XLS-Format gespeichert. Daher kann die Berechnung einer Arbeitsmappe langsamer sein, nachdem die Arbeitsmappe im XLS-Format gespeichert, geschlossen und erneut geöffnet wurde.

• 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, der Grad der Komprimierung hängt jedoch vom Inhalt der Arbeitsmappe ab. Beispielsweise weisen zehn Arbeitsmappen einen Größenverkleinerungsfaktor zwischen zwei und acht auf, mit einem durchschnittlichen Verkleinerungsfaktor von vier. Ab Excel 2007 ist die Leistung beim Öffnen und Speichern nur geringfügig langsamer als beim XLS-Format.

• XLSX-Format

  XLSX ist das XML-Format ab Excel 2007 und das Standardformat ab Excel 2007. 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 prüfen Sie den Inhalt). Typischerweise erstellt das XLSX-Format größere Dateien als das XLSB-Format (im Durchschnitt 1,5-mal größer), aber sie sind immer noch deutlich kleiner als die XLS-Dateien. Sie sollten damit rechnen, dass die Öffnungs- und Speicherzeiten etwas länger sind als bei XLSB-Dateien.

Arbeitsmappen öffnen, schließen und speichern

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

Wenn eine oder mehrere Ihrer Arbeitsmappen langsamer als angemessen geöffnet und geschlossen werden, kann dies an einem der folgenden Probleme liegen.

• Temporäre Dateien

  Temporäre Dateien können sich in Ihrem Verzeichnis \Windows\Temp (in Windows 95, Windows 98 und Windows ME) oder Ihrem 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. Auch Softwareinstallationsprogramme erstellen 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, deshalb sollten Sie sie gelegentlich bereinigen. Wenn Sie jedoch Software installiert haben, die einen Neustart Ihres Computers erfordert, und Sie dies noch nicht getan haben, sollten Sie vor dem Löschen der temporären Dateien einen Neustart durchführen.

Eine einfache Möglichkeit, Ihr temporäres Verzeichnis zu öffnen, ist WindowsStartMenü: KlickenStart, und klicken Sie dannLaufen. Geben Sie im Textfeld Folgendes ein:%temp%, und klicken Sie dannOK.

• Nachverfolgen von Änderungen in einer freigegebenen Arbeitsmappe

  Das Verfolgen von Änderungen in einer freigegebenen Arbeitsmappe führt dazu, dass die Dateigröße Ihrer Arbeitsmappe schnell zunimmt.

• Fragmentierte Auslagerungsdatei

  Stellen Sie sicher, dass sich Ihre Windows-Auslagerungsdatei auf einer Festplatte mit viel Speicherplatz befindet und dass Sie die Festplatte regelmäßig defragmentieren.

• Arbeitsmappe mit passwortgeschützter Struktur

  Eine Arbeitsmappe, deren Struktur durch ein Kennwort geschützt ist (WerkzeugMenü >Schutz>Arbeitsmappe schützen> Geben Sie das optionale Passwort ein) öffnet und schließt sich viel langsamer als eine, die ohne das optionale Passwort geschützt ist.

• Probleme mit der verwendeten Reichweite

  Übergroße verwendete Bereiche können zu einem langsamen Öffnen und einer erhöhten Dateigröße führen, insbesondere wenn sie durch ausgeblendete Zeilen oder Spalten verursacht werden, deren Höhe oder Breite nicht dem Standard entspricht. Weitere Informationen zu Problemen mit dem verwendeten Bereich finden Sie unterMinimieren Sie den genutzten Bereich.

• 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 zu Problemen beim Öffnen oder Speichern einer Arbeitsmappe in einem WAN (oder sogar einem LAN) führen. Wenn Sie dieses Problem haben, sollten Sie über eine Neugestaltung Ihrer Arbeitsmappe nachdenken.

• Zahlreiche Links zu anderen Arbeitsmappen

  Öffnen Sie nach Möglichkeit die Arbeitsmappen, zu denen Sie eine Verknüpfung herstellen, bevor Sie die Arbeitsmappe öffnen, die die Verknüpfungen enthält. Oft ist es schneller, eine Arbeitsmappe zu öffnen, als die Links aus einer geschlossenen Arbeitsmappe zu lesen.

• Einstellungen des Virenscanners

  Einige Einstellungen des Virenscanners können insbesondere auf einem Server zu Problemen oder Verlangsamungen beim Öffnen, Schließen oder Speichern führen. Wenn Sie glauben, dass dies das Problem sein könnte, versuchen Sie, den Virenscanner vorübergehend auszuschalten.

• Langsame Berechnung führt zu langsamem Öffnen und Speichern

  Unter bestimmten Umständen berechnet Excel Ihre Arbeitsmappe neu, wenn es sie öffnet oder speichert. Wenn die Berechnungszeit für Ihre Arbeitsmappe lang ist und ein Problem verursacht, stellen Sie sicher, dass die Berechnung auf eingestellt istHandbuch, und erwägen Sie, das auszuschaltenVor dem Speichern berechnenMöglichkeit (Werkzeug>Optionen>Berechnung).

• Symbolleistendateien (.xlb)

  Überprüfen Sie die Größe Ihrer Symbolleistendatei. Eine typische Symbolleistendatei ist zwischen 10 KB und 20 KB groß. Sie können Ihre XLB-Dateien finden, indem Sie nach suchen*.xlbmithilfe der Windows-Suche. Jeder Benutzer verfügt über eine eindeutige XLB-Datei. Durch das Hinzufügen, Ändern oder Anpassen von Symbolleisten erhöht sich die Größe Ihrer Datei „toolbar.xlb“. Durch das Löschen der Datei werden alle Ihre Symbolleistenanpassungen entfernt (die Umbenennung in „toolbar.OLD“ ist sicherer). Beim nächsten Öffnen von Excel wird eine neue XLB-Datei erstellt.

Nehmen Sie zusätzliche Leistungsoptimierungen vor

In den folgenden Bereichen können Sie Leistungsverbesserungen vornehmen.

• PivotTables

  PivotTables bieten eine effiziente Möglichkeit, große Datenmengen zusammenzufassen.

  • Summen als Endergebnisse. Wenn Sie Gesamt- und Zwischensummen als Teil der Endergebnisse Ihrer Arbeitsmappe erstellen müssen, versuchen Sie es mit PivotTables.

  • Summen als Zwischenergebnisse. PivotTables sind eine großartige Möglichkeit, zusammenfassende Berichte zu erstellen. Vermeiden Sie jedoch die Erstellung von Formeln, die PivotTable-Ergebnisse als Zwischensummen und Zwischensummen in Ihrer Berechnungskette verwenden, es sei denn, Sie können die folgenden Bedingungen sicherstellen:

  • Die PivotTable wurde während der Berechnung korrekt aktualisiert.

  • Die PivotTable wurde nicht verändert, sodass die Informationen weiterhin sichtbar sind.

  Wenn Sie dennoch PivotTables als Zwischenergebnisse verwenden möchten, verwenden Sie dieGETPIVOTDATAFunktion.

• Bedingte Formate und Datenvalidierung

  Bedingte Formate und Datenvalidierung sind großartig, aber die Verwendung vieler davon kann die Berechnung erheblich 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 verfügt über eineWorksheet.EnableFormatConditionsCalculationEigenschaft, damit Sie die Berechnung bedingter Formate aktivieren oder deaktivieren können.

• Definierte Namen

  Definierte Namen gehören zu den leistungsstärksten Funktionen in Excel, nehmen jedoch zusätzliche Rechenzeit in Anspruch. Die Verwendung von Namen, die auf andere Arbeitsblätter verweisen, erhöht die Komplexität des Berechnungsprozesses. Außerdem sollten Sie versuchen, verschachtelte Namen (Namen, die auf andere Namen verweisen) zu vermeiden.

  Da Namen jedes Mal berechnet werden, wenn eine auf sie verweisende Formel berechnet wird, sollten Sie es vermeiden, rechenintensive Formeln oder Funktionen in definierte Namen einzufügen. In diesen Fällen kann es wesentlich schneller sein, Ihre rechenintensive Formel oder Funktion irgendwo in einer Ersatzzelle abzulegen und stattdessen auf diese Zelle zu verweisen, entweder direkt oder über einen Namen.

• Formeln, die nur gelegentlich verwendet werden

  Viele Arbeitsmappen enthalten eine beträchtliche Anzahl von Formeln und Suchvorgängen, die sich damit befassen, die Eingabedaten für die Berechnungen in die richtige Form zu bringen, oder die als Abwehrmaßnahmen gegen Änderungen in der Größe oder Form der Daten verwendet werden. Wenn Sie Formelblöcke haben, die nur gelegentlich verwendet werden, können Sie spezielle Werte kopieren und einfügen, um die Formeln vorübergehend zu entfernen, oder Sie können sie in eine separate, selten geöffnete Arbeitsmappe einfügen. Da Arbeitsblattfehler häufig dadurch verursacht werden, dass nicht bemerkt wird, dass Formeln in Werte konvertiert wurden, ist die Methode der separaten Arbeitsmappe möglicherweise vorzuziehen.

• Verwenden Sie ausreichend Speicher

  Die 32-Bit-Version von Excel kann bis zu 2 GB RAM oder bis zu 4 GB RAM für Large Address Aware 32-Bit-Versionen von Excel 2013 und 2016 verwenden. Allerdings benötigt der Computer, auf dem Excel ausgeführt wird, auch Speicherressourcen. Wenn Sie also nur über 2 GB RAM auf Ihrem Computer verfügen, kann Excel nicht die vollen 2 GB nutzen, da ein Teil des Speichers dem Betriebssystem und anderen ausgeführten Programmen zugewiesen ist. Um die Leistung von Excel auf einem 32-Bit-Computer zu optimieren, empfehlen wir, dass der Computer über mindestens 3 GB RAM verfügt.

  In der 64-Bit-Version von Excel gibt es keine Beschränkung auf 2 GB oder bis zu 4 GB. Weitere Informationen finden Sie im Abschnitt „Große Datensätze und die 64-Bit-Version von Excel“ inExcel-Leistung: Leistungs- und Limitverbesserungen.

Abschluss

In diesem Artikel wurden Möglichkeiten zur Optimierung der Excel-Funktionalität wie Links, Suchvorgänge, Formeln, Funktionen und VBA-Code beschrieben, um häufige Hindernisse zu vermeiden und die Leistung zu verbessern.

Siehe auch

• Excel-Leistung: Verbesserung der Berechnungsleistung
• Excel-Leistung: Leistungs- und Limitverbesserungen
• Excel-Entwicklerportal

Unterstützung und Feedback

Haben Sie Fragen oder Feedback zu Office VBA oder dieser Dokumentation? Bitte sehenUnterstützung und Feedback zu Office VBAHier finden Sie Hinweise dazu, wie Sie Unterstützung erhalten und Feedback geben können.