Okay, in meinen frühen Tagen der Datenanalyse musste ich die eindeutigen Elemente in Excel automatisch aus einer Liste abrufen, anstatt jedes Mal Duplikate zu entfernen. Und ich habe einen Weg gefunden, es zu tun. Danach war mein Excel-Dashboard noch dynamischer als zuvor. Mal sehen, wie wir es schaffen…
Um eine Liste eindeutiger Werte aus einer Liste zu extrahieren, verwenden wir INDEX, MATCH und COUNTIF. Ich werde auch IFERROR verwenden, nur um saubere Ergebnisse zu erzielen, es ist optional.
Und ja, es wird eine Array-Formel sein… Also lass es uns erledigen…
Generische Formel zum Extrahieren eindeutiger Werte in Excel
{=INDEX(ref_list,MATCH(0,COUNTIF(expanding_output_range,ref_list),0))}
Ref_liste: Die Liste, aus der Sie eindeutige Werte extrahieren möchten
Expanding_output_range: Das ist jetzt sehr wichtig. Dies ist der Bereich, in dem Sie Ihre extrahierte Liste sehen möchten. Dieser Bereich muss eine eindeutige Überschrift haben, die nicht in der Liste enthalten ist, und Ihre Formel befindet sich unter der Überschrift (wenn die Überschrift in E1 ist, ist die Formel in E2).
Erweitern bedeutet jetzt, wenn Sie Ihre Formel nach unten ziehen, sollte sie über den Ausgabebereich erweitert werden. Um dies zu tun, müssen Sie die Referenz der Überschrift angeben als $E$1:E1 (Meine Überschrift ist in E1). Wenn ich es nach unten ziehe, wird es erweitert. In E2 wird es sein $E$1:E1. Auf der E3 wird es sein $E$1:E2. In E2 wird es sein $E$1:E3 und so weiter.
Sehen wir uns nun ein Beispiel an. Es wird klar.
Extrahieren eindeutiger Werte Excel-Beispiel
Hier habe ich also diese Kundenliste in der Spalte EIN im Bereich A2: A16. Jetzt möchte ich in Spalte E eindeutige Werte nur von Kunden erhalten. Jetzt kann dieser Bereich A2:A16 auch größer werden, also möchte ich, dass meine Formel jeden neuen Kundennamen aus der Liste holt, wenn die Liste größer wird.
Ok, um jetzt eindeutige Werte aus Spalte A abzurufen, schreiben Sie diese Formel in Zelle E2, und schlagen STRG+UMSCHALT+EINGABETASTE um es zu einer Array-Formel zu machen.
{=INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:E1,$A$2:A16),0))}
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. Dadurch kann es erweitert werden, wenn Sie die folgende Formel kopieren.
Wir wissen also, wie die INDEX- und MATCH-Funktion funktioniert. Der Hauptteil hier ist:
ZÄHLENWENN($E$1:E1,$A$2:A16): Diese Formel gibt ein Array von 1s und 0s zurück. Immer wenn ein Wert im Bereich $E$1:E1 findet sich in der Kriterienliste $A$2:A16, wird der Wert an seiner Position im Bereich $A$2:A16 in 1 umgewandelt.
Jetzt suchen wir mit der Funktion MATCH nach Werten 0. Match gibt die Position der ersten 0 zurück, die im von der COUNTIF-Funktion zurückgegebenen Array gefunden wird. Dann wird INDEX nachsehen A$2:A16 um den Wert zurückzugeben, der am Index gefunden wurde, der von der MATCH-Funktion zurückgegeben 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 unteren Zellen kopieren.
Jetzt zu vermeiden #N / A in können Sie die IFERROR-Funktion von Excel verwenden.
{=IFERROR(INDEX($A$2:A16,MATCH(0,COUNTIF($E$1:$E1,$A$2:A16),0)),"")}
Also ja, Sie können diese Formel verwenden, um eindeutige Werte aus einer Liste zu erhalten. In Excel 2019 mit dem Abonnement Office 365 bietet Microsoft eine Funktion namens UNIQUE. Es nimmt einfach einen Bereich als Argument und gibt ein Array mit eindeutigen Werten zurück. Es ist nicht in Microsoft Excel 2016 einmaliger Kauf verfügbar.
Download-Datei:
Excel-Formel zum Extrahieren eindeutiger Werte aus einer ListeExcel-Formel zum Extrahieren eindeutiger Werte aus einer Liste
So zählen Sie eindeutige Werte in Excel
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität
So verwenden Sie die SVERWEIS-Funktion in Excel
So verwenden Sie die ZÄHLENWENN-Funktion in Excel
So verwenden Sie die SUMIF-Funktion in Excel