Eine der häufigsten Fehlerwarnungen, auf die Benutzer in Excel stoßen, ist der „Rundschreiben“. Tausende von Benutzern haben das gleiche Problem, und es tritt auf, wenn eine Formel direkt oder indirekt auf ihre eigene Zelle verweist, was zu einer endlosen Schleife von Berechnungen führt.
Sie haben beispielsweise zwei Werte in den Zellen B1 und B2. Wenn die Formel =B1+B2 in B2 eingegeben wird, erzeugt sie einen Zirkelbezug; die Formel in B2 berechnet sich immer wieder neu, da sich der B2-Wert bei jeder Berechnung geändert hat.
Die meisten Zirkelverweise sind unbeabsichtigte Fehler; Excel warnt Sie vor diesen. Es sind jedoch auch Zirkelverweise vorgesehen, die dazu dienen, iterative Berechnungen durchzuführen. Unbeabsichtigte Zirkelverweise in Ihrem Arbeitsblatt können dazu führen, dass Ihre Formel falsch berechnet wird.
Daher erklären wir in diesem Artikel alles, was Sie über Zirkelverweise wissen müssen und wie Sie Zirkelverweise in Excel finden, beheben, entfernen und verwenden.
So finden und handhaben Sie Rundschreiben in Excel
Bei der Arbeit mit Excel treten manchmal Zirkelbezugsfehler auf, die auftreten, wenn Sie eine Formel eingeben, die die Zelle enthält, in der sich Ihre Formel befindet. Im Grunde passiert es, wenn Ihre Formel versucht, sich selbst zu berechnen.
Sie haben beispielsweise eine Zahlenspalte in Zelle A1:A4 und verwenden die SUM-Funktion (=SUM(A1:A5)) in Zelle A5. Die Zelle A5 verweist direkt auf ihre eigene Zelle, was falsch ist. Daher erhalten Sie die folgende zirkuläre Referenzwarnung:
Sobald Sie die obige Warnmeldung erhalten haben, können Sie auf die Schaltfläche „Hilfe“ klicken, um mehr über den Fehler zu erfahren, oder das Fehlermeldungsfenster schließen, indem Sie entweder auf die Schaltfläche „OK“ oder „X“ klicken und als Ergebnis „0“ erhalten.
Manchmal können kreisförmige Referenzschleifen dazu führen, dass Ihre Berechnung abstürzt oder die Leistung Ihres Arbeitsblatts verlangsamt. Ein Zirkelverweis kann auch zu einer Reihe anderer Probleme führen, die nicht sofort ersichtlich sind. Daher ist es am besten, diese zu vermeiden.
Direkte und indirekte Rundschreiben
Die Rundschreiben können in zwei Typen eingeteilt werden: Direkte Rundschreiben und Indirekte Rundschreiben.
Direkte Referenz
Ein direkter Zirkelbezug ist ziemlich einfach. Die Warnmeldung zum direkten Zirkelbezug wird angezeigt, wenn die Formel direkt auf ihre eigene Zelle verweist.
Im folgenden Beispiel bezieht sich die Formel in Zelle A2 direkt auf ihre eigene Zelle (A2).
Sobald die Warnmeldung erscheint, können Sie auf „OK“ klicken, aber es wird nur eine „0“ angezeigt.
Indirekter Rundschreiben-Verweis
Ein indirekter Zirkelbezug in Excel tritt auf, wenn ein Wert in einer Formel auf eine eigene Zelle verweist, jedoch nicht direkt. Mit anderen Worten, ein zirkulärer Bezug kann durch zwei aufeinander verweisende Zellen gebildet werden.
Lassen Sie es uns an diesem einfachen Beispiel erklären.
Jetzt beginnt der Wert bei A1, das den Wert 20 hat.
Als nächstes bezieht sich Zelle C3 auf Zelle A1.
Dann bezieht sich Zelle A5 auf Zelle C3.
Ersetzen Sie nun den Wert 20 in Zelle A1 durch die Formel wie unten gezeigt. Jede andere Zelle ist von Zelle A1 abhängig. Wenn Sie einen Verweis auf eine andere vorherige Formelzelle in A1 verwenden, wird eine Warnung zum zirkulären Verweis ausgegeben. Denn die Formel in A1 bezieht sich auf Zelle A5, die sich auf C3 bezieht, und Zelle C3 verweist zurück auf A1, daher der Zirkelbezug.
Wenn Sie auf „OK“ klicken, wird in Zelle A1 ein Wert von 0 angezeigt und Excel erstellt eine verknüpfte Zeile, die die Ablaufverfolgungspräzedenzfälle und Ablaufverfolgungsabhängigkeiten zeigt, wie unten gezeigt. Wir können diese Funktion verwenden, um Zirkelverweise leicht zu finden und zu beheben/zu entfernen.
So aktivieren / deaktivieren Sie Rundschreiben in Excel
Standardmäßig sind iterative Berechnungen in Excel deaktiviert (deaktiviert). Iterative Berechnungen sind sich wiederholende Berechnungen, bis eine bestimmte Bedingung erfüllt ist. Wenn es deaktiviert ist, zeigt Excel eine Zirkularreferenznachricht an und gibt als Ergebnis eine 0 zurück.
Manchmal sind jedoch Zirkelbezüge erforderlich, um eine Schleife zu berechnen. Um den Zirkelbezug zu verwenden, müssen Sie iterative Berechnungen in Ihrem Excel aktivieren, damit Sie Ihre Berechnungen durchführen können. Lassen Sie uns Ihnen nun zeigen, wie Sie iterative Berechnungen aktivieren oder deaktivieren können.
Wechseln Sie in Excel 2010, Excel 2013, Excel 2016, Excel 2019 und Microsoft 365 zur Registerkarte „Datei“ in der oberen linken Ecke von Excel und klicken Sie dann im linken Bereich auf „Optionen“.
Gehen Sie im Fenster Excel-Optionen zur Registerkarte „Formel“ und aktivieren Sie das Kontrollkästchen „Iterative Berechnung aktivieren“ im Abschnitt „Berechnungsoptionen“. Klicken Sie dann auf „OK“, um die Änderungen zu speichern.
Dadurch wird eine iterative Berechnung und damit ein zirkulärer Bezug ermöglicht.
Um dies in früheren Versionen von Excel zu erreichen, gehen Sie folgendermaßen vor:
- Klicken Sie in Excel 2007 auf die Office-Schaltfläche > Excel-Optionen > Formeln > Iterationsbereich.
- In Excel 2003 und früheren Versionen müssen Sie zu Menü > Extras > Optionen > Registerkarte Berechnung gehen.
Maximale Iterationen und maximale Änderungsparameter
Nachdem Sie iterative Berechnungen aktiviert haben, können Sie die iterativen Berechnungen steuern, indem Sie zwei Optionen angeben, die im Abschnitt Iterative Berechnungen aktivieren verfügbar sind, wie im Screenshot unten gezeigt.
- Maximale Iterationen – Diese Zahl gibt an, wie oft die Formel neu berechnet werden soll, bevor Sie das Endergebnis erhalten. Der Standardwert ist 100. Wenn Sie ihn auf „50“ ändern, wiederholt Excel die Berechnungen 50 Mal, bevor Sie das Endergebnis erhalten. Denken Sie daran, dass die Berechnung umso mehr Ressourcen und Zeit erfordert, je höher die Anzahl der Iterationen ist.
- Maximale Änderung – Er bestimmt die maximale Änderung zwischen den Berechnungsergebnissen. Dieser Wert bestimmt die Genauigkeit des Ergebnisses. Je kleiner die Zahl, desto genauer wäre das Ergebnis und desto länger dauert die Berechnung des Arbeitsblatts.
Wenn die Option für iterative Berechnungen aktiviert ist, erhalten Sie keine Warnung, wenn in Ihrem Arbeitsblatt ein Zirkelverweis vorhanden ist. Aktivieren Sie die interaktive Berechnung nur, wenn es unbedingt erforderlich ist.
Rundschreiben in Excel finden
Angenommen, Sie haben einen großen Datensatz und Sie erhalten die Warnung zum zirkulären Verweis, Sie müssen immer noch herausfinden, wo (in welcher Zelle) der Fehler aufgetreten ist, um ihn zu beheben. Gehen Sie folgendermaßen vor, um Zirkelverweise in Excel zu finden:
Verwenden des Fehlerprüfungstools
Öffnen Sie zunächst das Arbeitsblatt, in dem der Zirkelbezug stattgefunden hat. Gehen Sie zur Registerkarte „Formel“ und klicken Sie auf den Pfeil neben dem Tool „Fehlerprüfung“. Bewegen Sie dann den Cursor einfach über die Option "Circular References". Excel zeigt Ihnen die Liste aller Zellen, die an dem Zirkelbezug beteiligt sind, wie unten gezeigt.
Klicken Sie auf die gewünschte Zellenadresse in der Liste und Sie werden zu dieser Zellenadresse weitergeleitet, um das Problem zu lösen.
Verwenden der Statusleiste
Sie finden den Zirkelbezug auch in der Statusleiste. In der Statusleiste von Excel wird Ihnen die neueste Zelladresse mit einem Zirkelbezug angezeigt, z. B. "Circular References: B6" (siehe Screenshot unten).
Es gibt einige Dinge, die Sie beim Umgang mit Zirkelverweisen wissen sollten:
- Die Statusleiste zeigt die Zelladresse des Zirkelbezugs nicht an, wenn die Option Iterative Berechnung aktiviert ist. Sie müssen sie daher deaktivieren, bevor Sie die Arbeitsmappe nach Zirkelbezügen durchsuchen.
- Falls im aktiven Blatt kein Zirkelverweis gefunden wird, zeigt die Statusleiste nur „Zirkularverweise“ ohne Zelladresse an.
- Sie erhalten nur einmal eine Aufforderung zur zirkulären Referenz, und nachdem Sie auf "OK" geklickt haben, wird die Aufforderung beim nächsten Mal nicht erneut angezeigt.
- Wenn Ihre Arbeitsmappe Zirkelbezüge enthält, wird Ihnen bei jedem Öffnen die Eingabeaufforderung angezeigt, bis Sie die Zirkelbezüge auflösen oder die iterative Berechnung aktivieren.
Entfernen Sie einen Zirkelbezug in Excel
Zirkularverweise zu finden ist einfach, aber es ist nicht so einfach, es zu reparieren. Leider gibt es in Excel keine Option, mit der Sie alle Zirkelverweise auf einmal entfernen können.
Um Zirkelbezüge zu beheben, müssen Sie jeden Zirkelbezug einzeln finden und versuchen, ihn zu ändern, die Zirkelformel ganz zu entfernen oder durch eine andere zu ersetzen.
In einfachen Formeln müssen Sie manchmal nur die Parameter der Formel neu anpassen, damit sie nicht auf sich selbst verweist. Ändern Sie beispielsweise die Formel in B6 in =SUMME(B1:B5)*A5 (ändern Sie B6 in B5).
Das Ergebnis der Berechnung wird als „756“ zurückgegeben.
In Fällen, in denen ein Excel-Zirkelverweis schwer zu finden ist, können Sie ihn mithilfe der Funktionen zum Verfolgen von Präzedenzfällen und zum Verfolgen von Abhängigkeiten bis zur Quelle zurückverfolgen und nacheinander lösen. Der Pfeil zeigt an, welche Zellen von der aktiven Zelle betroffen sind.
Es gibt zwei Ablaufverfolgungsmethoden, mit denen Sie Zirkelbezüge löschen können, indem Sie die Beziehungen zwischen Formeln und Zellen anzeigen.
Um auf die Tracing-Methoden zuzugreifen, gehen Sie zur Registerkarte „Formeln“ und klicken Sie dann in der Gruppe „Formel-Audit“ entweder auf „Trace Precedents“ oder „Trace Dependents“.
Präzedenzfälle verfolgen
Wenn Sie diese Option auswählen, werden die Zellen verfolgt, die den Wert der aktiven Zelle beeinflussen. Es zeichnet eine blaue Linie, die anzeigt, welche Zellen die aktuelle Zelle beeinflussen. Die Tastenkombination zum Verwenden von Ablaufverfolgungspräzedenzfällen ist Alt + T U T.
Im folgenden Beispiel zeigt der blaue Pfeil die Zellen, die den B6-Wert beeinflussen, B1:B6 und A5 sind. Wie Sie unten sehen können, ist Zelle B6 auch Teil der Formel, was sie zu einem Zirkelbezug macht und dazu führt, dass die Formel als Ergebnis „0“ zurückgibt.
Dies kann leicht behoben werden, indem im Argument von SUM B6 durch B5 ersetzt wird: =SUM(B1:B5).
Abhängigkeiten verfolgen
Die Funktion "Abhängige verfolgen" verfolgt die Zellen, die von der ausgewählten Zelle abhängig sind. Diese Funktion zeichnet eine blaue Linie, die anzeigt, welche Zellen von der ausgewählten Zelle betroffen sind. Das heißt, es zeigt an, welche Zellen Formeln enthalten, die auf die aktive Zelle verweisen. Die Tastenkombination zur Verwendung von Abhängigen ist Alt + T U D.
Im folgenden Beispiel ist Zelle D3 von B4 betroffen. Es hängt von B4 ab, um Ergebnisse zu erzielen. Daher zeichnet spurabhängig eine blaue Linie von B4 zu D3, was anzeigt, dass D3 von B4 abhängig ist.
Bewusste Verwendung von Zirkelbezügen in Excel
Es wird nicht empfohlen, Zirkelverweise absichtlich zu verwenden, aber es kann einige seltene Fälle geben, in denen Sie Zirkelverweise benötigen, um die gewünschte Ausgabe zu erhalten.
Lassen Sie uns das anhand eines Beispiels erklären.
Aktivieren Sie zunächst in Ihrer Excel-Arbeitsmappe die Option "Iterative Berechnung". Sobald Sie die iterative Berechnung aktiviert haben, können Sie Zirkelverweise zu Ihrem Vorteil verwenden.
Nehmen wir an, Sie kaufen ein Haus und möchten Ihrem Makler eine Provision von 2% auf die Gesamtkosten des Hauses geben. Die Gesamtkosten werden in Zelle B6 berechnet und der Provisionsprozentsatz (Agentengebühr) wird in B4 berechnet. Die Provision wird aus den Gesamtkosten berechnet und die Gesamtkosten beinhalten die Provision. Da die Zellen B4 und B6 voneinander abhängen, entsteht ein zirkulärer Bezug.
Geben Sie die Formel zur Berechnung der Gesamtkosten in Zelle B6 ein:
=SUMME(B1:B4)
Da die Gesamtkosten die Maklergebühr enthalten, haben wir B4 in die obige Formel aufgenommen.
Um die Maklergebühr von 2% zu berechnen, fügen Sie diese Formel in B4 ein:
=B6*2%Jetzt hängt die Formel in Zelle B4 vom Wert von B6 ab, um 2% der Gesamtgebühr zu berechnen, und die Formel in B6 hängt von B4 ab, um die Gesamtkosten (einschließlich Agentengebühr) zu berechnen, daher der Zirkularverweis.
Wenn die iterative Berechnung aktiviert ist, gibt Excel Ihnen keine Warnung oder eine 0 im Ergebnis aus. Stattdessen wird das Ergebnis der Zellen B6 und B4 wie oben gezeigt berechnet.
Die Option für iterative Berechnungen ist normalerweise standardmäßig deaktiviert. Wenn Sie es nicht eingeschaltet haben und wenn Sie die Formel in B4 eingeben, wird ein Zirkelbezug erstellt. Excel gibt die Warnung aus und wenn Sie auf "OK" klicken, wird der Tracer-Pfeil angezeigt.
Das ist es. Dies war alles, was Sie über Zirkelverweise in Excel wissen müssen.