Comment CONCATENER des données dans Excel (toutes versions !)

Dans ce tutoriel, je vais vous montrer comment combiner des données les unes à la suite des autres dans Excel pour combiner intelligemment vos données et créer des rapports en quelques clics seulement.

Vous allez découvrir une technique simple mais puissante que beaucoup de professionnels utilisent quotidiennement, mais que peu de personnes maîtrisent vraiment.

Alors, restez bien jusqu'à la fin pour apprendre à créer votre propre fonction personnalisée qui va révolutionner votre façon de travailler avec Excel !

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. Présentation

Imaginons que nous sommes responsables du suivi des ventes dans une chaîne de magasins d'électroménager.

Nous avons un tableau qui répertorie les ventes par produit, par magasin et par vendeur, et notre objectif est de créer un rapport qui regroupe les informations de manière lisible et exploitable.

2. Les bases de la concaténation dans Excel

   2.1 La fonction CONCATENER traditionnelle

La concaténation consiste à joindre des chaînes de caractères les unes aux autres.

Dans Excel, cette opération est très utile pour créer des références complètes, des identifiants uniques ou simplement pour présenter les données sous une forme plus lisible.

Pour commencer, plaçons-nous dans la colonne « Référence » de notre tableau qui contiendra une information combinée qui nous permettra d'identifier rapidement chaque vente.

Nous allons utiliser la fonction CONCATENER pour combiner le magasin, le vendeur et le produit.

La syntaxe complète de la fonction CONCATENER est :

=CONCATENER(texte1; texte2; ...)

Où chaque argument correspond aux différents textes que nous souhaitons insérer les uns à la suite des autres.

Pour notre exemple, nous voulons combiner le magasin, un tiret comme séparateur, le vendeur, un autre tiret, et enfin le produit. Nous écrivons donc :

=CONCATENER(C9; " - "; D9; " -  "; E9)

Après avoir appuyé sur [Entrée], nous voyons apparaître dans la cellule I2 le texte « Paris - Dupont - Lave-vaisselle XP300 ».

Une méthode alternative pour réaliser cette même concaténation consiste à utiliser l'opérateur « & » à la place de la fonction CONCATENER, nous pouvons simplement écrire :

=C9 & " - " & D9 & " -  " & E9

Le résultat sera exactement le même, mais cette méthode est souvent plus rapide à taper.

Pour copier rapidement cette formule vers le bas et l'appliquer à toutes les lignes de notre tableau, nous cliquons sur la poignée de recopie et tout en maintenant le bouton gauche de la souris enfoncé et glissons vers le bas jusqu'à la dernière ligne de notre tableau.

Lorsque nous relâchons le bouton, Excel copie automatiquement la formule et ajuste les références de cellules.

   2.2 Limites de la fonction CONCATENER

Bien que la fonction CONCATENER soit utile, elle présente certaines limites qui peuvent rendre son utilisation fastidieuse dans des scénarios plus complexes.

Premièrement, la fonction CONCATENER ne gère pas automatiquement les cellules vides, comme nous pouvons le constater sur la dernière ligne de notre tableau, où le nom du produit est vide.

Une solution pour contourner ce problème serait d'utiliser la fonction SI pour vérifier si la cellule est vide avant de la concaténer. Afin de vérifier un champ est vide, et si c'est le cas, elle n'ajoute pas le tiret ni la valeur, mais cela complexifit grandement la formule.

Deuxièmement, la fonction CONCATENER devient rapidement complexe lorsque nous avons de nombreuses cellules à combiner. Imaginez que nous voulions concaténer 10 ou 20 cellules avec des séparateurs entre chacune d'elles. La formule deviendrait très longue et difficile à lire ou à modifier.

Troisièmement, si nous voulons concaténer des plages entières de cellules, par exemple toutes les valeurs d'une colonne qui répondent à un certain critère, la fonction CONCATENER n'est pas adaptée. Nous devrions écrire une formule très complexe utilisant des fonctions SI et CONCATENER imbriquées.

Heureusement, Excel propose une alternative plus puissante : la fonction JOINDRE.TEXTE, que nous allons explorer dans la section suivante.

Avant de passer à cette fonction plus avancée, il est important de noter que la fonction CONCATENER est progressivement remplacée par la fonction CONCAT dans les versions récentes d'Excel. La fonction CONCAT fonctionne de manière similaire, mais elle ne prend pas en charge directement la concaténation de plages comme JOINDRE.TEXTE, que nous allons découvrir maintenant.

3. La fonction JOINDRE.TEXTE : une alternative puissante

La fonction JOINDRE.TEXTE est une évolution de la fonction CONCATENER, qui a été introduite dans Excel 2016 et représente une solution beaucoup plus élégante pour combiner des textes, particulièrement lorsqu'il s'agit de plages de cellules.

La syntaxe de la fonction JOINDRE.TEXTE est la suivante :

=JOINDRE.TEXTE(délimiteur; ignorer_vides;  texte1; texte2; ...)

Le premier argument, « délimiteur », est le caractère ou la chaîne de caractères que nous souhaitons insérer entre chaque élément concaténé. Contrairement à CONCATENER, où nous devions ajouter manuellement le séparateur entre chaque argument, ici nous le définissons une seule fois et Excel l'applique automatiquement entre chaque élément.

Le deuxième argument, « ignorer_vides », est une valeur logique (VRAI ou FAUX) qui indique si les cellules vides doivent être ignorées lors de la concaténation. Si nous passons VRAI, les cellules vides seront simplement ignorées et le délimiteur ne sera pas ajouté pour ces cellules. Si nous passons FAUX, les cellules vides seront traitées comme des chaînes vides et le délimiteur sera ajouté.

Les arguments suivants sont les textes ou les plages de cellules que nous souhaitons concaténer.

Ainsi, revenons dans notre colonne « Référence » afin de remplacer la formule par :

=JOINDRE.TEXTE(" - "; VRAI; C9; D9;  E9)

Ici le résultat est exactement identique à celui de la formule obtenu avec CONCATENER, cependant, la formule est plus claire et plus facile à comprendre.

Lorsque nous examinons la dernière cellule de la colonne dans laquelle le nom du produit est omis, nous voyons que le résultat est « Paris - Robert » sans le tiret supplémentaire à la fin.

C'est parce que nous avons passé VRAI comme valeur pour l'argument « ignorer_vides », ce qui indique à Excel d'ignorer simplement les cellules vides.

L'un des grands avantages de JOINDRE.TEXTE est sa capacité à travailler avec des plages entières de cellules. Par exemple, si nous voulons concaténer tous les produits de notre tableau, nous pouvons simplement écrire : « =JOINDRE.TEXTE(", "; VRAI; E2) ». Cette formule combinera tous les produits de la colonne E, en les séparant par des virgules, et ignorera les cellules vides.

4. Création d'une fonction personnalisée pour les versions antérieures d'Excel

Malgré tous ses avantages, la fonction JOINDRE.TEXTE n'est disponible que dans Excel 2016 et les versions ultérieures.

Si nous travaillons avec une version plus ancienne d'Excel ou si nous devons partager notre classeur avec des personnes qui utilisent des versions antérieures, nous ne pourrons pas utiliser cette fonction.

Heureusement, Excel nous permet de créer nos propres fonctions personnalisées à l'aide du langage VBA (Visual Basic for Applications).

Outre la compatibilité avec les versions antérieures, la création d'une fonction personnalisée nous offre plusieurs avantages :

• Nous pouvons adapter la fonction à nos besoins spécifiques.
• Nous pouvons lui donner un nom qui a du sens dans notre contexte.
• Nous pouvons ajouter des fonctionnalités supplémentaires qui ne sont pas disponibles dans JOINDRE.TEXTE.

Ici, nous allons aborder rapidement le fonctionnement de VBA et son utilisation comme outil puissant pour décupler les capacités offertes par Excel, mais si vous souhaitez aller plus loin, vous pouvez découvrir mon livre « Apprendre le VBA pour les débutants ».

La fonction « Concatenation » que nous allons mettre en place va fonctionner avec trois paramètres, grandement inspirés de la fonction JOINDRE.TEXTE :

• « Delimiteur » : une chaîne de caractères qui sera insérée entre chaque élément concaténé.
• « IgnorerVides » : une valeur booléenne (True/False) indiquant si les cellules vides doivent être ignorées.
• « Plage » : une plage de cellules à concaténer.

Pour créer notre fonction personnalisée, nous devons accéder à l'éditeur VBA d'Excel, en appuyant sur les touches [Alt]+[F11].

Une fois dans l'éditeur VBA, nous devons insérer un nouveau module pour y écrire notre fonction. Nous cliquons sur « Insertion » dans la barre de menus, puis sur « Module ».

Dans le module vide qui s'ouvre, nous allons écrire le code de notre fonction personnalisée en commençant par créer cette dernière, que nous appellerons « Concatenation ».

Pour cela, nous utilisons le mot-clé « Function », suivi du nom de cette fonction :

Function Concatenation()

Lorsque nous appuyons sur la touche [Entrée], VBA va ajouter la ligne « End Function », qui permet de marquer la fin de la fonction.

Ainsi, tout ce que nous allons saisir entre ces deux lignes sera exécuté automatiquement à chaque fois que nous appellerons la fonction « Concatenation ».

VBA va également ajouter des parenthèses ouvrantes et fermantes pour nous permettre d'insérer d'éventuelles argument à notre fonction.

Ici, la fonction prendra trois arguments : un délimiteur sous forme de chaîne de caractères, un booléen indiquant s'il faut ignorer les cellules vides, et une plage de cellules à traiter.

Function Concatenation(Delimiteur As String,  IgnorerVides As Boolean, Plage As Range)

Ensuite, nous déclarons une variable nommée « Cellule » qui sera utilisée pour parcourir chaque cellule de la plage. Nous la typons en tant que Range, ce qui représente une cellule ou une plage de cellules dans Excel :

    Dim Cellule As Range

Nous déclarons également une seconde variable « Resultat » de type String qui contiendra la chaîne de caractères résultant de la concaténation :

    Dim Resultat As String

Et enfin, une troisième variable booléenne « Premier » qui nous permettra de savoir si nous traitons la première cellule non vide (pour éviter d'ajouter un délimiteur avant la première valeur) :

    Dim Premier As Boolean

Nous initialisons la variable « Premier » à True, indiquant que nous n'avons pas encore traité de cellule :

    Premier = True

Nous initialisons la variable « Resultat » avec une chaîne vide :

    Resultat = ""

Puis, nous nous commençons une boucle qui va parcourir chaque cellule de la plage spécifiée, en utilisant une boucle « For Each », qui permet de répéter une série d’instructions tant qu’une condition est remplie. Ici, nous souhaitons passer en revue chacune des cellule de la variable « Plage » :

    For Each Cellule In Plage

À l’intérieur de cette boucle, nous allons effectuer un test afin de vérifier si la cellule n'est pas vide OU si l'option d'ignorer les cellules vides n'est pas activée.

Pour cela, nous utilisons la fonction VBA « IsEmpty » qui vérifie si une cellule est vide, et nous la combinons avec l’instruction « Not » qui permet d’inverser le résultat:

    If Not IsEmpty(Cellule.Value) Or Not  IgnorerVides Then

À l'intérieur de cette première condition, nous vérifions si c'est la première cellule non vide que nous traitons:

        If Premier Then

Si c'est la première cellule non vide, nous initialisons la variable "Resultat" avec la valeur de la cellule actuelle:

            Resultat = Cellule.Value

Puis nous changeons la valeur de "Premier" à False car nous avons traité la première cellule:

            Premier = False

Si ce n'est pas la première cellule, nous entrons dans la clause Else:

        Else

Dans ce cas, nous ajoutons à "Resultat" la valeur de la cellule actuelle, précédée du délimiteur:

            Resultat = Resultat & Delimiteur & Cellule.Value 

Et enfin, nous fermons notre boucle principale avec un « Next » :

Next

Pour finir, nous assignons la valeur finale de « Resultat » à la fonction elle-même, ce qui constituera la valeur retournée :

Concatenation = Resultat

Une fois le code saisi, nous fermons l'éditeur VBA en cliquant sur le bouton de fermeture (X) dans le coin supérieur droit de la fenêtre.

Maintenant, nous devons enregistrer notre classeur au format .xlsm (Classeur Excel avec macros) pour que notre fonction soit conservée.

De retour sur la feuille de calcul, nous allons maintenant pouvoir utiliser celle-ci dans notre classeur comme n'importe quelle autre fonction Excel.

Pour cela retournons dans notre colonne « Paramètres » afin d’utiliser notre fonction personnalisée pour reproduire le même exemple que précédemment en saisissant la formule :

=Concatenation(" - ";VRAI;C9:E9)

Après avoir appuyé sur [Entrée], nous voyons apparaître le même résultat qu'avec JOINDRE.TEXTE : « Paris - Dupont - Lave-vaisselle XP300 ».

Une différence majeure entre notre fonction personnalisée et JOINDRE.TEXTE est que notre fonction prend une plage de cellules comme troisième argument, tandis que JOINDRE.TEXTE peut prendre un nombre variable d'arguments.

Pour reproduire exactement le comportement de JOINDRE.TEXTE, nous devrions modifier notre fonction VBA, mais cela dépasse le cadre de ce tutoriel.