Repérer et compter les erreurs de calculs 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 :


Comptabiliser les erreurs de calculs

Au gré des modifications et interventions dans une base de données Excel, il n'est pas rare de réceptionner une source d'information présentant de nombreuses anomalies.



Dans l'exemple illustré par la capture, nous livrons le bilan sur le nombre d'erreurs repérées et totalisées dans le tableau. Dans le même temps, ces anomalies sont automatiquement mises en valeur avec une couleur de police explicitement différente. Ces techniques s'appliquent aussi simplement sur des bases de données de plusieurs centaines voire de plusieurs milliers de lignes. Elles sont un excellent moyen de dresser un état des lieux rendant compte de l'ampleur des corrections à entreprendre.

Tableau de données
Pour la démonstration de cette nouvelle astuce Excel, nous proposons de travailler à partir de ce petit tableau exemple.

• Télécharger le fichier compter-erreurs.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

C'est un calcul trivial qui est construit en colonne D de ce petit tableau. Il réalise la division de l'information prélevée en colonne B par l'information placée en colonne C. Mais parfois des erreurs de traitement se sont glissées. Il n'est pas possible de diviser un nombre par un texte ou un texte par un nombre. Dans ce contexte, l'opération retourne le message d'erreur #Valeur!. De la même façon, il n'est pas possible de diviser un nombre par zéro. Le message d'erreur retourné est alors le suivant : #Div/0!.

Compter les erreurs
Pour dénombrer les erreurs, nous allons exploiter une technique que nous maîtrisons bien désormais. Elle consiste à passer une matrice conditionnelle à la fonction SommeProd. Grâce à la fonction logique EstErreur appliquée à l'ensemble de la colonne des résultats, la matrice doit être en mesure de repérer les positions des anomalies. Une fois ces indicateurs de position transformés en chiffres, la fonction SommeProd pourra les additionner naturellement. Il en résultera le décompte attendu.

• Sélectionner la cellule du résultat à trouver, soit F4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

Nous affichons ainsi l'assistant Excel pour la fonction SommeProd. Il va nous aider à mieux appréhender les étapes du calcul.

• Dans la zone Matrice1, ouvrir une parenthèse pour accueillir la matrice conditionnelle,
• Inscrire la fonction de test logique des anomalies suivie d'une parenthèse, soit : EstErreur(,
• Désigner l'intégralité des calculs, soit la plage de cellules D4:D10,
• Fermer la parenthèse de la fonction EstErreur,
• Puis, fermer la parenthèse de la matrice conditionnelle,

Comme vous pouvez le voir, les repérages surgissent aussitôt sous forme de matrice, sur la droite de la zone Matrice1.



Chaque booléen Vrai identifie la position d'une erreur de calcul dans la plage mentionnée. La fonction SommeProd ne peut additionner que des valeurs numériques. Nous devons donc convertir ces indicateurs booléens par une simple multiplication.

• Taper le symbole de l'étoile suivi du chiffre 1, soit : *1, pour forcer la conversion,

Cette fois, c'est une matrice de chiffres qui est effectivement retournée.



Désormais, la fonction SommeProd est en mesure de livrer le décompte de ces erreurs. Et c'est ce que confirme l'indication numérique en bas de la boîte de dialogue. Quatre chiffres 1 repèrent en effet les quatre erreurs de calcul présentes dans ce petit tableau.

• Valider la formule matricielle en cliquant sur le bouton Ok de la boîte de dialogue,

Le résultat est confirmé. Bien sûr, si vous générez une nouvelle erreur de calcul en inscrivant un texte ou en remplaçant un diviseur par le chiffre 0, celle-ci s'ajoute instantanément au décompte.

Surligner automatiquement les erreurs
Pour un état des lieux plus percutant, nous suggérons de faire ressortir en couleur toutes ces anomalies. Cette synthèse visuelle viendra parfaitement recouper le résultat livré par la formule matricielle. Et pour cela, une simple règle de mise en forme conditionnelle exploitant la fonction logique EstErreur suffit.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:D10,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, 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 du dessous pour l'activer,
• Dès lors, inscrire le symbole égal (=) pour initier la syntaxe de la mise en forme conditionnelle,
• Inscrire la fonction de test logique suivie d'une parenthèse, soit : EstErreur(,
• Désigner le premier résultat de calcul en cliquant sur sa cellule D4, soit : $D$4,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, ce qui donne : $D4,

Nous le savons, l'analyse d'une mise en forme conditionnelle n'est pas matricielle. Elle est chronologique. C'est la raison pour laquelle nous faisons débuter l'étude à partir du premier résultat. Pour qu'ils soient tous passés en revue, nous sommes dans l'obligation de libérer la cellule en ligne. Mais comme ces résultats sont forcément placés en colonne D, nous empêchons la colonne de bouger.

• Fermer la parenthèse de la fonction EstErreur,
• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un orange assez vif pour le texte,
• Valider cet attribut avec le bouton Ok de la boîte de dialogue,
• De retour sur la première boîte, valider la création de la règle avec le bouton Ok,

Non seulement, le renforcement visuel dynamique vient parfaitement recouper le résultat de la formule matricielle, mais il permet de repérer avec beaucoup d'efficacité l'emplacement de toutes ces erreurs.