- Un classeur fiable se prouve : chiffres rapprochés, formules cohérentes d’une ligne à l’autre, cellules de saisie identifiées, contrôles automatiques et notice d’utilisation.
- Les quatre anomalies les plus fréquentes sont la valeur saisie à la place d’une formule, le nombre stocké en texte, la constante erronée et la plage de somme incomplète ; sur notre cas, elles faussent le total HT de 13 400 et la TVA de 1 880.
- Excel fournit l’audit : Afficher les formules, Repérer les antécédents et dépendants, Vérification des erreurs, Évaluation de formule, Fenêtre Espion.
- La protection d’une feuille évite l’écrasement accidentel d’une formule ; elle ne constitue pas une sécurité forte, et un mot de passe perdu ne se récupère pas.
Un classeur qui « marche » n’est pas un classeur fiable. Une plage de somme qui s’arrête une ligne trop tôt, un nombre importé en texte, une TVA tapée à la main : aucune de ces erreurs ne déclenche de message, et le total reste plausible. Pour un comptable, un tableur faux est pire qu’un tableur absent. Cet épisode donne la méthode pour auditer un classeur, le protéger et le documenter.
Ce que vous saurez faire à la fin de l’épisode
- repérer les quatre anomalies les plus fréquentes d’un classeur ;
- utiliser les outils d’audit d’Excel ;
- lire et traiter les messages d’erreur ;
- protéger les cellules de calcul en laissant libres les cellules de saisie ;
- documenter le classeur et le doter de contrôles.
Le cas : un récapitulatif de six factures
Voici un récapitulatif de factures d’apparence normale. Il contient pourtant quatre anomalies.
Figure 1 : le classeur à auditer. Repères 1 à 4 : les quatre anomalies.
| N° | Anomalie | Cellule | Effet |
|---|---|---|---|
| 1 | TVA saisie en dur (4 500 au lieu de 4 800) | D4 | TVA sous-évaluée de 300 |
| 2 | Montant HT stocké en texte | B5 | 4 200 ignorés par la somme |
| 3 | Taux de 10 % au lieu de 20 % | C6 | TVA sous-évaluée de 1 580 |
| 4 | Total HT = SOMME(B2:B6) |
B8 | 9 200 oubliés |
Résultat affiché : total HT 65 300, TVA 13 860. Résultat correct : total HT 78 700, TVA 15 740. L’écart sur le total HT est de 13 400 (4 200 + 9 200) et celui sur la TVA de 1 880 (300 + 1 580). Un total HT qui s’écarte de 17 % de la réalité, sans le moindre message d’erreur.
Les outils d’audit d’Excel
| Besoin | Outil (onglet Formules sauf mention) |
|---|---|
| Voir toutes les formules d’un coup, repérer les valeurs en dur | Afficher les formules |
| Voir d’où vient une valeur, jusqu’où elle va | Repérer les antécédents / Repérer les dépendants |
| Chercher les erreurs courantes (nombre en texte, formule incohérente) | Vérification des erreurs |
| Comprendre une formule complexe pas à pas | Évaluer la formule |
| Surveiller des cellules clés depuis une autre feuille | Fenêtre Espion |
| Retrouver les nombres saisis dans une zone de formules | Ctrl+G > Cellules > Constantes |
Appliquer la méthode au cas
- Afficher les formules. La colonne D montre
=ARRONDI(B2*C2;2)partout… sauf enD4, qui contient un simple4500. Anomalie 1 trouvée en un coup d’œil. - Constantes. Ctrl+G > Cellules > Constantes sur
D2:D7sélectionne la seule celluleD4: même diagnostic, automatisable. - Vérification des erreurs. Excel signale
B5par un triangle vert : « nombre au format texte ». Anomalie 2. Le compteNB(B2:B7)renvoie 5 alors qu’il y a six factures : second indice. - Lire la colonne des taux. Cinq valeurs à 20 % et une à 10 % : un taux isolé est suspect, comme une cellule isolée dans une colonne homogène. Un rapprochement avec la facture d’origine ou une règle de validation de données (épisode 11) aurait bloqué la saisie. Anomalie 3.
- Repérer les antécédents de
B8. La flèche montre la plageB2:B6: elle s’arrête une ligne trop tôt. Anomalie 4. Plus généralement, une plage de total qui ne couvre pas tout le tableau est l’erreur la plus fréquente après l’ajout de lignes ; un tableau Excel (épisode 8) avec ligne de total évite ce risque.
Le journal d’audit récapitule les anomalies, leur détection et leur impact :
Figure 2 : le journal d’audit. Repère 1 : l’impact de chaque anomalie ; repère 2 : le contrôle de cohérence des impacts.
Le contrôle final (=SI(ARRONDI(E2+E4-E7;2)=0;"OK";"ÉCART")) vérifie que la somme des impacts des anomalies 1 et 3 (300 + 1 580) explique bien l’écart de TVA (1 880). Quand on corrige un classeur, on chiffre l’effet de chaque correction : c’est ce qui permet d’expliquer à la direction pourquoi un chiffre a changé.
Les erreurs d’Excel et leur sens
| Message | Cause habituelle | Remède |
|---|---|---|
#DIV/0! |
Division par zéro ou par une cellule vide | Tester le dénominateur avec SI |
#N/A |
Valeur introuvable par une recherche | Vérifier la clé (espaces, texte/nombre), utiliser SI.NON.DISP si c’est normal |
#REF! |
Référence supprimée (ligne ou colonne effacée) | Restaurer ou corriger la formule |
#VALEUR! |
Opération sur un texte, mauvais type d’argument | Convertir le texte, vérifier les types |
#NOM? |
Fonction ou nom inconnu (faute de frappe, version d’Excel) | Corriger l’orthographe, vérifier la version |
#NOMBRE! |
Nombre invalide pour la fonction (racine négative…) | Vérifier les valeurs |
##### |
Colonne trop étroite pour afficher le nombre | Élargir la colonne |
Un point essentiel (épisode 14) : ne masquez pas une erreur avec SIERREUR sans comprendre sa cause. Un #N/A qui devient « 0 » transforme une anomalie visible en erreur silencieuse.
Protéger sans bloquer
Deux types de cellules coexistent : celles où l’utilisateur saisit (cellules jaunes de nos classeurs) et celles qui calculent. La protection consiste à verrouiller les secondes.
- Sélectionnez les cellules de saisie, puis Format de cellule > Protection et décochez Verrouillée (par défaut, toutes les cellules sont verrouillées).
- Révision > Protéger la feuille : choisissez ce que l’utilisateur peut encore faire (sélectionner, mettre en forme, trier…), avec ou sans mot de passe.
- Pour empêcher l’ajout ou la suppression d’onglets : Révision > Protéger le classeur (structure).
Précautions :
- La protection évite l’écrasement accidentel d’une formule ; elle n’est pas une sécurité forte. Pour des données confidentielles, chiffrez le fichier ou restreignez l’accès.
- Conservez le mot de passe dans un endroit sûr : Excel ne le récupère pas.
- Gardez une copie non protégée dans un emplacement contrôlé : le classeur doit rester maintenable.
Documenter et contrôler
Un classeur fiable se transmet. Quatre habitudes :
- Un onglet « Lisez-moi » : objectif, source des données, cellules de saisie, périmètre, date et auteur de la dernière modification.
- Un code couleur constant : jaune pour la saisie, vert pour les contrôles, bleu pour les résultats clés (celui de toute cette série).
- Des contrôles intégrés : totaux rapprochés de la comptabilité, équilibre débit-crédit, nombre d’erreurs dans une zone (
=SOMMEPROD(--ESTERREUR(zone))doit valoir 0), avec une cellule de synthèse visible en tête. - Un journal des modifications : date, nature, impact chiffré (comme notre journal d’audit).
Ajoutez enfin une règle de relecture : un classeur important est relu par une seconde personne qui refait quelques calculs de tête ou sur un autre outil. C’est le même principe de séparation des tâches que dans le reste du cabinet.
À vous de jouer
Dans le classeur d’exercice :
- Repérez dans l’onglet À auditer les quatre anomalies sans regarder le journal.
- Corrigez-les et comparez avec l’onglet Corrigé : total HT et TVA.
- Quel est le total TTC avant et après correction ?
- Protégez la feuille Corrigé en laissant déverrouillées les colonnes B et C : que se passe-t-il si vous tapez dans D2 ?
Correction. (2) Total HT 78 700 (au lieu de 65 300) ; TVA 15 740 (au lieu de 13 860). (3) TTC avant : 92 560 ; après : 94 440 (78 700 + 15 740). L’écart de 1 880 est exactement celui de la TVA : le total TTC se somme ligne par ligne, et n’est donc affecté ni par le texte de B5 ni par la plage de B8, qui ne concernent que le total HT. (4) Excel refuse la saisie dans D2 avec un message indiquant que la cellule est protégée ; les colonnes B et C restent modifiables, et la TVA se recalcule.
À retenir
- Les erreurs les plus dangereuses sont silencieuses : valeur en dur, nombre en texte, plage incomplète, mauvaise constante.
- Auditez avec Afficher les formules, Repérer les antécédents, la vérification des erreurs et Ctrl+G > Constantes.
- Ne masquez pas une erreur sans en comprendre la cause.
- Verrouillez les formules, laissez libre la saisie ; la protection n’est pas une sécurité forte et le mot de passe est irrécupérable.
- Documentez (Lisez-moi), contrôlez (cellule de synthèse) et faites relire.
Épisode 50 : le projet final, un reporting financier mensuel automatisé, de A à Z.
Questions fréquentes
Comment retrouver les nombres tapés à la main au milieu des formules ?
Sélectionnez la zone, puis Ctrl+G (Atteindre) > Cellules > Constantes, en ne cochant que « Nombres » : Excel sélectionne uniquement les cellules qui contiennent une valeur saisie. Dans une colonne de formules, toute cellule sélectionnée est suspecte.
Pourquoi un nombre stocké en texte n’est-il pas additionné ?
Parce que SOMME ignore le texte, même s’il ressemble à un nombre. Un triangle vert, une valeur alignée à gauche ou un compte NB inférieur au nombre de lignes le trahissent. On convertit avec l’avertissement d’Excel ou avec CNUM, ou en réimportant les données avec le bon type.
La protection d’une feuille empêche-t-elle toute modification ?
Elle empêche les modifications accidentelles des cellules verrouillées tant qu’elle est active, mais elle n’est pas un dispositif de sécurité fort : des outils permettent de la lever. Pour des données confidentielles, il faut chiffrer le fichier ou en restreindre l’accès, pas se contenter de protéger la feuille.
Que faire si j’ai perdu le mot de passe de protection ?
Excel ne le récupère pas. D’où les bonnes pratiques : conserver le mot de passe dans un gestionnaire sécurisé, garder une copie non protégée dans un emplacement contrôlé et n’utiliser un mot de passe que si c’est utile.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.