Téléchargez l'exemple de classeur
Ce didacticiel montrera comment concaténer des valeurs de cellule en fonction de critères à l'aide de la fonction TEXTJOIN dans Excel et Google Sheets.
La fonction TEXTJOIN
Les utilisateurs d'Excel 2022+ ont accès à la fonction TEXTJOIN, qui fusionne plusieurs chaînes de texte, séparant chaque chaîne avec un délimiteur spécifié.
Noter: Les utilisateurs de Google Sheets peuvent utiliser la fonction TEXTJOIN, mais ont une manière légèrement différente de saisir des formules matricielles. Voir la section suivante sur ce sujet.
Cet exemple utilisera les fonctions TEXTJOIN et IF dans une formule matricielle pour créer une chaîne de texte séparée par des virgules de Noms des joueurs qui se rapportent à un Équipe valeur de rouge
| 1 | =TEXTJOIN(", ",VRAI,SI(C3:C8="Rouge",B3:B8,"")) |
Les utilisateurs d'Excel 2022 devront entrer cette formule sous forme de fonction de tableau en appuyant sur CTRL + MAJ + ENTRÉE. Les utilisateurs de versions ultérieures d'Excel n'ont pas besoin de suivre cette étape.
Pour expliquer ce que fait cette formule, décomposons-la en étapes :
Voici notre formule finale :
| 1 | =TEXTJOIN(", ",VRAI,SI(C3:C8="Rouge",B3:B8,"")) |
Tout d'abord, les valeurs de plage de cellules sont ajoutées à la formule :
| 1 | =TEXTJOIN(", ",TRUE,IF({"Rouge"; "Bleu"; "Bleu"; "Rouge"; "Bleu"; "Rouge"}="Rouge",{"A"; "B"; "C" ; "D" ; "E" ; "F"},"")) |
Ensuite la liste des Équipe les noms sont comparés à la valeur rouge:
| 1 | =TEXTJOIN(", ",TRUE,IF({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE},{"A"; "B"; "C"; "D"; "E"; "F"} ,"")) |
La fonction SI remplace les valeurs VRAI par la Nom de joueur, et les valeurs FAUX avec ""
| 1 | =TEXTJOIN(", ",TRUE,{"A"; ""; ""; "D"; ""; "F"}) |
La fonction TEXTJOIN combine ensuite toutes les valeurs du tableau en une seule chaîne de texte. Nous avons demandé à la fonction d'ignorer les valeurs vides et d'ajouter le texte ", " entre chaque valeur. Cela produit le résultat final :
| 1 | ="A, D, F" |
TextJoin If - en pré-Excel 2022
Comme la fonction TEXTJOIN n'est pas disponible avant la version Excel 2022, nous devons résoudre ce problème d'une manière différente. La fonction CONCATENATE est disponible mais ne prend pas de plages de cellules comme entrées ou n'autorise pas les opérations de tableau et nous devons donc utiliser une colonne d'aide avec une fonction SI à la place.
Cet exemple suivant montre comment utiliser une colonne d'assistance pour créer une chaîne de texte séparée par des virgules de Noms des joueurs qui se rapportent à un Équipe valeur de Rouge:
| 1 | =SI(C3="Rouge",B3&", ","")&D4 |
La première étape de cet exemple consiste à utiliser une fonction SI pour reproduire la condition de Équipe = Rouge. Si la condition est remplie, le Nom de joueur valeur est écrite et suivie du délimiteur choisi ", " :
| 1 | =SI(C3="Rouge",B3&", ","") |
Ensuite, nous pouvons créer une colonne qui construit une liste de ces valeurs dans une cellule en référençant également la cellule en dessous :
| 1 | =D3&E4 |
Cette formule utilise le caractère & pour joindre deux valeurs. Notez que la fonction CONCATENATE peut être utilisée pour créer exactement le même résultat, mais la méthode & est souvent préférée car elle est plus courte et rend plus claire l'action exécutée par la formule.
Ces deux colonnes d'aide peuvent ensuite être combinées en une seule formule :
| 1 | =SI(C3="Rouge",B3&", ","")&D4 |
Une cellule récapitulative peut alors référencer la première valeur de la Liste des joueurs colonne d'aide :
| 1 | =D3 |
Si la valeur finale "," n'est pas requise, une fonction IF imbriquée supplémentaire doit être ajoutée à la formule :
| 1 | =SI(C3="Rouge",SI(D4="",B3,B3&", "),"")&D4 |
La partie supplémentaire de la formule est la fonction SI ci-dessous :
| 1 | SI(D4="",B3,B3&", ") |
Cette partie de la formule vérifie la valeur de la cellule en dessous. Si elle est vide, alors cette cellule doit être la dernière Nom de joueur dans la liste et le nom est écrit sans délimiteur. Si la cellule ci-dessous contient une valeur, alors cette cellule n'est pas la dernière Nom de joueur dans la liste et ainsi le délimiteur « , » est ajouté.
TextJoin If dans Google Sheets
Ces formules fonctionnent de la même manière dans Google Sheets que dans Excel, sauf que la fonction ARRAYFORMULA doit être utilisée dans Google Sheets pour qu'elle évalue correctement les résultats. Cela peut être ajouté automatiquement en appuyant sur les touches CTRL + SHIFT + ENTER lors de l'édition de la formule :
| 1 | =ARRAYFORMULA(TEXTJOIN(", ",TRUE,(IF(C3:C8="Rouge",B3:B8,"")))) |
