Tutorials

Excel Tutorial: Werte bedingt verketten (CONCATENATEIF)

Melden

Hier ist ein ausführliches Tutorial, wie du in Excel Werte basierend auf einer Bedingung verkettet (ähnlich wie SUMMEWENN, nur für Text).

In Excel gibt es keine eingebaute Funktion namens VERKETTENWENN (oder CONCATENATEIF). Dennoch gibt es zwei sehr effiziente Wege, dies zu erreichen: Die moderne Methode mit TEXTKETTE (bzw. TEXTJOIN) oder die klassische Methode über ein VBA-Makro.

Methode 1: Die moderne Lösung (Excel 365, 2019 oder neuer)

Seit Excel 2019 gibt es die Funktion TEXTKETTE (im Englischen TEXTJOIN). In Kombination mit der WENN-Funktion ersetzt sie die fehlende "CONCATENATEIF"-Funktion perfekt.

Die Formel-Logik

=TEXTKETTE(Trennzeichen; Wahr_Ignorieren; WENN(Bedingungsbereich=Kriterium; Wertebereich; ""))

Schritt-für-Schritt-Beispiel

Stell dir vor, du hast eine Liste mit Projekten und Mitarbeitern:

  • Spalte A: Projektname (z. B. Projekt A, Projekt B)
  • Spalte B: Mitarbeitername (z. B. Max, Lisa, Tom)

Du möchtest alle Mitarbeiter von "Projekt A" in einer Zelle mit Komma getrennt auflisten.

  1. Klicke in die Zielzelle.
  2. Gib folgende Formel ein:
    =TEXTKETTE(", "; WAHR; WENN(A2:A10="Projekt A"; B2:B10; ""))
  3. WICHTIG: Wenn du eine ältere Excel-Version (vor Office 365) verwendest, musst du die Formel mit Strg + Umschalt + Enter bestätigen (Array-Formel). In Excel 365 reicht ein einfaches Enter.

Erklärung der Parameter:

  • ", ": Das Trennzeichen zwischen den Namen.
  • WAHR: Ignoriert leere Zellen (falls die Bedingung nicht zutrifft).
  • WENN(...): Prüft, ob in Spalte A "Projekt A" steht. Wenn ja, wird der Name aus Spalte B genommen, sonst ein leerer Text "".

Methode 2: Die VBA-Lösung (Für alle Excel-Versionen)

Wenn du eine ältere Excel-Version nutzt oder eine echte Funktion namens CONCATENATEIF haben möchtest, kannst du ein kleines Makro (UDF) erstellen.

Schritt 1: Den VBA-Editor öffnen

  1. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Gehe auf Einfügen > Modul.

Schritt 2: Code einfügen

Kopiere diesen Code in das weiße Fenster:

Function CONCATENATEIF(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ", ") As String
    Dim i As Long
    Dim result As String

    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            If result <> "" Then
                result = result & Separator & ConcatenateRange.Cells(i).Value
            Else
                result = ConcatenateRange.Cells(i).Value
            End If
        End If
    Next i

    CONCATENATEIF = result
End Function

Schritt 3: Die Funktion in Excel nutzen

Jetzt kannst du die Funktion wie eine ganz normale Formel in deiner Tabelle verwenden:

=CONCATENATEIF(A2:A10; "Projekt A"; B2:B10; "; ")

(Die Parameter sind: Suchbereich, Kriterium, Bereich der verkettet werden soll, Trennzeichen)

Methode 3: Power Query (Für große Datenmengen)

Wenn du sehr große Listen hast, ist Power Query der sauberste Weg:

  1. Markiere deine Daten -> Reiter Daten -> Aus Tabelle/Bereich.
  2. Wähle die Spalte aus, nach der gruppiert werden soll (z. B. "Projekt").
  3. Gehe auf Transformieren -> Gruppieren nach.
  4. Wähle unter "Vorgang" die Option Alle Zeilen.
  5. Erstelle im Power Query Editor eine benutzerdefinierte Spalte, die die Werte mit Text.Combine zusammenführt.
  6. Klicke auf Schließen & Laden.

Zusammenfassung

  • Excel 365/2019/2021: Nutze =TEXTKETTE(Trennzeichen; WAHR; WENN(...)). Dies ist die einfachste und beste Methode.
  • Alte Versionen: Nutze die VBA-Lösung, um eine eigene Funktion zu erstellen.
  • Große Daten: Nutze Power Query, um die Performance deiner Tabelle nicht zu beeinträchtigen.
0