locks

Verrouiller son modèle Excel

20/11/2013

Verrouiller un classeur Excel est un bon moyen de s’assurer que les utilisateurs du modèle ne pourront pas l’altérer en effectuant des modifications non souhaitables. Cependant, un modèle verrouillé perd en flexibilité et en adaptabilité, ce qui limite son utilité pratique et peut rapidement frustrer les utilisateurs. Est-il préférable de verrouiller un classeur ou d’ « éduquer » les utilisateurs ? Quelles alternatives existe-t-il pour sécuriser un modèle tout en conservant de la flexibilité ?

1. Former l’utilisateur aux subtilités du modèle

Plutôt que de verrouiller totalement un modèle en utilisant les boutons natifs d’Excel « Protéger le classeur » et « Protéger la feuille », il convient en premier lieu d’informer l’utilisateur. Quelles actions entrent dans le cadre normal de l’utilisation du modèle ? Quelles actions risquent de l’altérer et d’en fausser les résultats ? Quelles zones sont modifiables et quelles zones sont figées ? L’ajout de lignes et de colonnes est-il permis ou risque-t-il de créer des décalages de formules ? Autant de consignes que le créateur du modèle se doit d’expliciter clairement, dans une notice d’utilisation sur la page de couverture par exemple ou alors grâce aux validations de données.

Qu’est-ce que la validation de données ?

La validation de données (« Données », « Validation de données ») permet de contrôler le type de données entrées dans une cellule. Ainsi, le créateur d’un modèle peut limiter les données possibles aux nombres entiers ou décimaux dans un intervalle précis, à une date ou à une période de temps dans un intervalle précis, à une liste de valeurs prédéfinies etc.
Il est ensuite possible d’afficher un message informatif quand l’utilisateur sélectionne la cellule (« Validation de données », « Message de Saisie »), ou alors d’afficher une alerte d’erreur quand l’utilisateur entre une donnée qui ne correspond pas au format de validation (« Validation de données », « Alerte d’erreur »). L’alerte d’erreur peut alors, au choix, bloquer les données fausses ou alors simplement informer l’utilisateur que son entrée ne correspond pas au format attendu.

2. Verrouiller partiellement le modèle

Cependant, former les utilisateurs ne prémunit pas le créateur du modèle contre d’éventuelles erreurs de saisie ou modifications accidentelles de formules. C’est pourquoi la mise en place de verrouillages partiels doit être une solution privilégiée. Ainsi, les zones comprenant des données brutes (données historiques figées), des formules ou de la restitution de calculs seront préférablement verrouillées, tandis que les zones d’hypothèses ou les zones vides pourront être modifiées à souhait.

Comment faire pour ne verrouiller que certaines cellules ?

Par défaut, toutes les cellules seront verrouillées si l’utilisateur décide de protéger l’onglet. Pour faire en sorte que certaines cellules soient modifiables alors que le reste de l’onglet est protégé, il faut désactiver la protection des cellules concernées (clic droit sur la cellule, format de cellules, protection). Une fois l’onglet protégé, toutes les cellules seront protégées, sauf celles dont le verrouillage a été décoché !
Il est également possible de permettre la modification de certaines plages protégées seulement par les utilisateurs détenant le mot de passe correspondant à cette plage (« Révision », « Permettre la modification des plages »). Les plages en question seront alors protégées de manière générale, mais resteront modifiables par les utilisateurs détenant le bon mot de passe !

Comment verrouiller le format et la position des graphiques ?

Par défaut, la taille et la position des graphiques varient en fonction du format des cellules sous-jacentes. Pour verrouiller l’aspect des graphiques, une simple manipulation suffit. Sur Excel 2007, cliquer sur le graphique, aller dans l’onglet « Format », cliquer sur la petite flèche en bas à droite de la catégorie « Taille », sélectionner l’onglet « Propriétés » et cocher l’option souhaitée. Sur Excel 2010, effectuer un clic droit sur le graphique, sélectionner « Taille et Propriétés », « Propriétés » puis cocher l’option souhaitée !

De l’utilité des mots de passe

Pour augmenter encore la sécurité du modèle, le créateur peut mettre en place un mot de passe sans lequel la déprotection des onglets n’est pas possible (« Révision », « Protéger la feuille », « Mot de passe pour ôter la protection de la feuille »). Cependant, un mot de passe reste assez facilement contournable par tout utilisateur maniant relativement bien les moyens informatiques, ce qui nous ramène à notre premier constat : informer l’utilisateur reste le meilleur moyen d’éviter toute altération du modèle !

3. Utiliser des macros pour limiter les erreurs possibles

Par ailleurs, l’utilisation de macros permet de s’assurer que certaines actions seront effectuées correctement.
Ainsi, l’ajout de lignes ou de colonnes peut être automatisé pour éviter tout risque d’erreur. Une telle macro procédera alors à la déprotection de la feuille en question, à l’ajout des lignes et/ou des colonnes nécessaires, à la copie des formules et des formats dans les nouvelles plages de manière à respecter l’ergonomie globale du modèle, puis procédera à la reprotection de la feuille.
Autre exemple, une macro de contrôle peut être mise en place afin de vérifier que toutes les données d’un modèle correspondent bien aux formats attendus. Une telle macro parcourra alors les différentes plages de données du modèle et contrôlera que les dates sont bien des dates, que les sommes d’argent sont bien des sommes d’argent, que les données textuelles sont bien du texte etc. Ensuite, la macro informera l’utilisateur sous forme de messages du type « La cellule Input!C18 doit contenir une date ». Cela permet à l’utilisateur de vérifier qu’aucune erreur ne s’est glissée dans le modèle, ce qui peut s’avérer bien utile quand on traite de larges bases de données par exemple !