Protection, validation et audit de données

L’audit

excel-validation-1L’audit des formules est un outil permettant de cerner les problèmes des feuilles de calcul. Grâce à l’audit, nous pouvons voir visuellement quelle sont les données qui composent une formule, où les résultats qui en découlent.

Dans l’exemple ci-contre, nous voyons que pour la formule en B5, les valeurs en B3 et B4 ont été utilisées. Ce sont les antécédents de la formule. De même, la valeur de B5 joue dans le résultat en B7.

Pour activer cette représentation graphique :

  • Sélectionnez la cellule à auditer (ici B5).
  • Dans l’onglet Formules, groupe Vérification de formules, cliquez sur « Repérer les antécédents ».excel-validation-2
  • Les antécédents s’afficheront sous forme d’une flèche dans le tableur.
  • Pour afficher les dépendants, cliquez sur le bouton « Repérer les dépendants »
Si la cellule sélectionnée est référencée dans une autre feuille de calcul ou un autre classeur, une flèche noire pointant vers un symbole de feuille de calcul s’affichera. Il faut que cet autre classeur soit ouvert pour repérer les dépendances.

Exercice : Validation et protection

Concevons la feuille suivante.excel-validation-3

Nous souhaitons restreindre l’accès aux données du classeur, afin d’éviter les erreurs d’encodage et la modification des formules. Nous allons utiliser les outils de validation de données pour restreindre les valeurs, ainsi que la protection des feuilles pour empêcher toute modification intempestive.

  • Entrez le tableau ci-dessus.

  • En E11, utilisez la somme automatique. Tirez la formule vers la droite.

  • En F3, entrez une formule permettant de ventiler le montant en fonction du type1. Tirez la formule en bas et à droite.

Une fois le tableau réalisé, nous souhaitons :

  • Aider l’utilisateur dans le remplissage de la partie gauche du tableau :

    • La date doit être obligatoirement inférieure à la date du jour ;

    • le code de référence ne doit pas dépasser 5 caractères (texte) ;

    • le type doit être une liste contenant uniquement H, P ou G ;

    • le montant doit être positif.

  • D’autre part, nous voulons interdire la modification du reste de la feuille.

Validation de données

La validation de données permet de restreindre les données entrées par l’utilisateur. La validation peut afficher un texte d’aide à l’insertion des données, et peut comporter également un message d’erreur.

  • excel-validation-4En A3:A10. Dans l’onglet Données, groupe Outils de données, cliquez sur le bouton « Validation des données ».

  • Dans la boîte de dialogue qui s’affiche,choisissez d’autoriser les dates inférieures ou égales à la date du jour (=AUJOURDHUI() ).

  • Dans l’onglet suivant, vous avez la possibilité d’afficher un message d’aide à la saisie.

  • Dans l’onglet Alerte d’erreur, vous pouvez demander à Excel de réagir face à des erreurs de données. Il existe 3 niveaux d’erreurs, chacun représenté par une icône différente :

    • Le type Arrêt va obliger l’utilisateur à entrer une valeur correcte ;

    • Le type Avertissement va avertir l’utilisateur de la valeur incorrecte et lui demandera s’il veut continuer ;

    • Le type Information va signaler l’erreur, mais sans demander une correction.

Dans notre cas, nous allons placer un message d’erreur de type Arrêt.

  • En B3:B10. Réactivez la validation de données. Cette fois-ci, entrez une limitation de longueur de texte égale à 5. Le message d’erreur sera de type Avertissement.

  • En D3:D10, sélectionnez un type Liste dans la validation de données. Dans la source, vous pouvez soit importer une liste depuis une plage de cellules, ou entrer les données directement. Tapez G ; H ; P, séparés par un point-virgule (;).

  • En E3:E10, restreignez les données à des données décimales supérieures à 0.

Protection des feuilles

Pour éviter la modification ou la suppression de nos formules, nous allons protéger la feuille de calcul. Ainsi, seule la partie gauche du tableau sera disponible en écriture.excel-validation-8

  • Sélectionnez la plage A3:E10. Faites un clic droit et cliquez sur format de cellule. Vous pouvez aussi utiliser la boîte de dialogue se situant dans l’onglet Accueil, groupe Nombre.

  • Dans la partie « Protection », décocher la case Verrouillée.

  • Ensuite, dans l’onglet révision, groupe Modification, cliquez sur « Protéger la feuille ».

  • Entrez un mot de passe si nécessaire, puis choisissez les options désirées. La feuille est maintenant protégée.

Pourquoi déverrouiller les cellules avant de protéger la feuille ? Soyons logique. La protection des feuilles permet d’empêcher toute modification. Or, le fait de vouloir déverrouiller certaines cellules constitue une modification, qui est interdite par la protection.

Résumé

Dans cet exercice, nous avons vu :

  • La validation de données ;

  • Les types d’erreurs ;

  • La protection des feuilles.

1=SI(GAUCHE(F$2)=$D3;$E3 ; » »). On compare le premier caractère à gauche du mot avec le type, et on place le montant dans la colonne si les données correspondent.

[sb_sibling_prev] [sb_sibling_next]

Print Friendly, PDF & Email