Wie extrahiert man eindeutige Werte aus mehreren Spalten in Excel?
Wenn Sie regelmäßig mit Datensätzen arbeiten, die sich über mehrere Spalten in Excel erstrecken, kann es vorkommen, dass bestimmte Werte innerhalb einer Spalte oder sogar übergreifend zwischen verschiedenen Spalten doppelt auftauchen. Gerade bei Berichten oder Datenanalysen ist es oft entscheidend, alle eindeutigen Werte zu identifizieren und zu extrahieren – also jene, die im gesamten markierten Bereich exakt einmal vorkommen, unabhängig von ihrer Position. Dies manuell zu erledigen, ist nicht nur zeitaufwändig, sondern auch fehleranfällig, besonders bei umfangreichen Datensätzen oder komplex strukturierten Tabellen. Glücklicherweise bietet Excel mehrere effiziente Methoden, um genau diese eindeutigen Werte schnell und zuverlässig herauszufiltern.
Dieser Leitfaden stellt mehrere Lösungen vor, die Sie je nach Ihrer Excel-Version und Ihren Vorlieben nutzen können – etwa klassische Formeln für alle Versionen, dynamische Arrayformeln für neuere Excel-Versionen, KUTOOLS AI Aide für mühelose Ergebnisse, PivotTables zur visuellen Konsolidierung sowie VBA-Code für die automatisierte Extraktion in komplexen Szenarien.
Extrahieren eindeutiger Werte aus mehreren Spalten mithilfe von Formeln
Manchmal möchten Sie diese Extraktion mithilfe integrierter Excel-Funktionen durchführen. Dieser Abschnitt stellt zwei Ansätze vor: eine Matrixformel, die in allen Excel-Versionen funktioniert, sowie eine dynamische Matrixformel, die in neueren Versionen wie Excel 365 und Excel 2021 verfügbar ist. Diese Methoden eignen sich besonders dann, wenn Sie eine direkte, formelbasierte Lösung benötigen, bei Datenänderungen häufige automatische Aktualisierungen wünschen oder auf externe Add-Ins und Code verzichten möchten.
Extrahieren eindeutiger Werte aus mehreren Spalten mit Matrixformel für alle Excel-Versionen
Für maximale Kompatibilität mit allen Excel-Versionen ermöglicht eine Matrixformel die Extraktion eindeutiger Werte aus mehreren Spalten – selbst wenn Ihre Excel-Version keine dynamischen Matrizen unterstützt. Dieser Ansatz kombiniert die Funktionen INDIREKT, TEXT, MIN, WENN, ZÄHLENWENN, ZEILE und SPALTEund ist damit flexibel für unterschiedlichste Datenstrukturen.
Angenommen, Ihre Daten befinden sich im Bereich A2:C9. Um die eindeutigen Werte ab Zelle E2 zu extrahieren, gehen Sie wie folgt vor:
1.Klicken Sie auf Zelle E2(oder die erste Zelle Ihres Platzierungsbereich der Liste) und geben Sie die folgende Matrixformel ein:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>„")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&„"
- A2:C9 ist der Datenbereich, aus dem Sie eindeutige Werte extrahieren möchten.
- E1:E1 bezieht sich auf die Zellen unmittelbar oberhalb Ihrer ersten Ausgabezelle und ist erforderlich, um zu verfolgen, welche Einträge bereits ausgegeben wurden.
- $2:$9 sind die Zeilenbezüge Ihrer Daten; $A:$C sind die Spaltenbezüge. Passen Sie diese entsprechend Ihrem Arbeitsblattlayout an.
2. Nachdem Sie die Formel eingegeben haben, bestätigen Sie sie nicht einfach mit Enter, sondern drücken Sie gleichzeitig Strg + Umschalt + Enter, um sie als Matrixformel festzulegen. Bei korrekter Eingabe erscheinen geschweifte Klammern {} um Ihre Formel in der Bearbeitungsleiste. Ziehen Sie anschließend den Ausfüllkästchen von E2 die Spalte hinunter – und zwar so lange, bis leere Zellen erscheinen. Das zeigt an, dass keine weiteren eindeutigen Werte mehr vorhanden sind. So werden alle eindeutigen Werte in der Zielspalte angezeigt.
- $A$2:$C$9Gibt den gesamten Zellbereich an, der auf eindeutige Werte überprüft werden soll.
- IF(($A$2:$C$9<>„")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>„"stellt sicher, dass leere Zellen ignoriert werden.
- COUNTIF($E$1:E1,$A$2:$C$9)=0stellt sicher, dass nur neue (noch nicht extrahierte) Werte berücksichtigt werden.
- Sind beide Bedingungen erfüllt, erfolgt die entsprechende Ausgabe als Berechnung auf Basis der Zeilen- und Spaltennummer der Zelle, um eine eindeutige Indexnummer zu generieren.
- Wenn eine der beiden Bedingungen nicht erfüllt ist, gibt die Formel eine sehr große Zahl ()7^8) zurück, um eine versehentliche Auswahl zu vermeiden.
- MIN(...)Ermittelt die niedrigste Indexnummer und lokalisiert so effektiv die Position des nächsten verfügbaren eindeutigen Werts innerhalb der Daten.
- TEXT(...,"R0C00")Wandelt den Index mithilfe der R1C1-Notation in einen gültigen Zellbezug um.
- INDIRECT(...): Konvertiert den oben erstellten Zellbezug in einen Wert aus Ihrem Datenbereich.
- &„"Stellt sicher, dass das Formelergebnis als Text behandelt wird, um unerwartete Formatierungen zu vermeiden.
Extrahieren eindeutiger Werte aus mehreren Spalten mit Formel für Excel 365, Excel 2021 und neuere Versionen
Wenn Sie Excel 365, Excel 2021 oder eine neuere Version verwenden, stehen Ihnen dynamische Matrixfunktionen zur Verfügung, die eine einfachere und intuitivere Möglichkeit bieten, eindeutige Werte aus mehreren Spalten zu extrahieren. Mit den Funktionen UNIQUE und TOCOL kombinieren Sie Daten aus mehreren Spalten in einem einzigen Schritt und entfernen gleichzeitig Duplikate – besonders nützlich bei ständig aktualisierten oder umfangreichen Datensätzen.
Um diese Methode zu nutzen, wählen Sie einfach eine leere Zelle aus (z. B.)E2 oder wo auch immer die Ergebnisse erscheinen sollen), geben Sie die folgende Formel ein und drücken Sie Enter:
=UNIQUE(TOCOL(A2:C9,1))Nach dem Drücken der Eingabetaste werden alle eindeutigen Werte aus dem Bereich A2:C9 automatisch in die darunterliegenden Zellen übertragen. Diese Funktion ist besonders effizient – die Ausgabe aktualisiert sich dynamisch, sobald sich Ihre Quelldaten ändern, und erspart Ihnen manuelle Aktualisierungsschritte.
- TOCOL(A2:C9,1): Wandelt Ihren mehrspaltigen Wertebereich automatisch in eine einzelne Spalte um – und entfernt dabei alle leeren Zellen.
- UNIQUE(...): Extrahiert jeden Wert nur einmal und liefert eine bereinigte, doppeltelementfreie Liste.
Extrahieren Sie eindeutige Werte aus mehreren Spalten mit KUTOOLS AI Aide
Wenn Sie einen besonders einfachen Ansatz bevorzugen und manuellen Aufwand reduzieren möchten, hilft Ihnen KUTOOLS AI Aide in Kutools für Excel dabei, eindeutige Werte aus mehreren Spalten mühelos zu extrahieren. Diese Methode ist ideal, wenn Sie mit Formeln nicht vertraut sind oder Formelfehler vermeiden wollen. KUTOOLS AI Aide interpretiert Ihre Anweisungen und verarbeitet die Daten automatisch – perfekt sowohl für Einsteiger als auch für alle, die eine schnelle Lösung mit nur wenigen Klicks suchen.
Klicken Sie nach der Installation auf KUTOOLS AI>AI-Assistent, um den Bereich „KUTOOLS AI Aide“ zu öffnen:
- Geben Sie Ihre Anfrage in das Chatfeld ein, z. B.:„Extrahiere eindeutige Werte aus dem Bereich A2:C9, ignoriere leere Zellen und platziere die Ergebnisse ab Zelle E2.“
- Klicken Sie auf „Senden“ oder drücken Sie die Eingabetaste. Sobald die KI Ihre Anfrage analysiert hat, klicken Sie einfach auf „Ausführen“, um die Aktion zu starten. Die Ergebnisse erscheinen sofort an der von Ihnen angegebenen Stelle in Ihrem Arbeitsblatt.
Tipp: Diese Lösung ist besonders nützlich, wenn sich Ihr Datenextraktions-Workflow häufig ändert oder wenn Sie Funktionen zur Verarbeitung natürlicher Sprache nutzen möchten. Achten Sie darauf, die Liste „extrahieren“ auf leere Zellen zu prüfen, falls Ihre Originaldaten nicht vollständig konsistent sind – denn je nach Details Ihrer KI-Anfrage können leere Einträge entweder enthalten oder herausgefiltert werden.
Extrahieren Sie eindeutige Werte aus mehreren Spalten mit PivotTable
PivotTable sind eine weitere praktische Methode zur Extraktion eindeutiger Werte, insbesondere wenn Sie lieber mit visuellen Werkzeugen arbeiten und die eindeutigen Elemente zusammenfassen oder weiter analysieren möchten, beispielsweise durch Zählen ihrer Vorkommen. Dieser Ansatz ist unkompliziert und erfordert keine Formeln. Er setzt jedoch einige Einrichtungsschritte und geringfügige Datenanpassungen voraus, besonders wenn die beteiligten Spalten unterschiedliche Überschriften aufweisen.
Im Folgenden finden Sie einen empfohlenen Prozess zur Extraktion eindeutiger Werte mithilfe einer PivotTable:
1. Fügen Sie direkt links neben Ihren Daten eine neue leere Spalte ein. Wenn Ihre Daten beispielsweise in Spalte B beginnen, fügen Sie eine neue Spalte A ein. Diese Anpassung stellt sicher, dass die Bereichskonsolidierung korrekt funktioniert.
2. Wählen Sie eine beliebige Zelle innerhalb Ihres Datensatzes aus, drücken Sie Alt + D und anschließend schnell P, um den „Assistenten für PivotTable und PivotChart“ zu starten. Wählen Sie im ersten Schritt des Assistenten „Mehrere konsolidierte Bereiche“ aus – so kombinieren Sie Werte aus zahlreichen Spalten in einem einzigen zusammengefassten Feld.
3. Klicken Sie auf Weiter und wählen Sie dann „Ein einzelnes Seitenfeld für mich erstellen“. Dieser Schritt fasst alle Ihre Daten zu einer einzigen Gruppe zusammen, um die Extraktion eindeutiger Werte zu vereinfachen.
4. Wählen Sie im nächsten Schritt den gesamten Datenbereich aus (einschließlich der neuen leeren Spalte), klicken Sie auf die Schaltfläche Hinzufügen, um Ihre Auswahl in die Liste „Alle Bereiche“ zu übernehmen, und klicken Sie anschließend auf Weiter.
5. Wählen Sie im letzten Schritt des Assistenten aus, wo Sie die PivotTable platzieren möchten – entweder auf einem neuen oder einem bestehenden Arbeitsblatt – und klicken Sie dann auf Fertig stellen, um den PivotTable-Bericht zu generieren.
6. Deaktivieren Sie im neuen PivotTable alle Felder im Abschnitt „Zu Bericht hinzuzufügende Felder auswählen“, um die Standardansicht zu leeren.
7. Ziehen Sie abschließend das Feld „Wert“ in den Bereich Zeilen. Die PivotTable zeigt alle eindeutigen Werte aus Ihrem ursprünglichen mehrspaltigen Bereich übersichtlich in einer einzigen Spalte an.
Einschränkungen:Die Daten erfordern eine vorbereitende Anordnung, und falls sich Ihr Quelldaten aktualisiert, müssen Sie die PivotTable aktualisieren, um neue eindeutige Werte anzuzeigen.
Extrahieren eindeutiger Werte aus mehreren Spalten mit VBA-Code
In Fällen, in denen Sie die Extraktion automatisieren oder große, unregelmäßige Datensätze verarbeiten müssen, bietet VBA-Code (Visual Basic for Applications) eine schnelle und wiederverwendbare Lösung. Diese eignet sich ideal für Anwender mit grundlegenden Kenntnissen des Excel-VBA-Editors sowie für wiederkehrende Aufgaben, bei denen manuelle Arbeit auf ein Minimum reduziert werden soll. Zudem verarbeitet VBA große Datenmengen effizienter als Arrayformeln.
1. Öffnen Sie den VBA-Editor, indem Sie Alt + F11 drücken. Klicken Sie im erscheinenden Fenster „Microsoft Visual Basic for Applications“ auf Einfügen > Modul, um ein neues Modul hinzuzufügen.
2.Fügen Sie im neuen Modul den folgenden Code ein:
VBA: Extrahieren Sie eindeutige Werte aus mehreren Spalten
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Drücken Sie F5, um den Code auszuführen. Anschließend erscheint ein Dialog, der Sie auffordert, den Datenbereich auszuwählen. Markieren Sie dabei alle relevanten Spalten – auch solche mit leeren Zellen.
4.Nach dem Klicken auf „OK“ erscheint eine weitere Eingabeaufforderung, in der Sie festlegen, wo die eindeutigen Werte ausgegeben werden sollen. Geben Sie die Zelle darüber an, ab der die Ergebnisse aufgelistet werden sollen (z. B.)E2).
5. Klicken Sie auf „OK“, und das Makro wird automatisch ausgeführt. Alle eindeutigen Werte erscheinen ab der von Ihnen angegebenen Position.
- Falls Sie beim Einsatz von Formeln Fehler wie #WERT! oder #ÜBERLAUF! erhalten, überprüfen Sie Ihre Bereiche und stellen Sie sicher, dass der Ausgabebereich frei ist.
- Prüfen Sie stets auf ausgeblendete oder zusammengeführte Zeilen in Ihrem Datenbereich, da diese die Korrektheit der Extraktion eindeutiger Werte beeinträchtigen können.
- Matrix- und dynamische Matrixformeln aktualisieren sich automatisch bei Änderungen, während Lösungen mit dem Spezialfilter und PivotTable gegebenenfalls manuell aktualisiert oder erneut ausgeführt werden müssen.
- Für wiederkehrende Aufgaben empfiehlt sich die Automatisierung der Extraktion per VBA – so sichern Sie Konsistenz und Geschwindigkeit.
- Sichern Sie Ihre Daten, bevor Sie Massenextraktionen oder Automatisierungsroutinen – insbesondere in komplexen Arbeitsmappen – durchführen.
Weitere verwandte Artikel:
- Anzahl eindeutiger und unterschiedlicher Werte aus einer Liste zählen
- Angenommen, Sie haben eine lange Liste mit Werten, die einige Duplikate enthält, und möchten herausfinden, wie viele eindeutige Werte (also solche, die nur einmal vorkommen) oder insgesamt unterschiedliche Werte sich in einer Spalte befinden – so wie im linken Screenshot gezeigt. Dieser Artikel stellt effiziente Methoden vor, um eindeutige und unterschiedliche Einträge in Excel zu zählen.
- Eindeutige Werte basierend auf Kriterien in Excel extrahieren
- Angenommen, Sie möchten aus Spalte B nur die eindeutigen Namen extrahieren – und zwar basierend auf einer bestimmten Bedingung in Spalte A –, um Ergebnisse wie im Screenshot zu erhalten. Dieses Tutorial zeigt Ihnen Methoden, wie Sie bei der Extraktion eindeutiger Werte Kriterien anwenden können.
- Nur eindeutige Werte in Excel zulassen
- Wenn Sie in einer Spalte eines Arbeitsblatts nur eindeutige Einträge zulassen und Duplikate verhindern möchten, stellt dieser Artikel praktische Techniken zur Durchsetzung von Eindeutigkeitsregeln in Excel vor.
- Summe eindeutiger Werte basierend auf Kriterien in Excel
- Beispielsweise möchten Sie vielleicht nur die eindeutigen Werte in einer Spalte „Bestellung“ summieren – basierend auf den Namen in einer benachbarten Spalte, wie im Screenshot dargestellt. Dieser Artikel zeigt Ihnen effektive Ansätze, um Eindeutigkeits- und bedingte Berechnungen zu kombinieren.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins