Exceller

Guide de validation des données Excel

Excel Data Validation Guide

Formules de validation | Listes déroulantes dépendantes | Formules générales

introduction

La validation des données est une fonctionnalité d'Excel utilisée pour contrôler ce qu'un utilisateur peut entrer dans une cellule. Par exemple, vous pouvez utiliser la validation des données pour vous assurer qu'une valeur est un nombre compris entre 1 et 6, pour vous assurer qu'une date se situe dans les 30 prochains jours ou pour vous assurer qu'une entrée de texte comporte moins de 25 caractères.





La validation des données peut simplement afficher un message à un utilisateur lui indiquant ce qui est autorisé, comme indiqué ci-dessous :

Exemple de message de validation des données affiché lorsque la cellule est sélectionnée





La validation des données peut également arrêter les entrées utilisateur non valides. Par exemple, si un code produit échoue à la validation, vous pouvez afficher un message comme celui-ci :

alerte d'erreur de validation des données exemple de code produit non valide



De plus, la validation des données peut être utilisée pour présenter à l'utilisateur un choix prédéfini dans un menu déroulant :

Exemple de menu déroulant de validation des données

Cela peut être un moyen pratique de donner à un utilisateur exactement les valeurs qui répondent aux exigences.

Contrôles de validation des données

La validation des données est implémentée via des règles définies dans l'interface utilisateur d'Excel sur l'onglet Données du ruban.

formule pour combiner les noms dans Excel

Contrôles de validation des données sur l'onglet données du ruban

Limitation importante

Il est important de comprendre que la validation des données peut être facilement vaincue. Si un utilisateur copie des données d'une cellule sans validation vers une cellule avec validation de données, la validation est détruite (ou remplacée). La validation des données est un bon moyen de faire savoir aux utilisateurs ce qui est autorisé ou attendu, mais ce n'est pas un moyen infaillible de garantir la saisie.

Définition des règles de validation des données

La validation des données est définie dans une fenêtre avec 3 onglets : Paramètres, Message d'entrée et Alerte d'erreur :

La fenêtre de validation des données comporte trois onglets principaux

L'onglet Paramètres est l'endroit où vous entrez les critères de validation. Il existe un certain nombre de règles de validation intégrées avec diverses options, ou vous pouvez sélectionner Personnalisé et utiliser votre propre formule pour valider la saisie, comme indiqué ci-dessous :

Exemple d'onglet Paramètres de validation des données

L'onglet Message d'entrée définit un message à afficher lorsqu'une cellule avec des règles de validation est sélectionnée. Ce message d'entrée est complètement facultatif. Si aucun message d'entrée n'est défini, aucun message n'apparaît lorsqu'un utilisateur sélectionne une cellule avec la validation des données appliquée. Le message d'entrée n'a aucun effet sur ce que l'utilisateur peut entrer - il affiche simplement un message pour informer l'utilisateur de ce qui est autorisé ou attendu.

Onglet Paramètres de validation des données

L'onglet Alerte d'erreur contrôle la manière dont la validation est appliquée. Par exemple, lorsque le style est défini sur « Stop », les données non valides déclenchent une fenêtre avec un message et la saisie n'est pas autorisée.

Onglet Alerte d'erreur de validation des données

L'utilisateur voit un message comme celui-ci :

Exemple de message d'alerte d'erreur de validation des données

Lorsque le style est défini sur Information ou Avertissement, une icône différente s'affiche avec un message personnalisé, mais l'utilisateur peut ignorer le message et saisir des valeurs qui ne passent pas la validation. Le tableau ci-dessous résume le comportement de chaque option d'alerte d'erreur.

Style d'alerte Comportement
Arrêter Empêche les utilisateurs de saisir des données non valides dans une cellule. Les utilisateurs peuvent réessayer, mais doivent entrer une valeur qui réussit la validation des données. La fenêtre Arrêter l'alerte a deux options : Réessayer et Annuler.
Avertissement Avertit les utilisateurs que les données ne sont pas valides. L'avertissement ne fait rien pour arrêter les données invalides. La fenêtre d'alerte d'avertissement a trois options : Oui (pour accepter les données invalides), Non (pour modifier les données invalides) et Annuler (pour supprimer les données invalides).
Informations Informe les utilisateurs que les données ne sont pas valides. Ce message ne fait rien pour arrêter les données invalides. La fenêtre d'alerte d'information a 2 options : OK pour accepter les données invalides et Annuler pour les supprimer.

Options de validation des données

Lorsqu'une règle de validation des données est créée, huit options sont disponibles pour valider la saisie de l'utilisateur :

De n'importe quelle valeur - aucune validation n'est effectuée. Remarque : si la validation des données a été précédemment appliquée avec un message d'entrée défini, le message s'affichera toujours lorsque la cellule est sélectionnée, même lorsque n'importe quelle valeur est sélectionnée.

Nombre entier - seuls les nombres entiers sont autorisés. Une fois l'option de nombre entier sélectionnée, d'autres options deviennent disponibles pour limiter davantage la saisie. Par exemple, vous pouvez exiger un nombre entier compris entre 1 et 10.

Décimal - fonctionne comme l'option nombre entier, mais autorise les valeurs décimales. Par exemple, avec l'option Decimal configurée pour autoriser des valeurs comprises entre 0 et 3, des valeurs telles que 0,5, 2,5 et 3,1 sont toutes autorisées.

Lister - seules les valeurs d'une liste prédéfinie sont autorisées. Les valeurs sont présentées à l'utilisateur sous la forme d'un contrôle de menu déroulant. Les valeurs autorisées peuvent être codées en dur directement dans l'onglet Paramètres ou spécifiées sous forme de plage sur la feuille de calcul.

Date - seules les dates sont autorisées. Par exemple, vous pouvez exiger une date comprise entre le 1er janvier 2018 et le 31 décembre 2021, ou une date postérieure au 1er juin 2018.

Temps - seules les heures sont autorisées. Par exemple, vous pouvez exiger une heure entre 9h00 et 17h00, ou autoriser uniquement des heures après 12h00.

Longueur du texte - valide la saisie en fonction du nombre de caractères ou de chiffres. Par exemple, vous pourriez exiger un code contenant 5 chiffres.

Personnalisé - valide la saisie de l'utilisateur à l'aide d'une formule personnalisée. En d'autres termes, vous pouvez écrire votre propre formule pour valider la saisie. Les formules personnalisées étendent considérablement les options de validation des données. Par exemple, vous pouvez utiliser une formule pour vous assurer qu'une valeur est en majuscule, qu'une valeur contient « xyz » ou qu'une date est un jour de semaine dans les 45 prochains jours.

L'onglet Paramètres comprend également deux cases à cocher :

Ignorer le blanc - indique à Excel de ne pas valider les cellules qui ne contiennent aucune valeur. En pratique, ce paramètre semble n'affecter que la commande 'circle invalid data'. Lorsqu'elles sont activées, les cellules vides ne sont pas encerclées même si elles échouent à la validation.

Appliquer ces modifications à d'autres cellules avec les mêmes paramètres - ce paramètre mettra à jour la validation appliquée aux autres cellules lorsqu'elle correspond à la validation (originale) de la ou des cellules en cours d'édition.

comment rechercher quelque chose dans excel

Remarque : Vous pouvez également sélectionner manuellement toutes les cellules avec la validation des données appliquée à l'aide de Go To + Special, comme expliqué ci-dessous.

Menu déroulant simple

Vous pouvez fournir un menu déroulant d'options en codant en dur les valeurs dans la zone de paramètres ou en sélectionnant une plage sur la feuille de calcul. Par exemple, pour restreindre les entrées aux actions « ACHETER », « TENIR » ou « VENDRE », vous pouvez entrer ces valeurs séparées par des virgules comme indiqué ci-dessous :

Menu déroulant de validation des données avec des valeurs codées en dur

Lorsqu'il est appliqué à une cellule de la feuille de calcul, le menu déroulant fonctionne comme ceci :

Valeurs codées en dur du menu déroulant de validation des données en cours d'utilisation

Une autre façon de fournir des valeurs à un menu déroulant consiste à utiliser une référence de feuille de calcul. Par exemple, avec des tailles (c'est-à-dire petites, moyennes, etc.) dans la plage F3:F6, vous pouvez fournir cette plage directement dans la fenêtre des paramètres de validation des données :

Valeurs du menu déroulant de validation des données avec référence de la feuille de calcul

Notez que la plage est entrée en tant que adresse absolue pour l'empêcher de changer lorsque la validation des données est appliquée à d'autres cellules.

Astuce : cliquez sur la petite icône en forme de flèche à l'extrême droite du champ source pour effectuer une sélection directement sur la feuille de calcul afin de ne pas avoir à saisir la plage manuellement.

Vous pouvez aussi utiliser plages nommées pour spécifier des valeurs. Par exemple, avec la plage nommée appelée 'tailles' pour F3:F7, vous pouvez saisir le nom directement dans la fenêtre, en commençant par un signe égal :

Valeurs du menu déroulant de validation des données avec plage nommée

Plages nommées sont automatiquement absolus, ils ne changeront donc pas lorsque la validation des données est appliquée à différentes cellules. Si les plages nommées sont nouvelles pour vous, cette page a un bon aperçu et un certain nombre de conseils connexes .

Vous pouvez également créer des listes déroulantes dépendantes avec une formule personnalisée.

Astuce - si vous utilisez un tableau pour les valeurs déroulantes, Excel continuera à développer ou à réduire le tableau automatiquement lorsque des valeurs déroulantes sont ajoutées ou supprimées. En d'autres termes, Excel gardera automatiquement la liste déroulante synchronisée avec les valeurs du tableau à mesure que les valeurs sont modifiées, ajoutées ou supprimées. Si vous débutez avec les tableaux Excel, vous pouvez voir un démo dans cette vidéo sur les raccourcis de table.

Validation des données avec une formule personnalisée

Les formules de validation des données doivent être des formules logiques qui renvoient VRAI lorsque l'entrée est valide et FAUX lorsque l'entrée est invalide. Par exemple, pour autoriser n'importe quel nombre en entrée dans la cellule A1, vous pouvez utiliser la fonction ISNUMBER dans une formule comme celle-ci :

 
= ISNUMBER (A1)

Si un utilisateur entre une valeur comme 10 dans A1, ISNUMBER renvoie TRUE et la validation des données réussit. S'ils entrent une valeur comme 'apple' dans A1, ISNUMBER renvoie FALSE et la validation des données échoue.

Pour activer la validation des données avec une formule, sélectionnez « Personnalisé » dans l'onglet Paramètres, puis entrez une formule dans la barre de formule en commençant par un signe égal (=) comme d'habitude.

Formules de dépannage

Excel ignore les formules de validation des données qui renvoient des erreurs. Si une formule ne fonctionne pas et que vous ne pouvez pas comprendre pourquoi, configurez des formules fictives pour vous assurer que la formule fonctionne comme prévu. Les formules factices sont simplement des formules de validation de données saisies directement sur la feuille de calcul afin que vous puissiez voir facilement ce qu'elles renvoient. L'écran ci-dessous montre un exemple :

Tester la validation des données avec des formules fictives

Une fois que la formule factice fonctionne comme vous le souhaitez, copiez-la simplement et collez-la dans la zone de formule de validation des données.

Si cette idée de formule fictive vous perturbe, regarde cette video , qui montre comment utiliser des formules factices pour perfectionner les formules de mise en forme conditionnelle. Le concept est exactement le même.

Exemples de formules de validation des données

Les possibilités de validation des données des formules personnalisées sont pratiquement illimitées. Voici quelques exemples pour vous inspirer :

Pour n'autoriser que 5 valeurs de caractères commençant par « z », vous pouvez utiliser :

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Cette formule renvoie VRAI uniquement lorsqu'un code comporte 5 chiffres et commence par « z ». Les deux valeurs encerclées renvoient FALSE avec cette formule.

Pour autoriser uniquement une date dans les 30 jours à compter d'aujourd'hui :

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Pour autoriser uniquement les valeurs uniques :

 
= COUNTIF (range,A1)<2

Pour autoriser uniquement une adresse e-mail

 
=ISUMBER( FIND ('@',A1)
Cliquez pour plus d'exemples de formules et des explications détaillées

Validation des données pour encercler les entrées invalides

Une fois la validation des données appliquée, vous pouvez demander à Excel d'encercler les valeurs invalides précédemment saisies. Dans l'onglet Données du ruban, cliquez sur Validation des données et sélectionnez « Encercler les données non valides » :

Entourer les valeurs invalides avec validation des données - menu

Par exemple, l'écran ci-dessous affiche les valeurs encerclées qui échouent à la validation avec cette formule personnalisée :

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Validation des données valeurs invalides encerclées sur la feuille de calcul

Rechercher des cellules avec validation des données

Pour rechercher des cellules avec la validation des données appliquée, vous pouvez utiliser la boîte de dialogue Aller à > Spécial. Tapez le raccourci clavier Ctrl + G, puis cliquez sur le bouton Spécial. Lorsque la boîte de dialogue apparaît, sélectionnez « Validation des données » :

Aller au bouton spécial

charger le complément du solveur

Sélectionnez la validation des données avec aller à la boîte de dialogue spéciale

Copier la validation des données d'une cellule à une autre

Pour copier la validation d'une cellule vers d'autres cellules. Copiez la ou les cellules qui contiennent normalement la validation des données souhaitée, puis utilisez Collage spécial + Validation. Une fois la boîte de dialogue affichée, tapez 'n' pour sélectionner la validation, ou cliquez sur validation avec la souris.

Utilisation de pâte spéciale pour copier la validation des données

Remarque : vous pouvez utiliser le raccourci clavier Ctrl + Alt + V pour appeler Collage spécial sans la souris.

Effacer toutes les validations de données

Pour effacer toutes les validations de données d'une plage de cellules, effectuez la sélection, puis cliquez sur le bouton Validation des données dans l'onglet Données du ruban. Cliquez ensuite sur le bouton « Effacer tout » :

Utilisez le bouton Effacer tout pour supprimer les données validationimg/excel/59/excel-data-validation-guide-16.png

Pour effacer toutes les validations de données d'une feuille de calcul, sélectionnez la feuille de calcul entière, puis suivez les mêmes étapes ci-dessus.

Auteur Dave Bruns


^