Zählen Sie die Anzahl der Zeilen, die bestimmte Werte in Excel enthalten
Es kann für uns einfach sein, die Anzahl der Zellen mit einem bestimmten Wert in einem Excel-Arbeitsblatt zu zählen. Es kann jedoch ziemlich kompliziert sein, die Anzahl der Zeilen zu ermitteln, die bestimmte Werte enthalten. In diesem Fall kann Ihnen eine komplexere Formel basierend auf den Funktionen SUM, MMULT, TRANSPOSE und COLUMN einen Gefallen tun. In diesem Tutorial erfahren Sie, wie Sie diese Formel erstellen, um diesen Job in Excel zu bearbeiten.
Anzahl der Zeilen zählen, die bestimmte Werte enthalten
Zum Beispiel haben Sie einen Wertebereich in einem Arbeitsblatt, und jetzt müssen Sie die Anzahl der Zeilen mit einem bestimmten Wert „300“ zählen, wie im folgenden Screenshot gezeigt:
Um die Anzahl der Zeilen zu ermitteln, die bestimmte Werte enthalten, lautet die generische Syntax:
Array formula, should press Ctrl + Shift + Enter keys together.
- data: Der Zellbereich, der überprüft werden soll, ob er den spezifischen Wert enthält;
- X: Der spezifische Wert, den Sie zum Zählen der Zeilen verwenden.
1. Bitte geben Sie die folgende Formel ein oder kopieren Sie sie in eine leere Zelle, in die Sie das Ergebnis einfügen möchten:
2. Dann drücken Strg + Umschalt + Enter Tasten zusammen, um das richtige Ergebnis zu erhalten, siehe Screenshot:
Erklärung der Formel:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- --$A$2:$C$12=300: Dieser Ausdruck prüft, ob der Wert „300“ im Bereich A2:C12 vorhanden ist, und generiert ein WAHR- und ein falsches Array-Ergebnis. Das doppelte negative Vorzeichen wird verwendet, um die TRUEs in 1s und FALSE in 0s umzuwandeln. Sie erhalten also das Ergebnis wie folgt: {0,0,0;1,0,0;0,0,0;0,1,1;0,0,0;0,1,0;0,0,0 ,1,0,0;0,0,1;0,0,0;1,1,1;11}. Das aus 3 Zeilen und 1 Spalten bestehende Array funktioniert als ArrayXNUMX in der MMULT-Funktion.
- TRANSPOSIEREN(SPALTE($A$2:$C$12)): Die COLUMN-Funktion hier wird verwendet, um die Spaltennummer des Bereichs A2:C12 zu erhalten, sie gibt ein 3-Spalten-Array wie folgt zurück: {1,2,3}. Und dann tauscht die TRANSPOSE-Funktion dieses Array in ein 3-Zeilen-Array {1;2;3} aus, das als Array2 innerhalb der MMULT-Funktion fungiert.
- MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12))): Diese MMULT-Funktion gibt das Matrixprodukt der beiden obigen Arrays zurück, Sie erhalten das Ergebnis wie folgt: {0;1;0;5;0;2;0;1;3;0;6}.
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Überprüfen Sie zuerst, ob die Werte im Array größer als 0 sind: Wenn ein Wert größer als 0 ist, wird ein TRUE angezeigt; wenn kleiner als 0, wird FALSE angezeigt. Und dann erzwingt das doppelte negative Vorzeichen, dass die TRUEs und FALSEs 1s und 0s sind, also erhalten Sie Folgendes: SUM({0;1;0;1;0;1;0;1;1;0;1}). Schließlich summiert die SUM-Funktion die Werte im Array, um das Ergebnis zurückzugeben: 6.
Tipps:
Wenn Sie die Anzahl der Zeilen zählen müssen, die einen bestimmten Text in einem Arbeitsblatt enthalten, wenden Sie die folgende Formel an und denken Sie daran, die Taste . zu drücken Strg + Umschalt + Enter Tasten zusammen, um die Gesamtzahl zu erhalten:
Verwendete relative Funktion:
- SUMME:
- Die Excel-SUM-Funktion gibt die Summe der gelieferten Werte zurück.
- MULT:
- Die Excel-MMULT-Funktion gibt das Matrixprodukt zweier Arrays zurück.
- TRANSPOSIEREN:
- Die TRANSPOSE-Funktion gibt ein Array in einer neuen Ausrichtung basierend auf einem bestimmten Zellenbereich zurück.
- SPALTE:
- Die COLUMN-Funktion gibt die Spaltennummer zurück, in der die Formel angezeigt wird, oder gibt die Spaltennummer der angegebenen Referenz zurück.
Weitere Artikel:
- Zeilen zählen, wenn interne Kriterien erfüllt sind
- Angenommen, Sie haben einen Bericht über die Produktverkäufe dieses und des letzten Jahres, und jetzt müssen Sie möglicherweise Produkte zählen, bei denen die Verkäufe in diesem Jahr höher sind als im Vorjahr oder die Verkäufe in diesem Jahr niedriger sind als im Vorjahr, wie unten beschrieben Screenshot gezeigt. Normalerweise können Sie eine Hilfsspalte zur Berechnung der Verkaufsdifferenz zwischen den beiden Jahren hinzufügen und dann ZÄHLENWENN verwenden, um das Ergebnis zu erhalten. In diesem Artikel werde ich jedoch die SUMPRODUCT-Funktion vorstellen, um das Ergebnis direkt ohne Hilfsspalte zu erhalten.
- Zeilen zählen, wenn mehrere Kriterien erfüllt sind
- Zählen Sie die Anzahl der Zeilen in einem Bereich basierend auf mehreren Kriterien, von denen einige von den logischen Tests abhängen, die auf Zeilenebene funktionieren, kann Ihnen die SUMPRODUCT-Funktion in Excel einen Gefallen tun.
- Zählen Sie die Anzahl der Zellen gleich einem von vielen Werten
- Angenommen, ich habe eine Liste von Produkten in Spalte A, jetzt möchte ich die Gesamtzahl der spezifischen Produkte Apple, Grape und Lemon abrufen, die im Bereich C4:C6 aus Spalte A aufgeführt sind, wie unten im Screenshot gezeigt. Normalerweise funktionieren in Excel die einfachen Funktionen ZÄHLENWENN und ZÄHLENWENN in diesem Szenario nicht. In diesem Artikel werde ich darüber sprechen, wie Sie diese Aufgabe schnell und einfach mit der Kombination der SUMMENPRODUKT- und ZÄHLENWENN-Funktionen lösen können.
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.
