Limiter les saisies avec la validation des données

La validation des données définit les règles concernant ce qu’une cellule accepte. Au lieu de faire confiance aux utilisateurs pour saisir la bonne valeur, le tableur impose ces règles.

Vous pouvez exiger qu’une cellule contienne uniquement des nombres entiers, uniquement des dates comprises dans une période ou uniquement des valeurs provenant d’une liste approuvée.

• Repère les fautes de frappe avant qu’elles ne contaminent vos données.
• Guide les utilisateurs grâce à des indications et des messages d’avertissement.
• Fonctionne dans Excel et Google Sheets.

Cette fonctionnalité se trouve à des emplacements légèrement différents selon le programme.

• Excel : sélectionnez les cellules, ouvrez l’onglet Données, puis cliquez sur Validation des données.
• Google Sheets : sélectionnez les cellules, puis choisissez Données > Validation des données et cliquez sur Ajouter une règle.

Les deux programmes ouvrent un panneau dans lequel vous choisissez le type de règle, définissez ses limites et décidez de ce qui se passe lorsqu’une personne saisit des données non valides.

Une règle courante limite une cellule aux nombres. Dans la boîte de dialogue d’Excel, choisissez Nombre entier ou Décimal, puis définissez des conditions comme entre 1 et 100.

Si quelqu’un saisit abc ou 150, la saisie est alors refusée.

• Utilisez Nombre entier pour les décomptes et les quantités.
• Utilisez Décimal pour les prix ou les pourcentages.
• Les opérateurs comprennent entre, supérieur à et égal à.

Vous pouvez également limiter une cellule à des dates valides dans une période donnée. Choisissez la règle Date et définissez, par exemple, des dates entre aujourd’hui et la fin de l’année.

C’est idéal pour les feuilles de réservation ou les échéances, lorsque les dates passées n’ont aucun sens.

• Rejetez les dates antérieures à =TODAY() pour empêcher l’antidatage.
• Combinez une date de début et une date de fin pour imposer une période de projet.

Le tableur vérifie automatiquement chaque saisie par rapport à la règle.

La validation peut aussi encadrer le texte. Excel propose une règle Longueur du texte, utile pour des champs tels qu’un code pays à deux lettres ou un ID de longueur fixe.

• Exigez une longueur égale à 2 pour les codes d’État.
• Exigez une longueur inférieure ou égale à 50 pour les notes courtes.

Tout ce qui dépasse la limite est refusé, ce qui uniformise vos colonnes et rend prévisibles vos formules en aval.

Pour tout ce que les règles intégrées ne permettent pas d’exprimer, utilisez une règle Personnalisée (Excel) ou La formule personnalisée est (Sheets). Vous écrivez une formule qui doit renvoyer TRUE pour que la saisie soit autorisée.

Par exemple, pour imposer du texte en majuscules dans la cellule A2 :

=EXACT(A2,UPPER(A2))

Si la valeur saisie n’est pas entièrement en majuscules, la formule renvoie FALSE et la saisie est bloquée.

Une règle personnalisée très utile empêche les doublons dans une colonne. Sélectionnez A2:A100 et utilisez une formule personnalisée :

=COUNTIF($A$2:$A$100,A2)=1

Cela signifie que la valeur saisie doit apparaître une seule fois dans la plage. Si elle existe déjà, le nombre d’occurrences est 2 et la règle échoue.

• Idéal pour les ID uniques, les adresses e-mail ou les numéros de facture.
• Observez la plage absolue et la référence relative A2.

Excel vous permet d’afficher un Message de saisie — une petite info-bulle qui apparaît lorsque la cellule est sélectionnée, avant toute saisie.

Utilisez-le pour expliquer la règle en termes simples, par exemple Saisissez un nombre entier de 1 à 100.

• Guide les utilisateurs de manière proactive.
• Réduit les saisies refusées et la frustration.

Google Sheets affiche une aide similaire grâce au texte d’aide à la validation associé à la règle.

Lorsque des données enfreignent une règle, l’onglet Alerte d’erreur d’Excel détermine le niveau de rigueur :

• Arrêt — rejette complètement la saisie.
• Avertissement — la signale, mais permet à l’utilisateur de continuer.
• Information — informe simplement l’utilisateur.

Google Sheets propose un choix similaire : Rejeter la saisie ou Afficher un avertissement. Choisissez Arrêt lorsque les mauvaises données sont inacceptables, et Avertissement lorsqu’une décision au cas par cas est permise.

Les règles peuvent être vérifiées ou supprimées à tout moment.

• Dans Excel, sélectionnez les cellules et rouvrez Validation des données ; cliquez sur Effacer tout pour supprimer la règle.
• L’outil Entourer les données non valides d’Excel met en évidence les valeurs existantes qui enfreignent une règle nouvellement ajoutée.
• Dans Sheets, ouvrez Validation des données et supprimez la règle depuis le panneau.

N’oubliez pas que la validation ne vérifie que les saisies nouvelles, sauf si vous contrôlez les anciennes.

Un autre type de règle mérite d’être mentionné, car il prépare la leçon suivante : la règle Liste.

Au lieu d’accepter n’importe quel nombre ou n’importe quelle date, une règle Liste limite une cellule à un ensemble de choix précis, comme Open, Closed ou Pending.

• C’est le fondement d’un menu déroulant.
• Vous pouvez saisir les choix ou pointer vers une plage de cellules.

Vous transformerez cette liste en une liste déroulante soignée dans la prochaine leçon.

Vous voulez qu’une colonne refuse une valeur qui existe déjà ailleurs dans la colonne. Quelle formule de validation personnalisée permet de faire cela pour la cellule A2 dans la plage A2:A100 ?

Vous avez appris à contrôler les saisies avec la validation des données :

• Ouvrez-la depuis l’onglet Données (Excel) ou avec Données > Validation des données (Sheets).
• Limitez les saisies aux nombres entiers, aux nombres décimaux, aux dates ou à une longueur de texte.
• Écrivez des formules personnalisées comme =COUNTIF($A$2:$A$100,A2)=1 pour les règles avancées.
• Ajoutez des messages de saisie et choisissez Arrêt ou Avertissement pour les erreurs.

Ensuite, vous créerez des listes déroulantes conviviales alimentées par une plage nommée.