Hast du bei manchen Excel-Dateien schon einmal das Gefühl gehabt, dass Excel mit angezogener Handbremse arbeitet?
Alles wirkt etwas zäh und Excel ist permanent dabei, irgendwelche Ergebnisse neu zu berechnen, obwohl du nur ein paar unscheinbare Werte verändert hast.
Ein Grund dafür könnte die Verwendung von volatilen Funktionen sein.
Was das genau ist und was das für dich bedeutet, erkläre ich dir im heutigen Artikel.
Volatil: Was heißt das?
Der Begriff „volatil“ kommt aus dem Lateinischen und bedeutet soviel wie flüchtig, veränderlich, beweglich. Er findet z.B. in der Finanzmathematik im Zusammenhang mit Aktienkursen Verwendung, aber auch in der Statistik, der Informatik oder der Chemie.
Uns interessiert heute aber nur, was „volatil“ mit Excel zu tun hat: Es gibt einige Funktionen, die ebenfalls „flüchtig“ oder „veränderlich“ sind. Funktionen also, deren Ergebnisse permanent neu berechnet werden.
Volatile Funktionen in Excel
Warum sollte man sich Gedanken über so etwas machen? Nun, üblicherweise verwendet Excel ein sehr cleveres Modell für Neuberechnungen. Es werden normalerweise immer nur die Zellergebnisse neu kalkuliert, wenn sich ein Wert in einer Zelle ändert, auf die sich die Ergebniszelle bezieht.
So wird beispielsweise eine in Zelle A11 stehende Summenfunktion =SUMME(A1:A10) immer nur dann neu berechnet, wenn sich einer der Werte im Bereich A1:A10 verändert. Verändere ich hingegen irgendeinen anderen Wert in meinem Arbeitsblatt, berechnet Excel meine Summe in A11 auch nicht neu.
Nun gibt es aber Excel-Funktionen, die IMMER neu berechnet werden, egal ob sich nun der Wert in einer abhängigen Zelle ändert oder in irgendeiner x-beliebigen anderen Zelle etwas eingegeben oder verändert wird. Hier spricht man von volatilen Funktionen. Ein sehr offensichtliches Beispiel dafür ist die JETZT-Funktion:
=JETZT()
Sie gibt das aktuelle Datum und die aktuelle Uhrzeit an. Sobald an irgendeiner Stelle in meiner Arbeitsmappe ein Wert eingegeben oder verändert wird (oder du alternativ einfach die Funktionstaste F9 drückst), berechnet die JETZT-Funktion auch die Uhrzeit neu.
Was die Angelegenheit noch etwas heikler macht: Auch andere Zellen, die von meinen volatilen Funktionen abhängig sind, werden damit permanent neu berechnet. Und in großen Kalkulationsmodellen kann das zu einem echten Zeitfresser werden.
Welche Funktionen sind volatil
Leider sieht man es einer Funktion nicht direkt an, ob sie volatil ist oder nicht. Daher habe ich hier eine kleine Übersicht zusammengestellt, welche Funktionen davon betroffen sind:
- BEREICH.VERSCHIEBEN
- HEUTE
- INDIREKT
- INFO
- JETZT
- ZELLE (mit Parameter „Dateiname“)
- ZUFALLSBEREICH
- ZUFALLSZAHL
Wenn du also diese Funktionen in deinen Arbeitsmappen einsetzt, könnte ein wenig Vorsicht angebracht sein.
Welche Ereignisse lösen eine Neuberechnung aus?
Wie ich oben schon erläutert habe, führt die Eingabe oder Veränderung eines Wertes in einer beliebigen Zelle bereits zu einer Neuberechnung. Das ist aber leider noch nicht alles.
Auch jede der folgenden Aktivitäten führt dazu, dass die eingesetzten volatilen Funktionen und damit auch sämtliche von ihnen abhängigen Zellen neu kalkuliert werden:
- Zeilen oder Spalten einfügen oder löschen
- Zeilen ein- und ausblenden (nicht jedoch Spalten)
- Arbeitsblätter umbenennen
- Arbeitsblätter umsortieren (d.h. Reihenfolge ändern)
- Benannte Bereiche (Namen) hinzufügen, verändern oder löschen
- Sortieren und Filtern
Spezialfall Bedingte Formatierung
Die geniale und extrem hilfreiche Bedingte Formatierung ist in diesem Zusammenhang ein Sonderfall, der noch erwähnt werden sollte. Wie du wahrscheinlich weist, lassen sich in den Formatierungsregeln auch Formeln und Funktionen einsetzen. Da bei jeder Neuberechnung auch überprüft werden muss, ob sich die Formatierung ändert, wird somit jede Funktion, die in einer bedingten Formatierung verwendet wird, automatisch auch volatil.
Hilfe! Was soll ich jetzt tun?
Ich gebe zu, das hört sich alles sehr dramatisch an. Das ist aber gar nicht meine Absicht. In sehr vielen Kalkulationsblättern wirst du wahrscheinlich keine großen Auswirkungen spüren.
Setzt du jedoch komplexe, umfangreiche Kalkulationsmodelle ein, könnte sich die Performance bei der Verwendung der genannten Funktionen spürbar verschlechtern. Man sollte einfach im Hinterkopf behalten, dass es diese Problematik gibt und dass man bei Performance-Problemen (unter anderem) gezielt nach volatilen Funktionen Ausschau halten und diese – sofern möglich – durch Alternativen ersetzen sollte.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Pingback: Excel-Quickies im Dezember | Der Tabellen-Experte
Wenn es bei der Berechnung dann mal wieder etwas länger dauert und man dann doch mal etwas an der Liste noch „Arbeiten“ möchte, kann man die Automatische Berechnung auch abschalten (Excel2007 -> Optionen -> Formeln -> Berechnungsoptionen-> Manuell).
Dann aber nicht wundern, wenn man vergisst „F9“ zu tippen für die aktuellen Zellen und nur alte Inhalte zu sehen sind.
Habe die Erfahrung gemacht, wenn man über mehrere Tabellenblätter hinweg solche volatilen Befehle nutzt, wird es mit der Berechnungszeit immer länger.
Nutzt man die Befehle nur im Zusammenhang des gleichen Tabellenblattes, also ohne z.B. einen Verweis wie „TABELLE2!$A$5“, dann ist die Berechnungszeit weitaus geringer.
Selbst wenn der Verweis auf die eigene Tabelle im eigenen Tabellenblatt mit TabellXY! geht, sollte das unbedingt vermieden werden.
Ist mir durch „copy“ und paste mal passiert.
Durch Eleminieren dieser Verweise (Alles in ein Tabellenblatt kopiert), konnte die Tabelle wieder in „Echtzeit“ berechnet werden und nicht, wie mit diesen Verweisen mit fast 2 Minuten.
Hatte dabei die Vereise auf die eigenen Tabelle zuerst nicht eliminiert, wobei die Rechenzeit wie beschrieben erheblich länger war.
Hallo -Fragezeichen-
danke für den Tipp!
Schöne Grüße,
Martin
Ich habe einen Veriesgenerator auf Basis der Regeln/Tabellen eines Rollenspiels in Excel erstellt, damit das, den Spielfluss bremsende, Konsultieren von Tabellen und Erwürfeln von Ergebnissen entfällt. Dabei werden extrem viele Zufallszahlen in aufeinander Bezug nehmenden Tabellen eingesetzt. Z. B.: Raum/Gang > Länge des Ganges > Besonderheiten des Ganges > Streunendes Monster > Art des Monsters > Ausrüstung des Monsters.
Mittlerweile gibt es erhebliche Verzögerungen und sogar Abstürze. Schätzungsweise werden an die 100 Würfel (Zufallszahlen) gerollt.
Den Tipp mit nur einem Tabellenblatt hört sich interessant an, würde die Übersichtlichkeit von 10 Tabellenblättern aber zunichte machen. Gibt es denn noch weitere Optimierungstipps? Kann man z. B. die Berechnung eines Tabellenblattes per Befehl an- /ausschalten?
Hallo Florian,
Microsoft selbst hat einen Artikel zum Thema „Performance“ in Excel geschrieben. Vielleicht hilft Dir ja das ein wenig weiter:
Excel 2010: Verbessern der Berechnungsleistung
Grüße,
Martin
Hallo, vielen Dank für diese Erläuterungen.
Ab wann gilt den ein Tabellenblatt als komplex und umfangreich?
Gibt es eine „mindestsystemvoraussetzung“ an den PC um einen Performanceverlust durch die Verwendung von X volatilen Funktionen auszugleichen?
Vielen Dank.
Marius Schon
Hallo Marius,
diese Frage lässt sich nicht pauschal beantworten, denn es von vielen unterschiedlichen Faktoren ab Anzahl der Tabellenblätter, Anzahle der Datensätze in den Blättern, Anzahl und Komplexität der Formeln, wieviele bedingte Formatierungen sind im Einsatz …
Hier hilft wirklich nur, im Einzelfall für sich zu entscheiden, ob die Performance akzeptabel ist oder nicht. Und dabei gegebenenfalls das Thema mit den volatilen Funktionen im Hinterkopf zu haben.
Grüße,
Martin
Hallo Marius,
ist zwar schon uralt, aber vllt liests ein anderer:
1) 64bit Version statt 32 bit Version nutzen
2) Formeln/Berechnung/manuell –> „ganze Mappe neu berechnen“ und „einzelnes Blatt neu berechnen“ sind möglich
3) Die Zufallsfunktion auslagern in ein Makro (VBA) und die Ergebnisse in Excel weiterverwenden
–> bei Logischen Operationen (reine Arithmetik, Boolsche Logik, …) gilt aus meiner Sicht die Fasutformel:
Makros in VBA laufen ca. 100x schneller als reines Abbilden in Excel
Die Zahl greife ich nicht aus der Luft, ich habe eine 700MB Excel Dateien-Landschaft programmiert für meine beruflichen Zwecke, und das sind meine Erfahrungswerte – weshalb ich die Logik auch entsprechend konsequent auslagere!
LG th
Ergänzung:
https://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42/excel-vba-speed-and-efficiency/
Referenz gefunden:
„For Duration = 1 To 100
Attained_Age = Range(„Issue_Age“) + Duration
Next Duration
In the following code the variable Issue_Age is read in only once from the worksheet and traffic between VBA and Excel is minimized. The code below is more than 100 times faster than the code above!
Issue_Age = Range(„Issue_Age“)
For Duration = 1 to 100
Attained_Age = Issue_Age + Duration
Next Duration“
Pingback: Den Zufall in Excel kreativ nutzen | Der Tabellen-Experte
Bezugnehmend auf meine obigen Erfahrungswerte, ist der Unterschied zwischen einem i5 und einem i7 System schon recht deutlich (auf i7 12GB RAM win7 mit 64bit zu i5 win7 mit 32 bit)
Aber „ausgleichen“ würde ich das auch nicht nennen. Es war „etwas viel“ kürzer in der Berechnungszeit, aber dennoch „nervig“ wegen den Unterbrechungen, wenn man die Berechnung auf „automatisch“ lässt.
Auch die Office Version spielt da keine Rolle in dem jeweiligen System.
Gibt ja auch „Profis“ die mit „Word Art“ im Excel arbeiten und sich dann wundern, wenn man dann ein „Schnickers“ braucht…
Die Datei hatte im Übrigen ca 30 000 Zeilen mit 40 Spalten… und diesen Formeln…
Habe hier noch eine andere Seite zum Thema entdeckt. Sie macht vor allem darauf aufmerksam, dass sich Funktionen in Abhängigkeit der Verwendung volatil verhalten können: http://www.decisionmodels.com/calcsecretsi.htm
Dazu eine MS-HIlfe:
https://msdn.microsoft.com/de-de/library/office/ff726673%28v=office.14%29.aspx
HTH.
Hallo Hans,
vielen Dank für die ergänzenden Hinweise. Insbesondere die Microsoft-Hilfeseite hat ein paar generell gute Tipps, was Performance-Verbesserung angeht.
Schöne Grüße,
Martin
Pingback: Wieso ist das #Excel so #langsam? | Soprani Software
Hallo Martin,
ich lese gerne Deine Excel-Beiträge, auch die Grundlagen-Artikel, auf die verwiesen wird.
Im Beitrag 14/2019 wird auf den Artikel „Excel im Schneckentempo – volatile Funktionen“ verwiesen. Dort steht im Absatz „Welche Ereignisse lösen eine Neuberechnung aus?“ gleich im ersten Satz ein Fehler, der nicht gleich auffällt: „Wie ich oben schon erläutert habe, für die Eingabe oder Veränderung eines Wertes in einer beliebigen Zelle bereits zu einer Neuberechnung“.
ich meine das Wörtchen „für“, das eigentlich „führt“ heißen soll.
Ich bitte um Korrektur… vielen Dank!
Jörg Böhmichen
Hallo Jörg,
vielen Dank für den Hinweis, ist jetzt korrigiert.
Schöne Grüße,
Martin
Danke für die Hilfe, auf die bedingte Formatierung wäre ich nicht gekommen, dachte es liegt an der vielzahl von S-Verweisen.Konnte von ~1 Minute pro neuer Zeile auf 5 Sekunden runtergefahren werden.
Hallo Torsten,
das ist schön zu hören, das nenne ich mal einen Zeitgewinn!
Schöne Grüße,
Martin
Hihi – ich habe noch einen Spezialfall für ein langsam reagierendes Excel bei mir entdeckt: Seit heute lief Excel im Schneckentempo, schon beim Scrollen, selbst wenn ich ein leeres Blatt geöffnet hatte. Ich habe alle möglichen Tipps durchgeschaut, natürlich auch diesen hier von Martin, bis ich nach einer halben Stunde drauf gekommen bin, dass Civilization V noch im Hintergrund lief. Nach Schließen desselben war wieder alles ok.
So einfach ist es manchmal.
Hi Michael,
wahrscheinlich hatte Excel einfach keine Lust mehr auf eine weitere Runde Civilization 😉