Excel-TippEinfach alle Formelfehler finden

Fehler wie #DIV/0! oder #WERT! können in Excel-Tabellen schnell unübersichtlich werden, vor allem bei großen Datenmengen. Sie treten häufig auf, wenn Formeln fehlerhafte Eingabewerte haben oder wenn sie zu ungültigen Berechnungen führen.

Doch wie findet man schnell alle Zellen mit Fehlern, ohne mühsam jeden Bereich manuell zu durchsuchen?

In diesem Artikel zeigen wir Ihnen, wie Sie mithilfe einer Excel-Formel automatisch die Zelladressen aller Fehler in einem bestimmten Bereich auflisten können. So können Sie auch in umfangreichen Tabellen, die weit über die Bildschirmansicht hinausgehen, alle Fehler finden – ohne Scrollen und langes Suchen.

Beispiel: Tabelle mit Fehlerwerten

In der folgenden Abbildung sehen Sie einen Bereich mit Werten. Sie sollen die Zelladressen, die einen Fehler enthalten, an einer anderen Stelle untereinander auflisten.

Excel-Formel zur Ausgabe aller Zellen mit Fehlerwerten

Sie können diese Aufgabenstellung mit der folgenden Formel lösen:

=ZUSPALTE(WENN(ISTFEHLER(A1:C17); ADRESSE(ZEILE(A1:C17); SPALTE(A1:C17));NV());2)

Die Formel geht hierbei von den folgenden Voraussetzungen aus:

A1:C17 = Bereich, in dem die Werte stehen und in dem Sie nach Fehlern suchen.

Die Formel überprüft jede Zelle im Bereich A1:C17 und liefert entweder die Zelladresse oder den Fehlerwert #NV.

Wie ist die Formel aufgebaut?

Schauen wir uns die einzelnen Komponenten der Excel-Formel etwas genauer an:

ISTFEHLER(A1:C17): Diese Bedingung prüft, ob die Werte im Bereich einen Fehler beinhalten. Wenn der Zellinhalt ein Fehlerwert ist, wird WAHR zurückgegeben, andernfalls FALSCH.

ADRESSE(ZEILE(A1:C17);SPALTE(A1:C17))

• ZEILE(A1:C17) liefert für jede Zelle im Bereich die entsprechende Zeilennummer (zum Beispiel 1 für Zellen in der ersten Zeile, 2 für Zellen in der zweiten etc.).
• SPALTE(A1:C17) gibt für jede Zelle die Spaltennummer zurück (1 für Spalte A, 2 für Spalte B etc.).

ADRESSE(ZEILE(A1:C17);SPALTE(A1:C17)) kombiniert die Zeilen- und Spaltennummern und gibt die Adresse der jeweiligen Zelle als Text zurück (zum Beispiel $A$1 für die Zelle in der ersten Zeile und ersten Spalte).

In der Verbindung mit der WENN()-Funktion sorgt dieser Teil der Formel dafür, dass die Zelladressen aller Zellen, die einen Fehlerwert als Inhalt haben, als Text zurückgegeben werden.

WENN(ISTFEHLER(A1:C17); ADRESSE(ZEILE(A1:C17);SPALTE(A1:C17))

NV(): Wenn der Wert in einer Zelle kein Fehlerwert ist, gibt die Funktion WENN den Fehlerwert #NV (Nicht Verfügbar) zurück. Dies sorgt dafür, dass Sie später bei der Verwendung von ZUSPALTE keine leeren oder falschen Werte aufgelistet bekommen.

Zusammenfassend sorgt diese Kombination aus WENN, ISTFEHLER, ADRESSE, ZEILE, SPALTE und NV dafür, dass Sie die Zelladresse erhalten für Zellen, die einen Fehler als Inhalt haben. Für Zellen, die keinen Fehler als Inhalt haben, liefert die Funktion den Fehlerwert #NV.

Die Zelladressen und Fehlerwerte werden anschließend an die Funktion ZUSPALTE() weitergegeben:

=ZUSPALTE(WENN(ISTFEHLER(A1:C17); ADRESSE(ZEILE(A1:C17); SPALTE(A1:C17));NV());2)

Die Funktion ZUSPALTE() wandelt ein mehrdimensionales Array, wie in unserem Fall den Bereich A1:C17, in eine einspaltige Liste um. Sie sorgt dafür, dass alle Zelladressen oder Fehlerwerte aus dem Bereich mit mehreren Spalten (im Beispiel drei) in einer einzelnen Spalte angezeigt werden.

Hierbei gibt der zweite Parameter (ZUSPALTE(...;2)) an, dass die Funktion ZUSPALTE() keine Fehlerwerte berücksichtigen wird. Die Fehlerwerte NV entsprechen im Beispiel Zellen, deren Zellinhalte keine Fehlerwerte sind und daher nicht weiter berücksichtigt werden sollen.

Von der zeilenweisen zur spaltenweisen Auswertung

Standardmäßig werden die Daten zeilenweise gelesen und umgewandelt. Das bedeutet, Excel wird zunächst die erste Zeile, dann die zweite und alle weiteren Zeilen durchlaufen, um die Zelladressen oder Fehlerwerte zu extrahieren.

Wollen Sie die Daten spaltenweise im Datenbereich lesen und auswerten, dann müssen Sie die Formel wie folgt anpassen:

=ZUSPALTE(WENN(ISTFEHLER(A1:C17); ADRESSE(ZEILE(A1:C17); SPALTE(A1:C17));NV());2;WAHR)