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

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

Folgen Sie diesen Tipps zum Optimieren vieler häufig auftretender Leistungshindernisse in Excel.

Erfahren Sie, wie Sie die Leistung im Zusammenhang mit Arten von Verweisen und Links verbessern.

Vorwärtsverweisen und Rückwärtsverweisen nicht verwenden

Um die Übersichtlichkeit zu erhöhen und Fehler zu vermeiden, entwerfen Sie Ihre Formeln so, dass sie nicht (nach rechts oder unten) auf andere Formeln oder Zellen verweisen. Vorwärtsverweisen wirkt sich in der Regel nicht auf die Berechnungsleistung aus, außer in extremen Fällen für die erste Berechnung einer Arbeitsmappe, bei der es länger dauern kann, eine sinnvolle Berechnungssequenz einzurichten, wenn viele Formeln ihre Berechnung zurückstellen müssen.

Minimieren der Verwendung von Zirkelbezügen mit Iteration

Die Berechnung 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 Xaml "aufheben", sodass eine iterative Berechnung nicht mehr erforderlich ist. Versuchen Sie z. B. bei Cashflow- und Zinsberechnungen, den Cashflow vor dem Zinsvorgang zu berechnen, die Zinsen zu berechnen und dann den Cashflow einschließlich der Zinsen zu berechnen.

Excel berechnet Zirkelverweise blattweise, 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 kreisförmigen Berechnung und die Von diesen Zellen benötigte Berechnungszeit zu reduzieren.

Vermeiden Sie arbeitsmappenübergreifende Verknüpfungen, wenn dies möglich ist. Sie können langsam, leicht beschädigt und nicht immer leicht zu finden und zu beheben sein.

Das Arbeiten mit wenigen größeren Arbeitsmappen ist normalerweise, aber nicht immer, besser als das Arbeiten mit vielen kleineren Arbeitsmappen. Einige Ausnahmen hierfür 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 genügend ARBEITSSPEICHER haben.

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 haben, was häufig für das Debuggen und Überwachen der Arbeitsmappe wichtig ist.

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.

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, Informationen nur 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 exzessiv ist, sollten Sie erwägen, alle Zeilen und Spalten unten und rechts neben Ihrer tatsächlich zuletzt verwendeten Zelle zu löschen und dann die Arbeitsmappe zu speichern. Erstellen Sie zuerst 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 #N/A geändert.

Zulassen zusätzlicher Daten

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.

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

Diese Lösung hat mehrere Vorteile:

  • Es gibt weniger Leistungsnachteile als die Alternativen von verweisenden und dynamischen Bereichen ganzer Spalten.

  • Es ist einfach, mehrere Datentabellen auf einem einzigen 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.

Eine alternative Möglichkeit 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 beliebig viele Daten hinzufügen, 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 die zuletzt verwendete Zeile in der Spalte nicht automatisch 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 mehrere Datentabellen auf einem einzelnen Arbeitsblatt vorhanden sind.

  • In Excel 2007 und neueren Versionen können Arrayformeln Verweise auf ganze Spalten verarbeiten, dies erzwingt jedoch die Berechnung aller Zellen in der Spalte, einschließlich leerer Zellen. Dies kann langsam berechnet werden, insbesondere bei 1 Million Zeilen.

Alternativ können Sie dynamische Bereiche verwenden.

Mithilfe der Offset - oder INDEX - und COUNTA-Funktionen in der Definition eines benannten Bereichs können Sie den Bereich, auf den sich der benannte Bereich bezieht, dynamisch erweitern und verknappen. Erstellen Sie beispielsweise einen definierten Namen mithilfe 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 im Allgemeinen der OFFSET-Formel vorzuziehen, da OFFSET den Nachteil hat, eine veränderliche Funktion zu sein, die bei jeder Neuberechnung berechnet wird.

Die Leistung wird verringert, 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, indirect ist jedoch veränderlich 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 Nachschlagevorgänge 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 Übereinstimmungstyp- und Range-Lookup-Optionen 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 kleiner oder gleich dem Nachschlagewert zurück, wenn das Nachschlagearray aufsteigend sortiert ist (ungefähre Übereinstimmung). Wenn das Sucharray nicht aufsteigend sortiert ist, gibt MATCH eine falsche Antwort zurück. Die Standardoption ist eine ungefähre Übereinstimmung, die aufsteigend sortiert ist.

  • 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 jedoch eine genaue Übereinstimmung wünschen, lesen Sie zwei Nachschlagevorgänge für sortierte Daten mit fehlenden Werten.

Verwenden von INDEX und MATCH oder OFFSET anstelle von VLOOKUP

Versuchen Sie, anstelle von VLOOKUP die Funktionen INDEX und MATCH zu verwenden. VLOOKUP ist zwar etwas schneller (ca. 5 Prozent schneller), einfacher und verwendet weniger Arbeitsspeicher als eine Kombination aus MATCH und INDEX oder OFFSET, aber die zusätzliche Flexibilität, die MATCH und INDEX bieten, ermöglicht es Ihnen häufig, zeitsparend zu sparen. Sie können z. B. 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änderlich Funktion, die manchmal den Zeitaufwand für die Verarbeitung der Berechnungskette erheblich erhöht.

Es ist einfach, VLOOKUP in INDEX und MATCH zu konvertieren. Die folgenden beiden Aussagen geben die gleiche 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 Nachschlagevorgänge

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 Nachschlagewerten 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 dennoch keine ungefähre Übereinstimmung verwenden können, da 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)

Mit der folgenden Formel können Sie ü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 die Antwort aus der gewünschten Spalte zurückgeben.

  VLOOKUP(lookup_val, lookup_array, column, True)

Wenn die Antwort aus der Nachschlagespalte nicht mit dem Nachschlagewert übereinstimmte, haben Sie einen fehlenden Wert, und die Formel gibt "nicht vorhanden" 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 eine genaue Übereinstimmungssuche für unsortierte Daten verwenden müssen und 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 einfache und schnelle IFERROR-Funktion verwenden.

  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 nicht zwei Zellen verwenden können, verwenden Sie COUNTIF. Es ist in der Regel schneller als eine genaue Übereinstimmungssuche.

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

Verwenden von MATCH und INDEX für genaue Übereinstimmungssuchvorgänge 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 zum Speichern des Ergebnisses (stored_row) hinzu, 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 {und } hinzu, um anzuzeigen, 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 Match - und OFFSET-Funktionen , um einen rechteckigen Zellenblock zurückzugeben.

Verwenden von MATCH und INDEX für die 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 Teilbereichs für die Suche mit mehreren Indizes

In großen Arbeitsblättern müssen Sie möglicherweise häufig mithilfe mehrerer Indizes nachschlagen, z. B. nach Produktvolumes in einem Land. Dazu können Sie die Indizes verketten und die Suche mithilfe verketteter Nachschlagewerte ausfü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 häufig effizienter, einen Teilbereich für die Suche zu berechnen (z. B. indem Sie die erste und letzte Zeile für das Land suchen und dann das Produkt innerhalb dieses Teilbereichs nachschlagen).

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 nachschlagen 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 TableLookup_Value ausgewählt, welcher Bereichsname (TableName1, TableName2, ...) 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 verwendet und TableLookup_Value der Blattname dynamisch erstellt, 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 veränderlich singlethreaded-Funktion ist, wird die Suche bei jeder Berechnung mit einem einzigen 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 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 der Platzhaltersuche

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

Optimieren von Arrayformeln und SUMPRODUCT

Arrayformeln und die SUMPRODUCT-Funktion sind leistungsstark, sie müssen jedoch sorgfältig behandelt werden. 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 verändere Formel ist: Wenn sich eine der Zellen, auf die sie verweist, geändert hat, veränderisch 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 auf 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 sind. Wenn ab Excel 2007 1 Million Zeilen verfügbar sind, ist eine Arrayformel, die auf eine ganze Spalte verweist, extrem langsam zu berechnen.

  • 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 Versionen vor 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 SUM 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 Summe in Excel verwenden, der jedoch häufig langsam ist. In der Regel gibt es viel schnellere Möglichkeiten, dasselbe Ergebnis zu erhalten. Wenn Sie nur wenige SUMs mit mehreren Bedingungen haben, können Sie möglicherweise die DSUM-Funktion verwenden, die viel schneller als die entsprechende Arrayformel ist.

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 mehreren Bedingungen in eine Spalte mit Hilfsformeln auf, die für jede Zeile True oder False zurückgeben, und verweisen Sie dann in einer SUMIF - oder Arrayformel auf die Hilfsspalte. Dies scheint die Anzahl der Berechnungen für eine einzelne Arrayformel möglicherweise nicht zu verringern. Die meiste Zeit 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 Untergruppen.

Priorisieren von SUMIFS-, COUNTIFS- und anderen IFS-Family-Funktionen mit mehreren Bedingungen

Diese Funktionen werten alle Bedingungen von links nach rechts aus. Daher ist es effizienter, zuerst die restriktivste Bedingung zu platzieren, 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 und in Excel 2016 MAXIFS- und MINIFS-Funktionen anstelle von SUMPRODUCT-Formeln verwenden, wenn möglich.

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

  • SUMPRODUCT muss nicht per Array eingegeben werden, indem STRG+UMSCHALT+EINGABETASTE verwendet wird.

  • 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 handelt es Condition1 sich um 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 -- 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 Ausdrücke auch direkt innerhalb von 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 es wird ein Fehler ausgegeben, 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 haben.

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, nimmt nur etwa 77 Prozent der Berechnungszeit in Anspruch, die die anderen beiden Formeln benötigen.

Beachten Sie potenzielle 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 systemeigenen Excel-Funktionen funktionieren gut mit Multithreadberechnungen. Vermeiden Sie nach Möglichkeit jedoch die folgenden Singlethread-Funktionen:

  • Benutzerdefinierte VBA- und Automatisierungsfunktionen (User-Defined Functions, UDFs), XLL-basierte UDFs können jedoch multithreaded 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 verarbeiten, ist die Berechnungszeit proportional zur Anzahl der verwendeten Zellen, die Summierung oder Zählung. Nicht verwendete Zellen werden nicht untersucht, sodass Verweise auf ganze Spalten relativ effizient sind, es ist jedoch besser, sicherzustellen, dass Sie nicht mehr verwendete Zellen einschließen, als Sie benötigen. Verwenden Sie Tabellen, oder berechnen Sie Teilbereiche oder dynamische Bereiche.

Reduzieren veränderrer 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, führen in der Regel schneller aus als benutzerdefinierte Funktionen, die mithilfe von VBA oder Automatisierung (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 schneller benutzerdefinierter VBA-Funktionen

In der Regel ist es 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) geöffnet ist oder in der aktuellen Excel Sitzung 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 Automatisierungs-Add-Ins (Excel 2002 und höher) verursachen keinen Visual Basic Editor-Overhead, da sie nicht den integrierten Editor 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 früh 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:

  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 an VBA ist ein Zeitaufwand vorhanden. 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 PLATZHALTER-SUMIF-, COUNTIF-, SUMIFS-, COUNTIFS- und anderen IFS-Funktionen

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

Methode für Zeitraum bis Datum und kumulierte SUMs auswählen

Es gibt zwei Methoden zum Ausführen von Zeit-zu-Datum- oder kumulierten SUMs. Angenommen, die Zahlen, die Sie kumulativ summen möchten, 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 nach Bedarf nach unten ziehen. 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 erstellen, z =$A1 . B. in Zelle B1 und =$B1+$A2 in Zelle B2, und sie nach Bedarf nach unten ziehen. 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 Teilsummen

Wenn Sie mehrere sortierte Indizes für eine Tabelle haben (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 Teilbereichs von Zeilen oder Spalten:

  1. Zählen Sie die Anzahl der Zeilen jedes Teilblocks.

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

  3. 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 TEILERGEBNIS 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 AGGREGATE-Funktion

Die AGGREGATE-Funktion ist eine leistungsstarke und effiziente Methode zum Berechnen von 19 verschiedenen Methoden zum Aggregieren von Daten (z. B. SUM, MEDIAN, PERCENTILE und LARGE). AGGREGATE verfügt über Optionen zum Ignorieren ausgeblendeter oder gefilterter Zeilen, Fehlerwerte und geschachtelter SUBTOTAL - und AGGREGATE-Funktionen .

Vermeiden der Verwendung von DFunctions

Die DFunctions DSUM, DCOUNT, DAVERAGE usw. sind wesentlich schneller als entsprechende Arrayformeln. Der Nachteil der DFunctions besteht darin, dass sich die Kriterien in einem separaten Bereich befinden müssen, sodass sie unter vielen Umständen unpraktisch verwendet und beibehalten werden können. Ab Excel 2007 sollten Sie anstelle der DFunctions die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS verwenden.

Erstellen schnellerer VBA-Makros

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

Deaktivieren Sie alles außer dem 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 nur eine Neuberechnung oder neu gezeichnete Neuberechnung nach dem Ausführen des Codes erforderlich und kann die Leistung verbessern. Stellen Sie nach der Ausführung des Codes den ursprünglichen Zustand der Funktionalität wieder her.

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

  • Application.ScreenUpdating Deaktivieren der Bildschirmaktualisierung. If Application.ScreenUpdating is set to False, Excel does not redraw the screen. Während ihr Code ausgeführt wird, wird der Bildschirm schnell aktualisiert, und es ist in der Regel nicht erforderlich, dass der Benutzer jedes Update sehen kann. Eine einmalige Aktualisierung nach Ausführung des Codes kann die Leistung verbessern.

  • Application.DisplayStatusBar Deaktivieren Sie die Statusleiste. Wenn Application.DisplayStatusBar auf False festgelegt ist, zeigt Excel die Statusleiste 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 den Status jedes Vorgangs anzeigen müssen, wird durch Deaktivieren der Statusleiste während der Ausführung des Codes auch die Leistung verbessert.

  • Application.Calculation Switch to manual calculation. 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, verbrauchen diese Add-Ins Ressourcen auf dem Computer, während sie die Ereignisse aufzeichnen. Wenn es nicht erforderlich ist, dass das Add-In die Ereignisse aufzeichnet, die während der Ausführung des Codes auftreten, verbessert das Deaktivieren von Ereignissen die Leistung.

  • ActiveSheet.DisplayPageBreaks Seitenumbrüche deaktivieren. Wenn ActiveSheet.DisplayPageBreaks auf "False" festgelegt ist, werden Excel keine Seitenumbrüche angezeigt. 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 einzigen 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 die Genauigkeit verlieren.
  • . Der Wert gibt eine VBA-Währung oder eine VBA-Datumsvariable zurück, wenn der Bereich als Datum oder Währung formatiert wurde. Dies ist langsam, kann die Genauigkeit verlieren und Fehler beim Aufrufen von Arbeitsblattfunktionen verursachen.
  • . Wert2 ist schnell und ändert die aus Excel abgerufenen Daten nicht.

Vermeiden der Auswahl und Aktivierung von Objekten

Das Auswählen und Aktivieren von Objekten ist verarbeitungsintensiver als das direkte Verweisen auf Objekte. Durch direktes Verweisen auf ein Objekt wie einen Bereich oder ein Shape 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 schneller benutzerdefinierter VBA-Funktionen.

  • Verwenden Sie die Range.SpecialCells-Methode zum Verringern der Anzahl der Zellen, mit denen Ihr Code interagiert.

  • Berücksichtigen Sie die Leistungsvorteile, wenn Sie Ihre Funktionalität mithilfe der C-API im XLL SDK implementiert haben. Weitere Informationen finden Sie in der Excel 2010 XLL SDK-Dokumentation.

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. Wenn Sie die Dateiformatvarianten Makro, Vorlage, Add-In, PDF und XPS ignorieren, sind die drei Hauptformate XLS, XLSB und XLSX.

  • XLS-Format

    Das XLS-Format ist das gleiche Format wie in früheren Versionen. Wenn Sie dieses Format verwenden, sind Sie auf 256 Spalten und 65.536 Zeilen beschränkt. Wenn Sie eine Arbeitsmappe Excel 2007 oder Excel 2010 im XLS-Format speichern, führt Excel eine Kompatibilitätsprüfung aus. Die Dateigröße ist fast identisch mit früheren Versionen (möglicherweise werden zusätzliche Informationen gespeichert), und die Leistung ist etwas langsamer als bei früheren Versionen. Alle Multithreadoptimierungs-Excel in Bezug auf die Reihenfolge der Zellberechnungen werden nicht im XLS-Format gespeichert. Daher kann die Berechnung einer Arbeitsmappe langsamer sein, nachdem die Arbeitsmappe im XLS-Format gespeichert, die Arbeitsmappe 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, aber die 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 das Öffnen und Speichern der Leistung nur etwas 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 dessen Inhalt überprüfen). 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 in Ihrem \Windows\ Temp-Verzeichnis (in Windows 95, Windows 98 und Windows ME) oder \im Verzeichnis "Documents and Einstellungen\ User NameLocal\ Einstellungen\ Temp" (in Windows 2000 und Windows XP). 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 entfernen. 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 des temporären Verzeichnisses finden Sie im Windows Startmenü: Klicken Sie auf "Start" und 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" > "ProtectionProtect > Workbook" > das optionale Kennwort eingeben), wird viel langsamer geöffnet und geschlossen als eine Arbeitsmappe, die ohne das optionale Kennwort geschützt ist.

  • Probleme mit verwendeten Bereichen

    Ü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 verwendeten Bereichen 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 zu Problemen beim Öffnen oder Speichern einer Arbeitsmappe in einem WAN (oder sogar einem LAN) führen. Wenn Sie dieses Problem haben, sollten Sie erwägen, Ihre Arbeitsmappe neu zu 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.

  • Virenscannereinstellungen

    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 Ihre Arbeitsmappe lang ist und ein Problem verursacht, stellen Sie sicher, dass die Berechnung manuell festgelegt ist, und ziehen Sie in Betracht, die Option "Berechnen vor speichern" (ToolsOptionsCalculation > > ) zu deaktivieren.

  • 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 suchen, indem Sie Windows Suche verwenden*.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

Sie können in den folgenden Bereichen Leistungsverbesserungen vornehmen.

  • PivotTables

    PivotTables bieten eine effiziente Möglichkeit zum Zusammenfassen großer Datenmengen.

    • Summen als endgültige Ergebnisse. 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 des bedingten Formats 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 benötigen zusätzliche Berechnungszeit. Die Verwendung von Namen, die auf andere Arbeitsblätter verweisen, erhöht die Komplexität des Berechnungsprozesses zusätzlich. 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 mit großen Adressinformationen von Excel 2013 und 2016 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 über mindestens 3 GB RAM verfügt.

    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" in Excel Leistung: Leistungsverbesserungen und Begrenzungsverbesserungen.

Schlussbemerkung

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

Weitere Artikel

Support und Feedback

Haben Sie Fragen oder Feedback zu Office VBA oder zu dieser Dokumentation? Unter Office VBA-Support und Feedback finden Sie Hilfestellung zu den Möglichkeiten, wie Sie Support erhalten und Feedback abgeben können.