Comment concaténer des valeurs uniques dans Excel ?
Lorsque vous travaillez avec des feuilles de calcul, il est courant de rencontrer des situations où vous devez fusionner (concaténer) uniquement les valeurs uniques d'une colonne ou compiler des listes qui résument les entrées uniques avec leurs enregistrements correspondants. Gérer les doublons et présenter des informations résumées ne fait pas que nettoyer vos données mais rend également les rapports plus clairs et plus instructifs. Dans Excel, il existe plusieurs façons pratiques d'atteindre ces objectifs, allant de l'utilisation de fonctions intégrées à l'exploitation de modules complémentaires avancés ou de code personnalisé. Ce tutoriel offre une exploration détaillée de plusieurs méthodes pour concaténer des valeurs uniques et lister les entrées uniques avec leurs données associées. Les solutions couvertes répondent à différentes versions d'Excel et préférences utilisateur, vous aidant à sélectionner la meilleure approche pour votre scénario.
Concaténer uniquement les valeurs uniques d'une colonne
- En utilisant les fonctions TEXTJOIN et UNIQUE
- En utilisant Kutools AI Aide
- En utilisant une fonction définie par l'utilisateur
- En utilisant une formule Excel avancée (solution alternative)
Lister les valeurs uniques et concaténer les valeurs correspondantes
- En utilisant les fonctions TEXTJOIN et UNIQUE
- En utilisant Kutoolsfor Excel
- En utilisant le code VBA
- En utilisant un tableau croisé dynamique Excel avec des formules (solution alternative)
Concaténer uniquement les valeurs uniques d'une colonne
Lors de l'analyse de données dans Excel, une tâche fréquente consiste à fusionner uniquement les entrées distinctes d'une colonne dans une seule cellule. Cela peut être particulièrement utile pour créer des rapports de synthèse, éviter les valeurs en double dans une liste, ou préparer des données pour un traitement ultérieur. Le choix de la méthode dépend de votre version d'Excel, de la taille de votre jeu de données et de votre familiarité avec les formules ou le code. Les méthodes suivantes répondent à différents besoins, mettent en évidence les considérations pour chacune et offrent des conseils pratiques pour assurer une exécution correcte.
Méthode 1 : Utiliser les fonctions TEXTJOIN et UNIQUE
Pour les utilisateurs d'Excel 365 et Excel 2021, l'introduction des fonctions TEXTJOIN et UNIQUE rend la combinaison de valeurs uniques d'une colonne à la fois simple et flexible.
Cette solution est idéale lorsque votre colonne de données est continue et que vous souhaitez rapidement consolider toutes les entrées uniques dans une seule cellule avec un délimiteur choisi. Elle élimine automatiquement les doublons, est facilement vérifiable et vous permet de modifier la plage ou le délimiteur selon vos besoins. Cependant, notez que cette approche n'est disponible que dans les dernières versions d'Excel ; les versions antérieures ne prendront pas en charge la fonction UNIQUE.
Dans la cellule où vous souhaitez afficher le résultat, entrez la formule suivante (en supposant que vos données soient dans les cellules A2:A18) :
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18) filtre les entrées en double et ne retourne que les valeurs uniques de la plage A2:A18.
- TEXTJOIN(", ", TRUE, ...) combine (concatène) ces valeurs uniques dans une seule cellule, séparées par une virgule et un espace. L'argument TRUE garantit que toutes les cellules vides sont ignorées dans la concaténation.
Conseils et dépannage :
- Assurez-vous que votre version d'Excel prend en charge les fonctions UNIQUE et TEXTJOIN. Si vous voyez une erreur #NOM?, vous utilisez peut-être une version antérieure.
- Le délimiteur utilisé dans TEXTJOIN peut être changé à ce que vous préférez, comme "; " ou "|".
- Si vous ajoutez ou supprimez des données dans la plage d'origine, la formule se met à jour automatiquement.
- Pour éviter des espaces ou délimiteurs involontaires, revérifiez attentivement l'argument de délimiteur dans la formule.
Méthode 2 : Utiliser Kutools AI Aide
Lorsque vous avez besoin d'une méthode plus rapide et entièrement automatisée pour concaténer des valeurs uniques — sans écrire de formules — l’outil « Assistant IA » de Kutools pour Excel offre une solution pratique qui fait gagner du temps aux utilisateurs de tous niveaux. Cette méthode est particulièrement utile si vous n'êtes pas familier avec les formules avancées d'Excel ou si vos données changent fréquemment et nécessitent des tâches répétitives.
Après avoir installé Kutools pour Excel, accédez à cette fonctionnalité en cliquant sur "Kutools" > "AI Aide" pour ouvrir le panneau "Kutools AI Aide".
- Sélectionnez les cellules qui contiennent les valeurs que vous souhaitez combiner dans une seule cellule, en veillant à ce que votre sélection corresponde à vos données ciblées.
- Dans la boîte de dialogue, décrivez votre demande. Par exemple, vous pouvez taper :
Concaténer des valeurs uniques avec une virgule depuis la plage sélectionnée et placer le résultat combiné dans la cellule C2 - Appuyez sur le bouton Entrée ou cliquez sur le bouton "Envoyer" . L'IA analyse votre demande et, après traitement, appuyez sur « Exécuter » pour que Kutools effectue l'opération. Le résultat sera retourné comme décrit.
Remarques et conseils :
- Vérifiez que vous utilisez la version la plus récente de Kutools pour accéder à toutes les fonctionnalités d'IA.
- Soyez précis dans votre commande textuelle pour obtenir les meilleurs résultats ; mentionnez le délimiteur et la cellule cible.
- Kutools AI est particulièrement efficace pour les grandes plages ou pour les flux de travail qui doivent être répétés sur divers jeux de données.
Méthode 3 : Utiliser une fonction définie par l'utilisateur
Pour les utilisateurs qui nécessitent une flexibilité avancée, ont besoin de délimiteurs personnalisés, ou souhaitent un outil réutilisable pour plusieurs classeurs, coder une fonction définie par l'utilisateur (UDF) en VBA est un moyen efficace de concaténer automatiquement des valeurs uniques. Cette solution VBA est compatible avec toutes les versions d'Excel et n'est pas restreinte par la disponibilité de nouvelles fonctions.
- Vous devriez activer les macros dans votre classeur.
- Enregistrez le fichier sous forme de fichier activé pour les macros (.xlsm) au cas où vous continueriez à utiliser ce code VBA à l'avenir.
- Il est recommandé de faire des sauvegardes régulières de votre classeur avant d'exécuter un nouveau code.
1. Maintenez ALT + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Dans la fenêtre VBA, cliquez sur Insertion > Module, puis copiez et collez le code suivant :
Code VBA : Concaténer des valeurs uniques dans une seule cellule :
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Revenez à votre feuille de calcul, et dans une cellule vide (par exemple C2), tapez la formule suivante :
=ConcatUniq(A2:A18,",")Appuyez sur Entrée pour confirmer. La cellule affichera toutes les valeurs uniques de la plage spécifiée, séparées par des virgules.
- Si votre plage est différente, ajustez A2:A18 en conséquence.
- Si un délimiteur différent est nécessaire, remplacez "," dans la formule par votre symbole préféré (par exemple ";" ou |).
- Si vous rencontrez une erreur #NOM?, vérifiez que les macros sont activées et que le nom de la fonction UDF correspond exactement.
Astuce : Pour réutiliser cette fonction dans d'autres classeurs, copiez le code VBA dans leurs modules également.
Méthode 4 : Utiliser une formule Excel avancée (solution alternative)
Dans les environnements où la fonction UNIQUE n'est pas disponible (par exemple, dans Excel 2016 ou Excel 2019), vous pouvez toujours concaténer des valeurs uniques en utilisant une combinaison plus complexe des fonctions classiques SI, NB.SI et TEXTJOIN dans des formules matricielles. Cette approche fonctionne mais convient mieux aux petits jeux de données en raison de sa charge de calcul.
1. Dans une cellule cible (par exemple, C2), entrez la formule matricielle suivante (après avoir saisi, appuyez sur Ctrl+Shift+Entrée au lieu de simplement Entrée) :
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. Si vous voyez des accolades {} apparaître autour de votre formule, elle a été correctement saisie en tant que formule matricielle. La formule retournera les valeurs uniques concaténées de la plage A2:A18, séparées par des virgules.
Remarque : Cette méthode nécessite que vous ajustiez les plages pour qu'elles correspondent à vos données. Pour des plages très grandes, le temps de calcul peut augmenter. Si vous ne vous sentez pas à l'aise avec les formules matricielles, envisagez d'essayer les solutions VBA ou add-in décrites ci-dessus.
Lister des valeurs uniques et concaténer des valeurs correspondantes
Fréquemment, dans les rapports de données, vous pouvez ne pas seulement vouloir extraire les valeurs uniques d'une colonne, mais aussi agréger ou joindre ensemble leurs entrées correspondantes d'une autre colonne. Par exemple, consolider tous les produits vendus par chaque vendeur ou compiler toutes les entrées liées au même identifiant. Choisir la bonne méthode dépend de la complexité de vos données et de la priorité accordée à l'automatisation, la facilité d'utilisation ou la compatibilité.
Méthode 1 : Utiliser les fonctions TEXTJOIN et UNIQUE
Lorsque vous utilisez Excel 365 ou Excel 2021, vous pouvez combiner les fonctions UNIQUE et FILTRE avec TEXTJOIN pour une approche robuste et entièrement basée sur des formules. Cette méthode fonctionne bien pour résumer des données où une valeur peut être liée à plusieurs enregistrements et où vous souhaitez une liste de ces enregistrements séparés par un délimiteur.
1. Dans une colonne vide, entrez la formule suivante pour lister toutes les valeurs uniques de la Colonne A :
=UNIQUE(A2:A17)
2. Maintenant, pour concaténer les valeurs correspondantes de la Colonne B pour chaque entrée unique, dans la colonne suivante à côté de votre valeur unique (par exemple E2 si vos valeurs uniques commencent à D2), entrez cette formule et faites-la glisser vers le bas selon vos besoins :
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17) crée un tableau des éléments uniques de la Colonne A.
- FILTRE(B2:B17, A2:A17 = D2) génère un tableau contenant toutes les valeurs correspondantes de la Colonne B pour chaque valeur unique dans D2.
- TEXTJOIN(", ", TRUE, ...) joint ces valeurs correspondantes, séparées par des virgules.
- Si vous avez besoin d'un délimiteur différent, changez ", " dans TEXTJOIN en conséquence.
- Pour éviter les erreurs, assurez-vous que les plages dans vos formules ont une longueur égale et que FILTRE ne retourne pas d'erreurs pour les correspondances manquantes.
- Cette approche met à jour les résultats automatiquement lorsque vos données changent, la rendant adaptée aux tableaux de synthèse dynamiques.
Méthode 2 : Utiliser Kutools pour Excel
Kutools pour Excel propose un outil « Fusion avancée des lignes » conçu pour regrouper des données par valeurs uniques et fusionner les valeurs correspondantes avec le délimiteur de votre choix. Ceci convient aux utilisateurs qui souhaitent une solution graphique et ne sont pas à l'aise avec l'écriture de formules ou de code. Il est particulièrement précieux lorsqu'il s'agit de grands jeux de données ou lorsqu'une regroupement fréquent est requis, comme dans les rapports périodiques ou la maintenance continue des données.
Avant d'apporter des modifications, il est bon de sauvegarder vos données en copiant les données originales à un autre emplacement. Ensuite, suivez ces étapes :
- Sélectionnez la plage de données que vous souhaitez organiser.
- Naviguez jusqu'à "Kutools" > "Fusionner & Séparer" > "Fusion avancée des lignes", comme illustré ci-dessous :
- Dans la boîte de dialogue qui s'ouvre :
- Sélectionnez la colonne avec des doublons à fusionner, en la définissant comme « Clé principale » dans la colonne « Opération ».
- Choisissez la colonne que vous souhaitez agréger (les valeurs à concaténer) ; spécifiez votre délimiteur préféré dans la liste déroulante sous « Opération ».
- Cliquez sur OK pour exécuter.
Résultat :
Kutools réorganisera vos données, extrayant les entrées uniques et concaténant toutes les valeurs associées, selon votre configuration.
- Si vous faites une erreur, utilisez la fonction Annuler dans Excel (Ctrl+Z) pour revenir en arrière.
- Le processus fonctionne pour des jeux de données potentiellement composés de centaines ou milliers d'enregistrements, et prend en charge une variété de délimiteurs.
Méthode 3 : Utiliser le code VBA
Utiliser un script VBA fournit un contrôle total sur la manière dont vous extrayez et résumez les données. Cette approche est compatible avec toutes les versions d'Excel, et convient particulièrement bien aux flux de travail personnalisés, à l'automatisation, ou lorsque des fonctions comme UNIQUE ou FILTER ne sont pas disponibles. Si la structure de vos données varie fréquemment, cette solution VBA peut être facilement adaptée.
Pour utiliser le code ci-dessous, suivez simplement ces étapes :
1. Appuyez sur ALT + F11 pour ouvrir l'éditeur VBA.
2. Allez dans Insertion > Module, puis collez le code suivant dans la fenêtre de module ouverte :
Code VBA : Liste des valeurs uniques et concaténation des données correspondantes
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Appuyez sur F5 pour exécuter le script. Une fenêtre contextuelle vous demandera de sélectionner la plage de données. Assurez-vous de sélectionner exactement deux colonnes : la première pour les valeurs uniques, et la seconde pour les valeurs correspondantes.
4. Cliquez sur OK, et sélectionnez la première cellule où le tableau de résultats doit commencer.
5. Après avoir cliqué sur OK, le code générera un tableau contenant uniquement les valeurs uniques et leurs données concaténées associées.
- Si vous obtenez une erreur concernant le nombre de colonnes, vérifiez que votre sélection inclut uniquement deux colonnes.
- Si votre délimiteur doit changer d'une virgule à un autre symbole, ajustez le code dans la ligne
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)si nécessaire. - Sauvegardez toujours votre fichier avant d'exécuter de nouveaux scripts VBA.
En résumé, Excel propose une variété d'approches pour concaténer des valeurs uniques et consolider des données associées. Les méthodes basées sur des formules sont rapides et dynamiques dans les versions modernes d'Excel, tandis que les solutions VBA et Kutools offrent une compatibilité plus large et un contrôle accru. Choisissez toujours la méthode adaptée à la taille de vos données, à votre version d'Excel et à votre flux de travail préféré. N'oubliez pas de tester sur des données d'échantillon ou de sauvegarder votre travail, surtout lors de l'essai d'opérations basées sur des scripts ou en lot. Pour plus de conseils Excel et des astuces avancées, explorez notre collection complète de tutoriels.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels