top of page

MISSION 5: SECURISER UN CLASSEUR

2024-07-01 14_48_09-OBJECTIFS DE LA MISSION.odt - Word.png

Les erreurs de formules dans un tableur

Les antécédents et dépendants d'une cellule

Les fonctions ESTTEXTE(), ESTVIDE(), ESTNUM()

Avant de commencer la mission, vous devez maitriser les notions abordées dans :

​​

  • La fiche outil n°12 "Gestion des erreurs" et fiche outil n°13 "Audit d'une feuille de calcul" 

  • OU les 3 vidéos ci-dessous :

CONTEXTE :  Chez Lou Prunel, il est fréquent de manipuler des feuilles de calcul créées par d'autres personnes (salariés de l'entreprise, fournisseurs ou encore des clients). Pour accomplir votre travail, il est nécessaire de comprendre la logique du classeur telle que l'a voulue son auteur.

​

Madame DAVAR vous demande donc d'auditer le classeur "LOUPRUNELERREUR.xls". Il s'agit d'un tableur créé par son ancien stagiaire lors de l'été 2020. Sa proposition de classeur pour gérer l'activité n'est malheureusement pas fonctionnelle. De nombreuses erreurs sont présentes dans les feuilles "FACTURE" et "VENTES".

L'audit de formules permet de comprendre l'objectif d'une feuille de calcul et de vérifier son fonctionnement. Il répond à 5 questions :

  • 1) QUEL EST LE BUT DE LA FEUILLE DE CALCUL ?

  • 2) QUELS SONT LES TRAITEMENTS REALISES ? Quelles fonctions sont présentes ? Sont-elles correctement utilisées ?

  • 3) QUELLE EST LA PLACE DE LA FEUILLE DANS LE SI ? Utilise-t-elle des données provenant d'autres applications ? Ses résultats sont-ils récupérés par d'autres applications ?

  • 4) QUELS UTILISATEURS SONT AMENES A UTILISER LA FEUILLE ? Qui à accès à la feuille de calcul ? Est-ce qu'il y a des protections mises en place ?

  • 5) QUELS SONT LES DYSFONCTIONNEMENTS DANS LA FEUILLE DE CALCUL ?

​

Dans Excel, on trouve les outils d'audit dans le menu "Formules" > Vérifications de formules

Les outils de protection des éléments de la feuille de calcul sont accessibles dans le menu "Révision" > Protéger

2020-12-07 13_54_39-Window.png

Dans Libre Office 6, les outils d'audit sont accessibles dans le menu "Outils" et "Données" > Validité

2020-12-07 13_51_35-Window.png
2020-12-07 13_57_24-Window.png

TRAVAIL A FAIRE

1) Télécharger le classeur "LOUPRUNELERREUR.xls". A l'aide des antécédents/dépendants et de l'évaluation de formule, expliquer l'origine des erreurs puis les corriger : 

  • C15 (Zone en violet)

  • C16:D16 (Zone bleu ciel)

  • C17:D17 (Zone jaune)

  • C18:D19 (Zone rouge)

  • E22 (Zone orange)

  • D6:E9 (Zone verte)

​

Si besoin, consulter l'aide ci-dessous

J'AI BESOIN D'AIDE !

Si vous bloquez sur la question 1, une aide est disponible ci-dessous. Surlignez le texte ci-dessous pour le faire apparaitre :

  • Vérifier que le nom de la fonction est correct

  • Pour C16:D16, attention aux valeurs utilisées dans RECHERCHEV et EQUIV;
  • Pour C17:D17, l'erreur #N/A désigne une valeur non-connue. Vérifier la présence de la valeur "PRO_032" dans la feuille PRODUITS.
  • Pour C18:D18, on cherche à ne rien afficher si la cellule A18 est vide. Utilisez la fonction ESTVIDE() avec SI().
  • Pour D6:E9, l'erreur #REF! indique une suppression des cellules utilisées dans la formule. Reprendre la mission 4 pour identifier le problème.

     

TRAVAIL A FAIRE

2) Dans la feuille "VENTES", corriger les différentes erreurs (voir les zones en bleus)

3) Répondez aux questionnaires ci-dessous pour vous entrainer :

Par défaut, les tableurs laissent un libre accès (modification, saisie, suppression, ...) à l'ensemble des feuilles de calcul.

​

Il est possible de sécuriser l'accès au classeur par un mot de passe ou de gérer plus finement les protections de chaque feuille de calcul d'un classeur (droit d'insertion et de modifcation)

​

Pour plus de précision : https://support.microsoft.com/fr-fr/office/prot%C3%A9ger-une-feuille-de-calcul-3179efdb-1285-4d49-a9c3-f4ca36276de6

TRAVAIL A FAIRE

4) Toujours dans le classeur "LOUPRUNELERREUR", dans la feuille "PRODUITS", essayer de rajouter le produit "PRO_032". Expliquer le message d'erreur du tableur.

​

5) Justifier le choix de Madame DAVAR de bloquer la feuille "PRODUITS" avec un mot de passe.

​

​

CONTEXTE :  Madame DAVAR vous demande de reprendre votre classeur (voir la mission 4). Avant de le confier aux commerciaux de Lou Prunel, elle vous demande de réfléchir aux protections des cellules et des feuilles. Voici ces remarques :

  • Sur la feuille "FACTURE" : "Les commerciaux doivent uniquement saisir la référence du produit, la quantité, le code client, la date, le code commercial et le code de la vente. Le reste doit être intégralement protégés. La sélection doit être possible"

  • Sur la feuille "PRODUITS" : "Toutes les valeurs doivent être protégées. Nos commerciaux doivent pouvoir sélectionner une valeur. Ils ont également besoin de trier et de filtrer les données."

  • Sur la feuille "COMMERCIAUX" : "Les commerciaux doivent pouvoir changer leur produits de référence. Enfin, l'ajout de nouveau commercial doit être possible."

  • Sur les autres feuilles, les données proviennent d'une autre application (Progiciel de gestion intégré pour la feuille "VENTES"; Module CRM pour la feuille "SYNTHESE"). Aucune modification, ni sélection ne seront autorisées sur ces deux feuilles.

TRAVAIL A FAIRE

​

​

6) Reprenez votre classeur de la mission 4 (téléchargez la correction si besoin). Dans la feuille "FACTURE", identifier les zones de saisie. En déduire, les plages de cellule à bloquer. Utiliser l'onglet "Révision" pour configurer votre solution.

​

7) Toujours dans votre classeur, pour chaque feuille, proposer une politique de protection. Justifier votre raisonnement.

J'AI BESOIN D'AIDE !

Si vous bloquez sur la question 6 ou 7, cliquez sur les boutons ci-dessous pour des informations complémentaires. 

bottom of page