Techniques de recherche d’erreurs dans Excel : audit, références circulaires et espions
Une formule fausse ne prévient pas toujours : un #N/A peut se propager jusqu’au total, une référence circulaire peut bloquer un calcul, une cellule modifiée sur une feuille peut changer un résultat sur une autre sans que l’on s’en aperçoive. L’onglet Formules › Audit de formules regroupe les outils pour comprendre d’où vient une valeur, où elle est utilisée et pourquoi elle est en erreur.
Reconnaître les valeurs d’erreur
| Erreur | Cause la plus fréquente | Piste de correction |
|---|---|---|
#DIV/0! | Division par zéro ou par une cellule vide | Compléter la cellule, ou =SI(B2=0;"";A2/B2) |
#N/A | Valeur introuvable (RECHERCHEV, EQUIV, RECHERCHEX) | Vérifier l’orthographe, les espaces ; SI.NON.DISP |
#NOM? | Fonction mal orthographiée, nom défini inexistant, texte sans guillemets | Corriger le nom ; Formules › Gestionnaire de noms |
#REF! | Cellule référencée supprimée (ligne, colonne, feuille) | Réécrire la référence ; annuler la suppression |
#VALEUR! | Calcul sur du texte (nombre importé ou saisi en texte, précédé d’une apostrophe) | Convertir en nombre, CNUM, Données › Convertir |
#NOMBRE! | Résultat impossible (racine d’un négatif, TRI qui ne converge pas) | Contrôler les arguments |
#NUL! | Espace entre deux plages au lieu de « ; » ou « : » | =SOMME(A1:A5 B1:B5) → =SOMME(A1:A5;B1:B5) |
#PROPAGATION! | Formule matricielle bloquée par une cellule non vide | Vider les cellules sur le chemin |
###### | Pas une erreur : colonne trop étroite ou date négative | Élargir la colonne |
=SIERREUR(formule;"") cache toutes les erreurs, y compris celles qui signalent un vrai problème. Réservez-la aux cas prévus et préférez SI.NON.DISP pour les recherches.L’audit de formules
Dans Formules › Audit de formules, Repérer les antécédents dessine des flèches bleues depuis les cellules utilisées par la formule sélectionnée ; Repérer les dépendants montre les formules qui utilisent la cellule. Chaque clic ajoute un niveau. Sur une cellule en erreur, Vérification des erreurs › Repérer une erreur trace en rouge le chemin jusqu’à l’origine du problème.
Les références circulaires
Une formule qui fait référence, directement ou indirectement, à sa propre cellule crée une référence circulaire. Excel affiche un avertissement, renvoie 0 et indique la cellule dans la barre d’état. On la retrouve par Formules › Vérification des erreurs › Références circulaires.
=B2*B3/(1+B3) : plus de boucle.La fenêtre Espion
La fenêtre Espion (Formules › Audit de formules › Fenêtre Espion) affiche en permanence la valeur de cellules choisies, même si elles se trouvent sur une autre feuille ou dans un autre classeur ouvert. On modifie les hypothèses et on voit immédiatement l’effet sur les résultats.
Exercice de synthèse : corriger un devis truffé d’erreurs
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Devis : cinq erreurs (#N/A, #DIV/0!, #REF!, #VALEUR!, #NOM?). Pour chacune, utilisez Repérer une erreur et Évaluer la formule pour trouver la cellule d’origine.
- Corrigez-les : le total HT doit valoir 4 650 €, le net HT 4 417,50 € et le TTC 5 301 €.
- Feuille Primes : repérez la référence circulaire, testez le calcul itératif (prime 4 545,45 €), puis remplacez-la par la formule directe.
- Placez un espion sur Synthèse!B2:B4, modifiez les jours de formation dans la feuille Devis (corrigé) et observez la fenêtre Espion.