Generische Formel
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Zusammenfassung
Um nur eindeutige Werte aus einer Liste oder Spalte zu extrahieren, können Sie eine Array-Formel verwenden, die auf INDEX, MATCH und COUNTIF basiert. In dem gezeigten Beispiel lautet die heruntergeschriebene Formel in D5:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
Dabei ist "Liste" der benannte Bereich B5: B11.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Der Kern dieser Formel ist eine grundlegende Suche mit INDEX:
=INDEX(list,row)
Mit anderen Worten, geben Sie INDEX die Liste und eine Zeilennummer, und INDEX ruft einen Wert ab, der der eindeutigen Liste hinzugefügt werden soll.
Die harte Arbeit besteht darin, die ROW-Nummer zu ermitteln, um INDEX zu erhalten, sodass wir nur eindeutige Werte erhalten. Dies geschieht mit MATCH und COUNTIF, und der Haupttrick ist hier:
COUNTIF($D$4:D4,list)
Hier zählt COUNTIF, wie oft Elemente, die bereits in der eindeutigen Liste enthalten sind, in der Hauptliste angezeigt werden, wobei eine erweiterte Referenz für den Bereich $ D $ 4: D4 verwendet wird.
Eine expandierende Referenz ist auf der einen Seite absolut, auf der anderen relativ. In diesem Fall wird die Referenz beim Kopieren der Formel erweitert, um weitere Zeilen in die eindeutige Liste aufzunehmen.
Beachten Sie, dass die Referenz in D4, eine Zeile über dem ersten eindeutigen Eintrag, in der eindeutigen Liste beginnt. Dies ist beabsichtigt - wir möchten Elemente * bereits * in der eindeutigen Liste zählen und können die aktuelle Zelle nicht einschließen, ohne einen Zirkelverweis zu erstellen. Also beginnen wir in der obigen Zeile.
Wichtig: Stellen Sie sicher, dass die Überschrift für die eindeutige Liste nicht in der Hauptliste angezeigt wird.
Für die Kriterien in COUNTIF verwenden wir die Masterliste selbst. Wenn mehrere Kriterien angegeben sind, gibt COUNTIF mehrere Ergebnisse in einem Array zurück. In jeder neuen Zeile haben wir ein anderes Array wie das folgende:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Hinweis: COUNTIF behandelt mehrere Kriterien mit einer "ODER" -Beziehung (dh COUNTIF (Bereich, ("rot", "blau", "grün")) zählt rot, blau oder grün.
Jetzt haben wir die Arrays, die wir brauchen, um Positionen (Zeilennummern) zu finden. Dazu verwenden wir MATCH, das auf exakte Übereinstimmung eingestellt ist, um Nullwerte zu finden. Wenn wir die von COUNTIF oben erstellten Arrays in MATCH einfügen, erhalten wir Folgendes:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH sucht nach Elementen, indem nach einer Anzahl von Null gesucht wird (dh nach Elementen gesucht wird, die noch nicht in der eindeutigen Liste aufgeführt sind). Dies funktioniert, da MATCH immer die erste Übereinstimmung zurückgibt, wenn Duplikate vorhanden sind.
Schließlich werden die Positionen als Zeilennummern in INDEX eingegeben, und INDEX gibt den Namen an dieser Position zurück.
Nicht-Array-Version mit LOOKUP
Mit der flexiblen LOOKUP-Funktion können Sie eine Nicht-Array-Formel erstellen, um eindeutige Elemente zu extrahieren:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Die Formelkonstruktion ähnelt der obigen INDEX MATCH-Formel, aber LOOKUP kann die Array-Operation nativ verarbeiten.
- COUNTIF gibt die Anzahl der Werte aus "Liste" im erweiterten Bereich $ D $ 4: D4 zurück
- Beim Vergleich mit Null wird ein Array von TRUE- und FALSE-Werten erstellt
- Die Zahl 1 wird durch das Array geteilt, wodurch ein Array mit 1s und # DIV / 0-Fehlern erstellt wird
- Dieses Array wird zum lookup_vector in LOOKUP
- Der Lookup-Wert von 2 ist größer als alle Werte im lookup_vector
- LOOKUP entspricht dem letzten fehlerfreien Wert im Lookup-Array
- LOOKUP gibt den entsprechenden Wert in result_vector zurück, den benannten Bereich "list".
Extrahieren Sie Elemente, die nur einmal angezeigt werden
Die obige LOOKUP-Formel lässt sich leicht mit boolescher Logik erweitern. Um eine Liste eindeutiger Elemente zu extrahieren, die nur einmal in den Quelldaten vorkommen, können Sie eine Formel wie die folgende verwenden:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Die einzige Ergänzung ist der zweite COUNTIF-Ausdruck:
COUNTIF(list,list)=1
Hier gibt COUNTIF ein Array von Elementzählungen wie folgt zurück:
(2;2;2;2;2;1;2)
die mit 1 verglichen werden, was zu einem Array von TRUE / FALSE-Werten führt:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Diese fungieren als "Filter", um die Ausgabe auf Elemente zu beschränken, die nur einmal in den Quelldaten vorkommen.
