EX04 | Fiabiliser la saisie et protéger le classeur
Document fictif à usage pédagogique.
Énoncé
Contexte
- Entreprise : Brévalis Industries (fictive).
- Demande : les commerciaux saisissent eux-mêmes leurs ventes ; il faut empêcher les erreurs et protéger les formules.
- Durée : 35 min | Niveau : intermédiaire.
- Départ : EX04_depart.xlsx (EX03_corrige.xlsx + feuilles Produits avec tblProduit, Clients avec tblClient, Listes avec tblCanal et tblRegion) | Fin : EX04_corrige.xlsx.
Travail demandé
- Partie A : poser des règles de validation sur tblVente (liste de canaux, quantité, période, numéro de commande unique), avec message de saisie et alerte d'erreur.
- Partie A : construire une liste dépendante gamme puis article avec INDIRECT.
- Partie A : repérer les données non valides déjà saisies.
- Partie B : déverrouiller les cellules de saisie, protéger la feuille, autoriser une plage modifiable.
- Partie B : protéger la structure du classeur et poser un mot de passe de modification.
- Comparer les trois niveaux de protection (feuille, classeur, fichier).
Simulation
Simulation interactive EX04 | à venir
Corrigé
E00 | Ouvrir le fichier de départ
- Fichier > Ouvrir > Parcourir >
Documents\excel-lab\depart\EX04_depart.xlsx.
Capture : boîte Ouvrir, dossier depart, fichier EX04_depart.xlsx sélectionné
Partie A | Validation des données
E01 | Nom de plage pour la liste
- Formules > Définir un nom :
lstCanal==tblCanal[Canal]. - Résultat : nom créé.
Capture : boîte Nouveau nom
E02 | Liste déroulante
- Sélectionner les données de la colonne Canal de
tblVente, Données > Validation des données > Autoriser : Liste, Source :=lstCanal. - Résultat : liste déroulante active.
Capture : boîte Validation des données, onglet Options (Liste)
E03 | Tester la liste
- Cliquer dans une cellule Canal et ouvrir la liste.
- Résultat : 4 canaux proposés.
Capture : cellule avec liste déroulante ouverte
E04 | Nombre entier
- Colonne Quantite : Autoriser Nombre entier, entre 1 et 500.
- Résultat : règle posée.
Capture : boîte Validation, onglet Options (Nombre entier)
E05 | Message de saisie
- Onglet Message de saisie : titre « Quantité », message « Entre 1 et 500 unités ».
- Résultat : info-bulle à la sélection.
Capture : onglet Message de saisie, puis info-bulle sur la feuille
E06 | Alerte d'erreur
- Onglet Alerte d'erreur : style Arrêt, message « Quantité hors limites » ; saisir 900 pour tester.
- Résultat : saisie refusée.
Capture : fenêtre d'alerte Arrêt
E07 | Plage de dates
- Colonne Date : Autoriser Date, entre 01/01/2025 et 31/12/2026, alerte Avertissement.
- Résultat : saisie hors période signalée mais possible.
Capture : fenêtre d'alerte Avertissement
E08 | Refuser les doublons
- Colonne NumCommande : Autoriser Personnalisé, formule
=NB.SI(A:A;A2)=1. - Résultat : doublons refusés.
Capture : boîte Validation, onglet Options (Personnalisé)
E09 | Listes dépendantes
- Feuille Saisie : liste dépendante. En B2 liste des gammes P1 à P4 ; en B3 Source
=INDIRECT(B2)(noms P1 à P4 fournis). - Résultat : les articles proposés dépendent de la gamme.
Capture : deux listes ouvertes successivement
E10 | Entourer les données non valides
- Données > Validation des données > Entourer les données non valides.
- Résultat : cercles rouges sur 3 lignes anciennes.
Capture : feuille avec cercles rouges
Partie B | Protection
E11 | Déverrouiller les cellules de saisie
- Feuille Saisie : sélectionner les cellules à remplir, Ctrl+1 > Protection > décocher Verrouillée.
- Résultat : cellules de saisie déverrouillées.
Capture : boîte Format de cellule, onglet Protection
E12 | Protéger la feuille
- Révision > Protéger la feuille, mot de passe
Brevalis2026, autoriser Utiliser le filtre automatique et les segments. - Résultat : feuille protégée.
Capture : boîte Protéger la feuille et liste des autorisations
E13 | Tester la protection
- Tenter de modifier une cellule de formule.
- Résultat : saisie refusée.
Capture : message « La cellule ou le graphique se trouve sur une feuille protégée »
E14 | Plages modifiables
- Révision > Autoriser la modification des plages : plage B2:B3 sans mot de passe.
- Résultat : plage modifiable.
Capture : boîte Autoriser les utilisateurs à modifier des plages
E15 | Protéger la structure du classeur
- Révision > Protéger le classeur (structure), même mot de passe ; tenter un clic droit sur un onglet.
- Résultat : insérer, supprimer, renommer grisés.
Capture : boîte Protéger la structure et les fenêtres, puis menu d'onglet grisé
E16 | Mot de passe de modification
- Fichier > Enregistrer sous > Outils > Options générales : mot de passe pour la modification.
- Résultat : ouverture en lecture seule sans mot de passe.
Capture : boîte Options générales
E17 | Synthèse des protections
- Comparer les trois niveaux (feuille, classeur, fichier) dans un tableau de synthèse.
- Résultat : synthèse complétée.
Capture : feuille Synthèse
Points de vigilance
- La protection de feuille ne chiffre rien ; seul le mot de passe d'ouverture (Fichier > Informations > Protéger le classeur > Chiffrer avec mot de passe) protège réellement les données.