Excel Validation des données à l'aide d'expressions régulières (regex)

Le tutoriel montre comment effectuer la validation des données dans Excel en utilisant des expressions régulières à l'aide d'une fonction regexmatch personnalisée.

En ce qui concerne la restriction de la saisie des utilisateurs dans les feuilles de calcul Excel, la validation des données est indispensable. Vous voulez autoriser uniquement les nombres ou les dates dans une cellule donnée? Ou limiter les valeurs de texte à une longueur spécifique? Ou peut-être interdire les temps en dehors d'une plage donnée? Aucun problème, tout cela peut être facilement fait avec des critères de validation prédéfinis ou personnalisés. Mais que se passe-t-il si je veux autoriser uniquement les adresses e-mail valides ou les chaînes qui correspondent à un modèle spécifique? Hélas, ce n'est pas possible. Regex, dites-vous? Hmm… ça pourrait fonctionner!

• Comment faire la validation des données Excel avec Regex
• RegEx personnalisé pour valider les chaînes de texte
• Regex pour valider les adresses e-mail
• Regex pour valider les mots de passe

Comment faire la validation des données Excel avec Regex

Malheureusement, aucune des fonctionnalités Excel intégrée ne prend en charge les expressions exposées et la validation des données ne fait pas exception. Pour pouvoir valider l'entrée de cellules à l'aide d'expressions régulières, vous devez d'abord créer une fonction regex personnalisée. Une autre complication est que les fonctions définies par l'utilisateur de VBA ne peuvent pas être servies directement à la validation des données - vous aurez besoin d'un médiateur sous la forme d'une formule nommée.

Compte tenu de ce qui précède, décrivons brièvement les étapes à suivre pour valider les données dans Excel à l'aide de regex:

1. Créez une fonction regex personnalisée qui vérifie si une valeur d'entrée correspond à une expression régulière.
2. Définissez un nom pour votre formule regex.
3. Configurez une règle de validation des données basée sur la formule nommée.
4. Copiez les paramètres de validation dans autant de cellules que vous le souhaitez.

Cela ressemble à un plan? Essayons de le mettre en œuvre dans la pratique!

Excel Validation des données à l'aide d'expressions régulières personnalisées

Cet exemple aborde un cas très courant - comment autoriser uniquement les valeurs d'un modèle spécifique.

En supposant que vous conservez des codes de SKU dans votre feuille de calcul et que vous voulez être sûr que seuls les codes correspondant à un modèle donné entrent dans la liste. À condition que chaque SKU se compose de 2 groupes de caractères séparés avec un trait d'union, le premier groupe comprenant 3 majuscules et le deuxième groupe - 3 chiffres, vous pouvez identifier ces valeurs en utilisant le regex ci-dessous.

Modèle : ^ [az] {3} - \ d {3} $

Veuillez noter que le début (^) et la fin ($) de la chaîne sont ancrés, donc aucun caractères autre que dans le motif ne peut être entré dans une cellule.

1. Ajouter une fonction de correspondance regex personnalisée

Commencez par insérer la fonction regexpmatch dans votre classeur. Le code est déjà écrit par nos gourous Excel, il vous suffit donc de le copier à partir de la page et coller liés à votre éditeur VBA.

Voici la syntaxe de la fonction pour votre référence:

Regexpmatch (texte, modèle, [match_case])

Où:

• Texte (requis) - une chaîne source (dans notre contexte - une cellule validée).
• Modèle (requis) - Une expression régulière à correspondre.
• Match_case (facultatif) - type de correspondance. Vrai ou omis - sensible à la casse; Faux - insensible à la cas.

Conseil. Si vous êtes un utilisateur de notre suite ultime, vous pouvez effectuer une validation de données regex dans Excel sans ajouter de code VBA à vos classeurs. Tirez simplement parti d'une fonction CapyBitsRegexMatch personnalisée incluse avec nos outils Regex.

2. Créez une formule nommée

Dans votre feuille de calcul cible, sélectionnez la cellule A1 (quel que soit son contenu et quelle que soit la cellule que vous allez réellement valider), appuyez sur Ctrl F3 pour ouvrir le gestionnaire de noms et définir un nom pour cette formule:

=RegExpMatch(Sheet1!A1, "^[AZ]{3}-\d{3}$")

Ou vous pouvez entrer le regex dans une cellule (A2 dans cet exemple) et fournir $ a 2 $ au deuxième argument:

=RegExpMatch(Sheet1!A1, Sheet1!$A$2)

Pour que la formule fonctionne correctement, assurez-vous d'utiliser une référence relative pour l'argument texte (A1) et la référence absolue pour le modèle ($ a 2 $).

Étant donné que notre formule est visée pour valider les numéros de SKU, nous le nommons en conséquence: validate_sku .

Remarque importante! Lors de la définition de la formule, veuillez vérifier que le premier argument fait référence à la cellule actuellement sélectionnée , sinon la formule ne fonctionnera pas. Par exemple, si la cellule A1 est sélectionnée sur la feuille, mettez A1 dans le premier argument (selon nos recommandations); Si B2 est sélectionné, utilisez B2 pour le premier argument, et donc un. Peu importe la référence particulière que vous utilisez tant qu'elle correspond à la cellule actuellement sélectionnée.

Pour les instructions étape par étape, veuillez voir comment faire une formule nommée dans Excel.

3. Configurer la validation des données

Sélectionnez la première cellule à vérifier (A5 dans notre cas) et créez une règle de validation des données personnalisée basée sur la formule nommée. Pour cela, faites ce qui suit:

1. Cliquez sur l'onglet Données > Validation des données .
2. Dans la liste déroulante Autoriser , sélectionnez Custom .
3. Entrez la formule ci-dessous dans la zone correspondante.

   =Validate_SKU

4. Désélectionnez l'option Ignore Blank , sinon votre règle ne fonctionnera pas.

Facultativement, vous pouvez taper un message d'erreur personnalisé à afficher lorsque des données non valides sont entrées dans une cellule.

Si vous sentez que vous avez besoin des étapes détaillées, c'est parti: comment configurer la validation des données personnalisées dans Excel.

4. Copier la validation des données dans plus de cellules

Pour copier les paramètres de validation dans plus de cellules, c'est ce que vous devez faire:

1. Sélectionnez la cellule avec la validation des données et appuyez sur Ctrl C pour les copier.
2. Sélectionnez d'autres cellules que vous souhaitez valider, cliquez avec le bouton droit, cliquez sur Coller Special et choisissez l'option de validation .
3. Cliquez sur OK .

   

Plus d'informations peuvent être trouvées dans la façon de copier la validation des données.

Maintenant, chaque fois que quelqu'un tente d'entrer dans une SKU non valide dans l'une des cellules validées, le message d'avertissement suivant apparaîtra:

Validation des e-mails avec Regex

Pour effectuer la validation des e-mails, vous commencez par écrire une expression régulière qui correspond à une adresse e-mail.

Modèle : ^ [\ w \. \ -] @ [a-za-z0-9] [a-za-z0-9 \. \ -] * [a-za-z0-9] \. [A-za-z] {2,24} $

Pour l'explication détaillée de la syntaxe, veuillez consulter Regex pour correspondre aux adresses e-mail valides.

Et maintenant, spécifiez les critères de validation en effectuant les étapes déjà familières:

1. Entrez le regex ci-dessus dans B2.
2. Sélectionnez la cellule A1 et définissez un nom appelé validate_email qui fait référence à:

   =RegExpMatch(Sheet1!A1, Sheet1!$B$2)

   

3. Pour la cellule B5, appliquez une validation de données personnalisée à l'aide de la formule ci-dessous. Il est essentiel que l'option Ignore Blank soit non sélectionnée.

   =Validate_Email

   

   De plus, vous pouvez configurer un message d'erreur personnalisé incitant l'utilisateur à saisir une adresse e-mail valide.

4. Copiez la règle dans les cellules ci-dessous.

Si une adresse e-mail que vous entrez dans une cellule validée ne correspond pas à un modèle regex, l'alerte suivante apparaîtra:

Valider les mots de passe en utilisant des expressions régulières

Lorsque vous utilisez Regex pour la validation du mot de passe, la première chose à décider est exactement ce que votre expression régulière devrait vérifier. Voici quelques exemples qui pourraient vous mettre sur la bonne voie.

Un mot de passe doit comporter au moins 6 caractères et ne peut contenir que des lettres (majuscules ou minuscules) et des chiffres:

Modèle : ^ [a-za-z0-9] {6,} $

Un mot de passe doit contenir un minimum de 6 caractères et inclure au moins une lettre et un chiffre:

Modèle : ^ (? =. * [A-za-z]) (? =. * \ D) [a-za-z \ d] {6,} $

Un mot de passe doit contenir une longueur de 6 caractères et inclure au moins une lettre majuscule, une lettre minuscule et un chiffre:

Modèle : ^ (? =. * [Az]) (? =. * [Az]) (? =. * \ D) [a-za-z \ d] {6,} $

Un mot de passe doit comporter 6 caractères de 6 et inclure au moins une lettre, un chiffre et un caractère spécial:

Modèle : ^ (? =. * [A-za-z]) (? =. * \ D) (? =. * [@ $!% * #? & _-]) [a-za-z \ d @ $!% * #? & _-] {6,} $

Avec le modèle établi, vous pouvez passer à la configuration de la validation des données:

1. Entrez votre mot de passe regex en C2.
2. Sélectionnez la cellule A1 et créez une formule nommée appelée validate_password :

   =RegExpMatch(Sheet1!A1, Sheet1!$C$2)

   

3. Pour la cellule C5, créez une règle de validation personnalisée avec la formule ci-dessous. N'oubliez pas de ne pas sélectionner la case Ignore Blank .

   =Validate_Password

   

4. Copiez la règle sur autant de cellules que vous le souhaitez.

Maintenant, vous pouvez ajouter en toute sécurité de nouveaux mots de passe à la liste. Si une chaîne d'entrée ne correspond pas au regex, l'alerte suivante vous rappellera quels types de valeurs sont acceptées:

La validation des données regex ne fonctionne pas

Si la validation des données Regex ne fonctionne pas dans votre Excel, c'est probablement pour l'une des raisons suivantes.

Fonction regexpmatch manquante

Avant d'appliquer la validation des données, assurez-vous d'insérer le code de la fonction RegexPMatch dans votre classeur.

Expression régulière incorrecte

Pour vous assurer que votre regex fonctionne comme prévu, vous pouvez saisir une formule Regexpmatch dans une cellule et examiner les résultats. Pour plus d'informations, veuillez consulter l'expression régulière Excel correspondant aux exemples.

Pour analyser et déboguer vos expressions régulières, vous pouvez utiliser des services de test regex en ligne gratuits tels que regex101 ou regexr.

Mauvaise formule nommée

Une raison très courante de défaillance de validation des données est une formule nommée regex faisant référence à une mauvaise cellule. Dans tous les exemples, nous avons recommandé de définir une formule faisant référence à A1:

=RegExpMatch(A1, regex)

Cela ne fonctionne que si la cellule A1 est active lors de la définition d'un nom et d'une référence relative (sans le signe $) est utilisée.

L'idée est qu'une référence relative spécifiée dans la formule (A1) changera automatiquement en fonction de la position relative de la cellule validée. En d'autres termes, la cellule A1 est choisie juste pour la commodité et la cohérence. En fait, vous pouvez sélectionner la cellule B1 et vous référer à B1, sélectionner la cellule C1 et vous référer à C1, etc. L'essentiel est que la cellule référencée devrait être la cellule active .

Pour vérifier si votre formule nommée est correcte, sélectionnez n'importe quelle cellule de votre feuille de calcul, ouvrez le gestionnaire de noms et voyez à quelle cellule la formule pointe. S'il fait référence à la cellule actuellement sélectionnée, la formule est correcte. Sinon, vous devez modifier la référence dans le premier argument.

Dans la capture d'écran ci-dessous, la cellule A7 est sélectionnée, ce qui signifie qu'une formule nommée devrait avoir A7 dans le premier argument. Le deuxième argument ($ a 2 $) fait référence au regex - cette référence est censée rester constante, elle est donc verrouillée avec le signe $.

Ignorer l'option vide sélectionnée

Lors de la configuration d'une règle de validation des données personnalisée, il est important de ne pas sélectionner la case à cocher Ignore Blank . Sinon, la règle ne fonctionnera pas pour la raison suivante:

Si une correspondance n'est pas trouvée, la fonction regexpmatch renvoie false. Avec l'option Ignore Blank sélectionnée, False équivaut à vide et est ignoré.

Une solution alternative indique explicitement que la formule doit retourner vrai:

=RegExpMatch(…)=TRUE

C'est comment faire la validation des données dans Excel en utilisant des expressions régulières. Je vous remercie d'avoir lu et j'ai hâte de vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Exemples de validation des données Regex (fichier .xlsm)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!