Wie verknüpft man eindeutige Werte in Excel?
Beim Arbeiten mit Tabellenkalkulationen müssen Sie oft nur die eindeutigen Werte einer Spalte zusammenführen (verketten) oder Listen erstellen, die eindeutige Einträge mit ihren zugehörigen Datensätzen übersichtlich bündeln. Der gezielte Umgang mit Duplikaten und die klare Darstellung zusammengefasster Informationen bereinigt nicht nur Ihre Daten, sondern macht Ihre Berichte auch präziser und aussagekräftiger. Excel bietet hierfür gleich mehrere praktische Lösungswege – von integrierten Funktionen über leistungsstarke Add-Ins bis hin zu benutzerdefiniertem Code. Dieses Tutorial liefert eine detaillierte Übersicht über verschiedene Methoden, um eindeutige Werte zu verketten oder eindeutige Einträge mitsamt ihrer zugehörigen Daten aufzulisten. Die vorgestellten Ansätze berücksichtigen unterschiedliche Excel-Versionen und Benutzerpräferenzen – so finden Sie stets die beste Lösung für Ihr konkretes Szenario.
Nur eindeutige Werte aus einer Spalte verketten
- Durch Verwendung der Funktionen TEXTVERKETTEN und EINDEUTIG
- Durch Verwendung von KUTOOLS AI Aide
- Durch Verwendung einer benutzerdefinierten Funktion
- Durch Verwendung einer erweiterten Excel-Formel (alternative Lösung)
Eindeutige Werte auflisten und zugehörige Werte verketten
- Durch Verwendung der Funktionen TEXTVERKETTEN und EINDEUTIG
- Durch Verwendung von Kutools für Excel
- Durch Verwendung von VBA-Code
- Durch Verwendung einer Excel-PivotTable mit Formeln (alternative Lösung)
Nur eindeutige Werte aus einer Spalte verketten
Bei der Arbeit mit Excel-Datenanalyse gehört es oft dazu, die eindeutigen Einträge einer Spalte in einer einzigen Zelle zusammenzuführen. Das ist besonders hilfreich, um prägnante Zusammenfassungsberichte zu erstellen, doppelte Werte in einer Liste zu vermeiden oder Daten für nachfolgende Verarbeitungsschritte optimal vorzubereiten. Die passende Methode hängt von Ihrer Excel-Version, der Größe Ihres Datensatzes und Ihrem Umgang mit Formeln oder Code ab. Die folgenden Ansätze berücksichtigen unterschiedliche Anforderungen, heben entscheidende Aspekte hervor und liefern praktische Tipps für eine fehlerfreie Umsetzung.
Methode 1: Verwendung der Funktionen TEXTVERKETTEN und EINDEUTIG
Für Benutzer von Excel 365 und Excel 2021 vereinfachen die Funktionen TEXTVERKETTEN und EINDEUTIG das Zusammenführen eindeutiger Werte aus einer Spalte erheblich und bieten dabei maximale Flexibilität.
Diese Lösung eignet sich ideal, wenn Ihre Datenspalte lückenlos ist und Sie alle eindeutigen Elemente schnell mit einem selbst gewählten Trennzeichen in einer einzigen Zelle zusammenfassen möchten. Duplikate werden automatisch entfernt, die Lösung ist leicht nachvollziehbar und ermöglicht Ihnen, bei Bedarf den Bereich oder das Trennzeichen anzupassen. Beachten Sie jedoch, dass dieser Ansatz nur in den neuesten Excel-Versionen verfügbar ist – ältere Versionen unterstützen die Funktion EINDEUTIG nicht.
Geben Sie in der Zelle, in der das Ergebnis angezeigt werden soll, die folgende Formel ein (angenommen, Ihre Daten befinden sich in den Zellen A2:A18):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18)filtert doppelte Einträge heraus und gibt ausschließlich die eindeutigen Werte aus dem Bereich A2:A18 zurück.
- TEXTJOIN(", ", TRUE, ...)verknüpft (verkettet) diese eindeutigen Werte in einer Zelle, getrennt durch Komma und Leerzeichen. Das Argument WAHR stellt sicher, dass etwaige leere Zellen bei der Verkettung ignoriert werden.
Hilfreiche Tipps und Fehlerbehebung:
- Stellen Sie sicher, dass Ihre Excel-Version die Funktionen EINDEUTIG und TEXTVERKETTENunterstützt. Sollten Sie einen #NAME?– Fehler erhalten? Möglicherweise verwenden Sie eine ältere Version.
- Der in TEXTVERKETTEN verwendete Trennzeichen lässt sich beliebig anpassen – beispielsweise auf „; “ oder „|“.
- Wenn Sie Daten im ursprünglichen Bereich hinzufügen oder entfernen, aktualisiert sich die Formel automatisch.
- Um unbeabsichtigte zusätzliche Leerzeichen oder Trennzeichen zu vermeiden, überprüfen Sie sorgfältig das Trennzeichenargument in der Formel.
Methode 2: Verwendung von KUTOOLS AI Aide
Wenn Sie eine schnellere, vollautomatische Möglichkeit suchen, eindeutige Werte zu verketten – ganz ohne Formeln schreiben zu müssen – bietet der „AI-Assistent“ von Kutools für Excel eine praktische Lösung, die Zeit spart und sich ideal für Benutzer aller Erfahrungsstufen eignet. Diese Methode ist besonders hilfreich, wenn Sie mit den erweiterten Excel-Formeln nicht vertraut sind oder wenn sich Ihre Daten häufig ändern und wiederkehrende Aufgaben anfallen.
Nachdem Kutools für Excel installiert wurde, öffnen Sie den Bereich „KUTOOLS AI Aide“, indem Sie auf „Kutools“ > „AI-Assistent“ klicken.
- Wählen Sie die Zellen aus, die die Werte enthalten, die Sie in einer einzigen Zelle kombinieren möchten, und stellen Sie sicher, dass Ihre Auswahl Ihren beabsichtigten Daten entspricht.
- Beschreiben Sie Ihre Anforderung im Chatfeld. Geben Sie beispielsweise Folgendes ein:
Verkette eindeutige Werte mit Komma aus einem ausgewählten Bereich und füge das kombinierte Ergebnis in Zelle C2 ein - Drücken Sie die Eingabetaste oder klicken Sie auf die „Senden“-Schaltfläche. Die KI analysiert Ihre Anfrage und führt nach der Verarbeitung – durch einen Klick auf „Ausführen“ – die Operation mit Kutools durch. Das Ergebnis wird wie beschrieben zurückgegeben.
Hinweise und Tipps:
- Stellen Sie sicher, dass Sie die neueste Version von Kutools verwenden, um alle KI-Funktionen nutzen zu können.
- Formulieren Sie Ihren Textbefehl präzise, um optimale Ergebnisse zu erzielen – geben Sie dabei explizit das Trennzeichen und die Zielzelle an.
- KUTOOLS AI ist besonders effizient bei großen Bereichen oder bei Arbeitsabläufen, die für verschiedene Datensätze wiederholt werden müssen.
Methode 3: Verwendung einer benutzerdefinierten Funktion
Für Benutzer, die maximale Flexibilität benötigen, benutzerdefinierte Trennzeichen verwenden möchten oder ein wiederverwendbares Werkzeug für mehrere Arbeitsmappen erstellen wollen, ist das Programmieren einer benutzerdefinierten Funktion (UDF) in VBA eine effektive Möglichkeit, automatisch eindeutige Werte zu verketten. Diese VBA-Lösung ist mit allen Excel-Versionen kompatibel und nicht von der Verfügbarkeit neuer Funktionen abhängig.
- Aktivieren Sie die Makros in Ihrer Arbeitsmappe.
- Speichern Sie die Datei als „makrofähige Arbeitsmappe“ ().xlsm), wenn Sie diesen VBA-Code künftig weiter verwenden möchten.
- Regelmäßige Sicherungskopien Ihrer Arbeitsmappe vor dem Ausführen neuer Codeabschnitte werden empfohlen.
1. Halten Sie ALT + F11 gedrückt, um das Fenster Microsoft Visual Basic für Applikationen zu öffnen.
2. Klicken Sie im VBA-Fenster auf Einfügen > Modul, und fügen Sie anschließend den folgenden Code ein:
VBA-Code: Eindeutige Werte in einer einzigen Zelle verketten:
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Kehren Sie zu Ihrem Arbeitsblatt zurück und geben Sie in eine leere Zelle (z. B. C2) die folgende Formel ein:
=ConcatUniq(A2:A18,",")Drücken Sie Enter, um zu bestätigen. Die Zelle zeigt alle eindeutigen Werte aus dem begrenzten Bereich, durch Kommas getrennt, an.
- Falls Ihr Bereich abweicht, passen Sie A2:A18 entsprechend an.
- Falls ein anderes Trennzeichen erforderlich ist, ersetzen Sie das ","in der Formel durch Ihr bevorzugtes Symbol (z. B.)";" oder |).
- Falls Sie einen #NAME?-Fehler erhalten, überprüfen Sie, ob Makros aktiviert sind und der Name der benutzerdefinierten Funktion exakt übereinstimmt.
Tipp: Um diese Funktion in anderen Arbeitsmappen wiederzuverwenden, kopieren Sie den VBA-Code ebenfalls in deren Module.
Methode 4: Verwendung einer erweiterten Excel-Formel (alternative Lösung)
In Umgebungen, in denen die UNIQUE-Funktion nicht verfügbar ist (z. B. in Excel 2016 oder Excel 2019), können Sie eindeutige Werte dennoch mithilfe einer komplexeren Kombination der klassischen WENN-, ZÄHLENWENN- und TEXTVERKETTEN-Funktionen als Matrixformel zusammenfügen. Dieser Ansatz funktioniert zwar, eignet sich jedoch aufgrund des hohen Rechenaufwands am besten für kleinere Datensätze.
1. Geben Sie in einer Zielzelle (z. B. C2) die folgende Matrixformel ein – und drücken Sie anschließend Strg+Umschalt+Eingabe statt nur Eingabe:
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. Wenn geschweifte Klammern {} um Ihre Formel erscheinen, wurde sie korrekt als Matrixformel eingegeben. Die Formel gibt die zusammengefügten, eindeutigen Werte aus dem Bereich A2:A18 – durch Kommas getrennt – zurück.
Hinweis: Bei dieser Methode müssen Sie die Bereiche an Ihre Daten anpassen. Bei sehr großen Bereichen kann sich die Berechnungszeit verlängern. Falls Sie mit Matrixformeln nicht vertraut sind, empfehlen wir Ihnen, die oben beschriebenen VBA- oder Add-in-Lösungen in Betracht zu ziehen.
Eindeutige Werte auflisten und zugehörige Werte verketten
In der Datenberichterstattung möchten Sie oft nicht nur eindeutige Werte aus einer Spalte extrahieren, sondern auch die zugehörigen Einträge aus einer anderen Spalte aggregieren oder zusammenführen – beispielsweise alle von einem Verkäufer verkauften Produkte konsolidieren oder sämtliche Einträge erfassen, die derselben ID zugeordnet sind. Die Wahl der passenden Methode richtet sich nach der Komplexität Ihrer Daten sowie danach, ob Automatisierung, Benutzerfreundlichkeit oder Kompatibilität im Vordergrund steht.
Methode 1: Verwendung der TEXTVERKETTEN- und UNIQUE-Funktionen
Wenn Sie Excel 365 oder Excel 2021 verwenden, können Sie die Funktionen UNIQUE und FILTER mit TEXTVERKETTEN kombinieren, um einen robusten, vollständig formelbasierten Ansatz zu erhalten. Diese Methode eignet sich gut zur Zusammenfassung von Daten, bei denen ein Wert mehreren Datensätzen zugeordnet ist und Sie eine durch Trennzeichen getrennte Liste dieser zugehörigen Datensätze erhalten möchten.
1. Geben Sie in einer leeren Spalte die folgende Formel ein, um alle eindeutigen Werte aus Spalte A aufzulisten:
=UNIQUE(A2:A17)
2. Um die zugehörigen Werte aus Spalte B für jeden eindeutigen Eintrag zu summieren, geben Sie in der nächsten Spalte neben Ihrem eindeutigen Wert (z. B. in E2, wenn Ihre eindeutigen Werte ab D2 beginnen) die folgende Formel ein und ziehen Sie sie bei Bedarf nach unten:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17) erstellt ein Array mit eindeutigen Elementen aus Spalte A.
- FILTER(B2:B17, A2:A17 = D2) generiert ein Array, das alle zugehörigen Werte aus Spalte B für jeden eindeutigen Wert in D2 enthält.
- TEXTJOIN(", ", TRUE, ...)verknüpft diese zugehörigen Werte, durch Kommas getrennt.
- Falls Sie ein anderes Trennzeichen benötigen, ändern Sie ", " in TEXTVERKETTEN entsprechend.
- Um Fehler zu vermeiden, stellen Sie sicher, dass die Bereiche in Ihren Formeln gleich lang sind und dass FILTER keine Fehler aufgrund fehlender Übereinstimmungen zurückgibt.
- Dieser Ansatz aktualisiert Ergebnisse automatisch, sobald sich Ihre Daten ändern, und eignet sich daher ideal für dynamische Zusammenfassungstabellen.
Methode 2: Verwendung von Kutools für Excel
Kutools für Excel bietet ein speziell für die Gruppierung von Daten nach eindeutigen Werten und das Zusammenführen zugehöriger Werte mit Ihrem gewünschten Trennzeichen entwickeltes Werkzeug namens „Erweiterte Zeilen zusammenführen“. Diese Lösung eignet sich für Anwender, die eine grafische Oberfläche bevorzugen und weder Formeln noch Code schreiben möchten. Sie ist besonders wertvoll bei großen Datensätzen oder wenn häufige Neugruppierungen erforderlich sind, beispielsweise bei periodischen Berichten oder fortlaufender Datenpflege.
Bevor Sie Änderungen vornehmen, empfiehlt es sich, Ihre Daten durch Kopieren der Originaldaten an einen anderen Ort zu sichern. Gehen Sie dann wie folgt vor:
- Wählen Sie den Datenbereich aus, den Sie strukturieren möchten.
- Navigieren Sie zu „Kutools“ > „Vereinigen/Aufteilen“ > „Erweiterte Zeilen zusammenführen“, wie unten dargestellt:
- Im sich öffnenden Dialogfeld:
- Wählen Sie die Spalte mit den zu zusammenzuführenden Duplikaten aus und legen Sie sie in der Spalte „Operation“ als „Primärschlüssel“ fest.
- Wählen Sie die Spalte aus, deren Werte Sie aggregieren (d. h. verketten) möchten, und legen Sie im Dropdown-Menü unter „Operation“ Ihr bevorzugtes Trennzeichen fest.
- Klicken Sie auf OK, um die Aktion auszuführen.
Ergebnis:
Kutools bringt Ihre Daten neu in Ordnung, extrahiert eindeutige Einträge und fügt alle zugehörigen Werte gemäß Ihren Einstellungen zusammen.
- Sollten Sie einen Fehler machen, nutzen Sie einfach die Rückgängig-Funktion in Excel (Strg+Z), um Ihre Änderung sofort rückgängig zu machen.
- Der Prozess funktioniert auch bei Datensätzen mit potenziell Hunderten oder Tausenden von Datensätzen und unterstützt eine Vielzahl von Trennzeichen.
Methode 3: Verwendung von VBA-Code
Die Verwendung eines VBA-Skripts bietet volle Kontrolle darüber, wie Sie Daten extrahieren und zusammenfassen. Dieser Ansatz ist mit allen Excel-Versionen kompatibel und eignet sich besonders gut für benutzerdefinierte Workflows, Automatisierung oder wenn Funktionen wie UNIQUE oder FILTER nicht verfügbar sind. Falls sich Ihre Datenstruktur häufig ändert, lässt sich diese VBA-Lösung leicht anpassen.
Um den folgenden Code zu verwenden, gehen Sie einfach wie folgt vor:
1. Drücken Sie ALT + F11, um den VBA-Editor zu öffnen.
2. Wählen Sie Einfügen > Modul und fügen Sie den folgenden Code in das geöffnete Modulfenster ein:
VBA-Code: Eindeutige Werte auflisten und zugehörige Daten zusammenfügen
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Drücken Sie F5, um das Skript auszuführen. Ein Pop-up fordert Sie auf, den Datenbereich auszuwählen. Achten Sie darauf, genau zwei Spalten zu markieren: die erste für die eindeutigen Werte und die zweite für die zugehörigen Werte.
4. Klicken Sie auf OK und wählen Sie die erste Zelle aus, in der die Ergebnistabelle beginnen soll.
5. Nachdem Sie auf OK geklickt haben, generiert der Code eine Tabelle mit ausschließlich eindeutigen Werten und den zugehörigen zusammengeführten Daten.
- Sollten Sie eine Fehlermeldung zur Anzahl der Spalten erhalten, stellen Sie sicher, dass Ihre Auswahl genau zwei Spalten umfasst.
- Falls Ihr Trennzeichen von einem Komma auf ein anderes Symbol geändert werden muss, passen Sie den Code in der Zeile
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)entsprechend an. - Erstellen Sie stets eine Sicherungskopie Ihrer Datei, bevor Sie neue VBA-Skripte ausführen.
Zusammenfassend bietet Excel verschiedene Ansätze, um eindeutige Werte zusammenzuführen und zugehörige Daten zu konsolidieren. Formelbasierte Methoden sind in modernem Excel schnell und dynamisch, während VBA- und Kutools-Lösungen breitere Kompatibilität und mehr Kontrolle bieten. Wählen Sie stets die Methode, die am besten zu Ihrer Datenmenge, Ihrer Excel-Version und Ihrem bevorzugten Workflow passt. Denken Sie daran, skriptbasierte oder stapelverarbeitete Vorgänge stets zunächst an Beispieldaten zu testen oder ein Backup anzulegen. Für weitere Excel-Anleitungen und fortgeschrittene Tipps erkunden Sie unsere vollständige Tutorial-Sammlung.
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