In diesem Artikel wird beschrieben, wie Sie alle übereinstimmenden Werte aus einer Tabelle abrufen und in verschiedenen Zellen abrufen. Dies ist vergleichbar mit der Suche nach mehreren Werten.
Generische Formel
{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(erweiternde Bereiche))), „--Listenende--“)}
Zu viele Funktionen und Variablen!!!. Sehen wir uns an, was diese Variablen sind.
Namen: Dies ist die Namensliste.
Gruppen: Die Liste der Gruppe, zu der auch diese Namen gehören.
Gruppenname: die Referenz des Gruppennamens.
Erweiterungsbereiche: Dies ist ein sich erweiternder Bereich, der verwendet wird, um beim Kopieren nach rechts eine steigende Zahl zu erhalten.
Beispiel: Extrahieren Sie die Namen der Mitarbeiter in verschiedenen Spalten entsprechend ihrer Firma.
Angenommen, Sie haben eine Tabelle mit Mitarbeitern, die nach ihrem Unternehmen gruppiert ist. Die erste Spalte enthält Mitarbeiternamen und die zweite Spalte enthält den Namen des Unternehmens.
Jetzt müssen wir die Namen jedes Mitarbeiters in verschiedenen Spalten entsprechend seinem Unternehmen abrufen. Mit anderen Worten, wir müssen die Gruppierung aufheben.
Hier habe ich A2:A10 als Mitarbeiter und B2:B10 als Unternehmen bezeichnet, damit die Formel leicht zu lesen ist.
Schreiben Sie diese Matrixformel in F2. Verwenden Sie STRG+UMSCHALT+EINGABETASTE, um diese Formel einzugeben.
{=INDEX(Mitarbeiter,SMALL(IF(Company=$E2,ROW(Mitarbeiter)-MIN(ROW(Mitarbeiter))+1),COLUMNS($E$1:E1))), „--Liste endet--“ )}
Kopieren Sie diese Formel in alle Zellen. Es extrahiert jeden einzelnen Namen in den verschiedenen Spalten entsprechend ihrer Gruppe.
Wie Sie im Bild oben sehen können, ist jeder Mitarbeiter in verschiedene Zellen unterteilt.
Also, wie funktioniert diese Formel?
Um die Formel zu verstehen, schauen wir uns die Formel in G2 an
Was ist =IFERROR(INDEX(Angestellter,KLEIN(WENN(Unternehmen=$E3,REIHE(Angestellter)-MIN(ZEILE(Angestellter))+1),SPALTEN($E$1:F2))),"--Listenende--")
Die Mechanik ist einfach und fast die gleiche wie bei mehreren SVERWEIS-Formeln. Der Trick besteht darin, die Indexnummer jedes Mitarbeiters aus verschiedenen Gruppen zu erhalten und an die INDEX-Formel weiterzugeben. Dies geschieht durch diesen Teil der Formel.
WENN(Gesellschaft=$E3,REIHE(Angestellter)-MIN(ZEILE(Angestellter))+1):
Dieser Teil gibt ein Array von Indizes und false für den Firmennamen in $E3 zurück, der „Rankwatch“ enthält.
{FALSCH;2;FALSCH;4;FALSCH;FALSCH;7;FALSCH;9}.
Wie? Lass es uns von innen abreißen.
Hier gleichen wir den Firmennamen in $E3 mit jedem Wert in . ab Firmenbereich (Firma=$E3).
Dies gibt ein Array von true und false zurück.{FALSE;WAHR;FALSCH;WAHR;FALSCH;FALSCH;WAHR;FALSCH;WAHR}.
Jetzt führt die IF-Funktion ihre TRUE-Anweisungen für TRUE aus, die ROW(Employee)-MIN(ROW(Angestellter))+1. Dieser Teil gibt zurück Dieser Teil gibt ein Array von Indizes zurück, beginnend von 1 bis zur Anzahl von Mitarbeiter {1;2;3;4;5;6;7;8;9}. Die if-Funktion ermittelt nur Werte für WAHR, was uns wiederum {FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;9} ergibt.
Die aktuelle Formel wird vereinfacht zu
=IFERROR(INDEX(Mitarbeiter,KLEIN( {FALSCH;2;FALSCH;4;FALSCH;FALSCH;7;FALSCH;9},SPALTEN($E$1:F2))),"--Liste endet--"). Wie wir wissen, geben kleine Funktionen den n-kleinsten Wert aus einem Array zurück. SPALTEN($E$1:F2) dies gibt 2 zurück. Die SMALL-Funktion gibt den zweitkleinsten Wert aus dem obigen Array zurück, der 4 ist.
Jetzt wird die Formel vereinfacht =IFERROR(INDEX(Mitarbeiter,4),"--Liste endet--"). Jetzt gibt die INDEX-Funktion einfach den vierten Namen von . zurück Angestellter Array, das uns „Sam”.
Also ja, so extrahieren Sie Namen aus Gruppen in verschiedenen Spalten mit der INDEX-, SMALL-, ROW-, COLUMNS- und IF-Funktion. Wenn Sie Zweifel an dieser Funktion haben oder sie bei Ihnen nicht funktioniert, lassen Sie es mich im Kommentarbereich unten wissen.
Download-Datei:
In Verbindung stehende Artikel:
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