In diesem Artikel erfahren Sie, wie Sie Zeilen zählen, wenn Sie in Excel mit mindestens n Zahlen übereinstimmen.
Szenario:
In einfachen Worten, beim Arbeiten mit Zahlendaten. Wir müssen die Anzahl der Zeilen mit mindestens, höchstens oder genau n Werten aus der jeweiligen Zeile extrahieren, die die genannten Kriterien in Excel erfüllt. Zum Beispiel, wenn man die Anzahl der Schüler berechnet, die in Tests unterdurchschnittlich abschneiden. Wir können diese Art von Zählproblemen in einer langen Liste mit der unten erläuterten Excel-Formel durchführen.
Verwendete Formelfunktionen und Formelsyntax:
Die folgende Funktion wird verwendet. Informieren Sie sich also ein wenig darüber, bevor Sie es verwenden.
- Summenfunktion
- MMULT-Funktion
- TRANSPOSE-Funktion
- COLUMN-Funktion
Aus diesen angegebenen Funktionen werden wir eine Formel konstruieren.
Verwenden Sie die Formel:
| { =SUMME ( -- ( MMULT ( -- ( (Daten) < Krit ) , TRANSPOSE ( SPALTE (Daten) 0 ) ) = n ) ) } |
Hinweis: Für geschweifte Klammern ( { } ) Verwenden Strg + Umschalt + Eingabetaste beim Arbeiten mit Arrays oder Bereichen in Excel. Dadurch werden standardmäßig geschweifte Klammern für die Formel generiert. Versuchen Sie NICHT, geschweifte Klammern hart zu codieren.
data : ein Bündel von Zahlen als Daten.
= n : genaue Anzahl der Vorkommen (benutze <= für höchstens oder >= für mindestens Vorkommnisse)
crit : Kriterien, mit denen abgeglichen werden soll
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel testen, indem wir sie im unten gezeigten Beispiel ausführen. Hier haben wir Verkaufsdaten für die jeweilige Verkaufs-ID in einer Reihe. Wir müssen die Anzahl der Verkaufs-IDs ermitteln, bei denen der Verkäufer die Verkäufe von 40 genau zweimal nicht abgeschlossen hat. Hier ist das Kriterium o <40 und das Vorkommen 2 (=2 für das genaue Vorkommen)
Formel:
| { =SUMME ( -- ( MMULT ( -- ( (C4:H15) < 40 ) , TRANSPOSE ( SPALTE (C4:H15) 0 ) ) = 2 ) ) |
Erläuterung:
- Die COLUMN-Funktion gibt das Array der Spaltennummer von der ersten Zelle im Array bis zur letzten Zelle im Array zurück. { 3; 4; 5; 7; 8}
- ^0 , diese Operation mit beliebigen Array-Nummern gibt ein Array von 1 zurück.
- Die Funktion TRANSPOSE konvertiert die Zeilen des Matrix-Arrays in Spalten und die Spalten in Zeilen. Hier wird das Array von 1s in Zeile in Array von 6 (1s) in Spalten umgewandelt. { 1; 1 ; 1 ; 1 ; 1 ; 1 }
- -- ( (C4:H15) < 40 ) , diese Operation gibt die Werte als 1s als TRUE-Werte (übereinstimmende Kriterien) und 0s als False-Werte zurück.
- Die MMULT-Funktion nimmt die Matrixmultiplikation der 2 Arrays und gibt die Werte wie unten gezeigt zurück.
= SUMME ( -- ( { 1; 0 ; 1 ; 0 ; 2 ; 1 ; 0 ; 0 ; 1 ; 2 ; 3 ; 3 } = 2 ) )
- Die SUM-Funktion zählt die Werte in Array Matching 2, wenn das Vorkommen erforderlich ist.
- Verwenden Sie Strg + Umschalt + Eingabetaste anstelle von, um das Ergebnis zu erhalten. Da dies eine Array-Formel ist.
Hier werden die Arrays zur Funktion als Array-Referenz angegeben. Verwenden Sie doppelt (--) manuell Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Wie Sie im obigen Bild sehen können, gibt es 2 Verkäufer, die nicht einmal 40 Verkäufe abgeschlossen haben. Dies ist die Anzahl der entsprechenden Zeilen in den Daten, bei denen 2 Zahlen kleiner als 40 sind. Sie können die Anzahl der Vorkommen und die zu prüfenden Kriterien in den Zeilen der Tabelle anpassen. Unten ist nun das Beispiel, um die Anzahl mit verschiedenen Kriterien und verschiedenen Vorkommen zu erhalten.
Zählen Sie Zeilen, die mit Verkäufen über 100 übereinstimmen, wobei 3 als genaues Vorkommen gilt:
Jetzt möchten wir wissen, wie viele Verkäufer im Verkauf mäßig gute Leistungen erbringen. Dazu müssen wir die Anzahl der Verkäufer ermitteln, die genau dreimal Verkäufe über 100 getätigt haben.
Verwenden Sie die Formel:
| = SUMME ( -- ( MMULT ( -- ( (C4:H15) > 100 ) , TRANSPOSE ( SPALTE (C4:H15) 0 ) ) = 3 ) ) |
> 100 : Zahl größer als 100 sein.
= 3 : Auftreten gleich 3
Hier werden die Arrays zur Funktion als Array-Referenz angegeben. Verwenden Sie doppelt (--) manuell Drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.
Wie Sie sehen können, gibt es 3 Verkäufer, denen es mäßig gut geht. Dies ist die Anzahl der entsprechenden Zeilen in den Daten, bei denen 3 Verkäufe größer als 100 sind.
Hier sind alle Beobachtungshinweise zur Verwendung der Formel.
Anmerkungen:
- Die Formel funktioniert nur mit Zahlen.
- MMULT- und TRANSPOSE-Funktionen sind die Matrixfunktionen, also vor der Verwendung überprüfen.
- Wenn die Formel den Fehler #VALUE zurückgibt, überprüfen Sie, ob die geschweiften Klammern in der Formel vorhanden sein müssen, wie in den Beispielen im Artikel gezeigt.
- Negation (--) char ändert Werte, TRUEs oder 1s in FALSE oder 0s und FALSE oder 0s in TRUEs oder 1s .
- Operationen wie gleich ( = ), kleiner gleich ( <= ), größer als ( > ) oder ungleich ( ) kann innerhalb einer angewendeten Formel nur mit Zahlen ausgeführt werden.
Hoffen Sie, dass dieser Artikel zum Zählen von Zeilen bei n übereinstimmenden Werten in Excel erklärend ist. Weitere Artikel zum Thema Zählen von Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie es mit Ihren fristarts auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site
Finden Sie die letzte Datenzeile mit Zahlen in Excel : Suchen Sie in einem Bereich von Textwerten die letzten Daten in Excel.
So verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück.
ZÄHLENWENN mit dynamischem Kriterienbereich : Zählen Sie Zellen abhängig von anderen Zellwerten in Excel.
ZÄHLEN, WENN zwei Kriterien übereinstimmen : Zählen Sie Zellen, die zwei verschiedenen Kriterien auf der Liste in Excel entsprechen.
ZÄHLENWENN mit ODER für mehrere Kriterien : Zählen Sie Zellen mit mehreren übereinstimmenden Kriterien mithilfe der ODER-Funktion.
Die ZÄHLENWENN-Funktion in Excel : Zellen in Abhängigkeit von anderen Zellenwerten zählen.
So verwenden Sie Countif in VBA in Microsoft Excel : Zellen mit Visual Basic für Applikationen-Code zählen.
So verwenden Sie Platzhalter in Excel : Zählen Sie Zellen, die mit Phrasen übereinstimmen, indem Sie die Platzhalter in Excel verwenden
Populäre Artikel :
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.