#BEZUG! (englisch #REF!) bedeutet, dass eine Formel auf eine Zelle verweist, die nicht da ist. Die übliche Ursache ist eine gelöschte Zeile, Spalte oder ein gelöschtes Blatt: Wird Spalte C gelöscht, schreibt Excel =B2*C2 in =B2*#BEZUG! um, und das Ergebnis ist von da an #BEZUG!. Drück direkt nach dem Löschen Strg+Z (auf dem Mac Cmd+Z), um die Spalte und die Formel zurückzubekommen. Die Tabelle zeigt die englischen Fehlernamen, also #REF!, und die englischen Funktionsnamen; du kannst die Formeln dort auch deutsch eingeben, mit Semikolons, etwa =SVERWEIS(E2;A2:C6;3;FALSCH).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Qty | Total |
| 2 | Apple | 1.2 | 10 | #REF! |
| 3 | Pear | 1.5 | 20 | #REF! |
| 4 | Plum | 0.8 | 15 | #REF! |
| 5 | Bread | 2.4 | 5 | #REF! |
#REF! Die Formel verweist auf eine Zelle, die es nicht gibt.Im deutschen Excel: =B2*#REF!Die Mengenspalte wurde gelöscht und wieder eingetippt, aber die Formel sagt immer noch #REF!: Excel repariert einen Bezug nie, wenn er einmal weg ist. Klick auf D2, ersetze #REF! durch C2 und drück die Eingabetaste. Die ganze Spalte zieht mit, und D2 zeigt 12.
Wie #BEZUG! in eine Formel kommt
Excel schreibt #BEZUG! in eine Formel, sobald eine Zelle verschwindet, die die Formel verwendet hat:
| Das hast du getan | Aus =B2*C2 in D2 wird |
|---|---|
| Spalte C gelöscht | =B2*#BEZUG! |
| Zeile 2 gelöscht | die Formel wird mit ihrer Zeile gelöscht; Formeln in anderen Zeilen, die auf Zeile 2 gezeigt haben, bekommen #BEZUG! |
| Das Blatt gelöscht, auf das eine Formel verweist | =#BEZUG!B2*2 (bei einer Formel wie =Prices!B2*2) |
| Eine Zelle ausgeschnitten und über eine Zelle eingefügt, die die Formel verwendet | #BEZUG! an der Stelle des überschriebenen Bezugs |
Zellen innerhalb eines Bereichs zu löschen ist sicher: =SUMME(B2:D2) wird zu =SUMME(B2:C2), wenn Spalte C gelöscht wird. Wird die erste oder letzte Zelle eines Bereichs gelöscht, schrumpft er nur. Also ist =SUMME(B2:D2) sicherer als =B2+C2+D2, das zu =B2+#BEZUG!+C2 wird.
Warum SVERWEIS #BEZUG! liefert
Das dritte Argument von SVERWEIS (englisch VLOOKUP) zählt Spalten innerhalb des Tabellenbereichs. Ist es größer als die Zahl der Spalten im Bereich, ist das Ergebnis #BEZUG!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Pear | #REF! | |
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
#REF! Die Formel verweist auf eine Zelle, die es nicht gibt.Im deutschen Excel: =SVERWEIS(E2;A2:C6;4;FALSCH)A2:C6 hat drei Spalten, also gibt es die 4 nicht. Ändere die 4 in 3, und F2 zeigt 25. Das passiert vor allem, nachdem eine Spalte aus der Verweistabelle gelöscht wurde: Der Bereich schrumpft, die fest eingetragene Spaltennummer nicht. XVERWEIS oder INDEX mit VERGLEICH vermeiden das, weil sie die Rückgabespalte direkt benennen, wie in =XVERWEIS(E2;A2:A6;C2:C6). Die übrigen Argumente stehen unter SVERWEIS.
#BEZUG! mit INDEX und BEREICH.VERSCHIEBEN
INDEX liefert #BEZUG!, wenn die Zeilen- oder Spaltennummer außerhalb seines Bereichs liegt, und BEREICH.VERSCHIEBEN (englisch OFFSET), wenn es über Zeile 1 oder vor Spalte A rutscht.
| A | B | C | |
|---|---|---|---|
| 1 | Score | Result | What it asks for |
| 2 | 88 | #REF! | 6th value of 5 |
| 3 | 72 | 95 | 3rd value of 5 |
| 4 | 95 | #REF! | 2 rows above A2 |
| 5 | 64 | 81 | 4 rows below A2 |
| 6 | 81 |
#REF! Die Formel verweist auf eine Zelle, die es nicht gibt.Im deutschen Excel: =INDEX(A2:A6;6)A2:A6 hat fünf Punktzahlen, also ist INDEX(A2:A6;6) gleich #BEZUG!, während INDEX(A2:A6;3) 95 liefert. Eine Zeile 0 gibt es nicht, also ist BEREICH.VERSCHIEBEN(A2;-2;0) gleich #BEZUG!, und BEREICH.VERSCHIEBEN(A2;4;0) landet auf A6: 81. Kommt die Position aus einer anderen Formel (einem VERGLEICH, einem ANZAHL), prüf zuerst diese Formel. Mehr auf der Seite zu INDEX.
INDIREKT liefert #BEZUG! ebenfalls, wenn sein Text keine gültige Adresse ist (=INDIREKT("ZZZ1"), denn die letzte Spalte ist XFD) oder in eine geschlossene Arbeitsmappe zeigt.
#BEZUG! beim Kopieren einer Formel
Ein relativer Bezug wandert mit der Formel. Kopierst du sie weit genug nach oben oder zur Seite, fällt der Bezug vom Blatt:
C3: =B2*2 (one row up, one column back)
copy C3 to B2: =A1*2
copy C3 to A2: =#REF!*2 (there is no column before A)
In deutschem Excel steht in der letzten Zeile =#BEZUG!*2. Dasselbe passiert, wenn eine Formel, die in ein anderes Blatt oder eine andere Arbeitsmappe kopiert wurde, auf Zellen zeigt, die es dort nicht gibt. Fixiere die Zellen, die sich nicht bewegen dürfen, mit $ (=$B$2*2), oder kopiere den Formeltext aus der Bearbeitungsleiste statt der Zelle. Absolute Bezüge erklären das $.
Jedes #BEZUG! in einer Arbeitsmappe finden und entfernen
- Drück Strg+F (auf dem Mac Cmd+F), tippe
#BEZUG!, öffne Optionen, stell Suchen in auf Formeln und klick auf Alle suchen. Die Liste zeigt jede Formel mit einem kaputten Bezug. - Um viele auf einmal zu reparieren, nimmst du Strg+H (auf dem Mac Control+H): Such
#BEZUG!und ersetze es durch den richtigen Bezug, aber nur, wenn jeder Treffer dieselbe Zelle bekommen soll. - Prüf Formeln > Namens-Manager: Ein Name, dessen Spalte Bezieht sich auf
#BEZUG!zeigt, macht jede Formel kaputt, die ihn verwendet. - Sind die gelöschten Daten weg und wird die Formel nicht mehr gebraucht, markierst du die Zellen und ersetzt die Formeln durch ihre Werte (Kopieren, dann Start > Einfügen > Werte). Fehlerwerte bleiben Fehler, also lösch diese Zellen danach.
Einen Verweis reparieren, der #BEZUG! liefert
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock | Look for | Stock | |
| 2 | Apple | 1.2 | 40 | Plum | ||
| 3 | Pear | 1.5 | 25 | |||
| 4 | Plum | 0.8 | 60 | |||
| 5 | Bread | 2.4 | 12 | |||
| 6 | Milk | 1.1 | 30 |
Du bist dran: =VLOOKUP(E2,A2:C6,4,FALSE) hat #REF! geliefert. Schreib in F2 einen funktionierenden Verweis, der den Bestand des Produkts aus E2 liefert.
Jeder Verweis, der hier 60 liefert und den Daten folgt, besteht: der SVERWEIS mit Spalte 3, =XVERWEIS(E2;A2:A6;C2:C6) oder =INDEX(C2:C6;VERGLEICH(E2;A2:A6;0)).
Häufig gestellte Fragen
Was bedeutet #BEZUG! in Excel?
Die Formel zeigt auf eine Zelle, die es nicht gibt. Meistens wurde eine Zeile, Spalte oder ein Blatt gelöscht, das die Formel verwendet hat, und Excel hat den Bezug durch #BEZUG! ersetzt, also wurde aus =B2*C2 die Formel =B2*#BEZUG!. SVERWEIS und INDEX liefern #BEZUG! auch, wenn die Spalten- oder Zeilennummer größer ist als der Bereich.
Wie behebe ich #BEZUG! nach dem Löschen einer Spalte?
Drück sofort Strg+Z (auf dem Mac Cmd+Z), um das Löschen rückgängig zu machen. Ist es dafür zu spät, klick auf die Formel, ersetze #BEZUG! durch die Zelle, die sie verwenden soll, und füll die Formel wieder nach unten aus.
Warum liefert SVERWEIS #BEZUG!?
Die Spaltennummer ist größer als die Zahl der Spalten im Tabellenbereich. =SVERWEIS(E2;A2:C6;4;FALSCH) verlangt die 4. Spalte eines Bereichs mit 3 Spalten. Nimm 3, oder erweitere den Bereich auf A2:D6.
Wie finde ich alle #BEZUG!-Fehler in einer Arbeitsmappe?
Drück Strg+F (auf dem Mac Cmd+F), such nach #BEZUG!, stell Suchen in auf Formeln und klick auf Alle suchen. Excel listet jede Formel mit einem kaputten Bezug. Prüf auch Formeln > Namens-Manager: Namen können nach einem Löschen auf #BEZUG! zeigen.
Wie vermeide ich #BEZUG! beim Löschen von Zeilen oder Spalten?
Verweise auf Bereiche statt auf einzelne Zellen. =SUMME(B2:D2) schrumpft zu =SUMME(B2:C2), wenn Spalte C oder D gelöscht wird, während aus =B2+C2+D2 die Formel =B2+#BEZUG!+C2 wird. Verweise, die ihre Rückgabespalte benennen, etwa =XVERWEIS(E2;A2:A6;C2:C6), überstehen eingefügte Spalten und gelöschte Spalten, die sie nicht verwenden.
