Formule générique
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Sommaire
Pour utiliser la validation des données pour autoriser une liste de caractères spécifiques uniquement, vous pouvez utiliser une formule matricielle assez compliquée basée sur les fonctions COUNT, MATCH et LEN. Dans l'exemple illustré, la validation des données est appliquée avec cette formule:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
où "autorisé" est la plage nommée D5: D11.
Explication
Travaillant de l'intérieur vers l'extérieur, la fonction MID est utilisée pour générer un tableau à partir du texte entré en B5 avec cet extrait:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
expliqué en détail ici. Le résultat est un tableau comme celui-ci:
("A";"A";"A";"-";"1";"1";"1")
qui entre dans MATCH comme valeur de recherche. Pour le tableau de recherche, nous utilisons la plage nommée "autorisée", concaténée en une chaîne vide (""):
allowed&""
La concaténation convertit tous les nombres en chaînes, de sorte que nous faisons correspondre des pommes à des pommes. Le résultat est un tableau comme celui-ci:
("A";"B";"C";"1";"2";"3";"-")
Le dernier argument de MATCH, match_type est mis à zéro pour forcer une correspondance exacte. Parce que nous donnons plusieurs valeurs de recherche à MATCH, nous récupérons un tableau avec plusieurs résultats:
(1;1;1;7;4;4;4)
Chaque nombre de ce tableau représente une correspondance. Si aucune correspondance n'est trouvée pour un caractère, le tableau contiendra une erreur # N / A.
Enfin, la fonction COUNT est utilisée pour compter les nombres dans le tableau de résultats, qui est comparé à un nombre de tous les caractères de la cellule calculé avec la fonction LEN. Lorsque MATCH trouve une correspondance pour tous les caractères, les décomptes sont égaux, la formule renvoie VRAI et la validation des données réussit. Si MATCH ne trouve aucun caractère correspondant, il renvoie # N / A au lieu d'un nombre. Dans ce cas, les décomptes ne correspondent pas et la validation des données échoue.
Remarque: cette formule repose sur la force brute pour faire le travail. Si vous avez une meilleure approche, veuillez laisser un commentaire ci-dessous.
