Excel im Schneckentempo: Volatile Funktionen 21

Artikelbild-086
Wenn Excel sehr langsam ist, könnten volatile Funktionen die Ursache sein.
 

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.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Florian Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

21 Gedanken zu “Excel im Schneckentempo: Volatile Funktionen

  • Avatar-Foto
    --Fragezeichen--

    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.

    • Avatar-Foto
      Florian

      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?

  • Avatar-Foto
    Marius Schon

    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

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      th

      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

  • Avatar-Foto
    Oliver

    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…

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Jörg Böhmichen

    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

  • Avatar-Foto
    Torsten

    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.

  • Avatar-Foto
    Michael Krüger

    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.