Während Sie mit Excel-Formeln arbeiten, kann es vorkommen, dass Sie die folgende Warnmeldung sehen.
Diese Aufforderung weist Sie darauf hin, dass in Ihrem Arbeitsblatt ein zirkulärer Bezug vorhanden ist und dies zu einer falschen Berechnung durch die Formeln führen kann. Außerdem werden Sie aufgefordert, das Problem des Zirkelbezugs zu lösen und zu sortieren.
In diesem Tutorial werde ich alles behandeln, was Sie über den Zirkelbezug wissen müssen, und wie Sie Zirkelbezüge in Excel finden und entfernen können.
Lassen Sie uns also loslegen!
Dieses Tutorial behandelt:
Was ist ein Zirkelbezug in Excel?
Ein Zirkelbezug liegt vor, wenn Sie eine Formel in einer Zelle haben, die ihrerseits die Zelle, in der sie eingegeben wurde, für die Berechnung verwendet.
Lassen Sie mich versuchen, dies anhand eines einfachen Beispiels zu erklären.
Angenommen, Sie haben den Datensatz in Zelle A1:A5 und Sie verwenden die folgende Formel in Zelle A6:
=SUM(A1:A6)
Dadurch erhalten Sie eine Warnung vor einem Zirkelbezug.
Das liegt daran, dass Sie die Werte in Zelle A1:A6 summieren wollen und das Ergebnis in Zelle A6 stehen soll.
Das erzeugt eine Schleife, da Excel einfach immer wieder den neuen Wert in Zelle A6 hinzufügt, der sich immer wieder ändert (daher eine Zirkelbezugsschleife).
Wie findet man Zirkelbezüge in Excel?
Die Warnung vor zirkulären Bezügen sagt Ihnen zwar freundlicherweise, dass sie in Ihrem Arbeitsblatt vorhanden sind, aber nicht, wo sie auftreten und welche Zellbezüge sie verursachen.
Wenn Sie also versuchen, zirkuläre Bezüge im Arbeitsblatt zu finden und zu behandeln, müssen Sie einen Weg kennen, um diese irgendwie zu finden.
Nachfolgend finden Sie die Schritte, um einen zirkulären Bezug in Excel zu finden:
- Aktivieren Sie das Arbeitsblatt, in dem sich die zirkuläre Referenz befindet
- Klicken Sie auf die Registerkarte „Formeln“
- Klicken Sie in der Gruppe „Formelbearbeitung“ auf das Dropdown-Symbol „Fehlerprüfung“ (kleiner nach unten gerichteter Pfeil rechts)
- Halten Sie den Mauszeiger über die Option „Zirkuläre Referenzen“. Es wird Ihnen die Zelle angezeigt, die eine zirkuläre Referenz im Arbeitsblatt hat
- Klicken Sie auf die Zelladresse (die angezeigt wird) und es wird Sie zu dieser Zelle im Arbeitsblatt bringen.
Wenn Sie das Problem behoben haben, können Sie wieder die gleichen Schritte wie oben ausführen und es werden mehr Zellreferenzen angezeigt, die die zirkuläre Referenz haben. Wenn es keine gibt, sehen Sie keinen Zellbezug.
Eine weitere schnelle und einfache Möglichkeit, den zirkulären Bezug zu finden, ist ein Blick in die Statusleiste. Im linken Teil davon wird Ihnen der Text Zirkelbezug zusammen mit der Zelladresse angezeigt.
Es gibt ein paar Dinge, die Sie beim Arbeiten mit Kreisreferenzen wissen müssen:
- Wenn die iterative Berechnung aktiviert ist (wird später in diesem Tutorial behandelt), zeigt die Statusleiste die Adresse der Kreisreferenzzelle nicht an
- Wenn sich die Kreisreferenz nicht im aktiven Blatt befindet (sondern in anderen Blättern derselben Arbeitsmappe), zeigt sie nur die Kreisreferenz und nicht die Zelladresse an
- Wenn Sie einmal eine Kreisreferenz-Warnmeldung erhalten und diese verwerfen, wird die Meldung beim nächsten Mal nicht wieder angezeigt.
- Wenn Sie eine Arbeitsmappe öffnen, die den Zirkelbezug enthält, wird die Aufforderung angezeigt, sobald die Arbeitsmappe geöffnet wird.
Wie entfernt man einen Zirkelbezug in Excel?
Sobald Sie festgestellt haben, dass sich in Ihrem Blatt zirkuläre Verweise befinden, ist es an der Zeit, diese zu entfernen (es sei denn, Sie wollen, dass sie aus einem bestimmten Grund dort sind).
Leider ist es nicht so einfach, wie das Drücken der Löschtaste. Da diese von Formeln abhängig sind und jede Formel anders ist, müssen Sie dies von Fall zu Fall analysieren.
Falls es sich nur um einen versehentlichen Zellbezug handelt, der das Problem verursacht, können Sie diesen einfach korrigieren, indem Sie den Bezug anpassen.
Aber manchmal ist es nicht so einfach.
Eine zirkuläre Referenz kann auch durch mehrere Zellen verursacht werden, die sich auf vielen Ebenen gegenseitig beeinflussen.
Lassen Sie mich Ihnen ein Beispiel zeigen.
Unten gibt es eine zirkuläre Referenz in Zelle C6, aber es ist nicht nur ein einfacher Fall von Selbstreferenz. Es ist mehrstufig, wobei die Zellen, die in den Berechnungen verwendet werden, auch aufeinander verweisen.
- Die Formeln in Zelle A6 ist =SUMME(A1:A5)+C6
- Die Formel in Zelle C1 ist =A6*0.1
- Die Formel in Zelle C6 ist =A6+C1
Im obigen Beispiel hängt das Ergebnis in Zelle C6 von den Werten in Zelle A6 und C1 ab, die wiederum von Zelle C6 abhängen (und damit den Zirkelbezugsfehler verursachen)
Und wieder habe ich ein wirklich einfaches Beispiel nur zu Demozwecken gewählt. In der Realität könnten diese ziemlich schwierig herauszufinden sein und vielleicht weit entfernt im selben Arbeitsblatt oder sogar über mehrere Arbeitsblätter verstreut liegen.
In einem solchen Fall gibt es eine Möglichkeit, die Zellen zu identifizieren, die den Zirkelbezug verursachen, und diese dann zu behandeln.
Es ist die Verwendung der Option „Vorgänge verfolgen“.
Nachfolgend finden Sie die Schritte zur Verwendung von Trace Precedents, um Zellen zu finden, die zu der Zelle führen, die den Zirkelbezug hat:
- Markieren Sie die Zelle mit dem Kreisbezug
- Klicken Sie auf die Registerkarte Formeln
- Klicken Sie auf Vorgänger verfolgen
Die obigen Schritte würden Ihnen blaue Pfeile anzeigen, die Ihnen verraten, welche Zellen in die Formel in der ausgewählten Zelle einfließen. Auf diese Weise können Sie die Formeln und die Zellen inspizieren und den zirkulären Bezug loswerden.
Wenn Sie mit komplexen Finanzmodellen arbeiten, kann es sein, dass diese Präzedenzfälle auch mehrere Ebenen tief gehen.
Dies funktioniert gut, wenn sich alle Formeln auf Zellen im gleichen Arbeitsblatt beziehen. Wenn es sich um mehrere Arbeitsblätter handelt, ist diese Methode nicht effektiv.
So aktivieren/deaktivieren Sie iterative Berechnungen in Excel
Wenn Sie einen zirkulären Verweis in einer Zelle haben, erhalten Sie zunächst die unten gezeigte Warnmeldung, und wenn Sie dieses Dialogfeld schließen, erhalten Sie 0 als Ergebnis in der Zelle.
Das liegt daran, dass es sich bei einem zirkulären Verweis um eine Endlosschleife handelt, und Excel will sich nicht darin verfangen. Also gibt es eine 0 zurück.
Aber in manchen Fällen möchten Sie vielleicht, dass der Kreisverweis aktiv ist und ein paar Iterationen durchführt. In einem solchen Fall können Sie statt einer Endlosschleife entscheiden, wie oft die Schleife durchlaufen werden soll.
Dies nennt man in Excel iterative Berechnung.
Nachfolgend finden Sie die Schritte, um iterative Berechnungen in Excel zu aktivieren und zu konfigurieren:
- Klicken Sie auf die Registerkarte Datei
- Klicken Sie auf Optionen. Dadurch wird das Dialogfeld „Excel-Optionen“ geöffnet
- Wählen Sie im linken Bereich die Option „Formel“
- Im Abschnitt „Berechnungsoptionen“ aktivieren Sie das Kontrollkästchen „Iterative Berechnung aktivieren“. Hier können Sie die maximalen Iterationen und den maximalen Änderungswert angeben
Das war’s! Die obigen Schritte würden die iterative Berechnung in Excel aktivieren.
Lassen Sie mich auch kurz die beiden Optionen in der iterativen Berechnung erklären:
- Maximale Iterationen: Das ist die maximale Anzahl, wie oft Excel rechnen soll, bevor es das Endergebnis ausgibt. Wenn Sie hier also 100 angeben, führt Excel die Schleife 100 Mal durch, bevor es das Endergebnis ausgibt.
- Maximale Änderung: Dies ist die maximale Änderung, bei der die Berechnung abgebrochen wird, wenn sie zwischen den Iterationen nicht erreicht wird. Standardmäßig liegt der Wert bei .001. Je niedriger dieser Wert ist, desto genauer wäre das Ergebnis.
Erinnern Sie sich daran, dass Excel umso mehr Zeit und Ressourcen benötigt, je öfter die Iterationen durchgeführt werden. Wenn Sie die maximale Anzahl der Iterationen hoch halten, kann dies dazu führen, dass Ihr Excel langsamer wird oder abstürzt.
Absichtlich Zirkelbezüge verwenden
In den meisten Fällen ist das Vorhandensein eines Zirkelbezugs in Ihrem Arbeitsblatt ein Fehler. Deshalb zeigt Excel Ihnen eine Aufforderung an, die besagt: „Versuchen Sie, diese Referenzen zu entfernen oder zu ändern oder die Formeln in andere Zellen zu verschieben.“
Aber es gibt einige spezielle Fälle, in denen Sie eine zirkuläre Referenz benötigen, damit Sie das gewünschte Ergebnis erhalten.
Einen solchen speziellen Fall habe ich bereits beschrieben, nämlich den Zeitstempel in einer Zelle in Excel zu erhalten.
Angenommen, Sie möchten eine Formel erstellen, damit bei jedem Eintrag in eine Zelle in Spalte A der Zeitstempel in Spalte B erscheint (wie unten gezeigt):
Mit der folgenden Formel können Sie ganz einfach einen Zeitstempel einfügen:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Das Problem mit der obigen Formel ist, dass sie alle Zeitstempel aktualisieren würde, sobald eine Änderung im Arbeitsblatt vorgenommen wird oder wenn das Arbeitsblatt erneut geöffnet wird (da die NOW-Formel flüchtig ist)
Um dieses Problem zu umgehen, können Sie eine zirkuläre Referenzmethode verwenden. Verwenden Sie die gleiche Formel, aber aktivieren Sie die iterative Berechnung.
Es gibt auch einige andere Fälle, in denen die Möglichkeit, Zirkelbezüge zu verwenden, erwünscht ist (ein Beispiel finden Sie hier).
Ich hoffe, Sie fanden dieses Tutorial nützlich!
Weitere Excel-Tutorials, die Sie nützlich finden könnten: