Les erreurs de concaténation qui vous font perdre du temps sur Excel

Dans ce tutoriel, je vais vous montrer comment utiliser la fonction CONCATENER pour combiner des données dans Excel, avec des exemples concrets.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Introduction

Pour illustrer notre tutoriel, nous allons utiliser le tableau suivant qui présente les données d’une petite entreprise de livraison de fleurs.

Voici le tableau de données d'exemple à copier-coller dans Excel :

Ce tableau reprend ainsi les informations suivantes :

• Nom de la fleur
• Couleur
• Quantité en stock
• Prix unitaire
• Date de réception

2. La fonction CONCATENER

Pour commencer, nous allons souhaiter obtenir dans une cellule unique le nom de chaque fleur avec sa couleur.

Cette opération, qui consiste à ajouter des textes les uns à la suite des autres, s'appelle une concaténation.

Il est donc tout à fait logique que dans Excel, nous retrouvions une fonction pour automatiser cette opération, appelée CONCATENER.

Ainsi, pour ajouter une couleur à la suite du nom d’une fleur, nous insérons la formule suivante dans la cellule F9 :

 =CONCATENER(A9;B9) 

Cette formule combine les valeurs des cellules A9 et B9 en une seule chaîne de texte.

Comme vous pouvez le constater, cette fonction est très simple à utiliser : il suffit de saisir les paramètres que nous souhaitons combiner, séparés par des points-virgules.

Par contre, comme vous pouvez également le constater, ici les deux termes sont accolés directement les uns à la suite des autres.

Ainsi, pour ajouter une espace entre ces derniers, nous allons devoir venir intercaler celle-ci en tant qu’argument intermédiaire :

 =CONCATENER(A9;" ";B9) 

Maintenant, en appuyant sur [Entrée], nous voyons apparaître "Rose Rouge" dans la cellule F9.

La fonction CONCATENER a pris les valeurs des cellules A2 et B2 et les a combinées avec une espace entre elles.

Pour obtenir la liste de toutes les fleurs, il suffit maintenant d’étendre la formule sur toutes les cellules de la colonne en utilisant la poignée de recopie située en bas à droite de la cellule active.

Bien sûr, l'utilisation de CONCATENER ne se limite pas à la combinaison de deux cellules.

Nous pouvons enrichir nos descriptions en ajoutant des informations supplémentaires.

Par exemple, nous voulons ajouter la valeur totale (Quantité en stock multipliée par Prix unitaire) à notre description.

Pour cela, nous utilisons la formule suivante :

 =CONCATENER(A9;" ";B9;" - Valeur :  ";C9*D9;"€") 

En appliquant cette formule, nous obtenons "Rose Rouge - Total : 125 €".

Une autre application utile de CONCATENER est l'ajout de texte conditionnel. Supposons que nous voulions indiquer si une fleur est en stock ou en rupture en fonction de la quantité disponible. Nous pouvons utiliser la formule

 =CONCATENER(A9;" ";B9;" - Valeur :  ";C9*D9;"€ - ";SI(C9>0;"En  stock";"Rupture")) 

Si la quantité est supérieure à zéro, le texte "En stock" sera ajouté, sinon, "En rupture" apparaîtra.

3. L’Esperluette

En plus de la fonction CONCATENER que nous venons de découvrir, Excel offre d'autres moyens de combiner des chaînes de texte.

L'une des méthodes les plus simples et concises est l'utilisation de l'esperluette (&).

Il s’agit en effet d’un simple opérateur à utiliser directement dans une formule, sans fonction, ce qui permet ainsi de gagner du temps et simplifier les formules.

Ainsi, si nous reprenons notre exemple précédent, il suffira de supprimer l’appel à la fonction CONCATENER et de remplacer les points-virgules de séparation des arguments par des esperluettes :

 =A9&" "&B9&" - Valeur :  "&C9*D9&"€ - "&SI(C9>0;"En  stock";"Rupture") 

Le résultat est exactement identique, mais la rédaction de la formule est simplifiée !

Ainsi, la principale différence entre l'utilisation de l'esperluette (&) et la fonction CONCATENER réside dans la syntaxe. La fonction CONCATENER prend une série d'arguments séparés par des points-virgules, tandis que l'esperluette utilise un opérateur pour joindre les textes directement.

4. La fonction CONCAT

Pour ceux qui utilisent des versions récentes d'Excel, la fonction CONCAT est une alternative moderne à CONCATENER.

Elle a été introduite dans Excel 2016 et offre une flexibilité accrue tout en étant plus simple à utiliser.

CONCAT fonctionne de manière similaire à CONCATENER mais avec quelques améliorations significatives.

Nous pouvons donc remplacer simplement la formule :

 =CONCATENER(A10;" ";B10;" - Valeur :  ";C10*D10;"€ - ";SI(C10>0;"En  stock";"Rupture")) 

Par :

 =CONCAT(A10;" ";B10;" - Valeur :  ";C10*D10;"€ - ";SI(C10>0;"En  stock";"Rupture")) 

L’avantage principal de cette fonction CONCAT par rapport à CONCATENER est le support des plages de cellules.

Contrairement à CONCATENER, qui nécessite l'énumération explicite de chaque cellule, CONCAT peut prendre en charge les plages de cellules. Cela simplifie grandement les formules lorsque nous avons besoin de combiner un grand nombre de cellules.

 =CONCAT(A9:B9) 

En revanche, comme vous pouvez le constater ici, il n’est pas possible d’ajouter directement de caractère de séparation entre les valeurs accolées. Il faudra alors prévoir d’insérer les espaces directement dans les données sources, ou utiliser la fonction JOINDRE.TEXTE que nous découvrirons dans la suite de ce tutoriel.

De plus, la fonction CONCAT est optimisée pour fonctionner plus rapidement et de manière plus efficace, particulièrement lorsqu'elle est utilisée avec de grands ensembles de données.

Par contre, la fonction CONCAT étant une fonction relativement nouvelle, elle n'est pas disponible dans les versions d'Excel antérieures à 2016. Cela peut poser des problèmes de compatibilité si nous partageons des fichiers avec des utilisateurs utilisant des versions plus anciennes d'Excel.

5. La fonction JOINDRE.TEXTE

Enfin, la fonction JOINDRE.TEXTE est encore une autre alternative puissante pour combiner des chaînes de texte dans Excel. Elle est particulièrement utile pour éviter de répéter des séparateurs et pour gérer les cellules vides de manière élégante. JOINDRE.TEXTE est disponible dans Excel 2016 et les versions ultérieures.

L’atout de cette fonction JOINDRE.TEXTE est en effet de permettre de spécifier un séparateur unique qui sera inséré entre chaque élément combiné. Cela évite la répétition de séparateurs dans la formule.

Elle offre également la possibilité d'ignorer les cellules vides, ce qui est très pratique lorsque nous travaillons avec des ensembles de données irréguliers. En utilisant le paramètre VRAI, les cellules vides sont ignorées, ce qui permet d'éviter les séparateurs supplémentaires indésirables.

Enfin, comme CONCAT, JOINDRE.TEXTE peut prendre en charge les plages de cellules, simplifiant encore plus la combinaison de grandes quantités de données.

Pour illustrer l’utilisation de la JOINDRE.TEXTE, nous allons utiliser un autre tableau dans lequel se trouve les coordonnées des clients du fleuriste de l’exemple précédent :

Avec ce tableau, nous pouvons utiliser la fonction JOINDRE.TEXTE pour créer une chaîne de texte combinant les informations de chaque personne et ainsi créer une description complète des coordonnées de chaque personne dans une seule cellule.

Pour combiner les informations de Jean Dupont dans une seule cellule, nous pouvons utiliser la formule suivante :

 =JOINDRE.TEXTE(" - ";VRAI;A19:F19) 

Ici, il n’est donc pas nécessaire de devoir sélectionner toutes les cellules les unes à la suite des autres, il suffit de renseigner directement la plage de toutes les cellules à joindre.

Ensuite, pour appliquer cette formule à toutes les lignes du tableau, nous pouvons étendre la formule la poignée de recopie.

Nous pouvons ainsi constater que l’utilisation de l’argument « ignorer_si_vide » permet d’éviter que nous retrouver avec des zones vides lorsqu’une cellule n’a pas de contenu.

Pour nous en rendre compte, nous pouvons modifier la formule pour donner la valeur « FAUX » à cet argument :

Pour finir, voyons une utilisation un peu plus complexe de la fonction JOINDRE.TEXTE, qui va nous permettre de combiner les informations de manière structurée et lisible. Voici la formule :

Pour cela, nous utiliserons comme caractère de séparation UNICAR(10), qui correspond à un saut de ligne.

Cela signifie que chaque partie de la chaîne combinée sera séparée par un saut de ligne, ce qui rendra le texte plus lisible lorsque nous affichons les résultats.

 =JOINDRE.TEXTE(UNICAR(10); VRAI; ...) 

Ensuite, nous allons combiner les valeurs des cellules A19 et B19 (Prénom et Nom) avec une espace entre elles :

 =JOINDRE.TEXTE(UNICAR(10); VRAI; JOINDRE.TEXTE("  "; VRAI; A19:B19)) 

Puis, nous ajoutons la cellule C19 dans laquelle se trouve l’adresse email, juste après le prénom et le nom combinés :

Ensuite, nous combinons les cellules D19 et E19 avec le Code Postal et la Ville, en utilisant avec un tiret entre elles :

 =JOINDRE.TEXTE(UNICAR(10); VRAI; JOINDRE.TEXTE("  "; VRAI; A19:B19);C19 ; JOINDRE.TEXTE(" - "; VRAI; D19:E19))  

Et enfin, nous ajoutons la cellule F19 (Adresse) tout à la fin :

En appliquant cette formule, nous obtenons une chaîne de texte multiligne bien organisée avec les coordonnées de chaque personne.

Cette organisation rend les informations de chaque client faciles à lire et à interpréter.