Table des matières
Cet article vous prendra environ 10 minutes à lire. Au cours des 5 prochaines minutes, vous pouvez facilement comparer deux colonnes dans Excel et savoir s'il y a des doublons, les supprimer ou les mettre en surbrillance en couleur. Donc, le moment est venu!
Excel est une application très puissante et vraiment cool pour créer et traiter de grandes quantités de données. Si vous avez plusieurs classeurs avec des données (ou juste une énorme table), alors vous voudrez probablement comparer 2 colonnes, trouver des valeurs en double, puis faire quelque chose avec elles, par exemple, supprimer, mettre en surbrillance ou effacer le contenu . Les colonnes peuvent être dans le même tableau, être adjacentes ou non adjacentes, peuvent être situées sur 2 feuilles différentes ou même dans des livres différents.
Imaginez que nous ayons 2 colonnes avec les noms des personnes - 5 noms par colonne A et 3 noms dans une colonne B. Vous devez comparer les noms dans ces deux colonnes et trouver les doublons. Comme vous le comprenez, il s'agit de données fictives, prises uniquement à titre d'exemple. Dans les vraies tables, on a affaire à des milliers voire des dizaines de milliers d'enregistrements.
Option A: les deux colonnes sont sur la même feuille. Par exemple, une colonne A et colonne B.
Option B: Les colonnes sont sur des feuilles différentes. Par exemple, une colonne A sur la feuille Sheet2 et colonne A sur la feuille Sheet3.
Excel 2013, 2010 et 2007 ont un outil intégré Supprimer les doublons (Supprimer les doublons) mais il est impuissant dans cette situation car il ne peut pas comparer les données dans 2 colonnes. De plus, il ne peut supprimer que les doublons. Il n'y a pas d'autres options telles que la mise en surbrillance ou la modification des couleurs. Et pointe !
Ensuite, je vais vous montrer les moyens possibles de comparer deux colonnes dans Excel, ce qui vous permettra de rechercher et de supprimer les enregistrements en double.
Comparez 2 colonnes dans Excel et trouvez les entrées en double à l'aide de formules
Option A : les deux colonnes sont sur la même feuille
- Dans la première cellule vide (dans notre exemple, il s'agit de la cellule C1), nous écrivons la formule suivante :
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
Dans notre formule A1 c'est la première cellule de la première colonne que nous allons comparer. 1 $B$ и 10000 $B$ ce sont les adresses des première et dernière cellules de la deuxième colonne, avec lesquelles nous allons effectuer la comparaison. Notez les références absolues - les lettres de colonne et les numéros de ligne sont précédés du signe dollar ($). J'utilise des références absolues pour que les adresses de cellule restent les mêmes lors de la copie de formules.
Si vous voulez trouver des doublons dans une colonne B, modifiez les références afin que la formule ressemble à ceci :
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")À la place "Seulement" et "Dupliquer» Vous pouvez écrire vos propres étiquettes, par exemple, «Introuvable" et "Trouvés", ou ne laisser que "Dupliquer' et saisissez un espace à la place de la deuxième valeur. Dans ce dernier cas, les cellules pour lesquelles aucun doublon n'est trouvé resteront vides et, je crois, cette représentation des données est la plus pratique pour une analyse plus approfondie.
- Maintenant, copions notre formule dans toutes les cellules de la colonne C, jusqu'à la ligne du bas, qui contient les données de la colonne A. Pour ce faire, déplacez le pointeur de la souris dans le coin inférieur droit de la cellule C1, le pointeur prendra la forme d'un réticule noir, comme illustré dans l'image ci-dessous :Cliquez et maintenez enfoncé le bouton gauche de la souris et faites glisser la bordure du cadre vers le bas, en mettant en surbrillance toutes les cellules où vous souhaitez insérer la formule. Lorsque toutes les cellules requises sont sélectionnées, relâchez le bouton de la souris :
Cliquez et maintenez enfoncé le bouton gauche de la souris et faites glisser la bordure du cadre vers le bas, en mettant en surbrillance toutes les cellules où vous souhaitez insérer la formule. Lorsque toutes les cellules requises sont sélectionnées, relâchez le bouton de la souris :
Conseil: Dans les grands tableaux, la copie de la formule sera plus rapide si vous utilisez des raccourcis clavier. Mettre une cellule en surbrillance C1 et appuyez sur Ctrl + C (pour copier la formule dans le presse-papiers), puis cliquez sur Ctrl + Maj + Fin (pour sélectionner toutes les cellules non vides dans la colonne C) et enfin appuyez sur Ctrl + V (pour insérer la formule dans toutes les cellules sélectionnées).
- Génial, maintenant toutes les valeurs en double sont marquées comme "Dupliquer":
Option B : deux colonnes sont sur des feuilles différentes (dans des classeurs différents)
- Dans la première cellule de la première colonne vide de la feuille de calcul Sheet2 (dans notre cas c'est la colonne B) saisissez la formule suivante :
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Ici Sheet3 est le nom de la feuille sur laquelle se trouve la 2ème colonne, et $ A $ 1: $ A $ 10000 sont les adresses de cellule de la 1ère à la dernière dans cette 2ème colonne.
- Copier la formule dans toutes les cellules d'une colonne B (identique à l'option A).
- Nous obtenons ce résultat :
Traitement des doublons trouvés
Super, nous avons trouvé des entrées dans la première colonne qui sont également présentes dans la deuxième colonne. Maintenant, nous devons faire quelque chose avec eux. Parcourir manuellement tous les enregistrements en double d'une table est assez inefficace et prend trop de temps. Il existe de meilleures façons.
Afficher uniquement les lignes en double dans la colonne A
Si vos colonnes n'ont pas d'en-têtes, vous devez les ajouter. Pour ce faire, placez le curseur sur le nombre qui représente la première ligne, et il se transformera en une flèche noire, comme le montre la figure ci-dessous :
Faites un clic droit et sélectionnez dans le menu contextuel Insertion (Insérer):
Donnez des noms aux colonnes, par exemple, "Nom" et "Dupliquer?» Ouvrez ensuite l'onglet Données (Données) et appuyez sur Filtre (Filtre):
Après cela, cliquez sur la petite flèche grise à côté de "Dupliquer?« pour ouvrir le menu des filtres ; décochez tous les éléments de cette liste sauf Dupliqueret appuyez sur OK.
C'est tout, maintenant vous ne voyez que les éléments de la colonne А, qui sont dupliqués dans la colonne В. Il n'y a que deux cellules de ce type dans notre tableau de formation, mais, comme vous le comprenez, dans la pratique, il y en aura beaucoup plus.
Pour afficher à nouveau toutes les lignes d'une colonne А, cliquez sur le symbole de filtre dans la colonne В, qui ressemble maintenant à un entonnoir avec une petite flèche, puis sélectionnez Sélectionner tout (Tout sélectionner). Ou vous pouvez faire la même chose via le ruban en cliquant sur Données (Données) > Sélectionner et filtrer (Trier et filtrer) > Tansparent (Effacer) comme indiqué dans la capture d'écran ci-dessous :
Changer la couleur ou mettre en surbrillance les doublons trouvés
Si les notes "Dupliquer” n'est pas suffisant pour vos besoins et vous souhaitez marquer les cellules en double avec une couleur de police différente, une couleur de remplissage ou une autre méthode…
Dans ce cas, filtrez les doublons comme indiqué ci-dessus, sélectionnez toutes les cellules filtrées et cliquez sur Ctrl + 1pour ouvrir la boîte de dialogue Format de cellule (format cellule). Par exemple, changeons la couleur de remplissage des cellules dans les lignes avec des doublons en jaune vif. Bien sûr, vous pouvez changer la couleur de remplissage avec l'outil Niveau de remplissage Onglet (Couleur de remplissage) Accueil (Accueil) mais avantage de la boîte de dialogue Format de cellule (Format de cellule) en ce sens que vous pouvez configurer toutes les options de formatage en même temps.
Maintenant, vous ne manquerez certainement aucune cellule avec des doublons :
Suppression des valeurs en double de la première colonne
Filtrez le tableau afin que seules les cellules avec des valeurs en double soient affichées, puis sélectionnez ces cellules.
Si les 2 colonnes que vous comparez sont sur des feuilles différentes, c'est-à-dire, dans différentes tables, cliquez avec le bouton droit sur la plage sélectionnée et sélectionnez Supprimer la ligne (Supprimer la ligne):
Presse OKlorsqu'Excel vous demande de confirmer que vous voulez vraiment supprimer la ligne entière de la feuille, puis effacez le filtre. Comme vous pouvez le voir, seules les lignes avec des valeurs uniques restent :
Si 2 colonnes sont sur la même feuille, proches les uns des autres (adjacents) ou non proches les uns des autres (non adjacents), le processus de suppression des doublons sera un peu plus compliqué. Nous ne pouvons pas supprimer la ligne entière avec des valeurs en double, car cela supprimera également les cellules de la deuxième colonne. Donc, pour ne laisser que des entrées uniques dans une colonne А, faites ceci:
- Filtrez le tableau pour n'afficher que les valeurs en double et sélectionnez ces cellules. Faites un clic droit dessus et sélectionnez dans le menu contextuel Effacer le contenu (contenu clair).
- Nettoyez le filtre.
- Sélectionner toutes les cellules d'une colonne А, à partir de la cellule A1 jusqu'en bas contenant les données.
- Cliquez Données (Données) et appuyez sur Trier de A à Z (Trier de A à Z). Dans la boîte de dialogue qui s'ouvre, sélectionnez Continuer avec la sélection actuelle (Trier dans la sélection spécifiée) et cliquez sur le bouton Noir (Tri):
- Supprimez la colonne avec la formule, vous n'en aurez plus besoin, à partir de maintenant vous n'avez que des valeurs uniques.
- Ça y est, maintenant la colonne А contient uniquement des données uniques qui ne figurent pas dans la colonne В:
Comme vous pouvez le voir, supprimer les doublons de deux colonnes dans Excel à l'aide de formules n'est pas si difficile.