Comment identifier les valeurs communes à trois colonnes dans Excel ?
Lorsque vous travaillez avec des données dans Excel, il est fréquent de comparer des listes afin d’identifier les entrées partagées ou en double. Bien que la comparaison de deux colonnes pour localiser les valeurs communes soit une tâche courante, certaines situations exigent de déterminer quelles valeurs apparaissent simultanément dans trois colonnes distinctes. Par exemple, lors de la consolidation de données issues d’enquêtes, de la fusion de relevés de ventes ou de l’analyse d’entrées en double provenant de plusieurs listes, il est essentiel d’extraire avec précision l’ensemble des éléments présents dans les trois colonnes, comme illustré dans la capture d’écran ci-dessous. Cet article présente plusieurs méthodes pratiques pour résoudre ce problème dans Excel, vous permettant d’identifier efficacement et de manière fiable les valeurs communes à trois colonnes — que vous préfériez utiliser des formules ou du code VBA.
Trouver les valeurs communes à 3 colonnes à l’aide de formules matricielles
Macro VBA pour extraire les valeurs présentes dans les trois colonnes
Trouver les valeurs communes à 3 colonnes à l’aide de formules matricielles
Pour rechercher et extraire les valeurs communes à trois colonnes, vous pouvez utiliser des formules matricielles conçues pour identifier les éléments présents dans les trois. Sélectionnez la plage appropriée. Cette approche est particulièrement utile si vous préférez ne pas recourir à des compléments Excel supplémentaires ni à des outils externes.
Saisissez cette formule matricielle dans une cellule vide où vous souhaitez afficher la première valeur commune :
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))Comment utiliser cette formule matricielle :
- Après avoir saisi la formule dans la cellule sélectionnée, appuyez sur Maj + Ctrl + Entrée (et non simplement sur Entrée). Excel entourera alors la formule d’accolades pour indiquer qu’il s’agit d’une formule matricielle.
- Faites glisser la formule vers le bas de la colonne jusqu’à l’apparition de cellules vides. Cela affichera toutes les valeurs communes aux trois colonnes ; les cellules vides indiqueront qu’il n’y a plus d’autres correspondances.
Remarques et explications des paramètres :
- Si vous préférez une autre formule matricielle, celle-ci renvoie également toutes les valeurs uniques présentes dans les trois colonnes :
Là encore, n’oubliez pas d’appuyer sur Maj + Ctrl + Entrée après avoir saisi ou collé la formule.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0)) - Dans ces formules :
- A2:A10, B2:B8 et C2:C9 sont les plages des trois colonnes que vous souhaitez comparer.
- E1 fait référence à la cellule située directement au-dessus de l’endroit où votre formule commence (dans la logique d’exclusion). Ajustez les références de cellule pour qu’elles correspondent à vos plages réelles et à l’emplacement où vous souhaitez afficher les résultats.
- Ces méthodes conviennent parfaitement aux jeux de données de taille modérée, mais peuvent considérablement ralentir sur des volumes très importants en raison de la charge de calcul engendrée par les formules matricielles.
- Veillez à ne pas modifier la plage source en cours de route, car cela pourrait entraîner des résultats inexacts ou des erreurs de formule.
- Si le résultat inclut des lignes vides, cela signifie que toutes les valeurs communes ont été extraites et que les cellules restantes ne présentent plus aucune intersection.
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.
Macro VBA pour extraire les valeurs présentes dans les trois colonnes
Si vous préférez une approche automatisée qui ne nécessite ni saisie ni copie de formules complexes, vous pouvez utiliser VBA dans Excel pour parcourir vos données et n’afficher que les valeurs présentes dans chacune des trois colonnes. Cette méthode est particulièrement adaptée aux jeux de données très volumineux ou aux plages dynamiques, car VBA gère plus efficacement les tâches répétitives et les critères personnalisés.
1. Cliquez sur Développeur > Visual Basicpour ouvrir l’éditeur VBA (si l’onglet)Développeur n’est pas visible, vous pouvez l’activer via Fichier > Options > Personnaliser le ruban).
2. Dans l’éditeur VBA, cliquez sur Insertion > Module pour créer un nouveau module. Collez ensuite le code ci-dessous dans la fenêtre du module :
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. Dans la fenêtre VBA, avec le module sélectionné, appuyez sur F5 ou cliquez sur le bouton Exécuter (▶) pour lancer le code. Vous serez invité(e) successivement à sélectionner chacune des trois plages de colonnes que vous souhaitez comparer. À chaque étape, utilisez votre souris pour sélectionner les cellules appropriées.
4. La macro traitera vos sélections et affichera, à partir de la première ligne, toutes les valeurs des trois colonnes dans la colonne vide située juste à droite de votre jeu de données actuel.
Cette méthode s’avère particulièrement efficace pour les jeux de données volumineux ou dynamiques et peut aisément être étendue à quatre colonnes ou plus en dupliquant la logique fondée sur les dictionnaires. N’oubliez jamais d’enregistrer votre classeur avant d’exécuter des macros, car les modifications non sauvegardées ne pourront pas être annulées si vous souhaitez revenir en arrière.
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