Mit SVERWEIS bekommen wir immer das erste Match. Das gleiche passiert mit der INDEX MATCH-Funktion. Wie können wir also das zweite Match oder das dritte oder n-te VERSUCHEN? In diesem Artikel erfahren Sie, wie Sie das N-te Vorkommen eines Wertes im Bereich ermitteln.
Generische Formel
{=KLEIN(WENN(Bereich=Wert,REIHE(Bereich)-REIHE(first_cell_in_range)+1),n)}
Hinweis: Dies ist ein Array-Formel. Sie müssen es mit STRG + UMSCHALT + EINGABE eingeben.
Bereich: der Bereich, in dem Sie suchen möchten nte Position von Wert.
Wert: der Wert, den Sie suchen nPlatz in derBereich.
First_cell_in_range: die erste Zelle imBereich. Wenn der Bereich A2:A10 ist, ist die erste Zelle im Bereich A2.
n: das Auftreten Anzahl von Werte.
Sehen wir uns ein Beispiel an, um die Dinge zu verdeutlichen.
Beispiel: Finden Sie die zweite Übereinstimmung in Excel
Hier habe ich also diese Liste mit Namen im Excel-Bereich A2: A10. Ich habe diesen Bereich benannt als Namen. Jetzt möchte ich die Position des zweiten Vorkommens von „Rony“ in Namen.
Im obigen Bild können wir sehen, dass es sich an der 7. Position im Bereich A2: A10 befindet (Namen). Jetzt müssen wir seine Position mit einer Excel-Formel ermitteln.
Wenden Sie die obige generische Formel in C2 an, um das zweite Vorkommen von Rony in der Liste zu suchen.
{=KLEIN(WENN(Namen = „Rony“ ,REIHE(Namen)-REIHE(A2)+1),2)}
Geben Sie es mit STRG + UMSCHALT + EINGABE ein…
Und wir haben eine Antwort. Es zeigt 7, was richtig ist. Wenn Sie den Wert von n auf 3 ändern, ergibt sich 8. Wenn Sie den Wert von n ändern, der größer ist als das Vorkommen des Wertes im Bereich, wird #NUM Fehler zurückgegeben.
Wie funktioniert es?
Nun, es ist ganz einfach. Sehen wir uns jeden Teil einzeln an.
WENN(Namen = „Rony“ ,REIHE(Namen)-REIHE(A2)+1) :
Bei IF gibt names=“Rony“ ein Array von TRUE und FALSE zurück. TRUE, wenn sich eine Zelle im Bereich befindet Namen (A2:A10) stimmt mit „Rony“ überein.{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
Nächste REIHE(Namen)-REIHE(A2)+1:
REIHE(Namen): Hier gibt die ROW-Funktion die Zeilennummer jeder Zelle in Namen zurück. {2;3;4;5;6;7;8;9;10}.
REIHE(Namen)-REIHE(A2)Dann subtrahieren wir die Zeilennummer von A2 von jedem Wert im angegebenen Array. Dies gibt uns ein Array von Seriennummern, die bei 0 beginnen. {0;1;2;3;4;5;6;7;8}.
REIHE(Namen)-REIHE(A2)+1: Um Seriennummern ab 1 zu erhalten, fügen wir jedem Wert in diesem Array 1 hinzu. Dies gibt uns die Seriennummer beginnend mit 1. {1;2;3;4;5;6;7;8;9}.
Jetzt haben wir IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE},{1;2;3;4;5;6;7;8;9}). Dies wird zu {1;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;7;8;FALSCH} aufgelöst.
Jetzt haben wir die Formel zu SMALL({1;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;7;8;FALSCH},2). Jetzt gibt SMALL den zweitkleinsten Wert im Bereich zurück, der 7 ist.
Wie verwenden wir es?
Es stellt sich die Frage: Welchen Vorteil hat es, einen Rohindex des n-ten Treffers zu erhalten? Es wäre nützlicher, wenn Sie verwandte Informationen aus dem n-ten Wert abrufen könnten. Nun, das kann man auch machen. Wenn wir einen Wert aus dem Wert der n-ten Übereinstimmung der benachbarten Zelle im Bereich erhalten möchten Namen (A2: A10).
{=INDEX(B2:B10, KLEIN(WENN(Namen = „Rony“ ,REIHE(Namen)-REIHE(A2)+1),2))}
Also ja, Leute, so könnt ihr das n-te Match in einer Reihe bekommen. Ich hoffe, ich war erklärend genug. Wenn Sie Zweifel an diesem Artikel oder einem anderen Thema im Zusammenhang mit Excel/VBA haben, schreiben Sie in den Kommentarbereich unten.
So erhalten Sie eine fortlaufende Zeilennummer in Excel
Vlookup Top 5 Werte mit doppelten Werten mit INDEX-MATCH in Excel
SVERWEIS Mehrere Werte
Verwenden Sie INDEX und MATCH, um den Wert zu suchen
Lookup-Wert mit mehreren Kriterien
Populäre Artikel:
Die SVERWEIS-Funktion in Excel
ZÄHLENWENN in Excel 2016
So verwenden Sie die SUMIF-Funktion in Excel