Validation, protection et macros
Ce que ce chapitre apporte 6 points
- Empêcher les saisies fausses en amont avec la validation de données.
- Signaler automatiquement ce qui demande une action avec la mise en forme conditionnelle.
- Organiser un classeur en trois zones pour qu'il reste juste entre d'autres mains.
- Protéger les formules sans empêcher la saisie.
- Situer ce qu'une macro apporte, et ce qu'elle coûte.
- Reconnaître les signaux qui disent que le tableur n'est plus le bon outil.
La validation de données
La validation limite ce qu'une cellule accepte : une liste de choix, un intervalle de nombres, une plage de dates, une longueur de texte.
Elle se règle par Données → Validation des données, et se recopie comme une mise en forme.
Corriger en amont coûte cent fois moins cher. Une catégorie saisie tantôt « informatique », tantôt « Informatique », tantôt « info » donne trois catégories là où il n'y en a qu'une. Tous les NB.SI du chapitre 6 comptent alors faux, et silencieusement.
Une liste déroulante supprime le problème à la racine : on ne peut plus taper autre chose que ce qui est prévu.
La bonne façon de la construire est de mettre les valeurs autorisées dans une colonne à part, sur une feuille de paramètres, et de faire pointer la validation dessus. Ajouter une catégorie devient alors une saisie, pas une reconfiguration.
Le message de saisie et l'alerte d'erreur s'y règlent aussi : autant écrire ce qui est attendu plutôt que de laisser deviner.
Comment garantir qu'une colonne de catégories ne contienne que des valeurs prévues ?
La mise en forme conditionnelle
Elle applique une couleur, une icône ou une barre selon une règle portant sur la valeur de la cellule. La règle se réévalue à chaque changement.
Signaler un seuil : stock sous le minimum, retard de plus de trente jours, écart supérieur à 5 %.
Repérer les doublons : une règle prête à l'emploi les colore, ce qui remplace un tri manuel.
Comparer d'un coup d'œil avec des barres de données, qui donnent une échelle visuelle directement dans les cellules.
Ce qui nuit : colorier trop. Quand la moitié du tableau est en couleur, plus rien ne ressort et la mise en forme conditionnelle a l'effet exactement inverse de celui qu'on cherchait. Une règle, une couleur, et seulement pour ce qui demande une action.
Une règle qui suit la valeur
Le cas le plus simple compare la cellule elle-même à un seuil, avec le même critère qu'un NB.SI : <0, <=7, >100. On le règle par Accueil → Mise en forme conditionnelle → Règles de mise en surbrillance des cellules.
Le planning de maintenance ci-dessous en porte deux sur la colonne des jours restants : rouge quand la révision est dépassée, orange quand elle tombe dans la semaine. La légende sous la feuille rappelle chaque règle et compte les cellules qu'elle colore.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Machine | Jours depuis la révision | Périodicité (jours) | Jours restants |
| 2 | Tour CN 1 | 12 | 90 | 78 |
| 3 | Tour CN 2 | 86 | 90 | 4 |
| 4 | Fraiseuse 5 axes | 64 | 60 | -4 |
| 5 | Presse plieuse | 150 | 180 | 30 |
| 6 | Compresseur | 25 | 30 | 5 |
| 7 | Pont roulant | 170 | 180 | 10 |
| 8 | Scie à ruban | 40 | 30 | -10 |
| 9 |
- D2:D8 : valeur inférieure à 0 (2 cellules)
- D2:D8 : valeur inférieure ou égale à 7 (2 cellules)
B2 contient la valeur « 12 ». Ce n'est pas une formule : rien ne se recalculera ici.
- Sélectionner la plage D2:D8, puis onglet Accueil, Mise en forme conditionnelle, Nouvelle règle.
- Choisir le type Appliquer une mise en forme uniquement aux cellules qui contiennent, puis Valeur de la cellule et l'opérateur de comparaison voulu, ici une valeur inférieure à 0.
- Cliquer sur Format, choisir un remplissage rouge, valider par OK.
- Répéter pour la seconde règle (valeur inférieure ou égale à 7, remplissage orange).
-4. Cette valeur vérifie <0, mais elle vérifie aussi <=7 : les deux règles la réclament. Le tableur tranche par l'ordre du gestionnaire de règles (Mise en forme conditionnelle → Gérer les règles), de haut en bas.La feuille ci-dessous porte exactement les deux mêmes règles, dans l'ordre inverse. L'orange passe en premier et prend tout ce qui est inférieur ou égal à 7, retards compris. La légende l'avoue : la règle rouge ne colore plus aucune cellule, et les deux machines en retard se confondent avec celles qui ont encore quelques jours.
La règle pratique : la condition la plus grave en haut, la plus large en bas.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Machine | Jours depuis la révision | Périodicité (jours) | Jours restants |
| 2 | Tour CN 2 | 86 | 90 | 4 |
| 3 | Fraiseuse 5 axes | 64 | 60 | -4 |
| 4 | Compresseur | 25 | 30 | 5 |
| 5 | Scie à ruban | 40 | 30 | -10 |
| 6 |
- D2:D5 : valeur inférieure ou égale à 7 (4 cellules)
- D2:D5 : valeur inférieure à 0 (0 cellule)
D3 contient la formule =C3-B3, et affiche -4. Les cellules qu'elle utilise sont teintées.
- Sélectionner la plage concernée, puis onglet Accueil, Mise en forme conditionnelle, Gérer les règles.
- Sélectionner la règle rouge dans la liste, puis cliquer sur Monter jusqu'à ce qu'elle soit au-dessus de la règle orange.
- Valider par OK.
La règle placée plus haut l'emporte sur celle qui est plus bas : la plus grave doit passer en premier.
Colorer toute la ligne : la règle par formule
Colorer la seule case du stock ne suffit pas toujours : dans un long tableau, l'œil cherche la ligne, avec le nom de l'article. Un critère simple ne sait pas le faire, puisqu'il ne regarde que la cellule qu'il colore. Il faut une règle par formule : Mise en forme conditionnelle → Nouvelle règle → « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ».
La formule s'écrit pour la cellule en haut à gauche de la plage, puis le tableur la recopie sur toutes les autres, exactement comme une recopie vers le bas et vers la droite. D'où son écriture : =$B2<$C2. Le $ devant la lettre fige la colonne, et le 2 laissé libre suit la ligne. Chaque cellule, quelle que soit sa colonne, pose donc la même question : « le stock de ma ligne est-il sous le seuil de ma ligne ? ».
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Article | Stock | Seuil | En commande | Délai (jours) |
| 2 | Vis M6x20 | 1200 | 500 | 0 | 5 |
| 3 | Foret HSS 8 mm | 14 | 20 | 50 | 10 |
| 4 | Plaquette carbure | 35 | 30 | 0 | 15 |
| 5 | Gants anticoupure | 18 | 25 | 0 | 3 |
| 6 | Huile de coupe 5 L | 6 | 4 | 10 | 7 |
| 7 | Disque à tronçonner | 40 | 40 | 0 | 4 |
| 8 | Joint torique 40 mm | 90 | 100 | 200 | 12 |
| 9 |
- A2:E8 : formule =$B2<$C2 (15 cellules)
B4 contient la valeur « 35 ». Ce n'est pas une formule : rien ne se recalculera ici.
$ qui les fait regarder la colonne B.
- Sélectionner la plage à colorer, ici A2:E8, puis onglet Accueil, Mise en forme conditionnelle, Nouvelle règle.
- Choisir le type Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Saisir
=$B2<$C2dans la zone Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie. - Cliquer sur Format, choisir la couleur dans l'onglet Remplissage, valider par OK deux fois.
$, la couleur se décale$ est l'erreur la plus fréquente, et elle ne produit aucun message. La formule =B2<C2 est juste pour la colonne A. Mais recopiée d'une colonne vers la droite, elle devient =C2<D2 en colonne B, =D2<E2 en colonne C, et ainsi de suite : chaque colonne compare deux autres colonnes, qui n'ont rien à voir avec le stock.La feuille ci-dessous porte cette règle fautive sur les mêmes données. Des cellules s'allument sur des lignes saines (le stock de l'huile de coupe, le seuil des vis) parce que le seuil y est plus petit que la quantité en commande, ou que rien n'est en commande. Seule la colonne A dit encore vrai.
Le symptôme se reconnaît d'un coup d'œil : des couleurs en damier au lieu de lignes entières. L'excès inverse,
=$B$2<$C$2, fige aussi la ligne : tout le tableau se colore, ou rien, d'après la seule première ligne.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Article | Stock | Seuil | En commande | Délai (jours) |
| 2 | Vis M6x20 | 1200 | 500 | 0 | 5 |
| 3 | Foret HSS 8 mm | 14 | 20 | 50 | 10 |
| 4 | Plaquette carbure | 35 | 30 | 0 | 15 |
| 5 | Gants anticoupure | 18 | 25 | 0 | 3 |
| 6 | Huile de coupe 5 L | 6 | 4 | 10 | 7 |
| 7 | Disque à tronçonner | 40 | 40 | 0 | 4 |
| 8 | Joint torique 40 mm | 90 | 100 | 200 | 12 |
| 9 |
- A2:E8 : formule =B2<C2 (10 cellules)
C6 contient la valeur « 4 ». Ce n'est pas une formule : rien ne se recalculera ici.
- Sélectionner la plage de la règle, puis onglet Accueil, Mise en forme conditionnelle, Gérer les règles.
- Sélectionner la règle, puis cliquer sur Modifier la règle.
- Dans la zone de formule, corriger en
=$B2<$C2: le$devant la colonne la fige, et la ligne reste libre de varier. - Valider par OK dans les deux boîtes de dialogue.
1.Un tableau porte deux règles : « valeur inférieure à 0 » en rouge, puis « valeur inférieure ou égale à 7 » en orange. Quelle couleur prend une cellule qui vaut -4 ?
2.Pour colorer toute la ligne d'un article dont le stock (colonne B) est sous le seuil (colonne C), on applique au tableau la formule…
3.Un tableau entièrement colorié par des règles conditionnelles…
Séparer les données du calcul
C'est le principe qui rend un classeur utilisable par quelqu'un d'autre, et il est le plus souvent ignoré.
Trois zones, et rien d'autre.
Les paramètres : les constantes du modèle, chacune dans sa cellule, avec une étiquette à côté. Taux de TVA, objectif, seuil d'alerte, prix unitaire.
Les données : le tableau brut, une ligne par enregistrement, une seule ligne d'en-tête, aucune formule.
Les résultats : les calculs, les synthèses, les graphiques, qui ne contiennent aucune valeur tapée à la main.
La règle qui découle des trois : aucun nombre n'est écrit dans une formule. Chaque constante vit dans la zone des paramètres, et les formules la désignent par une référence absolue.
Le bénéfice se mesure le jour où le taux change : une seule cellule à modifier, et tout suit.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | PARAMÈTRES | ||||
| 2 | Taux de TVA | 0,2 | |||
| 3 | Remise à partir de | 500 | |||
| 4 | Taux de remise | 0,05 | |||
| 5 | |||||
| 6 | Article | Prix HT | Quantité | Total HT | Total TTC |
| 7 | Clavier | 25,9 | 12 | 310,8 | 372,96 |
| 8 | Souris | 12,5 | 40 | 500 | 600 |
| 9 | Écran | 149 | 5 | 745 | 894 |
| 10 | Total | 1555,8 | 1866,96 | ||
| 11 | Remise | 77,79 | 93,35 | ||
| 12 | Net à payer | 1478,01 | 1773,61 | ||
| 13 |
E7 contient la formule =D7*(1+$B$2), et affiche 372,96. Les cellules qu'elle utilise sont teintées.
$B$2, $B$3 et $B$4 remplacent trois nombres qui auraient pu être tapés directement.La différence apparaît le jour où le taux de TVA change : ici, une cellule. Dans la version qui aurait écrit
*1,2 partout, il faudrait retrouver chaque formule concernée, sans savoir lesquelles contiennent ce 1,2 et lesquelles contiennent un autre nombre.Deux exceptions raisonnables : le
1 de (1+taux), qui n'est pas un paramètre mais une opération, et le 2 d'une division en deux qui ne changera jamais.Le test à se poser : « ce nombre peut-il changer un jour ? ». Si oui, il mérite sa cellule.
Le principe vaut aussi pour les règles de couleur. Une tolérance tapée dans une règle (>0,05) est un nombre caché de plus, et le plus difficile à retrouver de tous, puisqu'il n'apparaît dans aucune cellule. Une règle par formule peut désigner une cellule de paramètres comme n'importe quelle formule, avec une référence absolue.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | PARAMÈTRES | |||
| 2 | Cote nominale (mm) | 25 | ||
| 3 | Tolérance (mm) | 0,05 | ||
| 4 | ||||
| 5 | Pièce | Mesure (mm) | Écart (mm) | Verdict |
| 6 | P-01 | 25,02 | 0,02 | Conforme |
| 7 | P-02 | 24,97 | -0,03 | Conforme |
| 8 | P-03 | 25,06 | 0,06 | Rebut |
| 9 | P-04 | 25 | 0 | Conforme |
| 10 | P-05 | 24,93 | -0,07 | Rebut |
| 11 | P-06 | 25,04 | 0,04 | Conforme |
| 12 | P-07 | 25,01 | 0,01 | Conforme |
| 13 | P-08 | 24,99 | -0,01 | Conforme |
| 14 | Rebuts | 2 | ||
| 15 |
- A6:D13 : formule =ABS($C6)>$B$3 (8 cellules)
B3 contient la valeur « 0,05 ». Ce n'est pas une formule : rien ne se recalculera ici.
- Sélectionner la plage à colorer, ici A6:D13, puis onglet Accueil, Mise en forme conditionnelle, Nouvelle règle.
- Choisir le type Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Saisir
=ABS($C6)>$B$3dans la zone Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie. - Cliquer sur Format, choisir la couleur dans l'onglet Remplissage, valider par OK deux fois.
25,05 - 25 ne donne pas exactement 0,05 mais 0,05000000000000071, parce que l'ordinateur ne représente pas 25,05 exactement. Sans l'ARRONDI, une pièce pile à la limite serait déclarée hors tolérance.Arrondir l'écart à la résolution de l'instrument (ici le millième de millimètre) règle la question une fois pour toutes, et rend la comparaison à la tolérance fidèle à ce que le contrôleur a lu.
Protéger sans bloquer
Un classeur destiné à d'autres se casse toujours de la même façon : quelqu'un écrit une valeur par-dessus une formule, et la colonne cesse de se recalculer sans que personne ne s'en aperçoive.
La parade tient en deux étapes, dans cet ordre. Déverrouiller les cellules de saisie, par Ctrl + 1 puis l'onglet Protection. Puis protéger la feuille, par Révision → Protéger la feuille.
L'ordre compte : toutes les cellules sont verrouillées par défaut, mais ce verrou ne fait rien tant que la feuille n'est pas protégée. Protéger sans avoir déverrouillé les zones de saisie rend le fichier inutilisable.
Un mot de passe est facultatif, et le plus souvent inutile : la protection sert ici à éviter l'accident, pas à interdire l'accès.
1.Pourquoi ranger un taux de TVA dans une cellule dédiée ?
2.Dans quel ordre protéger une feuille ?
Les macros
Une macro enregistre une suite d'actions et les rejoue à la demande. Le code produit est du VBA, un langage de programmation, et il reste modifiable.
.xlsm, et beaucoup d'environnements bloquent ces fichiers par défaut, pour des raisons de sécurité qui sont fondées.La macro enregistrée est fragile. L'enregistreur mémorise les adresses parcourues, pas l'intention. Insérer une colonne, et la macro travaille sur la mauvaise.
Elle contourne l'annulation. Ce qu'une macro modifie ne se retire pas avec
Ctrl + Z.Avant d'en écrire une, vérifier qu'il n'existe pas déjà une fonction ou un tableau croisé qui fait la même chose. La grande majorité des macros de débutant réimplantent laborieusement
RECHERCHEV ou un tri.
Une macro enregistrée cesse de fonctionner après l'ajout d'une colonne. Pourquoi ?
Quand le tableur ne suffit plus
Le fichier dépasse quelques dizaines de milliers de lignes et devient lent à chaque frappe.
Les mêmes données sont recopiées dans plusieurs feuilles. Elles finiront par diverger, et personne ne saura laquelle fait foi.
Il faut savoir qui a modifié quoi et quand. Un tableur ne conserve aucun historique fiable.
Le même traitement se refait chaque semaine à l'identique. C'est un travail de programme, et deux parcours de ce site s'en occupent : Python pour l'automatiser, Bases de données pour ranger ce que le tableur ne sait plus tenir.
Le tableur reste excellent pour explorer, prototyper et communiquer. Il devient un handicap dès qu'il sert de système d'information.
Quel signal indique qu'il faut quitter le tableur ?
Mettre en pratique
Comment empêcher qu'une colonne de catégories contienne trois orthographes différentes ?
Une liste déroulante par validation de données.
Ranger les valeurs autorisées dans une colonne à part, idéalement sur une feuille de paramètres. Puis, sur la colonne de saisie, appliquer Données → Validation des données → Liste, en pointant cette plage.
Deux bénéfices d'un coup. La saisie devient impossible à rater, et ajouter une catégorie se fait en ajoutant une ligne à la liste, sans reconfigurer quoi que ce soit.
Sans cela, « informatique », « Informatique » et « info » forment trois catégories, tous les NB.SI comptent faux, et rien ne le signale.
Un tableau est entièrement colorié par des règles conditionnelles. Est-ce une bonne chose ?
Non, c'est l'effet inverse de celui qu'on cherche.
La mise en forme conditionnelle sert à faire ressortir ce qui demande une action. Quand la moitié des cellules est en couleur, plus rien ne ressort et l'œil ne sait plus où aller.
La règle pratique : une couleur pour une action. Rouge pour ce qui exige une intervention immédiate, orange pour ce qu'il faut surveiller, et rien du tout pour le reste.
Une bonne mise en forme conditionnelle est celle qu'on ne remarque pas les jours où tout va bien.
Une règle censée colorer les lignes en rupture de stock colore des cases éparses, en damier. Que s'est-il passé ?
Il manque le $ devant la lettre de colonne dans la formule de la règle.
Une règle par formule s'écrit pour la cellule en haut à gauche de la plage, puis se recopie sur toutes les autres. Écrite =B2<C2, elle devient =C2<D2 une colonne plus à droite, =D2<E2 la suivante : chaque colonne compare deux autres colonnes, et les couleurs tombent là où ces comparaisons se trouvent vraies par hasard.
La correction : =$B2<$C2. Le $ fige la colonne, le numéro de ligne reste libre, et toutes les cellules d'une ligne posent la même question sur le stock et le seuil de cette ligne.
Deux $ de part et d'autre, =$B$2<$C$2, seraient l'excès inverse : toute la plage obéirait à la première ligne.
Deux règles se chevauchent : rouge sous zéro jour, orange sous sept jours. Les retards apparaissent en orange. Pourquoi ?
Parce que la règle orange est placée au-dessus de la rouge.
Une valeur de -4 vérifie les deux conditions. Le tableur applique la première règle vérifiée dans l'ordre du gestionnaire de règles, et l'orange, plus large, réclame tout ce qui est inférieur ou égal à 7, retards compris. La règle rouge ne colore alors plus rien.
La correction se fait dans Mise en forme conditionnelle → Gérer les règles, avec les flèches de déplacement : la condition la plus grave en haut, la plus large en bas.
Pourquoi ranger le taux de TVA dans une cellule plutôt que l'écrire dans les formules ?
Pour trois raisons qui se cumulent.
Une seule modification le jour où il change, au lieu d'autant de corrections qu'il y a de formules.
La valeur est visible. Quelqu'un qui ouvre le fichier voit le taux employé, sans avoir à ouvrir une formule.
La feuille devient un modèle interrogeable : changer le taux répond immédiatement à « et si la TVA passait à 5,5 % ? ».
La question à se poser sur chaque nombre est simple : peut-il changer un jour ? Si oui, il mérite sa cellule, et les formules le désignent par une référence absolue.
Dans quel ordre déverrouiller et protéger une feuille ?
Déverrouiller d'abord les cellules de saisie, protéger la feuille ensuite.
Toutes les cellules sont verrouillées par défaut, mais ce verrou ne produit aucun effet tant que la feuille n'est pas protégée. Protéger directement rend donc le fichier entièrement inutilisable.
La marche à suivre : sélectionner les zones où l'on doit pouvoir écrire, Ctrl + 1, onglet Protection, décocher « Verrouillée ». Puis Révision → Protéger la feuille.
Le mot de passe est facultatif et le plus souvent inutile : il s'agit d'éviter l'accident, pas d'interdire l'accès.
Une macro enregistrée fonctionne, puis cesse de fonctionner un mois plus tard. Pourquoi ?
Parce que l'enregistreur a mémorisé des adresses, pas une intention.
Une macro enregistrée retient « aller en D2, écrire ceci, sélectionner D2:D50 ». Insérer une colonne, ajouter des lignes, renommer une feuille, et elle travaille au mauvais endroit.
Le plus gênant est qu'elle ne s'arrête pas forcément : elle fait quelque chose, simplement pas ce qu'il fallait, et Ctrl + Z ne défait rien de ce qu'une macro a modifié.
Deux parades. Rendre la macro robuste en modifiant son code pour qu'il vise des tableaux structurés plutôt que des adresses. Ou, plus simplement, vérifier qu'une fonction ou un tableau croisé ne fait pas déjà la même chose.
À partir de quand faut-il abandonner le tableur ?
Dès qu'un de ces cinq signaux apparaît.
Plusieurs personnes saisissent en même temps : c'est le travail d'une base de données, pas d'un fichier.
Le fichier devient lent, au-delà de quelques dizaines de milliers de lignes.
Les mêmes données sont recopiées dans plusieurs feuilles : elles divergeront.
Il faut un historique de qui a modifié quoi : un tableur n'en conserve pas de fiable.
Le même traitement se refait à l'identique chaque semaine : c'est un travail de programme.
Le tableur reste excellent pour explorer, prototyper et communiquer. Il devient un handicap le jour où il sert de système d'information.
Chapitre révisé en octobre 2026 · Une erreur dans ce chapitre ?
Dans la bibliothèque d'exercices
Le taux qui ne s'écrit qu'une fois, et l'ordre dans lequel on protège une feuille.
- Le jour où le taux changeNiveau 3
- Protéger une feuille dans le bon ordreNiveau 2
- Le planning des révisions piloté par deux paramètresNiveau 2
- Les camions partis à moitié videsNiveau 2
- Les instruments de mesure à retirer du serviceNiveau 3