Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Applications Microsoft Office Excel Validation NAS canadien sans VBA

Validation NAS canadien sans VBA

28 réponses
Avatar
Denys
Bonjour =E0 tous,

Existe-t-il une formule pour valider un num=E9ro d'assurance sociale
canadien ? Je connais la version VBA, mais cette fois j'aimerais avoir
une formule sans VBA, autrement dit, si j'entre un num=E9ro en B1, je
mettrais la formule en C1 qui dirait si le no est valide ou pas....

Merci pour votre temps....et Joyeuses F=EAtes....

Denys
Signaler un problème avec ce contenu

8 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2 3
Avatar
Maude Este
Bonsour®

bcar44 a écrit
Notons tout de même que si l'onglet Feuil2 est fort bien présenté (mais
avec beaucoup d'étapes intermédiaires), l'onglet Feuil1 lui n'est pas
correct :



Oupsss!!!!
cette feuille peut-etre supprimée (phase de mise au point abandonnée)

- la fonction "nas" a été mal traduite depuis un autre langage, il est
nécessaire de la corriger pour qu'elle fonctionne.



tout a fait voici le code original
;o)))
'----- http://fr.wikipedia.org/wiki/Formule_de_Luhn
function checkLuhn(string purportedCC) : boolean {
int sum := 0
int nDigits := length(purportedCC)
int parity := nDigits AND 1
for i from nDigits-1 to 0 {
int digit := integer(purportedCC[i])
if (i AND 1) = parity
digit := digit × 2
if digit > 9
digit := digit - 9
sum := sum + digit
}
return (sum % 10) = 0
}
Avatar
michdenis
Et pour ces 3 numéros NAS en Feuil1 dans la colonne A:A
Le résultat attendu n'est pas au rendez-vous ...

46454286 ->retourne vrai même si 8 chiffres seulement
972487110 -> retourne faux, si j'utilise ma fonction et la formule de Bcar, la réponse est vrai
972487112 -> retourne vrai, résultat attendu faux

Je n'ai pas poussé plus loin le pourquoi de la chose !


MichD
--------------------------------------------
"michdenis" a écrit dans le message de groupe de discussion : ifcmb3$8k1$

Bonjour Modeste,

Intéressant si tu veux devenir un fonctionnaire canadien...
;-)

MichD
--------------------------------------------

"Modeste" a écrit dans le message de groupe de discussion : 4d19cc85$0$21683$

Bonsour®

"bcar" a écrit
Je ne crois pas qu'il y ait de séparateurs pour un numéro NAS.
Il doit par contre exister un formatage spécial Excel comme le formatage
"Numéro de sécurité sociale.

Le 27/12/2010 22:07, michdenis a écrit :
à partir de la suggestion de Bcar, on pourrait utiliser cette formule

=SI(ARRONDI((SOMMEPROD(SI((MOD({1;2;3;4;5;6;7;8;9};2)=0)+(NBCAR(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";""))=9)=2;MOD(2*STXT(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";"");{1;2;3;4;5;6;7;8;9};1);10)+ENT(2*STXT(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";"");{1;2;3;4;5;6;7;8;9};1)/10);0+STXT(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";"");{1;2;3;4;5;6;7;8;9};1)))/10);5)-ARRONDI((SOMMEPROD(SI(MOD({1;2;3;4;5;6;7;8;9};2)=0;MOD(2*STXT(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";"");{1;2;3;4;5;6;7;8;9};1);10)+ENT(2*STXT(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";"");{1;2;3;4;5;6;7;8;9};1)/10);0+STXT(SUBSTITUE(SUBSTITUE($A$1;"-";"");"
";"");{1;2;3;4;5;6;7;8;9};1)))/10);0)=0;VRAI;FAUX)





pour le fun ;o)))
http://www.cijoint.fr/cjlink.php?file=cj201012/cij5up4dsc.xls
Avatar
Maude Este
Bonsour®

"michdenis" a écrit
Et pour ces 3 numéros NAS en Feuil1 dans la colonne A:A
Le résultat attendu n'est pas au rendez-vous ...

46454286 ->retourne vrai même si 8 chiffres seulement
972487110 -> retourne faux, si j'utilise ma fonction et la formule de Bcar, la
réponse est vrai
972487112 -> retourne vrai, résultat attendu faux

Je n'ai pas poussé plus loin le pourquoi de la chose !



;o)))
je ne pousserai pas non plus mes investigation plus loin !!!
la proposition fournie est adaptée d'une doc IBM censée etre valable pour 16
digits

mais pour le fun : voici d'autres ressources :
http://en.wikipedia.org/wiki/Luhn_algorithm

bons amusements !!!
Avatar
michdenis
46454286 ->retourne vrai même si 8 chiffres seulement



Ce n'est quand même pas IBM qui dicte les 9 chiffres obligatoires
que doit contenir un NAS canadien...
;-)


MichD
--------------------------------------------
"Maude Este" a écrit dans le message de groupe de discussion : ifcp89$f2h$

Bonsour®

"michdenis" a écrit
Et pour ces 3 numéros NAS en Feuil1 dans la colonne A:A
Le résultat attendu n'est pas au rendez-vous ...

46454286 ->retourne vrai même si 8 chiffres seulement
972487110 -> retourne faux, si j'utilise ma fonction et la formule de Bcar, la
réponse est vrai
972487112 -> retourne vrai, résultat attendu faux

Je n'ai pas poussé plus loin le pourquoi de la chose !



;o)))
je ne pousserai pas non plus mes investigation plus loin !!!
la proposition fournie est adaptée d'une doc IBM censée etre valable pour 16
digits

mais pour le fun : voici d'autres ressources :
http://en.wikipedia.org/wiki/Luhn_algorithm

bons amusements !!!
Avatar
bcar
Bonjour,

grace au brainstorming :) avec michdenis et michel ou sam je vous
propose la formule (matricielle) suivante pour faire le job

en A1 le numéro à valider
{=SOMME(CHOISIR(STXT(A1;{1;2;3;4;5;6;7;8;9};1)+{11;1;11;1;11;1;11;1;11};0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9))}

ou pour finaliser
{=SI(MOD(SOMME(CHOISIR(STXT(A1;{1;2;3;4;5;6;7;8;9};1)+{11;1;11;1;11;1;11;1;11};0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9));10)=0;VRAI;FAUX)}

Avec une seule référence à la formule à valider ce qui permet sans trop
alourdir la formule de faire un peu de nettoyage du genre EPURAGE(A1) ou
SUBSTITUTE... ou utiliser REPT ou STXT pour mettre le numéro à la
longueur voulue...

Le 28/12/2010 15:00, michdenis a écrit :
46454286 ->retourne vrai même si 8 chiffres seulement



Ce n'est quand même pas IBM qui dicte les 9 chiffres obligatoires
que doit contenir un NAS canadien...
;-)


MichD
--------------------------------------------
"Maude Este" a écrit dans le message de groupe de discussion : ifcp89$f2h$

Bonsour®

"michdenis" a écrit
Et pour ces 3 numéros NAS en Feuil1 dans la colonne A:A
Le résultat attendu n'est pas au rendez-vous ...

46454286 ->retourne vrai même si 8 chiffres seulement
972487110 -> retourne faux, si j'utilise ma fonction et la formule de Bcar, la
réponse est vrai
972487112 -> retourne vrai, résultat attendu faux

Je n'ai pas poussé plus loin le pourquoi de la chose !



;o)))
je ne pousserai pas non plus mes investigation plus loin !!!
la proposition fournie est adaptée d'une doc IBM censée etre valable pour 16
digits

mais pour le fun : voici d'autres ressources :
http://en.wikipedia.org/wiki/Luhn_algorithm

bons amusements !!!

Avatar
michdenis
Ce sont des propositions intéressantes

sauf que :
si la cellule contient plus de 9 chiffres, on peut obtenir "Vrai" comme réponse
si les 9 premiers chiffres représentent un NAS.

Si l'usager entre par erreur un "O" plutôt qu'un "0", la cellule affiche #VALUE!
plutôt que Faux ce qui peut être déstabilisant pour un usager pas trop familier
avec Excel.

Je ne dis pas que l'on ne peut pas inclure ces éléments dans une formule, mais
c'est le type de chose dont une fonction personnalisée se charge plus facilement.

MichD
--------------------------------------------
"bcar" a écrit dans le message de groupe de discussion : ifk512$5um$

Bonjour,

grace au brainstorming :) avec michdenis et michel ou sam je vous
propose la formule (matricielle) suivante pour faire le job

en A1 le numéro à valider
{=SOMME(CHOISIR(STXT(A1;{1;2;3;4;5;6;7;8;9};1)+{11;1;11;1;11;1;11;1;11};0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9))}

ou pour finaliser
{=SI(MOD(SOMME(CHOISIR(STXT(A1;{1;2;3;4;5;6;7;8;9};1)+{11;1;11;1;11;1;11;1;11};0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9));10)=0;VRAI;FAUX)}

Avec une seule référence à la formule à valider ce qui permet sans trop
alourdir la formule de faire un peu de nettoyage du genre EPURAGE(A1) ou
SUBSTITUTE... ou utiliser REPT ou STXT pour mettre le numéro à la
longueur voulue...

Le 28/12/2010 15:00, michdenis a écrit :
46454286 ->retourne vrai même si 8 chiffres seulement



Ce n'est quand même pas IBM qui dicte les 9 chiffres obligatoires
que doit contenir un NAS canadien...
;-)


MichD
--------------------------------------------
"Maude Este" a écrit dans le message de groupe de discussion : ifcp89$f2h$

Bonsour®

"michdenis" a écrit
Et pour ces 3 numéros NAS en Feuil1 dans la colonne A:A
Le résultat attendu n'est pas au rendez-vous ...

46454286 ->retourne vrai même si 8 chiffres seulement
972487110 -> retourne faux, si j'utilise ma fonction et la formule de Bcar, la
réponse est vrai
972487112 -> retourne vrai, résultat attendu faux

Je n'ai pas poussé plus loin le pourquoi de la chose !



;o)))
je ne pousserai pas non plus mes investigation plus loin !!!
la proposition fournie est adaptée d'une doc IBM censée etre valable pour 16
digits

mais pour le fun : voici d'autres ressources :
http://en.wikipedia.org/wiki/Luhn_algorithm

bons amusements !!!

Avatar
bcar
Bon réveillon pour les derniers forumeurs du jour.

Je suis conscient des limites de la méthode
Mais cela répond à la demande du posteur (du moins ce que j'en ai
compris :) )

Cependant vérifier qu'il y a 9 chiffres n'est pas bien compliqué, un
SI(ET(ESTNUM(A1*1);NBCAR(A1)=9);la_formule_proposée;"un message d'erreur
(ou faux)") fera très bien l'affaire

Par contre on pourra sûrement encore trouver des cas particuliers de
saisie qui poseront problème et la formule peut devenir à rallonge.
On aura aussi moins de souplesse pour proposer des
corrections/intervention de l'utilisateur.

En même temps on s'épargne le VBA et donc entre autre toutes les
politiques de sécurité

Il est toujours intéressant de pousser un peu ce genre de problème dans
ses retranchements, cela permet de (re)découvrir plein de bonnes idées.


Le 31/12/2010 12:43, michdenis a écrit :
Ce sont des propositions intéressantes

sauf que :
si la cellule contient plus de 9 chiffres, on peut obtenir "Vrai" comme réponse
si les 9 premiers chiffres représentent un NAS.

Si l'usager entre par erreur un "O" plutôt qu'un "0", la cellule affiche #VALUE!
plutôt que Faux ce qui peut être déstabilisant pour un usager pas trop familier
avec Excel.

Je ne dis pas que l'on ne peut pas inclure ces éléments dans une formule, mais
c'est le type de chose dont une fonction personnalisée se charge plus facilement.

MichD
--------------------------------------------
"bcar" a écrit dans le message de groupe de discussion : ifk512$5um$

Bonjour,

grace au brainstorming :) avec michdenis et michel ou sam je vous
propose la formule (matricielle) suivante pour faire le job

en A1 le numéro à valider
{=SOMME(CHOISIR(STXT(A1;{1;2;3;4;5;6;7;8;9};1)+{11;1;11;1;11;1;11;1;11};0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9))}

ou pour finaliser
{=SI(MOD(SOMME(CHOISIR(STXT(A1;{1;2;3;4;5;6;7;8;9};1)+{11;1;11;1;11;1;11;1;11};0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9));10)=0;VRAI;FAUX)}

Avec une seule référence à la formule à valider ce qui permet sans trop
alourdir la formule de faire un peu de nettoyage du genre EPURAGE(A1) ou
SUBSTITUTE... ou utiliser REPT ou STXT pour mettre le numéro à la
longueur voulue...

Le 28/12/2010 15:00, michdenis a écrit :
46454286 ->retourne vrai même si 8 chiffres seulement



Ce n'est quand même pas IBM qui dicte les 9 chiffres obligatoires
que doit contenir un NAS canadien...
;-)


MichD
--------------------------------------------
"Maude Este" a écrit dans le message de groupe de discussion : ifcp89$f2h$

Bonsour®

"michdenis" a écrit
Et pour ces 3 numéros NAS en Feuil1 dans la colonne A:A
Le résultat attendu n'est pas au rendez-vous ...

46454286 ->retourne vrai même si 8 chiffres seulement
972487110 -> retourne faux, si j'utilise ma fonction et la formule de Bcar, la
réponse est vrai
972487112 -> retourne vrai, résultat attendu faux

Je n'ai pas poussé plus loin le pourquoi de la chose !



;o)))
je ne pousserai pas non plus mes investigation plus loin !!!
la proposition fournie est adaptée d'une doc IBM censée etre valable pour 16
digits

mais pour le fun : voici d'autres ressources :
http://en.wikipedia.org/wiki/Luhn_algorithm

bons amusements !!!

Avatar
claudevba
Le jeudi 23 Décembre 2010 à 15:59 par Denys :
Bonjour à tous,

Existe-t-il une formule pour valider un numéro d'assurance sociale
canadien ? Je connais la version VBA, mais cette fois j'aimerais avoir
une formule sans VBA, autrement dit, si j'entre un numéro en B1, je
mettrais la formule en C1 qui dirait si le no est valide ou pas....

Merci pour votre temps....et Joyeuses Fêtes....

Denys


Bonjour !

Voici une formule sans VBA, en supposant que le NAS est à la cellule E8:

=SI(DROITE(STXT(E15;1;1)+STXT(E15;3;1)+STXT(E15;5;1)+STXT(E15;7;1)+STXT(E15;9;1)+STXT(2*STXT(E15;2;1)&"0";1;1)+STXT(2*STXT(E15;2;1)&"0";2;1)+STXT(2*STXT(E15;4;1)&"0";1;1)+STXT(2*STXT(E15;4;1)&"0";2;1)+STXT(2*STXT(E15;6;1)&"0";1;1)+STXT(2*STXT(E15;6;1)&"0";2;1)+STXT(2*STXT(E15;8;1)&"0";1;1)+STXT(2*STXT(E15;8;1)&"0";2;1);1)="0";VRAI;FAUX)

Ça retourne VRAI si le NAS est valide et FAUX s'il ne l'est pas.

La formule fait l'addition de chacune des positions impairs.

Pour les positions pairs, on multiplie par deux et ajoute "0" à la fin, on additionne ensuite les deux premières positions du résultat.

Exemple si c'est 6, ça donne "120" donc on additionne 1 et 2 donc 3. Si c'est 4, ça donne "80", donc 8+0 donc 8.

Pour valider si la somme total est une dizaine on valide si le dernier caractère est un "0". Si c'est le cas on retourne la valeur VRAI.

Bonne journée!
1 2 3