Wie Alle Spiele in verschiedenen Spalten Get
In diesem Artikel wird erläutert, wie Sie alle Werte aus einer Tabelle abgleichen und in verschiedenen Zellen abrufen können. Dies ähnelt dem Nachschlagen mehrerer Werte.
Generische Formel
{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(expanding ranges))), “--List Ends--”)}
Zu viele Funktionen und Variablen !!!. Mal sehen, was diese Variablen sind.
Namen: Dies ist die Liste der Namen.
Gruppen: Die Liste der Gruppen, zu denen auch diese Namen gehören.
Gruppenname: Die Referenz des Gruppennamens.
link: / excel-range-name-expanding-references-in-excel [Erweiterungsbereiche]: Dies ist ein expandierender Bereich, der verwendet wird, um beim Kopieren nach rechts eine zunehmende Anzahl 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 den Namen jedes Mitarbeiters je nach Unternehmen in verschiedenen Spalten abrufen. Mit anderen Worten, wir müssen die Gruppierung aufheben.
Hier habe ich A2: A10 als Mitarbeiter und B2: B10 als Unternehmen benannt, damit die Formel leicht zu lesen ist.
Schreiben Sie diese Array-Formel in F2. Verwenden Sie STRG + UMSCHALT + EINGABETASTE, um diese Formel einzugeben.
{=INDEX(Employee,SMALL(IF(Company=$E2,ROW(Employee)-MIN(ROW(Employee))+1),COLUMNS($E$1:E1))), “--List Ends--”)}
Kopieren Sie diese Formel in alle Zellen. Es wird jeder einzelne Name in den verschiedenen Spalten entsprechend seiner Gruppe extrahiert.
Wie Sie im obigen Bild sehen können, ist jeder Mitarbeiter in verschiedene Zellen unterteilt.
Wie funktioniert diese Formel?
Um die Formel zu verstehen, schauen wir uns die Formel in G2 an, die = IFERROR (INDEX (Mitarbeiter, SMALL (IF (Firma = $ E3, ROW (Mitarbeiter)) -MIN (ROW (Mitarbeiter)) + 1), COLUMNS ($ E) lautet $ 1: F2))), „- List Ends–„)
Die Mechanik ist einfach und fast identisch mit link: / lookup-formulas-vlookup-multiple-values [multiple VLOOKUP-Formel]. Der Trick besteht darin, die Indexnummer jedes Mitarbeiters aus verschiedenen Gruppen abzurufen und an die INDEX-Formel zu übergeben. Dies geschieht durch diesen Teil der Formel.
Dieser Teil gibt ein Array von Indizes und false für den Firmennamen in $ E3 zurück, das „Rankwatch“ enthält.
\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Wie? Lassen Sie es uns von innen abreißen.
Hier stimmen wir den Firmennamen in $ E3 mit jedem Wert im Firmenbereich ab (Firma = $ E3).
Dies gibt ein Array von wahr und falsch zurück. \ {FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}.
Jetzt führt die IF-Funktion ihre TRUE-Anweisungen für TRUE aus, nämlich link: / lookup-and-reference-excel-row-function [ROW] (Mitarbeiter) -MIN (`link: / lookup-and-reference-excel-) Zeilenfunktion [ROW] `(Mitarbeiter)) + 1.
Dieser Teil gibt diesen Teil ein Array von Indizes zurück, das von 1 bis zur Anzahl der Mitarbeiter \ {1; 2; 3; 4; 5; 6; 7; 8; 9} beginnt. Die if-Funktion zeigt nur Werte für TRUE an, was wiederum \ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9} ergibt.
Die aktuelle Formel wird vereinfacht zu = IFERROR (INDEX (Mitarbeiter, KLEIN (\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}, SPALTEN ($ E $ 1: F2))), „- Listenenden– „). Wie wir wissen, gibt eine kleine Funktion den n-ten kleinsten Wert eines Arrays zurück. SPALTEN ($ E $ 1: F2) Dies gibt 2 zurück. Die SMALL-Funktion gibt den zweitkleinsten Wert aus dem obigen Array zurück, nämlich 4.
Jetzt ist die Formel vereinfacht = IFERROR (INDEX (Mitarbeiter, 4), „- List Ends–„). Jetzt gibt die INDEX-Funktion einfach den vierten Namen aus dem Mitarbeiterarray zurück, der uns „Sam“ gibt.
Also ja, so extrahieren Sie Namen aus Gruppen in verschiedenen Spalten mit link: / excel-range-name-expanding-reference-in-excel [INDEX], link: / statistische-formeln-excel-small-function [SMALL ] `, link: / lookup-and-reference-excel-row-function [ROW] `, COLUMNS und IF-Funktion. Wenn Sie Zweifel an dieser Funktion haben oder wenn sie für Sie nicht funktioniert, lassen Sie mich den Kommentarbereich unten wissen.