Passer au contenu principal

Validation des données Excel : ajouter, utiliser, copier et supprimer la validation des données dans Excel

Dans Excel, la validation des données est une fonctionnalité utile que vous pouvez utiliser pour limiter ce qu'un utilisateur peut entrer dans une cellule. Par exemple, la fonction de validation des données peut vous aider à limiter la longueur des chaînes de texte, ou du texte commençant/se terminant par des caractères spécifiques, ou des valeurs uniques à saisir, etc.

Ce didacticiel, nous expliquerons comment ajouter, utiliser et supprimer la validation des données dans Excel, certaines opérations de base et avancées de cette fonctionnalité seront également expliquées en détail.

Table des matières:

1. Qu'est-ce que la validation des données dans Excel ?

2. Comment ajouter la validation des données dans Excel ?

3. Exemples de base pour la validation des données

4. Règles personnalisées avancées pour la validation des données

5. Comment éditer la validation des données dans Excel ?

6. Comment rechercher et sélectionner des cellules avec validation des données dans Excel ?

7. Comment copier la règle de validation des données dans d'autres cellules ?

8. Comment utiliser la validation des données pour encercler les entrées invalides dans Excel ?

9. Comment supprimer la validation des données dans Excel ?


1. Qu'est-ce que la validation des données dans Excel ?

La Validation des données La fonctionnalité peut vous aider à restreindre le contenu d'entrée dans votre feuille de calcul. Normalement, vous pouvez créer des règles de validation pour empêcher ou autoriser uniquement la saisie de certains types de données dans une liste de cellules sélectionnées.

Quelques utilisations de base de la fonctionnalité de validation des données :

  • 1. N'importe quelle valeur: aucune validation n'est effectuée, vous pouvez saisir n'importe quoi dans les cellules spécifiées.
  • 2. Valeur entière: seuls les nombres entiers sont autorisés.
  • 3. Décimal: permet de saisir des nombres entiers ainsi que des décimales.
  • 4. Liste: seules les valeurs de la liste prédéfinie peuvent être saisies ou sélectionnées. Les valeurs sont affichées dans une liste déroulante.
  • 5. Rendez-vous amoureux: seules les dates sont autorisées.
  • 6. temps: seuls les horaires sont autorisés.
  • 7. Longueur du texte: autorise uniquement la saisie de la longueur spécifiée du texte.
  • 8. Douane: créez des règles de formule personnalisées pour valider les entrées des utilisateurs.

2. Comment ajouter la validation des données dans Excel ?

Dans la feuille de calcul Excel, vous pouvez ajouter la validation des données en procédant comme suit :

1. Sélectionnez une liste de cellules dans lesquelles vous souhaitez définir la validation des données, puis cliquez sur Données > Validation des données > Validation des données, voir capture d'écran:

2. Dans le Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez créer vos propres règles de validation. dans les zones de critères, vous pouvez fournir l'un des types suivants :

  • Valeurs: saisissez directement les nombres dans les cases des critères ;
  • Référence de cellule: référence une cellule de la feuille de calcul ou d'une autre feuille de calcul ;
  • Formules: créez des formules plus complexes en tant que conditions.

À titre d'exemple, je vais créer une règle qui autorise uniquement la saisie de nombres entiers entre 100 et 1000, définissez ici les critères comme illustré ci-dessous :

3. Après avoir configuré les conditions, vous pouvez accéder à la Message de saisie or Alerte d'erreur onglet pour définir le message d'entrée ou l'alerte d'erreur pour les cellules de validation comme vous le souhaitez. (Si vous ne souhaitez pas définir l'alerte, veuillez cliquer sur OK pour finir directement.)

3.1) Ajouter un message d'entrée (facultatif) :

Vous pouvez créer un message qui apparaît lors de la sélection d'une cellule contenant la validation des données. Ce message aide à rappeler à l'utilisateur ce qu'il peut saisir dans la cellule.

Allez à Message de saisie onglet et procédez comme suit :

  • Vérifiez la Afficher le message d'entrée lorsque la cellule est sélectionnée l'option;
  • Entrez le titre et le message de rappel que vous souhaitez dans les champs correspondants ;
  • Cliquez OK pour fermer cette boîte de dialogue.

Désormais, lorsque vous sélectionnez une cellule validée, une boîte de message s'affichera comme suit :

3.2) Créez des messages d'erreur significatifs (facultatif) :

En plus de créer le message d'entrée, vous pouvez également afficher des alertes d'erreur lorsque des données non valides sont entrées dans une cellule avec validation des données.

Allez à Alerte d'erreur onglet du Validation des données boîte de dialogue, procédez comme suit :

  • Vérifiez la Afficher une alerte d'erreur après la saisie de données non valides l'option;
  • Dans le Style liste déroulante, sélectionnez le type d'alerte dont vous avez besoin :
    • Arrêter (par défaut): ce type d'alerte empêche les utilisateurs de saisir des données non valides.
    • : Avertit les utilisateurs que les données sont invalides, mais n'empêche pas de les saisir.
    • Informations: Informe les utilisateurs d'une entrée de données invalide uniquement.
  • Saisissez le titre et le message d'alerte souhaités dans les champs correspondants ;
  • Cliquez OK pour fermer la boîte de dialogue.

Et maintenant, lors de la saisie d'une valeur non valide, la boîte d'alerte de message apparaîtra comme illustré ci-dessous :

Arrêter option : vous pouvez cliquer sur Refaire pour saisir une autre valeur ou Annuler pour supprimer l'entrée.

option : Cliquez sur Oui pour saisir l'entrée invalide, Non pour le modifier, ou Annuler pour supprimer l'entrée.

Informations option : Cliquez sur OK pour saisir l'entrée invalide ou Annuler pour supprimer l'entrée.

Notes: si vous ne définissez pas votre propre message personnalisé dans le Alerte d'erreur boîte, une valeur par défaut Arrêter La boîte d'invite d'alerte s'affichera comme ci-dessous :


3. Exemples de base pour la validation des données

Lorsque vous utilisez cette fonctionnalité de validation des données, 8 options intégrées vous permettent de définir la validation des données. Tels que : toute valeur, nombres entiers et décimaux, date et heure, liste, longueur de texte et formule personnalisée. Dans cette section, nous verrons comment utiliser certaines des options intégrées dans Excel ?

3.1 Validation des données pour les nombres entiers et les nombres décimaux

1. Sélectionnez une liste de cellules dans laquelle vous souhaitez autoriser uniquement les nombres entiers ou les nombres décimaux, puis cliquez sur Données > Validation des données > Validation des données.

2. Dans le Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez l'élément correspondant Nombre entier or Décimal dans l' Autoriser boîte déroulante.
  • Et puis, choisissez l'un des critères dont vous avez besoin dans le Données box (dans cet exemple, je choisis jusqu'à XNUMX fois option).
  • Pourboires: Les critères contiennent : entre, pas entre, égal à, différent de, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
  • Ensuite, entrez le Minimum et Maximum valeurs dont vous avez besoin (je veux les nombres entre 0 et 1).
  • Enfin, cliquez OK .

3. Désormais, seuls les nombres entiers de 0 à 100 peuvent être saisis dans vos cellules sélectionnées.


3.2 Validation des données pour la date et l'heure

Pour valider une date ou une heure précise à saisir, il est facile en utilisant ce Validation des données, procédez comme suit:

1. Sélectionnez une liste de cellules où vous souhaitez autoriser uniquement les dates ou heures spécifiques, puis cliquez sur Données > Validation des données > Validation des données.

2. Dans le Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez l'élément correspondant Date or Time Freak dans l' Autoriser boîte déroulante.
  • Et puis, choisissez l'un des critères dont vous avez besoin dans le Données boîte (Ici je choisis plus grand que option).
  • Pourboires: Les critères contiennent : entre, pas entre, égal à, différent de, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
  • Ensuite, entrez le Date de début vous avez besoin (je veux les dates supérieures au 8/20/2021).
  • Enfin, cliquez OK .

3. Désormais, seules les dates supérieures au 8/20/2021 peuvent être saisies dans vos cellules sélectionnées.


3.3 Validation des données pour la longueur du texte

Si vous devez limiter le nombre de caractères pouvant être saisis dans une cellule. Par exemple, pour limiter le contenu à 10 caractères maximum pour une plage particulière, cette Validation des données peut aussi vous rendre service.

1. Sélectionnez une liste de cellules dans lesquelles vous souhaitez limiter la longueur du texte, puis cliquez sur Données > Validation des données > Validation des données.

2. Dans le Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Longueur du texte du Autoriser boîte déroulante.
  • Et puis, choisissez l'un des critères dont vous avez besoin dans le Données box (dans cet exemple, je choisis moins que option).
  • Pourboires: Les critères contiennent : entre, pas entre, égal à, différent de, supérieur à, inférieur à, supérieur ou égal à, inférieur ou égal à.
  • Ensuite, entrez le Maximum nombre que vous devez limiter (je veux que la longueur du texte ne dépasse pas 10 caractères).
  • Enfin, cliquez OK .

3. Désormais, les cellules sélectionnées ne permettent de saisir que la chaîne de texte de moins de 10 caractères.


3.4 Liste de validation des données (liste déroulante)

Avec ce puissant Validation des données fonctionnalité, vous pouvez également créer une liste déroulante dans les cellules rapidement et facilement. Merci de faire comme ceci :

1. Sélectionnez les cellules cibles dans lesquelles insérer la liste déroulante, puis cliquez sur Données > Validation des données > Validation des données.

2. Dans le Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Liste du Autoriser la liste déroulante.
  • Dans le Identifier zone de texte, saisissez les éléments de la liste directement séparés par des virgules. Par exemple, pour limiter la saisie de l'utilisateur à trois choix, tapez Non démarré, En cours, Terminé, ou vous pouvez sélectionner une liste de cellules contenant les valeurs sur lesquelles insérer la liste déroulante.
  • Enfin, cliquez OK .

3. Maintenant, la liste déroulante a été créée dans les cellules comme illustré ci-dessous :

Cliquez pour connaître les informations plus détaillées de la liste déroulante…


4. Règles personnalisées avancées pour la validation des données

Dans cette section, je vais vous présenter comment créer des règles de validation de données personnalisées avancées pour résoudre vos types de problèmes, tels que : créer des formules de validation pour autoriser uniquement des nombres ou des chaînes de texte, uniquement des valeurs uniques, uniquement des numéros de téléphone spécifiés, des adresses e-mail, etc. .

4.1 La validation des données autorise uniquement les chiffres ou les textes

 Autoriser uniquement la saisie de chiffres avec la fonction de validation des données

Pour autoriser uniquement les nombres dans une plage de cellules, procédez comme suit :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez que seuls des nombres soient saisis.

2Cliquez sur Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez cette formule : =ESTNUM(A2) into the Laits en poudre zone de texte. (A2 est la première cellule de la plage sélectionnée que vous souhaitez limiter)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

3. Désormais, seuls des nombres peuvent être saisis dans les cellules sélectionnées.

Notes: Ce ISNUMBER La fonction autorise toutes les valeurs numériques dans les cellules validées, y compris les nombres entiers, les décimales, les fractions, les dates et les heures.


 Autoriser uniquement la saisie de chaînes de texte avec la fonction de validation des données

Pour restreindre les entrées de cellule au texte uniquement, vous pouvez utiliser le Validation des données fonctionnalité avec une formule personnalisée basée sur le ISTEXT fonction, s'il vous plaît faites comme ceci:

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez saisir uniquement des chaînes de texte.

2Cliquez sur Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez cette formule : =ISTEXT(A2) into the Laits en poudre zone de texte. (A2 est la première cellule de la plage sélectionnée que vous souhaitez limiter)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

3. Désormais, lors de la saisie de données dans des cellules spécifiques, seules les données au format texte peuvent être autorisées.


4.2 La validation des données n'autorise que les valeurs alphanumériques

À certaines fins, vous souhaitez simplement autoriser la saisie d'alphabets et de valeurs numériques, mais restreindre les caractères spéciaux tels que ~,%,$, espace, etc., cette section vous présentera quelques astuces.

 Autoriser uniquement les valeurs alphanumériques avec la fonction de validation des données

Pour empêcher les caractères spéciaux mais n'autoriser que les valeurs alphanumériques, vous devez créer une formule personnalisée dans le Validation des données fonction, procédez comme suit:

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez saisir uniquement des valeurs alphanumériques.

2Cliquez sur Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée que vous souhaitez limiter.

3. Désormais, seuls les alphabets et les valeurs numériques peuvent être saisis, et les caractères spéciaux seront restreints lors de la saisie, comme illustré ci-dessous :


 Autoriser uniquement les valeurs alphanumériques avec une fonctionnalité étonnante

Peut-être que la formule ci-dessus est compliquée à comprendre et à retenir, ici, je vais vous présenter une fonctionnalité pratique - Empêcher la frappe of Kutools for Excel, avec cette fonctionnalité, vous pouvez rapidement résoudre ce travail avec facilité.

Après l'installation de Kutools for Excel, veuillez faire comme ceci:

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez saisir uniquement des valeurs alphanumériques.

2. Puis clique Kutools > Empêcher la frappe > Empêcher la frappe, voir capture d'écran:

3. Dans le sauté Empêcher la frappe boîte de dialogue, sélectionnez Empêcher la saisie de caractères spéciaux option, voir capture d'écran:

4. Puis clique Ok et dans les boîtes de dialogue suivantes, cliquez sur Oui > OK pour terminer l'opération. Maintenant, dans les cellules sélectionnées, seuls les alphabets et les valeurs numériques sont autorisés, voir capture d'écran :


4.3 La validation des données permet aux textes de commencer ou de se terminer par des caractères spécifiques

Si toutes les valeurs d'une certaine plage doivent commencer ou se terminer par un caractère ou une sous-chaîne particulier, vous pouvez utiliser la validation des données avec une formule personnalisée basée sur la fonction EXACT, LEFT, RIGHT ou COUNTIF.

 Autoriser les textes à commencer ou à se terminer par des caractères spécifiques avec une seule condition

Par exemple, je veux que les textes commencent ou se terminent par "CN" lors de la saisie des chaînes de texte dans des cellules spécifiques, procédez comme suit :

1. Sélectionnez une plage de cellules qui n'autorise que les textes qui commencent ou se terminent par certains caractères.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • Begin with: =EXACT(LEFT(A2,2),"CN")
    End with: =EXACT(RIGHT(A2,2),"CN")
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée, le nombre 2 est le nombre de caractères que vous avez spécifié, CN est le texte par lequel vous voulez commencer ou terminer.

3. Désormais, seule la chaîne de texte commence ou se termine par les caractères spécifiés peut être saisie dans les cellules sélectionnées. Sinon, une alerte d'avertissement apparaîtra pour vous le rappeler, comme illustré ci-dessous :

Conseils: Les formules ci-dessus sont sensibles à la casse, si vous n'en avez pas besoin, veuillez appliquer les formules CONTIF ci-dessous :

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

Notes: L'astérisque * est un caractère générique qui correspond à un ou plusieurs caractères.


 Autoriser les textes à commencer ou à se terminer par des caractères spécifiques avec plusieurs critères (logique OU)

Par exemple, si vous souhaitez que les textes commencent ou se terminent par "CN" ou "UK" comme illustré ci-dessous, vous devez ajouter une autre instance de EXACT en utilisant un signe plus (+). Veuillez procéder avec les étapes suivantes :

1. Sélectionnez une plage de cellules qui n'autorise que les textes qui commencent ou se terminent par plusieurs critères.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
    End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée, le nombre 2 est le nombre de caractères que vous avez spécifié, CN et UK sont les textes spécifiques avec lesquels vous voulez commencer ou terminer.

3. Désormais, seule la chaîne de texte commence ou se termine par les caractères spécifiés peut être saisie dans les cellules sélectionnées.

Conseils: Pour ignorer la casse, veuillez appliquer les formules CONTIF ci-dessous :

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Notes: L'astérisque * est un caractère générique qui correspond à un ou plusieurs caractères.


4.4 Les entrées autorisées pour la validation des données doivent contenir / ne doivent pas contenir de texte spécifique

Cette section, je vais parler de la façon d'appliquer la validation des données pour autoriser les valeurs doivent contenir ou ne doivent pas contenir une sous-chaîne spécifique ou l'une des nombreuses sous-chaînes dans Excel.

 Les entrées autorisées doivent contenir un ou un des nombreux textes spécifiques

Les entrées autorisées doivent contenir un texte spécifique

Pour autoriser les entrées qui contiennent une chaîne de texte spécifique, par exemple, toutes les valeurs saisies doivent contenir le texte "KTE" comme illustré ci-dessous, vous pouvez appliquer la validation des données avec une formule personnalisée basée sur les fonctions FIND et ISNUMBER. Merci de faire comme ceci :

1. Sélectionnez une plage de cellules qui n'autorise que les textes contenant un certain texte.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume dans la liste déroulante Autoriser.
  • Et puis, entrez l'une des formules ci-dessous dans le Laits en poudre zone de texte.
  • =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
    =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée, le texte KTE est la chaîne de texte que les entrées doivent contenir.

3. Désormais, lorsque la valeur saisie ne contient pas le texte conçu, une boîte d'avertissement apparaît.


Les entrées autorisées doivent contenir l'un des nombreux textes spécifiques

La formule ci-dessus ne fonctionne que pour une chaîne de texte, si vous avez besoin que l'une des nombreuses chaînes de texte soit autorisée dans les cellules comme illustré ci-dessous, vous devez utiliser les fonctions SUMPRODUCT, FIND et ISNUMBER ensemble pour créer une formule.

1. Sélectionnez une plage de cellules qui n'autorise que les textes contenant l'un des nombreux éléments.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Ensuite, entrez l'une des formules ci-dessous selon vos besoins dans le Laits en poudre zone de texte.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
  • Et puis, cliquez OK pour fermer la boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée, C2: C4 est la liste des valeurs que vous souhaitez autoriser les entrées contiennent l'une d'entre elles.

3. Et maintenant, seules les entrées contenant l'une des valeurs de la liste spécifique peuvent être saisies.


 Les entrées autorisées ne doivent pas contenir un ou un des nombreux textes spécifiques

Les entrées autorisées ne doivent pas contenir un texte spécifique

Pour valider les entrées ne doivent pas contenir de texte spécifique, par exemple, pour autoriser des valeurs qui ne doivent pas contenir le texte « KTE » dans une cellule, vous pouvez utiliser les fonctions ESTERREUR et FIND pour créer une règle de validation des données. Merci de faire comme ceci :

1. Sélectionnez une plage de cellules qui n'autorise que les textes qui ne contiennent pas certains textes.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez l'une des formules ci-dessous dans le Laits en poudre zone de texte.
  • =ISERROR(FIND("KTE",A2))                  (Case sensitive)
    =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée, le texte KTE est la chaîne de texte que les entrées ne doivent pas contenir.

3. Désormais, les entrées qui contiennent le texte spécifique ne pourront pas être saisies.


Les entrées autorisées ne doivent pas contenir l'un des nombreux textes spécifiques

Pour éviter que l'une des nombreuses chaînes de texte d'une liste ne soit saisie comme illustré ci-dessous, procédez comme suit :

1. Sélectionnez une plage de cellules dans laquelle vous souhaitez empêcher certains textes.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, veuillez effectuer les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Ensuite, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
    =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
  • Et puis, cliquez OK pour fermer la boîte de dialogue.

Notes: Dans les formules ci-dessus, A2 est la première cellule de la plage sélectionnée, C2: C4 est la liste des valeurs que vous souhaitez empêcher si les entrées en contiennent une.

3. A partir de maintenant, les entrées qui contiennent l'un des textes spécifiques ne pourront pas être saisies.


4.5 La validation des données n'autorise que des valeurs uniques

Si vous souhaitez empêcher la saisie de données en double dans une plage de cellules, cette section présentera quelques méthodes rapides pour résoudre cette tâche dans Excel.

 Autoriser uniquement les valeurs uniques avec la fonction de validation des données

Normalement, la fonctionnalité de validation des données avec une formule personnalisée basée sur la fonction COUNTIF peut vous aider, procédez comme suit :

1. Sélectionnez les cellules ou la colonne dans laquelle vous souhaitez que seules des valeurs uniques soient saisies.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =COUNTIF($A$2:$A$9,A2)=1
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2: A9 est la plage de cellules que vous souhaitez autoriser uniquement les valeurs uniques, et A2 est la première cellule de la plage sélectionnée.

3. Désormais, seules des valeurs uniques peuvent être saisies, et un message d'avertissement apparaîtra lorsque des données en double sont saisies, voir capture d'écran :


 Autoriser uniquement les valeurs uniques avec le code VBA

Le code VBA suivant peut également vous aider à empêcher la saisie de valeurs en double, procédez comme suit :

1. Cliquez avec le bouton droit sur l'onglet de la feuille pour lequel vous souhaitez autoriser uniquement les valeurs uniques, puis choisissez Voir le code dans le menu contextuel, dans le popped out Microsoft Visual Basic pour applications , veuillez copier et coller le code suivant dans le module vide:

Code VBA : autoriser uniquement les valeurs uniques dans une plage de cellules :

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Notes: Dans le code ci-dessus, le A1: A100 et A: A sont les cellules de la colonne que vous souhaitez éviter en double, veuillez les modifier selon vos besoins.

2. Ensuite, enregistrez et fermez ce code, maintenant, lors de la saisie d'une valeur en double dans la cellule A1: A100, une boîte d'avertissement s'affiche comme illustré ci-dessous :


 Autoriser uniquement des valeurs uniques avec une fonctionnalité pratique

Si vous avez Kutools for Excel, Avec son Empêcher la duplication fonctionnalité, vous pouvez définir la validation des données pour éviter les doublons pour une plage de cellules en quelques clics seulement.

Après l'installation de Kutools for Excel, veuillez faire comme ceci:

1. Sélectionnez la plage de cellules que vous souhaitez empêcher les valeurs en double mais n'autorisez que les données uniques.

2. Puis clique Kutools > Empêcher la frappe > Empêcher la duplication, voir capture d'écran:

3. Et un message d'avertissement apparaîtra pour vous rappeler que la validation des données sera supprimée si vous appliquez cette fonctionnalité, cliquez sur Oui et dans la boîte d'invite suivante, cliquez sur OK, voir les captures d'écran:

4. Maintenant, lorsque vous entrez des données en double dans vos cellules spécifiées, une boîte de dialogue s'affiche pour vous rappeler que les données en double ne sont pas valides, voir capture d'écran :


4.6 La validation des données autorise uniquement les majuscules / minuscules / la casse appropriée

Cette validation des données est une fonctionnalité puissante, elle peut également permettre à un utilisateur de saisir uniquement des entrées en majuscules, en minuscules ou en casse appropriée dans une plage de cellules. Veuillez procéder avec les étapes suivantes :

1. Sélectionnez la plage de cellules dans laquelle vous souhaitez saisir uniquement du texte en majuscules, en minuscules ou en casse appropriée.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez l'une des formules ci-dessous dont vous avez besoin dans le Laits en poudre zone de texte.
  • =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
    =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
    =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser.

3. Désormais, seules les entrées conformes à la règle que vous avez créée seront acceptées.


4.7 La validation des données autorise les valeurs qui existent / n'existent pas dans une autre liste

Permettre que les valeurs existent ou n'existent pas dans une autre liste à entrer dans une plage de cellules peut être un problème douloureux pour la plupart d'entre nous. En fait, vous pouvez utiliser la fonction de validation des données avec une formule simple basée sur la fonction COUNTIF pour y faire face.

Par exemple, je souhaite que seules les valeurs de la plage C2:C4 soient saisies dans une plage de cellules comme illustré ci-dessous, pour résoudre ce travail, procédez comme suit :

1. Sélectionnez la plage de cellules à laquelle vous souhaitez appliquer la validation des données.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez l'une des formules ci-dessous dont vous avez besoin dans le Laits en poudre zone de texte.
  • =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
    =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser, C2: C4 est la liste des valeurs que vous souhaitez empêcher ou autoriser si les entrées en font partie.

3. Désormais, les entrées ne sont conformes qu'à la règle que vous avez créée, les autres seront bloquées.


4.8 La validation des données n'oblige à saisir que le format du numéro de téléphone

Lorsque vous saisissez les informations des employés de votre entreprise, une colonne doit saisir le numéro de téléphone, pour garantir une saisie rapide et précise des numéros de téléphone. Dans ce cas, vous pouvez définir la validation des données pour les numéros de téléphone. Par exemple, je veux juste que le numéro de téléphone au format (123) 456-7890 soit autorisé à entrer dans une feuille de calcul, cette section présentera deux astuces rapides pour résoudre cette tâche.

 Forcer uniquement le format du numéro de téléphone avec la fonction de validation des données

Pour autoriser uniquement la saisie d'un format de numéro de téléphone spécifique, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir un format de numéro de téléphone spécifique, puis cliquez avec le bouton droit de la souris, choisissez Format de cellule depuis le menu contextuel, voir capture d'écran:

2. Dans le Format de cellule boîte de dialogue, sous la Numéro onglet, sélectionnez Coutume à gauche Catégories zone de liste, puis entrez le format de numéro de téléphone dont vous avez besoin dans la zone de texte Type, par exemple, je vais utiliser ceci (###) ### - #### format, voir capture d'écran:

3. Puis clique OK pour fermer la boîte de dialogue.

4. Après avoir formaté les cellules, continuez à sélectionner les cellules, puis ouvrez le Validation des données boîte de dialogue par clic Données > Validation des données > Validation des données, dans la boîte de dialogue qui s'affiche, sous le Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez cette formule = ET (NOMBRE (A2), LEN (A2) = 10) dans la zone de texte Formule.
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne dans laquelle vous souhaitez valider le numéro de téléphone.

5. Désormais, lorsque vous saisissez un numéro à 10 chiffres, il sera automatiquement converti au format de numéro de téléphone spécifique selon vos besoins, voir les captures d'écran :

Notes: Si le numéro saisi n'est pas composé de 10 chiffres, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :


 Forcer uniquement le format de numéro de téléphone avec une fonctionnalité utile

Kutools for Excel's Valider le numéro de téléphone Cette fonctionnalité peut également vous aider à forcer uniquement le format de numéro de téléphone à saisir en quelques clics seulement.

Après l'installation de Kutools for Excel, veuillez faire comme ceci:

1. Sélectionnez la liste des cellules qui autorisent uniquement un numéro de téléphone spécifique, puis cliquez sur Kutools > Empêcher la frappe > Valider le numéro de téléphone, voir capture d'écran:

2. Dans le Téléphone boîte de dialogue, sélectionnez le format de numéro de téléphone spécifique dont vous avez besoin ou vous pouvez créer votre propre formatage en cliquant sur le bouton Ajouter bouton, voir capture d'écran:

3. Après avoir sélectionné ou défini le formatage du numéro de téléphone, cliquez sur OK, maintenant, seul le numéro de téléphone avec le formatage spécifique peut être saisi, sinon, un message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :


4.9 La validation des données force uniquement les adresses email à saisir

En supposant que vous deviez saisir plusieurs adresses e-mail dans une colonne d'une feuille de calcul, pour empêcher la saisie de certaines adresses e-mail incorrectes, vous pouvez normalement définir une règle de validation des données pour autoriser uniquement le formatage des adresses e-mail.

 Forcer uniquement le format des adresses e-mail avec la fonction de validation des données

En utilisant la fonctionnalité de validation des données avec une formule personnalisée, vous pouvez créer une règle pour empêcher la saisie rapide des adresses e-mail invalides, procédez comme suit :

1. Sélectionnez les cellules dans lesquelles vous souhaitez que seules les adresses e-mail soient saisies, puis cliquez sur Données > Validation des données > Validation des données.

2. Dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez cette formule = ESTNOMBRE (MATCH ("*@*.?*", A2,0)) into the Laits en poudre zone de texte.
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser.

3. Maintenant, si le texte saisi n'est pas au format d'adresse e-mail, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :


 Forcer uniquement le format des adresses e-mail avec une fonctionnalité pratique

Kutools for Excel prend en charge une fonctionnalité incroyable - Validez votre adresse email, avec cet utilitaire, vous pouvez empêcher les adresses e-mail invalides en un seul clic.

Après l'installation de Kutools for Excel, procédez comme suit:

1. Sélectionnez les cellules dans lesquelles vous autorisez uniquement la saisie d'adresses e-mail, puis cliquez sur Kutools > Empêcher la frappe > Validez votre adresse email. Voir la capture d'écran:

2. Et puis, seul le formatage de l'adresse e-mail permet d'être saisi, sinon, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :


4.10 La validation des données n'oblige à saisir que les adresses IP

Dans cette section, je présenterai quelques astuces rapides pour définir la validation des données afin d'accepter uniquement les adresses IP dans une plage de cellules.

 Forcer uniquement le format des adresses IP avec la fonction de validation des données

Autoriser uniquement la saisie des adresses IP dans une plage spécifique de cellules, procédez comme suit :

1. Sélectionnez les cellules dans lesquelles vous souhaitez saisir uniquement l'adresse IP, puis cliquez sur Données > Validation des données > Validation des données.

2. Dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Remarque: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser.

3. Maintenant, si vous entrez une adresse IP invalide dans la cellule, une boîte de message d'avertissement apparaîtra pour vous le rappeler comme illustré ci-dessous :


 Forcer uniquement le format des adresses IP avec le code VBA

Ici, le code VBA suivant peut également aider à autoriser uniquement la saisie d'adresses IP et à restreindre d'autres saisies, procédez comme suit :

1. Cliquez avec le bouton droit sur l'onglet de la feuille et cliquez sur Voir le code depuis le menu contextuel, dans l'ouverture Microsoft Visual Basic pour applications fenêtre, copiez le code VBA ci-dessous.

Code VBA : valider les cellules pour n'accepter que l'adresse IP

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Notes: Dans le code ci-dessus, A2: A10 est la plage de cellules que vous souhaitez accepter uniquement les adresses IP.

2. Ensuite, enregistrez et fermez ce code, maintenant, seules les adresses IP correctes permettent d'être saisies dans les cellules spécifiques.


 Forcer uniquement le format des adresses IP avec une fonctionnalité simple

Si vous avez Kutools for Excel installé dans votre classeur, son Valider l'adresse IP Cette fonctionnalité peut également vous aider à résoudre cette tâche.

Après l'installation de Kutools for Excel, veuillez faire comme ceci:

1. Sélectionnez les cellules dans lesquelles vous autorisez uniquement la saisie d'adresses IP, puis cliquez sur Kutools > Empêcher la frappe > Valider l'adresse IP. Voir la capture d'écran:

2. Après avoir appliqué cette fonctionnalité, maintenant, seule l'adresse IP permet d'être saisie, sinon, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :


4.11 La validation des données limite les valeurs qui dépassent la valeur totale

Supposons que vous ayez un rapport de dépenses mensuel et que le total du budget soit de 18000 18000 $, maintenant, vous avez besoin que le montant total de la liste des dépenses ne dépasse pas le total prédéfini de XNUMX XNUMX $, comme illustré ci-dessous. Dans ce cas, vous pouvez créer une règle de validation des données en utilisant la fonction SUM pour éviter que la somme des valeurs ne dépasse un total prédéfini.

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez limiter les valeurs.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =SUM($B$2:$B$7)<=18000
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, B2: B7 est la plage de cellules dont vous souhaitez limiter les entrées.

3. Maintenant, lors de la saisie des valeurs dans la plage B2:B7, si le total des valeurs est inférieur à 18000 18000 $, la validation passe. Si une valeur fait que le total dépasse XNUMX XNUMX $, une boîte de message d'avertissement apparaîtra pour vous le rappeler.


4.12 La validation des données restreint la saisie des cellules en fonction d'une autre cellule

Lorsque vous souhaitez limiter les entrées de données dans une liste de cellules en fonction de la valeur d'une autre cellule, la fonction de validation des données peut également aider à résoudre ce problème. Par exemple, si la cellule C1 est le texte "Oui", la plage A2:A9 est autorisée à entrer n'importe quoi, mais, si la cellule C1 est un autre texte, rien ne permet d'être saisi dans la plage A2:A9 comme ci-dessous les captures d'écran montrées :

Pour résoudre ce problème, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez limiter les valeurs.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =$C$1="Yes"
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, C1 est la cellule contient le texte spécifique que vous souhaitez utiliser, et le texte "Oui" est le texte sur lequel vous souhaitez limiter les cellules, veuillez les modifier selon vos besoins.

3. Maintenant, si la cellule C1 a le texte "Oui", tout peut être entré dans la plage A2:A9, si la cellule C1 a un autre texte, vous ne pourrez entrer aucune valeur, voir la démo ci-dessous :


4.13 La validation des données ne permet de saisir que les jours de semaine ou les week-ends

Si vous n'avez besoin de saisir que les jours de la semaine (du lundi au vendredi) ou les week-ends (samedi et dimanche) dans une liste de cellules, le Validation des données peut également vous aider, veuillez suivre les étapes suivantes:

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir les jours de la semaine ou les jours de la semaine.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez l'une des formules ci-dessous dans le Laits en poudre zone de texte selon vos besoins.
  • =WEEKDAY(A2,2)<6                      (allow only weekdays)
    =WEEKDAY(A2,2)>5                      (allow only weekends)
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser.

3. Désormais, vous ne pouvez entrer la date du jour de la semaine ou du week-end que dans les cellules spécifiques en fonction de vos besoins.


4.14 La validation des données permet de saisir la date en fonction de la date du jour

Parfois, vous devrez peut-être autoriser uniquement la saisie des dates supérieures ou inférieures à aujourd'hui dans une liste de cellules. Les Validation des données fonctionnalité avec le AUJOURD'HUI fonction peut vous rendre service. Merci de faire comme ceci :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir uniquement la date future (date supérieure à aujourd'hui).

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =A2>Today()
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser.

3. Désormais, seules les dates supérieures à la date d'aujourd'hui peuvent être saisies dans les cellules, sinon, une boîte de message d'avertissement apparaîtra pour vous le rappeler, voir capture d'écran :

Conseils:

1. Pour permettre la saisie de la date passée (date inférieure à aujourd'hui), veuillez appliquer la formule ci-dessous dans la validation des données :

=A2<Today()

2. Autorisez la saisie d'une date dans une plage de dates spécifique, telle que les dates des 30 prochains jours, veuillez saisir la formule ci-dessous dans la validation des données :

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


4.15 La validation des données permet la saisie de l'heure en fonction de l'heure actuelle

Si vous souhaitez valider les données en fonction de l'heure actuelle, par exemple, seules les heures avant ou après l'heure actuelle peuvent être saisies dans les cellules. Vous pouvez créer votre propre formule de validation des données, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir uniquement les heures avant ou après l'heure actuelle.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Time Freak du Autoriser la liste déroulante.
  • Alors choisi moins que pour n'autoriser que des heures avant l'heure actuelle, ou plus grand que pour autoriser des heures après l'heure actuelle selon vos besoins à partir du Données menu déroulant.
  • Et puis, dans le Heure de fin or Heure de début case, entrez la formule ci-dessous:
  • =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser.

3. Désormais, seules les heures avant ou après l'heure actuelle peuvent être saisies dans les cellules spécifiques.


4.16 Validation des données la date de l'année spécifique ou en cours

Pour autoriser uniquement la saisie des dates d'une certaine année ou de l'année en cours, vous pouvez utiliser la validation des données avec une formule personnalisée basée sur la fonction ANNÉE.

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir uniquement les dates d'une année donnée.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Coutume du Autoriser la liste déroulante.
  • Et puis, entrez la formule ci-dessous dans le Laits en poudre zone de texte.
  • =YEAR(A2)=2020
  • Cliquez OK bouton pour fermer cette boîte de dialogue.

Notes: Dans la formule ci-dessus, A2 est la première cellule de la colonne que vous souhaitez utiliser, 2020 est le numéro d'année que vous souhaitez restreindre.

3. Et puis, seules les dates de l'année 2020 peuvent être saisies, sinon, une boîte de message d'avertissement apparaîtra comme illustré ci-dessous :

Conseils:

Pour autoriser uniquement les dates de l'année en cours, vous pouvez appliquer la formule ci-dessous dans la validation des données :

=YEAR(A2)=YEAR(TODAY())


4.17 Validation des données la date dans la semaine ou le mois en cours

Si vous souhaitez permettre à l'utilisateur de pouvoir saisir les dates de la semaine ou du mois en cours dans des cellules spécifiques, cette section présentera quelques formules pour traiter cette tâche dans Excel.

 Permet de saisir la date de la semaine en cours

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir uniquement les dates de la semaine en cours.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Date du Autoriser la liste déroulante.
  • Et puis, choisissez jusqu'à XNUMX fois du Données menu déroulant.
  • Dans le Date de début zone de texte, entrez cette formule: =AUJOURD'HUI()-JOUR DE LA SEMAINE(AUJOURD'HUI(),3)
  • Dans le Date de fin zone de texte, entrez cette formule: =AUJOURD'HUI()-JOUR DE LA SEMAINE(AUJOURD'HUI(),3)+6
  • Enfin, cliquez OK .

3. Ensuite, seules les dates de la semaine en cours peuvent être saisies, les autres dates seront empêchées comme illustré ci-dessous :


 Permet de saisir la date du mois en cours

Pour autoriser uniquement la saisie des dates du mois en cours, procédez comme suit :

1. Sélectionnez la liste des cellules dans lesquelles vous souhaitez saisir uniquement les dates du mois en cours.

2. Puis clique Données > Validation des données > Validation des données, dans le sauté Validation des données boîte de dialogue, sous la Paramètres onglet, effectuez les opérations suivantes:

  • Sélectionnez Date du Autoriser la liste déroulante.
  • Et puis, choisissez parmi les Données menu déroulant.
  • Dans le Date de début zone de texte, entrez cette formule: =DATE(ANNÉE(AUJOURD'HUI()),MOIS(AUJOURD'HUI()),1)
  • Dans le Date de fin zone de texte, entrez cette formule: =DATE(ANNÉE(AUJOURD'HUI()),MOIS(AUJOURD'HUI()),JOUR(DATE(ANNÉE(AUJOURD'HUI()),MOIS(AUJOURD'HUI())+1,1)-1))
  • Enfin, cliquez OK .

3. Désormais, seules les dates du mois en cours permettent d'être saisies dans les cellules sélectionnées.


5. Comment éditer la validation des données dans Excel ?

Pour éditer ou changer la règle existante de validation des données, veuillez suivre les étapes ci-dessous :

1. Sélectionnez l'une des cellules avec la règle de validation des données.

2. Puis clique Données > Validation des données > Validation des données pour aller à la Validation des données boîte de dialogue, dans la boîte, éditez ou modifiez les règles selon vos besoins, puis cochez Appliquer ces modifications à toutes les autres cellules avec les mêmes paramètres possibilité d'appliquer cette nouvelle règle à toutes les autres cellules avec les critères de validation d'origine. Voir capture d'écran :

3Cliquez sur OK pour enregistrer les modifications.


6. Comment rechercher et sélectionner des cellules avec validation des données dans Excel ?

Si vous avez créé plusieurs règles de validation des données dans votre feuille de calcul, vous devez maintenant rechercher et sélectionner les cellules qui ont appliqué les règles de validation des données, le Aller à Spécial La commande peut vous aider à sélectionner toutes sortes de validation de données ou un type spécifique de validation de données.

1. Activez la feuille de calcul que vous souhaitez rechercher et sélectionnez les cellules avec validation des données.

2. Puis clique Accueil > Rechercher et sélectionner > Aller à Spécial, voir capture d'écran:

3. Dans le Aller à Spécial boîte de dialogue, sélectionnez La validation des données > Tous, voir capture d'écran:

4. Et toutes les cellules avec validation des données ont été sélectionnées à la fois dans la feuille de calcul actuelle.

Pourboires: Si vous souhaitez simplement sélectionner un type spécifique de validation de données, veuillez d'abord sélectionner une cellule contenant la validation de données que vous souhaitez découvrir, puis accédez à la Aller à Spécial boîte de dialogue et choisissez La validation des données > Béton.


7. Comment copier la règle de validation des données dans d'autres cellules ?

Supposons que vous ayez créé une règle de validation des données pour une liste de cellules et que vous deviez maintenant appliquer la même règle de validation des données à d'autres cellules. Au lieu de recréer la règle, vous pouvez copier et coller la règle existante dans d'autres cellules rapidement et facilement.

1. Cliquez pour sélectionner une cellule avec la règle de validation que vous souhaitez utiliser, puis appuyez sur Ctrl + C pour le copier.

2. Ensuite, sélectionnez les cellules que vous souhaitez valider, pour sélectionner plusieurs cellules non adjacentes, appuyez et maintenez la touche Ctrl tout en sélectionnant les cellules.

3. Et puis, faites un clic droit sur la sélection, sélectionnez Collage spécial option, voir capture d'écran:

4. Dans le Collage spécial boîte de dialogue, sélectionnez Validation option, voir capture d'écran:

5Cliquez sur OK bouton, maintenant la règle de validation est copiée dans les nouvelles cellules.


8. Comment utiliser la validation des données pour encercler les entrées invalides dans Excel ?

Parfois, vous devrez peut-être créer des règles de validation des données pour les données existantes, dans ce cas, des données non valides peuvent apparaître dans la plage de cellules. Comment vérifier les données invalides et les modifier ? Dans Excel, vous pouvez utiliser le Cercle des données non valides fonction pour mettre en évidence les données invalides avec un cercle rouge.

Pour encercler les données invalides dont vous avez besoin, vous devez appliquer le Validation des données fonction pour définir une règle pour la plage de données. Veuillez procéder avec les étapes suivantes :

1. Sélectionnez la plage de données que vous souhaitez encercler les données non valides.

2. Puis clique Données > Validation des données > Validation des données, Dans le Validation des données boîte de dialogue, définissez la règle de validation selon vos besoins, par exemple, ici, je vais valider les valeurs supérieures à 500, voir capture d'écran :

3. Puis clique OK pour fermer la boîte de dialogue. Après avoir défini la règle de validation des données, cliquez sur Données > Validation des données > Cercle des données non valides, alors toutes les valeurs invalides inférieures à 500 ont été entourées d'un ovale rouge. Voir les captures d'écran :

Notes:

  • 1. Dès que vous corrigez une donnée invalide, le cercle rouge disparaîtra automatiquement.
  • 2. Cette Cercle des données non valides La fonction ne peut encercler que 255 cellules au maximum. Lorsque vous enregistrez le classeur actuel, tous les cercles rouges seront supprimés.
  • 3. Ces cercles ne sont pas imprimables.
  • 4. Vous pouvez également supprimer les cercles rouges en cliquant sur Données > Validation des données > Effacer les cercles de validation.

9. Comment supprimer la validation des données dans Excel ?

Pour supprimer les règles de validation des données d'une plage de cellules, de la feuille de calcul en cours ou de l'ensemble du classeur, les méthodes suivantes peuvent vous rendre service.

 Supprimer la validation des données dans la plage sélectionnée avec la fonction de validation des données

1. Sélectionnez les cellules avec validation des données que vous souhaitez supprimer.

2. Puis clique Données > Validation des données > Validation des données, dans la boîte de dialogue qui s'affiche, sous le Paramètres onglet, cliquez sur Tout supprimer bouton, voir capture d'écran:

3. Puis clique OK bouton pour fermer cette boîte de dialogue. Et la règle de validation des données appliquée à la plage sélectionnée a été supprimée d'un seul coup.

Pourboires: Pour supprimer la validation des données de la feuille de calcul actuelle, sélectionnez d'abord la feuille entière, puis appliquez les étapes ci-dessus.


 Supprimer la validation des données dans la plage sélectionnée avec une fonctionnalité pratique

Si vous avez Kutools for Excel, il est Effacer les restrictions de validation des données Cette fonctionnalité peut également aider à supprimer les règles de validation des données de la plage sélectionnée ou de la feuille de calcul entière.

Après l'installation de Kutools for Excel, veuillez faire comme ceci:

1. Sélectionnez la plage de cellules ou toute la feuille de calcul contient la validation des données que vous souhaitez supprimer.

2. Puis clique Kutools > Empêcher la frappe > Effacer les restrictions de validation des données, voir capture d'écran:

3. Dans la boîte d'invite qui s'affiche, cliquez sur OK, et la règle de validation des données ont été effacées selon vos besoins.


 Supprimer la validation des données de toutes les feuilles de calcul avec le code VBA

Pour supprimer les règles de validation des données de l'ensemble du classeur, les méthodes ci-dessus prendront du temps s'il y a beaucoup de feuilles de calcul, ici, le code ci-dessous peut vous aider à gérer cette tâche rapidement.

1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Puis clique insérer > Moduleet collez la macro suivante dans le Module fenêtre.

Code VBA : supprimez les règles de validation des données dans toutes les feuilles de calcul :

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Puis appuyez F5 key pour exécuter ce code, et toutes les règles de validation des données ont été immédiatement supprimées de l'ensemble du classeur.

 


  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée
  • Fusionner les cellules / lignes / colonnes et conservation des données; Contenu des cellules divisées; Combiner les lignes en double et la somme / moyenne... Empêcher les cellules en double; Comparer les gammes
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Formules favorites et insérer rapidement, Plages, graphiques et images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF
  • Regroupement du tableau croisé dynamique par numéro de semaine, jour de la semaine et plus encore ... Afficher les cellules déverrouillées et verrouillées par différentes couleurs; Mettre en évidence les cellules qui ont une formule / un nom
onglet kte 201905
  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine

 

Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks For Sharing this Great Information. I loved it.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations