Fehlende Werte finden
Es gibt Fälle, in denen Sie zwei Listen vergleichen müssen, um zu überprüfen, ob ein Wert von Liste A in Liste B in Excel vorhanden ist. Sie haben beispielsweise eine Produktliste und möchten prüfen, ob die Produkte in Ihrer Liste in der von Ihrem Lieferanten bereitgestellten Produktliste enthalten sind. Um diese Aufgabe zu erfüllen, haben wir hier unten drei Möglichkeiten aufgelistet. Sie können die gewünschte auswählen.
Finden Sie fehlende Werte mit MATCH, ISNA und IF
Finden Sie fehlende Werte mit SVERWEIS, ISNA und IF
Finden Sie fehlende Werte mit COUNTIF und IF
Finden Sie fehlende Werte mit MATCH, ISNA und IF
Um herauszufinden, wenn alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten vorhanden sind Wie im obigen Screenshot gezeigt, können Sie zunächst mit der Funktion MATCH die Position eines Produkts Ihrer Liste (Wert der Liste A) in der Lieferantenliste (Liste B) abrufen. MATCH gibt den Fehler #N/A zurück, wenn ein Produkt nicht gefunden wird. Dann können Sie das Ergebnis an ISNA senden, um die #N/A-Fehler in WAHR zu konvertieren, was bedeutet, dass diese Produkte fehlen. Die IF-Funktion gibt dann das erwartete Ergebnis zurück.
Generische Syntax
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")
√ Hinweis: Sie können die Werte für "Fehlend", "Gefunden" beliebig ändern.
- Lookup-Wert: Der Wert MATCH, der verwendet wird, um seine Position abzurufen, wenn er in existiert Suchbereich oder #N/A Fehler, wenn nicht. Hier bezieht sich auf die Produkte in Ihrer Liste.
- Suchbereich: Der Zellbereich, der mit dem verglichen werden soll Lookup-Wert. Bezieht sich hier auf die Produktliste des Lieferanten.
Um herauszufinden, wenn alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten vorhanden sind, bitte kopieren oder geben Sie die Formel unten in die Zelle H6 ein und drücken Sie Enter um das Ergebnis zu erhalten:
=WENN(ISNA(VERGLEICH(30002,$ B $ 6: $ B $ 10,0)),"Vermisst","Gefunden")
Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu machen:
=WENN(ISNA(VERGLEICH(G6,$ B $ 6: $ B $ 10,0)),"Vermisst","Gefunden")
√ Hinweis: Die Dollarzeichen ($) oben stehen für absolute Referenzen, d.h. die Suchbereich in der Formel ändert sich nicht, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es werden jedoch keine Dollarzeichen hinzugefügt Lookup-Wert weil es dynamisch sein soll. Ziehen Sie nach der Eingabe der Formel den Ausfüllpunkt nach unten, um die Formel auf die unteren Zellen anzuwenden.
Erklärung der Formel
Hier verwenden wir die folgende Formel als Beispiel:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")
- SPIEL(G8,$B$6:$B$10,0): Der match_type 0 zwingt die MATCH-Funktion, einen numerischen Wert zurückzugeben, der die Position der ersten Übereinstimmung von angibt 3004, der Wert in der Zelle G8, im Array $ B $ 6: $ B $ 10. In diesem Fall konnte MATCH jedoch den Wert im Lookup-Array nicht finden, sodass es die zurückgibt #N / A Fehler.
- ISNA(SPIEL(G8,$B$6:$B$10,0)) = ISNA(#N / A): ISNA arbeitet, um herauszufinden, ob ein Wert ein „#N/A“-Fehler ist oder nicht. Wenn ja, gibt die Funktion TURE zurück; Wenn der Wert alles andere als der Fehler „#N/A“ ist, wird FALSE zurückgegeben. Diese ISNA-Formel wird also zurückkehren TURE.
- WENN(ISNA(SPIEL(G8,$B$6:$B$10,0)),"Fehlend","Gefunden") = IF(TRUE,"Vermisst","Gefunden"): Die IF-Funktion gibt Missing zurück, wenn der Vergleich von ISNA und MATCH WAHR ist, andernfalls wird Found zurückgegeben. Die Formel wird also zurückkehren Vermisst.
Finden Sie fehlende Werte mit SVERWEIS, ISNA und IF
Um herauszufinden, ob alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten vorhanden sind, können Sie die obige MATCH-Funktion durch SVERWEIS ersetzen, da sie genauso funktioniert wie MATCH, dass sie den #N/A-Fehler zurückgibt, wenn der Wert nicht vorhanden ist eine andere Liste, oder wir sagen, sie fehlt.
Generische Syntax
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")
√ Hinweis: Sie können die Werte für "Fehlend", "Gefunden" beliebig ändern.
- Lookup-Wert: Der Wert SVERWEIS, der verwendet wird, um seine Position abzurufen, wenn er in existiert Suchbereich oder #N/A Fehler, wenn nicht. Hier bezieht sich auf die Produkte in Ihrer Liste.
- Suchbereich: Der Zellbereich, der mit dem verglichen werden soll Lookup-Wert. Bezieht sich hier auf die Produktliste des Lieferanten.
Um herauszufinden, ob alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten vorhanden sind, kopieren Sie bitte die Formel unten in die Zelle H6 oder geben Sie sie ein und drücken Sie Enter um das Ergebnis zu erhalten:
=WENN(ISNA(SVERWEIS(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Fehlend","Gefunden")
Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu machen:
=WENN(ISNA(SVERWEIS(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Fehlend","Gefunden")
√ Hinweis: Die Dollarzeichen ($) oben stehen für absolute Referenzen, d.h. die Suchbereich in der Formel ändert sich nicht, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es werden jedoch keine Dollarzeichen hinzugefügt Lookup-Wert weil es dynamisch sein soll. Ziehen Sie nach der Eingabe der Formel den Ausfüllpunkt nach unten, um die Formel auf die unteren Zellen anzuwenden.
Erklärung der Formel
Hier verwenden wir die folgende Formel als Beispiel:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- SVERWEIS(G8,$B$6:$B$10,1,FALSCH): Die range_lookup FALSCH zwingt die SVERWEIS-Funktion, den genau übereinstimmenden Wert zu suchen und zurückzugeben 3004, der Wert in der Zelle G8. Wenn der lookup_value 3004 existiert in der 1Spalte des Arrays $ B $ 6: $ B $ 10, gibt SVERWEIS diesen Wert zurück; Andernfalls wird der Fehlerwert #N/A zurückgegeben. Hier existiert 3004 nicht im Array, das Ergebnis wäre also #N / A.
- ISNA(SVERWEIS(G8,$B$6:$B$10,1,FALSCH)) = ISNA(#N / A): ISNA arbeitet, um herauszufinden, ob ein Wert ein „#N/A“-Fehler ist oder nicht. Wenn ja, gibt die Funktion TURE zurück; Wenn der Wert alles andere als der Fehler „#N/A“ ist, wird FALSE zurückgegeben. Diese ISNA-Formel wird also zurückkehren TURE.
- WENN(ISNA(SVERWEIS(G8,$B$6:$B$10,1,FALSCH)),"Fehlend","Gefunden") = IF(TRUE,"Vermisst","Gefunden"): Die IF-Funktion gibt Missing zurück, wenn der von ISNA und SVERWEIS durchgeführte Vergleich WAHR ist, andernfalls wird Found zurückgegeben. Die Formel wird also zurückkehren Vermisst.
Finden Sie fehlende Werte mit COUNTIF und IF
Um herauszufinden, ob alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten vorhanden sind, können Sie eine einfachere Formel mit den Funktionen ZÄHLENWENN und WENN verwenden. Die Formel nutzt die Tatsache, dass Excel jede Zahl außer Null (0) als WAHR auswertet. Wenn also ein Wert in einer anderen Liste vorhanden ist, gibt die Funktion ZÄHLENWENN die Anzahl seiner Vorkommen in dieser Liste zurück, dann nimmt IF die Zahl als TURE; Wenn der Wert nicht in der Liste vorhanden ist, gibt die ZÄHLENWENN-Funktion 0 zurück, und IF nimmt ihn als FALSE.
Generische Syntax
=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")
√ Hinweis: Sie können die Werte für "Gefunden", "Fehlend" beliebig ändern.
- Suchbereich: Der Zellbereich, der mit dem verglichen werden soll Lookup-Wert. Bezieht sich hier auf die Produktliste des Lieferanten.
- Lookup-Wert: Der Wert COUNTIF, der verwendet wird, um die Anzahl seiner Vorkommen in zurückzugeben Suchbereich. Hier bezieht sich auf die Produkte in Ihrer Liste.
Um herauszufinden, ob alle Produkte in Ihrer Liste in der Liste Ihres Lieferanten vorhanden sind, kopieren Sie bitte die Formel unten in die Zelle H6 oder geben Sie sie ein und drücken Sie Enter um das Ergebnis zu erhalten:
=WENN(ZÄHLENWENN($ B $ 6: $ B $ 10,30002),"Gefunden","Vermisst")
Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu machen:
=WENN(ZÄHLENWENN($ B $ 6: $ B $ 10,G6),"Gefunden","Vermisst")
√ Hinweis: Die Dollarzeichen ($) oben stehen für absolute Referenzen, d.h. die Suchbereich in der Formel ändert sich nicht, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es werden jedoch keine Dollarzeichen hinzugefügt Lookup-Wert weil es dynamisch sein soll. Ziehen Sie nach der Eingabe der Formel den Ausfüllpunkt nach unten, um die Formel auf die unteren Zellen anzuwenden.
Erklärung der Formel
Hier verwenden wir die folgende Formel als Beispiel:
=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")
- ZÄHLENWENN($B$6:$B$10,G8): Die ZÄHLENWENN-Funktion zählt, wie oft 3004, der Wert in der Zelle G8, erscheint im Array $ B $ 6: $ B $ 10. Anscheinend existiert 3004 nicht im Array, also wäre das Ergebnis 0.
- WENN(ZÄHLENWENN($B$6:$B$10,G8),"Gefunden","Fehlend") = IF(0,"Gefunden","Vermisst"): Die IF-Funktion wertet 0 als FALSE aus. Die Formel wird also zurückkehren Vermisst, der Wert, der zurückgegeben werden soll, wenn die erste Vergrößerung FALSE ergibt.
Verwandte Funktionen
Die IF-Funktion ist eine der einfachsten und nützlichsten Funktionen in der Excel-Arbeitsmappe. Es führt einen einfachen logischen Test durch, der vom Vergleichsergebnis abhängt, und gibt einen Wert zurück, wenn ein Ergebnis TRUE ist, oder einen anderen Wert, wenn das Ergebnis FALSE ist.
Die Excel MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position des Werts zurück.
Die Excel VLOOKUP-Funktion sucht nach einem Wert, indem sie in der ersten Spalte einer Tabelle übereinstimmt, und gibt den entsprechenden Wert aus einer bestimmten Spalte in derselben Zeile zurück.
Die ZÄHLENWENN-Funktion ist eine statistische Funktion in Excel, die verwendet wird, um die Anzahl der Zellen zu zählen, die ein Kriterium erfüllen. Es unterstützt logische Operatoren (<>, =, > und <) und die Platzhalter (? und *) für die teilweise Übereinstimmung.
Verwandte Formeln
Suchen Sie einen Wert mit einem bestimmten Text mit Platzhaltern
Um die erste Übereinstimmung zu finden, die eine bestimmte Textzeichenfolge in einem Bereich in Excel enthält, können Sie eine INDEX- und MATCH-Formel mit Platzhalterzeichen verwenden - das Sternchen (*) und das Fragezeichen (?).
Teilweise Übereinstimmung mit SVERWEIS
Es gibt Zeiten, in denen Sie Excel benötigen, um Daten basierend auf Teilinformationen abzurufen. Um das Problem zu lösen, können Sie eine SVERWEIS-Formel zusammen mit Platzhalterzeichen verwenden - dem Sternchen (*) und dem Fragezeichen (?).
Ungefähre Übereinstimmung mit INDEX und MATCH
Es gibt Zeiten, in denen wir ungefähre Übereinstimmungen in Excel finden müssen, um die Leistung der Mitarbeiter zu bewerten, die Ergebnisse der Schüler zu bewerten, das Porto basierend auf dem Gewicht zu berechnen usw. In diesem Tutorial werden wir darüber sprechen, wie die Funktionen INDEX und MATCH zum Abrufen der Ergebnisse, die wir brauchen.
Suchen Sie den nächstgelegenen Übereinstimmungswert mit mehreren Kriterien
In einigen Fällen müssen Sie möglicherweise basierend auf mehr als einem Kriterium den nächstgelegenen oder ungefähren Übereinstimmungswert suchen. Mit der Kombination von INDEX-, MATCH- und IF-Funktionen können Sie dies schnell in Excel erledigen.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.