Contrôle, confidentialité et sécurisation d’une feuille de calcul

Introduction

Dans les leçons précédentes, le tableur a été abordé comme outil d’automatisation et de modélisation de problèmes de gestion : structure d’un classeur, formules, fonctions, ergonomie, validation des données et maintenabilité. Une feuille de calcul bien construite ne suffit pourtant pas. En pratique, un classeur peut être :

• mal calculé à cause d’une formule erronée ;
• mal alimenté à cause de données incohérentes ;
• mal utilisé à cause d’une protection insuffisante ;
• mal contrôlé si aucun test n’a été prévu ;
• mal sécurisé si des cellules sensibles restent modifiables ou visibles.

Cette leçon traite donc de la phase indispensable d’audit d’une feuille de calcul. L’objectif n’est plus seulement de produire un résultat, mais de garantir sa fiabilité, sa cohérence et sa confidentialité.

Autrement dit, dans le cadre de la gestion des données du système d’information, le tableur n’est pas seulement un outil de calcul : c’est aussi un support de contrôle, de sécurisation et de maîtrise des risques.

Objectifs d’apprentissage

À l’issue de cette leçon, vous devez être capable de :

• exploiter les outils de contrôle des formules implémentées ;
• concevoir un jeu d’essai de données pour tester une feuille de calcul ;
• concevoir des formules de contrôle de cohérence ;
• sécuriser le classeur et la feuille de calcul ;
• comprendre pourquoi ces contrôles sont essentiels dans le traitement de données via le tableur.

1. Pourquoi auditer une feuille de calcul ?

1.1. Le tableur comme outil de gestion des données

Dans l’UE 8, le tableur s’inscrit dans la gestion des données du système d’information. Il sert à :

• collecter des données ;
• les organiser ;
• automatiser des traitements ;
• produire des résultats utiles à la gestion ;
• aider à la décision.

Mais contrairement à un progiciel fortement paramétré, le tableur reste un outil très souple. Cette souplesse est un avantage… et un risque.

1.2. Les principaux risques liés à une feuille de calcul

Une feuille de calcul peut contenir :

• des références de cellules incorrectes ;
• des copier-coller défectueux ;
• des formules écrasées par une saisie manuelle ;
• des données manquantes ;
• des données aberrantes ;
• des calculs justes sur des hypothèses fausses ;
• des informations sensibles accessibles à des personnes non autorisées.

1.3. Pourquoi le contrôle est indispensable

Le contrôle répond à trois finalités majeures :

1. Fiabilité : s’assurer que les calculs donnent un résultat exact.
2. Cohérence : vérifier que les résultats sont logiquement compatibles entre eux.
3. Sécurisation : empêcher les modifications non souhaitées ou l’accès à certaines données.

Un tableur non contrôlé peut produire des décisions erronées : budget faux, paie erronée, analyse commerciale trompeuse, prévision de trésorerie inexacte, etc.

2. Exploiter les outils de contrôle des formules implémentées

L’une des compétences centrales de cette leçon consiste à exploiter les outils de contrôle des formules implémentées.

L’idée est simple : avant de faire confiance à un résultat, il faut comprendre comment la formule a été construite, quelles cellules elle utilise, et si sa logique est correcte.

2.1. Que signifie « contrôler une formule » ?

Contrôler une formule, c’est vérifier :

• sa syntaxe ;
• les références utilisées ;
• la cohérence des plages ;
• la bonne recopie lors de la duplication ;
• les erreurs éventuelles ;
• la logique métier du calcul.

Exemple simple :

• une cellule de total mensuel contient =SOMME(B2:B12) ;
• si une ligne a été oubliée et que la plage correcte devait être B2:B13, le résultat sera faux sans forcément afficher d’erreur.

Le contrôle ne consiste donc pas seulement à repérer les messages d’erreur visibles, mais aussi les erreurs silencieuses.

2.2. Les outils de contrôle les plus utiles

Sans entrer dans les spécificités d’un logiciel particulier, les tableurs proposent généralement des fonctionnalités d’audit telles que :

• l’affichage de la formule dans la cellule ;
• la visualisation des antécédents ;
• la visualisation des dépendants ;
• l’évaluation pas à pas d’une formule ;
• le repérage des erreurs de formule ;
• la recherche des cellules contenant des constantes ou des formules.

2.3. Afficher les formules

Principe

Au lieu d’afficher les résultats, le tableur peut afficher les formules elles-mêmes.

Intérêt

Cela permet de :

• repérer rapidement une cellule dont la formule diffère des autres ;
• détecter une cellule écrasée par une valeur saisie ;
• vérifier l’uniformité d’une zone de calcul.

Exemple

Dans un tableau de commissions commerciales, la colonne F doit contenir :

=E2*$B$1

Si, en ligne 8, on trouve =E8*5% au lieu de =E8*$B$1, le résultat peut être identique aujourd’hui, mais deviendra faux si le taux en B1 change.

Le contrôle des formules permet ici d’identifier une rupture de logique.

2.4. Repérer les antécédents et les dépendants

Antécédents

Ce sont les cellules utilisées par la formule.

Dépendants

Ce sont les cellules qui utilisent le résultat d’une cellule donnée.

Pourquoi c’est utile

• Pour comprendre la chaîne de calcul.
• Pour vérifier qu’une formule pointe vers les bonnes données.
• Pour mesurer l’impact d’une modification.

Exemple

Une cellule « Résultat net prévisionnel » dépend de :

• chiffre d’affaires ;
• achats ;
• charges de personnel ;
• charges fixes.

Si la formule ne dépend pas de la cellule « charges de personnel », l’oubli devient immédiatement visible avec l’outil d’audit.

2.5. Évaluer une formule pas à pas

Certaines formules sont longues : imbrication de SI, fonctions de recherche, calculs conditionnels, dates, arrondis.

L’évaluation pas à pas permet de voir :

• dans quel ordre le calcul est réalisé ;
• quelle valeur prend chaque sous-expression ;
• à quel endroit la logique se dégrade.

Exemple

Formule :

=SI(C2>1000;C2*0,08;SI(C2>500;C2*0,05;0))

Avec une valeur de C2 = 750, l’évaluation pas à pas permet de vérifier que :

1. C2>1000 est faux ;
2. on passe au second SI ;
3. C2>500 est vrai ;
4. le résultat est 750*0,05.

2.6. Identifier les erreurs visibles

Les erreurs visibles sont précieuses car elles signalent explicitement un problème. Exemples fréquents :

• division par zéro ;
• référence invalide ;
• nom de fonction incorrect ;
• incompatibilité de type ;
• valeur non disponible.

Mais attention : l’absence de message d’erreur ne garantit pas l’exactitude.

2.7. Méthode pratique d’audit des formules

Voici une démarche simple et efficace.

Étape 1 : repérer les zones sensibles

Priorité aux cellules :

• de total ;
• de synthèse ;
• de résultat final ;
• servant à une prise de décision.

Étape 2 : afficher les formules

Vérifier l’homogénéité des calculs sur chaque colonne ou ligne.

Étape 3 : contrôler les références

S’assurer que les formules pointent vers les bonnes cellules et bonnes plages.

Étape 4 : tester les recopies

Vérifier les références relatives et absolues.

Étape 5 : analyser les dépendances

Identifier l’impact d’une erreur sur l’ensemble du classeur.

Étape 6 : documenter les anomalies

Noter :

• la cellule concernée ;
• le type d’erreur ;
• la correction apportée ;
• le risque évité.

3. Concevoir un jeu d’essai de données

Le programme demande explicitement de concevoir un jeu d’essai de données. C’est une compétence essentielle.

3.1. Définition

Un jeu d’essai est un ensemble de données volontairement choisies pour tester une feuille de calcul.

Il ne s’agit pas de données réelles utilisées pour produire un résultat opérationnel, mais de données servant à vérifier que :

• les formules calculent correctement ;
• les cas normaux fonctionnent ;
• les cas limites sont bien gérés ;
• les anomalies sont détectées.

3.2. Pourquoi un jeu d’essai est indispensable

Une formule peut sembler correcte avec des données ordinaires mais échouer :

• pour une valeur nulle ;
• pour une valeur négative ;
• pour une cellule vide ;
• pour une date incohérente ;
• pour un seuil exact.

Le jeu d’essai permet donc de tester le comportement du modèle dans plusieurs situations.

3.3. Les qualités d’un bon jeu d’essai

Un bon jeu d’essai doit être :

• simple : pour pouvoir recalculer mentalement ou manuellement les résultats attendus ;
• ciblé : chaque donnée sert à tester un point précis ;
• varié : il couvre plusieurs cas ;
• documenté : on sait ce que chaque test cherche à vérifier.

3.4. Les catégories de cas à prévoir

1. Cas normal

Le traitement attendu dans une situation classique.

2. Cas limite

Valeur égale à une borne ou très proche d’une borne.

3. Cas nul

Valeur égale à zéro.

4. Cas vide

Cellule non renseignée.

5. Cas aberrant

Valeur incohérente ou inattendue.

6. Cas exceptionnel

Situation rare mais possible.

3.5. Exemple : feuille de calcul de remise commerciale

Supposons la règle suivante :

• si le montant HT est inférieur à 500 €, pas de remise ;
• entre 500 € et 999,99 €, remise de 5 % ;
• à partir de 1 000 €, remise de 8 %.

Jeu d’essai pertinent

• 300 € → remise attendue : 0 %
• 500 € → remise attendue : 5 %
• 999,99 € → remise attendue : 5 %
• 1 000 € → remise attendue : 8 %
• 0 € → remise attendue : 0 %
• cellule vide → comportement à vérifier
• -100 € → anomalie à détecter

Ce jeu d’essai permet de tester :

• les seuils ;
• les bornes ;
• les cas nuls ;
• les cas anormaux.

3.6. Exemple : feuille de calcul de paie simplifiée

On calcule une prime de présence si :

• absence = 0 jour → prime entière ;
• absence entre 1 et 2 jours → demi-prime ;
• absence > 2 jours → pas de prime.

Jeu d’essai

• 0 jour → prime entière
• 1 jour → demi-prime
• 2 jours → demi-prime
• 3 jours → pas de prime
• cellule vide → à traiter
• valeur négative → incohérence

3.7. Méthode de conception d’un jeu d’essai

Étape 1 : identifier la règle de gestion

Exemple : « une commission de 3 % s’applique au-delà de 10 000 € de chiffre d’affaires ».

Étape 2 : repérer les zones à risque

• seuil ;
• division ;
• date ;
• recherche ;
• condition multiple.

Étape 3 : sélectionner des données simples

Choisir des valeurs faciles à vérifier.

Étape 4 : déterminer le résultat attendu

Le résultat attendu doit être connu avant le test.

Étape 5 : comparer le résultat obtenu au résultat attendu

Si l’écart existe, il faut investiguer.

4. Concevoir des formules de contrôle de cohérence

Le programme exige aussi de concevoir des formules de contrôle de cohérence. Il s’agit d’un niveau supérieur au simple calcul.

4.1. Définition

Une formule de contrôle de cohérence ne sert pas à produire le résultat principal, mais à vérifier que ce résultat — ou les données qui y conduisent — est logique.

Elle répond à une question du type :

• le total correspond-il à la somme du détail ?
• le montant TTC est-il cohérent avec le montant HT et la TVA ?
• la date de fin est-elle postérieure à la date de début ?
• le nombre d’heures supplémentaires est-il compatible avec le total d’heures ?

4.2. Pourquoi ces formules sont essentielles

Même avec de bonnes formules, une feuille de calcul peut produire des résultats faux si les données d’entrée sont incohérentes.

Les contrôles de cohérence permettent de :

• détecter les anomalies ;
• alerter l’utilisateur ;
• sécuriser la saisie ;
• fiabiliser l’exploitation du classeur.

4.3. Principales logiques de contrôle

1. Contrôle d’égalité

Vérifier que deux résultats doivent être identiques.

Exemple : Total général = somme des sous-totaux

2. Contrôle de borne

Vérifier qu’une valeur reste dans un intervalle acceptable.

Exemple : Taux de remise entre 0 % et 20 %

3. Contrôle de signe

Vérifier qu’une valeur ne peut pas être négative ou qu’elle doit l’être.

Exemple : Quantité vendue >= 0

4. Contrôle de chronologie

Vérifier l’ordre logique des dates.

Exemple : Date de fin >= date de début

5. Contrôle de complétude

Vérifier qu’une cellule obligatoire n’est pas vide.

6. Contrôle de rapprochement

Comparer deux sources ou deux calculs indépendants.

4.4. Exemples de formules de contrôle

Exemple 1 : contrôle d’un total

Si le total détaillé est en F20 et le total général en F21 :

=SI(F20=F21;"OK";"ANOMALIE")

Exemple 2 : contrôle d’une borne

Pour vérifier qu’un taux en C5 est compris entre 0 et 1 :

=SI(ET(C5>=0;C5<=1);"OK";"HORS BORNE")

Exemple 3 : contrôle de date

=SI(B2<=C2;"OK";"DATE INCOHÉRENTE")

Exemple 4 : contrôle de cellule obligatoire

=SI(ESTVIDE(D2);"À COMPLÉTER";"OK")

Exemple 5 : contrôle de somme

Si un budget total doit égaler la somme de trois postes :

=SI(B10=SOMME(B7:B9);"OK";"ÉCART")

4.5. Contrôles visibles et exploitables

Un bon contrôle de cohérence doit être :

• lisible ;
• immédiatement interprétable ;
• placé au bon endroit ;
• éventuellement mis en valeur par un formatage conditionnel.

Exemple :

• cellule verte si « OK » ;
• cellule rouge si « ANOMALIE ».

4.6. Contrôle de cohérence et séparation des rôles

Il est recommandé de distinguer :

• les cellules de saisie ;
• les cellules de calcul ;
• les cellules de contrôle.

Cette séparation améliore :

• la lisibilité ;
• l’auditabilité ;
• la maintenance ;
• la sécurité.

4.7. Étude de cas : budget mensuel

Une entreprise suit ses dépenses mensuelles dans un tableur.

Données

• achats ;
• salaires ;
• loyers ;
• autres charges ;
• total des charges.

Contrôles à concevoir

1. Vérifier que le total des charges = somme des postes.
2. Vérifier qu’aucune charge n’est négative.
3. Vérifier que le budget réalisé n’excède pas un plafond autorisé.

Exemples de contrôles

• =SI(F10=SOMME(B10:E10);"OK";"ÉCART TOTAL")
• =SI(MIN(B10:E10)>=0;"OK";"VALEUR NÉGATIVE")
• =SI(F10<=G10;"OK";"DÉPASSEMENT")

5. Sécuriser le classeur et la feuille de calcul

La compétence visée inclut explicitement : sécuriser le classeur, la feuille de calcul.

5.1. Pourquoi sécuriser ?

La sécurité d’un tableur répond à plusieurs enjeux :

• empêcher la modification accidentelle des formules ;
• préserver la confidentialité de certaines données ;
• limiter les erreurs de saisie ;
• encadrer les usages selon les utilisateurs.

5.2. Sécuriser n’est pas seulement « mettre un mot de passe »

La sécurisation comprend plusieurs dimensions :

• protéger la structure du classeur ;
• verrouiller certaines cellules ;
• autoriser seulement les zones de saisie ;
• masquer certaines formules ou feuilles ;
• contrôler la diffusion du fichier.

5.3. Protéger la feuille de calcul

La protection d’une feuille permet en général de :

• interdire la modification des cellules verrouillées ;
• autoriser uniquement certaines actions ;
• préserver la mise en page et les formules.

Logique de base

1. identifier les cellules à laisser modifiables ;
2. déverrouiller uniquement ces cellules ;
3. protéger la feuille.

Exemple

Dans un budget prévisionnel :

• cellules jaunes : saisies autorisées ;
• cellules grises : formules ;
• feuille protégée pour éviter toute modification des calculs.

5.4. Protéger le classeur

La protection du classeur vise plutôt :

• sa structure ;
• l’ajout ou la suppression de feuilles ;
• le déplacement ou le renommage d’onglets.

Cela évite, par exemple, qu’un utilisateur supprime par erreur une feuille de paramètres ou une feuille de contrôles.

5.5. Verrouiller les cellules sensibles

Toutes les cellules n’ont pas le même niveau de sensibilité.

À protéger prioritairement

• formules ;
• paramètres de calcul ;
• tables de correspondance ;
• zones de synthèse ;
• cellules de contrôle.

À laisser modifiables

• zones de saisie prévues pour l’utilisateur.

5.6. Masquer certaines formules ou feuilles

Dans certains cas, il peut être utile de :

• masquer une feuille technique ;
• masquer une formule pour éviter sa modification ou sa lecture directe ;
• séparer l’interface utilisateur et les calculs internes.

Cette pratique améliore la lisibilité et réduit les manipulations risquées.

5.7. Confidentialité des données

La sécurisation vise aussi la confidentialité.

Exemples de données sensibles dans un tableur :

• rémunérations ;
• coordonnées personnelles ;
• marges ;
• informations bancaires ;
• hypothèses stratégiques ;
• données commerciales nominatives.

Dans ce cas, il faut limiter :

• l’accès au fichier ;
• l’accès à certaines feuilles ;
• la possibilité de copie ou de modification.

5.8. Bonnes pratiques de sécurisation

1. Séparer saisie, calcul et restitution

Un utilisateur ne doit pas modifier les cellules de calcul.

2. Identifier visuellement les zones modifiables

Par exemple par une couleur dédiée.

3. Protéger les cellules contenant des formules

Pour éviter l’écrasement accidentel.

4. Protéger la structure du classeur

Pour éviter la suppression d’onglets essentiels.

5. Documenter les droits d’usage

Qui peut saisir ? qui peut consulter ? qui peut modifier la structure ?

6. Prévoir une version de sauvegarde

Avant modification importante.

6. Démarche complète d’audit d’une feuille de calcul

Voici une méthode globale mobilisant toutes les compétences du thème.

6.1. Étape 1 : comprendre la finalité du classeur

Avant tout contrôle, il faut savoir :

• à quoi sert le classeur ;
• qui l’utilise ;
• quelles décisions dépendent de lui ;
• quelles cellules sont critiques.

6.2. Étape 2 : identifier les zones

Repérer :

• les zones de saisie ;
• les zones de calcul ;
• les zones de contrôle ;
• les zones de restitution.

6.3. Étape 3 : auditer les formules

Utiliser les outils de contrôle pour :

• afficher les formules ;
• repérer les antécédents ;
• repérer les dépendants ;
• détecter les incohérences de recopie.

6.4. Étape 4 : tester avec un jeu d’essai

Construire des cas :

• normaux ;
• limites ;
• nuls ;
• vides ;
• aberrants.

Comparer les résultats obtenus aux résultats attendus.

6.5. Étape 5 : mettre en place des contrôles de cohérence

Créer des cellules de contrôle avec messages explicites :

• OK ;
• anomalie ;
• dépassement ;
• donnée manquante.

6.6. Étape 6 : sécuriser le fichier

• verrouiller les cellules sensibles ;
• protéger la feuille ;
• protéger le classeur ;
• limiter les zones de saisie ;
• organiser la confidentialité.

6.7. Étape 7 : documenter

Documenter :

• les hypothèses ;
• les contrôles ;
• les zones modifiables ;
• les mots de passe ou procédures d’accès selon les règles internes.

7. Cas pratique fil rouge : audit d’un tableau de suivi commercial

7.1. Situation

Une entreprise suit ses ventes mensuelles dans un tableur. Pour chaque commercial, le classeur calcule :

• chiffre d’affaires ;
• remise ;
• chiffre d’affaires net ;
• commission ;
• total mensuel.

7.2. Risques identifiés

• erreur dans la formule de commission ;
• remise appliquée hors seuil ;
• total mensuel incomplet ;
• saisie d’un chiffre d’affaires négatif ;
• modification accidentelle des formules.

7.3. Audit des formules

On affiche toutes les formules et on constate que :

• la colonne Commission utilise partout =CA_net*$B$1, sauf une ligne où un taux en dur a été saisi ;
• le total général oublie la dernière ligne.

7.4. Jeu d’essai

On choisit les données suivantes :

• 0 € ;
• 499 € ;
• 500 € ;
• 1 000 € ;
• -50 € ;
• cellule vide.

Résultats attendus :

• pas de remise sous 500 € ;
• remise correcte à 500 € ;
• anomalie pour valeur négative ;
• comportement défini pour cellule vide.

7.5. Contrôles de cohérence

On ajoute :

• contrôle du total : somme détail = total affiché ;
• contrôle de signe : CA >= 0 ;
• contrôle de commission : commission <= CA net ;
• contrôle de complétude : nom du commercial non vide.

7.6. Sécurisation

• cellules de saisie déverrouillées ;
• cellules de calcul verrouillées ;
• feuille protégée ;
• feuille « Paramètres » masquée ;
• structure du classeur protégée.

7.7. Résultat

Le classeur devient :

• plus fiable ;
• plus lisible ;
• plus sûr ;
• plus adapté à un usage professionnel.

8. Erreurs fréquentes à éviter

8.1. Confondre contrôle et correction

Un contrôle ne corrige pas automatiquement l’erreur. Il la signale.

8.2. Tester uniquement avec des données réalistes

Il faut aussi tester les cas extrêmes et les cas anormaux.

8.3. Protéger trop tard

Si la sécurisation intervient après diffusion du fichier, des erreurs ont peut-être déjà été introduites.

8.4. Laisser des constantes dans les formules

Les paramètres doivent être isolés dans des cellules dédiées.

8.5. Ne pas distinguer les zones de saisie

L’utilisateur doit savoir immédiatement où il peut intervenir.

8.6. Utiliser des contrôles incompréhensibles

Un contrôle doit être lisible :

• « OK » ;
• « ÉCART » ;
• « VALEUR NÉGATIVE ».

Pas seulement VRAI ou FAUX sans contexte, si cela nuit à l’exploitation.

9. Méthode de travail recommandée en contexte professionnel

Dans un contexte de gestion, on peut retenir la séquence suivante :

Avant la mise en service

• vérifier la structure ;
• auditer les formules ;
• créer un jeu d’essai ;
• ajouter des contrôles de cohérence ;
• sécuriser le classeur.

Pendant l’utilisation

• limiter les saisies aux zones prévues ;
• surveiller les messages d’anomalie ;
• conserver une version de référence.

Lors d’une modification

• tester à nouveau les calculs ;
• vérifier les dépendances ;
• actualiser les contrôles ;
• revalider la protection.

10. Points à retenir

Mémoriser l’essentiel

• Le tableur fait partie de la gestion des données du système d’information.
• Une feuille de calcul doit être auditée, pas seulement remplie.
• Exploiter les outils de contrôle des formules implémentées permet de repérer erreurs de références, ruptures de logique et anomalies de recopie.
• Concevoir un jeu d’essai de données consiste à prévoir des cas normaux, limites, nuls, vides et aberrants.
• Concevoir des formules de contrôle de cohérence permet de détecter les écarts, incohérences et données anormales.
• Sécuriser le classeur, la feuille de calcul signifie protéger les cellules sensibles, la structure et l’accès aux informations.
• Fiabilité, cohérence et confidentialité sont trois exigences majeures d’un tableur professionnel.

Mémo opérationnel

Pour auditer un tableur

1. Identifier les cellules critiques.
2. Afficher les formules.
3. Contrôler antécédents et dépendants.
4. Tester les recopies.
5. Utiliser un jeu d’essai.
6. Comparer au résultat attendu.
7. Ajouter des contrôles de cohérence.
8. Protéger feuille et classeur.

Pour concevoir un bon contrôle

• simple ;
• visible ;
• explicite ;
• utile ;
• relié à un risque réel.

Pour sécuriser efficacement

• distinguer saisie / calcul / contrôle ;
• verrouiller les formules ;
• protéger la structure ;
• limiter l’accès aux données sensibles.

Conclusion

Traiter des données via le tableur ne se limite jamais à écrire des formules. Dans un environnement professionnel, la vraie compétence consiste à produire une feuille de calcul fiable, contrôlée, cohérente et sécurisée.

Cette leçon complète donc les apprentissages précédents : après avoir appris à construire un modèle de feuille de calcul, il faut désormais savoir le tester, le contrôler et le protéger. C’est cette démarche qui transforme un simple fichier en véritable outil de gestion exploitable dans le système d’information.