Décrypter aisément des formules de calcul

Les outils d'audit

Vous n'avez appliqué aucune des méthodes décrites précédemment et vos formules demeurent donc indéchiffrable.

Plus pour longtemps car Excel a prévu de tels cas de figure.
Pour décrypter vos formules et découvrir visuellement les cellules entrant dans votre formule, utilisez les outils d'audit.

Ces outils sont rassemblés sur une barre d'outils.
Pour afficher cette dernière, vous disposez de deux solutions :

1. utiliser le menu Outils, Audit de formules..., Afficher la barre d'outils Audit de formules ;
2. faire un clic droit de la souris sur une icône quelconque puis sélectionner Audit de formules.

La première icône scanne votre feuille de calcul et vous permet, si votre formule renvoie un message d'erreur, d'évaluer la formule, étape par étape, pour déceler l'erreur. Cette option est particulièrement pratique si vos calculs s'enchaînent : vous localisez ainsi l'emplacement de votre erreur.
Cet outil vous permet de détecter également des anomalies. Dans l'exemple ci-contre, l'outil de Vérification des erreurs a détecté que la formule saisie dans la colonne J, n'était pas identique partout... (le fond coloré a été ajouté par mes soins : l'outil se contente d'afficher le message ci-dessous). Cela est néanmoins suffisamment efficace pour vous mettre la puce à l'oreille... Il faut, préalable, sélectionner votre tableau pour que cette option fonctionne.

Le groupe d'icônes suivantes vous permet de localiser les cellules qui entrent dans la composition de votre formule (icône 1 en partant de la gauche) : la valeur contenue dans la cellule analysée est donc le résultat de ces autres cellules. Dans l'exemple ci-contre, le curseur est placé sur la cellule contenant 1 435,20 €. Lorsque l'on clique sur la première icône Repérer les antécédents, Excel affiche deux flèches nous indiquant que ce montant résulte du montant ht (appelé Base dans l'exemple) de 1 200 € et du montant de la TVA (235,20 €). L'icône suivante permet de désactiver ces flèches.

Pour déterminer si votre cellule est utilisée ou non à son tour dans une formule de calcul, il vous faut afficher les cellules dépendantes. C'est l'objectif de la 3e icône. On voit ci-contre que notre Total TTC sert à calculer le Net à payer.
L'icône suivante permet de désactiver les flèches des cellules dépendantes.
La gomme quant à elle permet de désactiver l'ensemble des flèches des cellules dépendantes ou antécédentes.L'icône est un peu identique à mais au contraire de l'icône Vérification des erreurs, pour fonctionner repérer une erreur doit être positionné sur une cellule contenant déjà un message d'erreur. Ensuite, vous pouvez tester pas à pas votre formule et détecter l'origine du problème.
L'icône suivante vous est sans doute familière. vous permet d'insérer des commentaires dans une cellule. Nous y reviendrons à l'occasion d'un autre tutoriel.
Le groupe d'icônes suivant permet d'entourer les données non valides. Elles s'utilisent conjointement avec la validation des données. La première icône entoure les cellules dans lesquelles l'utilisateur a saisi une valeur ne figurant pas dans la liste. La seconde efface les cercles.

Vous trouverez sans doute plus d'intérêt à l'icône suivante : . Cette icône permet d'ajouter un Espion sur une cellule. Imaginez que vous travailliez sur un très grand tableau et que, lorsque vous modifiez une valeur tout en Hautde votre feuille, vous vouliez immédiatement visualiser l'impact sur d'autres cellules situées en milieu et en bas de tableau : il vous faut alors faire défiler votre feuille de calcul. Idem si plusieurs de vos feuilles de calcul soient liées et que vous souhaitiez garder un oeil sur l'impact d'une modification dans une autre feuille : il vous faut alors passer d'un onglet à l'autre.

Cette icône vous évite ce genre de désagrément. Vous cliquez simplement sur l'icône puis sur la (ou les) cellule(s) à surveiller et, pour terminer, sur le bouton Ajouter un espion puis Ajouter.
En cas de changement, le Volet Espion vous permet de contrôler l'impact de vos changements sur les cellules surveillées ! La dernière icône est sans conteste ma préférée. Vous permet de contrôler certaines formules complexes : les calculs se déroulent au pas à pas afin de vous permettre d'en mieux comprendre le fonctionnement.Imaginons la formule suivante : =SI(MOYENNE($B$8+$B$10)>=14;"Doué";"Pas doué").
Seriez-vous en mesure, en la lisant, de donner le résultat final de la formule ? Sans doute que non.
L'icône Évaluation de la formule va vous aider.
Il suffit pour cela de placer votre curseur sur la cellule contenant la formule à analyser et de cliquer sur . Une boîte de dialogue s'ouvre. Au fur et à mesure que vous cliquez sur le bouton Évaluer, Excel, simplifie et résout pour vous l'équation :

Magique, non ?

Haut

Niveau

Pré-requis :

• Aucun