Comment extraire des valeurs uniques de plusieurs colonnes dans Excel ?
Si vous travaillez souvent avec des ensembles de données répartis sur plusieurs colonnes dans Excel, vous pouvez rencontrer des situations où certaines valeurs sont dupliquées dans la même colonne ou entre différentes colonnes. Dans de nombreuses tâches de création de rapports ou d'analyse de données, il devient nécessaire d'identifier et d'extraire toutes les valeurs uniques — celles qui n'apparaissent qu'une seule fois dans l'ensemble de la sélection, quel que soit leur emplacement. Faire cela manuellement peut être chronophage et sujet à erreurs, surtout lorsqu'il s'agit de grands ensembles de données ou de tableaux complexes. Heureusement, Excel offre une gamme de méthodes pour extraire efficacement ces valeurs uniques.
Ce guide présente plusieurs solutions que vous pouvez utiliser en fonction de votre version d'Excel et de vos préférences — telles que des formules adaptées à toutes les versions, des formules de tableau dynamique pour les versions récentes, l'utilisation de Kutools AI Aide pour des résultats simples, des tableaux croisés dynamiques pour une consolidation visuelle, et du code VBA pour une extraction automatisée dans des scénarios complexes.
Extraire des valeurs uniques de plusieurs colonnes avec des formules
Il y a des moments où vous souhaiteriez réaliser cette extraction avec les fonctions intégrées d'Excel. Cette section détaille comment procéder en utilisant deux approches : une formule matricielle adaptée à toutes les versions d'Excel, et une formule de tableau dynamique disponible dans les versions plus récentes comme Excel 365 et Excel 2021. Ces méthodes sont idéales lorsque vous voulez une solution basée sur des formules directes, nécessitant des mises à jour fréquentes lorsque vos données changent, ou lorsque vous souhaitez éviter les modules complémentaires externes ou le code.
Extraire des valeurs uniques de plusieurs colonnes avec une formule matricielle pour toutes les versions d'Excel
Pour assurer la compatibilité avec toutes les versions d'Excel, l'utilisation d'une formule matricielle vous permet d'extraire des valeurs uniques de plusieurs colonnes, même si votre Excel ne prend pas en charge les tableaux dynamiques. Cette approche utilise une combinaison des fonctions INDIRECT, TEXTE, MIN, SI, NB.SI, LIGNE et COLONNE, ce qui la rend flexible pour diverses structures de données.
Supposons que vos données se trouvent dans la plage A2:C9. Pour extraire les valeurs uniques en commençant dans la cellule E2, suivez la procédure suivante :
1. Cliquez sur la cellule E2 (ou la première cellule de votre plage de sortie) et saisissez la formule matricielle suivante :
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
- A2:C9 est la plage de données à partir de laquelle vous souhaitez extraire les valeurs uniques.
- E1:E1 fait référence aux cellules immédiatement au-dessus de votre première cellule de sortie et est nécessaire pour suivre les entrées déjà extraites.
- $2:$9 sont les références de ligne de vos données ; $A:$C sont les références de colonne. Ajustez-les selon vos besoins pour correspondre à la disposition de votre propre feuille de calcul.
2. Une fois que vous avez saisi la formule, au lieu de simplement appuyer sur Entrée, appuyez sur Ctrl + Maj + Entrée ensemble pour la confirmer comme une formule matricielle. Lorsque c'est fait correctement, des accolades {} apparaîtront autour de votre formule dans la barre de formule. Ensuite, faites glisser la poignée de remplissage depuis E2 vers le bas de la colonne. Continuez à faire glisser jusqu'à ce que des cellules vides apparaissent, indiquant qu'il n'y a plus de valeurs uniques à extraire. Ce processus garantit que toutes les valeurs uniques seront affichées dans la colonne cible.
- $A$2:$C$9: Spécifie l'ensemble complet des cellules à examiner pour les valeurs uniques.
- SI(($A$2:$C$9<>"")*(NB.SI($E$1:E1,$A$2:$C$9)=0), LIGNE($2:$9)*100+COLONNE($A:$C),7^8):
- $A$2:$C$9<>"" s'assure que les cellules vides sont ignorées.
- NB.SI($E$1:E1,$A$2:$C$9)=0 s'assure que seules les nouvelles valeurs (non encore extraites) sont incluses.
- Si les deux conditions sont vraies, la sortie correspondante est un calcul basé sur la ligne et la colonne de la cellule pour générer un numéro d'index unique.
- Si l'une des conditions est fausse, la formule renvoie un très grand nombre (7^8) pour éviter toute sélection accidentelle.
- MIN(...): Identifie le plus petit numéro d'index, localisant ainsi la position de la prochaine valeur unique disponible dans les données.
- TEXTE(...,"R0C00"): Convertit l'index en une référence de cellule valide en utilisant le style R1C1.
- INDIRECT(...): Convertit la référence de cellule créée ci-dessus en une valeur provenant de votre plage de données.
- &"": Force le résultat de la formule à être traité comme du texte, garantissant qu'il n'y aura pas de surprises de formatage.
Extraire des valeurs uniques de plusieurs colonnes avec une formule pour Excel 365, Excel 2021 et versions ultérieures
Si vous utilisez Excel 365, Excel 2021 ou une version plus récente, vous avez accès aux fonctions de tableau dynamique, qui offrent une manière plus simple et intuitive d'extraire des valeurs uniques de plusieurs colonnes. Les fonctions UNIQUE et TOCOL facilitent et accélèrent la combinaison des données de plusieurs colonnes et l'élimination des doublons en une seule étape — particulièrement utile pour ceux qui travaillent avec des ensembles de données constamment mis à jour ou plus volumineux.
Pour utiliser cette méthode, sélectionnez simplement une cellule vide (par exemple, E2, ou là où vous souhaitez que les résultats apparaissent), saisissez cette formule, puis appuyez sur Entrée :
=UNIQUE(TOCOL(A2:C9,1))Après avoir appuyé sur Entrée, toutes les valeurs uniques de la plage A2:C9 se déverseront automatiquement dans les cellules sous la formule. Cette fonctionnalité est particulièrement efficace — la sortie se met à jour dynamiquement lorsque vos données sources changent, vous faisant économiser des étapes de rafraîchissement manuel.
- TOCOL(A2:C9,1): Convertit votre plage de valeurs de plusieurs colonnes en une seule colonne, supprimant automatiquement les cellules vides.
- UNIQUE(...): Extrait chaque valeur une seule fois, fournissant une liste propre et sans doublons.
Extraire des valeurs uniques de plusieurs colonnes avec Kutools AI Aide
Si vous préférez une approche plus simplifiée et souhaitez réduire les efforts manuels, Kutools AI Aide dans Kutools pour Excel peut vous aider à extraire facilement des valeurs uniques de plusieurs colonnes. Cette méthode est particulièrement précieuse si vous ne connaissez pas bien les formules ou si vous souhaitez éviter le risque d'erreurs de formule. Kutools AI Aide interprète vos instructions et traite les données automatiquement, ce qui est idéal tant pour les débutants que pour les utilisateurs recherchant une solution rapide en quelques clics.
Après installation, cliquez sur Kutools AI > AI Aide pour ouvrir le panneau "Kutools AI Aide" :
- Saisissez votre demande dans la boîte de chat, par exemple : "Extrayez les valeurs uniques de la plage A2:C9, en ignorant les cellules vides, et placez les résultats à partir de E2 :"
- Cliquez sur "Envoyer" ou appuyez sur Entrée, puis après analyse de la demande par l'IA, cliquez simplement sur "Exécuter" pour lancer. Les résultats apparaîtront instantanément dans votre feuille de calcul, à l'emplacement exact que vous avez indiqué.
Astuce : Cette solution est très utile si votre flux de travail d'extraction de données varie ou si vous souhaitez des fonctionnalités de traitement en langage naturel. N'oubliez pas de revérifier la liste extraite pour les cellules vides si vos données originales ne sont pas parfaitement cohérentes, car les entrées vides pourraient être incluses ou filtrées en fonction des détails de votre demande IA.
Extraire des valeurs uniques de plusieurs colonnes avec un Tableau Croisé Dynamique
Les tableaux croisés dynamiques constituent une autre méthode pratique pour extraire des valeurs uniques, en particulier si vous préférez travailler avec des outils visuels et souhaitez résumer ou analyser davantage les éléments uniques, comme compter les occurrences. Cette approche est simple et ne nécessite pas de formules. Cependant, elle nécessite quelques étapes de configuration et un léger réarrangement des données, surtout si les colonnes concernées ont des titres différents.
Voici un processus suggéré pour extraire des valeurs uniques à l'aide d'un tableau croisé dynamique :
1. Insérez une nouvelle colonne vide juste à gauche de vos données. Par exemple, insérez une nouvelle colonne A si vos données commencent à la colonne B. Ce réglage aide à assurer une consolidation correcte de la plage.
2. Sélectionnez une cellule dans votre ensemble de données, appuyez sur Alt + D, puis rapidement sur P pour lancer l'"Assistant Tableau croisé dynamique et graphique croisé dynamique." Dans la première étape de l'assistant, sélectionnez "Plages de consolidation multiples." Cela vous permet de combiner les valeurs de nombreuses colonnes en un seul champ résumé.
3. Cliquez sur Suivant, puis choisissez "Créer un seul champ de page pour moi." Cette étape organise toutes vos données en un seul groupe pour une extraction plus facile des valeurs uniques.
4. À l'étape suivante, sélectionnez l'intégralité de la plage de données (incluez la nouvelle colonne vide), cliquez sur le bouton Ajouter pour amener votre sélection dans la liste "Toutes les plages", puis cliquez sur Suivant.
5. À la dernière étape de l'assistant, sélectionnez où vous souhaitez placer le tableau croisé dynamique (nouvelle feuille de calcul ou feuille existante), puis cliquez sur Terminer pour générer le rapport de tableau croisé dynamique.
6. Dans le nouveau tableau croisé dynamique, décochez tous les champs dans la section "Choisir les champs à ajouter au rapport" pour effacer la vue par défaut.
7. Enfin, faites glisser le champ "Valeur" vers la zone Lignes. Le tableau croisé dynamique affichera toutes les valeurs uniques de votre plage multi-colonnes d'origine, organisées de manière nette dans une seule colonne.
Limitations : Les données nécessitent un arrangement préalable, et si votre ensemble de données source est mis à jour, vous devez actualiser le tableau croisé dynamique pour voir les nouvelles valeurs uniques.
Extraire des valeurs uniques de plusieurs colonnes avec du code VBA
Dans les cas où vous devez automatiser l'extraction ou gérer de grands ensembles de données irréguliers, utiliser du code VBA (Visual Basic for Applications) peut fournir une solution rapide et réutilisable. Cela est idéal pour les utilisateurs ayant une connaissance de base de l'éditeur VBA Excel, ou pour des tâches récurrentes où vous souhaitez minimiser les opérations manuelles. VBA peut également gérer de grands volumes de données plus efficacement que les formules matricielles.
1. Ouvrez l'éditeur VBA en appuyant sur Alt + F11. Dans la fenêtre "Microsoft Visual Basic for Applications" qui apparaît, cliquez sur Insertion > Module pour ajouter un nouveau module.
2. Dans le nouveau module, collez le code ci-dessous :
VBA : Extraire des valeurs uniques de plusieurs colonnes
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Appuyez sur F5 pour exécuter le code. Une boîte de dialogue vous invitera à sélectionner la plage de données. Sélectionnez toutes les colonnes pertinentes (y compris celles avec des cellules vides).
4. Après avoir cliqué sur OK, une autre invite demande où placer les valeurs uniques. Spécifiez une cellule de départ où vous souhaitez que les résultats soient listés (par exemple, E2).
5. Cliquez sur OK, et la macro s'exécutera automatiquement. Toutes les valeurs uniques apparaîtront, en commençant à l'emplacement spécifié.
- Si vous recevez des erreurs comme #VALEUR! ou #DÉBORDEMENT! en utilisant des formules, vérifiez vos plages et assurez-vous que la zone de sortie est claire.
- Vérifiez toujours les lignes masquées ou les cellules fusionnées dans votre plage de données, car elles peuvent affecter la justesse de votre extraction de valeurs uniques.
- Les formules matricielles et de tableau dynamique se mettent à jour automatiquement avec les modifications, mais les solutions Filtre avancé et Tableau croisé dynamique peuvent nécessiter un rafraîchissement ou une exécution manuelle.
- Pour les tâches récurrentes, envisagez d'automatiser l'extraction à l'aide de VBA pour plus de cohérence et de rapidité.
- Sauvegardez vos données avant d'appliquer des routines d'extraction ou d'automatisation en masse, surtout dans des classeurs complexes.
Plus d'articles relatifs :
- Compter le Nombre de Valeurs Uniques et Distinctes d'une Liste
- Supposons que vous ayez une longue liste de valeurs avec certains doublons, et que vous souhaitiez compter combien de valeurs uniques (les valeurs qui n'apparaissent qu'une seule fois) ou de valeurs distinctes totales existent dans une colonne, comme indiqué dans la capture d'écran de gauche. Cet article explique des méthodes efficaces pour compter les entrées uniques et distinctes dans Excel.
- Extraire des Valeurs Uniques Basées sur des Critères dans Excel
- Supposons que vous souhaitiez extraire uniquement les noms uniques de la colonne B en fonction d'une condition spécifique dans la colonne A, produisant des résultats comme illustré dans la capture d'écran. Ce tutoriel montre des moyens d'appliquer des critères lors de l'extraction de valeurs uniques.
- N'autoriser que des Valeurs Uniques dans Excel
- Si vous souhaitez autoriser uniquement des entrées uniques dans une colonne de feuille de calcul et empêcher les valeurs en double, cet article présente des techniques pratiques pour appliquer des règles d'unicité dans Excel.
- Sommer des Valeurs Uniques Basées sur des Critères dans Excel
- Par exemple, vous pourriez avoir besoin de sommer uniquement les valeurs uniques dans une colonne "Commande" en fonction des noms dans une colonne adjacente, comme montré dans la capture d'écran. Cet article discute des approches pour combiner des calculs uniques et conditionnels.
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