Wie extrahiert man eindeutige Werte aus mehreren Spalten in Excel?

Wenn Sie oft mit Datensätzen arbeiten, die sich über mehrere Spalten in Excel erstrecken, können Sie auf Situationen stoßen, in denen bestimmte Werte innerhalb derselben Spalte oder zwischen verschiedenen Spalten dupliziert sind. Bei vielen Berichts- oder Datenanalyseaufgaben wird es notwendig, alle eindeutigen Werte zu identifizieren und zu extrahieren – also diejenigen, die nur einmal im gesamten Auswahlbereich vorkommen, unabhängig davon, wo sie sich befinden. Dies manuell zu tun, kann zeitaufwendig und fehleranfällig sein, besonders bei großen Datensätzen oder komplexen Tabellen. Glücklicherweise bietet Excel eine Reihe von Methoden, um diese eindeutigen Werte effizient zu extrahieren.

Dieser Leitfaden stellt mehrere Lösungen vor, die Sie je nach Ihrer Excel-Version und Ihren Vorlieben verwenden können – wie Formeln, die für alle Versionen geeignet sind, dynamische Array-Formeln für neuere Versionen, die Verwendung von Kutools AI-Assistent für einfache Ergebnisse, Pivot-Tabellen zur visuellen Zusammenfassung und VBA-Code für automatisierte Extraktion in komplexen Szenarien.

• Eindeutige Werte aus mehreren Spalten mit Formeln extrahieren
  • Array-Formel (alle Excel-Versionen)
  • Formel (Excel 365, Excel 2021 und neuere Versionen)
• Eindeutige Werte aus mehreren Spalten mit Kutools AI-Assistent extrahieren
• Eindeutige Werte aus mehreren Spalten mit einer Pivot-Tabelle extrahieren
• Eindeutige Werte aus mehreren Spalten mit VBA-Code extrahieren

Eindeutige Werte aus mehreren Spalten mit Formeln extrahieren

Es gibt Zeiten, in denen Sie diese Extraktion gerne mit integrierten Excel-Funktionen durchführen möchten. Dieser Abschnitt beschreibt, wie dies mit zwei Ansätzen erreicht werden kann: einer Array-Formel, die für alle Excel-Versionen geeignet ist, und einer dynamischen Array-Formel, die in neueren Versionen wie Excel 365 und Excel 2021 verfügbar ist. Diese Methoden sind ideal, wenn Sie eine direkte formelbasierte Lösung wünschen, eine häufige Aktualisierung bei sich ändernden Daten benötigen oder externe Add-ins oder Code vermeiden möchten.

Eindeutige Werte aus mehreren Spalten mit Array-Formel für alle Excel-Versionen extrahieren

Für Kompatibilität über alle Excel-Versionen hinweg ermöglicht die Verwendung einer Array-Formel das Extrahieren eindeutiger Werte aus mehreren Spalten – selbst wenn Ihre Excel-Version keine dynamischen Arrays unterstützt. Dieser Ansatz nutzt eine Kombination der Funktionen INDIREKT, TEXT, MIN, WENN, ZÄHLENWENN, ZEILE und SPALTE, was ihn flexibel für verschiedene Datenstrukturen macht.

Angenommen, Ihre Daten befinden sich im Bereich A2:C9. Um die eindeutigen Werte ab der Zelle E2 zu extrahieren, gehen Sie wie folgt vor:

1. Klicken Sie auf die Zelle E2 (oder die erste Zelle Ihres Ausgabebereichs) und geben Sie die folgende Array-Formel ein:

=INDIREKT(TEXT(MIN(WENN(($A$2:$C$9<>"")*(ZÄHLENWENN($E$1:E1,$A$2:$C$9)=0),ZEILE($2:$9)*100+SPALTE($A:$C),7^8)),"R0C00"),)&""

2. Sobald Sie die Formel eingegeben haben, drücken Sie statt einfach Enter die Tastenkombination Strg + Umschalt + Enter, um sie als Array-Formel zu bestätigen. Wenn Sie alles richtig gemacht haben, erscheinen geschweifte Klammern {} um Ihre Formel in der Formelleiste. Ziehen Sie dann den Ausfüllkästchen von E2 nach unten in der Spalte. Fahren Sie mit dem Ziehen fort, bis leere Zellen erscheinen, was darauf hinweist, dass keine weiteren eindeutigen Werte mehr zu extrahieren sind. Dieser Prozess stellt sicher, dass alle eindeutigen Werte in der Zielspalte angezeigt werden.

Eindeutige Werte aus mehreren Spalten mit Formel für Excel 365, Excel 2021 und neuere Versionen extrahieren

Wenn Sie Excel 365, Excel 2021 oder eine neuere Version verwenden, haben Sie Zugriff auf dynamische Array-Funktionen, die eine einfachere und intuitivere Möglichkeit bieten, eindeutige Werte aus mehreren Spalten zu extrahieren. Die Funktionen EINDEUTIG und TOCOL erleichtern und beschleunigen das Kombinieren von Daten über Spalten hinweg und das Entfernen von Duplikaten in einem einzigen Schritt – besonders nützlich für diejenigen, die mit sich ständig aktualisierenden oder größeren Datensätzen arbeiten.

Um diese Methode zu verwenden, wählen Sie einfach eine leere Zelle (z.B. E2 oder wo auch immer Sie möchten, dass die Ergebnisse erscheinen), geben Sie diese Formel ein und drücken Sie Enter:

=UNIQUE(TOCOL(A2:C9,1))

Nachdem Sie Enter gedrückt haben, füllen sich alle eindeutigen Werte aus dem Bereich A2:C9 automatisch in die Zellen unter der Formel aus. Diese Funktion ist besonders effizient – die Ausgabe aktualisiert sich dynamisch, wenn sich Ihre Quelldaten ändern, sodass Sie manuelle Aktualisierungsschritte sparen.

Eindeutige Werte aus mehreren Spalten mit Kutools AI-Assistent extrahieren

Wenn Sie einen strömungsoptimierten Ansatz bevorzugen und den manuellen Aufwand reduzieren möchten, kann Ihnen der Kutools AI-Assistent in Kutools für Excel helfen, eindeutige Werte aus mehreren Spalten mühelos zu extrahieren. Diese Methode ist besonders wertvoll, wenn Sie sich mit Formeln nicht auskennen oder das Risiko von Formelfehlern vermeiden möchten. Der Kutools AI-Assistent interpretiert Ihre Anweisungen und verarbeitet die Daten automatisch, was sowohl für Anfänger als auch für Benutzer, die eine schnelle Lösung in wenigen Klicks suchen, ideal ist.

Nach der Installation klicken Sie auf Kutools AI > AI-Assistent, um den „Kutools AI-Assistent“-Bereich zu öffnen:

1. Geben Sie Ihre Anfrage im Chatfeld ein, z.B.: „Extrahieren Sie eindeutige Werte aus dem Bereich A2:C9, ignorieren Sie leere Zellen, und platzieren Sie die Ergebnisse ab E2:“
2. Klicken Sie auf „Senden“ oder drücken Sie Enter, und nachdem die KI die Anfrage analysiert hat, klicken Sie einfach auf „Ausführen“, um die Aktion auszuführen. Die Ergebnisse erscheinen sofort in Ihrem Arbeitsblatt, genau an der von Ihnen angegebenen Stelle.

Tipp: Diese Lösung ist sehr nützlich, wenn sich Ihr Datenextraktionsworkflow ändert oder Sie natürliche Sprachverarbeitungsfunktionen wünschen. Denken Sie daran, die extrahierte Liste auf leere Zellen zu überprüfen, falls Ihre Originaldaten nicht perfekt konsistent sind, da leere Einträge je nach Details Ihrer KI-Anfrage enthalten oder gefiltert sein könnten.

Eindeutige Werte aus mehreren Spalten mit Pivot-Tabelle extrahieren

Pivot-Tabellen sind eine weitere praktische Methode zum Extrahieren eindeutiger Werte, insbesondere wenn Sie lieber mit visuellen Werkzeugen arbeiten und die eindeutigen Elemente zusammenfassen oder weiter analysieren möchten, wie z.B. das Zählen von Vorkommen. Dieser Ansatz ist einfach und erfordert keine Formeln. Allerdings sind einige Einrichtungsschritte und geringfügige Datenanpassungen erforderlich, insbesondere wenn die beteiligten Spalten unterschiedliche Überschriften haben.

Hier ist ein vorgeschlagener Prozess zum Extrahieren eindeutiger Werte mit einer Pivot-Tabelle:

1. Fügen Sie eine neue leere Spalte unmittelbar links neben Ihren Daten ein. Zum Beispiel, fügen Sie eine neue Spalte A ein, wenn Ihre Daten in Spalte B beginnen. Diese Anpassung hilft sicherzustellen, dass der Bereich korrekt zusammengeführt wird.

2. Wählen Sie eine beliebige Zelle in Ihrem Datensatz aus, drücken Sie Alt + D, und drücken Sie schnell P, um den „PivotTable- und PivotChart-Assistenten“ zu starten. Im ersten Schritt des Assistenten wählen Sie „Mehrere Konsolidierungsbereiche.“ Dies ermöglicht es Ihnen, Werte aus zahlreichen Spalten in ein einziges zusammengefasstes Feld zu kombinieren.

3. Klicken Sie auf „Weiter“ und wählen Sie „Ein einzelnes Seitenfeld für mich erstellen.“ Dieser Schritt organisiert alle Ihre Daten als eine Gruppe, um die Extraktion eindeutiger Werte zu vereinfachen.

4. Im nächsten Schritt wählen Sie den gesamten Datenbereich aus (inklusive der neuen leeren Spalte), klicken Sie auf die Schaltfläche „Hinzufügen“, um Ihre Auswahl in die Liste „Alle Bereiche“ aufzunehmen, und klicken Sie auf „Weiter“.

5. Im letzten Schritt des Assistenten wählen Sie aus, wo Sie die Pivot-Tabelle platzieren möchten (neues Arbeitsblatt oder vorhandenes Blatt), und klicken Sie auf „Fertigstellen“, um den Pivot-Tabellen-Bericht zu generieren.

6. Deaktivieren Sie im neuen Pivot-Tabellen-Bericht alle Felder im Bereich „Felder zum Bericht hinzufügen“, um die Standardansicht zu löschen.

7. Ziehen Sie abschließend das Feld „Wert“ in den Zeilenbereich. Die Pivot-Tabelle zeigt alle eindeutigen Werte aus Ihrem ursprünglichen Mehrspaltenbereich ordentlich in einer einzigen Spalte an.

Eindeutige Werte aus mehreren Spalten mit VBA-Code extrahieren

In Fällen, in denen Sie die Extraktion automatisieren oder große und unregelmäßige Datensätze verarbeiten müssen, kann die Verwendung von VBA-Code (Visual Basic for Applications) eine schnelle und wiederverwendbare Lösung bieten. Dies ist ideal für Benutzer mit grundlegenden Kenntnissen im Excel-VBA-Editor oder für wiederkehrende Aufgaben, bei denen Sie manuelle Operationen minimieren möchten. VBA kann auch große Datenmengen effizienter verarbeiten als Array-Formeln.

1. Öffnen Sie den VBA-Editor, indem Sie Alt + F11 drücken. Klicken Sie im Fenster „Microsoft Visual Basic for Applications“, das erscheint, auf Einfügen > Modul, um ein neues Modul hinzuzufügen.

2. Fügen Sie im neuen Modul den folgenden Code ein:

VBA: Eindeutige Werte aus mehreren Spalten extrahieren

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. Ein Dialogfenster fordert Sie auf, den Datenbereich auszuwählen. Wählen Sie alle relevanten Spalten (einschließlich solcher mit leeren Zellen) aus.

4. Nachdem Sie auf OK geklickt haben, erscheint eine weitere Eingabeaufforderung, die fragt, wo die eindeutigen Werte ausgegeben werden sollen. Geben Sie eine Startzelle an, wo Sie die Ergebnisse auflisten möchten (z.B., E2).

5. Klicken Sie auf OK, und das Makro wird automatisch ausgeführt. Alle eindeutigen Werte erscheinen, beginnend an der von Ihnen angegebenen Stelle.

Weitere relevante Artikel: