Wie kann man Text einfach basierend auf Kriterien in Excel verketten?

Das Verketten von Text basierend auf spezifischen Kriterien in Excel ist eine wertvolle Technik, die die Datenanalyse optimiert, insbesondere bei der Bearbeitung großer oder komplexer Datensätze. Diese Operation ermöglicht es Benutzern, Zellwerte entsprechend einer Bedingung oder einem Schlüssel zusammenzuführen, wodurch Berichte übersichtlicher werden und tiefere Einblicke in gruppierte Daten erleichtert werden. Angenommen, Sie möchten zum Beispiel alle Namen auflisten, die mit jedem Land verbunden sind; das Verketten von Text nach Kriterien kann dies prägnant erreichen. Im Folgenden finden Sie mehrere praktische Methoden, um dies in Excel zu erreichen, die für verschiedene Versionen und Benutzerpräferenzen geeignet sind.

• Text basierend auf Kriterien mit der TEXTJOIN-Funktion verketten (Excel 365, 2019 und höher)
• Text basierend auf Kriterien mit Kutools für Excel verketten (Alle Versionen)
• Text basierend auf Kriterien mit FILTER und TEXTJOIN verketten (Alternative für 365/2021+)
• Text basierend auf Kriterien mit benutzerdefinierter Funktion verketten (Alle Versionen)

Text basierend auf Kriterien mit der TEXTJOIN-Funktion verketten

Die TEXTJOIN-Funktion, in Kombination mit der WENN-Funktion, bietet eine effiziente Lösung, um Text basierend auf einer bestimmten Bedingung in Excel zu verketten. Diese Methode ist in Excel 2019, Excel 2021 und Excel 365 verfügbar. Sie ist besonders nützlich, wenn Sie Werte zusammenführen möchten, wo ein bestimmtes Kriterium erfüllt ist, wie zum Beispiel das Gruppieren aller Namen, die zu einem bestimmten Land gehören. Der Ansatz ist dynamisch und passt sich leicht an Änderungen in Ihren Daten an.

Ein Vorteil dieser Technik ist ihre Geschwindigkeit und formelbasierte Flexibilität, was sie ideal für Echtzeit-Datenaktualisierungen oder große Datensätze macht. Es sollte jedoch beachtet werden, dass diese Funktion nur in neueren Versionen von Excel verfügbar ist. Wenn Sie eine ältere Version verwenden, sollten Sie die unten aufgeführten Alternativen in Betracht ziehen.

1. Geben Sie die folgende Formel in die Zielformel ein (z. B. E2):

=TEXTJOIN(", ", TRUE, IF($A$2:$A$15=D2, $B$2:$B$15, ""))

2. In Excel 2019 drücken Sie nach dem Eingeben der Formel die Tastenkombination Strg + Umschalt + Eingabetaste um es als Array-Formel anzuwenden. In Excel 365/2021 und später drücken Sie einfach Eingabetaste. Dann ziehen Sie den Ausfüllkursor nach unten, um die Formel für andere Zellen auszufüllen, die verschiedene Kriterien darstellen. Siehe Screenshot unten:

Tipp: Wenn Sie mit sehr großen Datensätzen arbeiten, kann das Aktivieren des manuellen Berechnungsmodus oder die Begrenzung des Berechnungsbereichs helfen, die Leistung zu erhalten. Überprüfen Sie Ihre Bereiche sorgfältig, damit sie vollständig übereinstimmen; andernfalls kann die Formel Fehler oder unerwartete Ergebnisse zurückgeben.

Wenn Ihr Excel TEXTJOIN nicht unterstützt oder Sie mehr Flexibilität benötigen (z. B. Fehler ignorieren, unterschiedliche Trennzeichen verwenden oder Leerzeichen handhaben), lesen Sie weiter für allgemeinere Methoden.

Text basierend auf Kriterien mit Kutools für Excel verketten

Für Benutzer, deren Excel-Version die "TEXTJOIN"-Funktion nicht bereitstellt oder die eine interfacegesteuerte Herangehensweise gegenüber Formeln bevorzugen, bietet Kutools für Excel eine einfache Lösung zum Verketten von Text basierend auf Kriterien. Diese Methode eliminiert die Notwendigkeit komplexer Formeln und eignet sich für alle Excel-Versionen, was sie einer breiten Benutzerbasis zugänglich macht. Kutools integriert sich nahtlos in Excel und ist ideal für Benutzer, die Zuverlässigkeit und Zeitersparnis in ihrem täglichen Workflow schätzen.

Um dieses Feature zu verwenden, installieren Sie zunächst Kutools für Excel. Sobald es installiert ist, gehen Sie wie folgt vor:

1. Öffnen Sie Ihr Arbeitsblatt, dann gehen Sie zur Registerkarte "Kutools", wählen Sie "Erweiterte SVERWEIS", gefolgt von "Ein-zu-viele-Suche (mehrere Ergebnisse zurückgeben)".
2. Im erscheinenden Dialogfeld legen Sie den "Platzierungsbereich der Liste" fest, in dem die Ergebnisse angezeigt werden, und die "Bereich der zu durchsuchenden Werte", die Ihre eindeutigen Schlüssel oder Kriterien sind.
3. Wählen Sie den gesamten Datensatz als Quelltabelle aus.
4. Legen Sie die "Schlüsselspalte" (die Spalte, die Ihr Kriterium enthält, z. B. Land) und die "Rückgabespalte" (die Spalte, die die zu verkettenden Werte enthält, z. B. Namen) mit den bereitgestellten Dropdown-Menüs fest.
5. Klicken Sie auf "OK", um die Operation auszuführen.
   

Ergebnis: Kutools wird sofort den relevanten Text basierend auf Ihren angegebenen Kriterien verketten und die Ergebnisse im Ausgabebereich anzeigen, wie unten gezeigt:

Vorteile der Verwendung von Kutools:

• Anwendbar auf jede Excel-Version
• Keine Eingabe von Formeln oder Array-Operationen erforderlich
• Reduziert das Risiko von Fehlern durch komplexe Zellbezüge
• Effizient für Benutzer, die mit fortgeschrittenen Formeln nicht vertraut sind

Text basierend auf Kriterien mit FILTER und TEXTJOIN-Funktionen verketten (Excel 365/2021 oder spätere Alternative)

In Excel 365 oder Excel 2021 und höher bietet die Kombination von FILTER und TEXTJOIN eine weitere effiziente und dynamische Möglichkeit, Textwerte basierend auf einer Bedingung zu verketten. Dies ist besonders praktisch für Benutzer, die Array-Formeln vermeiden möchten, aber dennoch die neuesten Excel-Funktionen nutzen möchten.

1. Geben Sie in der Ergebniszelle (z. B. E2) die folgende Formel ein:

=TEXTJOIN(", ", TRUE, FILTER(B$2:B$15, A$2:A$15=D2))

2. Drücken Sie die Eingabetaste, um die Formel zu bestätigen, dann ziehen Sie den Ausfüllkursor nach unten für so viele Zeilen, wie Sie benötigen.

Diese Methode vermeidet die Notwendigkeit von Strg + Umschalt + Eingabe und passt sich automatisch an, wenn sich Daten ändern, was sie für dynamische Tabellen sehr robust macht. In der obigen Formel ist B$2:B$15 die zu verknüpfende Spalte, A$2:A$15 ist der Kriterienbezug und D2 ist der spezifische Kriterienwert. Passen Sie die Bereiche je nach Bedarf für Ihren Datensatz an. Beachten Sie, dass FILTER eine kompatible Version von Excel erfordert.

Text basierend auf Kriterien mit benutzerdefinierter Funktion verketten

Für Benutzer, die nach einem individuellen, flexiblen Ansatz suchen – insbesondere in Excel-Versionen, die keine dynamischen Array-Funktionen haben – bietet eine benutzerdefinierte Funktion (UDF) in VBA eine robuste Lösung, um Text basierend auf bestimmten Kriterien zu verketten. Diese Methode ermöglicht es Ihnen, das Trennzeichen oder die Logik nach Bedarf anzupassen und ist in Legacy-Excel-Umgebungen oder für spezifische benutzerdefinierte Workflows sehr praktisch. Allerdings erfordert sie grundlegende Kenntnisse im Excel-VBA-Editor und in Makro-Sicherheitseinstellungen.

1. Drücken Sie die ALT + F11-Tasten, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie im VBA-Fenster auf "Einfügen" > "Modul". Kopieren und fügen Sie den folgenden Code in das Modulpanel ein, das erscheint:

VBA-Code: Text basierend auf Kriterien verketten

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Nachdem Sie den Code eingefügt haben, speichern und schließen Sie das Codefenster. Kehren Sie zu Ihrem Excel-Arbeitsblatt zurück. Um diese UDF zu verwenden, geben Sie eine Formel wie die folgende in Zelle E2 ein und ziehen Sie den Ausfüllkursor nach unten für andere Werte:

=CONCATENATEIF($A$2:$A$15, D2, $B$2:$B$15, ",")

Hinweis: In der obigen Formel zeigt A2:A15 den Bereich mit Kriterien an (z. B. Länder), D2 ist das zu treffende Kriterium und B2:B15 ist der zu verknüpfende Textrahmen. Das Trennzeichen ist standardmäßig ein Komma (",") kann aber in jedes beliebige Zeichen oder Zeichenfolge geändert werden. Stellen Sie sicher, dass Ihre Kriterien und zu verknüpfenden Bereiche gleich groß sind, sonst gibt die Funktion einen Fehler zurück.

Tipps für UDFs:

• Makros müssen aktiviert sein, damit UDFs funktionieren.
• UDFs aktualisieren sich nicht automatisch, wenn sich Quelldaten ändern – drücken Sie F9, um bei Bedarf zu aktualisieren.

Dieser auf VBA basierende Ansatz ist am besten für Benutzer geeignet, die mit Makros vertraut sind oder wenn native Excel-Funktionen nicht verfügbar sind. Die Flexibilität, den VBA-Code zu ändern, unterstützt fortschrittliche Verkettungslogik oder benutzerdefinierte Formate.

Wenn Sie die am besten geeignete Methode zum Verketten von Text nach Kriterien in Excel auswählen, berücksichtigen Sie Ihre Excel-Version, die Häufigkeit der Operation (einmalig vs. wiederkehrend) und Ihre Vertrautheit mit Formeln oder Add-ins. Wenn Sie Formelfehler begegnen, überprüfen Sie, dass Ihre Bereiche übereinstimmen und alle Referenzen auf die vorgesehenen Zeilen oder Spalten zeigen. Für Lösungen mit VBA stellen Sie sicher, dass Makros aktiviert sind, und für Kutools überprüfen Sie die erfolgreiche Installation. Wenn die Ergebnisse unerwartet oder leer sind, bestätigen Sie, dass tatsächlich Daten vorhanden sind, die den ausgewählten Kriterien entsprechen. Die Kombination dieser Ansätze in Ihrem täglichen Workflow kann die Produktivität erheblich steigern, saubere Datenzusammenfassungen gewährleisten und manuelle Bearbeitungen reduzieren. Wenn Sie Hilfe bei anderen Datenverarbeitungsaufgaben benötigen, besuchen Sie unser Tutorial-Center für weitere Excel-Einblicke.