Wie kann man Text in Excel einfach anhand von Kriterien verketten?
Das Verketten von Text basierend auf bestimmten Kriterien in Excel ist eine wertvolle Technik, die die Datenanalyse – besonders bei großen oder komplexen Datensätzen – effizienter und aussagekräftiger macht. Mithilfe dieses Vorgangs können Benutzer Zellwerte gezielt nach einer Bedingung oder einem Schlüssel zusammenführen, um übersichtlichere Berichte zu erstellen und tiefere Einblicke in gruppierte Daten zu gewinnen. Stellen Sie sich vor, Sie möchten alle Namen auflisten, die einem bestimmten Land zugeordnet sind: Das bedingte Verketten von Text ermöglicht genau das – klar, präzise und auf einen Blick. Im Folgenden finden Sie mehrere praktische Methoden, um dies in Excel umzusetzen – passend für unterschiedliche Versionen und individuelle Vorlieben.
- Text basierend auf Kriterien mit der TEXTJOIN-Funktion verketten (Excel 365,2019 und höher)
- Text basierend auf Kriterien mit Kutools für Excel (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 Kombination aus TEXTJOIN- und WENN-Funktion bietet eine effiziente Lösung, um Text in Excel basierend auf einer bestimmten Bedingung zu verketten. Diese Methode steht in Excel 2019, Excel 2021 und Excel 365 zur Verfügung und eignet sich ideal, um Werte zusammenzuführen, die ein bestimmtes Kriterium erfüllen – beispielsweise alle Namen eines bestimmten Landes zu gruppieren. Der Ansatz ist dynamisch und passt sich mühelos an Änderungen Ihrer Daten an.
Ein entscheidender Vorteil dieser Technik liegt in ihrer Geschwindigkeit und formelbasierten Flexibilität – ideal für Echtzeit-Datenaktualisierungen oder große Datensätze. Beachten Sie jedoch, dass sie nur in neueren Excel-Versionen verfügbar ist. Sollten Sie eine ältere Version nutzen, prüfen Sie bitte die folgenden Alternativen.
1. Geben Sie folgende Formel in die Zielzelle ein (z. B. E2):
=TEXTJOIN(", ", TRUE, IF($A$2:$A$15=D2, $B$2:$B$15, ""))2. In Excel 2019 drücken Sie nach der Eingabe der Formel die Tastenkombination Strg+Umschalt+Eingabe, um sie als Matrixformel einzugeben. In Excel 365/2021 und neuer genügt ein einfacher Druck auf Eingabe. Ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zellen mit unterschiedlichen Kriterien zu übertragen. Siehe Abbildung unten:
- TEXTJOIN: Fügt Text aus mehreren Zellen zusammen und trennt ihn mit einem festgelegten Trennzeichen (z. B. „, “).
- WENN-Funktion: Prüft jeden Wert im Bereich A2:A15, ob er der Bedingung in D2 entspricht, und gibt bei Übereinstimmung den entsprechenden Wert aus einem anderen Bereich zurück.
Tipp: Bei sehr großen Datensätzen können Sie die Leistung erhalten, indem Sie den manuellen Berechnungsmodus aktivieren oder den Berechnungsbereich einschränken. Prüfen Sie Ihre Bereiche sorgfältig, um sicherzustellen, dass sie vollständig übereinstimmen – andernfalls liefert die Formel möglicherweise Fehler oder unerwartete Ergebnisse.
Falls Ihre Excel-Version TEXTJOIN nicht unterstützt oder Sie mehr Flexibilität benötigen – etwa um Fehler zu ignorieren, andere Trennzeichen zu verwenden oder mit leeren Zellen umzugehen – lesen Sie weiter für universellere Methoden.
Text basierend auf Kriterien mit Kutools für Excel verketten
Für Benutzer, deren Excel-Version die TEXTJOIN-Funktion nicht unterstützt, oder für alle, die eine benutzerfreundliche Oberfläche Formeln vorziehen, bietet Kutools für Excel eine unkomplizierte Lösung zum Verketten von Text basierend auf Kriterien. Diese Methode spart komplexe Formeln und ist mit allen Excel-Versionen kompatibel – so wird sie zur idealen Wahl für eine breite Nutzergruppe. Kutools integriert sich nahtlos in Excel und ist perfekt für alle, die im täglichen Workflow Wert auf Zuverlässigkeit und Zeitersparnis legen.
Um diese Funktion zu nutzen, installieren Sie zunächst Kutools für Excel. Führen Sie nach der Installation Folgendes aus:
- Öffnen Sie Ihr Arbeitsblatt, wechseln Sie zur Registerkarte „Kutools“, wählen Sie „Erweiterter SVERWEIS“ und anschließend „Ein-zu-viele-Suche (mehrere Ergebnisse zurückgeben)“.
- Legen Sie im erscheinenden Dialogfeld den „Platzierungsbereich der Liste“ fest, in dem die Ergebnisse angezeigt werden sollen, sowie den „Suchwertbereich“, der Ihre eindeutigen Schlüssel oder Kriterien enthält.
- Wählen Sie den gesamten Datensatz als Ihre Quelltabelle aus.
- Wählen Sie mithilfe der bereitgestellten Dropdowns Ihre „Schlüsselspalte“ (die Spalte mit Ihrem Kriterium, z. B. Land) und Ihre „Rückgabespalte“ (die Spalte mit den zu verknüpfenden Werten, z. B. Namen) aus.
- Klicken Sie auf „OK“, um den Vorgang durchzuführen.
Ergebnis: Kutools verkettet sofort den relevanten Text basierend auf Ihren festgelegten Kriterien und zeigt die Ergebnisse im Platzierungsbereich der Liste wie unten dargestellt an:
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Tools, um Ihre Arbeit schneller und einfacher zu erledigen, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen
Vorteile der Verwendung von Kutools:
- Anwendbar auf jede Excel-Version
- Keine Formeleingabe oder Matrixoperationen erforderlich
- Verringert das Fehlerrisiko durch komplexe Zellbezüge
- Effizient für Benutzer, die mit fortgeschrittenen Formeln nicht vertraut sind
Text basierend auf Kriterien mit den Funktionen FILTER und TEXTJOIN verketten (Excel 365/2021 oder höhere Alternative)
In Excel 365 oder Excel 2021 und höher bietet die Kombination aus FILTER und TEXTJOIN eine besonders effiziente und dynamische Möglichkeit, Textwerte basierend auf einer Bedingung zu verketten – ideal für alle, die Matrixformeln vermeiden möchten, aber dennoch die neuesten Excel-Funktionen nutzen wollen.
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, und ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf so viele Zeilen zu kopieren, wie Sie benötigen.
Diese Methode kommt ganz ohne Strg + Umschalt + Eingabe aus und passt sich automatisch an, sobald sich die Daten ändern – was sie für dynamische Tabellen besonders robust macht. In der obigen Formel ist B$2:B$15 die zu verknüpfende Spalte, A$2:A$15 der Kriterienbereich und D2 der konkrete Kriterienwert. Passen Sie die Bereiche entsprechend Ihrem Datensatz an. Beachten Sie, dass die FILTER-Funktion eine kompatible Excel-Version voraussetzt.
Text basierend auf Kriterien mit benutzerdefinierter Funktion verketten
Für Anwender, die einen individuellen und flexiblen Ansatz bevorzugen – insbesondere in Excel-Versionen ohne dynamische Arrayfunktionen – bietet eine in VBA erstellte benutzerdefinierte Funktion (UDF) eine leistungsstarke Lösung, um Text basierend auf vorgegebenen Kriterien zu verketten. Diese Methode ermöglicht es Ihnen, Trennzeichen oder Logik ganz nach Bedarf anzupassen, und eignet sich ideal für ältere Excel-Umgebungen oder spezielle, maßgeschneiderte Workflows. Voraussetzung ist jedoch ein grundlegendes Verständnis des Excel-VBA-Editors sowie der Makrosicherheitseinstellungen.
1. Drücken Sie die Tastenkombination ALT+F11, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.
2. Klicken Sie im VBA-Fenster auf „Einfügen“ > „Modul“, und fügen Sie anschließend den folgenden Code in das erscheinende Modulfenster ein:
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 anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zellen zu übertragen:
=CONCATENATEIF($A$2:$A$15, D2, $B$2:$B$15, ",")
Hinweis: In der obigen Formel kennzeichnet A2:A15 den Bereich mit den Kriterien (z. B. Land), D2 das zu treffende Kriterium und B2:B15 den Bereich des zu verknüpfenden Texts. Das Trennzeichen ist standardmäßig ein Komma („,“), kann aber auf jedes beliebige Zeichen oder jede beliebige Zeichenfolge geändert werden. Achten Sie darauf, dass Ihre Kriterien- und Verknüpfungsbereiche exakt dieselbe Größe haben – andernfalls gibt die Funktion einen Fehler zurück.
Tipps für benutzerdefinierte Funktionen:
- Damit benutzerdefinierte Funktionen ordnungsgemäß funktionieren, müssen Makros aktiviert sein.
- Benutzerdefinierte Funktionen werden nicht automatisch neu berechnet, wenn sich die Quelldaten ändern – drücken Sie gegebenenfalls F9, um eine Aktualisierung durchzuführen.
Dieser VBA-basierte Ansatz eignet sich ideal für Anwender, die mit Makros vertraut sind, oder wenn native Excel-Funktionen nicht zur Verfügung stehen. Dank der Flexibilität beim Anpassen des VBA-Codes lassen sich fortgeschrittene Verknüpfungslogiken oder benutzerdefinierte Formate problemlos umsetzen.
Berücksichtigen Sie bei der Auswahl der am besten geeigneten Methode zum Verketten von Text nach Kriterien in Excel Ihre Excel-Version, wie oft Sie die Aufgabe durchführen (einmalig oder regelmäßig) und wie vertraut Sie mit Formeln oder Add-Ins sind. Sollten Formelfehler auftreten, prüfen Sie sorgfältig, ob Ihre Bereiche übereinstimmen und alle Bezüge tatsächlich auf die gewünschten Zeilen oder Spalten verweisen. Stellen Sie bei VBA-Lösungen sicher, dass Makros aktiviert sind, und bei Kutools, dass die Installation erfolgreich abgeschlossen wurde. Falls die Ergebnisse unerwartet ausfallen oder leer sind, vergewissern Sie sich, dass tatsächlich Daten vorhanden sind, die Ihren Kriterien entsprechen. Die geschickte Kombination dieser Ansätze in Ihrem täglichen Arbeitsablauf steigert Ihre Produktivität spürbar, sorgt für saubere Datenaufbereitungen und reduziert manuelles Bearbeiten erheblich. Wenn Sie Unterstützung bei weiteren Datenverarbeitungsaufgaben benötigen,besuchen Sie unser Tutorial-Center für weitere Excel-Einblicke.
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