Auditer et sécuriser une feuille de calcul : outils de contrôle, jeu d'essai, formules de contrôle et protection
À retenir
Cadre programme : DCG, programme 2025 (arrêté du 4 août 2025, première session 2027), UE 8 « Systèmes d'information de gestion », partie 2 « Gérer des données du système d'information », sous-partie 2.2.2 « Auditer une feuille de calcul » (exploiter les outils de contrôle des formules ; concevoir un jeu d'essai de données ; sécuriser le classeur et la feuille de calcul ; concevoir des formules de contrôle de cohérence ; contrôle de la confidentialité et de l'intégrité des données ; protection de la feuille de calcul).
Pourquoi c'est central à l'examen : une feuille qui calcule n'est pas une feuille qui calcule juste. L'énoncé fournit fréquemment une feuille contenant une ou plusieurs erreurs (plage incomplète, valeur saisie à la place d'une formule, taux écrit en dur) et demande de les repérer, de proposer un jeu d'essai, d'ajouter une formule de contrôle ou de protéger les cellules sensibles.
01Pourquoi auditer
Les erreurs d'une feuille de calcul sont fréquentes et silencieuses : le tableur affiche un résultat, rarement un message. Un audit vise à vérifier que :
- les formules sont correctes (elles calculent ce que l'on veut) ;
- elles sont cohérentes (la même logique sur toute une colonne) ;
- les résultats sont vraisemblables (jeu d'essai) ;
- les données et les formules sont protégées contre une modification accidentelle ou non autorisée.
1.1 Les erreurs les plus courantes
| Erreur | Exemple | Effet |
|---|---|---|
| Plage incomplète | =SOMME(C5:C8) alors que les données vont jusqu'à C9 | Total trop faible |
| Valeur saisie à la place d'une formule | 450 tapé dans une cellule de calcul | La cellule ne suit plus les données |
| Valeur en dur | =B7*0,04 alors que le taux est un paramètre | Taux incohérent entre lignes ; modèle non maintenable |
| Référence mal verrouillée | B3 au lieu de $B$3 (cours 25) | Résultats faux après recopie |
| Texte pris pour un nombre | 1 250 importé comme du texte | Cellule ignorée dans les sommes |
| Mélange d'unités | Euros et milliers d'euros dans la même colonne | Écarts de facteur 1 000 |
| Référence circulaire | Une formule qui dépend de son propre résultat | Calcul impossible ; le tableur avertit |
| Arrondi | Total de montants arrondis différent de l'arrondi du total | Écarts de quelques centimes |
02Les outils d'audit du tableur
Les noms exacts des commandes varient d'un tableur à l'autre ; les fonctions sont communes.
| Outil | Fonction | Usage |
|---|---|---|
| Afficher les formules | Montre dans les cellules les formules à la place des résultats | Repérer d'un coup d'œil une formule différente de ses voisines, ou une valeur saisie |
| Repérer les antécédents | Trace des flèches vers les cellules dont dépend la cellule sélectionnée | Comprendre d'où vient un résultat |
| Repérer les dépendants | Trace des flèches vers les cellules qui utilisent la cellule sélectionnée | Mesurer l'effet d'une modification |
| Évaluer la formule | Exécute la formule pas à pas, en montrant chaque calcul intermédiaire | Comprendre une formule complexe, localiser l'étape fautive |
| Vérification des erreurs | Signale les cellules en erreur et celles dont la formule diffère de ses voisines | Parcours systématique |
| Fenêtre de surveillance | Garde sous les yeux quelques cellules sensibles pendant qu'on modifie la feuille | Suivre un indicateur clé |
Une démarche d'audit simple :
- Cartographier : lire la structure (paramètres, données, calculs, résultats, cours 25).
- Afficher les formules et repérer les ruptures de régularité dans chaque colonne.
- Suivre les antécédents des résultats clés.
- Rechercher les valeurs en dur dans les formules.
- Tester par un jeu d'essai.
- Contrôler par des formules de cohérence.
- Protéger ce qui doit l'être.
03Le jeu d'essai
3.1 Définition
Un jeu d'essai est un ensemble de données choisies à la main, dont on connaît à l'avance le résultat correct. On saisit ces données dans la feuille et on compare ce qu'elle calcule au résultat attendu. Le programme se limite à des jeux d'essai simples, conçus à la main, sans méthode formelle de test ni automatisation des tests.
3.2 Comment le concevoir
- Calculer le résultat attendu indépendamment de la feuille (à la main, ou sur une calculatrice), avant de regarder ce que la feuille donne.
- Choisir trois familles de cas :
| Famille | Exemples |
|---|---|
| Cas normaux | Un montant courant, une quantité courante |
| Cas limites | Valeur exactement égale à un seuil de barème, zéro, plus petite et plus grande valeur admises, valeur qui provoque un arrondi |
| Cas d'erreur | Texte à la place d'un nombre, valeur négative (avoir), donnée manquante, division par zéro |
- Présenter le jeu dans un tableau : cas, entrées, résultat attendu, résultat obtenu, verdict.
- En cas d'écart, chercher l'origine (antécédents, évaluation pas à pas), corriger, puis rejouer tout le jeu : une correction peut créer une autre erreur.
04Les formules de contrôle de cohérence
Une formule de contrôle vérifie qu'une propriété qui doit toujours être vraie l'est bien. Elle affiche OK ou un message d'alerte. Elle ne corrige rien : elle signale.
| Contrôle | Principe | Exemple |
|---|---|---|
| Total croisé | La somme des totaux de lignes égale la somme des totaux de colonnes | =SI(SOMME(E5:E7)=SOMME(B8:D8);"OK";"ERREUR") |
| Recalcul global | Un total détaillé égale un calcul direct sur l'ensemble | =SI(ABS(C10-ARRONDI(B10*Taux_comm;2))<0,005;"OK";"ERREUR") |
| Équilibre | Total des débits égal total des crédits | =SI(SOMME(D5:D7)=SOMME(E5:E7);"OK";"Déséquilibre") |
| Comptage | Autant de nombres que de lignes remplies | =SI(NB(B5:B9)=NBVAL(A5:A9);"OK";"Montant non numérique") |
| Rapprochement | Un total égale celui de la source | =SI(B10=Total_source;"OK";"Écart à justifier") |
| Vraisemblance | Une valeur reste dans un intervalle plausible | =SI(ET(B5>=0;B5<=100000);"OK";"Vérifier") |
Précautions :
- comparer des montants arrondis avec une tolérance (
ABS(écart)<0,005) et non avec=quand des arrondis interviennent (les centimes s'accumulent) ; - placer le contrôle bien en vue (en haut de la feuille de résultats), avec un formatage conditionnel rouge sur l'alerte (cours 29) ;
- un contrôle ne vaut que par son indépendance : un total comparé à lui-même ne détecte rien ; il faut comparer deux calculs construits différemment.