Wie kann man in Excel eindeutige Werte verketten?
Beim Arbeiten mit Tabellenkalkulationen ist es üblich, Situationen zu begegnen, in denen Sie nur die einzigartigen Werte aus einer Spalte zusammenführen (verketten) oder Listen erstellen müssen, die eindeutige Einträge mit ihren entsprechenden Datensätzen zusammenfassen. Das Behandeln von Duplikaten und das Darstellen zusammengefasster Informationen bringt nicht nur Ordnung in Ihre Daten, sondern macht Berichte auch klarer und aussagekräftiger. In Excel gibt es mehrere praktische Möglichkeiten, diese Ziele zu erreichen – von der Verwendung integrierter Funktionen bis hin zur Nutzung fortgeschrittener Add-ins oder benutzerdefinierter Code. Dieses Tutorial bietet eine detaillierte Untersuchung verschiedener Methoden, um eindeutige Werte zu verketten und eindeutige Einträge zusammen mit ihren zugehörigen Daten aufzulisten. Die vorgestellten Lösungen richten sich nach verschiedenen Excel-Versionen und Benutzervorlieben und helfen Ihnen, den besten Ansatz für Ihr Szenario auszuwählen.
Nur eindeutige Werte aus einer Spalte verketten
- Durch die Verwendung der Funktionen TEXTVERKETTEN und EINDEUTIG
- Durch die Verwendung von Kutools AI-Assistent
- Durch die Verwendung einer benutzerdefinierten Funktion
- Durch die Verwendung einer erweiterten Excel-Formel (Alternative Lösung)
Liste eindeutiger Werte und verketten entsprechender Werte
- Durch die Verwendung der Funktionen TEXTVERKETTEN und EINDEUTIG
- Durch die Verwendung von Kutools für Excel
- Durch die Verwendung von VBA-Code
- Durch die Verwendung von Excel-PivotTable mit Formeln (Alternative Lösung)
Nur eindeutige Werte aus einer Spalte verketten
Während der Excel-Datenanalyse ist eine häufige Aufgabe, nur die unterschiedlichen Einträge in einer Spalte in eine einzige Zelle zusammenzuführen. Dies kann besonders nützlich sein, um Zusammenfassungsberichte zu erstellen, doppelte Werte in einer Liste zu vermeiden oder Daten für weitere Verarbeitungen vorzubereiten. Die Wahl der Methode hängt von Ihrer Excel-Version, der Größe des Datensatzes und Ihrer Vertrautheit mit Formeln oder Code ab. Die folgenden Methoden decken verschiedene Bedürfnisse ab, heben Überlegungen für jede hervor und bieten praktische Tipps, um eine korrekte Ausführung sicherzustellen.
Methode 1: Verwenden der Funktionen TEXTVERKETTEN und EINDEUTIG
Für Nutzer von Excel365 und Excel2021 macht die Einführung der Funktionen TEXTVERKETTEN und EINDEUTIG das Kombinieren eindeutiger Werte aus einer Spalte sowohl einfach als auch flexibel.
Diese Lösung ist am besten geeignet, wenn Ihre Datenspalte kontinuierlich ist und Sie schnell alle eindeutigen Elemente in eine einzelne Zelle mit einem gewählten Trennzeichen konsolidieren möchten. Es werden Duplikate automatisch entfernt, sie ist leicht nachprüfbar und ermöglicht es Ihnen, den Bereich oder das Trennzeichen bei Bedarf zu ändern. Beachten Sie jedoch, dass dieser Ansatz nur in den neuesten Excel-Versionen verfügbar ist; ältere Versionen unterstützen die EINDEUTIG-Funktion nicht.
Geben Sie in der Zelle, in der Sie das Ergebnis anzeigen möchten, die folgende Formel ein (angenommen, Ihre Daten befinden sich in den Zellen A2:A18):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- EINDEUTIG(A2:A18) filtert doppelte Einträge heraus und gibt nur die eindeutigen Werte aus dem Bereich A2:A18 zurück.
- TEXTVERKETTEN(", ", WAHR, ...) kombiniert (verkettet) diese eindeutigen Werte in eine Zelle, getrennt durch ein Komma und ein Leerzeichen. Das Argument WAHR stellt sicher, dass leere Zellen bei der Verkettung ignoriert werden.
Hilfreiche Tipps und Fehlerbehebung:
- Stellen Sie sicher, dass Ihre Excel-Version die Funktionen EINDEUTIG und TEXTVERKETTEN unterstützt. Wenn Sie einen #NAME?-Fehler sehen, verwenden Sie möglicherweise eine ältere Version.
- Das in TEXTVERKETTEN verwendete Trennzeichen kann nach Belieben geändert werden, zum Beispiel zu "; " oder "|".
- Wenn Sie Daten im ursprünglichen Bereich hinzufügen oder entfernen, wird die Formel automatisch aktualisiert.
- Um unbeabsichtigte zusätzliche Leerzeichen oder Trennzeichen zu vermeiden, überprüfen Sie das Trennzeichen-Argument in der Formel sorgfältig.
Methode 2: Verwenden von Kutools AI-Assistent
Wenn Sie eine schnellere, vollständig automatisierte Möglichkeit benötigen, eindeutige Werte zu verketten – ohne Formeln schreiben zu müssen – bietet das Tool „AI-Assistent“ von Kutools für Excel eine praktische Lösung, die Zeit für Benutzer aller Fähigkeitsstufen spart. Diese Methode ist besonders hilfreich, wenn Sie nicht mit den erweiterten Formeln von Excel vertraut sind oder wenn sich Ihre Daten häufig ändern und wiederholte Aufgaben erforderlich machen.
Nachdem Kutools für Excel installiert wurde, greifen Sie auf diese Funktion zu, indem Sie auf "Kutools" > "AI-Assistent" klicken, um den Bereich „Kutools AI-Assistent“ zu öffnen.
- Wählen Sie die Zellen aus, die die Werte enthalten, die Sie in eine einzelne Zelle kombinieren möchten, und stellen Sie sicher, dass Ihre Auswahl Ihren beabsichtigten Daten entspricht.
- Beschreiben Sie im Chatfeld Ihre Anforderung. Zum Beispiel können Sie tippen:
Verketten Sie eindeutige Werte mit Komma aus dem ausgewählten Bereich und fügen Sie 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 die Operation aus, indem Sie auf „Ausführen“ klicken. Das Ergebnis wird wie beschrieben zurückgegeben.
Hinweise und Tipps:
- Stellen Sie sicher, dass Sie die neueste Version von Kutools ausführen, um auf alle KI-Funktionen zugreifen zu können.
- Seien Sie spezifisch in Ihrem Textbefehl für die besten Ergebnisse; erwähnen Sie Trennzeichen und Zielzelle.
- Kutools KI ist besonders effizient für große Bereiche oder für Workflows, die auf verschiedenen Datensätzen wiederholt werden müssen.
Methode 3: Verwenden einer benutzerdefinierten Funktion
Für Benutzer, die erweiterte Flexibilität benötigen, benutzerdefinierte Trennzeichen oder ein wiederverwendbares Werkzeug für mehrere Arbeitsmappen wollen, ist das Codieren 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 durch die Verfügbarkeit neuer Funktionen eingeschränkt.
- Sie sollten Makros in Ihrer Arbeitsmappe aktivieren.
- Speichern Sie die Datei als „makrofähig“ (.xlsm), falls Sie diesen VBA-Code in Zukunft weiterhin verwenden möchten.
- Regelmäßige Backups Ihrer Arbeitsmappe vor dem Ausführen neuen Codes werden empfohlen.
1. Halten Sie ALT + F11 gedrückt, um das Microsoft Visual Basic for Applications-Fenster zu öffnen.
2. Klicken Sie im VBA-Fenster auf Einfügen > Modul und kopieren Sie dann den folgenden Code:
VBA-Code: Eindeutige Werte in eine einzelne 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. Gehen Sie zurück zu Ihrem Arbeitsblatt und geben Sie in einer leeren Zelle (z. B. C2) die folgende Formel ein:
=ConcatUniq(A2:A18,",")Drücken Sie die Eingabetaste, um zu bestätigen. Die Zelle zeigt alle eindeutigen Werte aus dem angegebenen Bereich an, getrennt durch Kommas.
- Wenn sich Ihr Bereich unterscheidet, passen Sie A2:A18 entsprechend an.
- Wenn ein anderes Trennzeichen benötigt wird, ersetzen Sie "," in der Formel durch Ihr bevorzugtes Symbol (z. B. ";" oder |).
- Wenn Sie einen #NAME?-Fehler erhalten, überprüfen Sie, ob Makros aktiviert sind und der UDF-Name genau übereinstimmt.
Tipp: Um diese Funktion in anderen Arbeitsmappen wiederzuverwenden, kopieren Sie den VBA-Code auch in ihre Module.
Methode 4: Verwenden einer erweiterten Excel-Formel (Alternative Lösung)
In Umgebungen, in denen die EINDEUTIG-Funktion nicht verfügbar ist (z. B. in Excel 2016 oder Excel 2019), können Sie immer noch eindeutige Werte mithilfe einer komplexeren Kombination der klassischen WENN-, ZÄHLENWENN- und TEXTVERKETTEN-Funktionen in Array-Formeln verketten. Diese Methode funktioniert, ist aber aufgrund ihrer Berechnungsbelastung am besten für kleinere Datensätze geeignet.
1. Geben Sie in einer Zielzelle (z. B. C2) die folgende Array-Formel ein (nach der Eingabe drücken Sie 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 um Ihre Formel geschweifte Klammern {} erscheinen, wurde sie korrekt als Array-Formel eingegeben. Die Formel gibt verkettete eindeutige Werte aus dem Bereich A2:A18 zurück, getrennt durch Kommas.
Hinweis: Bei dieser Methode müssen Sie die Bereiche an Ihre Daten anpassen. Für sehr große Bereiche kann die Berechnungszeit zunehmen. Wenn Sie sich mit Array-Formeln nicht wohl fühlen, ziehen Sie in Erwägung, die oben beschriebenen VBA- oder Add-in-Lösungen auszuprobieren.
Liste eindeutiger Werte und verketten entsprechender Werte
Häufig möchten Sie bei der Datenberichterstattung nicht nur die eindeutigen Werte aus einer Spalte extrahieren, sondern auch die ihnen entsprechenden Einträge aus einer anderen Spalte aggregieren oder zusammenführen. Zum Beispiel alle verkauften Produkte eines jeden Verkäufers zusammenfassen oder alle Einträge verknüpfen, die mit derselben ID verknüpft sind. Die Wahl der richtigen Methode hängt von der Komplexität Ihrer Daten und davon ab, ob Automatisierung, Benutzerfreundlichkeit oder Kompatibilität Ihre Priorität ist.
Methode 1: Verwenden der Funktionen TEXTVERKETTEN und EINDEUTIG
Wenn Sie Excel 365 oder Excel 2021 verwenden, können Sie die Funktionen EINDEUTIG und FILTER mit TEXTVERKETTEN für einen robusten, vollständig formelgesteuerten Ansatz kombinieren. Diese Methode eignet sich gut, um Daten zu summieren, wo ein Wert möglicherweise mit mehreren Datensätzen zusammenhängt, und Sie möchten eine durch Trennzeichen getrennte Liste dieser verbundenen Datensätze.
1. Geben Sie in einer leeren Spalte die folgende Formel ein, um alle eindeutigen Werte aus Spalte A aufzulisten:
=UNIQUE(A2:A17)
2. Um nun die entsprechenden Werte aus Spalte B für jeden eindeutigen Eintrag zu verketten, geben Sie in der nächsten Spalte neben Ihrem eindeutigen Wert (z. B. E2, wenn Ihre eindeutigen Werte bei D2 beginnen) diese Formel ein und ziehen Sie sie nach unten, sofern nötig:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- EINDEUTIG(A2:A17) erstellt ein Array von eindeutigen Elementen aus Spalte A.
- FILTER(B2:B17, A2:A17 = D2) generiert ein Array, das alle entsprechenden Werte aus Spalte B für jeden eindeutigen Wert in D2 enthält.
- TEXTVERKETTEN(", ", WAHR, ...) verbindet diese entsprechenden Werte, getrennt durch Kommas.
- Wenn 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 für fehlende Übereinstimmungen zurückgibt.
- Dieser Ansatz aktualisiert Ergebnisse automatisch, wenn sich Ihre Daten ändern, was ihn für dynamische Zusammenfassungstabellen geeignet macht.
Methode 2: Verwenden von Kutools für Excel
Kutools für Excel verfügt über ein „Erweiterte Zeilen zusammenführen“-Tool, das speziell für das Gruppieren von Daten nach eindeutigen Werten und das Zusammenführen entsprechender Werte mit Ihrem gewünschten Trennzeichen entwickelt wurde. Dies ist für Benutzer geeignet, die eine grafische Lösung wünschen und nicht mit dem Schreiben von Formeln oder Code vertraut sind. Es ist besonders wertvoll, wenn Sie große Datensätze bearbeiten oder häufige Neugruppierungen benötigen, wie bei regelmäßigen Berichten oder laufender Datenpflege.
Bevor Sie Änderungen vornehmen, ist es eine gute Praxis, Ihre Daten zu sichern, indem Sie die Originaldaten an einen anderen Ort kopieren. Folgen Sie dann diesen Schritten:
- Wählen Sie den Datenbereich aus, den Sie organisieren möchten.
- Navigieren Sie zu "Kutools" > "Zusammenführen & Teilen" > "Erweiterte Zeilen zusammenführen", wie unten dargestellt:
- Im sich öffnenden Dialogfeld:
- Wählen Sie die Spalte mit Duplikaten aus, die zusammengeführt werden sollen, und legen Sie sie als „Primärschlüssel“ in der „Operation“-Spalte fest.
- Wählen Sie die Spalte aus, die Sie aggregieren möchten (die Werte zu verketten); geben Sie Ihr bevorzugtes Trennzeichen in der Dropdown-Liste unter „Operation“ an.
- Klicken Sie auf OK, um auszuführen.
Ergebnis:
Kutools wird Ihre Daten neu organisieren, eindeutige Einträge extrahieren und alle zugehörigen Werte basierend auf Ihrer Einstellung verketten.
- Wenn Sie einen Fehler gemacht haben, verwenden Sie die Rückgängig-Funktion in Excel (Strg+Z), um rückgängig zu machen.
- Der Prozess funktioniert für Datensätze mit potenziell Hunderten oder Tausenden von Datensätzen und unterstützt eine Vielzahl von Trennzeichen.
Methode 3: Verwenden 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 besonders gut für benutzerdefinierte Workflows, Automatisierung oder wenn Funktionen wie EINDEUTIG oder FILTER nicht verfügbar sind. Wenn sich Ihre Datenstruktur häufig ändert, kann diese VBA-Lösung leicht angepasst werden.
Um den folgenden Code zu verwenden, folgen Sie einfach diesen Schritten:
1. Drücken Sie ALT + F11, um den VBA-Editor zu öffnen.
2. Gehen Sie zu Einfügen > Modul und fügen Sie dann den folgenden Code in das geöffnete Modulfenster ein:
VBA-Code: Liste eindeutiger Werte und verketten entsprechender Daten
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 wird Sie auffordern, den Datenbereich auszuwählen. Stellen Sie sicher, dass Sie genau zwei Spalten auswählen: die erste für eindeutige Werte und die zweite für entsprechende Werte.
4. Klicken Sie auf OK und wählen Sie die erste Zelle aus, wo die Ergebnistabelle beginnen soll.
5. Nachdem Sie auf OK geklickt haben, wird der Code eine Tabelle generieren, die nur eindeutige Werte und ihre zugehörigen verketteten Daten enthält.
- Wenn Sie einen Fehler bezüglich der Anzahl der Spalten erhalten, überprüfen Sie, dass Ihre Auswahl nur zwei Spalten umfasst.
- Wenn Ihr Trennzeichen von einem Komma zu einem anderen 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)bei Bedarf an. - Sichern Sie Ihre Datei immer, bevor Sie neue VBA-Skripte ausführen.
Zusammengefasst bietet Excel eine Vielzahl von Ansätzen zum Verketten eindeutiger Werte und Zusammenfassen zugehöriger Daten. Formelmethoden sind schnell und dynamisch in modernem Excel, während VBA- und Kutools-Lösungen größere Kompatibilität und mehr Kontrolle bieten. Wählen Sie immer die Methode, die zu Ihrer Datengröße, Ihrer Excel-Version und Ihrem bevorzugten Workflow passt. Denken Sie daran, an Beispieldaten zu testen oder Ihre Arbeit zu sichern, insbesondere bei skriptbasierten oder Batch-Vorgängen. Für weitere Excel-Anleitungen und fortgeschrittene Tipps, erkunden Sie unsere vollständige Tutorialsammlung.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins