Fehlende Werte zählen
Angenommen, Sie haben zwei Listen. Um die Gesamtzahl der Werte in einer Liste zu zählen, die in einer anderen Liste nicht vorhanden sind, können Sie a verwenden SUMMENPRODUKT Formel mit Hilfe von PASSEN ZU und ISNA-Funktionen, oder die COUNTIF Funktion.
Zählen Sie fehlende Werte mit SUMPRODUCT, MATCH und ISNA
Zählen Sie fehlende Werte mit SUMPRODUCT und COUNTIF
Zählen Sie fehlende Werte mit SUMPRODUCT, MATCH und ISNA
Um die zu zählen Gesamtzahl der Werte in Liste B, die in Liste A fehlen Wie oben gezeigt, können Sie zunächst die MATCH-Funktion verwenden, um ein Array der relativen Position der Werte aus Liste B in Liste A zurückzugeben. Wenn ein Wert in Liste A nicht vorhanden ist, wird ein #N/A-Fehler zurückgegeben. Die ISNA-Funktion identifiziert dann die #N/A-Fehler und SUMPRODUCT zählt die Gesamtzahl der Fehler.
Generische Syntax
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- range_to_count: Der Bereich, aus dem fehlende Werte gezählt werden sollen. Hier wird auf die Liste B verwiesen.
- Suchbereich: Der Bereich zum Vergleich mit dem range_to_count. Hier wird auf die Liste A verwiesen.
- 0: Das Übereinstimmungstyp 0 zwingt MATCH, eine exakte Übereinstimmung durchzuführen.
Um die zu zählen Gesamtzahl der Werte in Liste B, die in Liste A fehlen, bitte kopieren oder geben Sie die Formel unten in die Zelle H6 ein und drücken Sie Enter um das Ergebnis zu erhalten:
=SUMPRODUCT(--ISNA(MATCH(F6: F8,B6: B10,0)))
Erklärung der Formel
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- SPIEL(F6:F8,B6:B10,0): Das Übereinstimmungstyp 0 zwingt die MATCH-Funktion dazu, numerische Werte zurückzugeben, die die relativen Positionen der Werte in den Zellen angeben F6 zu F8 im Bereich B6: B10. Wenn ein Wert in Liste A nicht vorhanden ist, wird ein #N/A-Fehler zurückgegeben. Die Ergebnisse werden also in einem Array wie diesem vorliegen: {2;3;#N/A}.
- ISNA(SPIEL(F6:F8,B6:B10,0)) = ISNA({2;3;#N/A}): ISNA ermittelt, ob es sich bei einem Wert um einen „#N/A“-Fehler handelt oder nicht. Wenn ja, gibt die Funktion ein TURE zurück; Wenn nicht, wird FALSE zurückgegeben. Die ISNA-Formel wird also zurückkehren {FALSE;FALSE;TRUE}.
- SUMMENPRODUKT(--ISNA(SPIEL(F6:F8,B6:B10,0))) = SUMMENPRODUKT(--{FALSE;FALSE;TRUE}): Das doppelte negative Vorzeichen wandelt WAHR-Werte in Einsen und FALSCH-Werte in Nullen um: {0; 1; 0}. Dann gibt die SUMPRODUCT-Funktion die Summe zurück: 1.
Zählen Sie fehlende Werte mit SUMPRODUCT und COUNTIF
Um die zu zählen Gesamtzahl der Werte in Liste B, die in Liste A fehlen, können Sie auch die COUNTIF-Funktion verwenden, um mit der Bedingung „=0“ festzustellen, ob ein Wert in Liste A vorhanden ist oder nicht, da eine 0 generiert wird, wenn ein Wert fehlt. Das SUMPRODUCT zählt dann die Gesamtzahl der fehlenden Werte.
Generische Syntax
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- Suchbereich: Der Bereich zum Vergleich mit dem range_to_count. Hier wird auf die Liste A verwiesen.
- range_to_count: Der Bereich, aus dem fehlende Werte gezählt werden sollen. Hier wird auf die Liste B verwiesen.
- 0: Das Übereinstimmungstyp 0 zwingt MATCH, eine exakte Übereinstimmung durchzuführen.
Um die zu zählen Gesamtzahl der Werte in Liste B, die in Liste A fehlen, bitte kopieren oder geben Sie die Formel unten in die Zelle H6 ein und drücken Sie Enter um das Ergebnis zu erhalten:
=SUMMENPRODUKT(--(ZÄHLENWENN(B6: B10,F6: F8)=0))
Erklärung der Formel
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- ZÄHLENWENN(B6:B10,F6:F8): Die ZÄHLENWENN-Funktion zählt die Anzahl der Vorkommen der Zellen F6 zu F8 im Bereich B6: B10. Das Ergebnis wird in einem Array wie diesem vorliegen: {1; 1; 0}.
- - ((ZÄHLENWENN(B6:B10,F6:F8)= 0) = - (({1; 1; 0}= 0): Das Snippet {1;1;0}=0 ergibt ein TRUE- und FALSE-Array {FALSE;FALSE;TRUE}. Das doppelte negative Vorzeichen verwandelt dann TRUEs in 1 und FALSE in 0. Das endgültige Array sieht folgendermaßen aus: {0; 0; 1}.
- SUMMENPRODUKT(- ((ZÄHLENWENN(B6:B10,F6:F8)= 0)) = SUMMENPRODUKT({0; 0; 1}): Das SUMPRODUCT gibt dann die Summe zurück: 1.
Verwandte Funktionen
In Excel kann die Funktion SUMPRODUCT verwendet werden, um zwei oder mehr Spalten oder Arrays miteinander zu multiplizieren und dann die Summe der Produkte zu erhalten. Tatsächlich ist SUMPRODUCT eine nützliche Funktion, mit deren Hilfe Zellenwerte mit mehreren Kriterien wie COUNTIFS oder SUMIFS gezählt oder summiert werden können. In diesem Artikel werden die Funktionssyntax und einige Beispiele für diese SUMPRODUCT-Funktion vorgestellt.
Die Excel MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position des Werts 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
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.
Dieser Artikel konzentriert sich auf die Excel-Formeln zum Zählen der Zellen, die genau einer von Ihnen angegebenen Textzeichenfolge oder teilweise der angegebenen Textzeichenfolge entsprechen (siehe Abbildung unten). Zunächst werden die Formelsyntax und das Argument erläutert und Beispiele zum besseren Verständnis bereitgestellt.
Zählen Sie die Anzahl der Zellen, die nicht zwischen zwei gegebenen Zahlen liegen
Die Anzahl der Zellen zwischen zwei Zahlen zu zählen, ist eine häufige Aufgabe für uns in Excel, aber in bestimmten Fällen möchten Sie möglicherweise die Zellen nicht zwischen zwei gegebenen Zahlen zählen. Zum Beispiel habe ich eine Produktliste mit den Verkäufen von Montag bis Sonntag, jetzt muss ich die Anzahl der Zellen abrufen, die nicht zwischen den spezifischen niedrigen und hohen Zahlen liegen, wie unten gezeigt. In diesem Artikel werde ich einige Formeln für den Umgang mit dieser Aufgabe in Excel vorstellen.
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.
