Wie Unique Records in Excel filtern

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

In diesem Artikel erfahren Sie, wie Sie eindeutige Datensätze in Excel filtern.

Früher in Excel 2016 und älteren Versionen haben wir komplexe Formeln verwendet, um zu verknüpfen: / Zählen, wie eindeutige Werte in Excel mit Kriterien 2 gezählt werden [eindeutige Werte aus einem Bereich extrahieren]. In der Online-Version von Excel 365 bietet MS eine neue dynamische Array-Funktion UNIQUE, die einfach die Unikate aus einer bestimmten Liste zurückgibt.

Syntax der UNIQUE-Funktion

=UNIQUE(array,[by_col],[exactly_once])

Array: Das Array, aus dem Sie eindeutige Werte extrahieren möchten:

[by_col]: Setzen Sie TRUE (1), wenn das Array horizontal ist. Standardmäßig ist es für vertikale Daten FALSE.

[genau_once]: Setzen Sie TRUE (1), wenn Sie Werte extrahieren möchten, die nur einmal im Array vorkommen. Standardmäßig ist es FALSE (0), um alle eindeutigen Werte zu extrahieren.

Lassen Sie uns einige Beispiele geben, um zu verstehen, wie diese EINZIGARTIGE Funktion funktioniert.

Beispiel für eine UNIQUE-Funktion Beispiel 1: Extrahieren Sie mit der UNIQUE-Funktion von Excel 365 eindeutige Zahlen aus der Liste.

Hier habe ich Beispieldaten. Im Bereich A2: A11 habe ich einige Zahlen, die doppelte Werte enthalten. Ich möchte eindeutige Werte aus diesem Bereich erhalten. Ich werde einfach die EINZIGARTIGE Funktion von Excel 365 verwenden.

=UNIQUE(A2:A11)

Diese Formel gibt einfach alle eindeutigen Werte aus dem Bereich zurück.

Sobald Sie die Eingabetaste drücken, wird das resultierende Array mit eindeutigen Werten in den Zellen angezeigt. Dies ist ein dynamisches Array, das nur für Excel 365 online verfügbar ist.

Beispiel 2: Extrahieren Sie Werte, die nur einmal im Bereich vorkommen.

Im obigen Beispiel haben wir eindeutige Werte aus dem Bereich erhalten. Wenn ein Wert einmal, zweimal oder mehrmals vorkam, erhielten wir nur eine Haltung davon. Wenn wir jedoch Werte extrahieren möchten, die nur einmal im Bereich vorkommen (eindeutige Werte im Bereich), lautet die Formel:

=UNIQUE(A2:A11,1)

Hier setzen wir die Variable exact_once auf TRUE. Dies gibt den Wert zurück, der im Bereich / Array selbst eindeutig ist.

Beispiel 3: Suchen eindeutiger Werte in horizontalen Daten

In den obigen zwei Beispielen war das Array vertikal und hatte numerische Werte. Was ist, wenn wir nicht numerische Werte und horizontale Daten haben? Nun, keine Sorge. Die Excel UNIQUE-Funktion funktioniert für jede Art von Wert, sei es numerisch, Text, Boolescher Wert usw. Und wir können sie auch für die Einrichtung horizontaler Daten verwenden.

Hier habe ich einige Namen in Zellen benachbarter Spalten. Ich möchte alle Namen ohne Wiederholung in den Zellen unten erhalten.

Die Formel lautet:

=UNIQUE(C2:K2,,1)

Es gibt die Namen zurück, nachdem alle Duplikate in Spalten aus dem Array entfernt wurden.

Für Excel 2010 – 2016

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 machen wir es… Generische Formel Extrahieren Sie eindeutige Werte in Excel

\{=INDEX(ref_list,MATCH(0,COUNTIF*(expanding_ouput_range,ref_list),0))}

Ref_list: Die Liste, aus der Sie eindeutige Werte extrahieren möchten. Expanding_ouput_range: Dies 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 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 $ 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.

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))}

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-passender-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 konvertiert.

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)),““)}

Hinweis: Setzen Sie diese geschweiften Klammern nicht manuell ein. Verwenden Sie Strg + Umschalt + Eingabetaste, um das Ergebnis zu erhalten.

Hoffe, dieser Artikel über das Filtern eindeutiger Datensätze in Excel in Excel ist erklärend. Weitere Artikel zum Extrahieren von Werten und verwandten Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden gerne von Ihnen hören, uns mitteilen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns unter [email protected].

Beliebte Artikel:

link: / tips-if-condition-in-excel [Verwendung der IF-Funktion in Excel]: Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung TRUE ist, oder gibt einen anderen bestimmten Wert zurück, wenn FALSE .

link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Verwendung der VLOOKUP-Funktion in Excel]: Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen verwendet wird und Blätter.

link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel]: Dies ist eine weitere wichtige Dashboard-Funktion. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.

link: / tips-countif-in-microsoft-excel [Verwendung der COUNTIF-Funktion in Excel]: Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.