Comment comparer des valeurs séparées par des virgules situées dans deux cellules Excel et obtenir celles qui sont en double ou uniques ?
Comme le montre la capture d’écran ci-dessous, deux colonnes – Colonne1 et Colonne2 – contiennent chacune des nombres séparés par des virgules dans chaque cellule. Comment comparer les nombres de la Colonne1 avec ceux de la cellule correspondante sur la même ligne dans la Colonne2, puis extraire toutes les valeurs en double ou uniques ?
Ce tutoriel vous propose deux méthodes pour accomplir cette tâche.
Comparer des valeurs séparées par des virgules dans deux cellules et renvoyer les valeurs en double ou uniques à l’aide de formules
Cette section propose deux formules pour comparer les valeurs séparées par des virgules dans deux cellules et identifier celles qui sont en double ou uniques entre elles.
Remarque : Les formules suivantes fonctionnent uniquement dans Excel pour Microsoft 365. Si vous utilisez une autre version d’Excel, essayez la méthode VBA ci-dessous.
En prenant les deux colonnes ci-dessus comme exemple, pour comparer les nombres séparés par des virgules de la Colonne 1 avec ceux situés sur la même ligne dans la Colonne 2 et obtenir les valeurs en double ou uniques, suivez la procédure suivante.
Renvoyer Valeurs en double
1. Sélectionnez une cellule pour afficher les nombres en double entre deux cellules spécifiées contenant des nombres séparés par des virgules. Dans cet exemple, je sélectionne la cellule D2, saisissez ensuite la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez la cellule contenant la formule et faites glisser sa poignée de recopie vers le bas pour obtenir les nombres en double entre les cellules des autres lignes.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, «»)))
Renvoyer les valeurs uniques
Pour extraire les nombres uniques situés entre deux cellules spécifiées contenant des nombres séparés par des virgules sur la même ligne, la formule suivante vous sera utile.
1. Sélectionnez une cellule où afficher les nombres uniques. Dans cet exemple, j’ai choisi la cellule E2 : saisissez ensuite la formule ci-dessous et appuyez sur la touche Entrée. Sélectionnez la cellule contenant la formule, puis faites glisser sa poignée de recopie vers le bas pour obtenir les nombres uniques présents dans les cellules des lignes suivantes.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Remarques:
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Comparer deux colonnes contenant des valeurs séparées par des virgules et renvoyer les valeurs en double ou uniques à l’aide de VBA
La fonction personnalisée présentée dans cette section permet de comparer les valeurs séparées par des virgules contenues dans deux cellules spécifiées et de renvoyer les valeurs en double ou les valeurs uniques entre elles. Procédez comme suit.
En reprenant le même exemple que ci-dessus, pour comparer les nombres séparés par des virgules de la Colonne1 avec ceux situés sur la même ligne dans la Colonne2 et renvoyer les valeurs en double ou uniques, essayez la fonction définie par l’utilisateur présentée dans cette section.
1. Dans le classeur ouvert, appuyez sur les touches Alt+F11pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Dans la fenêtre Microsoft Visual Basic pour Applications, cliquez sur Insertion>Module, puis copiez le code VBA suivant dans la fenêtre Module (Code).
Code VBA : Comparer des valeurs séparées par des virgules dans deux cellules et renvoyer les valeurs en double/uniques
Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
Dim R1Arr As Variant
Dim R2Arr As Variant
Dim Ans1 As String
Dim Ans2 As String
Dim Separator As String
Dim d1 As New Dictionary
Dim d2 As New Dictionary
Dim d3 As New Dictionary
Application.Volatile
Separator = ", "
R1Arr = Split(Rng1.Value, Separator)
R2Arr = Split(Rng2.Value, Separator)
Ans1 = ""
Ans2 = ""
For Each ch In R2Arr
If Not d2.Exists(ch) Then
d2.Add ch, "1"
End If
Next
If Op Then
For Each ch In R1Arr
If d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans1 = Ans1 & ch & Separator
End If
End If
Next
If Ans1 <> "" Then
Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
End If
COMPARE = Ans1
Else
For Each ch In R1Arr
If Not d1.Exists(ch) Then
d1.Add ch, "1"
End If
Next
For Each ch In R1Arr
If Not d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
For Each ch In R2Arr
If Not d1.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
If Ans2 <> "" Then
Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
End If
COMPARE = Ans2
End If
End Function3. Après avoir collé le code dans la fenêtre Module (Code), cliquez sur Outils > Références pour ouvrir la fenêtre Références – VBAProject, cochez la case Microsoft Scripting Runtime, puis cliquez sur le bouton OK.
4. Appuyez sur les touches Alt + Q pour fermer la fenêtre Microsoft Visual Basic pour Applications.
5. Vous devez désormais appliquer deux fonctions distinctes pour extraire, respectivement, les valeurs en double et les valeurs uniques à partir de deux cellules contenant des valeurs séparées par des virgules.
Renvoyer la valeur en double
Sélectionnez une cellule pour afficher les nombres en double. Dans cet exemple, je sélectionne la cellule D2, saisissez ensuite la formule ci-dessous et appuyez sur la touche Entréepour obtenir les nombres en double entre les cellules A2 et B2.
Sélectionnez la cellule contenant la formule, puis faites glisser sa poignée de recopie vers le bas afin d’obtenir les nombres en double dans les cellules des lignes suivantes.
=COMPARE(A2,B2,TRUE)
Renvoyer les valeurs uniques
Sélectionnez une cellule pour afficher les nombres uniques. Dans cet exemple, je choisis la cellule E2 ; saisissez ensuite la formule ci-dessous et appuyez sur la touche.Entréepour obtenir les nombres uniques présents entre les cellules A2 et B2.
Sélectionnez la cellule contenant la formule, puis faites glisser sa poignée de recopie vers le bas afin d’obtenir des nombres uniques dans les cellules des lignes suivantes.
=COMPARE(A2,B2,FALSE)
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels