Umgang mit zirkulären Referenzen in Excel

In Excel geben Sie normalerweise Formeln ein, um Ergebnisse zu erhalten, und es ist ziemlich üblich, dabei auf andere Zellen zu verweisen. Was passiert aber, wenn Sie die Zelle selbst referenzieren, entweder direkt oder indirekt? Das bedeutet eine Endlosschleife von Berechnungen! Zum Glück hat Excel Lösungen, um mit solchen Situationen umzugehen.

Zirkulärer Bezug bedeutet, dass Ihre Formel versucht, die Ursprungszelle zu berechnen. Normalerweise wird dies als Fehler angesehen. Es gibt jedoch Fälle, in denen dieser Fehler tatsächlich nützlich sein kann und Sie vielleicht sogar absichtlich einen Kreisbezug erzeugen wollen. Schauen wir uns dieses Phänomen anhand einiger Beispiele genauer an. Die Beispiel-Arbeitsmappe können Sie hier herunterladen.

Direktreferenz

Wenn Sie z. B. in die Zelle C3 gehen und dann „=C3“ eingeben, würden Sie Excel ein wenig verwirren, weil diese Zelle nun auf sich selbst zeigt und somit eine Kreisreferenz erzeugt. Wenn Sie dasselbe innerhalb einer Formel tun, hat das denselben Effekt (z. B. =IF(C3,…,…) oder =C3+10).

Ein weiteres häufiges Beispiel ist das Einschließen der ursprünglichen Zelle, wenn Sie einen Bereich aus benachbarten Zellen auswählen. Wenn Sie z. B. die Zelle C4 in die Summenformel =SUMME(C2:C4) einbeziehen, wird dies zu einer zirkulären Referenz.

Indirekte Referenz

Eine Zelle, die auf eine Zelle zielt, die auf die Ursprungszelle zeigt, erzeugt eine indirekte Referenz. Mit anderen Worten, eine zirkuläre Referenz kann durch zwei Zellen, die sich gegenseitig anvisieren, hergestellt werden. Zum Beispiel, wenn Sie „=D3“ in Zelle B3 eingeben, während Zelle D3 eine Referenz von Zelle B3 enthält (in diesem Fall „=B3“).

Warum ist das ein Problem?

Excel warnt den Benutzer, wenn eine zirkuläre Referenz auftritt, denn eine zirkuläre Referenz bedeutet im Wesentlichen Endlosschleifen. Excel zu sagen, dass es die gleiche Berechnung unendlich oft wiederholen soll, macht nicht viel Sinn. Um zu verhindern, dass Ihr Computer einfriert, ignoriert Excel zirkuläre Referenzen einfach.

Schauen wir uns diese Warnmeldung an. Nach der Eingabe einer Formel mit zirkulärem Bezug erhalten Sie folgende Meldung,

Beachten Sie, dass Excel bei weiteren zirkulären Formelbezügen nicht den gleichen Fehler ausgibt. In der Statusleiste von Excel wird die letzte Zelle mit einem Zirkelbezug angezeigt. Das bedeutet, dass es möglicherweise mehr als einen solchen Verweis auf dieser Registerkarte gibt. So sieht es aus,

Excel führt auch eine Liste aller zirkulären Referenzen, die über die Option „Fehlerprüfung“ auf der Registerkarte „Formel“ aufgerufen werden kann,

So werden Sie sie los

Es gibt leider keine einfache Möglichkeit, unerwünschte Zirkelbezüge zu entfernen oder zu reparieren. Sie müssen eine der zuvor genannten Möglichkeiten nutzen und sie eine nach der anderen auflösen. Um herauszufinden, was das Problem verursacht, können Sie die Option Formelprüfung auf der Registerkarte Formel verwenden. In unserer Anleitung erfahren Sie, wie das geht.https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/

Kann das nützlich sein?

Ja! Allerdings nur unter bestimmten Umständen. Es kann sein, dass Sie einen kreisförmigen Formelblock absichtlich mehr als nur ein paar Mal berechnen wollen. Excel erlaubt diesen „iterativen“ Berechnungsansatz mit Hilfe von Grenzwerten.

Um Zirkelbezüge zu aktivieren und zu verwenden, müssen Sie sie zunächst aktivieren, indem Sie im Menü Datei die Option Iterative Berechnungen aktivieren aktivieren.

In Excel 2016, Excel 2013 und Excel 2010 finden Sie unter Datei > Optionen > Formeln > Berechnungsoptionen.

In Excel 2007, müssen Sie zur Office-Schaltfläche gehen > Excel-Optionen > Formeln > Iterationsbereich

In Excel 2003 und früher, müssen Sie aufMenü > Extras > Optionen > Berechnung

Wenn Sie iterative Berechnungen aktivieren, erhalten Sie zwei zusätzliche Eingaben im gleichen Menü,

• Maximale Iterationen bestimmt, wie oft Excel die Arbeitsmappe neu berechnen soll
• Maximale Änderung bestimmt die maximale Differenz zwischen den Werten der iterativen Formeln. Beachten Sie, dass eine kleinere Zahl genauere Ergebnisse bedeutet.

Iterative Berechnungen stoppen, wenn eine der oben definierten Bedingungen erfüllt ist.

Benutzungsfälle

Berechnen des zukünftigen Wertes einer Investition

Angenommen, wir haben 10.000 $ und wollen dieses Geld in ein CD-Konto investieren. Die monatlichen Zinsen betragen hier 1,25 %. Laden Sie die Beispiel-Arbeitsmappe für diesen Anwendungsfall hier herunter. Um den Gesamtwert am Ende des 21. Monats zu berechnen, werden wir jeden Monat berechnen und die Zinsen zum Vormonat addieren.

Zunächst geben Sie das Startgeld, die Zinsen und die grundlegende Gesamtwertfunktion ein,

=Wert * ( 1 + Zinssatz)

Dann markieren Sie die Zelle mit dem Startbarwert und fügen die Referenz der Gesamtwertfunktion hinzu.

Dieser Schritt führt zu einer Warnung vor einer zirkulären Referenz, wenn die iterative Berechnung nicht aktiviert ist. Wenn Sie dies noch nicht getan haben, aktivieren Sie diese Option und setzen Sie Maximale Iterationen auf 20, um die Zinsen am 21. Monat zu finden.

Drücken Sie OK.

Automatischer Zeitstempel

Zirkelverweise können auch dazu verwendet werden, Zeitstempel in Zellen einzufügen. Laden Sie die Beispiel-Arbeitsmappe für diesen Anwendungsfall hier herunter. Nehmen wir an, wir wollen den Bestellungen in der Tabelle unten Zeitstempel hinzufügen,

Wir können zirkuläre Referenzen verwenden, um einen Zeitstempel hinzuzufügen, wenn eine neue Bestellinformation eingegeben wird.

Beginnen Sie mit dem Hinzufügen einer neuen Spalte in die Tabelle, um die Zeitstempel darin zu platzieren. Geben Sie die Formel ein, =IF(A2<>““,IF(I2<>““,I2,NOW()),““)

Diese Formel prüft, ob die Bestellnummer (Zelle A2) eingegeben wurde. Wenn ein Wert eingegeben wurde und die Zelle mit dem Zeitstempel leer ist, gibt die Formel die Funktion JETZT() zurück.

Beachten Sie, dass das Drücken der Eingabetaste eine Warnung vor Zirkelbezügen auslöst, wenn iterative Berechnungen nicht aktiviert sind. Die Zahlen für Maximale Iterationen oder Maximale Änderung haben diesmal keine große Bedeutung, da wir nur eine einzige Iteration benötigen, also können Sie diese beiden Eingaben auf ihren Standardwerten belassen.

Jetzt wird jedes Mal, wenn wir einen neuen Auftrag eingeben und eine neue Zeile erzeugen, automatisch ein Zeitstempel in der Spalte Zeitstempel ausgegeben.