Repérer visuellement les redondances 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 :


Identifier les répétitions

Repérer facilement et rapidement les répétitions dans les bases de données est un enjeu souvent capital. C'est ainsi que nous pouvons juger de l'état de la source d'informations et entreprendre des travaux destinés à la nettoyer.



Pour appréhender les techniques à mettre en place, nous proposons d'agir sur des données numériques.

Source et présentation du concept
Pour étayer les démonstrations, comme toujours, nous suggérons de débuter les travaux à partir d'un classeur Excel offrant une structure et des fonctionnalités.

• Télécharger le classeur alertes-visuelles-repetitions.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur l'unique feuille de ce classeur. Elle est nommée Redondances. Elle offre un tableau rempli de valeurs numériques bornées. Ces dernières sont générées aléatoirement. En sélectionnant la cellule C5 de ce tableau et en consultant sa barre de formule, vous constatez qu'il s'agit d'une formule unique répliquée sur l'intégralité de la plage.



Nous exploitons la fonction Excel ALEA.ENTRE.BORNES. Ici, avec les valeurs qui lui sont passées en paramètres, elle produit des nombres entiers nécessairement compris entre 1 et 20 inclus. Comme ce tableau est constitué de 64 cases, des redondances sont forcément générées.

Sur la droite du tableau, en colonne N, vous remarquez la présence d'un bouton et d'une légende.



Tous les calculs d'une feuille Excel s'actualisent sur évènement. Il peut s'agir d'une cellule modifiée par exemple. Pour plus de confort, nous ordonnons le recalcul au clic sur le bouton Générer. De fait, à chaque clic, la fonction ALEA.ENTRE.BORNES produit de nouvelles valeurs.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Dès lors, double cliquer sur le bouton Générer de la feuille Excel,

De fait, nous basculons dans l'éditeur de code VBA Excel, entre les bornes de la procédure évènementielle Generer_Click.

Private Sub Generer_Click()
Application.Calculate
End Sub

Il s'agit du code déclenché sur l'évènement du clic engagé par l'utilisateur sur le bouton Générer. La méthode Calculate de l'objet VBA Application intime l'ordre aux fonctions de se recalculer. Mais soyez sans crainte, il s'agit de la seule apparition du code VBA dans cette application.

Alertes visuelles sur les redondances
De retour sur la feuille Excel, après avoir fermé l'éditeur VBA, vous notez la présence d'une légende sous le bouton encore sélectionné. Pour plus de pertinence et pour un meilleur état des lieux, elle suggère d'augmenter la densité de couleur avec la répétition des redondances. Plus un nombre est répété, plus sa couleur doit être sombre et dense.

Comme vous le savez, c'est une règle de mise en forme conditionnelle qui permet de faire réagir dynamiquement les cellules en fonction de leur contenu. Cette règle consiste à exploiter la fonction Excel de dénombrement conditionnel. Plus précisément, il s'agit de la fonction Nb.Si :

=Nb.Si(Plage_où_compter_le_critère; Critère)

En premier argument, nous devons donc lui indiquer la plage de cellules sur laquelle le critère doit être dénombré. Il s'agit du tableau des nombres aléatoires. En deuxième argument, nous devons spécifier le critère. Ce critère n'est autre que chacune des valeurs numériques du tableau. Ainsi, elles seront toutes comptabilisées par le jeu des références absolues à exploiter à bon escient. Et si le décompte est important, la couleur à déclencher doit être ajustée.

• Dans le ruban Développeur, cliquer sur le bouton Mode création,

Nous désactivons ainsi la conception du bouton.

• Ensuite, sélectionner tous les nombres du tableau soit la plage de cellules C5:J12,
• En haut de la fenêtre Excel, cliquer sur l'onglet Accueil pour activer son ruban,
• Dans la section Styles du ruban, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Taper alors le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction de dénombrement suivie d'une parenthèse, soit : Nb.Si(,
• Désigner l'ensemble du tableau en premier argument, soit la plage de cellules C5:J12,

Par défaut, dans une règle de mise en forme conditionnelle, une plage de cellules désignée apparaît totalement figée : $C$5:$J$12. C'est bien ce que nous souhaitons. Chaque nombre doit être comparé avec tous les autres dans cette plage aux bornes fixes.

• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Sélectionner alors le premier nombre du tableau, soit la cellule C5,
• Enfoncer trois fois de suite la touche F4 du clavier pour la libérer complètement,

De cette manière les dollars disparaissent. De fait, la cellule désignée va progresser en même temps que l'analyse chronologique de la règle de mise en forme conditionnelle. Comme tout le tableau est sélectionné, tous les nombres vont être étudiés tour à tour et comparés aux autres, dans une plage en revanche figée.

• Fermer la parenthèse de la fonction Nb.Si,
• Puis, ajouter le critère d'égalité suivant : =2,

Cette règle a donc pour mission de déceler la présence de tous les doublons. Lorsqu'ils sont identifiés, comme le mentionne la légende, ils doivent être repérés sur un fond orange très clair.

• Cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange clair fidèle à la légende,
• Valider ce format en cliquant sur le bouton Ok,

De fait, nous sommes de retour sur la première boîte de dialogue.



Le résumé est explicite. Tous les doublons doivent être dynamiquement mis en forme sur fond orange clair.

• Valider cette règle de mise en forme conditionnelle en cliquant sur le bouton Ok,

De retour sur la feuille, tous les nombres en double surgissent instantanément. Et si vous cliquez sur le bouton Générer de la barre d'outils sur la droite, les couleurs se déplacent automatiquement pour identifier les nouveaux doublons.

Deux règles restent à bâtir pour identifier les répétitions supérieures. Le même protocole doit être respecté. Tout d'abord, l'intégralité des données doit être sélectionnée, soit la plage de cellules C5:J12. Ensuite, il s'agit de bâtir les deux nouvelles règles suivantes :

=Nb.Si($C$5:$J$12;C5)=3 et =Nb.Si($C$5:$J$12;C5)>3.

A la première des deux, il convient d'associer un remplissage orange soutenu et une police rouge rubis. Avec la seconde, il faut choisir un remplissage rouge rubis et une police en gris très clair.



Dès lors et comme vous pouvez le voir, toutes les répétitions sont clairement et distinctement identifiées selon leur rang. Pour des redondances moins nombreuses, il paraît intéressant d'augmenter l'amplitude de la génération aléatoire de nombres, comme ceci par exemple : =ALEA.ENTRE.BORNES(1;40).

Calculer la donnée la plus répétée
Pour parfaire ces démonstrations, nous proposons de réaliser deux calculs de synthèse. Vous notez la présence d'une petite zone en bas à gauche du tableau des nombres aléatoires. En D15, nous devons extraire le nombre le plus souvent répété. Juste en dessous en D16, nous devons indiquer à quelle fréquence il apparaît. C'est la fonction Excel Mode qui permet de sortir la valeur la plus redondante d'une plage de cellules à lui passer en paramètre. Ensuite, sur cette valeur extraite, nous n'aurons plus qu'à exploiter la fonction Nb.Si pour compter ses répétitions.

• Sélectionner la cellule D15,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse, soit : Mode(,
• Sélectionner alors tous les nombres du tableau, soit la plage de cellules C5:J12,

Comme ce calcul n'est pas destiné à être répliqué, il n'est pas nécessaire de figer la plage.

• Fermer la parenthèse de la fonction Mode et valider le calcul par la touche Entrée du clavier,

En même temps que tous les nombres se régénèrent, la synthèse surgit. La valeur la plus répétée est automatiquement isolée. Et ce résultat corrobore parfaitement la mise en valeur dynamique.



Il ne nous reste plus qu'à comptabiliser le nombre de ces redondances. Pour cela, la cellule D16 doit porter la formule suivante : =NB.SI(C5:J12;D15).

Sur le tableau des nombres aléatoires, nous comptons ainsi le nombre de fois que la valeur la plus fréquente apparaît.