Excel

Extrahieren Sie einzigartige Elemente aus einer Liste

Extract Unique Items From List

Excel-Formel: Extrahieren Sie eindeutige Elemente aus einer ListeAllgemeine Formel |_+_| Zusammenfassung

Um nur eindeutige Werte aus einer Liste oder Spalte zu extrahieren, können Sie eine Matrixformel verwenden, die auf INDEX, MATCH und COUNTIF basiert. Im gezeigten Beispiel lautet die nach unten kopierte Formel in D5:





{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}

wo 'Liste' ist benannter Bereich B5: B11.

Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.





wie man nicht gleich in excel schreibt
Erläuterung

Der Kern dieser Formel ist eine grundlegende Suche mit INDEX:

 
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}

Mit anderen Worten, geben Sie INDEX die Liste und eine Zeilennummer, und INDEX ruft einen Wert ab, der der eindeutigen Liste hinzugefügt wird.



Die harte Arbeit besteht darin, die ROW-Nummer herauszufinden, um INDEX zu geben, damit wir nur eindeutige Werte erhalten. Dies geschieht mit MATCH und COUNTIF, und der Haupttrick ist hier:

 
= INDEX (list,row)

Hier, COUNTIF zählt, wie oft Elemente, die bereits in der eindeutigen Liste enthalten sind, in der Hauptliste erscheinen, indem ein Ausbau Referenz für das Sortiment,$D:D4.

Eine expandierende Referenz ist aauf der einen Seite absolut, auf der anderen relativ. Wenn die Formel in diesem Fall nach unten kopiert wird, wird die Referenz erweitert, um mehr Zeilen in die eindeutige Liste aufzunehmen.

Beachten Sie dasHinweisbeginnt in D4,eine Reihe Oben der erste eindeutige Eintrag in der eindeutigen Liste. Dies ist beabsichtigt – wir möchten Elemente *bereits* in der eindeutigen Liste zählen, und wir können die aktuelle Zelle nicht einschließen, ohne einen Zirkelbezug zu erstellen. Wir beginnen also mit der Zeile oben.

Wichtig: Stellen Sie sicher, dass die Überschrift für die eindeutige Liste nicht in der Masterliste erscheint.

Für die Kriterien in COUNTIF verwenden wir die Masterliste selbst. Wenn mehrere Kriterien angegeben werden, gibt COUNTIF mehrere Ergebnisse in einem . zurück Array . In jeder neuen Zeile haben wir ein anderes Array wie folgt:

 
 COUNTIF ($D:D4,list)

Hinweis: ZÄHLENWENN verarbeitet mehrere Kriterien mit einer 'ODER'-Beziehung (d. h. ZÄHLENWENN (Bereich, {'rot','blau', 'grün'}) zählt rot, blau oder grün.

wie man Abweichungen in Excel findet

Jetzt haben wir die Arrays, die wir brauchen, um Positionen (Zeilennummern) zu finden. Dazu verwenden wir MATCH, das für genaue Übereinstimmung eingerichtet ist, um Nullwerte zu finden. Wenn wir die von COUNTIF oben erstellten Arrays in MATCH einfügen, erhalten wir Folgendes:

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

MATCH findet Elemente, indem es nach einer Zählung von Null sucht (d. h. nach Elementen sucht, die noch nicht in der eindeutigen Liste erscheinen). Das funktioniert, weil MATCH bei Duplikaten immer die erste Übereinstimmung zurückgibt.

Schließlich werden die Positionen als Zeilennummern in INDEX eingespeist, und INDEX gibt den Namen an dieser Position zurück.

Nicht-Array-Version mit LOOKUP

Sie können eine Nicht-Array-Formel erstellen, um mit der flexiblen LOOKUP-Funktion eindeutige Elemente zu extrahieren:

 
 MATCH (0,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},0) // 6 (Aya)

Die Formelkonstruktion ähnelt der obigen INDEX MATCH-Formel, aber LOOKUP kann die Array-Operation nativ verarbeiten.

  • ZÄHLENWENN gibt die Anzahl der einzelnen Werte aus 'list' im Erweiterung des Sortiments $D:D4
  • Ein Vergleich mit Null erzeugt ein Array von TRUE- und FALSE-Werten
  • Die Zahl 1 wird durch das Array geteilt, wodurch ein Array von 1s und #DIV/0-Fehlern entsteht
  • Dieses Array wird zum lookup_vector in LOOKUP
  • Der Lookup-Wert von 2 ist größer als alle Werte im lookup_vector
  • LOOKUP stimmt mit dem letzten nicht fehlerfreien Wert im Lookup-Array überein
  • LOOKUP gibt den entsprechenden Wert in result_vector zurück, den benannten Bereich 'list'

Extrahieren Sie Elemente, die nur einmal vorkommen

Die obige LOOKUP-Formel lässt sich einfach um erweitern Boolesche Logik . Um eine Liste eindeutiger Elemente zu extrahieren, die nur einmal in den Quelldaten vorkommen, können Sie eine Formel wie diese verwenden:

 
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)

Die einzige Ergänzung ist der zweite COUNTIF-Ausdruck:

Verwendung der Indexformel in Excel
 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Hier gibt COUNTIF ein Array von Elementanzahlen wie folgt zurück:

 
 COUNTIF (list,list)=1

die mit 1 verglichen werden, was zu einem Array von TRUE/FALSE-Werten führt:

 
{2222212}

die als 'Filter' fungieren, um die Ausgabe auf Elemente zu beschränken, die nur einmal in den Quelldaten vorkommen.

In Excel 365 , das EINZIGARTIGE Funktion ist der beste Weg, um eindeutige Werte zu extrahieren.

Autor Dave Bruns


^