Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie Zellenwerte basierend auf Kriterien mit der TEXTJOIN-Funktion in Excel und Google Sheets verketten.
Die TEXTJOIN-Funktion
Benutzer von Excel 2022+ haben Zugriff auf die TEXTJOIN-Funktion, die mehrere Textzeichenfolgen zusammenführt und jede Zeichenfolge mit einem angegebenen Trennzeichen trennt.
Notiz: Benutzer von Google Sheets können die TEXTJOIN-Funktion verwenden, haben jedoch eine etwas andere Möglichkeit, Arrayformeln einzugeben. Siehe den späteren Abschnitt zu diesem Thema.
In diesem Beispiel werden die Funktionen TEXTJOIN und IF in einer Arrayformel verwendet, um eine durch Kommas getrennte Textzeichenfolge von zu erstellen Spielernamen die sich auf a beziehen Mannschaft Wert von rot
| 1 | =TEXTJOIN(", ",TRUE,IF(C3:C8="Rot",B3:B8,"")) |
Benutzer von Excel 2022 müssen diese Formel als Array-Funktion eingeben, indem Sie STRG + UMSCHALT + EINGABETASTE drücken. Benutzer neuerer Excel-Versionen müssen diesen Schritt nicht ausführen.
Um zu erklären, was diese Formel macht, teilen wir sie in Schritte auf:
Dies ist unsere endgültige Formel:
| 1 | =TEXTJOIN(", ",TRUE,IF(C3:C8="Rot",B3:B8,"")) |
Zuerst werden die Zellenbereichswerte zur Formel hinzugefügt:
| 1 | =TEXTJOIN(", ",TRUE,IF({"Rot"; "Blau"; "Blau"; "Rot"; "Blau"; "Rot"}="Rot",{"A"; "B"; "C"; "D"; "E"; "F"},"")) |
Als nächstes die Liste der Mannschaft Namen wird mit dem Wert verglichen rot:
| 1 | =TEXTJOIN(", ",TRUE,IF({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE},{"A"; "B"; "C"; "D"; "E"; "F"} ,"")) |
Die IF-Funktion ersetzt TRUE-Werte durch die Spielername, und FALSE-Werte mit „“
| 1 | =TEXTJOIN(", ",TRUE,{"A"; ""; ""; "D"; ""; "F"}) |
Die TEXTJOIN-Funktion kombiniert dann alle Array-Werte in einer Textzeichenfolge. Wir haben die Funktion angewiesen, leere Werte zu ignorieren und den Text „, “ zwischen jedem Wert hinzuzufügen. Daraus ergibt sich das Endergebnis:
| 1 | ="A, D, F" |
TextJoin If - in Pre-Excel 2022
Da die TEXTJOIN-Funktion erst ab der Excel 2022-Version verfügbar ist, müssen wir dieses Problem auf andere Weise lösen. Die CONCATENATE-Funktion ist verfügbar, akzeptiert aber keine Zellbereiche als Eingaben oder erlaubt Array-Operationen. Daher müssen wir stattdessen eine Hilfsspalte mit einer IF-Funktion verwenden.
Dieses nächste Beispiel zeigt, wie eine Hilfsspalte verwendet wird, um eine durch Kommas getrennte Textzeichenfolge von zu erstellen Spielernamen die sich auf a beziehen Mannschaft Wert von Rot:
| 1 | =WENN(C3="Rot",B3&", ","")&D4 |
Der erste Schritt in diesem Beispiel besteht darin, eine IF-Funktion zu verwenden, um die Bedingung von . zu replizieren Mannschaft = Rot. Wenn die Bedingung erfüllt ist, wird die Spielername Wert wird geschrieben und gefolgt vom gewählten Trennzeichen “, “:
| 1 | =IF(C3="Rot",B3&", ","") |
Als Nächstes können wir eine Spalte erstellen, die eine Liste dieser Werte in einer Zelle aufbaut, indem wir auch auf die Zelle darunter verweisen:
| 1 | =D3&E4 |
Diese Formel verwendet das &-Zeichen, um zwei Werte miteinander zu verbinden. Beachten Sie, dass die CONCATENATE-Funktion verwendet werden könnte, um genau das gleiche Ergebnis zu erzielen, aber die &-Methode wird oft bevorzugt, da sie kürzer ist und klarer macht, welche Aktion die Formel ausführt.
Diese beiden Hilfsspalten können dann zu einer Formel zusammengefasst werden:
| 1 | =WENN(C3="Rot",B3&", ","")&D4 |
Eine Zusammenfassungszelle kann dann auf den ersten Wert im verweisen Spielerliste Helferspalte:
| 1 | =D3 |
Wenn der letzte Wert „“, nicht erforderlich ist, sollte der Formel eine zusätzliche verschachtelte IF-Funktion hinzugefügt werden:
| 1 | =IF(C3="Rot",IF(D4="",B3,B3&", "),"")&D4 |
Der zusätzliche Teil der Formel ist die folgende IF-Funktion:
| 1 | WENN(D4="",B3,B3&", ") |
Dieser Teil der Formel überprüft den Wert der darunter liegenden Zelle. Wenn es leer ist, muss diese Zelle die letzte sein Spielername in der Liste und der Name wird ohne Trennzeichen geschrieben. Wenn die Zelle darunter einen Wert enthält, ist diese Zelle nicht die letzte Spielername in der Liste und so wird das Trennzeichen „, “ hinzugefügt.
TextJoin If in Google Sheets
Diese Formeln funktionieren in Google Sheets genauso wie in Excel, außer dass die ARRAYFORMULA-Funktion in Google Sheets verwendet werden muss, damit die Ergebnisse korrekt ausgewertet werden. Dies kann durch Drücken der Tasten STRG + UMSCHALT + EINGABE während der Bearbeitung der Formel automatisch hinzugefügt werden:
| 1 | =ARRAYFORMULA(TEXTJOIN(", ",TRUE,(IF(C3:C8="Rot",B3:B8,"")))) |
