Formules de Validation de Données Personnalisées
Télécharger le Classeur Exemple
Ce tutoriel vous montre comment créer des formules personnalisées dans la validation des données dans Excel et Google Sheets
Validation des données – Doit Commencer Par – Excel
Nous pouvons écrire une formule personnalisée pour nous assurer que les données d’une cellule commencent par un certain texte.
- Mettez en surbrillance la plage requise, par exemple : B3:B8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :
=EXACT(GAUCHE(B3;4); « FRU-« )
La formule utilise 2 fonctions EXACT et GAUCHE pour déterminer si les 4 premiers caractères saisis dans la cellule sont identiques à « FRU- »
- Si vous voulez ajouter un message de saisie pour votre utilisateur, cliquez sur Message de saisie.
- Assurez-vous que la case à cocher « Quand la cellule est sélectionnée » est cochée, puis tapez le titre et le message que vous souhaitez.
- Si vous voulez ajouter une alerte d’erreur, cliquez sur Alerte d’erreur.
- Vous pouvez changer le style de Stop à Avertissement ou Information si vous le souhaitez, puis tapez votre titre et votre message d’erreur.
- Cliquez sur OK.
- Si vous avez utilisé l’option Message de Saisie, un commentaire apparaîtra à l’écran pour informer l’utilisateur de la règle.
- Tapez « FRI-124 » dans la cellule B3. Si vous avez sélectionné Arrêt, la boîte de message suivante s’affiche.
- Si vous avez sélectionné Avertissement, la boîte de message suivante s’affiche. Elle vous permet de continuer si vous déterminez que les données sont correctes.
- Si vous avez sélectionné Information, cette boîte de message s’affiche.
- Si vous cliquez sur OK, vous serez autorisé à poursuivre avec les données incorrectes saisies dans la cellule.
- Cliquez sur Annuler pour quitter le message ou sur OK pour entrer le texte dans la cellule.
Validation des Données – Autoriser Uniquement les Majuscules dans Excel
Nous pouvons écrire une formule personnalisée pour garantir que les données d’une cellule n’autorisent que les majuscules si du texte est saisi dans la cellule.
- Mettez en surbrillance la plage requise, par exemple : B3:B8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :
=EXACT(B3;MAJUSCULE(B3))
La formule utilise 2 fonctions EXACT et MAJUSCULE pour déterminer si le texte saisi dans la cellule est en majuscules. Les cellules contenant un mélange de chiffres et de texte sont considérées comme du texte et les chiffres sont ignorés dans la règle.
- Cliquez sur OK.
- Tapez « fru-124 » dans la cellule B3.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
- Tapez « 123456 » dans la cellule B3.
- Cette saisie sera autorisée car il s’agit d’un chiffre et non d’un texte.
Dans notre prochain exemple, nous allons nous assurer que seul du texte en majuscules peut être saisi dans la cellule.
Validation des Données – N’Autoriser que le Texte en Majuscules dans Excel
Nous pouvons écrire une formule personnalisée dans la validation des données pour nous assurer que les données d’une cellule n’autorisent que le texte en majuscules
REMARQUE : si vous saisissez des informations dans une cellule qui commence par du texte mais contient des chiffres, Excel considérera ces informations comme du texte.
- Mettez en surbrillance la plage requise, par exemple : B3:B8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :
=ET(EXACT(B3;MASCULE(B3));ESTTEXTE(B3))
La formule utilise 4 fonctions ET, EXACT, MAJUSCULE et ESTTEXTE pour déterminer si le texte saisi dans les cellules est en majuscules ET pour déterminer si l’information saisie est réellement du texte et non un simple chiffre par exemple.
- Cliquez sur OK.
- Tapez « fru-124 » dans la cellule B3.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affichera. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
- Tapez « 123456 » dans la cellule B3.
- Vous obtenez à nouveau le message d’erreur.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Éviter les Espaces dans Excel
Nous pouvons écrire une formule personnalisée pour nous assurer qu’aucun espace n’est introduit dans les données saisies dans une plage de cellules.
- Mettez en surbrillance la plage requise, par exemple : B3:B8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :
=B3=SUBSTITUE(B3; » « ; « »)
La formule utilise la fonction SUBSTITUE pour vérifier que la cellule ne contient pas d’espace.
- Cliquez sur OK.
- Tapez « FRU – 124 » dans la cellule B4.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Éviter les Doublons dans Excel
Nous pouvons écrire une formule personnalisée pour nous empêcher de saisir des informations en double dans une plage de cellules.
- Mettez en surbrillance la plage requise, par exemple : B3:B8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=NB.SI($B$3:$B$8;B3)<2
La formule utilise la fonction NB.SI et utilise des RÉFÉRENCES ABSOLUES dans la plage B3:B8 pour s’assurer qu’il s’agit de la liste que la fonction NB.SI examine lorsqu’elle vérifie s’il y a des valeurs en double.
- Cliquez sur OK.
- Tapez « FRU-123 » dans la cellule D4.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Existe dans une Liste dans Excel
Nous pouvons écrire une formule personnalisée pour nous assurer que seul un texte spécifique est saisi dans une cellule.
- Mettez en surbrillance la plage requise, par exemple D3:D8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Personnalisé dans la liste déroulante Autoriser, puis tapez la formule suivante :
=NB.SI($F$6:$F$8;D3)>0
La formule utilise la fonction NB.SI et utilise des RÉFÉRENCES ABSOLUES dans la plage F3:F8 pour s’assurer qu’il s’agit de la liste que la fonction NB.SI examine lorsqu’elle vérifie que le texte correct est saisi.
- Cliquez sur OK.
- Tapez « Simple » dans la cellule D4.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – N’Existe pas dans une Liste dans Excel
Nous pouvons utiliser une formule personnalisée pour nous assurer qu’un texte spécifique ne fait pas partie d’une liste spécifiée.
- Mettez en surbrillance la plage requise, par exemple C3:C8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=NB.SI($F$6:$F$8;C3)=0
La formule utilise la fonction NB.SI et utilise des RÉFÉRENCES ABSOLUES dans la plage F3:F8 pour s’assurer qu’il s’agit de la liste que la fonction NB.SI examine lorsqu’elle vérifie que le texte correct est saisi.
- Cliquez sur OK.
- Tapez « Bœuf » dans la cellule C4.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Permettre les Nombres Seulement dans Excel
Nous pouvons utiliser une formule personnalisée pour nous assurer que seul des nombres sont permis dans certaines cellules.
- Mettez en surbrillance la plage requise, par exemple F3:F8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=ESTNUM(F3:F8)
La formule utilise la fonction ESTNUM pour s’assurer qu’un nombre est saisi dans les cellules de la plage.
- Cliquez sur OK.
- Tapez « cinq » dans la cellule F3.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Inférieures à une Valeur Spécifiée dans Excel
Nous pouvons utiliser une formule personnalisée pour nous assurer que les valeurs saisies dans une cellule ne dépassent pas une valeur spécifiée.
- Mettez en surbrillance la plage requise, par exemple : E3:E8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=E3<=$G$6
La plage G6 est verrouillée dans la formule pour s’assurer que c’est la valeur que la règle vérifie lorsque les données sont entrées dans E3.
- Cliquez sur OK.
- Tapez « 9 » dans la cellule E4.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Total Inférieur à une Valeur dans Excel
Nous pouvons utiliser une formule personnalisée pour nous assurer que la somme des valeurs saisies dans une plage de cellules ne dépassent pas une valeur spécifiée pour la plage
- Mettez en surbrillance la plage requise, par exemple F3:F8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=SOMME($F$3:$F$8)<=$H$6
La formule utilise la fonction SOMME et utilise des RÉFÉRENCES ABSOLUES dans la plage F3:F8 pour s’assurer que c’est la liste que la fonction SOMME examine lorsqu’elle vérifie que le total de la plage n’est pas supérieur à la valeur saisie dans H6.
- Cliquez sur OK.
- Tapez « 40 » dans la cellule F4.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler pour quitter le message ou sur Réessayer pour saisir à nouveau le texte correct dans la cellule.
Validation des Données – Dates Correspondant à des Jours de la Semaine Uniquement dans Excel.
Nous pouvons utiliser une formule personnalisée pour nous assurer que les dates saisies dans une plage spécifiée correspondent seulement à des jours de la semaine.
- Mettez en surbrillance la plage requise, par exemple G3:G8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=JOURSEM(F3;2)<6
La fonction JOURSEM vérifie que le jour contenu dans la date n’est pas un samedi ou un dimanche.
- Modifiez la date dans G5 pour afficher un samedi (par exemple, le9 mai 2020).
- 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.
- Cliquez sur Annuler ou Réessayer pour saisir une autre date.
Validation des Données – Permettre les Dates Futures Uniquement dans Excel
Nous pouvons créer une formule personnalisée pour permettre à l’utilisateur de saisir uniquement une date située dans le futur.
- Mettez en surbrillance la plage requise, par exemple G3:G8.
- Dans le ruban, sélectionnez Données > Outils de données > Validation des données.
- Sélectionnez Client dans la liste déroulante Autoriser, puis tapez la formule suivante :
=G3>AUJOURDHUI()
Cette formule utilise la fonction AUJOURDHUI pour vérifier si la date saisie dans la cellule est supérieure à la date d’aujourd’hui.
- Modifiez la date dans G5 en la remplaçant par hier.
- Si vous avez utilisé l’option Alerte d’erreur, votre message d’avertissement et d’erreur personnalisé s’affiche. Si vous n’avez pas utilisé cette option, l’avertissement standard s’affiche.
- Cliquez sur Annuler ou Réessayer pour entrer une autre date.
Validation des Données – Doit Commencer Par dans Google Sheets
- Mettez en surbrillance la plage requise, par exemple : B3:B8.
- Dans le menu, sélectionnez Données > Validation des données.
- La plage de cellules sera déjà remplie.
- Sélectionnez Formule personnalisée dans la liste déroulante Critères.
- Tapez la formule.
=EXACT(GAUCHE(B3;4); « FRU-« )
- Sélectionnez soit Afficher un avertissement, soit Rejeter la saisie si les données ne sont pas valides.
- Vous pouvez saisir un texte d’aide à la validation si vous le souhaitez.
- Cliquez sur Enregistrer.
- Cliquez sur B3 et tapez FRI-123
- Si vous avez sélectionné Afficher un Avertissement, le message suivant s’affiche.
- Si vous avez sélectionné Refuser la Saisie, vous ne pourrez pas saisir les données et le message suivant apparaîtra à l’écran.
Les autres exemples de formules personnalisées dans Google Sheets fonctionnent exactement de la même manière.
