Ok, in meinen frühen Tagen der Datenanalyse musste ich die eindeutigen Elemente in Excel automatisch aus einer Liste entfernen, anstatt jedes Mal doppelte Elemente zu entfernen. Und ich habe einen Weg gefunden, es zu tun. Danach war mein Excel-Dashboard noch dynamischer als zuvor. Mal sehen, wie wir das machen können …​

image

Um eine Liste eindeutiger Werte aus einer Liste zu extrahieren, verwenden wir link: / lookup-formulas-excel-index-function [INDEX], link: / lookup-formulas-excel-match-function [MATCH]

und link: / tips-countif-in-microsoft-excel [COUNTIF].

Ich werde auch link: / logische Formeln-excel-iferror-Funktion [IFERROR] verwenden, nur um saubere Ergebnisse zu erzielen, es ist optional.

Und ja, es wird eine Array-Formel sein… Also lasst es uns erledigen… ===== Generische Formel zum Extrahieren eindeutiger Werte in Excel

{=INDEX(ref_list,MATCH(0,COUNTIF(expanding_output_range,ref_list),0))}

Ref_list: Die Liste, aus der Sie eindeutige Werte extrahieren möchten link: / excel-range-name-expanding-references-in-excel [Expanding_output_range:]

Das ist sehr wichtig. Dies ist der Bereich, in dem Sie Ihre extrahierte Liste sehen möchten. Dieser Bereich muss eine eindeutige Überschrift haben, die nicht Teil der Liste ist, und Ihre Formel befindet sich unter der Überschrift (wenn die Überschrift in E1 ist, befindet sich die Formel in E2). Jetzt erweitern bedeutet, wenn Sie Ihre Formel nach unten ziehen, sollte sie über den Ausgabebereich erweitert werden. Dazu müssen Sie die Überschrift als $ E $ 1: E1 angeben (Meine Überschrift befindet sich in E1). Wenn ich es nach unten ziehe, wird es erweitert. In E2 ist es $ E $ 1: E1. In E3 ist es $ E $ 1: E2. In E2 ist es $ E $ 1: E3 und so weiter.

Nun sehen wir uns ein Beispiel an. Es wird es klar machen.

Extrahieren eindeutiger Werte Excel-Beispiel Hier habe ich also diese Kundenliste in Spalte A im Bereich A2: A16.

Jetzt in Spalte E möchte ich eindeutige Werte nur von Kunden erhalten. Jetzt kann auch dieser Bereich A2: A16 erhöht werden, daher möchte ich, dass meine Formel bei jeder Erhöhung der Liste jeden neuen Kundennamen aus der Liste abruft.

image

Ok, um jetzt eindeutige Werte aus Spalte A abzurufen, schreiben Sie diese Formel in Zelle E2 und drücken Sie STRG + UMSCHALT + EINGABETASTE, um daraus eine Array-Formel zu machen.

{=INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}

image

A $ 2: A16: Ich gehe davon aus, dass diese Liste erweitert wird und möglicherweise neue eindeutige Werte enthält, die ich extrahieren möchte. Deshalb habe ich es von unten offen gelassen, indem ich nicht absolut auf A16 Bezug genommen habe. Es kann erweitert werden, wenn Sie die folgende Formel kopieren.

Wir kennen also link: / Lookup-Formeln-Abrufen-übereinstimmender-Werte-aus-nicht-benachbarter-Liste [wie INDEX und MATCH funktionieren]. Der Hauptteil hier ist:

COUNTIF ($ E $ 1: E1, $ A $ 2: A16): Diese Formel gibt ein Array von Einsen und Nullen zurück. Immer wenn ein Wert im Bereich $ E $ 1: E1 in der Kriterienliste $ A $ 2: A16 gefunden wird, wird der Wert an seiner Position im Bereich $ A $ 2: A16 in 1 umgewandelt.

Mit der MATCH-Funktion suchen wir nun nach Werten 0. Match gibt die Position der ersten 0 zurück, die in dem von der COUNTIF-Funktion zurückgegebenen Array gefunden wurde. Dann prüft INDEX A $ 2: A16, um den Wert zurückzugeben, der bei dem von der MATCH-Funktion zurückgegebenen Index gefunden wurde.

Es ist vielleicht ein bisschen schwer zu verstehen, aber es funktioniert. Die 0 am Ende der Liste zeigt an, dass keine eindeutigen Werte mehr vorhanden sind. Wenn Sie diese 0 am Ende nicht sehen, sollten Sie die Formel in die folgenden Zellen kopieren.

Um nun #NA zu vermeiden, können Sie die IFERROR-Funktion von Excel verwenden.

{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:$E1,$A$2:A16),0)),"")}

Sie können diese Formel also verwenden, um eindeutige Werte aus einer Liste abzurufen. In Excel 2019 mit Office 365-Abonnement bietet Microsoft eine Funktion namens UNIQUE an. Es nimmt einfach einen Bereich als Argument und gibt ein Array eindeutiger Werte zurück. Es ist in Microsoft Excel 2016 nicht verfügbar.

Datei herunterladen: