Excel 2010 : trouvez la faille dans vos formules de calcul [astuce]

Que faire si vos formules ne donnent pas le résultat escompté ou vous renvoient des messages d'erreur ? Pas de panique : Excel fourmille d'outils permettant de décortiquer une formule et d'en localiser le défaut.

Etienne Oehmichen

Micro Hebdo

le 25/06/12 à 17h31

laisser un avis

agrandir la photo

Votre formule n'affiche pas un nombre, mais un message d'erreur ? Ce dernier peut vous aider à identifier l'anomalie. Passons sur le plus simple : #DIV/0 (division par zéro) : vous vous êtes sans doute trompé en divisant un nombre par une cellule vide.

Un peu moins évident : le message #REF signifie que l'un des éléments de votre formule a disparu. Peut-être avez-vous supprimé une ligne ou une colonne contenant un des termes de la formule.

Si vous lisez #NOMBRE, c'est que vous avez tenté d'utiliser une fonction mathématique en dehors de ses limites, par exemple la racine carrée ou le logarithme d'un nombre négatif.

Décortiquez vos formules

agrandir la photo

Parfois, la formule n'affiche pas un message d'erreur, mais donne un résultat aberrant. Il faut donc l'analyser pour en trouver la cause.

Commencez par double-cliquer sur la formule : les cellules qu'elle utilise (B5 et D5 dans l'exemple ci-contre) sont entourées, dans la formule et dans la feuille par un filet coloré. Si vous constatez qu'une des cellules est mal placée (vous avez choisi par erreur la cellule voisine), déplacez-la à la souris et validez.

Repérez les antécédents

agrandir la photo

Excel vous offre un moyen très efficace de repérer d'un seul coup toutes les cellules concernées par une formule. Pour cela, sélectionnez cette dernière et activez le menu Formules.

Dans la zone Audit de formules, cliquez sur Repérer les antécédents. Excel trace des flèches bleues de la formule vers toutes les cellules qu'elle utilise.

Dans le même ordre d'idées, le bouton Repérer les dépendants affiche toutes les formules de calcul qui exploitent une cellule donnée. Cliquez sur Effacer les flèches pour supprimer ces repères.

Faites un audit en profondeur

agrandir la photo

Si tous les outils précédents ne vous ont pas permis de repérer l'erreur dans votre formule, ou si cette dernière est très longue, nous vous proposons un autre outil.

Sélectionnez la formule et, dans le menu Formules, cliquez sur Evaluation de formule. La fenêtre qui s'affiche présente le détail de la formule sous forme de blocs, chaque bloc correspondant à un des arguments de l'expression calculée.

Pointez un bloc et cliquez sur Pas-à-pas détaillé : Excel affiche le détail des cellules concernées par ce bloc, leur contenu, et le résultat de l'opération.

Si certaines de ces cellules sont elles-mêmes des formules, vous pourrez, toujours avec le bouton Pas-à-pas détaillé, « descendre » dans les niveaux les plus profonds de votre formule, comme dans l'arborescence d'un disque dur. Le tableur affiche toujours, dans la fenêtre, la valeur numérique du bloc en cours d'examen. L'outil n'est pas facile à maitriser, mais très puissant.