Zeigen Sie die Zeilen an, die die COUNTIF-Kriterien erfüllen

Hier ist ein sehr einfaches Beispiel. Wir möchten die Anzahl der Werte in Spalte B zählen, die 10 überschreiten . Wir setzen die Kriterien in Zelle C1 und geben in C2 ein:

=COUNTIF(B:B,C1) 

Wir wissen jetzt, dass es 11 Elemente gibt, die zur Zählung beitragen. Jetzt wollen wir sie finden.
Geben Sie die folgende benutzerdefinierte Funktion in einem Standardmodul ein:

Public Function CountIfFinder(rng As Range, crit As String) As String Dim r As Range, DQ As String  DQ = Chr(34) crit = DQ & crit & DQ CountIfFinder = "" Set rng = Intersect(rng, rng.Parent.UsedRange)  For Each r In rng s = "=countif(" & r.Address & "," & crit & ")" If Evaluate(s) = 1 Then CountIfFinder = CountIfFinder & "," & r.Address(0, 0) Next r  CountIfFinder = Mid(CountIfFinder, 2) End Function 

Wählen Sie eine Zelle aus (sagen Sie D1 ) und geben Sie Folgendes ein:

=CountIfFinder(B:B,C1) 

Benutzerdefinierte Funktionen (UDFs) sind sehr einfach zu installieren und zu verwenden:

1. ALT-F11 öffnet das VBE-Fenster
2. ALT-I ALT-M öffnet ein neues Modul
3. Fügen Sie das Zeug ein und schließen Sie das VBE-Fenster

Wenn Sie die Arbeitsmappe speichern, wird die UDF damit gespeichert. Wenn Sie später als 2003 eine Version von Excel verwenden, müssen Sie die Datei als .xlsm statt als .xlsx speichern

So entfernen Sie die UDF:

1. Öffnen Sie das VBE-Fenster wie oben
2. Löschen Sie den Code
3. Schließen Sie das VBE-Fenster

So verwenden Sie die UDF aus Excel:

= meine Funktion (A1)

Weitere Informationen zu Makros im Allgemeinen finden Sie unter:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

und

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

und Einzelheiten zu UDFs finden Sie unter:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Makros müssen aktiviert sein, damit dies funktioniert!

Siehe die Lösung unten, obwohl alle Schritte im angehängten Bild beschrieben wurden.

Ich werde eine detaillierte Erklärung der einzelnen Schritte erstellen, um zu erklären, wie diese funktionieren.

Schritt 1:

Zeigt die von uns festgelegte Suchbedingung. In diesem Beispiel suchen wir nach allen Zeilen, in denen Gender = M ist

Die äquivalente COUNTIF-Funktion wurde unten gezeigt, um die Anzahl der gefundenen Zeilen mit dieser Bedingung 3 zurückzugeben

Schritt 2:

Legen Sie eine Arrayformel fest =IF($B$2:$B$8=$B$11,ROW($B$2:$B$8)). Dies ist eine Arrayformel und verwendet eine Erweiterung der regulären IF-Funktion. Er vergleicht die Werte im Array B2:B8mit B11den Ergebnissen des Vergleichs und gibt sie als Array von Werten zurück. Wenn der Vergleich wahr ist, ist das Ergebnis die ROW () - Nummer, andernfalls FALSCH (da kein Wert angegeben wird, wenn der Vergleich falsch ist).

Um dies weiter zu verstehen, können Sie mit den unten beschriebenen einfacheren IF-Formeln beginnen und mit verschiedenen Optionen in value_if_true und value_if_false experimentieren und die Ergebnisse IF (B2 = B11, ROW (B2),) 'verstehen.

WENN (B2 = B11, REIHE (B2), "Nichtübereinstimmung") "

Versuchen Sie nun dasselbe, indem Sie B11 in F ändern und dann sehen, was mit den Ergebnissen geschieht.

Schritt 3: Hier verwenden wir die Funktion SMALL, um den n-ten kleinsten Wert in einem Array zurückzugeben. Der Trick hier ist jedoch, den n-ten Wert in jeder Zeile zu ändern. Die erste Zeile sollte also den kleinsten Wert im Array anzeigen F2:F8, die zweite Zeile sollte die 2. kleinste und die dritte Zeile den drittkleinsten Wert zurückgeben.

Also verwenden wir die ROW () - 1, um die entsprechende n-te Variable einzurichten, und der Rest ist einfach.

Schritt 4:

Am Ende von Schritt 3 haben wir die Anzahl der Zeilen, in denen unsere Suchbedingung erfüllt ist. Jetzt müssen Sie in diesem Schritt nur die INDEX-Funktion verwenden, um die Zeilenwerte zu extrahieren, die diesen Zeilennummern entsprechen.

Um dies zu erreichen, wählen Sie zunächst die Zellen M2: O2 aus und drücken F2Sie. Der Cursor befindet sich in der Zelle M2. Geben Sie die Formel ein INDEX($A$1:$C$8,J2,0)und drücken Sie gleichzeitig Strg + Umschalt + Eingabetaste, um als Matrixformel zu fungieren. Die 0 in dieser Formel erzwingt die Rückgabe der gesamten Zeile anstelle von Werten aus einer bestimmten Spalte aus dem Bereich A1: C8.

Wählen Sie nun M2: O4 und drücken Sie Strg + D, um die Formel in der obersten Zeile in die darunter liegenden Zellen zu kopieren.

BINGO!

Veröffentlichen Sie Ihre Kommentare, wenn Sie Klarstellungen benötigen, und ich erkläre Sie gerne. Ich habe einige Vereinfachungen verwendet und die Schritte zur Erklärung der Funktionsweise aufgeschlüsselt. Alle diese Formeln können miteinander kombiniert werden, um in einem Durchgang dieselben Ergebnisse zu erzielen.

Eine weitere Vereinfachung: Wenn Sie die Formel in der exakten Anzahl von Zeilen eingeben, können Sie das endgültige Ergebnis-Array jedoch so groß wie die ursprünglichen Daten machen, wenn Sie nicht wissen, wie viele Zeilen von der Suchbedingung zurückgegeben werden Legen Sie einen Bereich fest, der möglicherweise berücksichtigt wird, wenn alle Zeilen zurückgegeben werden. Sie können auch eine Fehlerbehandlung in jeder Formel hinzufügen, um leere Zeilen anzuzeigen, wenn die Anzahl der zurückgegebenen Zeilen kleiner als der Ergebnisbereich ist. Hoffe das macht Sinn!