Pour aller plus loin
Ce que ce chapitre apporte
- 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.
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.
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.
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é.
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.
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.
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.
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 les modules Python et Bases de données de ce site s'en occupent.
Le tableur reste excellent pour explorer, prototyper et communiquer. Il devient un handicap dès qu'il sert de système d'information.
Exercices type
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.
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.
1.Comment garantir qu'une colonne de catégories ne contienne que des valeurs prévues ?
2.Un tableau entièrement colorié par des règles conditionnelles…
3.Pourquoi ranger un taux de TVA dans une cellule dédiée ?
4.Dans quel ordre protéger une feuille ?
5.Une macro enregistrée cesse de fonctionner après l'ajout d'une colonne. Pourquoi ?
6.Quel signal indique qu'il faut quitter le tableur ?
La méthode
- Ranger le classeur en trois zones avant d'écrire la première formule : paramètres, données, résultats.
- Poser chaque constante dans sa cellule, avec son étiquette dans la colonne d'à côté, et la désigner partout par une référence absolue.
- Mettre les valeurs autorisées d'une colonne dans une plage à part, puis Données → Validation des données → Liste, en pointant cette plage.
- Limiter la mise en forme conditionnelle à ce qui demande une action : une règle, une couleur, et rien pour le reste.
- Déverrouiller les cellules de saisie avant de protéger : les sélectionner,
Ctrl + 1, onglet Protection, décocher « Verrouillée ». Puis Révision → Protéger la feuille. - Tester le classeur en saisissant comme le fera quelqu'un d'autre : une valeur hors liste doit être refusée, une formule doit être impossible à écraser.
- Avant d'enregistrer une macro, chercher la fonction ou le tableau croisé qui fait déjà la même chose.
- Passer en revue les cinq signaux de sortie : plusieurs saisisseurs, lenteur, données recopiées, historique à tenir, traitement répété à l'identique. Un seul suffit à changer d'outil.
Synthèse
- La validation de données empêche les saisies fausses au lieu de les corriger après.
- Une liste déroulante pointe sur une plage à part, ce qui la rend extensible.
- La mise en forme conditionnelle ne sert que si elle reste rare : une couleur, une action.
- Un classeur utilisable se range en trois zones : paramètres, données, résultats.
- Aucun nombre susceptible de changer ne s'écrit dans une formule.
- Protéger une feuille demande de déverrouiller d'abord les cellules de saisie.
- Une macro enregistrée mémorise des adresses, pas une intention, et contourne l'annulation.
- Le tableur cesse d'être le bon outil dès qu'il y a plusieurs saisisseurs, un historique à tenir, ou un traitement répété.