Volatile-Funktionen in Excel: Was sie sind und wann man sie vermeiden sollte

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Was ist eine volatile Funktion in Excel?

A: Eine volatile Funktion wird jedes Mal neu berechnet, wenn Excel eine Neuberechnung der Arbeitsmappe durchführt, selbst wenn sich die eigenen Eingabewerte nicht geändert haben. Dies unterscheidet sich von regulären Funktionen, die nur dann neu berechnet werden, wenn sich eine der Zellen, von denen sie abhängen, tatsächlich ändert. Die gängigsten Beispiele sind JETZT(), HEUTE(), ZUFALLSZAHL(), ZUFALLSBEREICH(), INDIREKT(), BEREICH.VERSCHIEBEN() und unter bestimmten Bedingungen ZELLE().

Q: Warum ist es von Bedeutung, dass eine Funktion volatil ist?

A: Weil volatile Funktionen jedes Mal eine Neuberechnung auslösen, wenn Sie etwas eingeben, einen Filter anwenden, die Datei öffnen oder einen Wert an einer beliebigen Stelle in der Arbeitsmappe ändern. In einer kleinen Datei mit ein oder zwei volatilen Aufrufen ist dies kaum wahrnehmbar. In einer großen Arbeitsmappe mit Tausenden von Zeilen und Dutzenden von volatilen Formeln führt der kumulative Effekt jedoch zu einer spürbar langsamen, unresponsive Tabellenkalkulation.

Q: Gibt es eine Möglichkeit, festzustellen, wie viele volatile Formeln eine Arbeitsmappe enthält?

A: Ja, gehen Sie zu Formeln -> Formeln anzeigen (Strg+Umschalt+`) und suchen Sie mit Strg+F nach JETZT, HEUTE, ZUFALLSZAHL, INDIREKT, and BEREICH.VERSCHIEBEN. Der Bereich „Arbeitsmappenstatistiken“ (Registerkarte „Überprüfen“ in Excel 365) zeigt zudem die Gesamtzahl der Formeln an, was bei der Beurteilung der Gesamtkomplexität hilft.

Q: Sind volatile Funktionen immer schlecht?

A: Nein, ein Uhr-Widget, das die aktuelle Uhrzeit anzeigt, muss JETZT() verwenden. Eine einmalige Zufallsauswahlliste erfordert ZUFALLSZAHL(). Volatile Funktionen sind in bestimmten Anwendungsfällen das richtige Werkzeug. Das Problem entsteht, wenn sie aus Gewohnheit oder versehentlich verwendet werden, obwohl eine nicht-volatile Alternative besser geeignet wäre.

Q: Was ist die nicht-volatile Alternative zu INDIREKT und BEREICH.VERSCHIEBEN?

A: In den meisten dynamischen Nachschlageszenarien kann INDIREKT() durch INDEX() oder XVERWEIS() ersetzt werden. BEREICH.VERSCHIEBEN() ist fast immer durch INDEX() ersetzbar, noch besser ist jedoch eine strukturierte Excel-Tabelle, die sich automatisch ohne dynamische Referenzformel erweitert.

Was „volatil“ eigentlich bedeutet

Jedes Mal, wenn Sie in Excel eine Änderung vornehmen, beispielsweise eine Zelle bearbeiten, einen Filter anwenden oder eine Zeile einfügen, durchläuft Excel seinen Abhängigkeitsbaum. So wird festgestellt, welche Formeln neu berechnet werden müssen. Dieser Vorgang wird als intelligente Neuberechnung bezeichnet. Excel prüft, welche Zellen sich geändert haben, und berechnet nur die Formeln neu, die von diesen Zellen abhängen.

Volatile Funktionen sind von dieser Optimierung jedoch ausgeschlossen: Excel markiert sie als immer verschmutzt, was bedeutet, dass sie unabhängig von Änderungen ihrer Eingaben in die Neuberechnungswarteschlange gestellt werden. Sobald irgendwo in der Arbeitsmappe eine Neuberechnung ausgelöst wird, werden auch alle volatilen Formeln neu berechnet.

Stellen Sie sich das wie einen Rauchmelder vor, der immer dann ausgelöst wird, wenn im Gebäude Kaffee gekocht wird, nicht nur, wenn tatsächlich Rauch im Raum ist.

Das obige Diagramm veranschaulicht den Unterschied: SUMME() wird nur dann neu berechnet, wenn sich eine Zelle in seinem Bereich ändert. JETZT() hingegen wird bei jeder einzelnen Änderung neu berechnet, unabhängig davon, wo diese erfolgt.

Die „Volatile“-Funktionen, denen Sie am häufigsten begegnen

Die offizielle Liste der „Volatile“-Funktionen in Excel ist länger als von den meisten Nutzern erwartet. Dies sind diejenigen, mit denen Sie bei der täglichen Arbeit konfrontiert sind:

Wann ist ZELLE() volatil?
Die Funktion ZELLE() ist einzigartig, da sie bedingt volatil ist. Sie verhält sich nur dann wie eine volatile Funktion, wenn das erste Argument (Infotype) auf „dateiname” oder „format” gesetzt ist. Häufig wird beispielsweise die Formel =ZELLE("dateiname", A1) verwendet, um den Namen des aktuellen Arbeitsblatts zu ermitteln. Da sich der Blattname oder der Dateipfad theoretisch ändern könnten, behandelt Excel diese spezielle Verwendung als volatil. Wenn Sie andere Argumente wie „breite” oder „typ” verwenden, bleibt die Funktion nicht-volatil.

Die Funktionen INDIREKT und BEREICH.VERSCHIEBEN sind aus einem bestimmten Grund volatil: Sie geben einen Bereich zurück, den Excel erst zur Laufzeit bestimmen kann. Da das Ziel nicht im Voraus bekannt sein kann, hat Excel keine andere Wahl, als diese Funktionen als stets neu zu berechnen zu kennzeichnen.

„JETZT”, „HEUTE”, „ZUFALLSZAHL” und „ZUFALLSBEREICH” sind von Natur aus volatil, da ihr einziger Zweck besteht darin, einen sich mit der Zeit oder bei jeder Neuberechnung ändernden Wert zurückzugeben.

Warum die Leistung nachlässt und wann dies zu einem Problem wird

Ein einzelnes JETZT() in einer kleinen Datei ist im Grunde nicht von Bedeutung. Das Problem liegt in der Kumulierung: Wenn volatile Funktionen in großen Array-Formeln eingebettet sind, über Tausende von Zeilen kopiert oder in anderen volatilen Funktionen verschachtelt werden, dann steigt der Aufwand für die Neuberechnung rapide an.

Stellen Sie sich eine Arbeitsmappe mit 80.000 Zeilen vor:

• 80.000 Zeilen mit Verkaufsdaten
• 12 Übersichtsblättern, die jeweils mehrere INDIREKT()-Verweise auf dynamische benannte Bereiche enthalten
• Einem Dashboard mit JETZT(), das den Zeitpunkt der letzten Aktualisierung anzeigt

Jedes Mal, wenn Sie ein einzelnes Zeichen in eine beliebige Zelle eingeben, muss Excel alle INDIREKT()-Verweise in allen zwölf Blättern sowie JETZT() neu berechnen, bevor es Ihnen das Ergebnis der von Ihnen bearbeiteten Zelle anzeigen kann. Auf älterer Hardware oder bei besonders großen Datensätzen kann dies zu einer Verzögerung von mehreren Sekunden pro Tastenanschlag führen, wodurch die Datei praktisch unbrauchbar wird.

Der Kaskadeneffekt (Pseudo-Volatilität)

Volatile Funktionen lösen die Neuberechnung jeder von ihnen abhängigen Formel aus. Wenn Sie einen volatilen Bereich in eine nicht-volatile Funktion einfügen, wird diese praktisch volatil.

Dies ist eine häufige Falle bei hocheffizienten Funktionen wie IFS oder SUMIFS. Sie sind von ihrer Konzeption her streng nicht-volatil, verlieren jedoch diese Eigenschaft, wenn sie auf eine volatile Funktion verweisen.

Betrachten Sie beispielsweise diese beiden SUMMEWENNS-Formeln:

• =SUMMEWENNS(B:B, A:A, "Apple") → Schnell, nicht volatil: Sie verhält sich normal und wird nur neu berechnet, wenn Sie Daten in Spalte A oder B ändern.
• =SUMMEWENNS(INDIREKT("B:B"), A:A, "Apple") → Langsam, volatil. Da INDIREKT volatil ist, wird es bei jedem Tastendruck ausgelöst. Da SUMMEWENNS auf die Ausgabe von INDIREKT angewiesen ist, muss auch SUMMEWENNS bei jedem einzelnen Tastendruck neu berechnet werden.

Diese Kaskade von Abhängigkeiten ist der Grund dafür, dass selbst eine einzige volatile Funktion am Anfang einer Abhängigkeitskette einen großen Teil des gesamten Formelstrukturbaums der Arbeitsmappe verlangsamen kann.

Wann sind volatile Funktionen die richtige Wahl?

Bevor Sie sich nach Alternativen umsehen, ist es wichtig zu erkennen, in welchen Fällen ein volatiles Verhalten genau das ist, was Sie wollen.

Live-Uhren und Zeitstempel auf Dashboards:
Wenn Sie ein Status-Dashboard erstellen, das „Zuletzt aktualisiert: 14:32” anzeigt und die Uhr in Echtzeit ticken soll, ist JETZT() das richtige Werkzeug. Die Volatilität ist hier das gewünschte Merkmal.

Erzeugen einer einmaligen Zufallsstichprobe:
Die Verwendung von ZUFALLSZAHL() oder ZUFALLSBEREICH() zum Mischen einer Liste, zum Zuweisen zufälliger IDs oder zur Generierung von Testdaten ist ein völlig legitimer Anwendungsfall, vorausgesetzt, Sie frieren die Ergebnisse unmittelbar danach mit Einfügen → Werte ein, damit sich die Zahlen nicht mehr ändern.

Einfache Dateien mit geringem Datenverkehr: Wenn Sie einen persönlichen Budget-Tracker mit 200 Zeilen erstellen und HEUTE() in einer Formel verwenden, die überfällige Tage berechnet, sind die Performance-Kosten in der Praxis gleich Null. Wenden Sie das Prinzip der Verhältnismäßigkeit an: Optimieren Sie dort, wo es tatsächlich wehtut.

Wann Sie sie vermeiden sollten und was Sie stattdessen verwenden sollten

In den folgenden Situationen sollten volatile Funktionen durch nicht-volatile Äquivalente ersetzt werden.

Ersetzen Sie INDIREKT durch INDEX oder XVERWEIS.

INDIREKT wird am häufigsten verwendet, um einen Verweis aus einer Textzeichenfolge zu erstellen, beispielsweise um Daten aus einem Arbeitsblatt abzurufen, dessen Name in einer Zelle gespeichert ist.

=INDIREKT(A1 & "!B2")      ← volatil, da der Verweis bei jeder Neuberechnung neu erstellt wird
=XVERWEIS(A1, Sheets, Data) ← nicht volatil, da die Auswertung nur bei tatsächlichen Änderungen erfolgt

In den meisten Fällen, in denen INDIREKT zum Nachschlagen von Daten aus einer anderen Tabelle verwendet wird, erzielen XVERWEIS, INDEX/VERGLEICH oder eine strukturierte Excel-Tabellenreferenz das gleiche Ergebnis, ohne dass der Nachteil der Volatilität entsteht.

Ersetzen Sie BEREICH.VERSCHIEBEN durch INDEX oder Excel-Tabellen.

BEREICH.VERSCHIEBEN wird häufig verwendet, um einen dynamischen benannten Bereich zu definieren, der sich vergrößert, wenn neue Zeilen hinzugefügt werden.

=BEREICH.VERSCHIEBEN(Blatt1!$A$1, 0, 0, ANZAHL2(Blatt1!$A:$A), 1)  ← volatil

Die nicht-volatile Alternative: Konvertieren Sie Ihre Daten in eine Excel-Tabelle (drücken Sie Strg+T). Tabellen erweitern sich automatisch, wenn neue Zeilen hinzugefügt werden, und ihre strukturierten Verweise (Tabelle1[Spalte]) sind vollständig nicht-volatil. Es gibt fast kein Szenario, in dem BEREICH.VERSCHIEBEN das beste Werkzeug zum Erweitern von Bereichen in einer modernen Excel-Arbeitsmappe ist.

Fixieren Sie ZUFALLSZAHL und RANDBETWEEN nach der Generierung.

Wenn Sie eine Zufallszahl benötigen, die sich nicht ständig ändern soll, generieren Sie diese mit ZUFALLSZAHL() und fixieren Sie sie anschließend sofort.

1. Markieren Sie dazu die Zellen, die ZUFALLSZAHL() oder ZUFALLSBEREICH() enthalten.
2. Drücken Sie Strg+C, um den Inhalt zu kopieren.
3. Drücken Sie Strg+Alt+V, wählen Sie Werte aus und klicken Sie auf OK.

Die Formel wird durch ihr statisches numerisches Ergebnis ersetzt. Die Zufälligkeit bleibt erhalten, die Schwankungen sind beseitigt.

Fixieren Sie JETZT und HEUTE, wenn Sie einen festen Zeitstempel benötigen.

Wenn Sie einen Zeitstempel für ein Audit-Protokoll, ein Fertigstellungsdatum oder ein Vertragsdatum erfassen möchten, ist die Funktion HEUTE() ungeeignet, da sich das Datum morgen ändern wird. Fügen Sie das Datum daher fest ein (`Strg+.) oder verwenden Sie ein Makro, das bei einem bestimmten Ereignis ausgelöst wird, um den Zeitstempel einmalig zu schreiben und nie wieder zu ändern.

Praktische Techniken zur Überprüfung einer bestehenden Arbeitsmappe

Wenn Sie eine langsame Arbeitsmappe übernehmen und vermuten, dass flüchtige Funktionen die Ursache sind, finden Sie hier eine schnelle Vorgehensweise zur Überprüfung:

Schritt 1: Manuelle Berechnung aktivieren. Gehen Sie zu Formeln > Berechnungsoptionen > Manuell. Dadurch werden alle automatischen Neuberechnungen gestoppt. Die Arbeitsmappe reagiert sofort und Sie können sie ohne Verzögerungen durchsehen. Drücken Sie die Taste F9, wann immer Sie eine Neuberechnung erzwingen möchten.

Schritt 2: Suchen Sie nach volatilen Funktionen. Drücken Sie Strg+Umschalt+`, um alle Formeln anzuzeigen. Suchen Sie dann mit Strg + F nach INDIREKT, BEREICH.VERSCHIEBEN, JETZT, HEUTE, ZUFALLSZAHL und ZUFALLSBEREICH. Notieren Sie sich, wie oft diese Funktionen vorkommen und wo sie konzentriert sind.

Schritt 3: Bewerten Sie den Einflussbereich. Prüfen Sie, ob die volatilen Funktionen in andere Berechnungen einfließen. Ein einziges BEREICH.VERSCHIEBEN, das einen benannten Bereich definiert, der von 40 SUMMEWENNS-Formeln verwendet wird, löst bei jedem Tastendruck die Neuberechnung aller 40 SUMMEWENNS-Formeln aus.

Schritt 4: Ersetzen und erneut testen: Ersetzen Sie die volatilen Formeln Abschnitt für Abschnitt durch nicht-volatile Entsprechungen. Schalten Sie nach jedem Austausch wieder auf automatische Berechnung um und prüfen Sie, ob sich die Reaktionsgeschwindigkeit verbessert hat.

Schritt 5: Dokumentieren Sie die verbleibenden volatilen Elemente. Einige volatile Funktionen bleiben bestehen, beispielsweise die JETZT()-Funktion auf dem Dashboard oder die HEUTE()-Funktion in der Berechnung der überfälligen Posten. Hinterlassen Sie im Namensmanager oder in einem Dokumentationsblatt einen Kommentar, in dem Sie erklären, warum jedes verbleibende volatile Element beabsichtigt ist.

Zusammenfassung

Volatile-Funktionen sind weder fehlerhaft noch grundsätzlich schlecht. Es handelt sich um spezielle Werkzeuge, die mit bestimmten Kosten verbunden sind. Wenn Sie diese Kosten verstehen, die verschiedenen Alternativen kennen und sich angewöhnen, Werte zu fixieren, wenn Zufallswerte oder Zeitstempel nur einmal benötigt werden, bleiben Ihre Arbeitsmappen schnell, stabil und für Kollegen leicht zu pflegen.