Excel-Leistung: Verbesserung der Berechnungsleistung

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

Das große Raster aus 1 Million Zeilen und 16.000 Spalten in Office Excel 2016 bedeutet kombiniert mit den vielen weiteren Grenzwerterhöhungen, dass Sie deutlich größere Arbeitsmappen erstellen können als in früheren Versionen von Excel. Ein einziges Arbeitsblatt in Excel kann jetzt mehr als 1.000-mal so viele Zellen enthalten wie in früheren Versionen.

In früheren Excel-Versionen haben viele Benutzer Arbeitsblätter erstellt, deren Berechnung langsam war. Generell gilt, dass größere Arbeitsblätter langsamer berechnet werden als kleinere. Mit der Einführung des großen Rasters in Excel 2007 wurde der Aspekt Leistung zum Knackpunkt. Wenn Berechnungen und Datenbearbeitungsvorgänge wie Sortieren und Filtern nur langsam ausgeführt werden, ist es für Benutzer schwieriger, sich auf ihre Aufgabe zu konzentrieren. Konzentrationsmangel wiederum bedeutet mehr Fehler.

In den neueren Excel-Versionen wurden mehrere Features eingeführt, um dieses Plus an Kapazität auch entsprechend nutzen zu können, z. B. das gleichzeitige Einsetzen mehrerer Prozessoren für Berechnungen und allgemeine Datasetvorgänge wie das Aktualisieren, Sortieren und Öffnen von Arbeitsmappen. Dank der Multithreadberechnung kann die Berechnungszeit von Arbeitsblättern wesentlich verkürzt werden. Doch der wichtigste Faktor für das Tempo von Berechnungen in Excel ist weiterhin die Art und Weise, mit der Arbeitsblätter entworfen und erstellt werden.

Die meisten Arbeitsblätter mit langsamer Berechnung lassen sich so modifizieren, dass die Berechnung um einen zweistelligen, dreistelligen oder sogar vierstelligen Faktor beschleunigt wird. Möglich wird eine solche Beschleunigung durch ein Identifizieren, Messen und Verbessern der Berechnungshindernisse in Ihren Arbeitsblättern.

Die Bedeutung der Berechnungsgeschwindigkeit

Langsame Berechnungen wirken sich negativ auf die Produktivität aus und führen zu mehr Benutzerfehlern. Je länger die Reaktionszeit, desto mehr leiden die Benutzerproduktivität und die Fähigkeit, sich auf eine Aufgabe zu konzentrieren.

Excel bietet zwei Hauptberechnungsmodi, mit denen Sie steuern können, wann die Berechnung erfolgt:

• Automatische Berechnung : Formeln werden automatisch neu berechnet, sobald Sie eine Änderung vornehmen.

• Manuelle Berechnung: Formeln werden erst auf Anforderung neu berechnet (z. B., wenn Sie F9 drücken).

Wenn die Berechnungszeit weniger als etwa eine Zehntelsekunde beträgt, haben Benutzer das Gefühl, dass das System sofort reagiert. Sie können Berechnungen automatisch durchführen lassen, noch während sie die Daten eingeben.

Zwischen einer Zehntelsekunde und einer Sekunde können Benutzer einen Gedankengang erfolgreich verfolgen, wenngleich sie eine verzögerte Antwortzeit bemerken.

Bei zunehmender Berechnungszeit (in der Regel zwischen einer und zehn Sekunden) müssen die Benutzer bei der Dateieingabe zur manuellen Berechnung wechseln. Benutzerfehler und Verärgerung nehmen nach und nach zu, insbesondere bei sich wiederholenden Aufgaben, und es wird schwierig, einen Gedankengang zu verfolgen.

Bei einer mehr als zehn Sekunden dauernden Berechnungszeit werden die Benutzer ungeduldig und beschäftigen sich meist während des Wartens mit anderen Aufgaben. Dies kann Probleme verursachen, wenn die Berechnung eine Aufgabe in einer Folge ist und der Benutzer den Überblick verliert.

Grundlegendes zu Berechnungsmethoden in Excel

Zur Verbesserung der Berechnungsleistung in Excel müssen Sie mit den beiden möglichen Berechnungsmethoden und deren Steuerung vertraut sein.

Abhängigkeiten für eine vollständige Berechnung und Neuberechnung

Das intelligente Neuberechnungsmodul in Excel versucht, die Berechnungszeit zu minimieren, indem sowohl die vorherigen Berechnungen und Abhängigkeiten jeder Formel (die Zellen, auf die die Formel verweist) als auch alle Änderungen seit der letzten Berechnung nachverfolgt werden. Bei der nächsten Neuberechnung wird von Excel nur Folgendes neu berechnet:

• Zellen, Formeln, Werte oder Namen, die sich geändert haben oder die für eine benötigte Neuberechnung gekennzeichnet sind.

• Zellen, die von anderen Zellen, Formeln, Namen oder Werten abhängig sind, die neu berechnet werden müssen.

• Veränderliche Funktionen und sichtbare bedingte Formate.

Excel berechnet weiter Zellen, die von zuvor berechneten Zellen abhängig sind, auch wenn sich der Wert der zuvor berechneten Zellen bei der Berechnung nicht ändert.

Da Sie meist nur einen Teil der Eingabedaten oder einige Formeln zwischen Berechnungen ändern, dauert diese intelligente Neuberechnung in der Regel nur einen Bruchteil der Zeit, die eine vollständige Berechnung aller Formeln benötigen würde.

Im Modus für manuelle Berechnung können Sie die intelligente Neuberechnung durch Drücken von F9 auslösen. Durch Drücken von STRG+ALT+F9 können Sie eine vollständige Berechnung aller Formeln erzwingen und durch Drücken von UMSCHALT+STRG+ALT+F9 eine vollständige Neuerstellung der Abhängigkeiten sowie eine vollständige Berechnung.

Berechnungsprozess

Excel-Formeln, die auf andere Zellen verweisen, können vor oder nach den referenzierten Zellen eingefügt werden (Vorwärtsverweis oder Rückwärtsverweis). Das ist möglich, da Excel Zellen weder in einer festen Reihenfolge noch nach Zeile oder Spalte berechnet. Stattdessen bestimmt Excel die Berechnungsreihenfolge dynamisch auf Basis einer Liste aller zu berechnenden Formeln (der Berechnungskette) und der Abhängigkeitsinformationen jeder einzelnen Formel.

Excel arbeitet mit klar getrennten Berechnungsphasen:

1. Eine erste Berechnungskette wird konstruiert, und der Anfangspunkt der Berechnung wird bestimmt. Diese Phase wird durchgearbeitet, sobald die Arbeitsmappe in den Arbeitsspeicher geladen wird.

2. Abhängigkeiten werden nachverfolgt, Zellen werden als nicht berechnet markiert, und die Berechnungskette wird aktualisiert. Diese Phase wird bei jeder Zelleingabe oder Zelländerung durchgearbeitet, auch im Modus für manuelle Berechnung. Normalerweise geschieht dies so schnell, dass die Benutzer es nicht bemerken; in komplexen Fällen kann die Reaktionszeit jedoch langsam sein.

3. Alle Formeln werden berechnet. Als Teil des Berechnungsprozesses wird die Berechnungskette von Excel neu angeordnet und umstrukturiert, um künftige Neuberechnungen zu optimieren.

4. Die sichtbaren Bereiche der Excel-Fenster werden aktualisiert.

Die dritte Phase wird bei jeder Berechnung und Neuberechnung durchgearbeitet. Excel versucht, nacheinander jede Formel in der Berechnungskette zu berechnen. Hängt eine Formel jedoch von einer oder mehreren noch nicht berechneten Formeln ab, wird sie in der Kette nach hinten verschoben und später erneut berechnet. Das bedeutet, dass eine Formel pro Neuberechnung möglicherweise mehrfach berechnet wird.

Die zweite Berechnung einer Arbeitsmappe erfolgt häufig wesentlich schneller als die erste. Das hat mehrere Gründe:

• Excel berechnet in der Regel nur Zellen neu, die geändert wurden, sowie die von diesen Zellen abhängigen Zellen.

• Excel speichert die zuletzt genutzte Berechnungsreihenfolge und verwendet sie wieder, sodass der Zeitaufwand zum Bestimmen der Berechnungsreihenfolge minimiert werden kann.

• Auf Computern mit Multi-Core-Prozessoren versucht Excel, die Berechnungen optimal auf die einzelnen Prozessorkerne zu verteilen, basierend auf den Ergebnissen der vorherigen Berechnung.

• In einer Excel-Sitzung legen sowohl Windows als auch Excel die zuletzt verwendeten Daten und Programme für schnelleren Zugriff im Zwischenspeicher ab.

Berechnen von Arbeitsmappen, Arbeitsblättern und Bereichen

Mithilfe der verschiedenen Excel-Berechnungsmethoden können Sie festlegen, was berechnet wird.

Alle geöffneten Arbeitsmappen berechnen

Bei jeder Neuberechnung bzw. vollständigen Berechnung werden alle dann offenen Arbeitsmappen berechnet. Dabei werden alle Abhängigkeiten innerhalb der Arbeitsmappen und Arbeitsblätter sowie zwischen den Arbeitsmappen und Arbeitsblättern aufgelöst. Zudem werden alle bisher noch nicht berechneten Zellen berechnet.

Ausgewählte Arbeitsmappen berechnen

Sie können auch nur die ausgewählten Arbeitsblätter mit UMSCHALT+F9 neu berechnen. Dadurch werden Abhängigkeiten für das Arbeitsblatt aufgelöst und alle zuvor nicht berechneten Zellen (modifiziert) wie berechnet zurückgesetzt.

In früheren Excel-Versionen war das Verhalten anders, und modifiziert Zellen wurden nach Abschluss der Berechnung nicht als berechnet festgelegt. Wenn benutzerdefinierte Funktionen auf diesem Verhalten beruhten, sollten diese Funktionen stattdessen flüchtig gemacht werden, wie im Abschnitt Flüchtige Funktionen in diesem Artikel erläutert.

Zellenbereich berechnen

Excel erlaubt auch die Berechnung von Zellenbereichen mithilfe der Visual Basic for Applications-Methoden (VBA-Methoden) Range.CalculateRowMajorOrder und Range.Calculate:

• Range.CalculateRowMajorOrder berechnet den Bereich von links nach rechts und von oben nach unten. Dabei werden alle Abhängigkeiten ignoriert.

• Range.Calculate berechnet den Bereich unter Auflösung sämtlicher Abhängigkeiten innerhalb des Bereichs.

Da CalculateRowMajorOrder keinerlei Abhängigkeiten innerhalb des berechneten Bereichs auflöst, ist die Ausführung in der Regel deutlich schneller als bei Range.Calculate. Die Methode sollte jedoch mit Bedacht verwendet werden, da sie möglicherweise nicht dieselben Ergebnisse liefert wie Range.Calculate.

Range.Calculate ist eine der nützlichsten Excel-Methoden zur Leistungsoptimierung, da Sie sie zum Messen und Vergleichen der Berechnungsgeschwindigkeit verschiedener Formeln nutzen können.

Weitere Informationen finden Sie unter Excel-Leistung: Leistungsverbesserungen und höhere Grenzwerte.

Veränderliche Funktionen

Eine veränderliche Funktion wird bei jeder Neuberechnung stets neu berechnet, auch wenn Sie anscheinend keine veränderten Werte aufweist. Durch Verwenden vieler veränderlicher Funktionen wird jede Neuberechnung verlangsamt, ohne dass ein Unterschied zu einer vollständigen Berechnung besteht. Sie können eine benutzerdefinierte Funktion veränderlich machen, indem Sie Application.Volatile in den Funktionscode aufnehmen.

Einige der in Excel integrierten Funktionen sind offenkundig veränderlich: ZUFALLSZAHL(), JETZT() und HEUTE(). Bei anderen ist die Veränderlichkeit weniger offensichtlich: BEREICH.VERSCHIEBEN, ZELLE(), INDIREKT() und INFO().

Einige Funktionen, die bisher in der Dokumentation als veränderlich bezeichnet wurden, sind es tatsächlich nicht: INDEX(), ZEILEN(), SPALTEN() und BEREICHE().

Verändernde Aktionen

Verändernde Aktionen sind Aktionen, die eine Neuberechnung auslösen. Dazu zählen u. a.:

• Das Klicken auf ein Zeilen- oder Spaltentrennzeichen im automatischen Modus.
• Das Einfügen oder Löschen von Zeilen, Spalten oder Zellen auf einem Blatt.
• Das Hinzufügen, Ändern oder Löschen definierter Namen.
• Das Umbenennen von Arbeitsblättern oder Ändern der Arbeitsblattposition im automatischen Modus.
• Das Filtern, Ausblenden oder Einblenden von Zeilen.
• Das Öffnen von Arbeitsmappen im automatischen Modus. Wenn die Arbeitsmappe zuletzt von einer anderen Version von Excel berechnet wurde, führt das Öffnen der Arbeitsmappe gewöhnlich zu einer vollständigen Berechnung.
• Das Speichern von Arbeitsmappen im manuellen Modus bei aktivierter Option Calculate before Save.

Bedingungen für die Auswertung von Formeln und Namen

Eine Formel oder ein Teil einer Formel wird (selbst im manuellen Berechnungsmodus) unmittelbar ausgewertet (berechnet), wenn Sie eine der folgenden Aktionen ausführen:

• Eingeben oder Bearbeiten der Formel.
• Eingeben oder Bearbeiten der Formel mit dem Funktionsassistenten.
• Eingeben der Formel als Argument im Funktionsassistenten.
• Auswählen der Formel in der Formelleiste oder Drücken von F9 (drücken Sie ESC zum Rückgängigmachen und Zurückkehren zur Formel) oder Klicken auf Formel auswerten.

Eine Formel wird als nicht berechnet gekennzeichnet, wenn sie auf eine Zelle oder Formel verweist (bzw. von dieser abhängig ist), die eine der folgenden Bedingungen aufweist:

• Sie wurde eingegeben.
• Sie wurde geändert.
• Sie befindet sich in einer AutoFilter-Liste, und die Dropdownliste mit den Kriterien wurde aktiviert.
• Sie wird als nicht berechnet gekennzeichnet.

Eine als nicht berechnet gekennzeichnete Formel wird ausgewertet, wenn das Arbeitsblatt, die Arbeitsmappe oder die Excel-Instanz, in dem/der sie enthalten ist, berechnet oder neu berechnet wird.

Die Bedingungen, gemäß denen ein definierter Name ausgewertet wird, unterscheiden sich von denen für eine Formel in einer Zelle:

• Ein definierter Name wird immer dann ausgewertet, wenn eine Formel, die darauf verweist, so ausgewertet wird, dass das Verwenden eines Namens in mehreren Formeln bewirken kann, dass der Name mehrfach ausgewertet wird.
• Namen, auf die keine Formel verweist, werden selbst bei einer vollständigen Berechnung nicht berechnet.

Datentabellen

Excel-Datentabellen (Registerkarte "Daten" >Gruppe>"Was-wäre-wenn-Analysedatentabelle>") sollte nicht mit dem Tabellenfeature verwechselt werden (RegisterkarteStart Registerkarte >Formatgruppe>Formatieren als Tabelle oder Registerkarte Einfügen>Tabellengruppe>Tabelle). In Excel-Datentabellen werden mehrere Neuberechnungen der Arbeitsmappe ausgeführt, die von den unterschiedlichen Werten in der Tabelle ausgehen. Zunächst berechnet Excel die Arbeitsmappe normal. Für jedes Paar aus Zeilenwert und Spaltenwert werden dann die Werte ersetzt, eine Singlethread-Neuberechnung ausgeführt und die Ergebnisse in der Datentabelle gespeichert.

Zur Neuberechnung von Datentabellen wird stets nur ein einziger Prozessor verwendet.

Datentabellen bieten Ihnen eine bequeme Möglichkeit, mehrere Variationen zu berechnen und die Ergebnisse der Variationen miteinander zu vergleichen. Wenn Sie die Berechnungsoption Automatic except Tables verwenden, löst Excel nicht bei jeder Berechnung automatisch die Mehrfachberechnungen aus, berechnet aber dennoch alle abhängigen Formeln außer Tabellen.

Steuern von Berechnungsoptionen

Excel bietet eine Reihe von Optionen, über die Sie Berechnungen steuern können. Die in Excel am häufigsten verwendeten Optionen können Sie im Menüband auf der Registerkarte Formeln in der Gruppe Berechnung ändern.

Abbildung 1. Gruppe „Berechnung“ auf der Registerkarte „Formeln“

Weitere Excel-Berechnungsoptionen können Sie aufrufen, indem Sie auf der Registerkarte Datei auf Optionen klicken. Klicken Sie im Dialogfeld Excel-Optionen auf die Registerkarte Formeln.

Abbildung 2. Berechnungsoptionen auf der Registerkarte 'Formeln' in 'Excel-Optionen'

Viele Berechnungsoptionen (Automatisch, Automatisch außer bei Datentabellen, Manuell, Vor dem Speichern die Arbeitsmappe neu berechnen) und die Iterationseinstellungen (Iterative Berechnung aktivieren, Maximale Iterationszahl, Maximale Änderung) werden auf Anwendungs- und nicht auf Arbeitsmappenebene angewendet (und sind für alle geöffneten Arbeitsmappen identisch).

Klicken Sie zum Aufrufen der erweiterten Berechnungsoptionen auf der Registerkarte Datei auf Optionen. Klicken Sie im Dialogfeld Excel-Optionen auf Erweitert. Legen Sie im Abschnitt Formeln die gewünschten Berechnungsoptionen fest.

Abbildung 3. Erweiterte Berechnungsoptionen

Wenn Sie Excel starten oder wenn in Excel keine Arbeitsmappen geöffnet sind, werden der anfängliche Berechnungsmodus und die anfänglichen Iterationseinstellungen auf Basis der ersten geöffneten Arbeitsmappe erstellt, die keine Vorlage und kein Add-In ist. Das bedeutet: Die Berechnungseinstellungen in später geöffneten Arbeitsmappen werden ignoriert. Natürlich können Sie die Einstellungen jederzeit manuell in Excel ändern. Wenn Sie eine Arbeitsmappe speichern, werden die aktuellen Berechnungseinstellungen in der Arbeitsmappe gespeichert.

Automatische Berechnung

Im Modus „Automatische Berechnung“ berechnet Excel alle geöffneten Arbeitsmappen automatisch neu, sobald Sie eine Änderung vornehmen oder eine Arbeitsmappe öffnen. In der Regel bemerken Sie die Neuberechnung nicht, die Excel beim Öffnen von Arbeitsmappen im automatischen Modus durchführt, weil seit dem Speichern der Arbeitsmappe keine Änderungen vorgenommen wurden.

Sie bemerken diese Berechnung jedoch möglicherweise, wenn Sie eine Arbeitsmappe in einer neueren Version von Excel öffnen als derjenigen, mit der die Arbeitsmappe zuletzt berechnet wurde (z. B. Excel 2016 statt Excel 2013). Da die Excel-Berechnungsmodule unterschiedlich sind, führt Excel eine vollständige Berechnung durch, sobald eine Arbeitsmappe geöffnet wird, die mit einer früheren Excel-Version gespeichert wurde.

Manuelle Berechnung

Im Modus „Manuelle Berechnung“ berechnet Excel nur dann alle geöffneten Arbeitsmappen, wenn Sie dies durch Drücken von F9 oder STRG+ALT+F9 anfordern oder wenn Sie eine Arbeitsmappe speichern. Bei Arbeitsmappen, deren Neuberechnung länger als einen Sekundenbruchteil dauert, müssen Sie den manuellen Berechnungsmodus aktivieren, um bei Verzögerungen bei Änderungen zu vermeiden.

Excel informiert Sie, sobald eine Arbeitsmappe im manuellen Modus neu berechnet werden muss, indem Berechnen auf der Statusleiste angezeigt wird. Berechnen wird auch dann auf der Statusleiste angezeigt, wenn eine Arbeitsmappe Zirkelbezüge enthält und die Iterationsoption ausgewählt ist.

Iterationseinstellungen

Wenn Ihre Arbeitsmappe beabsichtigte Zirkelverweise enthält, können Sie über die Iterationseinstellungen die maximale Anzahl der Neuberechnungen (Iterationen) der Arbeitsmappe und die Konvergenzkriterien (maximale Änderung: Beendigungszeitpunkt) steuern. Deaktivieren Sie das Kästchen für die Iteration, damit Excel Sie warnt, wenn Zirkelbezüge erkannt werden, und versucht, diese aufzulösen.

Arbeitsmappeneigenschaft „ForceFullCalculation“

Wenn Sie diese Arbeitsmappeneigenschaft auf „True“ setzen, wird die intelligente Neuberechnung in Excel deaktiviert, und bei jeder Neuberechnung werden sämtliche Formeln in allen offenen Arbeitsmappen neu berechnet. Bei einigen komplexen Arbeitsmappen ist der Zeitaufwand für die Erstellung und Pflege der für die intelligente Neuberechnung erforderlichen Abhängigkeitenstrukturen größer als die Zeitersparnis durch die intelligente Neuberechnung.

Wenn das Öffnen Ihrer Arbeitsmappe übermäßig lang dauert oder kleine Änderungen selbst im Modus für manuelle Berechnung lang dauern, ist „ForceFullCalculation“ einen Versuch wert.

Auf der Statusleiste wird Berechnen angezeigt, wenn die Arbeitsmappeneigenschaft ForceFullCalculation auf „True“ gesetzt ist.

Sie können diese Einstellung mithilfe des VBE (ALT+F11) steuern. Wählen Sie DieseArbeitsmappe im Projektexplorer (STRG+R) aus, und rufen Sie das Fenster Eigenschaften (F4) auf.

Abbildung 4. Festlegen der Arbeitsmappeneigenschaft „ForceFullCalculation“

Beschleunigen der Berechnung von Arbeitsmappen

Nutzen Sie die folgenden Schritte und Methoden, um die Berechnung Ihrer Arbeitsmappen zu beschleunigen.

Prozessorgeschwindigkeit und mehrere Kerne

Bei den meisten Versionen von Excel sorgt ein schnellerer Prozessor auch für schnellere Excel-Berechnungen. Das in Excel 2007 eingeführte Modul für Multithread-Berechnungen ermöglicht Excel eine ausgezeichnete Nutzung von Multi-Prozessor-Systemen, sodass es bei den meisten Arbeitsmappen zu beträchtlichen Leistungsverbesserungen kommt.

Bei den meisten großen Arbeitsmappen ergibt sich durch die Nutzung mehrerer Prozessoren eine Leistungssteigerung, die nahezu linear ist zur Anzahl der physischen Prozessoren. Hyperthreading auf den physischen Prozessoren liefert jedoch nur geringe Leistungsverbesserungen.

Weitere Informationen finden Sie unter Excel-Leistung: Leistungsverbesserungen und höhere Grenzwerte.

RAM

Die Auslagerung in eine Auslagerungsdatei im virtuellen Arbeitsspeicher ist langsam. Sie benötigen genügend physischen RAM für das Betriebssystem, Excel und Ihre Arbeitsmappen. Wenn es bei Berechnungen mehr als nur gelegentlich zu Festplattenaktivitäten kommt und Sie keine benutzerdefinierte Funktionen ausführen, die eine Festplattenaktivität auslösen, benötigen Sie mehr Arbeitsspeicher.

Wie erwähnt, können die neueren Versionen von Excel große Arbeitsspeicherkapazitäten effektiv nutzen. Die 32-Bit-Versionen von Excel 2007 und Excel 2010 können einzelne oder mehrere Arbeitsmappen verarbeiten, die bis zu 2 GB Arbeitsspeicher belegen.

Die 32-Bit-Versionen von Excel 2013 und Excel 2016, die das Laa-Feature (Large Address Aware) verwenden, können je nach installierter Windows-Version bis zu 3 oder 4 GB Arbeitsspeicher verwenden. Die 64-Bit-Version von Excel kann größere Arbeitsmappen verarbeiten. Weitere Informationen finden Sie im Abschnitt "Große Datasets, LAA und die 64-Bit-Version von Excel" in Excel-Leistung: Leistungsverbesserungen und höhere Grenzwerte.

Zum Erzielen einer effizienten Berechnung benötigen Sie genügend Arbeitsspeicher für die Aufnahme der größten Gruppe von Arbeitsmappen, die gleichzeitig geöffnet sein sollen, plus 1 bzw. 2 GB für Excel und das Betriebssystem, plus zusätzlichen RAM für andere laufende Anwendungen.

Messen der Berechnungszeit

Um die Arbeitsmappenberechnung beschleunigen zu können, müssen Sie die Berechnungszeit präzise messen können. Sie benötigen einen Zeitgeber, der schneller und genauer ist als die VBA-Funktion Time. Die Funktion MICROTIMER() im nachstehenden Codebeispiel verwendet Windows-API-Aufrufe an den hochauflösenden Zeitgeber des Systems. Sie kann Zeitintervalle bis zu einer kleinen Anzahl von Mikrosekunden messen. Zu beachten ist, dass Windows ein multitaskingfähiges Betriebssystem ist und eine zweite Berechnung schneller als die erste erfolgen kann, weshalb die gemessenen Zeiten meist nicht identisch sind. Messen Sie für größtmögliche Präzision die Berechnungsaufgaben mehrmals, und ermitteln Sie den Durchschnitt.

Weitere Informationen zu den signifikanten Auswirkungen des Visual Basic-Editor auf die Leistung benutzerdefinierter VBA-Funktionen finden Sie im Abschnitt „Faster VBA user-defined functions“ unter Excel performance: Tips for optimizing performance obstructions.

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Function MicroTimer() As Double
'

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

Zum Messen der Berechnungszeit müssen Sie die entsprechende Berechnungsmethode aufrufen. Diese Unterroutinen liefern die Berechnungszeit für einen Bereich, für ein Blatt oder alle geöffneten Arbeitsmappen oder die für eine vollständige Berechnung aller geöffneten Arbeitsmappen benötigte Zeit.

Kopieren Sie alle diese Unterroutinen und Funktionen in ein VBA-Standardmodul. Drücken Sie ALT+F11, um den VBA-Editor zu öffnen. Wählen Sie im Menü Einfügen die Option Modul aus, und kopieren Sie den Code in das Modul.

Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer              

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then 
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

Drücken Sie ALT+F8, um die Unterroutinen in Excel auszuführen. Wählen Sie die gewünschte Unterroutine aus, und klicken Sie dann auf Ausführen.

Abbildung 5. Das Excel-Makrofenster mit Berechnungstimern

Auffinden und Priorisieren von Berechnungshindernissen

Bei den meisten langsam berechneten Arbeitsmappen liegen nur einige wenige Problembereiche oder Hindernisse vor, die die meiste Berechnungszeit in Anspruch nehmen. Wenn Ihnen diese noch nicht bekannt sind, können Sie sie mithilfe der in diesem Abschnitt beschriebenen Detailsuche ermitteln. Wenn Ihnen die Hindernisse bekannt sind, müssen Sie die Berechnungszeit messen, die von jedem Hindernis verursacht wird, damit Sie diese mit Prioritäten für die Entfernung versehen können.

Detailsuche zum Auffinden von Hindernissen

Die Detailsuchmethode beginnt mit der Messung der Zeit für die Berechnung der Arbeitsmappe, die Berechnung jedes Arbeitsblatts und die Berechnung der Formelblöcke auf Blättern mit langsamer Berechnung. Führen Sie alle Schritte in dieser Reihenfolge aus, und notieren Sie die Berechnungszeiten.

So ermitteln Sie Hindernisse mithilfe der Detailsuchmethode

1. Vergewissern Sie sich, dass nur eine Arbeitsmappe geöffnet ist und keine anderen Aufgaben ausgeführt werden.

2. Legen Sie den manuellen Berechnungsmodus fest.

3. Erstellen Sie eine Sicherungskopie der Arbeitsmappe.

4. Öffnen Sie die Arbeitsmappe mit den Makros zur Berechnungszeitmessung, oder fügen Sie sie der Arbeitsmappe hinzu.

5. Überprüfen Sie den verwendeten Bereich, indem Sie auf allen Arbeitsblättern nacheinander STRG+ENDE drücken.

   Dadurch wird erkennbar, wo sich die letzte verwendete Zelle befindet. Wenn diese über den erwarteten Bereich hinausgeht, erwägen Sie das Löschen der überzähligen Spalten und Zeilen und das Speichern der Arbeitsmappe. Weitere Informationen finden Sie im Abschnitt „Minimizing the used range“ unter Excel performance: Tips for optimizing performance obstructions.

6. Führen Sie das Makro FullCalcTimer aus.

   Die Zeit für die Berechnung aller Formeln in der Arbeitsmappe ist in der Regel die Worst-Case-Zeit.

7. Führen Sie das Makro RecalcTimer aus.

   Eine Neuberechnung sofort im Anschluss an eine vollständige Berechnung liefert meist die Zeit des günstigsten Szenarios.

8. Berechnen Sie die Arbeitsmappenveränderlichkeit als Verhältnis der Neuberechnungszeit zur Zeit einer vollständigen Berechnung.

   Dadurch wird der Grad gemessen, zu dem veränderliche Formeln und die Auswertung der Berechnungskette Hindernisse darstellen.

9. Aktivieren Sie jedes Blatt, und führen Sie jeweils das Makro SheetTimer aus.

   Da Sie kurz zuvor die Arbeitsmappe neu berechnet haben, erhalten Sie hierdurch die Neuberechnungszeit für jedes Arbeitsblatt. Auf diese Weise sollten Sie die problematischen Arbeitsblätter bestimmen können.

10. Führen Sie das Makro RangeTimer für ausgewählte Formelblöcke aus.

11. Unterteilen Sie auf jedem problematischen Arbeitsblatt die Spalten und Zeilen in eine kleine Anzahl von Blöcken.

12. Wählen Sie nacheinander jeden Block aus, und führen Sie jeweils das Makro RangeTimer aus.

13. Führen Sie bei Bedarf eine weitere Detailsuche durch, indem Sie jeden Block in noch mehr Blöcke unterteilen.

14. Versehen Sie die Hindernisse mit Prioritäten.

Beschleunigen von Berechnungen und Verringern von Hindernissen

Es ist nicht die Anzahl der Formeln oder die Größe einer Arbeitsmappe, die die Berechnungszeit in die Höhe treiben. Ausschlaggebend sind die Anzahl der Zellbezüge und Berechnungsvorgänge sowie die Effizienz der verwendeten Funktionen.

Da die meisten Arbeitsblätter erstellt werden, indem Formeln kopiert werden, die eine Kombination aus absoluten und relativen Bezügen enthalten, ist meist eine große Anzahl von Formeln vorhanden, die wiederholte oder duplizierte Berechnungen und Bezüge enthalten.

Vermeiden Sie komplexe Mega- und Arrayformeln. In der Regel ist es besser, mit mehreren Zeilen und Spalten und weniger komplexen Berechnungen zu arbeiten. Dadurch können mithilfe der intelligenten Neuberechnung und der Multithread-Berechnung in Excel die Berechnungen besser optimiert werden. Außerdem sind Nachvollziehbarkeit und Fehlerbehebung einfacher. Es folgen einige Regeln, die zur Beschleunigung von Arbeitsmappenberechnungen beitragen.

Regel 1: Entfernen Sie duplizierte, wiederholte und unnötige Berechnungen.

Suchen Sie duplizierte, wiederholte und unnötige Berechnungen, und bestimmen Sie, wie viele Zellbezüge und Berechnungen Excel ungefähr benötigt, um das Ergebnis dieses Hindernisses zu berechnen. Überlegen Sie anschließend, wie Sie dasselbe Ergebnis mit weniger Bezügen und Berechnungen erhalten könnten.

Hierfür sind meist ein oder mehrere der folgenden Schritte erforderlich:

• Verringern Sie die Anzahl der Bezüge in jeder Formel.

• Verschieben Sie die wiederholten Berechnungen in eine oder mehrere Hilfszellen, und verweisen Sie anschließend aus den ursprünglichen Formeln auf die Hilfszellen.

• Verwenden Sie zusätzliche Zeilen und Spalten zum Berechnen und einmaligen Speichern, damit Sie sie in anderen Formeln wiederverwenden können.

Regel 2: Arbeiten Sie mit der effizientesten Funktion, die möglich ist.

Wenn Sie ein Hindernis finden, dessen Ursache eine Funktion oder Arrayformel ist, bestimmen Sie, ob es eine effizientere Möglichkeit zum Erzielen desselben Ergebnisses gibt. Beispiel:

• Nachschlagevorgänge in sortierten Daten können zehn- oder gar hundertmal effizienter als Nachschlagevorgänge in unsortierten Daten sein.

• Benutzerdefinierte VBA-Funktionen sind meist langsamer als die in Excel vordefinierten Funktionen (wenngleich durchdacht geschriebene VBA-Funktionen schnell sein können).

• Minimieren Sie die Anzahl der verwendeten Zellen in Funktionen wie SUMME und SUMMEWENN. Die Berechnungszeit ist proportional zur Anzahl der verwendeten Zellen (nicht verwendete Zellen werden ignoriert).

• Erwägen Sie das Ersetzen langsamer Arrayformeln durch benutzerdefinierte Funktionen.

Dritte Regel: Nutzen Sie die intelligente Neuberechnung und die Multithread-Berechnung.

Je effektiver Sie die intelligente Neuberechnung und die Multithread-Berechnung in Excel einsetzen, desto niedriger ist der Verarbeitungsaufwand bei den einzelnen Excel-Neuberechnungen. Deshalb gilt:

• Vermeiden Sie nach Möglichkeit veränderliche Funktionen wie INDIREKT und BEREICH.VERSCHIEBEN, es sei denn, sie sind wesentlich effizienter als die Alternativen. (Ein gut durchdachter Einsatz von BEREICH.VERSCHIEBEN ist häufig schnell.)

• Minimieren Sie die Größe der Bereiche, die Sie in Arrayformeln und Funktionen verwenden.

• Verschieben Sie Mega- und Arrayformeln in gesonderte Hilfsspalten und -zeilen.

• Vermeiden Sie Singlethread-Funktionen:

  • PHONETIC
  • ZELLE mit Argument „format“ oder Argument „address“
  • INDIREKT
  • PIVOTDATENZUORDNEN
  • CUBEELEMENT
  • CUBEWERT
  • CUBEELEMENTEIGENSCHAFT
  • CUBEMENGE
  • CUBERANGELEMENT
  • CUBEKPIELEMENT
  • CUBEMENGENANZAHL
  • ADRESSE, wenn der fünfte Parameter (sheet_name) angegeben ist
  • Jede Datenbankfunktion (DBSUMME, DBMITTELWERT usw.), die auf eine PivotTable verweist
  • FEHLER.TYP
  • HYPERLINK
  • Benutzerdefinierte Funktionen (VBA, COM-Add-In)

• Vermeiden Sie eine iterative Verwendung von Datentabellen und Zirkelbezügen: Beide werden stets per Singlethread-Berechnung berechnet.

Regel 4: Messen und testen Sie die (zeitlichen) Auswirkungen jeder Änderung.

Einige Ihrer Änderungen werden Sie möglicherweise überraschen, entweder weil Sie nicht das Ergebnis erhalten, das Sie erwartet haben, oder weil die Berechnung langsamer erfolgt, als Sie erwartet haben. Deshalb müssen Sie alle Änderungen wie folgt überprüfen:

1. Messen Sie mit dem Makro RangeTimer die Zeit für die Formel, die Sie ändern möchten.

2. Nehmen Sie die Änderung vor.

3. Messen Sie mit dem Makro RangeTimer die Zeit für die Formel.

4. Überprüfen Sie, ob die geänderte Formel immer noch die richtige Antwort zurückgibt.

Regelbeispiele

In den folgenden Abschnitten finden Sie Beispiele dafür, wie Sie mit den beschriebenen Regeln die Berechnung beschleunigen können.

Periode-bis-Datum-Summen

Nehmen wir an, Sie müssen die Periode-bis-Datum-Summen einer Spalte mit 2.000 Zahlen berechnen. Nehmen wir weiter an, dass Spalte A die Zahlen enthält und Spalte B sowie Spalte C die Periode-bis-Datum-Gesamtwerte.

Sie könnten die Formel mithilfe von SUMME schreiben, einer effizienten Funktion.

  B1=SUM($A$1:$A1)
  B2=SUM($A$1:$A2)

Abbildung 6 Beispiel für SUMME-Formeln für Periode bis Datum

Kopieren Sie die Formel bis hinunter zu B2000.

Wie viele Zellbezüge werden nun insgesamt durch SUMME addiert? B1 bezieht sich auf eine Zelle, B2000 bezieht sich auf 2.000 Zellen. Der Mittelwert sind 1.000 Bezüge pro Zelle, was insgesamt 2 Millionen Bezüge ergibt. Wenn Sie nun die 2.000 Formeln auswählen und das Makro RangeTimer ausführen, sehen Sie, dass die 2.000 Formeln in Spalte B innerhalb von 80 Millisekunden berechnet werden. Die meisten dieser Berechnungen werden sehr oft dupliziert: SUMME addiert A1 und A2 in jeder Formel von B2 bis B2000.

Sie können diese Duplizierung neutralisieren, wenn Sie die Formeln wie folgt schreiben:

  C1=A1
  C2=C1+A1

Kopieren Sie diese Formel bis hinunter zu C2000.

Wie viele Zellbezüge werden nun insgesamt addiert? Alle Formeln außer der ersten verwenden zwei Zellbezüge. Daher ist das Gesamtergebnis 1.999 × 2 + 1 = 3.999. Die Anzahl der Zellbezüge hat sich um den Faktor 500 verringert.

RangeTimer gibt an, dass die 2.000 Formeln in Spalte C in 3,7 Millisekunden berechnet werden, verglichen mit einer Berechnungszeit von 80 Millisekunden für Spalte B. Diese Änderung ergibt also nur einen Verbesserungsfaktor von 80 ÷ 3,7 = 22 statt 500, weil pro Formel ein geringer Overhead anfällt.

Fehlerbehandlung

Wenn Sie über eine berechnungsintensive Formel verfügen, bei der Sie wünschen, dass das Ergebnis bei einem Fehler als 0 angezeigt wird (was häufig bei Suchen nach genauen Übereinstimmungen vorkommt), stehen Ihnen mehrere Möglichkeiten zur Verfügung.

• Sie können eine einzelne (allerdings langsame) Formel schreiben.

  B1=IF(ISERROR(time expensive formula),0,time expensive formula)

• Sie können zwei (schnelle) Formeln schreiben:

  A1=time expensive formula

  B1=IF(ISERROR(A1),0,A1)

• Oder Sie können die einfache und schnelle Funktion WENNFEHLER verwenden und alles in einer Formel zusammenfassen:

  B1=IFERROR(time expensive formula,0)

Dynamisches Zählen eindeutiger Elemente

Abbildung 7. Beispielliste für das Zählen eindeutiger Werte

Wenn Sie eine Liste mit 11.000 Datenzeilen in Spalte A haben, die sich oft ändert, und Sie eine Formel benötigen, die die Anzahl eindeutiger Elemente in der Liste bei Ignorierung leerer Zeilen dynamisch berechnet, haben Sie mehrere Möglichkeiten.

• Matrixformeln (STRG+UMSCHALT+EINGABETASTE). Sie brauchen laut RangeTimer 13,8 Sekunden.

  {=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}
  

• SUMMENPRODUKT wird in der Regel schneller berechnet als eine gleichwertige Matrixformel. Die Berechnung dieser Formel dauert 10,0 Sekunden. Dies ergibt einen Verbesserungsfaktor von 13,8 ÷ 10,0 = 1,38, was besser, aber nicht gut genug ist.

  =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))
  

• Benutzerdefinierte Funktionen. Das folgende Codebeispiel zeigt eine benutzerdefinierte VBA-Funktion, die die Tatsache nutzt, dass der Index einer Sammlung eindeutig sein muss. Erläuterungen zu einigen der verwendeten Techniken finden Sie im Abschnitt zu benutzerdefinierten Funktionen im Abschnitt „Using functions efficiently“ unter Excel performance: Tips for optimizing performance. Die Berechnung dieser Formel (=COUNTU(A2:A11000)) dauert nur 0,061 Sekunden. Das ergibt einen Verbesserungsfaktor von 13,8 ÷ 0,061 = 226.

  Public Function COUNTU(theRange As Range) As Variant
      Dim colUniques As New Collection
      Dim vArr As Variant
      Dim vCell As Variant
      Dim vLcell As Variant
      Dim oRng As Range
  
      Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
      vArr = oRng
      On Error Resume Next
      For Each vCell In vArr
      If vCell <> vLcell Then
          If Len(CStr(vCell)) > 0 Then
               colUniques.Add vCell, CStr(vCell)
          End If
      End If
      vLcell = vCell
      Next vCell
  
      COUNTU = colUniques.Count
  End Function
  

• Hinzufügen einer Spalte mit Formeln. Wie Sie sehen werden die Daten im oben beschriebenen Beispiel sortiert (Excel braucht 0,5 Sekunden für das Sortieren der 11.000 Zeilen). Sie können dies ausnutzen, indem Sie eine Spalte von Formeln hinzufügen, die überprüft, ob die Daten in dieser Zeile mit den Daten in der Zeile davor übereinstimmen. Tun sie das nicht, gibt die Formel 1 zurück. Andernfalls wird 0 zurückgegeben.

  Fügen Sie diese Formel in Zelle B2 ein.

    =IF(AND(A2<>"",A2<>A1),1,0)
  

  Kopieren Sie die Formel, und fügen Sie eine Formel zum Summieren von Spalte B hinzu.

    =SUM(B2:B11000)
  

  Eine vollständige Berechnung aller dieser Formeln dauert 0,027 Sekunden. Das ergibt einen Verbesserungsfaktor von 13,8 ÷ 0,027 = 511.

Schlussbemerkung

Excel ermöglicht das effektive Verwalten wesentlich größerer Arbeitsblätter und bietet gegenüber früheren Versionen eine stark verbesserte Berechnungsgeschwindigkeit. Bei großen Arbeitsblättern besteht stets die Gefahr, dass ihre Erstellung so erfolgt, dass die Berechnung langsam stattfindet. Langsam berechnete Arbeitsblätter führen zu mehr Fehlern, da es den Benutzern schwerfällt, die Konzentration zu bewahren, während die Berechnung ausgeführt wird.

Mithilfe einiger einfacher Techniken können Sie die meisten nur langsam berechneten Arbeitsblätter um den Faktor 10 oder 100 beschleunigen. Sie können diese Techniken auch bereits beim Entwurf und bei der Erstellung Ihrer Arbeitsblätter anwenden, um sicherzustellen, dass sie schnell berechnet werden können.

Weitere Artikel

• Excel-Leistung: Leistungsverbesserungen und höhere Grenzwerte
• Excel performance: Tips for optimizing performance obstructions
• Entwicklerportal für Excel

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.