Recopier des formules sans savoir ce qu'elles calculent : la liste à dresser avant votre formation Excel

Étendre une formule n'est pas la comprendre

Quand on reprend un classeur construit par quelqu'un d'autre, le geste quotidien consiste souvent à tirer la poignée de recopie vers le bas, à vérifier que les colonnes se remplissent, puis à passer à la suite. Tant que les données ressemblent à celles du mois précédent, la mécanique tient.

Elle cède dès qu'un cas sort du cadre : une ligne située hors de la plage figée, un libellé écrit autrement, une erreur affichée sur quelques lignes seulement. Impossible alors de trancher entre un résultat faux et une donnée inhabituelle, puisque la règle de calcul n'a jamais été formulée par écrit. L'inventaire décrit ci-dessous sert à rassembler ce matériel avant les séances.

Quatre informations à relever pour chaque formule

L'objectif n'est pas de décoder les formules maintenant, mais de les décrire assez précisément pour qu'elles soient exploitables. Pour chaque formule retenue, notez quatre choses : l'adresse de la cellule et le nom de la feuille, le texte exact de la formule, le résultat que cette colonne est censée produire, et le symptôme observé quand quelque chose cloche. Dans l'exercice, travaillez sur le classeur fictif fourni et ne reportez sur votre liste aucune information confidentielle ni donnée personnelle.

Trois commandes du groupe Audit de formules, onglet Formules, suffisent. Afficher les formules remplace les résultats par les formules dans toute la feuille ; le raccourci Ctrl et accent grave produit le même effet et rétablit ensuite l'affichage normal. Évaluer la formule ouvre une boîte de dialogue qui déroule les calculs intermédiaires d'une formule imbriquée étape par étape. Repérer les antécédents trace des flèches vers les cellules qui alimentent la formule sélectionnée ; ces flèches disparaissent si la formule est modifiée ou si des lignes sont insérées, il faut alors relancer la commande.

Ce qu'il faut interroger selon la formule rencontrée

Les formules héritées se répartissent en quelques familles. Le tableau associe à chacune la question à poser et l'information à consigner. Les formules citées sont inventées pour illustrer la lecture.

Une même ligne de liste peut combiner plusieurs cas : notez-les séparément plutôt que de résumer.

Mise en situation fictive : le classeur d'interventions repris en cours d'année

Cas entièrement imaginé pour l'exercice, avec des données fictives. Un classeur de suivi d'interventions techniques comporte deux feuilles, Saisie et Synthèse. Trois colonnes y sont recopiées chaque mois sans que leur règle soit connue. Sur une copie du fichier, Ctrl et accent grave affiche les formules de la feuille Synthèse : la colonne D contient =SI(NB.SI(Saisie!$C$2:$C$400;B2)>0;"traité";"à planifier"). La plage s'arrête à la ligne 400 alors que la feuille Saisie en compte davantage, d'où une première ligne de liste avec le symptôme « les derniers chantiers restent à planifier alors qu'ils sont faits ».

En colonne F, =RECHERCHEV(B2;Tarifs!A:F;5) renvoie un montant plausible mais parfois surprenant ; Évaluer la formule montre la valeur retenue à chaque étape, ce qui suffit à écrire « le montant ne correspond pas toujours au code chantier ». En colonne H, Repérer les antécédents fait apparaître une flèche venant d'un autre classeur : on note le chemin affiché et la mention « dépend d'un classeur externe ». Bilan : trois formules décrites, aucun calcul modifié, un ordre du jour clair.

Les erreurs fréquentes pendant cet inventaire

La plus courante consiste à recopier la formule de mémoire ou en la reformulant. Un point-virgule, un signe dollar ou une paire de guillemets déplacés changent le comportement : copiez le texte tel quel depuis la barre de formule. Vient ensuite la généralisation : observer une colonne sur sa première ligne ne dit rien des lignes suivantes, où les plages figées peuvent produire un autre résultat. Vérifiez la première ligne, une ligne intermédiaire et la dernière.

Deux croyances techniques faussent aussi l'inventaire. NB.SI n'accepte qu'un seul critère et ne peut pas compter les cellules d'après leur couleur d'arrière-plan ou de police : si un code couleur porte la règle, celle-ci n'est pas calculée. Et lorsque le dernier argument de RECHERCHEV est omis, il vaut VRAI, soit une correspondance approximative qui suppose la première colonne triée ; une formule sans cet argument est donc à signaler, même quand le résultat semble juste.

• Corriger les formules en cours d'inventaire, au lieu de s'en tenir au relevé
• Ne noter que la formule, sans le résultat attendu, qui est pourtant la seule information que vous détenez
• Oublier le nom de la feuille, ce qui rend la liste inutilisable une semaine plus tard

Ce que votre liste ouvre dans le programme

Cette liste constitue un point de départ concret pour la formation Excel du catalogue, dont l'objet est la maîtrise du tableur. Les lignes signalant une plage trop courte, une recherche approximative ou une dépendance vers un autre classeur donnent les cas à reprendre en séance. Consultez le programme publié de la formation pour situer ces points dans le déroulé.