Note: The other languages of the website are Google-translated. Back to English

Wie extrahiere ich dynamisch eine Liste eindeutiger Werte aus einem Spaltenbereich in Excel?

Für einen Spaltenbereich, dessen Werte sich regelmäßig ändern, und Sie müssen immer alle eindeutigen Werte aus dem Bereich abrufen, unabhängig davon, wie er sich geändert hat. Wie erstelle ich eine dynamische Liste eindeutiger Werte? Dieser Artikel zeigt Ihnen, wie Sie damit umgehen.

Extrahieren Sie dynamisch eine Liste eindeutiger Werte aus einem Spaltenbereich mit Formel
Extrahieren Sie dynamisch eine Liste eindeutiger Werte aus einem Spaltenbereich mit VBA-Code


Extrahieren Sie dynamisch eine Liste eindeutiger Werte aus einem Spaltenbereich mit Formel

Wie im folgenden Screenshot gezeigt, müssen Sie dynamisch eine Liste eindeutiger Werte aus dem Bereich B2: B9 extrahieren. Bitte versuchen Sie die folgende Array-Formel.

1. Wählen Sie eine leere Zelle wie D2 aus, geben Sie die folgende Formel ein und drücken Sie die Taste Ctrl + Verschiebung + Weiter Tasten gleichzeitig. (B2: B9 sind die Spaltendaten, aus denen Sie die eindeutigen Werte extrahieren möchten. D1 ist die obige Zelle, in der sich Ihre Formel befindet.)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Wählen Sie weiterhin Zelle D2 aus und ziehen Sie den Füllgriff nach unten, um alle eindeutigen Werte aus dem angegebenen Bereich zu erhalten.

Jetzt werden alle eindeutigen Werte im Spaltenbereich B2: B9 extrahiert. Wenn sich Werte in diesem Bereich ändern, wird die Liste der eindeutigen Werte sofort dynamisch geändert.

Wählen und markieren Sie ganz einfach alle eindeutigen Werte in einem Bereich in Excel:

Das Wählen Sie Doppelte und eindeutige Zellen Nutzen von Kutools for Excel Sie können auf einfache Weise alle eindeutigen Werte (einschließlich der ersten Duplikate) oder die nur einmal angezeigten eindeutigen Werte sowie die erforderlichen doppelten Werte auswählen und hervorheben (siehe Abbildung unten).
Laden Sie jetzt Kutools für Excel herunter! (30-Tag kostenlose Loipe)


Extrahieren Sie dynamisch eine Liste eindeutiger Werte aus einem Spaltenbereich mit VBA-Code

Sie können auch eine Liste eindeutiger Werte dynamisch aus einem Spaltenbereich mit dem folgenden VBA-Code extrahieren.

1. Drücken Sie Andere + F11 Tasten gleichzeitig zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. In dem Microsoft Visual Basic für Applikationen Klicken Sie im Fenster Insert > Modul. Kopieren Sie dann den folgenden VBA-Code und fügen Sie ihn in das ein Modul Fenster.

VBA-Code: Extrahieren Sie eine Liste eindeutiger Werte aus einem Bereich

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Hinweis: Im Code ist D2 die Zelle, in der Sie die Liste der eindeutigen Werte finden. Sie können es nach Bedarf ändern.

3. Kehren Sie zum Arbeitsblatt zurück und klicken Sie auf Insert > Formen > Rechteck. Siehe Screenshot:

4. Zeichnen Sie ein Rechteck in Ihr Arbeitsblatt und geben Sie einige Wörter ein, die Sie darauf anzeigen möchten. Klicken Sie dann mit der rechten Maustaste darauf und wählen Sie Makro zuweisen aus dem Kontextmenü. In dem Makro zuweisen Wählen Sie im Dialogfeld CreateUniqueList der Makroname Feld, und klicken Sie dann auf OK Taste. Siehe Screenshot:

5. Klicken Sie nun auf die Rechteckschaltfläche a Kutools for Excel Das Dialogfeld wird angezeigt. Wählen Sie den Bereich aus, der eindeutige Werte enthält, die Sie extrahieren müssen, und klicken Sie dann auf OK .

Von nun an können Sie den obigen Schritt 5 wiederholen, um die Liste der eindeutigen Werte automatisch zu aktualisieren.


In Verbindung stehende Artikel:


Die besten Tools für die Office-Produktivität

Kutools for Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%

  • Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Super Formelriegel (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2021 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. 30-tägige kostenlose Testversion mit allen Funktionen. 60 Tage Geld-zurück-Garantie.
kte tab 201905

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
officetab unten
Kommentare (35)
Noch keine Bewertungen. Bewerten Sie als Erster!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Vielen Dank für das Tutorial. Wie würden Sie mit der Formelmethode die Formel ändern, wenn Sie einen Kategoriequalifizierer hinzufügen möchten? Angenommen, Sie unterscheiden in Spalte C, ob es sich bei dem Artikel um Obst oder Gemüse handelt. Wie würden Sie den Code ändern, um nur die einzigartigen Früchte zu sortieren und das Gemüse auszuschließen? Ich habe versucht, COUNTIF durch COUNTIFS zu ersetzen, wobei ich das zweite Countifs-Kriterium von (LIST RANGE, "CATEGORY") verwendet habe, aber es wird leer zurückgegeben. Muss ich mein Array erweitern und SVERWEIS integrieren?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich bin anständig in Excel, aber ich versuche wirklich, mir einen Kopf darüber zu machen, wie und warum die obige Formel funktioniert (sie funktioniert für das, wofür ich sie verwende, aber ich muss verstehen, warum). Ich bin manchmal ein wenig verwirrt, wenn ich Arrays verwende, daher wäre jede Erklärung in idiotischen Begriffen äußerst hilfreich. Grüße
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Diese Formel ist veraltet und funktioniert nicht. Ich habe buchstäblich genau dieses Excel-Blatt eingerichtet, um zu sehen, ob ich diese Formel zum Laufen bringen kann, und das tut es nicht.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hey Junge,
Welche Office-Version verwendest du?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - gefunden, dass dies von einer anderen Seite aus funktioniert...

Verwenden Sie Strg+Umschalt+Eingabe, um die Array-Funktion (geschweifte Klammern) zu erhalten. Ziehen Sie die Formeln per Drag-Copy-Paste, bis #NA angezeigt wird. Mein Datensatz befand sich in Spalte Q, er wurde verglichen, um zu sehen, ob er in der Liste der eindeutigen Elemente in Spalte V vorhanden ist, die sich kontinuierlich entlang derselben Spalte erstreckt.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Schönen Tag.
Bitte listen Sie alle eindeutigen Werte der Spalte Q mit der obigen Formel auf und verwenden Sie dann seine Formel =IF(D2=V1,"Match","No match"), um zu vergleichen, ob die eindeutigen Werte in Spalte Q mit Spalte V in derselben Zeile verglichen werden .
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo und danke für deine Hilfe.

Ich brauche genau diese Funktionalität, aber meine Liste mit "eindeutigen Werten" muss sich über Spalten statt über Zeilen erstrecken, sodass die Erweiterungsliste in den Zeilen für mich nicht funktioniert.

Wie kann ich diese Formel ändern, damit die Liste "eindeutige Werte" erweitert wird, wenn ich sie über die Spalten ziehe?

Offset()?
Transponieren()?
Indirect() mit einer Zeichenfolge absoluter Referenzen, die mit einer Referenz auf die Spalte anstelle der Zeile verkettet sind?


Nochmals vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Lieber Ryan,
Diese Formel =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter kann helfen Ihnen, das Problem zu lösen.
Siehe unten stehenden Screenshot:
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Außerdem, aus welchen Gründen auch immer, lieferte die ursprüngliche Formel:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

gibt eine "zirkuläre Referenz"-Warnung zurück und wird nicht berechnet.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Lieber Ryan,
Welche Office-Version verwendest du? Die Formel funktioniert gut in meinem Office 2016 und 2013.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das ist mir schon einmal passiert - meine Lösung war, dass ich die Formel in die Zelle D1 eingegeben habe (entspricht dem von mir verwendeten Arbeitsblatt). Unabhängig davon, welcher Zelle $D:$1 entspricht, müssen Sie sie in die Zelle darunter eingeben - D2. Entschuldigung, wenn das nicht der Grund für den Fehler ist
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Irgendwelche Tipps, wie Sie die VBA-Option mit Excel 2016 für macOS verwenden können? Ich bin den Schritten gefolgt; Wenn ich das Makro jedoch ausführe, passiert überhaupt nichts. Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Daer Jones,
Bitte versuchen Sie den folgenden VBA-Code und lassen Sie mich wissen, ob er für Sie funktioniert. Danke!

Unter CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I als ganze Zahl
' Bei Fehler Fortsetzen als Nächstes
Set xRng = Application.InputBox("Bitte Bereich auswählen:", "Kutools for Excel", Selection.Address, , , , , 8)
Wenn xRng nichts ist, dann beenden Sie Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
Für I = 1 bis xLastRow2
If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
End If
Weiter
End Sub
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Kristall,
Ich versuche, die VB-Version der eindeutigen Werteliste zu verwenden, und stoße auf ein Problem.
Der Bereich, aus dem ich eine Spalte mit eindeutigen Werten erstellen möchte, sind alle Formeln, die sich auf verschiedene Registerkarten beziehen.
Wie bekommt man statt der Formel den zu übertragenden Wert?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Lieber Mike,
Bitte konvertieren Sie Ihre Formelreferenzen in absolute und wenden Sie dann das VB-Skript an.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe das gleiche Problem, außer dass sich meine Formel auf Spaltennamen bezieht und nicht in absolut konvertieren kann.
Wie ändere ich die vba, um die Werte und nicht die Formel einzufügen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wie würden Sie mehrere Kriterien hinzufügen, z. B. wenn Sie nur etwas zur dynamischen Liste hinzufügen möchten, wenn das Datum nur der 9. September ist?

Ich versuche "&" in der MATCH-Formel, aber es funktioniert nicht.

Zum Beispiel, basierend auf Ihrem Beispiel:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Dies löst einen Fehler aus oder erstellt Duplikate.

Alternativ habe ich gelesen, dass "+" funktionieren könnte, obwohl ich es nicht zum Laufen bringen kann. Oder mit SMALL.

Ideen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Lieber Zac,
Tut mir leid, dass ich dir dabei nicht helfen kann, du kannst deine Frage in unserem Forum stellen: https://www.extendoffice.com/forum.html um mehr Excel-Unterstützung von unserem Fachmann zu erhalten.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wie würden Sie eine zweite Variable hinzufügen? Zum Beispiel möchte ich alle eindeutigen Elemente in einer Spalte, die auch einen ähnlichen Wert in einer anderen Spalte haben. Stellen Sie sich in Ihrem Beispiel eine dritte Spalte mit dem Titel "Abteilung" vor, die Werte wie Produkt, Fleisch usw. enthält. Mir ist klar, dass dies alles Produkte sind, aber hoffentlich verstehen Sie, was ich meine. Würden Sie die CountIF-Formel in eine COUNTIFS-Formel ändern oder ändern Sie sie auf andere Weise?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Matt
Bitte versuchen Sie diese Formel =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","").
Angenommen, die beiden verglichenen Listen sind Spalte A und Spalte C. Wenn die eindeutigen Werte nur in Spalte A, aber nicht in Spalte C bleiben, wird Ja in Spalte B angezeigt; Wenn in Spalte B nichts zurückgegeben wird, bedeutet dies, dass der entsprechende Wert sowohl in Spalte A als auch in Spalte C verbleibt.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Danke für die Antwort.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn ich dies für ein Excel-Blatt mit tausend Zeilen in der neuesten Version von Excel auf einem Mac mache, kehrt es nie zurück. Die erste Zeile funktioniert, aber wenn ich nach unten dupliziere, wechselt Excel in einen Berechnungsmodus, der seit über zwei Stunden keine Werte mehr zurückgegeben hat.

Irgendwelche Gedanken, wie man dies für große Listen (bis zu 2 Zeilen) macht, die 50 oder 60 eindeutige Werte zurückgeben?

Ich habe das in der „Numbers“-App nachgeahmt, und es funktioniert dort perfekt, die Berechnung dauert nur ein paar Minuten. Es dauert nur so lange in Excel, dass ich mich frage, ob es jemals fertig wird. Ich plane, es über Nacht "laufen" zu lassen, um zu sehen, was passieren wird.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Überprüfen Sie Ihre Berechnungsoptionen. Es muss auf Automatik gestellt werden. Datei > Optionen > Formeln > Berechnungsoptionen > Arbeitsmappenberechnung (automatische Auswahl)
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich versuche, die Formel an meinen tatsächlichen Daten vorbei nach unten zu ziehen, damit ich unterschiedlich große Datensätze eingeben kann und nichts anpassen muss. Die letzte Zeile nach dem Ende meiner tatsächlichen Daten gibt jedoch immer eine "0" zurück. Ich verwende die eindeutigen Werte für etwas anderes in einer angrenzenden Spalte, und die 0 bewirkt, dass der letzte Wert wiederholt wird (wenn ich die 0 lösche, wird der Wert nicht mehr wiederholt). Irgendeine Idee, wie man das beheben kann? Außerdem nutze ich Office 365 Business
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, danke für deine Hilfe.
Wie kann ich nun meine Werte auch alphabetisch sortieren lassen? (Ich möchte den Filter nicht auf meiner Master-Tabelle verwenden)
Sollte ich COUNTIFS anstelle von COUNTIF verwenden?
Bitte helfen
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Alexis,
Leider kann ich nicht helfen, den extrahierten Wert gleichzeitig mit der Formel alphabetisch zu sortieren. Danke für deinen Kommentar.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich verwende diese =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")-Formel, die großartig ist für eine Spalte, aber meine Daten sind über eine Reihe von Spalten und Zeilen verteilt. Kann ich die Formel bearbeiten, um den gesamten Bereich einzubeziehen? Meine Daten leben von AC4 bis AR60 ...
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich versuche den VBA-Code und die Formel. Der Code VBA funktioniert sehr gut, aber ich kann keine Datei mit Makro behalten. Aber das Problem ist, dass ich die Formel nicht zum Laufen bringen kann. Hatte jemand eine Idee? Danke
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Charlotte,
Danke für deinen Kommentar. Sie können die Datei mit Makro für die zukünftige Verwendung aufbewahren, indem Sie die Arbeitsmappe als Excel-Arbeitsmappe mit Makros speichern.
Würden Sie für das Formelproblem bitte einen Screenshot Ihrer Daten bereitstellen? Danke für deinen Kommentar.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
ich danke Ihnen sehr
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wie lässt sich der VBA-Code für einen Bereich verwenden, in dem eine andere Formel verwendet wurde? In Spalte BI haben Sie eine Formel, die sich auf die Spalten D und E bezieht.
Wenn ich den Code auf Spalte L anwende (sagen wir) (offensichtlich die Zellen im Code richtig ändere), gibt das Makro die Formel zurück, die auf die Spalten M und N angewendet wird ... Es funktioniert also, aber nicht so, wie ich es möchte! Wie werden die Werte in Spalte B beibehalten? Danke
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje met unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich möchte genau dasselbe tun können, außer dass zwei separate Spaltenbereiche (B2: B9) sowie (D2: D9) verwendet werden. Ist dies möglich?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Anthony,
Sie können die Ergebnisse in derselben Spalte wie die Originaldaten platzieren. Wie in diesem Fall Spalte B.
Aber Sie müssen die oberste Zelle der Ergebniszelle in der Formel wie folgt referenzieren.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Strg + Shift + Enter
Dieser Kommentar wurde vom Moderator auf der Website minimiert
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se definieren un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
Es sind noch keine Kommentare vorhanden
Hinterlassen Sie Ihre Kommentare
Als Gast posten
×
Bewerte diese Nachricht:
0   Figuren
Vorgeschlagene Standorte

Folgen Sie uns

Copyright © 2009 - www.extendoffice.com. | Alle Rechte vorbehalten. Unterstützt von ExtendOffice. | Sitemap
Microsoft und das Office-Logo sind Marken oder eingetragene Marken der Microsoft Corporation in den USA und / oder anderen Ländern.
Geschützt durch Sectigo SSL