Kurze Antwort
Die Fehler #REF!, #WERT! und #N/A in Excel entstehen meist durch gelöschte Zellen, auf die eine Formel verweist, durch Text statt Zahlen oder durch Daten, die in der Suchtabelle nicht vorhanden sind. Beheben Sie sie, indem Sie die Bezüge in der Formel verfolgen, die Datentypen prüfen und Funktionen wie IFERROR und XLOOKUP mit einer klaren Meldung statt eines Fehlers verwenden.
So erkennen Sie das Problem
- In der Zelle wird #REF! anstelle des berechneten Werts angezeigt
- Die Formel gibt #WERT! zurück, obwohl alle Zellen ausgefüllt sind
- VLOOKUP zeigt #N/A für Daten, die offensichtlich vorhanden sind
- Der Fehler tritt nur in einigen Zeilen auf, in anderen nicht
- Der gesamte Bericht oder die Pivot-Tabelle zeigt nach dem Kopieren Fehler
Häufigste Ursachen
Gelöschte Zeilen oder Spalten
Wenn Sie eine Zelle, Zeile oder Spalte löschen, auf die eine Formel verweist, weiß Excel nicht mehr, woher es den Wert nehmen soll, und zeigt #REF! an.
Text statt Zahl
#WERT! tritt auf, wenn eine Formel eine Zahl erwartet, in der Zelle aber Text, ein Leerzeichen oder eine als Text gespeicherte Zahl steht.
Wert existiert nicht in der Suchtabelle
#N/A bedeutet, dass die Suchfunktion den gesuchten Wert nicht gefunden hat, meist wegen Unterschieden bei Leerzeichen, versteckten Zeichen oder dem Datentyp.
Nicht übereinstimmende Bezüge nach dem Kopieren
Das Kopieren einer Formel ohne absolute Bezüge verschiebt den Suchbereich und liefert ein falsches Ergebnis oder einen Fehler.
Leere Zellen in mathematischen Operationen
Division durch Null ergibt #DIV/0!, und eine leere Zelle kann in einigen Funktionen #WERT! oder ein falsches Ergebnis verursachen.
Lösung Schritt für Schritt
- 1
Feststellen, um welchen Fehler es sich handelt
Klicken Sie auf die Zelle mit dem Fehler und sehen Sie sich das kleine Dreieck in der oberen linken Ecke der Zelle an. Wenn Sie mit der Maus darüberfahren, zeigt Excel eine kurze Erklärung des Fehlers an. Für weitere Details gehen Sie zur Registerkarte
Formelnund wählenFehlerüberprüfung. - 2
Bezüge in der Formel verfolgen
Doppelklicken Sie auf die Zelle, um in den Bearbeitungsmodus zu wechseln. Excel färbt die Bezüge in verschiedenen Farben ein, sodass Sie sofort sehen, welche Zellen die Formel verwendet. Wenn ein Bezug nicht eingefärbt ist, wurde die Zelle gelöscht und dort ist #REF! entstanden.
- 3
#REF! durch Wiederherstellen des Bezugs beheben
Ersetzen Sie den Teil der Formel, der
#REF!enthält, durch die korrekte Adresse der Zelle oder des Bereichs. Wenn Sie nicht wissen, welche Zelle es war, machen Sie die letzte Änderung mitStrg+Zrückgängig und löschen Sie den Inhalt der Zelle anstelle der gesamten Zeile oder Spalte. - 4
Datentyp bei #WERT! prüfen
Wählen Sie die Zellen aus, die die Formel verwendet, und prüfen Sie auf der Registerkarte
Start, ob das FormatAllgemeinoderZahlist. Als Text gespeicherte Zahlen erkennen Sie am grünen Dreieck; wählen Sie den Bereich aus und dannDaten > Text in Spalten > Fertig stellen, um sie in Zahlen umzuwandeln. Erstellen Sie vor der Konvertierung eine Kopie der Datei, da führende Nullen verloren gehen können. - 5
Leerzeichen und versteckte Zeichen entfernen
Prüfen Sie bei #N/A zuerst, ob in den Zellen führende oder nachfolgende Leerzeichen vorhanden sind. Die Funktion
=TRIM(A2)entfernt überflüssige Leerzeichen, und=CLEAN(A2)entfernt versteckte Zeichen. Vergleichen Sie das Ergebnis mit der Suchtabelle und versuchen Sie es erneut. - 6
Suchbereich fixieren
Wenn Sie VLOOKUP oder XLOOKUP verwenden, drücken Sie
F4auf dem Bezug der Suchtabelle, um einen absoluten Bezug zu erhalten, zum Beispiel$A$2:$D$100. So verschiebt das Kopieren der Formel in andere Zeilen den Bereich nicht und verursacht kein #N/A. - 7
Fehler mit der Funktion IFERROR ausblenden
Schreiben Sie statt eines rohen Fehlers
=IFERROR(A2/B2;"keine Daten")oder=IFERROR(XLOOKUP(...);"nicht gefunden"). Die Tabelle bleibt lesbar, und Sie sehen sofort, wo Daten fehlen. Achten Sie darauf, nicht das eigentliche Problem zu verbergen, das behoben werden muss. - 8
Formel an einer kleinen Stichprobe testen
Kopieren Sie die Formel in eine Hilfsspalte und wenden Sie sie auf einige Zeilen mit bekanntem Ergebnis an. Wenn es übereinstimmt, kopieren Sie sie auf den gesamten Bereich. So vermeiden Sie, dass der Fehler Hunderte von Zeilen auf einmal erfasst.
Wenn der Fehler auch nach der Korrektur bleibt
Wenn der Fehler auch nach der Korrektur wiederkehrt, prüfen Sie, ob das Blatt geschützt ist oder ob eine externe Datei an der Formel beteiligt ist, die verschoben wurde. Bei freigegebenen Arbeitsmappen in Microsoft 365 achten Sie darauf, ob jemand anderes denselben Bereich gleichzeitig ändert. Bei großen Tabellen mit mehreren Tausend Zeilen verwenden Sie die Filteroption auf der Registerkarte Daten und filtern Sie nach Fehlern, um sie in Stapeln zu korrigieren. Wenn die Arbeitsmappe durch Zusammenführen mehrerer Dateien entstanden ist, prüfen Sie, ob die Formeln mit falschen Bezügen übernommen wurden. Speichern Sie eine Kopie der Datei vor größeren Änderungen, damit Sie zum vorherigen Zustand zurückkehren können.
So beugen Sie vor
- Verwenden Sie absolute Bezüge für Suchtabellen und benannte Bereiche
- Löschen Sie anstelle von Zeilen und Spalten nur den Inhalt von Zellen
- Importieren Sie Daten mit `Daten > Aus Text/CSV` anstelle des Kopierens aus anderen Programmen
- Überprüfen Sie Formeln gelegentlich mit `Formeln > Formeln anzeigen`, bevor Sie die Datei freigeben
Wann Sie einen Profi rufen sollten
Wenn Fehler eine große Anzahl von Formeln in mehreren Blättern betreffen oder die Arbeitsmappe Makros und externe Verknüpfungen enthält, rufen Sie den IT-Support an, damit er die Struktur der Datei vor größeren Änderungen prüft.
IT-Infrastruktur-Wartung · Server, Netzwerke, Backup, Sicherheit und HelpdeskHäufige Fragen
Was bedeutet #REF! in Excel?
#REF! bedeutet, dass eine Formel auf eine Zelle, Zeile oder Spalte verweist, die nicht mehr existiert, meist weil sie gelöscht wurde. Korrigieren Sie den Bezug in der Formel oder machen Sie das Löschen mit `Strg+Z` rückgängig.
Warum gibt VLOOKUP #N/A zurück, obwohl der Wert vorhanden ist?
Die häufigsten Gründe sind Unterschiede bei Leerzeichen, versteckte Zeichen, der Datentyp oder ein falsch eingestelltes viertes Argument für die genaue Übereinstimmung. Bereinigen Sie die Werte mit den Funktionen `TRIM` und `CLEAN` und prüfen Sie, ob beide Seiten denselben Typ haben.
Ist die Verwendung von IFERROR sicher?
Ja, aber IFERROR verbirgt den Fehler und kann das eigentliche Problem in den Daten verschleiern. Verwenden Sie es, um eine klare Meldung anzuzeigen, nicht als dauerhafte Lösung für falsche Bezüge.