Chapitre 3 · Avancé · Leçon 19 / 26
Nommage & audit
Tu ouvres un fichier monté par un collègue parti depuis. Une cellule affiche =K12*$AB$3-Feuil2!G7. Que calcule-t-elle ? Impossible à dire sans enquêter. Deux compétences évitent ce cauchemar : nommer ses cellules pour que les formules se lisent, et savoir auditer un tableau pour en révéler la logique. C'est le sujet de cette leçon.
Une plage nommée donne un nom lisible à une cellule ou à une plage, utilisable dans toutes tes formules à la place de ses coordonnées. =Prix*Quantite remplace =B2*C2. L'audit de formules, lui, regroupe les outils qui montrent d'où vient un calcul, comment il se déroule et où il échoue. Les deux se complètent : des noms clairs rendent l'audit presque inutile, et l'audit sauve les fichiers qui n'en ont pas.
Le raccourci à mémoriser dès maintenant
Ctrl + ` (l'accent grave, au-dessus de Tab) affiche toutes les formules d'une feuille d'un coup. C'est le premier geste à faire devant un classeur inconnu : tu vois la logique avant de toucher quoi que ce soit.
Les plages nommées : des formules qui se lisent
Nommer une cellule prend cinq secondes et change tout : la formule devient une phrase compréhensible, et le nom ne se décale jamais par accident. Voici comment t'y prendre.
Nommer via la zone Nom
Le plus rapide : sélectionne la cellule, clique dans la zone Nom à gauche de la barre de formule (elle affiche normalement « A1 »), tape un nom sans espace comme TauxTVA, et valide. La cellule porte désormais ce nom dans tout le classeur.
Utiliser le nom dans une formule
À la place de =B2*C2, tu écris =Prix*Quantite. Excel reconnaît les noms et calcule. Une formule nommée se relit instantanément : tu n'as plus à retrouver ce que contient la cellule C2, le nom le dit.
Gérer ses noms
Formules > Gestionnaire de noms ouvre la liste de tous les noms du classeur : tu vois leur portée, leur plage, et tu peux les modifier, les supprimer ou en créer. C'est le tableau de bord de tes noms, à nettoyer de temps en temps pour éviter les noms morts.
Avant / après : la même formule, nommée
Sans noms
=B2*C2*(1+$F$1)Avec noms
=Prix*Quantite*(1+TauxTVA)La seconde se comprend sans contexte. Bonus : TauxTVA joue le rôle d'une référence absolue, tu n'as plus besoin des dollars. Un nom est toujours « absolu » par nature.
Règles de nommage
Un nom ne contient pas d'espace (utilise une majuscule ou un underscore : TauxTVA ou Taux_TVA), ne commence pas par un chiffre et ne ressemble pas à une référence de cellule (pas de nom « A1 » ou « TVA2 » ambigu). Garde-les courts et parlants.
Les outils d'audit de formules
Des noms clairs t'évitent l'enquête, mais tous les classeurs n'en ont pas. L'onglet Formules cache alors une boîte à outils d'audit que peu de gens utilisent, et qui transforme un tableau opaque en logique limpide. Voici les quatre à connaître.
D'abord, les antécédents et les dépendants tracent la chaîne de calcul. Sélectionne une cellule, puis Formules > Repérer les antécédents : des flèches partent des cellules qui alimentent la formule. Repérer les dépendants montre l'inverse, celles qui en dépendent. Tu remontes à la source d'une valeur, ou tu mesures l'impact d'une modification, en un coup d'œil.
Ensuite, Évaluer la formule déroule un calcul pas à pas. Excel calcule chaque morceau l'un après l'autre et te montre le résultat intermédiaire. Sur une formule imbriquée qui renvoie une erreur, tu repères l'étape exacte qui dérape, sans avoir à la réécrire en plusieurs cellules pour la disséquer.
La fenêtre Espion garde l'œil sur tes cellules clés. Elle affiche en permanence la valeur de cellules choisies, même quand tu changes de feuille. Pratique pour surveiller un total ou un indicateur pendant que tu modifies des données ailleurs dans le classeur, sans perdre le fil de l'effet produit.
Enfin, Afficher les formules bascule toute la feuille en mode lecture. Le raccourci Ctrl + ` remplace les résultats par les formules sur l'ensemble de la feuille. Idéal pour relire la logique d'un tableau hérité, repérer une valeur saisie en dur au milieu de formules, ou imprimer la structure d'un modèle.
La méthode pour comprendre un fichier inconnu
Ctrl + ` pour voir toutes les formules, puis Repérer les antécédents sur la cellule de résultat final pour remonter la chaîne, et enfin Évaluer la formule sur le calcul qui te pose question. En trois gestes, tu reconstruis la logique de n'importe quel classeur.
Vérifier et tracer les erreurs
Ces mêmes flèches servent aussi quand un calcul casse. Une erreur qui s'affiche dans une cellule vient presque toujours d'ailleurs, en amont dans la chaîne de calcul. L'audit te permet de remonter à la vraie cause au lieu de bricoler la cellule visible.
Vérifier les erreurs automatiquement
Formules > Vérification des erreurs balaye la feuille et liste les cellules en erreur (#REF!, #DIV/0!, #N/A…) une par une, avec une explication et une proposition de correction. Plus rapide que de chasser chaque triangle vert à la main.
Tracer une erreur jusqu'à sa source
Quand une cellule affiche une erreur, Repérer les erreurs (dans le menu Vérification des erreurs) trace des flèches rouges vers la cellule d'origine du problème. Tu remontes la chaîne jusqu'à la vraie cause, souvent une division par une cellule vide en amont.
Les triangles de couleur
Un petit triangle vert en haut à gauche d'une cellule signale une incohérence possible (nombre stocké en texte, formule différente des voisines). Ce n'est pas toujours une erreur, mais un drapeau à vérifier. Clique sur le losange jaune qui apparaît pour voir le diagnostic.
Questions fréquentes sur le nommage et l'audit
Sélectionne la cellule ou la plage, clique dans la zone Nom (à gauche de la barre de formule), tape le nom voulu et valide avec Entrée. Tu peux aussi passer par Formules > Définir un nom pour plus d'options. Une fois créé, le nom s'utilise dans n'importe quelle formule à la place des coordonnées de cellule.
Une plage nommée remplace une référence de cellule par un mot lisible. Au lieu de =C2*D2, tu écris =Prix*Quantite : la formule se lit comme une phrase et tu repères une erreur d'un coup d'œil. Le nom reste valable sur tout le classeur et ne se décale pas par accident lors d'un copier-coller.
Appuie sur Ctrl + ` (la touche de l'accent grave, au-dessus de Tab). Excel bascule l'affichage et montre les formules à la place de leurs résultats dans toute la feuille. Une seconde pression revient à l'affichage normal. C'est le moyen le plus rapide de relire la logique d'un tableau entier.
Évaluer la formule (onglet Formules) déroule un calcul étape par étape. Excel souligne la partie qu'il va calculer, tu cliques sur Évaluer, et il remplace ce morceau par son résultat, puis passe au suivant. Tu vois exactement où une formule longue produit un résultat inattendu, sans la découper à la main.
Sélectionne la cellule, puis Formules > Repérer les antécédents : des flèches bleues partent des cellules qui nourrissent la formule. Repérer les dépendants montre l'inverse, les cellules qui dépendent de celle-ci. Ces flèches révèlent en un instant la chaîne de calcul d'un tableau complexe.
La fenêtre Espion (Formules > Fenêtre Espion) garde sous les yeux la valeur de cellules clés, même quand tu navigues sur une autre feuille. Tu y ajoutes les cellules importantes (un total, un taux) et tu surveilles leur évolution pendant que tu modifies le reste du classeur, sans faire d'aller-retours.
Tu veux des classeurs Excel que tout le monde peut reprendre ?
Le Dojo Club te donne accès à des exercices guidés sur les plages nommées et l'audit, des lives experts chaque semaine et une communauté active pour bâtir des modèles clairs et maintenables.
Rejoindre le Dojo