War diese Seite hilfreich?
Ihr Feedback ist uns wichtig. Teilen Sie uns Ihre Meinung mit.
Weiteres Feedback?
1500 verbleibende Zeichen
Exportieren (0) Drucken
Alle erweitern
Erweitern Minimieren

Excel 2010: Tipps für das Entfernen von Leistungshindernissen

Office 2010

Zusammenfassung:  Dieser Artikel enthält Tipps zum Beseitigen häufig auftretender Leistungshindernisse in Microsoft Excel. Dieser Artikel ist einer von drei zusammenhängenden Artikeln zu Möglichkeiten, die Ihnen zum Verbessern der Leistung beim Entwerfen und Erstellen von Arbeitsblättern in Excel zur Verfügung stehen.

Weitere Informationen zum Steigern der Leistung von Excel finden Sie unter Excel 2010: Verbessern der Berechnungsleistung und Excel 2010: Leistungsverbesserungen und höhere Grenzwerte.

In den folgenden Abschnitten werden Möglichkeiten zum Verbessern der Leistung von Verweis- und Verknüpfungstypen beschrieben.

Vorwärts- und Rückwärtsverweise

Entwerfen Sie Ihre Formeln zum Verbessern der Übersichtlichkeit und Vermeiden von Fehlern so, dass sie keine Vorwärtsverweise (nach rechts oder unten) auf andere Formeln oder Zellen enthalten. Vorwärtsverweise wirken sich in der Regel nicht auf die Berechnungsleistung aus. Eine extreme Ausnahme stellt die erste Berechnung einer Arbeitsmappe dar, bei der es ggf. länger dauert, eine sinnvolle Berechnungsfolge einzurichten, wenn viele Formeln vorhanden sind, deren Berechnung verzögert werden muss.

Zirkelverweise mit Iteration

Das Berechnen von Zirkelverweisen mit Iterationen erfolgt langsam, da mehrere Berechnungen erforderlich sind. Häufig können Sie die Zirkelverweise so auflösen, dass keine iterative Berechnung mehr erforderlich ist. Versuchen Sie beispielsweise, bei Berechnungen von Cashflow und Zins den Cashflow vor dem Zins, dann den Zins und danach den Cashflow einschließlich Zins zu berechnen.

Excel berechnet Zirkelverweise Blatt für Blatt ohne Berücksichtigung von Abhängigkeiten. 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 im Zirkelverweis und die Berechnungsdauer dieser Zellen zu verringern.

Verknüpfungen zwischen Arbeitsmappen

Vermeiden Sie nach Möglichkeit Verknüpfungen zwischen Arbeitsmappen, da diese langsam, einfach zu unterbrechen und nicht immer leicht zu finden und zu beheben sind.

Das Arbeiten mit wenigen größeren Arbeitsmappen ist normalerweise, aber nicht immer, besser als das Arbeiten mit vielen kleineren Arbeitsmappen. Ausnahmen hierfür können vorliegen, wenn viele Front-End-Berechnungen vorhanden sind, die selten neu berechnet werden, sodass es sinnvoll ist, diese in einer gesonderten Arbeitsmappe abzulegen, oder der Arbeitsspeicher unzureichend ist.

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 werden die Werte angezeigt, die Excel aus der geschlossenen Arbeitsmappe gelesen hat, was häufig für die Fehlerbehebung und Überwachung 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.

Verknüpfungen zwischen Arbeitsblättern

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

In Excel 97 und Excel 2000 werden Arbeitsblätter und Arbeitsmappen in alphabetischer Reihenfolge in einzelnen Berechnungsketten berechnet. Bei diesen Versionen ist es wichtig, die Arbeitsblätter in einer Reihenfolge zu benennen, die dem Fluss der Berechnungen zwischen Arbeitsblättern entspricht.

Zum Einsparen von Arbeitsspeicher und Verringern der Dateigröße versucht Excel, Informationen zum Bereich nur auf einem Arbeitsblatt zu speichern, das 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.

Drücken Sie STRG+ENDE, um den sichtbaren verwendeten Bereich auf einem Arbeitsblatt zu überprüfen. Sollte dieser überschritten worden sein, erwägen Sie das Löschen aller Zeilen und Spalten unterhalb bzw. rechts der letzten tatsächlich verwendeten Zellen, und speichern Sie anschließend die Arbeitsmappe. Erstellen Sie zuvor eine Sicherungskopie. Wenn Sie Formeln mit Bereichen haben, die in den gelöschten Bereich hineinreichen oder auf diesen verweisen, werden diese Bereiche verkleinert oder in #NV geändert.

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.

Strukturierte Tabellenverweise

Beginnend mit Excel 2007 können Sie mit strukturierten Tabellenverweisen arbeiten, die entsprechend der zu- oder abnehmenden Größe der referenzierten Tabelle automatisch vergrößert oder verkleinert werden. Diese Lösung hat mehrere Vorteile:

  • Es gibt weniger Leistungsnachteile als bei den Alternativen, d. h. Verweise auf ganze Spalten und dynamische Bereiche.

  • Es ist einfach, mehrere Tabellen mit Daten auf einem einzelnen Arbeitsblatt zu pflegen.

  • Formeln, die in die Tabelle eingebettet sind, werden auch entsprechend den Daten vergrößert bzw. verkleinert.

Verweise auf ganze Spalten und Zeilen

Ein alternativer Ansatz besteht darin, einen Verweis auf eine ganze Spalte, z. B. $ A: $ A, zu verwenden. Dieser Verweis gibt alle Zeilen in Spalte A zurück. Deshalb können Sie so viele Daten wie gewünscht hinzufügen, die der Verweis stets enthalten wird.

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

  • Viele vordefinierte Excel-Funktionen (SUM, SUMIF) berechnen Verweise auf ganze Spalten effizient, da sie automatisch die letzte verwendete Spalte in der Zeile erkennen. Arrayberechnungsfunktionen wie SUMPRODUCT können entweder keine Verweise auf ganze Spalten verarbeiten oder berechnen alle Zellen in der Spalte.

  • Benutzerdefinierte Funktionen erkennen nicht automatisch die letzte verwendete Zeile in der Spalte und berechnen daher häufig Verweise auf ganze Spalten nicht effizient. 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.

  • Arrayformeln in Versionen vor Excel 2007 können Verweise auf ganze Spalten nicht verarbeiten. In Excel 2007 können Arrayformeln Verweise auf ganze Spalten verarbeiten, doch wird dadurch die Berechnung aller Zellen in der Spalte einschließlich leerer Zellen erzwungen. Diese Berechnung kann sehr lange dauern, insbesondere bei z. B. 1 Mio. Zeilen.

Dynamische Bereiche

Durch Verwenden der Funktionen OFFSET und COUNTA in der Definition eines benannten Bereichs können Sie veranlassen, dass der Abschnitt, auf den sich der benannte Bereich bezieht, dynamisch vergrößert und verkleinert wird. Erstellen Sie beispielsweise wie folgt einen definierten Namen:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

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

Es kommt zu einer Leistungsminderung, da OFFSET eine veränderliche Funktion ist und deshalb stets neu berechnet wird und da die Funktion COUNTA innerhalb der Funktion OFFSET zahlreiche Zeilen untersuchen muss. Sie können diese Leistungsminderung minimieren, indem Sie den COUNTA-Teil der Formel in einer gesonderten Zelle speichern und anschließend im dynamischen Bereich auf die Zelle verweisen:

Counts!z1=COUNTA(Sheet1!$A:$A)
DynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)

Sie können auch Funktionen wie INDIRECT verwenden, um dynamische Bereiche zu erstellen. 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.

  • Das Verwenden mehrerer dynamischer Bereiche mit einer einzelnen Spalte erfordert spezielle Zählfunktionen.

  • Das Arbeiten mit vielen dynamischen Bereichen kann die Leistung verringern.

Nachschlagevorgänge stellen häufig signifikante 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.

Nachschlageoptionen

Vergewissern Sie sich, dass Sie die Matchtype- und Range-lookup-Optionen in MATCH, VLOOKUP und HLOOKUP verstanden haben.

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 gleich dem Nachschlagewert zurück, wenn das Nachschlagearray aufsteigend sortiert ist (ungefähre Übereinstimmung). Dies ist die Standardoption.

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

  • matchtype=-1 gibt die größte Übereinstimmung größer gleich dem Nachschlagewert zurück, wenn das Nachschlagearray absteigend sortiert ist (ungefähre Übereinstimmung).

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

VLOOKUP(lookup value, table array, col index num, range-lookup)
HLOOKUP(lookup value, table array, row index num, range-lookup)
  • range-lookup=TRUE gibt die größte Übereinstimmung kleiner gleich dem Nachschlagewert (ungefähre Übereinstimmung) zurück. Dies ist die Standardoption. Das Tabellenarray muss aufsteigend sortiert sein.

  • range-lookup=FALSE fordert eine genaue Übereinstimmung an und geht davon aus, dass die Daten nicht sortiert sind.

Vermeiden Sie möglichst das Anwenden von Nachschlagevorgängen auf unsortierte Daten, da es langsam ist. Wenn Ihre Daten sortiert sind, Sie aber eine genaue Übereinstimmung wünschen, finden Sie unter Sortierte Daten mit fehlenden Werten weitere Informationen.

SVERWEIS im Vergleich zu INDEX und VERGLEICH oder BEREICH.VERSCHIEBEN

Verwenden Sie die Funktionen INDEX und MATCH anstelle von VLOOKUP. VLOOKUP ist geringfügig schneller (ca. 5 %), einfacher und belegt weniger Arbeitsspeicher als eine Kombination aus MATCH und INDEX oder OFFSET. Doch die zusätzliche Flexibilität, die MATCH und INDEX bieten, sorgt jedoch oft für eine Zeitersparnis. Sie können beispielsweise eine genaue Übereinstimmung (MATCH) in einer Zelle speichern und in mehreren INDEX-Anweisungen wiederverwenden.

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

VLOOKUP kann mühelos in INDEX und MATCH umgewandelt werden. Die beiden folgenden Anweisungen liefern dasselbe Ergebnis.

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ängen

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

  • Verwenden Sie nur ein Arbeitsblatt, da Nachschlagevorgänge schneller erfolgen, wenn sich die Daten auf demselben Blatt befinden.

  • 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 dynamische Bereiche, 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.

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, jedoch weiterhin nicht mit der ungefähren Übereinstimmung arbeiten 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)

Wenn die Rückgabe aus der Nachschlagespalte mit dem Nachschlagewert identisch ist, verwenden Sie die folgende Formel.

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

Sie haben eine genaue Übereinstimmung gefunden, sodass Sie die Suche nach einer ungefähren Übereinstimmung erneut ausführen können, wobei aber diesmal die Antwort aus der gewünschten Spalte zurückgegeben werden soll.

VLOOKUP(lookup_val, lookup_array, column, True)

Wenn die Antwort aus der Nachschlagespalte nicht dem Nachschlagewert entspricht, handelt es sich um einen fehlenden Wert, weshalb "nicht vorhanden" zurückgegeben wird.

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.

Unsortierte Daten mit fehlenden Werten

Wenn Sie mit einem Nachschlagevorgang nach einer genauen Übereinstimmung arbeiten müssen und nicht sicher sein können, dass der Nachschlagewert vorhanden ist, müssen Sie oft mit der Rückgabe "#N/V" zurecht kommen, wenn keine Übereinstimmung gefunden wird. In 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, arbeiten Sie mit der COUNTIF-Funktion, die generell schneller als ein Nachschlagevorgang nach einer genauen Übereinstimmung ist.

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

Nachschlagevorgänge nach genauen Übereinstimmungen in mehreren Spalten

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

Fügen Sie für die MATCH-Funktion eine zusätzliche Spalte zum Speichern des Ergebnisses (stored_row) hinzu, und verwenden Sie für jede Ergebniszeile Folgendes.

INDEX(Lookup_Range,stored_row,column_number)

Alternativ können Sie die VLOOKUP-Funktion in einer Arrayformel verwenden.

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

Nachschlagen einer Gruppe 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.

Nachschlagen eines rechteckigen Zellenblocks

Mit den Funktionen MATCH und OFFSET können Sie einen rechteckigen Zellenblock zurückgeben.

Zweidimensionales Nachschlagen

Sie können eine Tabelle effizient zweidimensional durchsuchen, indem Sie durch Angeben einer INDEX-Funktion mit zwei eingebetteten MATCH-Funktionen (eine für die Zeile und eine für die Spalte) getrennte Nachschlagevorgänge auf die Zeilen und Spalten einer Tabelle anwenden.

Nachschlagevorgänge unter Verwendung mehrerer Indizes

In umfangreichen Arbeitsblättern müssen Sie ggf. häufig Nachschlagevorgänge unter Verwendung mehrerer Indizes durchführen, z. B. beim Nachschlagen von Produktmengen in einem Land. Hierzu können Sie die Indizes verketten und den Nachschlagevorgang mithilfe verketteter Nachschlagewerte durchführen. Dies ist jedoch aus zwei Gründen nicht effizient:

  • Das Verketten von Zeichenfolgen ist ein berechnungsintensiver Vorgang.

  • Der Nachschlagevorgang bezieht sich auf einen großen Bereich.

Es ist häufig effizienter, einen Unterbereich für den Nachschlagevorgang zu berechnen (z.  B. indem die erste und letzte Zeile des Landes gesucht wird und anschließend das Produkt in diesem Bereich nachgeschlagen wird).

Dreidimensionales Nachschlagen

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 die Tabelle, die Sie nachschlagen möchten (die dritte Dimension) als eine Gruppe benannter strukturierter Tabellen, Bereichsnamen oder als Tabelle mit Textzeichenfolgen, die Bereiche darstellen, gespeichert ist, können Sie ggf. die Funktionen INDIRECT und CHOOSE 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.

INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

Im vorherigen Beispiel wird TableLookup_Value dynamisch verwendet, um auszuwählen, welche Bereichsnamen (Tabellenname1, Tabellenname2, ...) für die Nachschlagetabelle verwendet werden sollen.

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

In diesem Beispiel werden die INDIRECT-Funktion und TableLookup_Value zum dynamischen Erstellen des Blattnamens verwendet, 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änderliche Funktion ist, wird der Nachschlagevorgang bei jeder Berechnung berechnet, auch sich wenn keine Daten geändert haben.

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.

Nachschlagevorgänge unter Verwendung von Platzhaltern

Die Funktionen MATCH, VLOOKUP und HLOOKUP erlauben die Angabe der Platzhalterzeichen ? (beliebiges einzelnes Zeichen) und * (kein Zeichen oder eine beliebige Anzahl von Zeichen) für alphabetisch genaue Übereinstimmungen. Mitunter können Sie diese Methode verwenden, um mehrere Übereinstimmungen zu vermeiden.

Arrayformeln und die SUMPRODUCT-Funktion sind leistungsstark, müssen jedoch überlegt eingesetzt werden, da eine einzelne Arrayformel viele Berechnungen erfordern kann.

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. Bedenken Sie, dass eine Arrayformel sich ähnlich wie eine veränderliche Formel verhält: Wenn eine der Zellen, auf die die Formel verweist, geändert wurde und veränderlich ist oder neu berechnet wurde, berechnet die Arrayformel alle Zellen in der Formel und wertet alle virtuellen Zellen aus, die für die Berechnung benötigt werden.

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.

  • Arbeiten Sie nicht mit Bezügen auf vollständige Zeilen bzw. auf mehr Zeilen und Spalten als nötig. Arrayformeln sind gezwungen, alle Zellbezüge in der Formel zu berechnen, auch wenn die Zellen leer sind oder nicht verwendet werden. Ab Excel 2007 stehen 1 Mio. Zeilen zur Verfügung, sodass eine Arrayformel, die sich auf eine ganze Spalte bezieht, nur extrem langsam berechnet werden kann.

  • 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 möglichst 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.

Arrayformeln: SUMME mit mehreren Bedingungen

Ab Excel 2007 sollten Sie stets nach Möglichkeit die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS anstatt Arrayformeln verwenden, da deren Berechnung wesentlich schneller erfolgt.

In Versionen vor Excel 2007 werden Arrayformeln häufig zum Berechnen einer Summe mit mehreren Bedingungen verwendet. Dies ist relativ einfach, insbesondere wenn Sie den Teilsummen-Assistenten in Excel zu Hilfe nehmen, jedoch oft langsam. Meist gibt es schnellere Möglichkeiten zum Erzielen desselben Ergebnisses. Wenn Sie nur über wenige SUMME-Funktionen mit mehreren Bedingungen verfügen, können Sie ggf. die DSUM-Funktion nutzen, die wesentlich schneller als die entsprechend 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 zum Minimieren der Anzahl der Zellen.

  • Verschieben Sie mehrere Bedingungen in eine Spalte mit Hilfsformeln, die für jede Zeile True oder False zurückgeben, und verweisen Sie anschließend in einer SUMIF-Funktion oder Arrayformeln auf die Hilfsspalte. Dadurch wird die Anzahl der Berechnungen für eine einzelne Arrayformel scheinbar nicht verringert, doch meistens ermöglicht es dem intelligenten Neuberechnungsprozess die Neuberechnung nur der Formeln in der Hilfsspalte, 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, bietet sich das Zählen von Zeilengruppen und Beschränken der Arrayformeln auf eine Untersuchung der Teilgruppen an.

Verwenden von SUMMENPRODUKT für Arrayformeln mit mehreren Bedingungen

Ab Excel 2007 sollten Sie nach Möglichkeit stets die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS anstatt SUMPRODUCT-Formeln verwenden.

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

  • SUMPRODUCT muss nicht wie ein Array über STRG+UMSCHALT+EINGABETASTE eingegeben werden.

  • SUMPRODUCT ist in der Regel etwas schneller (5 bis 10 %).

Sie können SUMPRODUCT für Arrayformeln mit mehreren Bedingungen wie folgt verwenden.

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

In diesem Beispiel sind Condition1 und Condition2 bedingte Ausdrücke, z. B. $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. Verwenden Sie hierzu zwei Minuszeichen (--), oder fügen Sie 0 (+0) hinzu, oder multiplizieren Sie mit 1 (*1). Das Verwenden von -- ist geringfügig schneller als +0 oder *1.

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 Bedingungen in der SUMPRODUCT-Funktion auch direkt multiplizieren, statt sie durch Kommas zu trennen.

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

Dies ist in der Regel etwas langsamer als die Verwendung der Syntax mit Kommas und gibt einen Fehler zurück, wenn der zu summierende Bereich einen Textwert enthält. Die Flexibilität ist allerdings dahingehend etwas größer, dass beispielsweise der zu summierende Bereich ggf. mehrere Spalten enthält, während die Bedingungen nur eine Spalte aufweisen.

Verwenden von SUMMENPRODUKT zum Multiplizieren und Addieren 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, doch die dritte Formel, die die Syntax mit Kommas für SUMPRODUCT verwendet, benötigt nur 77 % der Zeit, die die anderen beiden Formeln zur Berechnung benötigen.

Hindernisse bei der Array- und Funktionsberechnung

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.

Funktionen steigern deutlich die Leistung von Excel, aber die Art und Weise, in der Sie sie verwenden, kann sich oft auf die Berechnungszeit auswirken.

Funktionen zum Verarbeiten von Bereichen

Bei Funktionen wie SUM, SUMIF und SUMIFS, die Bereiche verarbeiten, ist die Berechnungszeit proportional zur Anzahl der verwendeten Zellen, die summiert oder gezählt werden. Nicht verwendete Zellen werden nicht untersucht, weshalb Verweise auf ganze Spalten relativ effizient sind. Es sollte jedoch sichergestellt sein, dass nicht mehr verwendete Zellen als benötigt einbezogen werden. Verwenden Sie Tabellen, oder berechnen Sie Teil- oder dynamische Bereiche.

Veränderliche 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. Doch OFFSET ist eine schnelle Funktion und kann oft kreativ zum Beschleunigen der Berechnung eingesetzt werden.

Benutzerdefinierte Funktionen

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

XML-Funktionen können auch schnell sein, weil Sie dieselbe eng gekoppelte API wie C-XLL-Add-In-Funktionen verwenden. Die Leistung benutzerdefinierter VBA-Funktionen hängt sehr von ihrer Programmierung und dem Aufruf ab.

Schnellere benutzerdefinierte VBA-Funktionen

Es ist normalerweise schneller, die Excel-Formelberechnungen und Arbeitsblattfunktionen zu verwenden, als mit benutzerdefinierten VBA-Funktionen zu arbeiten. 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 eine große Anzahl von Formeln mit benutzerdefinierten Funktionen haben, stellen Sie sicher, dass Sie sich im manuellen Berechnungsmodus befinden und dass die Berechnung in VBA ausgelöst 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 geöffnet ist (ALT-F11) 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 Modul "DieseArbeitsmappe" 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

Bei benutzerdefinierten Funktionen in Automatisierungs-Add-Ins (Excel 2002 und höher) fällt der Visual Basic Editor-Verarbeitungsaufwand nicht an, 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, können nicht berechnete Argumente an sie übergeben werden. In einer benutzerdefinierten Funktion können Sie nicht berechnete Zellen erkennen, indem Sie die folgenden Überprüfung auf leere Zellen durchführen, die eine Formel enthalten.

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

Für jeden Aufruf einer benutzerdefinierten Funktion und jede Übertragung von Daten aus Excel nach VBA fällt ein zusätzlicher Verarbeitungsaufwand an. 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.

SUMME und SUMMEWENN

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.

SUMMEWENN und ZÄHLENWENN mit Platzhalterzeichen

Sie können die Platzhalterzeichen ? (beliebiges einzelnes Zeichen) und * (kein Zeichen oder eine beliebige Anzahl von Zeichen) als Teil der Kriterien der Funktionen SUMIF und COUNTIF für alphabetische Bereiche angeben.

Periode-bis-Datum und kumulative Summenberechnungen

Es gibt zwei Möglichkeiten zum Durchführen von Periode-bis-Datum- und kumulativen Summenberechnungen. Angenommen, die Werte die Sie mit SUM kumulieren möchten, sind in Spalte A, und Spalte B soll die kumulierte Summe enthalten. Sie können dazu wie folgt vorgehen:

  • Sie können eine Formel in Spalte B erstellen, z. B. =SUM($A$1:$A2), und sie so weit wie nötig 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 in Zelle B1 eine Formel wie =$A1 und wie =$B1+$A2 in Zelle B2 erstellen und so weit wie nötig 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.

Teilmengensummierung

Wenn Sie über mehrere sortierte Indizes für eine Tabelle verfügen, können Sie die Berechnungszeit wesentlich verkürzen, indem Sie die Adresse eines Teilbereichs der Zeilen (oder Spalten) dynamisch berechnen, um sie in der SUM- oder SUMIF-Funktion zu verwenden:

Berechnen der 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.

Teilergebnisse

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.

DBFunktionen

Die DBFunktionen DSUM, DCOUNT, DAVERAGE usw. sind wesentlich schneller als entsprechende Arrayformeln. Der Nachteil der DBFunktionen ist, dass sich die Kriterien in einem gesonderten Bereich befinden müssen, wodurch sie in vielen Fällen unpraktisch hinsichtlich Verwendung und Pflege sind. Ab Excel 2007 sollten Sie die Funktionen SUMIFS, COUNTIFS und AVERAGEIFS anstelle der DBFunktionen verwenden.

In den folgenden Abschnitten werden verschiedene einfache Tipps zum Erstellen schnellerer VBA-Makros beschrieben.

Deaktivieren Sie bei der Ausführung von Code alles außer den erforderlichen Komponenten.

Deaktivieren Sie zum Verbessern der Leistung von VBA-Makros während der Ausführung Ihres Codes explizit nicht benötigte Funktionalität. Häufig ist nach der Ausführung Ihres Codes nur eine Neuberechnung oder Neuzeichnung erforderlich, wodurch die Leistung verbessert werden kann. Stellen Sie nach Ausführung Ihres Codes den Originalzustand 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 Sie die Bildschirmaktualisierung. Wenn Application.ScreenUpdating auf False festgelegt ist, zeichnet Excel den Bildschirm nicht neu. Während der Ausführung Ihres Codes wird der Bildschirm rasch aktualisiert, und in der Regel muss der Benutzer nicht jede Aktualisierung erkennen. 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 sehen müssen, wird durch Deaktivieren der Statusleiste bei der Ausführung Ihres Codes die Leistung verbessert.

  • Application.Calculation Wechseln Sie zur manuellen Berechnung. Wenn Application.Calculation auf xlCalculationManual festgelegt ist, berechnet Excel die Arbeitsmappe nur dann, wenn der Benutzer explizit eine Berechnung auslöst. 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 Ihr Code ausgeführt wird, können Sie die Leistung verbessern.

  • Application.EnableEvents Deaktivieren Sie Ereignisse. Wenn Application.EnableEvents auf False festgelegt ist, löst Excel keine Ereignisse aus. Wenn Add-In-Auflistungen für Excel-Ereignisse vorhanden sind, belegen diese Add-Ins Ressourcen des Computers, sobald sie die Ereignisse aufzeichnen. Es ist nicht erforderlich, dass das Add-In die Ereignisse aufzeichnet, die erfolgen, während Ihr Code ausgeführt wird. Durch eine Deaktivierung wird die Leistung verbessert.

  • ActiveSheet.DisplayPageBreaks Deaktivieren Sie Seitenumbrüche. Wenn ActiveSheet.DisplayPageBreaks auf False festgelegt ist, zeigt Excel keine Seitenumbrüche an. Das Neuberechnen von Seitenumbrüchen während der Ausführung Ihres Codes ist nicht erforderlich, weshalb dieser Vorgang im Anschluss an die Ausführung Ihres Codes die Leistung verbessert.

Wichtiger Hinweis Wichtig

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

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

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

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

' Insert your code here.

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

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

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

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

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

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

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

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

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").Value = DataRange 

Vermeiden der Auswahl und Aktivierung von Objekten

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

Das folgende Codebeispiel zeigt nicht optimierten Code, der jede Form im aktiven Arbeitsblatt 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 auf jede Form direkt verweist und den Text in "Hello" ändert.

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

Weitere 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 Schnellere benutzerdefinierte VBA-Funktionen.

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

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

Beginnend mit Excel 2007 bietet Excel im Vergleich zu früheren Formaten eine große Vielfalt von Dateiformaten. Bei Außerachtlassung der Dateiformatvariationen Makro, Vorlage, Add-In, PDF und XPS gibt es drei Hauptformate: XLS, XLSB und XLSX.

XLS-Format

Das XLS-Format entspricht dem Format früherer Versionen. Bei Verwenden dieses Formats sind Sie auf 256 Spalten und 65.536 Zeilen beschränkt. Wenn Sie eine Excel 2007- oder Excel 2010-Arbeitsmappe im XLS-Format speichern, wird eine Kompatibilitätsprüfung durchgeführt. Die Dateigröße entspricht nahezu der früherer Versionen (ggf. werden einige zusätzliche Informationen gespeichert), und die Leistung ist geringfügig niedriger als in früheren Versionen. Multithreadoptimierungen, die Excel hinsichtlich der Berechnungsreihenfolge von Zellen vornimmt, werden nicht im XLS-Format gespeichert. Deshalb kann die Berechnung einer Arbeitsmappe langsamer erfolgen, nachdem die Arbeitsmappe im XLS-Format gespeichert, geschlossen und anschließend wieder geöffnet wurde.

XLSB-Format

XLSB ist das in Excel 2007 erstmals eingeführte Binärformat, das als komprimierter Ordner mit einer großen Anzahl von Binärdateien strukturiert ist. Es ist wesentlich kompakter als das XLS-Format, jedoch hängt der Grad der Komprimierung stark vom Inhalt der Arbeitsmappe ab. Bei zehn Arbeitsmappen reicht der Reduzierungsfaktor von zwei bis acht, wobei die durchschnittliche Reduzierung den Faktor vier hat. Beginnend mit Excel 2007 ist die Öffnungs- und Speicherleistung nur geringfügig niedriger als beim 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 mit einer großen Anzahl von XML-Dateien (wenn Sie die Dateinamenerweiterung in ZIP ändern, können Sie den komprimierten Ordner öffnen und seinen Inhalt untersuchen). In der Regel sind im XLSX-Format erstellte Dateien größer als Dateien im XLSB-Format (durchschnittlich 1,5-mal), allerdings immer noch wesentlich kleiner als XLS-Dateien. Das Öffnen und Schließen dauert etwas länger als bei XLSB-Dateien.

Sie werden ggf. feststellen, dass das Öffnen, Schließen und Speichern von Arbeitsmappen wesentlich langsamer als deren Berechnung erfolgt. Mitunter ist der Grund, dass die Arbeitsmappe sehr groß ist, es sind aber auch andere Ursachen möglich.

Langsames Öffnen und Schließen

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

Temporäre Dateien

In den Verzeichnissen \Windows\Temp (unter Microsoft Windows 95, Microsoft Windows 98 und Microsoft Windows ME) oder \Dokumente und Einstellungen\Benutzername\Lokale Einstellungen\Temp (unter Microsoft Windows 2000 und Microsoft Windows XP) können sich temporäre Dateien anhäufen. Excel erstellt diese Dateien für die Arbeitsmappen und insbesondere für Steuerelemente, die von geöffneten Arbeitsmappen verwendet werden. Softwareinstallationsprogramme erstellen ebenfalls temporäre Dateien. Wenn Excel aus einem beliebigen Grund nicht mehr reagiert, müssen Sie ggf. diese Dateien löschen.

Zu viele temporäre Dateien können Probleme verursachen, weshalb Sie sie regelmäßig entfernen sollten. Wenn Sie jedoch Software installiert haben, die den Neustart des Computers erfordert und Sie diesen Schritt noch nicht ausgeführt haben, müssen Sie den Neustart von dem Löschen der temporären Dateien durchführen.

Eine einfache Möglichkeit zum Öffnen des Verzeichnisses Temp bietet das Windows-Startmenü. Klicken Sie auf Start und dann auf Ausführen. Geben Sie in das Textfeld %temp% ein, und klicken Sie auf OK.

Nachverfolgen von Änderungen in einer freigegeben 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 mit einem Kennwort geschützt ist (zeigen Sie im Menü Extras auf Schutz , und klicken Sie auf Arbeitsmappe schützen, und geben Sie das optionale Kennwort ein), wird wesentlich langsamer geöffnet und geschlossen als eine Arbeitsmappe ohne optionalen Kennwortschutz.

Probleme mit dem verwendeten Bereich

Übergroße verwendete Bereiche können für ein langsames Öffnen und eine hohe Dateigröße sorgen, insbesondere wenn sie durch ausgeblendete Zeilen oder Spalten mit nicht standardmäßiger Höhe oder Breite verursacht werden. Weitere Informationen zu Problemen mit 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 verwendeter temporärer Dateien verlangsamen. Dies kann auch Probleme beim Öffnen oder Speichern einer Arbeitsmappe in einem WAN (oder einem lokalen Netzwerk) verursachen. Wenn Sie diese Probleme haben sollten, erwägen Sie eine Überarbeitung Ihrer Arbeitsmappe.

Große Anzahl von Verknüpfungen 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 Schließen

Unter bestimmten Umständen berechnet Excel Ihre Arbeitsmappe beim Öffnen oder Schließen neu. Wenn die Berechnungszeit der Arbeitsmappe lang ist und ein Problem darstellt, prüfen Sie, ob Sie die Berechnung auf Manuell festgelegt haben, und erwägen Sie das Deaktivieren der Option Vor dem Speichern neu berechnen (wählen Sie im Menü Extras den Eintrag Optionen und dann Formeln aus).

Symbolleistendateien (.xlb)

Überprüfen Sie die Größe der Symbolleistendatei. Die Größe einer typischen Symbolleistendatei ist 10-20 KB. Sie finden XLB-Dateien, indem Sie in der Windows-Suchfunktion nach (*.xlb) suchen. 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.

In den folgenden Abschnitten werden weitere Bereiche beschrieben, in denen Sie Leistungsverbesserungen vornehmen können.

PivotTables

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

Summen als Endergebnisse

Wenn Sie Summen und Teilergebnisse als Endergebnisse Ihrer Arbeitsmappe erstellen müssen, erwägen Sie das Arbeiten mit PivotTables.

Summen als Zwischenergebnisse

PivotTables eignen sich sehr gut zum Erstellen von Zusammenfassungsberichten. Versuchen Sie jedoch das Erstellen von Formeln zu vermeiden, die PivotTable-Ergebnisse als Zwischen- oder Teilergebnisse in Ihrer Berechnungskette verwenden, es sei denn, Sie gewährleisten Folgendes:

  • Die PivotTable wurde während der Berechnung ordnungsgemäß aktualisiert.

  • Die PivotTable wurde nicht geändert, sodass die Informationen weiter angezeigt werden.

Wenn Sie PivotTables dennoch als Zwischenergebnisse nutzen möchten, verwenden Sie die GETPIVOTDATA-Funktion.

Bedingte Formate und Datengültigkeitsprü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, werden alle bedingten Formatformeln bei jeder Berechnung und auch bei Aktualisierung der Anzeige der Zelle mit dem bedingten Format ausgewertet. Das Excel-Objektmodell bietet die Worksheet.EnableFormatConditionsCalculation-Eigenschaft, sodass Sie die Berechnung bedingter Formate aktivieren und deaktivieren können.

Definierte Namen

Definierte Namen zählen zu den leistungsstärksten Funktionen in Excel, benötigen allerdings zusätzliche Berechnungszeit, denn das Verwenden von Namen, die auf andere Arbeitsblätter verweisen, fügt dem Berechnungsprozess einen zusätzlichen Grad an Komplexität hinzu. Vermeiden sollten Sie verschachtelte Namen (d. h. Namen, die auf andere Namen verweisen).

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.

Nur gelegentlich verwendete Formeln

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.

Bereitstellen von genügend Arbeitsspeicher

32-Bit-Excel kann bis zu 2 GB Arbeitsspeicher nutzen. 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. Zum Optimieren der Leistung von Excel auf einem 32-Bit-Computer sollte der Computer mindestens 3 GB Arbeitsspeicher haben.

Für 64-Bit-Excel gilt der Grenzwert von 2 GB nicht. Weitere Informationen finden Sie im Abschnitt "Große Datasets und 64-Bit-Excel" in Excel 2010: Leistungsverbesserungen und höhere Grenzwerte.

In diesem Artikel wurden Möglichkeiten zur Optimierung der Funktionalität von Excel behandelt, z. B. in den Bereichen Verknüpfungen, Nachschlagevorgänge, Formeln, Funktionen und VBA-Code, um gängige Hindernisse zu umgehen und die Leistung zu verbessern.

Charles Williams gründete Decision Models 1996 mit dem Ziel, umfassende Beratungsdienste, Entscheidungsunterstützungslösungen und Tools anzubieten, die auf Microsoft Excel und relationalen Datenbanken basieren. Williams ist der Autor von FastExcel, dem vielfach eingesetzten Excel-Leistungsoptimierungstool, und Co-Autor von Name Manager, dem beliebten Hilfsprogramm zum Verwalten definierter Namen. Weitere Informationen zur Excel-Berechnungsleistung sowie zu Methoden, Arbeitsspeicherauslastung und benutzerdefinierten VBA-Funktionen finden Sie auf der Website von Decision Models.

Dieser technische Artikel wurde zusammen mit A23 Consulting erstellt.

Allison Bokone, Microsoft Corporation, ist technische Redakteurin im Office-Team.

Chad Rothschiller, Microsoft Corporation, ist ein Programm-Manager im Office-Team.

Microsoft führt eine Onlineumfrage durch, um Ihre Meinung zur MSDN-Website zu erfahren. Wenn Sie sich zur Teilnahme entscheiden, wird Ihnen die Onlineumfrage angezeigt, sobald Sie die MSDN-Website verlassen.

Möchten Sie an der Umfrage teilnehmen?
Anzeigen:
© 2015 Microsoft