Beim Arbeiten mit Excel-Formeln wird möglicherweise manchmal die folgende Warnmeldung angezeigt.
Diese Eingabeaufforderung weist Sie darauf hin, dass in Ihrem Arbeitsblatt ein Zirkelbezug vorhanden ist und dies zu einer fehlerhaften Berechnung der Formeln führen kann. Es fordert Sie auch auf, dieses Thema des Rundschreibens anzusprechen und zu sortieren.
In diesem Tutorial werde ich alles behandeln, was Sie über Zirkelverweise wissen müssen, und auch So finden und entfernen Sie Zirkelverweise in Excel.
Also lasst uns anfangen!
Was ist ein Zirkelbezug in Excel?
In einfachen Worten, ein Zirkelbezug tritt auf, wenn Sie am Ende eine Formel in einer Zelle haben - die selbst die Zelle (in der sie eingegeben wurde) für die Berechnung verwendet.
Lassen Sie mich versuchen, dies an einem einfachen Beispiel zu erklären.
Angenommen, Sie haben den Datensatz in Zelle A1: A5 und verwenden die folgende Formel in Zelle A6:
=SUMME(A1:A6)
Dadurch erhalten Sie eine zirkuläre Referenzwarnung.
Dies liegt daran, dass Sie die Werte in Zelle A1:A6 summieren möchten und das Ergebnis in Zelle A6 sein sollte.
Dadurch wird eine Schleife erstellt, da Excel einfach den neuen Wert in Zelle A6 hinzufügt, der sich ständig ändert (daher eine kreisförmige Referenzschleife).
Wie finde ich Rundschreiben in Excel?
Während die Warnmeldung für zirkuläre Referenzen freundlich genug ist, um Ihnen mitzuteilen, dass sie in Ihrem Arbeitsblatt vorhanden ist, erfahren Sie nicht, wo sie stattfindet und welche Zellreferenzen sie verursachen.
Wenn Sie also versuchen, Zirkelverweise im Arbeitsblatt zu finden und zu behandeln, müssen Sie einen Weg kennen, diese irgendwie zu finden.
Nachfolgend finden Sie die Schritte, um einen Zirkelverweis in Excel zu finden:
- Aktivieren Sie das Arbeitsblatt mit dem Zirkelbezug
- Klicken Sie auf die Registerkarte Formeln
- Klicken Sie in der Gruppe Formelbearbeitung auf das Dropdown-Symbol Fehlerprüfung (kleiner nach unten zeigender Pfeil rechts).
- Bewegen Sie den Cursor über die Option Kreisreferenzen. Es zeigt Ihnen die Zelle mit einem Zirkelbezug im Arbeitsblatt an
- Klicken Sie auf die Zellenadresse (die angezeigt wird) und Sie gelangen zu dieser Zelle im Arbeitsblatt.
Sobald Sie das Problem behoben haben, können Sie die oben beschriebenen Schritte erneut ausführen und es werden mehr Zellbezüge mit dem Zirkelbezug angezeigt. Wenn keine vorhanden ist, wird kein Zellbezug angezeigt.
Eine weitere schnelle und einfache Möglichkeit, den Zirkelbezug zu finden, ist die Statusleiste. Auf der linken Seite wird Ihnen der Text Circular Reference zusammen mit der Zelladresse angezeigt.
Es gibt einige Dinge, die Sie wissen müssen, wenn Sie mit Zirkelbezügen arbeiten:
- Falls die iterative Berechnung aktiviert ist (wird später in diesem Tutorial behandelt), zeigt die Statusleiste die kreisförmige Referenzzellenadresse nicht an
- Falls sich der Zirkelbezug nicht im aktiven Blatt befindet (aber in anderen Blättern in derselben Arbeitsmappe), wird nur der Zirkelbezug und nicht die Zellenadresse angezeigt
- Falls Sie einmal eine Warnmeldung für einen zirkulären Verweis erhalten und diese schließen, wird die Meldung beim nächsten Mal nicht mehr angezeigt.
- Wenn Sie eine Arbeitsmappe öffnen, die über den Zirkelbezug verfügt, wird Ihnen die Eingabeaufforderung angezeigt, sobald die Arbeitsmappe geöffnet wird.
So entfernen Sie einen Zirkelbezug in Excel?
Sobald Sie festgestellt haben, dass in Ihrem Blatt Zirkelverweise vorhanden sind, ist es an der Zeit, diese zu entfernen (es sei denn, Sie möchten, dass sie aus einem bestimmten Grund vorhanden sind).
Leider ist es nicht so einfach, die Löschtaste zu drücken. Da diese von Formeln abhängig sind und jede Formel anders ist, müssen Sie dies im Einzelfall analysieren.
Falls es nur darum geht, dass der Zellbezug das Problem versehentlich verursacht, können Sie ihn einfach korrigieren, indem Sie den Bezug anpassen.
Aber manchmal ist es nicht so einfach.
Ein zirkulärer Bezug kann auch basierend auf mehreren Zellen verursacht werden, die sich auf vielen Ebenen ineinander füttern.
Lassen Sie mich Ihnen ein Beispiel zeigen.
Darunter befindet sich ein Zirkelbezug in Zelle C6, aber es ist nicht nur ein einfacher Fall von Selbstbezug. Es ist mehrstufig, wobei die Zellen, die es in den Berechnungen verwendet, auch aufeinander verweisen.
- Die Formeln in Zelle A6 sind =SUMME(A1:A5)+C6
- Die Formel lautet Zelle C1 ist =A6*0,1
- Die Formel in Zelle C6 lautet =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ängig sind (und damit den Zirkelbezugsfehler verursachen).
Und wieder habe ich ein wirklich einfaches Beispiel nur zu Demozwecken ausgewählt. In Wirklichkeit könnten diese ziemlich schwer zu erkennen sein und möglicherweise weit entfernt im selben Arbeitsblatt oder sogar über mehrere Arbeitsblätter verstreut sein.
In einem solchen Fall gibt es eine Möglichkeit, die Zellen zu identifizieren, die einen zirkulären Bezug verursachen, und diese dann zu behandeln.
Dies geschieht mithilfe der Option Trace Precedents.
Nachfolgend finden Sie die Schritte zum Verwenden von Ablaufverfolgungspräzedenzfällen, um Zellen zu finden, die an die Zelle mit dem Zirkelbezug füttern:
- Wählen Sie die Zelle mit dem Zirkelbezug aus
- Klicken Sie auf die Registerkarte Formeln
- Klicken Sie auf Präzedenzfälle verfolgen
Die obigen Schritte zeigen Ihnen blaue Pfeile, die Ihnen sagen, welche Zellen in die Formel in der ausgewählten Zelle einspeisen. Auf diese Weise können Sie die Formeln und Zellen überprüfen und den Zirkelbezug loswerden.
Falls Sie mit komplexen Finanzmodellen arbeiten, kann es sein, dass diese Präzedenzfälle auch mehrere Ebenen tief gehen.
Dies funktioniert gut, wenn Sie alle Formeln haben, die sich auf Zellen in demselben Arbeitsblatt beziehen. Wenn es sich in mehreren Arbeitsblättern befindet, ist diese Methode nicht effektiv.
So aktivieren/deaktivieren Sie iterative Berechnungen in Excel
Wenn Sie in einer Zelle einen Zirkelbezug haben, erhalten Sie zuerst die unten gezeigte Warnmeldung, und wenn Sie dieses Dialogfeld schließen, erhalten Sie als Ergebnis in der Zelle 0.
Dies liegt daran, dass es sich bei einem Zirkelbezug um eine Endlosschleife handelt und Excel nicht darin aufholen möchte. Es gibt also eine 0 zurück.
In einigen Fällen möchten Sie jedoch möglicherweise, dass die Zirkelreferenz aktiv ist und einige Iterationen durchgeführt werden. In einem solchen Fall können Sie anstelle einer Endlosschleife entscheiden, wie oft die Schleife ausgeführt werden soll.
Das nennt man iterative Berechnung in Excel.
Nachfolgend finden Sie die Schritte zum Aktivieren und Konfigurieren von iterativen Berechnungen in Excel:
- Klicken Sie auf die Registerkarte Datei
- Klicken Sie auf Optionen. Dies öffnet das Dialogfeld Excel-Optionen
- Wählen Sie Formel im linken Bereich
- Aktivieren Sie im Abschnitt Berechnungsoptionen das Kontrollkästchen „Iterative Berechnung aktivieren“. Hier können Sie die maximalen Iterationen und den maximalen Änderungswert angeben
Das ist es! Die obigen Schritte würden eine iterative Berechnung in Excel ermöglichen.
Lassen Sie mich auch kurz die beiden Optionen in der iterativen Berechnung erklären:
- Maximale Iterationen: Dies ist die maximale Häufigkeit, die Excel berechnen soll, bevor das Endergebnis angezeigt wird. Wenn Sie dies also als 100 angeben, führt Excel die Schleife 100 Mal aus, bevor Sie das Endergebnis erhalten.
- Maximale Änderung: Dies ist die maximale Änderung, die, wenn sie zwischen den Iterationen nicht erreicht wird, die Berechnung anhalten würde. Standardmäßig ist der Wert 0,001. Je niedriger dieser Wert, desto genauer wäre das Ergebnis.
Denken Sie daran, dass Excel umso mehr Zeit und Ressourcen benötigt, je öfter die Iterationen ausgeführt werden. Falls Sie die maximalen Iterationen hoch halten, kann dies dazu führen, dass Ihr Excel langsamer wird oder abstürzt.
Hinweis: Wenn iterative Berechnungen aktiviert sind, zeigt Excel Ihnen die Warnmeldung zum zirkulären Bezug nicht an und zeigt sie jetzt auch in der Statusleiste an.Bewusste Verwendung von Rundschreiben
In den meisten Fällen wäre das Vorhandensein von Zirkelverweisen in Ihrem Arbeitsblatt ein Fehler. Aus diesem Grund zeigt Ihnen Excel eine Eingabeaufforderung an, die besagt: "Versuchen Sie, diese Referenzen zu entfernen oder zu ändern oder die Formeln in andere Zellen zu verschieben."
Es kann jedoch bestimmte Fälle geben, in denen Sie einen Zirkelbezug benötigen, um das gewünschte Ergebnis zu erzielen.
Einen solchen speziellen Fall habe ich bereits darüber geschrieben, den Zeitstempel in einer Zelle in einer Zelle in Excel zu erhalten.
Angenommen, Sie möchten eine Formel erstellen, sodass jeder Eintrag in einer Zelle in Spalte A erfolgt, der Zeitstempel in Spalte B angezeigt wird (wie unten gezeigt):
Sie können zwar einfach einen Zeitstempel einfügen, indem Sie die folgende Formel verwenden:
=WENN(A2"",WENN(B2"",B2,JETZT()),"")
Das Problem mit der obigen Formel besteht darin, dass alle Zeitstempel aktualisiert werden, sobald eine Änderung im Arbeitsblatt vorgenommen wird oder das Arbeitsblatt erneut geöffnet wird (da die JETZT-Formel flüchtig ist).
Um dieses Problem zu umgehen, können Sie eine Zirkelreferenzmethode verwenden. Verwenden Sie dieselbe Formel, aber aktivieren Sie die iterative Berechnung.
Es gibt auch einige andere Fälle, in denen die Möglichkeit zum Zirkelbezug erwünscht ist (ein Beispiel finden Sie hier).
Hinweis: Obwohl es einige Fälle geben kann, in denen Sie Zirkelverweise verwenden können, finde ich es am besten, die Verwendung zu vermeiden. Zirkelverweise können auch die Leistung Ihrer Arbeitsmappe beeinträchtigen und sie verlangsamen. In seltenen Fällen, in denen Sie es benötigen, bevorzuge ich immer die Verwendung von VBA-Codes, um die Arbeit zu erledigen.Ich hoffe, Sie fanden dieses Tutorial nützlich!
Andere Excel-Tutorials, die Sie möglicherweise nützlich finden:
- #REF! Fehler in Excel; So beheben Sie den Referenzfehler!
- Excel VBA-Fehlerbehandlung
- Verwenden Sie IFERROR mit SVERWEIS, um #N/A-Fehler zu beseitigen
- So verweisen Sie auf ein anderes Blatt oder eine Arbeitsmappe in Excel (mit Beispielen)
- Absolute, relative und gemischte Zellreferenzen in Excel
