So finden Sie Zirkelbezüge in Excel

Bei der Arbeit mit Microsoft Excel kann es gelegentlich zu einem sogenannten Zirkelverweis kommen. Dabei verweist eine Formel in einer Zelle auf sich selbst, was zu einer Endlosschleife im Berechnungsprozess führt.

Um Zirkelbezüge in Excel zu finden, verwenden Sie das Tool „Fehlerprüfung“ auf der Registerkarte „Formeln“, um Zellen mit einer Zirkelabhängigkeit zu identifizieren.

In diesem Artikel erfahren Sie, wie Sie Zirkelverweise in Excel finden und beheben. Außerdem erfahren Sie, wie Sie diese bei der Arbeit mit großen oder komplexen Arbeitsblättern verhindern können.

Inhaltsverzeichnis

• Was sind Zirkelverweise?
• Das Fehlermeldungsfenster für Zirkelverweise
• So finden Sie Zirkelverweise in Excel
• So finden Sie Zirkelbezüge mit der Excel-Statusleiste
• So finden Sie versteckte Zirkelbezüge in Excel
  • So verwenden Sie das Trace-abhängige Tool
  • Tool „Präzedenzfälle verfolgen“.
• 2 Möglichkeiten, Zirkelbezüge in Excel zu entfernen
  • 2. Verwenden Sie alternative Formeln
• 5 Tipps zur Vermeidung von Zirkelbezügen
  • 1. Seien Sie vorsichtig bei der Auswahl der Bereiche
  • 2. Planen Sie Ihre Formeln sorgfältig
  • 3. Verwenden Sie alternative Formeln
  • 4. Teilen Sie komplexe Formeln in kleinere Teile auf
  • 5. Beheben Sie Probleme unterwegs
• Wie aktiviert man iterative Berechnungen und warum sollte man sich die Mühe machen?

Was sind Zirkelverweise?

Zirkelverweise treten in Excel auf, wenn eine Formelberechnung in einer Zelle vom eigenen Wert der Zelle abhängt. Dies erzeugt eine Schleife und führt zu falschen oder unvorhersehbaren Ergebnissen.

Anhand eines einfachen Beispiels ist dies leichter zu verstehen.

Angenommen, Sie haben zwei Zahlen in den Zellen A1 und B1. Sie möchten in Zelle A3 eine Formel verwenden, die die beiden Zahlen addiert. Die Formel in Zelle A3 sollte so aussehen:

=A1 + B1

Wenn Sie jedoch einen Fehler machen und C1 in die Berechnungen einbeziehen, haben Sie jetzt einen Zirkelverweis:

=A1 + B1 + C1

Dies kann ein offensichtliches Problem sein, das es zu vermeiden gilt, wenn es um zwei Zellen geht. Wenn Sie jedoch mit großen Zellbereichen arbeiten, sind Fehler wahrscheinlicher.

Glücklicherweise ist Excel darauf ausgelegt, solche Situationen zu bewältigen, indem es eine Warnmeldung anzeigt, wenn ein Zirkelverweis erkannt wird.

In einigen Fällen kann es jedoch vorkommen, dass Sie auf einen Zirkelverweis stoßen, ohne dass Sie eine Warnung erhalten. Daher ist es wichtig zu wissen, wie diese Probleme manuell gefunden und behoben werden können.

Das Fehlermeldungsfenster für Zirkelverweise

Standardmäßig überwacht Excel, ob Zirkelbezüge vorliegen, wenn Sie Formeln in Zellen eingeben. Wenn Sie in einer Zelle mit einem Problem die Eingabetaste drücken, wird in einem Popup-Fenster die Zirkelverweis-Fehlermeldung angezeigt.

Dies ist der erklärende Text:

Es gibt einen oder mehrere Zirkelbezüge, bei denen eine Formel direkt oder indirekt auf die eigene Zelle verweist. Dies könnte dazu führen, dass sie falsch berechnen. Versuchen Sie, diese Verweise zu entfernen oder zu ändern oder die Formeln in andere Zellen zu verschieben.

Wenn Sie dieses Dialogfeld sehen, sollten Sie auf „OK“ klicken und die in diesem Artikel beschriebenen Methoden verwenden, um das Problem zu beheben.

Sie müssen den Fehler jedoch nicht sofort beheben. Sie können mit der Tabelle weiterarbeiten, um das Problem später zu lösen.

In diesem Fall müssen Sie wissen, wie Sie Zirkelverweise in Excel finden, nachdem Sie das Warnfeld ausgeblendet haben.

So finden Sie Zirkelverweise in Excel

Excel verfügt über ein integriertes Tool zur Fehlerprüfung, das verschiedene Arten von Fehlern findet, einschließlich Zirkelverweise. Sie greifen auf das Tool über die Registerkarte „Formeln“ zu.

Dieses Bild zeigt den Standort innerhalb der Formula Auditing-Gruppe.

Jetzt wissen Sie, wie Sie das Tool finden. Hier sind die detaillierten Schritte zur Verwendung:

1. Gehen Sie zur Registerkarte Formeln.

1. Klicken Sie in der Gruppe „Formelprüfung“ auf das Dropdown-Menü „Fehlerprüfung“.

2. Wählen Sie „Zirkuläre Referenzen“ aus der Dropdown-Liste.

3. Klicken Sie auf den Link, um mit dem Cursor zur ersten Zelle mit einem Problem zu springen.

Das Bild unten zeigt, dass das Tool einen kreisförmigen Fehler in Zelle G1 gefunden hat. Die angezeigte Zelle ist ein Hyperlink.

Wenn Sie auf den Link klicken, bewegt sich Ihr Cursor in die Zelle. Dies ist bei großen Datenmengen sehr hilfreich.

Sobald Sie den ersten Zirkelverweis aufgelöst haben, können Sie zum Fehlerprüfungstool zurückkehren, um den nächsten Zirkelverweis auf dem Blatt zu finden.

Befolgen Sie die Schritte in den späteren Abschnitten dieses Artikels, um das Problem zu beheben, und verwenden Sie das Tool, um zum nächsten Problem überzugehen.

Wenn keine zirkulären Probleme mehr vorliegen, ist der Menüpunkt „Rundschreiben“ im Dropdown-Menü ausgegraut. Sie haben keine weiteren Arbeiten zu erledigen.

So finden Sie Zirkelbezüge mit der Excel-Statusleiste

Eine andere Möglichkeit, einen Zirkelverweis zu erkennen, besteht darin, einen Blick auf die Statusleiste am unteren Rand des Excel-Arbeitsblatts zu werfen.

Wenn das Arbeitsblatt ein oder mehrere Probleme enthält, zeigt die Statusleiste die Position einer Problemzellenreferenz an.

Wenn sich beispielsweise in Zelle C1 ein Zirkelverweis befindet und es keine anderen Probleme mit der Tabelle gibt, lautet die Statusleiste: „Zirkelverweise: C1“.

Dieses Bild zeigt Ihnen, wo Sie das Display sehen können.

So finden Sie versteckte Zirkelbezüge in Excel

Versteckte Zirkelverweise in Excel sind Zirkelverweise, die von den integrierten Fehlerfunktionen nicht sofort erkannt werden. Mit anderen Worten: Excel zeigt die Standardwarnung nicht an.

Sie treten auf, wenn eine Zelle über eine Reihe von Zwischenzellen indirekt auf eine andere Zelle verweist und schließlich auf die ursprüngliche Zelle zurück verweist.

Excel verfügt über zwei Tools, mit denen Sie Beziehungen nachverfolgen und diese Probleme untersuchen können:

1. Tool „Abhängige verfolgen“.

2. Tool „Präzedenzfälle verfolgen“.

So verwenden Sie das Trace-abhängige Tool

Erfahrene Excel-Benutzer nutzen das Tool „Trace Dependents“, um diese Probleme zu untersuchen und Zirkelverweise zu beheben. Wenn Sie eine oder mehrere Zellen auswählen und dieses Tool verwenden, zeigt Ihnen Excel die abhängigen Zellen an.

Das Tool ist im Abschnitt „Formelprüfung“ auf der Registerkarte „Formeln“ verfügbar.

Um das Tool zu verwenden, gehen Sie folgendermaßen vor:

1. Klicken Sie auf die Zelle oder Zellen, deren Angehörige Sie verfolgen möchten.

2. Gehen Sie im Excel-Menüband auf die Registerkarte „Formeln“.

3. Klicken Sie in der Gruppe „Formelprüfung“ auf die Schaltfläche „Abhängige verfolgen“.

4. Überprüfen Sie den bzw. die Markierungspfeile, die Abhängigkeiten anzeigen.

Excel zeigt Pfeile vom Wert der aktiven Zelle zu allen davon abhängigen Zellen an. Der blaue Pfeil im Bild unten zeigt, dass Zelle D6 eine Abhängigkeit von Zelle B2 aufweist.

Tool „Präzedenzfälle verfolgen“.

Präzedenzfälle sind das Gegenteil von Abhängigen. Mit dem Werkzeug „Präzedenzfälle verfolgen“ können Sie eine Zelle mit einer Formel auswählen und alle Zellen anzeigen, von denen sie abhängt, also die Präzedenzfälle.

Das Tool ist im Abschnitt „Formelprüfung“ auf der Registerkarte „Formeln“ verfügbar.

Um das Tool zu verwenden, gehen Sie folgendermaßen vor:

1. Klicken Sie auf die Zelle, die die Formel enthält, die Sie untersuchen möchten.

2. Gehen Sie im Excel-Menüband zur Registerkarte Formel.

3. Klicken Sie in der Gruppe „Formelprüfung“ auf die Schaltfläche „Präzedenzfälle verfolgen“.

4. Überprüfen Sie die Verfolgungspfeile, die Excel anzeigt.

Die blauen Pfeile im Bild unten zeigen Ihnen, dass Zelle D6 vom Bereich der Zellen A2:B5 abhängt.

Mit beiden Tools können Sie Excel-Zirkelverweise finden, die nicht sofort erkennbar sind. Der nächste Schritt besteht darin, das Problem in der ausgewählten Zelle zu beheben. Anschließend können Sie in anderen Zellen weiter nach demselben Problem suchen.

2 Möglichkeiten, Zirkelbezüge in Excel zu entfernen

Es gibt zwei Hauptmethoden zum Auflösen von Zirkelverweisen:

• den Zirkelverweis brechen

• Verwendung alternativer Formeln

Lassen Sie uns beide Möglichkeiten erklären.

1. Den Zirkelverweis brechen

Das Aufbrechen des Zirkelverweises in Excel ist eine effiziente Möglichkeit, das Problem zu lösen. Dazu müssen Sie die am Zirkelbezug beteiligten Zellen identifizieren und ihre Formeln entsprechend ändern.

Befolgen Sie dazu die zuvor beschriebenen Schritte, um die Zellenadresse zu ermitteln.

Anschließend sollten Sie die Formel in der Formelleiste untersuchen. Suchen Sie nach einem Verweis auf die Zelle selbst.

In unserem ersten Beispiel eines Zirkelbezugs enthielt die Additionsformel in Zelle C1 einen Verweis auf die Zelle selbst:

=A1 + B1 + C1

Dies kann durch Entfernen des letzten Zusatzes behoben werden. Sobald dieses Problem gelöst ist, machen Sie sich auf die Suche nach anderen Zirkelverweisen und beheben diese.

2. Verwenden Sie alternative Formeln

Zu den häufigsten Ursachen für Zirkelbezüge gehören das Summieren oder Mitteln von Zellen. Der Fehler besteht darin, die Ergebniszelle in die Formel einzubeziehen.

In unserem vorherigen Beispiel verwendete die Formel Additionsoperatoren, um die Summe eines Zellbereichs zu berechnen. Die alternative Methode besteht darin, die Excel-Funktion SUMME zu verwenden.

Um das Beispiel zu korrigieren, ersetzen Sie die Formel durch:

=SUMME(A1:B1)

Ebenso könnten Sie einen Bereich mit einer Formel wie =(A1+B1)/2 mitteln. Allerdings ist die Wahrscheinlichkeit, dass die Funktion AVERAGE zu einem Fehler führt, geringer.

=DURCHSCHNITT(A1:B1)

5 Tipps zur Vermeidung von Zirkelbezügen

Um Zirkelverweise in Microsoft Excel zu verhindern, ist es wichtig, genau zu verstehen, wie Ihre Formeln in Ihrer Excel-Tabelle miteinander interagieren.

Indem Sie die Beziehungen zwischen Zellen identifizieren und wissen, wie diese Verbindungen zusammenarbeiten, können Sie die Erstellung von Zirkelformeln vermeiden.

Hier sind unsere fünf besten Tipps, die Ihnen helfen, Zirkelbezüge zu vermeiden.

1. Seien Sie vorsichtig bei der Auswahl der Bereiche

Meiner Erfahrung nach besteht eine der häufigsten Ursachen für Zirkelverweise darin, dass Benutzer schnell Bereiche auswählen, die sie in ihre Formeln aufnehmen möchten.

Wenn Sie vertikal und/oder horizontal wischen, um Zellen zu gruppieren, können Sie ganz einfach eine weitere Zelle auswählen, die an den richtigen Bereich angrenzt. Wenn diese Zelle auch die Ergebniszelle ist, haben Sie versehentlich einen Zirkelbezug erstellt.

Dieser Fehler wird typischerweise bei der Arbeit mit großen Datenmengen gemacht, beispielsweise bei der Durchführung von Chi-Quadrat-Tests. Die Lösung besteht darin, bei der Auswahl Ihres Sortiments vorsichtig zu sein.

2. Planen Sie Ihre Formeln sorgfältig

Bevor Sie Formeln eingeben, denken Sie über die Logik hinter Ihren Berechnungen nach und darüber, wie diese über die Zellen hinweg ablaufen sollen.

Sie können die Berechnungen im Voraus mithilfe von Diagrammtools oder einem Whiteboard planen.

Dies kann Ihnen dabei helfen, mögliche Schleifen zu identifizieren, die zu Zirkelverweisen führen könnten.

3. Verwenden Sie alternative Formeln

Bestimmte Funktionen wie INDIRECT oder OFFSET können bei unsachgemäßer Verwendung versehentlich Zirkelverweise erstellen.

Erwägen Sie die Verwendung alternativer Funktionen, die eine ähnliche Funktionalität ohne das Risiko von Zirkelverweisen bieten.

4. Teilen Sie komplexe Formeln in kleinere Teile auf

Anstatt eine einzige komplexe Formel mit mehreren verschachtelten Funktionen zu erstellen, sollten Sie Ihre Berechnungen in kleinere, besser handhabbare Teile aufteilen.

Dies erleichtert nicht nur das Verständnis und die Pflege Ihrer Arbeitsmappe, sondern verringert auch die Gefahr der Einführung eines Zirkelverweises.

5. Beheben Sie Probleme unterwegs

Bei der Arbeit mit großen oder komplizierten Arbeitsmappen ist es außerdem wichtig, auf Zirkelverweise zu achten. Anstatt Probleme zu ignorieren, bis eine große Menge an Daten und Formeln zusammengestellt wurde, ist es besser, jeden Fehler zu beheben, sobald er auftritt.

Sie können eine Warnmeldung zum Zirkelverweis für kurze Zeit ignorieren. Sie sollten jedoch die Fehlerprüfungsfunktion verwenden, um die vorhandenen Zirkelverweise in Kürze nachzuholen.

Dadurch können sie einfacher behoben werden und ihre Auswirkungen auf Ihre Arbeitsmappe werden verhindert.

Wie aktiviert man iterative Berechnungen und warum sollte man sich die Mühe machen?

Normalerweise möchten Sie Zirkelbezüge in Ihrem Arbeitsblatt vermeiden. Es gibt jedoch einige Szenarien, in denen sie erforderlich sind.

Beispielsweise müssen einige technische oder wissenschaftliche Berechnungen das Ergebnis einer früheren Berechnung verwenden. Dies wird als iterative Lösung bezeichnet.

Jede Schleife verfeinert die Ergebnisse einer Berechnung, bis ein bestimmter Schwellenwert oder ein bestimmtes Ziel erreicht ist.

In diesen Fällen ist es wichtig, iterative Berechnungen in den Excel-Einstellungen zu aktivieren, um beabsichtigte Zirkelbezüge zu ermöglichen. Dadurch kann die Software die Berechnungen eine bestimmte Anzahl von Malen wiederholen und so zu einer Lösung gelangen.

Befolgen Sie diese Schritte, um einen Zirkelverweis in Ihrer Excel-Datei zu erstellen:

1. Klicken Sie auf die Registerkarte Datei.

2. Optionen wählen.

3. Wählen Sie die Kategorie Formeln.

4. Aktivieren Sie im Abschnitt „Berechnungsoptionen“ das Kontrollkästchen „Iterative Berechnung aktivieren“.

5. Ändern Sie die Zahl im Feld „Maximale Iterationen“ oder belassen Sie den Standardwert 100.

6. Verwenden Sie das Feld „Maximale Änderung“, um die Iterationen gemäß Ihren Anforderungen zu reduzieren.