Beim Bau von großen Excel Tabellen habe ich selbst schon die Erfahrung gemacht, dass es in Excel zu Geschwindigkeitseinbrüchen kommen kann. Dabei bin ich auf das Problem der volatilen Funktionen gestoßen. Ein Thema das leider viel zu wenig Aufmerksamkeit bekommt.
Bei kleineren Tabellen fällt es gar nicht auf, arbeitet man allerdings an größeren Projekten, kann es schnell mal passieren, dass Excel auf einmal langsam wird. Spürt man bei jeder Eingabe eine kurze Verzögerung, liegt das mit großer Wahrscheinlichkeit an dem übermäßigen Gebrauch volatiler Funktionen.
Volatile Excel Funktionen, was ist das denn und braucht man das?
Wird in einer Arbeitsmappe durch den Benutzer irgendwo eine Zelle aktualisiert, ist Excel so schlau und berechnet nur Zellen neu, die in irgend einer Art auf die veränderte Zelle verweisen. Dadurch bleiben Tabellen auch bei großen Datenmengen performant.
Volatile Funktionen bilden dabei die Ausnahme. Wird auf irgendeinem Tabellenblatt in der gesamten Arbeitsmappe eine Zelle durch eine Eingabe aktualisiert, wird gleichzeitig jede volatile Funktion zuzüglich aller Zellen, die sich auf diese Funktion/Zelle beziehen, neu berechnet. Das führt bei komplexen Tabellen schnell dazu, das Excel ins Schwitzen kommt.
Volatile Funktionen sollten also nur mit Bedacht eingesetzt werden, bzw. sollten Sie versuchen, Alternativen zu finden, was in den meisten Fällen auch möglich ist.
Die folgenden Funktionen sollten also nur mit bedacht eingesetzt werden.
- INFO
- HEUTE
- JETZT
- ZELLE
- ZUFALLSBEREICH
- ZUFALLSZAHL
- BEREICH.VERSCHIEBEN
- INDIREKT
- ZUFALLSMATRIX (E 365)
Wann werden volatile Funktionen neu berechnet?
Volatile Funktionen werden immer dann neu berechnet, wenn sich in irgend einer Zelle, innerhalb aller offenen Excelanwendungen etwas ändert. Der eigentliche Zellbezug spielt dabei keine Rolle. Auch nicht die geöffnete Arbeitsmappe.
Das es keine Rolle spielt in welcher Arbeitsmappe man sich befindet, kann man sehr gut mit der “=JETZT()” Funktion testen. Arbeitet man gleichzeitig in zwei oder mehr Arbeitsmappen, in denen sich in mindestens einer Mappe volatile Funktionen befinden, zwingt das die Performance unter Umständen schnell in die Knie.
Es gibt auch einige weitere Funktionen, die eine Neuberechnung auslösen. Zum Beispiel der “Format übertragen” Pinsel, das Löschen von Tabellenblättern, Zellen oder Zellinhalten, neue/ kopierte Zellen einfügen, Sortier- und Filterfunktionen.
Die Seite www.online-excel.de hat zu diesem Thema einige sehr interessante Experimente durchgeführt.
Kann man volatile Funktionen aushebeln?
Ja, indem man unter Berechnungsoptionen die automatische Neuberechnung ausschaltet.
Nein, wenn man versucht das Problem formelseitig zu lösen. Selbst wenn man die volatile Funktion in einer anderen Funktion verschachtelt: Bsp. – “=WENN(1<3; “Wahr” ;JETZT())” und dabei die “JETZT” Funktion eigentlich niemals aufgerufen wird, erhält das gesamte Konstrukt wohl trotzdem den Status volatil.
Dieses Beispiel habe ich in einem englischen Artikel zu dem Thema gefunden.
Sonderform bedingte Formatierung
Eine interessante Sonderform zu volatil berechneten Funktionen stellt die bedingte Formatierung dar. Denn jede Zelle, die eine Formatierungsregel zugewiesen bekommt, erfährt mit jeder ausgelösten Bildschirmaktualisierung eine Neuberechnung. Das bedeutet, dass die Formatierungsregeln selbst beim Scrollen in einem Tabellenblatt immer wieder neu berechnet werden. Auch durch das Setzen des automatischen “ScreenUpdating = false” über eine VBA Funktion, lässt sich die Neuberechnung der bedingten Formatierung nicht abschalten.
Weiteres zu dem Thema
https://www.online-excel.de/excel/singsel.php?f=24
https://www.tabellenexperte.de/excel-im-schneckentempo-volatile-funktionen/
https://www.tabellenexperte.de/excel-im-schneckentempo-volatile-funktionen/
Ausgehende Verlinkungen auf Amazon (*) sind sogenannte Provision-Links. Wenn du auf so einen Verweislink klickst und über diesen Link einkaufst, bekommen wir von deinem Einkauf eine Provision. Für dich verändert sich der Preis nicht. [Quelle: affima.de]