Hallo Jens,
vielleicht hilft dieser Tipp, ist zwar etwas zu lesen aber wenn es hilft. (Quelle: SmartTools Excel Weekly)
Berechnungsmethoden steuern und optimieren, Teil 1
Versionen: Excel 2013, 2010, 2007, 2003 und 2002/XP
Es kann manchmal recht lange dauern, bis Excel alle Formeln einer Tabelle berechnet hat. Zeit koennen Sie sparen, indem Sie die automatische auf eine manuelle Berechnung umschalten. Doch das hat mitunter unerwartete Konsequenzen.
In diesem Tipp stellen wir Ihnen deshalb die verschiedenen Berechnungsmethoden von Excel vor und zeigen, wie Sie Kalkulationen optimieren.
Normalerweise berechnet Excel Formelergebnisse neu, sobald sich die zugrundeliegenden Werte aendern. Dabei wendet Excel ein besonders effizientes Berechnungsverfahren an, so dass nur die Formelergebnisse aktualisiert werden, die tatsaechlich von Werteaenderungen betroffen sind.
Dennoch stoesst auch dieses Verfahren an seine Grenzen, wenn Tabellen besonders umfangreich sind und viele oder komplexe Formeln enthalten. Unter Umstaenden entstehen dann nach der Eingabe oder Aenderung von Werten unzumutbare Wartezeiten.
Um dem vorzubeugen, koennen Sie die automatische Neuberechnung abschalten, so dass eine Aktualisierung der Formelergebnisse nur auf Befehl erfolgt:
In Excel 2013, 2010 und 2007 aktivieren Sie dazu im Menue der Befehlsschaltflaeche FORMELN-BERECHNUNG- BERECHNUNGSOPTIONEN die Option MANUELL.
In Excel 2003 und 2002/XP waehlen Sie EXTRAS-OPTIONEN an und im danach angezeigten Dialogfenster wechseln Sie auf die Registerkarte BERECHNUNG. Dort aktivieren Sie im Bereich BERECHNUNG die Option MANUELL, was Sie mit OK bestaetigen.
Formeln, die Sie neu eingeben, werden jetzt zwar immer noch sofort berechnet. Um vorhandene Formeln kuemmert sich Excel aber erst, wenn Sie explizit das Kommando zur Neuberechnung geben.
Nur beim Speichern berechnet Excel Ihre Arbeitsmappen automatisch neu, damit Ihnen beim naechsten Oeffnen eine aktuelle Version zur Verfuegung steht. Falls sich der Speichervorgang dadurch zu sehr verzoegern sollte, koennen Sie auch diese automatische Berechnung abstellen:
In Excel 2013, 2010 und 2007 waehlen Sie dazu DATEI-OPTIONEN an und wechseln zur Kategorie FORMELN. Unter der Berechnungsoption MANUELL entfernen Sie dann das Haekchen im Kontrollkaestchen VOR DEM SPEICHERN DIE ARBEITSMAPPE NEU BERECHNEN. Zur Bestaetigung klicken Sie auf OK.
In Excel 2003 und 2002/XP muessen Sie wieder per EXTRAS- OPTIONEN zur Registerkarte BERECHNUNG wechseln und dort das Kontrollkaestchen VOR DEM SPEICHERN NEU BERECHNEN deaktivieren. Danach klicken Sie auf OK.
Neben diesen Berechnungsmethoden bietet Excel zudem die Option AUTOMATISCH AUSSER BEI DATENTABELLEN (2013, 2010 und
2007) bzw. AUTOMATISCH AUSSER BEI MEHRFACHOPERATIONEN (2003 und 2002/XP). Dabei werden nur Bereiche fuer „Was-waere- wenn-Analysen“ von der Berechnung ausgenommen, der Rest wird wie gewohnt automatisch berechnet. Die Option laesst sich wie die manuelle Berechnung per Menuebandbefehl bzw. im Optionen-Dialogfenster auswaehlen.
Wenn Sie bei der manuellen Berechnung die aktuellen Formelergebnisse sehen wollen, brauchen Sie nur das Kommando zur Neuberechnung geben - am schnellsten per Tastenkombination. Folgende Varianten stehen zur Verfuegung:
* F9: berechnet alle geoeffneten Tabellen und Arbeitsmappen
neu, wobei nur die Formeln aktualisiert werden, die von
Werteaenderungen betroffen sind.
* Umschalt+F9: berechnet nur das aktuelle Tabellenblatt neu
und ist damit meist schneller als F9.
* Strg+Alt+F9: berechnet alle geoeffneten Tabellen und
Arbeitsmappen neu, wobei alle Formeln neu berechnet werden
- auch die, deren Basiswerte sich nicht geaendert haben.
Ausserdem aktualisiert die Tastenkombination Formeln mit
benutzerdefinierten VBA-Funktionen.
* Strg+Alt+Umschalt+F9: funktioniert wie Strg+Alt+F9 und
erzeugt zudem eine aktualisierte Fassung der dateiinternen
Formelabhaengigkeiten.
Ob eine Arbeitsmappe automatisch oder manuell berechnet werden soll, merkt sich Excel beim Speichern der Datei. Das heisst aber trotzdem nicht, dass die Datei beim naechsten Oeffnen wieder in diesem Berechnungsmodus geladen wird - ein Verhalten, das viele Anwender verwirrt.
Grund dafuer ist, dass die Berechnungsmethode eine programmweite Einstellung ist und somit in allen geladenen Arbeitsmappen gleich ist: Wenn Sie die Berechnung in einer Arbeitsmappe auf manuell umschalten, wirkt sich das auch auf andere, parallel geoeffnete Arbeitsmappen aus.
Entscheidend ist zudem die Reihenfolge, in der Sie Arbeitsmappen in Excel oeffnen. Wenn Sie zuerst eine Mappe mit manueller Berechnung oeffnen, werden auch alle danach geoeffneten Mappen manuell berechnet. Das gilt im gleichen Sinne fuer automatisch berechnete Arbeitsmappen.
Problematisch daran ist, dass nicht nur die von Ihnen selbst geoeffneten Dateien die Berechnungsmethode beeinflussen, sondern auch die Dateien, die Excel beim Starten automatisch laedt. Dazu zaehlen die „persoenliche Makroarbeitsmappe“, Dateien im XLSTART-Ordner oder installierte Add-Ins (XLA/M- Dateien).
Wenn also schon in der leeren Arbeitsmappe, die Excel nach dem Programmstart anzeigt, der manuelle Berechnungsmodus aktiv ist, muessen Sie die Berechnungsmethoden der im Hintergrund geladenen Arbeitsmappen ueberpruefen und gegebenenfalls umstellen.
So viel zu den grundlegenden Berechnungsoptionen. Wie Sie Berechnungen weiter optimieren, erfahren Sie im zweiten Teil dieses Tipps.
Berechnungsmethoden steuern und optimieren, Teil 2
Wenn Excel unverhaeltnismaessig lange braucht, um die Formeln einer Tabelle zu berechnen, kann das Umschalten auf eine manuelle Berechnung Zeit sparen. Wie Sie aus dem ersten Teil dieses Tipps wissen, sind davon aber auch alle anderen, derzeit geoeffneten Arbeitsmappen betroffen. Ausserdem kann eine Neuberechnung auf Befehl immer noch viel Zeit in Anspruch nehmen. Darum stellen wir Ihnen in diesem zweiten Teil weitere Optimierungsmassnahmen vor.
Dass sich die Berechnungsmethode nicht je nach Arbeitsmappe einstellen laesst, fuehrt mitunter zu Irritationen.
Womoeglich wundern Sie sich, warum bestimmte Formelergebnisse nicht mehr stimmen, bis Sie herausfinden, dass die Aktivierung der manuellen Berechnung in einer ganz anderen Arbeitsmappe der Grund dafuer ist.
Um getrennt zu steuern, wie Arbeitsmappen berechnet werden, oeffnen Sie sie in eigenen Excel-Instanzen. Starten Sie Excel also ein zweites Mal und oeffnen Sie dann zum Beispiel eine Arbeitsmappe, die nur manuell berechnet werden soll, in dieser zweiten Instanz:
-
Oeffnen Sie zuerst die Arbeitsmappe(n), die Sie mit
normaler, automatischer Berechnung bearbeiten wollen.
Sorgen Sie mit den im ersten Teil beschriebenen Verfahren
dafuer, dass die automatische Berechnung tatsaechlich
aktiviert ist.
-
Starten Sie Excel ein zweites Mal, indem Sie es ueber das
Windows-Startmenue aufrufen. In Windows 8 klicken Sie im
Startbildschirm mit der rechten Maustaste auf das Excel-
Symbol und in der Befehlsleiste waehlen Sie NEUES FENSTER
OEFFNEN an.
-
Waehlen Sie in der zweiten Excel-Instanz den OEFFNEN-
Befehl an, um die Datei zu oeffnen, die Sie manuell
berechnen wollen.
-
Aktivieren Sie in dieser Datei den manuellen
Berechnungsmodus.
Sie haben jetzt zwei Excel-Fenster mit voneinander unabhaengigen Berechnungsmethoden. Beachten Sie aber, dass zwei separate Excel-Instanzen auch mehr Systemressourcen beanspruchen.
Vielleicht moechten Sie aber auch generell alle Arbeitsmappen anfaenglich mit automatischer Berechnung oeffnen, unabhaengig davon, in welchem Modus sie gespeichert wurden und welcher Modus gerade aktiv ist. Das erreichen Sie mit einer VBA-Loesung, die Sie in einem Add-In im XLSTART- Ordner speichern:
-
Legen Sie eine neue, leere Arbeitsmappe an.
-
Wechseln Sie mit Alt+F11 in die Programmierumgebung von
Excel.
-
Druecken Sie Strg+R, um ins Fenster des Projekt-Explorers
zu wechseln.
-
Suchen Sie darin das „VBAProject“ der aktuellen
Arbeitsmappe - zu erkennen an der Dateibezeichnung.
Blenden Sie - falls das noch nicht der Fall ist - den
untergeordneten Zweig „Microsoft Excel Objekte“ ein und
doppelklicken Sie in diesem Zweig auf
„DieseArbeitsmappe“.
-
Es oeffnet sich ein Codefenster, in dem Sie folgende VBA-
Anweisungen eingeben:
Dim WithEvents oXlApp As Excel.Application
Private Sub oXlApp_NewWorkbook(ByVal Wb As Workbook)
On Error Resume Next
oXlApp.Calculation = xlCalculationAutomatic End Sub
Private Sub oXlApp_WorkbookOpen(ByVal Wb As Workbook)
On Error Resume Next
oXlApp.Calculation = xlCalculationAutomatic End Sub
Private Sub Workbook_Open()
On Error Resume Next
Set oXlApp = Excel.Application
End Sub
-
Klicken Sie in der Symbolleiste der Programmierumgebung
auf das SPEICHERN-Symbol.
-
Im danach angezeigten Dialogfenster waehlen Sie als
DATEITYP je nach Excel-Version „Excel-Add-In (*.xlam)“
oder „Microsoft Office Excel-Add-In (*.xla)“ aus.
-
Im Feld DATEINAME geben Sie folgende Pfadeingabe ein, die
Sie mit Return abschliessen:
%appdata%\Microsoft\Excel\XLSTART\
-
Damit wechseln Sie in den XLSTART-Ordner, aus dem alle
Arbeitsmappen automatisch geladen werden. Geben Sie im
Feld DATEINAME jetzt den tatsaechlich gewuenschten
Dateinamen ein - zum Beispiel „AutoBerechnung“ - und
klicken Sie auf die Schaltflaeche SPEICHERN.
-
Verlassen Sie die Programmierumgebung per DATEI-
SCHLIESSEN UND ZURUECK ZU MICROSOFT EXCEL.
-
Beenden Sie Excel. Die Aenderungen an der in Schritt 1
angelegten Arbeitsmappe speichern Sie nicht noch einmal.
Ab dem naechsten Start von Excel sorgt der VBA-Code dafuer, dass beim Oeffnen oder beim Anlegen einer Arbeitsmappe generell die automatische Berechnung aktiviert wird.
VBA-Code kann auch dazu beitragen, dass Neuberechnungen waehrend des manuellen Berechnungsmodus’ schneller ausgefuehrt werden. Das funktioniert, indem Sie nur die Tabellenbereiche berechnen lassen, die gerade relevant sind.
Mit dem folgenden Makro koennten Sie zum Beispiel nur die aktuelle Zellauswahl berechnen lassen:
Sub AuswahlBerechnen()
Selection.Calculate
End Sub
Anstelle von „Selection“ koennen Sie natuerlich auch ein konkretes „Range“-Objekt einsetzen - etwa:
ActiveSheet.Range(„A1:smiley:100“)
Die „Calculate“-Methode aktualisiert neben dem angegebenen Bereich auch alle davon abhaengigen Formelzellen. In Excel
2007 und neuer koennen Sie das unterbinden, indem Sie die Methode „CalculateRowMajorOrder“ verwenden - also etwa:
Selection.CalculateRowMajorOrder
Das entspricht uebrigens der Berechnungsmethode von Excel 97-2000.
Bei all diesen Verfahren sollte auch fuer Sie eine Berechnungsmethode dabei sein, die eine geschwindigkeitsoptimierte Bearbeitung Ihrer Arbeitsmappen ermoeglicht.
Gruß - Wolfgang
