(0) exportieren Drucken
Alle erweitern
9 von 13 fanden dies hilfreich - Dieses Thema bewerten.

Excel 2010: Verbessern der Berechnungsleistung

Office 2010

Zusammenfassung:  In diesem Artikel wird das Verbessern der Leistung von Arbeitsmappen mit Schwerpunkt auf der Berechnungsoptimierung behandelt. 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: Leistungsverbesserungen und höhere Grenzwerte und Excel 2010: Tipps für das Entfernen von Leistungshindernissen.

Gilt für:  Microsoft Excel 2010 ¦ Microsoft Office Excel 2007 ¦ Microsoft Office Excel 2003 ¦ Microsoft Excel 2002 ¦ Microsoft Excel 2000

Veröffentlichung:  Juni 2010
Von:  MVP-MitwirkenderCharles Williams, Decision Models Limited ¦ Allison Bokone, Microsoft Corporation ¦ Chad Rothschiller, Microsoft Corporation ¦ Informationen zu den Autoren

Inhalt

Übersicht

Das "Große Raster" mit 1 Mio. Zeilen und 16.000 Spalten in Microsoft Office Excel 2007 und Microsoft Excel 2010 sowie der Aufhebung vieler anderer Grenzwerte sorgen dafür, dass im Vergleich zu früheren Excel-Versionen mit wesentlich größeren Arbeitsblättern gearbeitet werden kann. Ein einzelnes Arbeitsblatt in Excel 2007 oder Excel 2010 kann mehr als 1000-mal so viele Zellen enthalten wie in früheren Versionen.

In früheren Excel-Versionen erstellten viele Benutzer langsam berechnete Arbeitsblätter, wobei größere Arbeitsblätter in der Regel langsamer berechnet werden als kleinere. Mit der Einführung des "Großen Rasters" in Excel 2007 zählt Leistung wirklich, denn langsame Berechnungs- und Datenbearbeitungsvorgänge wie Sortieren und Filtern erschweren es Benutzern, sich auf die eigentliche Aufgabe zu konzentrieren, und aufgrund nachlassender Konzentration steigt die Fehlerquote.

In Excel 2007 und Excel 2010 wurden mehrere Funktionen 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 Datenvorgä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.

Sie können die meisten langsam berechneten Arbeitsblätter so optimieren, dass die Berechnung zehn-, hundert- oder sogar tausendmal schneller erfolgt. In diesem Artikel wird erläutert, wie Sie die Berechnung durch Bestimmen, Bewerten und Entfernen der Berechnungshindernisse in Ihren Arbeitsblättern die Berechnung beschleunigen können.

Die Bedeutung der Berechnungsgeschwindigkeit

Bei einer langsamen werdenden Berechnungsgeschwindigkeit lassen die Benutzerproduktivität und die Fähigkeit, sich auf eine Aufgabe zu konzentrieren, nach.

Excel bietet zwei Berechnungsmodi, mit denen Sie bestimmen können, wann die Berechnung erfolgt:

  • Automatische Berechnung -  Formeln werden, wenn Sie eine Änderung vornehmen, automatisch neu berechnet.

  • Manuelle Berechnung -  Formeln werden erst auf Anforderung neu berechnet (z. B. durch Drücken von F9).

Wenn die Berechnungszeit weniger als ca. eine Zehntelsekunde beträgt, haben die Benutzer das Gefühl, dass das System sofort antwortet, weshalb sie sogar bei der Dateneingabe die automatische Berechnung verwenden können.

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

Bei zunehmender Berechnungszeit müssen die Benutzer bei der Dateieingabe zur manuellen Berechnung wechseln.

Zwischen einer und zehn Sekunden werden die Benutzer wahrscheinlich zur manuellen Berechnung wechseln. Benutzerfehler und Verärgerung nehmen nach und nach zu, insbesondere bei sich wiederholenden Aufgaben, und es wird problematisch, 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

Zum Verbessern 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) sowie Ä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 bedingte Formate.

Excel berechnet weiter Zellen, die von zuvor berechneten Zellen abhängig sind, auch wenn sich der Wert der zuvor berechneten Zellen bei seiner 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 manuellen Berechnungsmodus können Sie diese 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 und durch Drücken von UMSCHALT+STRG+ALT+F9 eine vollständige Neuerstellung der Abhängigkeiten sowie eine vollständige Berechnung erzwingen.

Berechungsprozess

Excel-Formeln, die auf andere Zellen verweisen, können vor und hinter den Zellen platziert werden, auf die verwiesen wird (Vorwärts- und Rückwärtsverweis). Der Grund ist, dass Excel Zellen nicht in einer festen Reihenfolge oder nach Zeile bzw. Spalte berechnet. Stattdessen bestimmt Excel die Berechnungsreihenfolge basierend auf einer Liste aller zu berechnenden Formeln (der Berechnungskette) und den Informationen zu Abhängigkeiten jeder Formel.

Excel weist unterschiedliche Berechnungsphasen auf:

  1. Erstellen der anfänglichen Berechnungskette und Festlegen, wo die Berechnung beginnen soll – Diese Phase erfolgt, wenn die Arbeitsmappe in den Arbeitsspeicher geladen wird.

  2. Nachverfolgen von Abhängigkeiten, Kennzeichnen von Zellen als nicht berechnet und Aktualisieren der Berechnungskette – Diese Phase erfolgt auch im manuellen Berechnungsmodus bei jeder Eingabe in eine oder Änderung einer Zelle. Meist wird diese Phase so schnell ausgeführt, dass der Benutzer sie nicht bemerkt.

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

Die dritte Phase findet bei jeder Berechnung bzw. Neuberechnung statt. Excel versucht, alle Formeln in der Berechnungskette der Reihe nach zu berechnen. Doch wenn eine Formel von einer oder mehreren Formeln abhängt, die noch nicht berechnet wurden, wird die Formel in der Kette nach hinten verschoben, um später berechnet zu werden. Dies bedeutet, dass eine Formel pro Neuberechnungsvorgang mehrfach berechnet werden kann. In Excel 2000 wird für jedes Arbeitsblatt eine gesonderte Berechnungskette verwaltet, und die Arbeitsblätter werden in alphabetischer Namensreihenfolge berechnet. Ab Excel 2002 gibt es eine einzelne globale Berechnungskette, die die Berechnung der meisten Arbeitsmappen beschleunigt.

Weitere Informationen und eine detaillierte Beschreibung des Berechnungsprozesses in Excel finden Sie unter Neuberechnung in Microsoft Excel 2002.

Die zweite Berechnung einer Arbeitsmappe erfolgt häufig wesentlich schneller als die erste, und zwar aus mehreren Gründen:

  • Excel berechnet in der Regel nur Zellen, die sich geändert haben, und deren abhängige Zellen.

  • In Excel wird die letzte Berechnungsreihenfolge gespeichert und wiederverwendet, sodass der Zeitaufwand zum Bestimmen der Berechnungsreihenfolge minimiert werden kann.

  • Bei Computern mit Multikernprozessoren versuchen Excel 2007 und Excel 2010, basierend auf den Ergebnissen der vorherigen Berechnung, die Verteilung der Berechnung auf die einzelnen Prozessorkerne zu optimieren.

  • In einer Excel-Sitzung speichern sowohl Microsoft Windows als auch Excel die zuletzt verwendeten Daten und Programme für einen schnelleren Zugriff zwischen.

Berechnen von Arbeitsmappen, Arbeitsblättern und Bereichen

Sie können mithilfe der verschiedenen Excel-Berechnungsmethoden steuern, was berechnet wird.

Berechnen aller geöffneten Arbeitsmappen

Bei jeder Neuberechnung bzw. vollständigen Berechnung werden alle derzeit geöffneten Arbeitsmappen berechnet, wobei Abhängigkeiten innerhalb der und zwischen den Arbeitsmappen und Arbeitsblättern aufgelöst werden.

Berechnen ausgewählter Arbeitsmappen

Sie können auch nur die ausgewählten Arbeitsblätter neu berechnen, indem Sie UMSCHALT+F9 drücken. Dadurch werden bei den berechneten Blättern nur die Abhängigkeiten zwischen Blättern aufgelöst.

Berechnen eines Zellenbereichs

Excel lässt auch mithilfe der VBA-Methode (Visual Basic für Anwendungen) Range.Calculate die Berechnung eines Zellenbereichs zu. Das Verhalten von Range.Calculate wurde in den verschiedenen Versionen von Excel wesentlich geändert:

  • Excel 2000
    Range.Calculate wird von links nach rechts und oben nach unten unter Ignorierung aller Abhängigkeiten berechnet.

  • Excel 2002 und Excel 2003
    Range.Calculate löst die Abhängigkeiten im berechneten Bereich auf.

  • Excel 2007 und Excel 2010
    Diese beiden Versionen bieten Range Calculate-Methoden. Range.Calculate funktioniert wie in Excel 2002 und Excel 2003, doch Range.CalculateRowMajorOrder funktioniert auf dieselbe Weise wie in Excel 2000. Da CalculateRowMajorOrder keine Abhängigkeiten im berechneten Bereich auflöst, erfolgt die Ausführung wesentlich schneller. Diese Methode sollte jedoch wohl überlegt eingesetzt werden, da sie ggf. nicht dieselben Ergebnisse wie Range.Calculate liefert. Weitere Informationen finden Sie unter Excel 2010: Leistungsverbesserungen und höhere Grenzwerte.

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

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 als veränderlich angeben, indem Sie dem Funktionscode Application.Volatile hinzufügen.

Einige vordefinierte Funktionen in Excel sind offenkundig veränderlich: RAND(), NOW(), TODAY(). Die folgenden sind weniger offenkundig veränderlich: OFFSET(), CELL(), INDIRECT(), INFO().

Einige Funktionen wurden zuvor als veränderlich dokumentiert, sind es tatsächlich aber nicht: INDEX(), ROWS(), COLUMNS(), AREAS().

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 bzw. Aufheben des Ausblendens von Zeilen in Excel 2003, Excel 2007 und Excel 2010.

  • Das Öffnen einer Arbeitsmappe 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 einer Arbeitsmappe im manuellen Modus, wenn die Option Vor dem Speichern neu berechnen ausgewählt ist.

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 mithilfe des Funktions-Assistenten. 

  • Eingeben der Formel als Argument mithilfe des Funktions-Assistenten. 

  • 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 wurde als nicht berechnet gekennzeichnet.

Eine als nicht berechnet gekennzeichnete Formel wird ausgewertet, wenn sie in dem Bereich, dem Arbeitsblatt, der Arbeitsmappe oder der 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 (klicken Sie auf der Registerkarte Daten in der Gruppe Datentools auf Was-wäre-wenn-Analyse und dann auf Datentabelle) dürfen nicht mit der Tabellenfunktion (klicken Sie auf der Registerkarte Start in der Gruppe Formatvorlagen auf Als Tabelle formatieren oder auf der Registerkarte Einfügen in der Gruppe Tabellen auf Tabelle) verwechselt werden. In Excel-Datentabellen werden mehrere Neuberechnungen der Arbeitsmappe ausgeführt, die von den unterschiedlichen Werten in der Tabelle gesteuert werden. Excel berechnet zunächst die Arbeitsmappe normal. Für jedes Paar aus Zeilen- und Spaltenwerten werden die Werte ersetzt, eine Neuberechnung ausgeführt und die Ergebnisse in der Datentabelle gespeichert.

Datentabellen bieten eine bequeme Möglichkeit zur Berechnung mehrerer Variationen sowie zum Anzeigen und Vergleichen der Ergebnisse der Variationen. Wählen Sie die Excel-Berechnungsoption Automatisch außer bei Datentabellen aus, damit bei jeder Berechnung nicht automatisch die mehreren Berechnungen ausgelöst werden, aber dennoch alle abhängigen Formeln außer Tabellen berechnet werden.

Steuern von Berechnungsoptionen

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



Abbildung 1. Gruppe 'Berechnung' auf der Registerkarte 'Formeln'

Berechnungsoptionen auf der Registerkarte 'Formeln'

Klicken Sie zum Anzeigen weiterer Excel 2010-Berechnungsoptionen auf der Registerkarte Datei auf Optionen. Klicken Sie im Dialogfeld Excel-Optionen auf die Registerkarte Formeln.



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

Berechnungsoptionen in der Backstage-Ansicht

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 auf der Registerkarte Datei auf Optionen, um zu weiteren Berechnungsoptionen zu gelangen. Klicken Sie im Dialogfeld Excel-Optionen auf Erweitert. Legen Sie unter dem Abschnitt Formeln Berechnungsoptionen fest.



Abbildung 3. Erweiterte Berechnungsoptionen

Erweiterte Berechnungsoptionen in der Backstage-Ansicht

Wählen Sie in Excel-Versionen vor Excel 2007 im Menü Extras den Eintrag Optionen aus, und klicken Sie dann auf die Registerkarte Berechnung, die alle Berechnungsoptionen enthält.

Wenn Sie Excel starten oder Excel ohne geöffnete Arbeitsmappen ausgeführt wird, werden die anfänglichen Berechnungsmodus- und Iterationseinstellungen anhand der ersten Arbeitsmappe festgelegt, die keine Vorlage bzw. kein Add-In ist. Dies bedeutet, dass die Berechnungseinstellungen in Arbeitsmappen, die später geöffnet werden, ignoriert werden, wenngleich Sie die Einstellungen in Excel jederzeit manuell ändern können. 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 bei jeder Änderung sowie beim Öffnen einer Arbeitsmappe automatisch neu. Wenn Sie normalerweise eine Arbeitsmappe im automatischen Modus öffnen und Excel die Neuberechnung durchführt, ist diese nicht sichtbar, da sich seit der letzten Speicherung der Arbeitsmappe nichts geändert hat.

Sie bemerken diese Berechnung ggf., wenn Sie eine Arbeitsmappe in einer späteren Version von Excel öffnen als in derjenigen, mit der die Arbeitsmappe zuletzt berechnet wurde (z. B. Excel 2007 im Vergleich zu Excel 2003). Da die Excel-Berechnungsmodule unterschiedlich sind, führt Excel eine vollständige Berechnung durch, wenn eine Arbeitsmappe geöffnet wird, die mit einer früheren Excel-Version gespeichert wurde.

Manuelle Berechnung

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

Excel informiert Sie, wenn eine Arbeitsmappe im manuellen Modus neu berechnet werden muss, indem Berechnen auf der Statusleiste angezeigt wird. Berechnen wird auch auf der Statusleiste angezeigt, wenn Ihre Arbeitsmappe Zirkelverweise enthält und die Iterationsoption ausgewählt ist. Berechnen wird außerdem angezeigt, wenn es zu viele Abhängigkeiten gibt. Die entsprechenden Abhängigkeitsgrenzwerte wurden in Excel 2007 und Excel 2010 beträchtlich angehoben. Weitere Informationen finden Sie unterExcel 2010: Leistungsverbesserungen und höhere Grenzwerte.

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. Im Allgemeinen sollten Sie das Iterationsfeld deaktivieren, sodass Excel Sie, sofern Sie über beabsichtigte Zirkelverweise verfügen, warnt und diese nicht aufzulösen versucht.

Beschleunigen der Berechnung von Arbeitsmappen

In diesem Abschnitt werden Möglichkeiten zum Beschleunigen der Berechnung Ihrer Arbeitsmappen beschrieben.

Prozessorgeschwindigkeit und mehrere Kerne

Bei den meisten Versionen von Excel sorgt ein schnellerer Prozessor für schnellere Excel-Berechnungen.

Ab Excel 2007 gibt es neue Funktionen zur Unterstützung von Systemen mit mehreren Prozessoren. Das in Excel 2007 eingeführte Multithreadberechnungsmodul ermöglicht Excel eine ausgezeichnete Nutzung von Multiprozessorsystemen, sodass es bei den meisten Arbeitsmappen zu beträchtlichen Leistungsverbesserungen kommt.

Excel 2010 bietet zusätzliche neue Funktionen zur Unterstützung von Systemen mit mehreren Prozessoren.

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

Arbeitsspeicher (RAM)

Das Auslagern in eine Auslagerungsdatei mit virtuellem Arbeitsspeicher erfolgt langsam. Sie benötigen für das Betriebssystem, Excel und Ihre Arbeitsmappen ausreichend physischen Arbeitsspeicher. 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 Arbeitsspeicher effektiv nutzen, und Excel 2007 und Excel 2010 können eine einzelne oder mehrere Arbeitsmappen verarbeiten, die bis zu 2 GB Arbeitsmappenspeicher belegen. 64-Bit-Excel kann noch größere Arbeitsmappen verarbeiten. Weitere Informationen finden Sie im Abschnitt "Große Datasets und 64-Bit-Excel" in Excel 2010: 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 256 bzw. 512 MB für Excel und das Betriebssystem plus zusätzlichen Arbeitsspeicher für weitere eventuell ausgeführte Anwendungen. Im Windows Task-Manager können Sie prüfen, wie viel Arbeitsspeicher Excel belegt.



Abbildung 4. Task-Manager mit Excel-Speicherauslastung

Windows-Task-Manager – Excel-Verwendung


Weitere Informationen finden Sie im Abschnitt "Große Datasets und 64-Bit-Excel" in Excel 2010: Leistungsverbesserungen und höhere Grenzwerte.

Messen der Berechnungszeit

Zum Beschleunigen der Berechnung von Arbeitsmappen müssen Sie die Berechnungszeit präzise messen können. Sie benötigen einen Timer, der schneller und präziser als die VBA-Funktion Time funktioniert. Die im folgenden Codebeispiel gezeigte MICROTIMER()-Funktion arbeitet mit Aufrufen der Windows-API an den hoch auflösenden Systemtimer. 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. Führen Sie zum Erzielen einer größtmöglichen Präzision die Berechnungsaufgaben mehrmals durch, und ermitteln Sie den Durchschnitt.

Weitere Informationen dazu, wie der Visual Basic-Editor sich wesentlich auf die Leistung benutzerdefinierter VBA-Funktionen auswirken kann, finden Sie im Abschnitt "Schnellere benutzerdefinierte VBA-Funktionen" in Excel 2010: Tipps für das Entfernen von Leistungshindernissen.

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

Sie müssen alle diese Unterroutinen und Funktionen in ein VBA-Standardmodul kopieren. Drücken Sie zum Öffnen des VBA-Editors ALT+F11. Klicken Sie im Menü Einfügen auf Modul, und kopieren Sie dann 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.Calculation = 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 zum Ausführen der Unterroutinen in Excel 2010 ALT+F8, oder klicken Sie auf Makro wiedergeben. Wählen Sie die gewünschte Unterroutine aus, und klicken Sie dann auf Ausführen.



Abbildung 5. Das Excel-Makrofenster mit Berechnungstimern

Excel-Makrofenster

Auffinden und Entfernen 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 Berechnungszeit der Arbeitsmappe sowie der einzelnen Arbeitsblätter und blockiert anschließend Formeln auf langsam berechneten Blättern. 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 "Minimieren des verwendeten Bereichs" in Excel 2010: Tipps für das Entfernen von Leistungshindernissen.

  6. Führen Sie das Makro FullCalcTimer aus.

    Die Zeit zum Berechnen aller Formeln in der Arbeitsmappe ist im Allgemeinen die des ungünstigsten Szenarios.

  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 die einzelnen Blätter, und führen Sie nacheinander 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.

    1. Unterteilen Sie bei jedem problematischen Arbeitsblatt die Spalten und Zeilen in kleinere Anzahlen von Blöcken.

    2. Wählen Sie die Blöcke nacheinander aus, und wenden Sie anschließend das Makro RangeTimer auf den Block an.

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

  11. Versehen Sie die Hindernisse mit Prioritäten.

Beschleunigen von Berechnungen und Verringern von Hindernissen

Die Beanspruchung von Berechnungszeit wird nicht von der Anzahl von Formeln oder Größe einer Arbeitsmappe bestimmt, sondern von der Anzahl der Zellbezüge und Berechnungsvorgänge sowie der 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 Multithreadberechnung in Excel 2010 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 benötigt, um das Ergebnis dieses Hindernisses zu berechnen. Überlegen Sie anschließend, wie Sie dasselbe Ergebnis mit weniger Bezügen und Berechnungen erhalten.

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 möglichst mit der effizientesten Funktion.

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 überlegt geschriebene VBA-Funktionen schnell sein können).

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

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

Regel 3: Setzen Sie die intelligente Neuberechnung überlegt ein.

Je besser Sie mit der intelligenten Neuberechnung in Excel arbeiten, desto niedriger ist der Verarbeitungsaufwand bei den einzelnen Excel-Neuberechnungen, weshalb gilt:

  • Vermeiden Sie nach Möglichkeit veränderliche Funktionen wie INDIRECT und OFFSET, es sei denn, sie sind wesentlich effizienter als die Alternativen. (Durchdacht eingesetzt, ist OFFSET 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.

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

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

  1. Messen Sie die Zeit der zu ändernden Formel mithilfe des Makros RangeTimer.

  2. Nehmen Sie die Änderung vor.

  3. Messen Sie die Zeit der geänderten Formel mithilfe des Makros RangeTimer.

  4. Prüfen Sie, ob die geänderte Formel immer noch das ordnungsgemäße Ergebnis liefert.

Beispiele der Regeln

In den folgenden Abschnitten werden Beispiele beschrieben, wie die Berechnung durch Befolgen der Regeln beschleunigt werden kann.

Periode-bis-Datum-Summen

Sie müssen beispielsweise die Periode-bis-Datum-Summen einer Spalte mit 2000 Werten berechnen. Angenommen, Spalte A enthält die Werte, und die Spalten B und C sollen die Periode-bis-Datum-Summen enthalten.

Sie könnten die Formel mithilfe der effizienten SUM-Funktion schreiben.

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


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

Periode bis Datum (Formelbeispiel)

Kopieren Sie anschließend die Formel hinunter nach B2000.

Wie viele Zellbezüge werden durch SUM insgesamt addiert? B1 bezieht sich auf eine Zelle, B2000 auf 2000 Zellen. Der Durchschnitt ist 1000 Bezüge pro Zelle, weshalb die Anzahl der Bezüge 2 Mio. beträgt. Bei Auswählen der 2000 Formeln und Verwenden des Makros RangeTimer ergibt sich, dass die 2000 Formeln in Spalte B in 80 Millisekunden berechnet werden. Viele dieser Berechnungen werden mehrfach dupliziert, denn die SUM-Funktion addiert A1 mit A2 in jeder Formel von B2:B2000.

Sie können diese Duplizierung vermeiden, indem Sie die Formeln wie folgt schreiben.

C1=A1
C2=C1+A1

Kopieren Sie diese Formel anschließend hinunter nach C2000.

Wie viele Zellbezüge werden nun insgesamt addiert? Alle Formeln außer der ersten verwenden zwei Zellbezüge. Deshalb beträgt die Summe 1999x2+1=3999. Dies ist ein Faktor von 500 Zellbezügen weniger.

RangeTimer gibt an, dass die 2000 Formeln in Spalte C in 3,7 Millisekunden im Vergleich zu 80 Millisekunden für Spalte B berechnet werden. Der Verbesserungsfaktor dieser Änderung beträgt nur 80:3,7=22 anstatt 500, da ein kleiner zusätzlicher Verarbeitungsaufwand pro Formel 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)
    
  • Ab Excel 2007 können Sie die einfache und schnelle IFERROR-Funktion mit einer Formel verwenden:

    B1=IFERROR(time expensive formula,0)
    

Dynamisches Zählen eindeutiger Elemente



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

Anzahl (eindeutiges Datenbeispiel)

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.

Arrayformeln

Sie können mit der folgenden Arrayformel arbeiten (drücken Sie STRG+UMSCHALT+EINGABE).

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

RangeTimer misst für diese Formel 13,8 Sekunden.

SUMMENPRODUKT

SUMPRODUCT liefert zumeist eine schnellere Berechnung als eine entsprechende Arrayformel.

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

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.

Benutzerdefinierte Funktionen

Das folgende Codebeispiel zeigt eine benutzerdefinierte VBA-Funktion, die berücksichtigt, dass der Index einer Auflistung eindeutig sein muss. Eine Erläuterung einiger verwendeter Techniken finden Sie im Abschnitt zu benutzerdefinierten Funktionen unter "Effizientes Verwenden von Funktionen" in Excel 2010: Tipps für das Entfernen von Leistungshindernissen.

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

Die Berechnung dieser Formel (=COUNTU(A2:A11000)) dauert 0,061 Sekunden, was den Verbesserungsfaktor 13,8:0,061=226 ergibt.

Hinzufügen einer Spalte mit Formeln

Wenn Sie sich das vorherige Datenbeispiel ansehen, erkennen Sie, dass es sortiert ist (Excel benötigt 0,5 Sekunden zum Sortieren der 11.000 Zeilen). Dies können Sie sich zu Nutzen machen, indem Sie eine Spalte mit Formeln hinzufügen, die prüft, ob die Daten in dieser Zeile den Daten in der vorherigen Zeile entsprechen. Falls nicht, gibt die Formel 1, andernfalls 0 zurück.

Fügen Sie diese Formel der Zelle B2 hinzu.

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

Kopieren Sie anschließend die Formel nach unten, 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, was den Verbesserungsfaktor 13,8:0,027=511 ergibt.

Schlussbemerkung

Excel 2010 ermöglicht das effektive Verwalten wesentlich größerer Arbeitsblätter und bietet 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.

Durch den Einsatz verschiedener übersichtlicher Methoden können Sie die meisten langsam berechneten Arbeitsblätter um einen Faktor von 10 bzw. 100 schneller berechnen lassen. Sie können diese Methoden auch beim Entwerfen und Erstellen von Arbeitsblättern befolgen, um sicherzustellen, dass ihre Berechnung schnell erfolgt.

Informationen zu den Autoren

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 beliebiten 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.

Weitere Ressourcen

Fanden Sie dies hilfreich?
(1500 verbleibende Zeichen)
Vielen Dank für Ihr Feedback.

Community-Beiträge

HINZUFÜGEN
Anzeigen:
© 2014 Microsoft. Alle Rechte vorbehalten.