Video ansehen - Suchen Sie nach dem zweiten, dritten oder N-ten übereinstimmenden Wert
Wenn es um das Nachschlagen von Daten in Excel geht, gibt es zwei erstaunliche Funktionen, die ich oft verwende - SVERWEIS und INDEX (meist in Verbindung mit der MATCH-Funktion).
Diese Formeln sind jedoch darauf ausgelegt, nur die erste Instanz des Nachschlagewerts zu finden.
Aber was ist, wenn Sie den zweiten, dritten, vierten oder N-ten Wert nachschlagen möchten.
Nun, es ist mit ein bisschen Mehrarbeit machbar.
In diesem Tutorial zeige ich Ihnen verschiedene Möglichkeiten (mit Beispielen), wie Sie den zweiten oder den N-ten Wert in Excel nachschlagen können.
Suchen Sie den zweiten, dritten oder n-ten Wert in Excel
In diesem Tutorial werde ich zwei Möglichkeiten behandeln, den zweiten oder den N-ten Wert in Excel nachzuschlagen:
- Verwenden einer Hilfsspalte.
- Array-Formeln verwenden.
Lass uns loslegen und direkt eintauchen.
Verwenden der Hilfsspalte
Angenommen, Sie sind Schulungskoordinator in einer Organisation und verfügen über einen Datensatz wie unten gezeigt. Sie möchten alle Schulungen vor dem Namen eines Mitarbeiters auflisten.
Im obigen Datensatz wurden die Mitarbeiter zu verschiedenen Microsoft Office-Tools (Excel, PowerPoint und Word) geschult.
Jetzt können Sie mit der SVERWEIS-Funktion oder der INDEX/MATCH-Kombination die absolvierte Schulung eines Mitarbeiters finden. Es wird jedoch nur die erste übereinstimmende Instanz zurückgegeben.
Im Fall von John zum Beispiel hat er alle drei Trainings absolviert, aber wenn ich seinen Namen mit SVERWEIS oder INDEX/MATCH nachschlage, wird immer 'Excel' zurückgegeben, was das erste Training für seinen Namen in der Liste ist .
Um dies zu erreichen, können wir eine Hilfsspalte verwenden und darin eindeutige Lookup-Werte erstellen.
Hier sind die Schritte:
- Fügen Sie eine Spalte vor der Spalte ein, die das Training auflistet.
- Geben Sie in Zelle B2 die folgende Formel ein:
=A2&ZÄHLENWENN($A$2:$A2,A2)
- Geben Sie in Zelle F2 die folgende Formel ein und kopieren Sie sie für alle anderen Zellen:
=IFNA(SVERWEIS($E2&SPALTEN($F$1:F1),$B$2:$C$14,2,0),"")
Die obige Formel würde die Schulung für jeden Mitarbeiter in der Reihenfolge zurückgeben, in der sie in der Liste angezeigt wird. Falls für einen Mitarbeiter keine Schulung aufgeführt ist, wird ein Leerzeichen zurückgegeben.
Wie funktioniert diese Formel?
Die ZÄHLENWENN-Formel in der Hilfsspalte macht den Namen jedes Mitarbeiters einzigartig, indem sie ihm eine Zahl hinzufügt. Beispielsweise wird die erste Instanz von John zu John1, die zweite Instanz zu John2 und so weiter.
Die SVERWEIS-Formel verwendet nun diese eindeutigen Mitarbeiternamen, um die passende Schulung zu finden.
Beachten Sie, dass $E2&COLUMNS($F$1:F1) der Nachschlagewert in der Formel ist. Dies würde dem Mitarbeiternamen basierend auf der Spaltennummer eine Nummer hinzufügen. Wenn diese Formel beispielsweise in Zelle F2 verwendet wird, wird der Nachschlagewert „John1“. In Zelle G2 wird es zu „John2“ und so weiter.
Array-Formel verwenden
Wenn Sie das ursprüngliche Dataset nicht durch Hinzufügen von Hilfsspalten ändern möchten, können Sie auch eine Arrayformel verwenden, um den zweiten, dritten oder n-ten Wert nachzuschlagen.
Angenommen, Sie haben denselben Datensatz wie unten gezeigt:
Hier ist die Formel, die den richtigen Lookup-Wert zurückgibt:
=IFERROR(INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,""),COLUMNS($E$1 :E1))),"")
Kopieren Sie diese Formel und fügen Sie sie in Zelle E2 ein.
Beachten Sie, dass dies eine Matrixformel ist und Sie Strg + Umschalt + Eingabetaste verwenden müssen (halten Sie die Strg- und Umschalttaste gedrückt und drücken Sie die Eingabetaste), anstatt nur die Eingabetaste zu drücken.
Klicken Sie hier, um die Beispieldatei herunterzuladen.
Wie funktioniert diese Formel?
Lassen Sie uns diese Formel in Teile aufteilen und sehen, wie sie funktioniert.
$A$2:$A$14=$D2
Der obige Teil der Formel vergleicht jede Zelle in A2:A14 mit dem Wert in D2. In diesem Datensatz prüft es, ob eine Zelle den Namen „John“ enthält oder nicht.
Es gibt ein Array von TRUE oder FALSE zurück. Wenn die Zelle den Namen „John“ hat, wäre sie True, andernfalls wäre sie False.
Unten ist das Array, das Sie in diesem Beispiel erhalten würden:
{WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;WAHR;FALSCH;FALSCH}
Beachten Sie, dass an der 1., 7. und 111. Position TRUE steht, da dort der Name John im Datensatz erscheint.
WENN($A$2:$A$14=$D2,ROW($A$2:$A$14)-1””)
Die obige IF-Formel verwendet das Array von TRUE und FALSE und ersetzt TRUE durch die Position seines Vorkommens in der Liste (angegeben durch ROW($A$2:$A$14)-1) und FALSE durch „“ (Leerzeichen). Das folgende ist das resultierende Array, das Sie mit dieser IF-Formel erhalten:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Beachten Sie, dass 1, 7 und 11 die Vorkommensposition von John in der Liste sind.
KLEIN(WENN($A$2:$A$14=$D2,ROW($A$2:$A$14)-1”,”),COLUMNS($E$1:E1))
Die Funktion SMALL wählt nun die erste kleinste, zweitkleinste und drittkleinste Zahl aus diesem Array. Beachten Sie, dass es die COLUMNS-Funktion verwendet, um die Spaltennummer zu generieren. In Zelle E2 gibt die COLUMNS-Funktion 1 und die SMALL-Funktion 1 zurück. In Zelle F2 gibt die COLUMNS-Funktion 2 zurück und die SMALL-Funktion 7 zurück.
INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1”,”),COLUMNS($E$1:E1) ))
Die INDEX-Funktion gibt jetzt den Wert aus der Liste in Spalte B basierend auf der von der SMALL-Funktion zurückgegebenen Position zurück. Daher gibt es in Zelle E2 „Excel“ zurück, das erste Element in B2:B14. In Zelle F2 gibt es PowerPoint zurück, das das 7. Element in der Liste ist.
Da es Fälle gibt, in denen für einige Mitarbeiter nur ein oder zwei Schulungen durchgeführt werden, würde die INDEX-Funktion einen Fehler zurückgeben. Die Funktion IFERROR wird verwendet, um anstelle des Fehlers ein Leerzeichen zurückzugeben.
Beachten Sie, dass ich in diesen Beispielen Bereichsreferenzen verwendet habe. In praktischen Beispielen ist es jedoch von Vorteil, die Daten in eine Excel-Tabelle umzuwandeln. Durch die Konvertierung in eine Excel-Tabelle können Sie strukturierte Verweise verwenden, was die Erstellung von Formeln erleichtert. Außerdem kann eine Excel-Tabelle automatisch alle neuen Trainingselemente berücksichtigen, die der Liste hinzugefügt werden (damit Sie die Formeln nicht jedes Mal anpassen müssen).
Was tun Sie, wenn Sie den zweiten, dritten oder N-ten Wert nachschlagen müssen? Ich bin sicher, es gibt mehr Möglichkeiten, dies zu tun. Wenn Sie etwas einfacheres als das hier aufgeführte verwenden, teilen Sie es uns allen im Kommentarbereich mit.
Klicken Sie hier, um die Beispieldatei herunterzuladen.
