Iterative Berechnung, Freund oder Feind?
Unter den unzähligen Einstellungen in Excel gibt es eine scheinbar harmlose Option, die die Art und Weise, wie Excel Formelergebnisse berechnet, grundlegend verändert: die Iterative Berechnung. Diese wenig bekannte Funktion hat ihre Verwendungsmöglichkeiten, ist jedoch nicht ohne Risiko. Lassen Sie uns untersuchen, was sie tut, wie sie verwendet werden kann und was beachtet werden sollte, bevor man sie aktiviert.
Anmerkung: PerfectXL Academy wurde mit KI übersetzt. Haben Sie Verbesserungsvorschläge? Dann senden Sie uns eine E-Mail an info@Perfectxl.com.
Wie Excel berechnet
Wie jeder weiß, der Excel verwendet, werden die Ergebnisse von Formeln automatisch neu berechnet, wann immer sich etwas in einer der Zellen ändert, auf die sie verweisen. Wenn die referenzierten Zellen ebenfalls Formeln enthalten, berechnet Excel diese zuerst. Auf diese Weise kann selbst bei einem bescheidenen Arbeitsblattmodell bereits eine einzige Zelländerung dazu führen, dass eine ganze Kette von Formeln neu berechnet wird. Sehr praktisch!
Was passiert jedoch, wenn in dieser Kette von Formeln eine Formel gefunden wird, die bereits zuvor aufgetreten ist? Nehmen wir den einfachen Fall, bei dem eine Formel in Zelle A1 auf Zelle B1 verweist und Zelle B1 eine Formel enthält, die wieder auf Zelle A1 verweist. Was sollte Excel tun? Es ist wie der alte Witz im Wörterbuch:
„Rekursion Substantiv
Ein sich selbst wiederholender Prozess. Siehe Rekursion“
Was Excel tun sollte, hängt sehr davon ab, was beabsichtigt war. Oft schleichen sich zirkuläre Referenzen versehentlich ein. In solchen Fällen müssen die Formeln einfach neu überarbeitet werden. Aber was, wenn man eine zirkuläre Abhängigkeit absichtlich haben möchte?
Ist eine zirkuläre Referenz immer ein Fehler?
Eine zirkuläre Abhängigkeit mag wie eine kontraintuitive Anforderung erscheinen. Doch der Bedarf, zu einem vorherigen Ergebnis zurückzukehren, tritt viel zu leicht auf, ein häufig genanntes Beispiel ist die Berechnung von Zinseszinsen.
Probieren Sie es aus und Excel wird sich beschweren. Die gefürchtete „Wir haben eine oder mehrere zirkuläre Referenzen gefunden“-Meldung ist unter Excel-Nutzern berüchtigt, besonders wenn sie unbeabsichtigt ist. Doch Excel’s Protest ist gerechtfertigt: Wenn es einfach weiter versucht, die Antwort zu berechnen, würde es für immer berechnen. Idealerweise möchte man, wenn die zirkuläre Abhängigkeit beabsichtigt ist, eine Möglichkeit, ihm zu sagen, es solle nach einer bestimmten Anzahl von Iterationen aufhören. Lernen Sie die Iterative Berechnung kennen.
Mit der Option „Iterative Berechnung“ können Sie festlegen, wie oft (Iterationen) Excel eine Formel kettet, um ein Ergebnis zu berechnen, normalerweise, um auf ein Ergebnis mit akzeptabler Präzision zu konvergieren.
Iterative Berechnung: Ein einfaches Anwendungsbeispiel
Hier ein interessantes Beispiel: Wir erstellen eine Tabelle mit zwei Spalten, in der die zweite Spalte automatisch einen Zeitstempel einfügt, wenn etwas hinzugefügt wird, eine Art „Erstellungsdatum“ für Tabellenzeilen.
Beachten Sie, dass eine reguläre Excel-Formel dies nicht leisten kann. Wenn Sie etwas wie IF(ISBLANK(A1), “”, NOW()) versuchen, wird der Zeitstempel jedes Mal aktualisiert, auch wenn er bereits generiert wurde. Alle Zeitstempel würden immer die neueste Bearbeitung widerspiegeln.
Wenn die Formelzelle bereits einen Zeitstempel enthält, belassen Sie ihn.
Wenn noch kein Zeitstempel vorhanden ist, überprüfen Sie, ob einer erstellt werden muss.
Ohne iterative Berechnung wäre Schritt 1 nicht möglich. Nachdem Sie also unter Datei > Optionen > Formeln die optionale „Iterative Berechnung aktivieren“ aktiviert haben, geben Sie die folgende Formel in Spalte B ein:
=IF(AND(B1<>"",B1<>0),B1,IF(ISBLANK(A1),"",NOW()))
Stellen Sie sicher, dass Sie das Zahlenformat auf „Uhrzeit“ setzen.
Das war’s! Versuchen Sie, einige Daten in die Spalten A-C hinzuzufügen und beobachten Sie, wie die Zeitstempel hinzugefügt werden. Einmal erstellt, sollten die Zeitstempel nicht mehr verändert werden.
Die Fallstricke der Verwendung iterativer Berechnung
Da unbeabsichtigte zirkuläre Referenzen eine häufige Ursache für Kopfschmerzen in der Excel-Welt sind, könnte man versucht sein, die iterative Berechnung zu aktivieren, um ein Arbeitsblatt am Laufen zu halten, ohne das zugrunde liegende Problem zu beheben. Es versteht sich von selbst, dass dies keine gute Praxis ist.
Selbst wenn die iterative Berechnung absichtlich aktiviert wird, ist sie nicht ohne Risiko. Nicht jeder ist mit dieser Option vertraut, und selbst diejenigen, die wissen, dass sie existiert, sind sich möglicherweise nicht bewusst, dass sie aktiviert ist. Schließlich erfordern die meisten Arbeitsblätter keine iterative Berechnung.
Was ist also das Risiko? Obwohl die iterative Berechnung es Ihnen ermöglicht, zirkuläre Abhängigkeiten absichtlich zu erstellen, maskiert sie auch unbeabsichtigte. Wenn Sie die iterative Berechnung verwenden müssen, stellen Sie sicher, dass das Verhalten gut dokumentiert ist und dass das Modell gründlich auf unbeabsichtigte Berechnungsloops überprüft wird.
Tipp: Hilfreiche Tools wie der PerfectXL Risk Finder können auch dann auf Berechnungsloops überprüfen, wenn die iterative Berechnung aktiviert ist.