Fehlende Werte finden

Es gibt Fälle, in denen Sie zwei Listen vergleichen müssen, um zu überprüfen, ob ein Wert aus Liste A in Liste B in Excel vorhanden ist. Zum Beispiel haben Sie eine Liste von Produkten und möchten überprüfen, ob die Produkte in Ihrer Liste in der vom Lieferanten bereitgestellten Produktliste enthalten sind. Um diese Aufgabe zu erledigen, listen wir hier drei Möglichkeiten auf, wählen Sie diejenige aus, die Ihnen gefällt.

Fehlende Werte mit MATCH, ISNA und IF finden
Fehlende Werte mit VLOOKUP, ISNA und IF finden
Fehlende Werte mit COUNTIF und IF finden

Fehlende Werte mit MATCH, ISNA und IF finden

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten vorhanden sind, wie im obigen Screenshot gezeigt, können Sie zunächst die MATCH-Funktion verwenden, um die Position eines Produkts aus Ihrer Liste (Wert aus Liste A) in der Liste des Lieferanten (Liste B) abzurufen. MATCH gibt den #N/A-Fehler zurück, wenn ein Produkt nicht gefunden wird. Dann können Sie das Ergebnis an ISNA weitergeben, um die #N/A-Fehler in TRUEs umzuwandeln, was bedeutet, dass diese Produkte fehlen. Die IF-Funktion gibt dann das gewünschte Ergebnis zurück.

Allgemeine Syntax

=WENN(ISTNV(VERGLEICH("Suchwert",Suchbereich,0)),"Fehlend","Gefunden")

√ Hinweis: Sie können "Fehlend", "Gefunden" nach Bedarf in beliebige Werte ändern.

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten vorhanden sind, kopieren Sie bitte die folgende Formel in die Zelle H6, oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten:

=WENN(ISTNV(VERGLEICH(30002,$B$6:$B$10,0)),"Fehlend","Gefunden")

Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu gestalten:

=WENN(ISTNV(VERGLEICH(G6,$B$6:$B$10,0)),"Fehlend","Gefunden")

√ Hinweis: Das Dollarzeichen ($) oben zeigt absolute Bezüge an, was bedeutet, dass sich der Suchbereich in der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es wurden jedoch keine Dollarzeichen zum Suchwert hinzugefügt, da dieser dynamisch sein soll. Nachdem Sie die Formel eingegeben haben, ziehen Sie den Ausfüllknauf nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erklärung der Formel

Hier verwenden wir die folgende Formel als Beispiel:

=WENN(ISTNV(VERGLEICH(G8,$B$6:$B$10,0)),"Fehlend","Gefunden")

• VERGLEICH(G8,$B$6:$B$10,0): Der match_type 0 zwingt die VERGLEICH-Funktion dazu, einen numerischen Wert zurückzugeben, der die Position des ersten Treffers von 3004, dem Wert in der Zelle G8, im Array $B$6:$B$10 angibt. In diesem Fall konnte VERGLEICH den Wert jedoch nicht im Sucharray finden, daher wird es den #N/A-Fehler zurückgeben.
• ISTNV(VERGLEICH(G8,$B$6:$B$10,0)) = ISTNV(#N/A): ISTNV prüft, ob ein Wert ein „#N/A“-Fehler ist oder nicht. Wenn ja, gibt die Funktion WAHR zurück; Wenn der Wert etwas anderes als „#N/A“-Fehler ist, gibt sie FALSCH zurück. Daher gibt diese ISTNV-Formel WAHR zurück.
• WENN(ISTNV(VERGLEICH(G8,$B$6:$B$10,0)),"Fehlend","Gefunden") = WENN(WAHR,"Fehlend","Gefunden"): Die WENN-Funktion gibt Fehlend zurück, wenn der Vergleich durch ISTNV und VERGLEICH WAHR ist, andernfalls gibt sie Gefunden zurück. Daher gibt die Formel Fehlend zurück.

Fehlende Werte mit VLOOKUP, ISNA und IF finden

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten vorhanden sind, können Sie die oben genannte VERGLEICH-Funktion durch VLOOKUP ersetzen, da sie genauso funktioniert wie VERGLEICH, indem sie den #N/A-Fehler zurückgibt, wenn der Wert in einer anderen Liste nicht existiert, oder wir sagen, er fehlt.

Allgemeine Syntax

=WENN(ISTNV(SVERWEIS("Suchwert",Suchbereich,1,FALSCH)),"Fehlend","Gefunden")

√ Hinweis: Sie können "Fehlend", "Gefunden" nach Bedarf in beliebige Werte ändern.

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten vorhanden sind, kopieren Sie bitte die folgende Formel in die Zelle H6, oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten:

=WENN(ISTNV(SVERWEIS(30002,$B$6:$B$10,1,FALSCH)),"Fehlend","Gefunden")

Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu gestalten:

=WENN(ISTNV(SVERWEIS(G6,$B$6:$B$10,1,FALSCH)),"Fehlend","Gefunden")

√ Hinweis: Das Dollarzeichen ($) oben zeigt absolute Bezüge an, was bedeutet, dass sich der Suchbereich in der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es wurden jedoch keine Dollarzeichen zum Suchwert hinzugefügt, da dieser dynamisch sein soll. Nachdem Sie die Formel eingegeben haben, ziehen Sie den Ausfüllknauf nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erklärung der Formel

Hier verwenden wir die folgende Formel als Beispiel:

=WENN(ISTNV(SVERWEIS(G8,$B$6:$B$10,1,FALSCH)),"Fehlend","Gefunden")

• SVERWEIS(G8,$B$6:$B$10,1,FALSCH): Der range_lookup FALSCH zwingt die SVERWEIS-Funktion dazu, den Wert zu suchen und zurückzugeben, der genau mit 3004 übereinstimmt, dem Wert in der Zelle G8. Wenn der Suchwert 3004 in der ersten Spalte des Arrays $B$6:$B$10 vorhanden ist, gibt SVERWEIS diesen Wert zurück; Andernfalls gibt es den #N/A-Fehlerwert zurück. Hier existiert 3004 nicht im Array, daher wäre das Ergebnis #N/A.
• ISTNV(SVERWEIS(G8,$B$6:$B$10,1,FALSCH)) = ISTNV(#N/A): ISTNV prüft, ob ein Wert ein „#N/A“-Fehler ist oder nicht. Wenn ja, gibt die Funktion WAHR zurück; Wenn der Wert etwas anderes als „#N/A“-Fehler ist, gibt sie FALSCH zurück. Daher gibt diese ISTNV-Formel WAHR zurück.
• WENN(ISTNV(SVERWEIS(G8,$B$6:$B$10,1,FALSCH)),"Fehlend","Gefunden") = WENN(WAHR,"Fehlend","Gefunden"): Die WENN-Funktion gibt Fehlend zurück, wenn der Vergleich durch ISTNV und SVERWEIS WAHR ist, andernfalls gibt sie Gefunden zurück. Daher gibt die Formel Fehlend zurück.

Fehlende Werte mit COUNTIF und IF finden

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten vorhanden sind, können Sie eine einfachere Formel mit den COUNTIF- und IF-Funktionen verwenden. Die Formel nutzt die Tatsache aus, dass Excel jede Zahl außer Null (0) als WAHR auswertet. Wenn also ein Wert in einer anderen Liste vorhanden ist, gibt die COUNTIF-Funktion die Anzahl seiner Vorkommen in dieser Liste zurück, und IF nimmt die Zahl als WAHR; Wenn der Wert nicht in der Liste vorhanden ist, gibt die COUNTIF-Funktion 0 zurück, und IF nimmt dies als FALSCH.

Allgemeine Syntax

=WENN(ZÄHLENWENN("Suchbereich",Suchwert),"Gefunden","Fehlend")

√ Hinweis: Sie können "Gefunden", "Fehlend" nach Bedarf in beliebige Werte ändern.

Um herauszufinden, ob alle Produkte in Ihrer Liste auch in der Liste Ihres Lieferanten vorhanden sind, kopieren Sie bitte die folgende Formel in die Zelle H6, oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten:

=WENN(ZÄHLENWENN($B$6:$B$10,30002),"Gefunden","Fehlend")

Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu gestalten:

=WENN(ZÄHLENWENN($B$6:$B$10,G6),"Gefunden","Fehlend")

√ Hinweis: Das Dollarzeichen ($) oben zeigt absolute Bezüge an, was bedeutet, dass sich der Suchbereich in der Formel nicht ändert, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es wurden jedoch keine Dollarzeichen zum Suchwert hinzugefügt, da dieser dynamisch sein soll. Nachdem Sie die Formel eingegeben haben, ziehen Sie den Ausfüllknauf nach unten, um die Formel auf die darunterliegenden Zellen anzuwenden.

Erklärung der Formel

Hier verwenden wir die folgende Formel als Beispiel:

=WENN(ZÄHLENWENN($B$6:$B$10,G8),"Gefunden","Fehlend")

• ZÄHLENWENN($B$6:$B$10,G8): Die ZÄHLENWENN-Funktion zählt, wie oft 3004, der Wert in der Zelle G8, im Array $B$6:$B$10 vorkommt. Offensichtlich existiert 3004 nicht im Array, daher wäre das Ergebnis 0.
• WENN(ZÄHLENWENN($B$6:$B$10,G8),"Gefunden","Fehlend") = WENN(0,"Gefunden","Fehlend"): Die WENN-Funktion wertet 0 als FALSCH aus. Daher gibt die Formel Fehlend zurück, den Wert, der zurückgegeben wird, wenn das erste Argument als FALSCH ausgewertet wird.

Verwandte Funktionen

Verwandte Formeln