Extraire les données de doublons avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Extraire les informations multiples associées aux doublons

En guise de doublons, nous souhaitons considérer des homonymes ou des noms redondants. Nous traitons ici une liste volumineuse dans un tableau de base de données Excel. Il s'agit de la liste des communes françaises triées par ordre alphabétique. Certaines communes ont exactement la même orthographe et donc la même prononciation mais ne correspondent pas à la même ville, car situées dans d'autres départements. En conséquence ces villes sont différenciées par un code INSEE unique inscrit en colonne C, comme l'illustre la capture ci-dessous.



Par formule Excel, l'objectif est de pouvoir extraire tous les codes INSEE correspondant à la saisie du nom d'une commune.

Base de données Excel
Pour réaliser ce travail, nous partons d'un classeur existant dans lequel la base de données des communes a été importée.

• Télécharger le classeur Excel extraire_donnees-doublons.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Ce classeur est constitué de deux feuilles. La première feuille, Liste des communes, est la base de données illustrée par la capture ci-dessus. La seconde, Extraction doublons, est la feuille dans laquelle nous devons bâtir les formules capables d'extraire toutes les informations uniques, des noms de communes identiques.

• Cliquer sur l'onglet Extraction doublons pour activer sa feuille,

La cellule C1 reçoit la saisie du nom de la commune. En fonction de cette saisie, une plage d'extraction est prévue de F2 à F10, pour afficher tous les codes INSEE potentiels, rattachés aux communes, dont l'orthographe est identique. Nous partons du principe qu'il n'y a pas plus de 9 doublons par commune.


Les calculs devront donc être écrits dans chaque cellule. Et comme les fonctions de recherche usuelles comme Index, Equiv ou RechercheV, ne renvoient que lapremière occurrence trouvée, il faudra être capable, pour les calculs suivants, de poursuivre la recherche, en partant de la ligne de la précédente occurrence trouvée.

Pour rendre les formules plus lisibles, nous allons décomposer le calcul en utilisant une colonne intermédiaire pour inscrire les numéros de ligne de la dernière occurrence trouvée. Comme nous l'avait apprise la formation Excel pour rechercher dans des tableaux, c'est la fonction Equiv qui renvoie le numéro de ligne d'une recherche. De même, nous allons simplifier les noms des feuilles en les renommant.

• Sélectionner la cellule G1 de la feuille Extraction doublons,
• Saisir le chiffre 0 et valider,

C'est la valeur de référence qui sera utilisée pour la première recherche dynamique. Comme aucune précédente recherche n'aura été effectuée, l'extraction devra considérer le tableau de recherche complet, sans le décaler vers le bas.

• Cliquer avec le bouton droit de la souris sur l'onglet Liste des communes, en bas de la feuille,
• Dans le menu contextuel, choisir Renommer,
• Puis, saisir communes à la place et valider,
• Cliquer avec le bouton droit de la souris sur l'onglet de la feuille Extraction doublons,
• Dans le menu contextuel, choisir Renommer,
• Puis, saisir extraction à la place et valider,

En effet, lorsque les formules interviennent de feuille à feuille, les cellules désignées sont préfixées des noms de feuilles. Avec des noms plus courts, les syntaxes des calculs s'en trouveront allégées.

Extraire les données dynamiquement
Il s'agit donc de bâtir une formule de recherche sur un texte, qui soit capable de reprendre là où elle en était, pour extraire toutes les informations associées aux doublons potentiels qui suivent, dans ce tableau de données, trié croissant sur le nom des communes.

• Activer la feuille Extraction,
• Sélectionner les cellules du calcul pour l'extraction, soit la plage F2:F10,
• Taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction de recherche suivi d'une parenthèse ouvrante, soit Index(,
• Cliquer sur l'onglet de la feuille communes pour la sélectionner,
• Cliquer sur la première cellule du tableau de recherche, soit B1,
• Faire défiler le tableau jusqu'à la dernière ligne à l'aide de l'ascenseur vertical,
• En maintenant la touche MAJ enfoncée (Shift), cliquer sur la dernière cellule, soit E39200,
• Enfoncer la touche F4 du clavier pour figer la plage de recherche,
• Taper un point-virgule pour passer à l'argument de la ligne de la fonction Excel Index,

La touche F4 du clavier est utilisée pour les références absolues, afin que les bornes du tableau de recherche ne se déplacent pas en même temps que le calcul est répliqué. Le deuxième argument de la fonction Excel Index, consiste à indiquer le numéro de ligne où se trouve l'information à extraire depuis le tableau de données. Nous ne le connaissons pas. Il dépend du nom de commune saisi en cellule C1 de la feuille Extraction. Et, c'est la fonction Excel Equiv qui permet de retourner le numéro de ligne d'une information recherchée. Nous devons donc l'imbriquer dans la fonction Index.

• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit Equiv(,
• Sélectionner la cellule C1 de la feuille Extraction,
• Puis, enfoncer la touche F4 afin qu'elle soit toujours l'élément de référence à rechercher,
• Taper un point-virgule pour passer à l'argument de la plage de recherche,

La syntaxe de la formule non terminée à ce stade, est la suivante :

=index( communes!$B$1:$E$39200; equiv( Extraction!$C$1;

L'argument à suivre est donc la colonne de recherche. Il s'agit précisément de la plage qui doit évoluer dynamiquement, en fonction du dernier élément extrait, pour trouver le doublon suivant. La recherche doit partir de la ligne de l'information précédemment extraite pour ne pas risquer de l'extraire à nouveau. Nous ne pouvons donc pas désigner une plage fixe. C'est la fonction Excel Decaler qui permet de faire varier dynamiquement une plage de cellules.

• Taper le nom de la fonction suivi d'une parenthèse ouvrante, soit Decaler(,
• Cliquer sur l'onglet de la feuille communes pour l'activer,
• Puis, cliquer sur l'étiquette de colonne B afin de désigner le début de la plage de recherche,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper un point-virgule pour passer à l'argument du décalage de la recherche en ligne,

Par rapport à ce point de départ pour la recherche du nom de la commune, l'analyse doit se poursuivre à partir de l'indice de ligne précédemment trouvé, pour la dernière information extraite. Cette information sera livrée dans la colonne G de la feuille Extraction.

• Cliquer sur l'onglet de la feuille Extraction pour l'activer,
• Cliquer sur la cellule G1 pour la désigner,
• Puis taper un point-virgule pour passer à l'argument du décalage en colonne,
• Saisir le chiffre 0 et taper un nouveau point-virgule pour passer à l'argument de la hauteur,

En effet, la recherche s'effectuera immuablement sur le nom de la commune en colonne B. Il n'est donc pas question de décaler cette plage de recherche sur la largeur. C'est la raison pour laquelle nous fixons cet argument à zéro. Nous n'avons pas figé la cellule G1 puisqu'il s'agira de décaler la plage de la recherche, en fonction des indices de ligne renvoyés par la colonne G, dans les cellules du dessous. Il s'agit maintenant de définir la hauteur (Nombres de cellules) de la plage sur laquelle nous effectuons la recherche des noms de communes. Nous souhaitons indiquer à la fonction de réaliser cette recherche tant que des valeurs sont inscrites. C'est pourquoi, nous allons utiliser la fonction Excel NBVal qui permet de compter le nombre de cellules non vides et de renvoyer cette valeur. Cette donnée servira à définir la hauteur de la plage de cellules sur laquelle rechercher les doublons à extraire.

• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit NbVal(,
• Cliquer sur l'onglet de la feuille communes pour l'activer,
• Cliquer alors sur l'étiquette de colonne B pour indiquer la plage sur laquelle compter,
• Enfoncer la touche F4 du clavier pour la figer,
• Fermer la parenthèse de la fonction Decaler,

En effet le dernier argument de la fonction Excel Decaler concerne la largeur. Il s'agit d'un paramètre facultatif. Cette largeur est fixe puisque la recherche s'effectue seulement en colonne B. Donc nous ignorons ce dernier argument.

Après avoir fermé la parenthèse de la fonction Decaler, nous sommes de retour dans la fonction Equiv, plus précisément au niveau de l'argument du tableau de recherche.



Il s'agit désormais d'indiquer que nous souhaitons une recherche avec une correspondance exacte. En effet, nous cherchons à extraire des doublons de noms de communes en fonction de leur orthographe. Pour ce faire :

• Taper un point-virgule et saisir le chiffre 0,
• Puis, fermer la parenthèse de la fonction Excel Equiv,

Comme la fonction Decaler a permis de décaler la recherche sur la dernière ligne extraite, l'indice de ligne renvoyé par la fonction Equiv est celui de la première occurrence trouvée. En relatif, cela signifie qu'il s'agit de la première ligne que nous devons incrémenter du dernier indice extrait pour le nom de commune, situé en colonne G.

• Taper le symbole + du pavé numérique,
• Cliquer sur l'onglet de la feuille Extraction pour l'activer,
• Puis, cliquer de nouveau sur la cellule G1 pour la désigner,
• Taper un point-virgule pour passer à l'argument de la colonne de recherche de la fonction Index,
• Saisir le chiffre 2 et fermer la parenthèse de la fonction Index,
• Réaliser le raccourci clavier CTRL + Entrée pour valider le calcul et le répliquer,

Le calcul est en effet reproduit sur l'ensemble des cellules présélectionnées. A ce stade cependant, c'est toujours le même code Insee de la commune saisie qui s'affiche. Il s'agit tout simplement du code Insee de la première occurrence trouvée dans la base de données. La syntaxe complète du calcul est la suivante :

=INDEX( communes!$B$1:$E$39200; EQUIV( Extraction!$C$1;DECALER( communes!$B:$B; Extraction!G1;0; NBVAL( communes!$B:$B));0) + Extraction!G1;2)



La raison est évidente, la recherche reprend toujours du début du tableau et non de la dernière occurrence trouvée, puisque qu'aucune information de ligne n'est encore indiquée en colonne G. De fait, et comme nous l'avions évoqué, une fonction de base de données, extrait toujours la première occurrence trouvée. Nous devons donc exploiter la fonction Excel Equiv pour indiquer l'indice de ligne où se situe le dernier code Insee extrait.

• Dans la feuille Extraction, sélectionner la plage de cellules G2:G10,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit Equiv(,
• Sélectionner le premier code Insee extrait, soit la cellule F2,
• Puis, taper un point-virgule pour passer à l'argument de la plage de recherche,
• Cliquer sur l'onglet de la feuille communes pour l'activer,
• Cliquer sur l'étiquette de colonne C où se trouvent les codes Insee,
• Taper un point-virgule pour passer à l'argument de la correspondance souhaitée,
• Saisir le chiffre 0 pour une correspondance exacte et fermer la parenthèse de la fonction,
• Valider le calcul avec le raccourci CTRL + Entrée pour le répliquer sur toutes les cellules,

Cette fois, la recherche s'effectue convenablement puisque pour un même nom de commune, tous les codes Insee uniques sont bien extraits et affichés. Vous pouvez le vérifier en parcourant la source de données et en modifiant à votre guise, le nom de la commune recherchée. Cependant, des erreurs disgracieuses (#N/A) persistent. Lorsque l'élément demandé n'est pas trouvé, aucune information correspondante ne peut être extraite. C'est par ce message d'erreur que les fonctions de recherche le signalent. Pour un résultat plus propre, il s'agit de gérer ces exceptions grâce à la fonction Excel conditionnelle SI. Pour valider le critère, nous allons exploiter la fonction booléenne EstErreur qui permettra de tester la recherche effectuée. Si une erreur est interceptée, la fonction EstErreur retournera la valeur Vrai, auquel cas la recherche devra être annulée pour laisser les cellules vides.

• Sélectionner les cellules d'extraction des codes Insee, soit la plage F2:F10,
• Enfoncer la touche F2 du clavier pour activer la saisie des formules,
• Intégrer l'imbrication des fonctions de recherche comme suit :

=SI( ESTERREUR( INDEX( communes!$B$1:$E$39200;EQUIV( Extraction!$C$1; DECALER( communes!$B:$B; Extraction!G1;0; NBVAL( communes!$B:$B));0)+ Extraction!G1;2));''; INDEX( communes!$B$1:$E$39200; EQUIV( Extraction!$C$1;DECALER( communes!$B:$B; Extraction!G1;0; NBVAL( communes!$B:$B));0) + Extraction!G1;2))

Si la recherche retourne une erreur (=SI( ESTERREUR( INDEX(...), alors nous n'inscrivons rien dans la cellule du calcul (;''). Sinon, nous effectuons bien la recherche (INDEX...) ce qui explique pourquoi l'imbrication des fonctions est écrite deux fois. Pour gérer les erreurs des retours d'indices de la colonne G, la méthode est plus simple. Il suffit de tester si un code Insee est retourné ou non.

• Sélectionner la plage de cellules G2:G10,
• Enfoncer la touche F2 du clavier pour accéder au mode saisie de la formule,
• Modifier le calcul selon la syntaxe suivante :

=SI(F2=''; ''; EQUIV(F2; communes!C:C;0))

Si la recherche du code Insee est vide (F2=''), alors nous ne tentons pas d'extraire l'indice de ligne correspondant (''). Sinon, nous recherchons bien le numéro de ligne du code Insee extrait (EQUIV(F2; communes!C:C; 0)).



Nous allons parfaire cette application fort utile pour extraire les informations de doublons et/ou d'homonymes. Pour cela, nous allons réaliser quelques réglages.

• Cliquer avec le bouton droit de la souris sur l'étiquette de colonne G,
• Dans le menu contextuel, choisir Masquer,

Ainsi les résultats intermédiaires des indices de lignes pour les fonctions d'extraction, ne sont plus visibles. Mais comme ils ne sont pas supprimés, ils servent toujours effectivement pour les calculs.

• Cliquer sur l'onglet de la feuille communes pour activer cette dernière,
• Cliquer sur l'étiquette de colonne B pour la sélectionner intégralement,
• Cliquer ensuite sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, pointer sur la rubrique Règles de mise en surbrillance des cellules,
• Dans la sous liste qui s'affiche, sélectionner Valeurs en double,
• Dans la boîte de dialogue qui apparaît, accepter le format par défaut en cliquant sur Ok,

Ainsi, toutes les valeurs en double sont mises en évidence grâce au format dynamique d'Excel dans la colonne B. A ce titre, nous constatons que leur densité est relativement élevée.

• Revenir sur la feuille Extraction,
• En C1, saisir le nom de commune :AIGREMONT puis valider par Entrée,

Comme vous le constatez, cette ville avec exactement la même orthographe existe dans quatre départements puisque nos fonctions d'extraction retournent quatre codes Insee uniques. La mise en valeur conditionnelle de la feuille communes le confirme d'ailleurs.