Validation des données Formules personnalisées

Ce didacticiel vous montrera comment créer des formules personnalisées dans Data Validation dans Excel et Google Sheets

Validation des données - Doit commencer par - Excel

Nous pouvons écrire une formule personnalisée pour garantir que les données d'une cellule commencent par un certain texte.

1. Mettez en surbrillance la plage requise, par exemple : B3:B8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.

3. Sélectionner Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :

=EXACT(GAUCHE(B3,4),,"FRU-")

La formule utilise 2 fonctions EXACT et LA GAUCHE pour déterminer si les 4 premiers caractères entrés dans la cellule sont identiques à « FRU- »

4. Si vous souhaitez ajouter un message d'entrée pour votre utilisateur, cliquez sur Message d'entrée.

5. Assurez-vous que la case "Afficher le message d'entrée lorsque la cellule est sélectionnée” est coché, puis saisissez le titre et le message dont vous avez besoin.
6. Si vous souhaitez ajouter une alerte d'erreur, cliquez sur Alerte d'erreur.

7. Vous pouvez changer le style de Arrêter soit Avertissement ou Informations si vous en avez besoin, puis saisissez votre titre et votre message d'erreur.
8. Cliquez sur d'accord.
9. Si vous avez utilisé le Saisir Option message, un commentaire apparaîtra à l'écran informant l'utilisateur de la règle.

10. Tapez « FRI-124 » dans la cellule B3. Si vous avez sélectionné Arrêter, la boîte de message suivante apparaîtra.

11. Si vous avez sélectionné Avertissement, alors cette boîte de message apparaîtra. Cela vous permet de continuer si vous déterminez que les données sont correctes.

12. Si vous avez sélectionné Informations, alors cette boîte de message apparaîtra.

13. Si vous cliquez d'accord, vous serez autorisé à continuer avec les données incorrectes saisies dans la cellule.
14. Cliquez sur Annuler pour quitter le message ou d'accord pour saisir le texte dans la cellule.

Validation des données Autoriser les majuscules uniquement dans Excel

Nous pouvons écrire une formule personnalisée pour garantir que les données d'une cellule n'autorisent les majuscules que si du texte est entré dans la cellule.

1. Mettez en surbrillance la plage requise, par exemple : B3:B8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionner Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :

=EXACT(B3,SUPÉRIEUR(B3))

La formule utilise 2 fonctions EXACT et SUPÉRIEUR pour déterminer si le texte entré dans la cellule est en majuscule. Les cellules avec un mélange de nombre et de texte sont considérées comme du texte et les nombres sont ignorés dans la règle.

4. Cliquez sur d'accord.
5. Tapez « fru-124 » dans la cellule B3.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.
8. Tapez "123456" dans la cellule B3.
9. Cela sera autorisé car il s'agit d'un nombre et non d'un texte.

Dans notre exemple suivant, nous veillerons à ce que seul du texte en majuscules puisse être saisi dans la cellule.

Validation des données Autoriser le texte en majuscules uniquement dans Excel

Nous pouvons écrire une formule personnalisée dans la validation des données qui peut être utilisée pour garantir que les données d'une cellule n'autorisent que le texte en majuscules

REMARQUE : si vous entrez des informations dans une cellule qui commence par du texte mais contient des chiffres, Excel prendra en compte le texte des informations.

1. Mettez en surbrillance la plage requise, par exemple : B3:B8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionner Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :

=ET(EXACT(B3,SUPÉRIEUR(B3)),ISTEXT(B3))

La formule utilise 4 fonctions ET, EXACT, SUPÉRIEUR et TEXTE pour déterminer si le texte entré dans les cellules est en majuscule ET pour déterminer si les informations saisies sont en fait du texte et non un pur nombre.

4. Cliquez sur d'accord.
5. Tapez « fru-124 » dans la cellule B3.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.
8. Tapez "123456" dans la cellule B3.
9. Vous obtiendrez à nouveau le message d'erreur.
10. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

La validation des données empêche les espaces dans Excel

Nous pouvons écrire une formule personnalisée pour garantir qu'aucun espace n'est saisi dans les données saisies dans une plage de cellules.

1. Mettez en surbrillance la plage requise, par exemple : B3:B8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionner Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :

=B3=REMPLACER(B3, ” ” , “”)

La formule utilise la fonction SUBSTITUTE pour vérifier que les espaces n'existent pas.

4. Cliquez sur d'accord.
5. Tapez "FRU - 124" dans la cellule B4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

La validation des données empêche les doublons dans Excel

Nous pouvons écrire une formule personnalisée pour nous empêcher d'entrer des informations en double dans une plage de cellules.

1. Mettez en surbrillance la plage requise, par exemple : B3:B8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=COUNTIF($B$3:$B$8,B3)<2

La formule utilise le COUNTIF fonction et utilisations ABSOLUS dans la plage B3:B8 pour s'assurer qu'il s'agit de la liste que le COUNTIF La fonction examine lorsqu'elle vérifie s'il existe des valeurs en double.

4. Cliquez sur d'accord.
5. Tapez « FRU-123 » dans la cellule D4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

La validation des données existe dans la liste dans Excel

Nous pouvons écrire une formule personnalisée pour garantir que seul un texte spécifique est entré dans une cellule.

1. Mettez en surbrillance la plage requise, par exemple : D3:D8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Personnalisé dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=COUNTIF($F$6:$F$8,D3)>0

La formule utilise le COUNTIF fonction et utilisations ABSOLUS dans la plage F3:F8 pour s'assurer qu'il s'agit de la liste que le COUNTIF La fonction regarde lorsqu'elle vérifie que le texte correct est entré.

4. Cliquez sur d'accord.
5. Tapez « Unique » dans la cellule D4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

La validation des données n'existe pas dans la liste dans Excel

Nous pouvons utiliser une formule personnalisée pour nous assurer qu'un texte spécifique n'est pas entré dans une cellule.

1. Mettez en surbrillance la plage requise, par exemple : C3:C8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=COUNTIF($F$6:$F$8,C3)=0

La formule utilise le COUNTIF fonction et utilisations ABSOLUS dans la plage F3:F8 pour s'assurer qu'il s'agit de la liste que le COUNTIF La fonction regarde lorsqu'elle vérifie que le texte correct est entré.

4. Cliquez sur d'accord.
5. Tapez « Bœuf » dans la cellule C4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

Validation des données, seuls les nombres sont entrés dans Excel

Nous pouvons utiliser une formule personnalisée pour garantir que seul un nombre est entré dans une cellule.

1. Mettez en surbrillance la plage requise, par exemple : E3:E8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=ISNUMBER(F3:F8)

La formule utilise la fonction ISNUMBER pour s'assurer qu'un nombre est entré dans les cellules de la plage.

4. Cliquez sur d'accord.
5. Tapez « neuf » dans la cellule F4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

La validation des données ne dépasse pas la valeur dans Excel

Nous pouvons utiliser une formule personnalisée pour garantir que les valeurs entrées dans une cellule ne dépassent pas une valeur spécifiée.

1. Mettez en surbrillance la plage requise, par exemple : E3:E8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=E3<=$G$6

La formule un ABSOLU dans la plage G6 pour s'assurer qu'il s'agit de la valeur que la règle vérifie lorsque les données sont saisies dans E3.

4. Cliquez sur d'accord.
5. Tapez « 9 » dans la cellule E4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

La validation des données ne dépasse pas le total dans Excel

Nous pouvons utiliser une formule personnalisée pour garantir que les valeurs entrées dans une plage de cellules ne dépassent pas une valeur totale spécifiée pour la plage

1. Mettez en surbrillance la plage requise, par exemple : F3:F8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=SOMME($F$3:$F$8)<=$H$6

La formule utilise le SOMME fonction et utilisations ABSOLUS dans la plage F3:F8 pour s'assurer qu'il s'agit de la liste que le SOMME La fonction regarde lorsqu'elle vérifie que le total de la plage n'est pas supérieur à la valeur entrée dans H6.

4. Cliquez sur d'accord.
5. Tapez « 40 » dans la cellule F4.
6. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler pour quitter le message ou Réessayez pour ressaisir le texte correct dans la cellule.

Dates de validation des données en semaine uniquement dans Excel.

Nous pouvons utiliser une formule personnalisée pour garantir que seuls les jours de la semaine sont saisis lorsque les dates sont utilisées dans Excel.

1. Mettez en surbrillance la plage requise, par exemple : G3:G8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=JOUR DE LA SEMAINE(F3,2)<6

La fonction jour de la semaine vérifie que le jour contenu dans la date n'est pas un samedi ou un dimanche.

4. Modifier la date dans G5 pour afficher un samedi (par exemple 9^e mai 2022).
5. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

7. Cliquez sur Annuler ou Réessayer pour saisir une autre date.

Date future de validation des données uniquement dans Excel

Nous pouvons créer une formule personnalisée pour permettre uniquement à l'utilisateur de saisir une date future.

1. Mettez en surbrillance la plage requise, par exemple : G3:G8.
2. Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
3. Sélectionnez Client dans la liste déroulante Autoriser, puis saisissez la formule suivante :

=G3>AUJOURD'HUI()

La formule utilise le AUJOURD'HUI pour vérifier si la date entrée dans la cellule est supérieure à la date d'aujourd'hui.

4. Modifier la date dans G5 à hier.
5. Si vous avez utilisé l'option Alerte d'erreur, votre message d'avertissement et d'erreur personnalisé apparaîtra. Si vous n'avez pas utilisé cette option, l'avertissement standard apparaîtra.

6. Cliquez sur Annuler ou Réessayer pour saisir une autre date.

La validation des données doit commencer par dans Google Sheets

1. Mettez en surbrillance la plage requise, par exemple : B3:B8.
2. Dans le menu, sélectionnez Données > Validation des données.

3. La plage de cellules sera déjà remplie.

4. Sélectionner Formule personnalisée est de la liste déroulante Critères.

5. Tapez la formule.

=EXACT(GAUCHE(B3,4),,"FRU-")

6. Sélectionnez soit Afficher l'avertissement ou Rejeter l'entrée si les données sont invalides.

7. Vous pouvez saisir du texte d'aide à la validation si vous le souhaitez.

8. Cliquez sur Enregistrer.

9. Cliquez en B3 pour voir le texte d'aide à la validation

10. Tapez FRI-123
11. Si vous avez sélectionné Afficher l'avertissement, le message suivant apparaît.

12. Sinon, si vous avez sélectionné Rejeter l'entrée sur les données invalides, vous ne pourrez plus saisir les données et le message suivant s'affichera à l'écran.

Les autres exemples de formules personnalisées dans Google Sheets fonctionnent exactement de la même manière.