Zirkelbezug in Excel: finden, verstehen, beheben

Was ein Zirkelbezug ist

Eine Formel braucht Werte, um zu rechnen. Ist einer dieser Werte ihr eigenes Ergebnis, dreht sie sich im Kreis: Um B8 auszurechnen, muss Excel B8 kennen, und dafür müsste es B8 schon ausgerechnet haben. Das nennt man Zirkelbezug, im englischen Excel „circular reference“. Excel bricht ab, zeigt eine Warnung und in der Zelle meist 0.

Es gibt zwei Arten. Beim direkten Zirkelbezug steht die eigene Adresse in der Formel, etwa =SUMME(B2:B8) in B8 oder =B5*1,19 in B5. Beim indirekten läuft der Kreis über andere Zellen: In C2 steht =D2*2, in D2 steht =C2+10. Jede Formel sieht für sich harmlos aus, zusammen bilden sie einen Kreis. Diese Variante ist schwerer zu finden, deshalb lohnt sich die Liste unter Fehlerüberprüfung.

Typische Ursachen im Büro

Die Summe schließt sich selbst ein. Der Klassiker: Unter einer Spalte steht die Summe, und der Bereich wurde eine Zeile zu weit gezogen. Oder du fügst über der Summenzeile eine neue Zeile ein, und beim Anpassen rutscht der Bereich auf die Summe selbst. Auch =SUMME(B:B) in einer Zelle von Spalte B ist ein Zirkelbezug, weil die ganze Spalte die eigene Zelle enthält.

Anteil oder Prozent bezieht sich auf die Gesamtsumme, die den Anteil enthält. Rechnest du in C2 den Anteil =B2/B8, und B8 summiert versehentlich Spalte C mit, entsteht ein indirekter Kreis. Mehr zum Rechnen mit Anteilen zeigt Prozent in Excel.

Kopierte Formeln mit verrutschten Bezügen. Kopierst du eine Formel in eine andere Zeile, wandern relative Bezüge mit. So kann eine Formel plötzlich auf ihre eigene Zeile zeigen. Feste Bezüge mit Dollarzeichen verhindern das, siehe absoluter Bezug.

Zirkelbezug finden, wenn die Datei groß ist

Die Statusleiste zeigt immer nur eine Zelle. Gibt es mehrere Zirkelbezüge, behebst du den ersten, dann zeigt Excel den nächsten. Die Liste unter Fehlerüberprüfung › Zirkelbezüge ist schneller, weil sie alle betroffenen Zellen auf einmal anbietet.

Bei indirekten Kreisen hilft Spur zum Vorgänger mehrmals hintereinander: Jeder Klick zeigt eine Ebene weiter zurück, bis der Pfeil wieder bei der Ausgangszelle ankommt. Mit Pfeile entfernen räumst du hinterher auf. Siehst du nur Zahlen und keine Formeln, schaltest du mit Strg + # alle Formeln sichtbar, wie bei Formeln anzeigen beschrieben.

Iterative Berechnung: wann sie sinnvoll ist (meist nicht)

Unter Datei › Optionen › Formeln gibt es den Haken Iterative Berechnung aktivieren. Dann rechnet Excel den Kreis bis zu 100 Mal durch und hört auf, wenn sich das Ergebnis um weniger als 0,001 ändert. Die Warnung verschwindet. Am Mac findest du die Einstellung unter Excel › Einstellungen › Berechnung als Iteration begrenzen.

Sinnvoll ist das nur, wenn der Kreis gewollt ist. Ein Beispiel aus dem Controlling: Ein Bonus von 10 % soll vom Gewinn nach Abzug des Bonus berechnet werden. Der Bonus hängt vom Gewinn ab und der Gewinn vom Bonus. Iterativ rechnet Excel das aus. Oft geht es aber auch ohne Kreis, hier mit einer direkten Formel: Steht der Gewinn vor Bonus in A2, ist der Bonus =A2*0,1/1,1.

Für alle anderen Fälle ist iterative Berechnung eine Falle. Sie versteckt echte Fehler: Eine Summe, die sich selbst einrechnet, zeigt dann keinen Zirkelbezug mehr, sondern ein falsches Ergebnis ohne jede Warnung. Dazu kommt, dass Excel die Einstellung aus der ersten geöffneten Datei übernimmt. Öffnest du danach eine andere Mappe, gilt sie dort mit. Lass den Haken also aus, solange du keinen konkreten Grund hast.

So prüfst du, ob die Datei sauber ist

Steht unten links nichts mehr von Zirkelbezügen, und ist der Eintrag Zirkelbezüge unter Fehlerüberprüfung ausgegraut, gibt es keinen mehr. Prüf danach die reparierte Summe mit der Statusleiste: Markier die Zahlen darüber, unten rechts steht ihre Summe. Stimmt sie mit deiner Formel überein, passt der Bereich. Wie SUMME und MITTELWERT sauber aufgebaut werden, zeigt SUMME und MITTELWERT. Weitere Formel-Fehler und ihre Ursachen übst du im Excel-Kurs online.