Validation NAS canadien sans VBA
Denys
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
michdenis
L'idéal est une procédure comme ceci dans le module de la feuille
où l'action se déroule. Tu adaptes Range("C:C") de la procédure
pour l'adresse de ta plage de cellules de ton application.
Ce code permet la saisie du code postal en minuscule et sans
espace après les 3 et 4 nièmes caractères. Si au lieu d'un message et
d'un format de cellule, tu veux utiliser une autre colonne pour
inscrire vrai ou faux, cela peut s'adapter !
'--------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rg As Range
Set Rg = Intersect(Target, Range("C:C"))
Application.EnableEvents = False
If Not Rg Is Nothing Then
For Each c In Rg
If c <> "" Then
c.Value = UCase(Application.Trim(c))
If c.Value Like "[A-Z][0-9][A-Z] [0-9][A-Z][0-9]" Or _
c.Value Like "[A-Z][0-9][A-Z][0-9][A-Z][0-9]" Then
c.Value = Left(c, 3) & " " & Right(c, 3)
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
Else
MsgBox "le format du code postal n'est pas canadien."
c.Interior.ColorIndex = 3
c.Font.ColorIndex = 2
End If
Else
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
End If
Next
End If
Application.EnableEvents = True
End Sub
'--------------------------------------------
MichD
--------------------------------------------
"Denys" a écrit dans le message de groupe de discussion :
3cef2993-d0cb-4874...@n10g2000yqd.googlegroups.com...
Denys
Merci bien mais c'est pour les numéros d'assurance sociale, pas les
codes postaux !! :-)
En fait, je crois que ce serait une formule Mod 10 ou quelque chose
dans le genre...
Bonne journée
Denys
michdenis
POUR VALIDER UN NAS, PROC�DER DE LA FA�ON SUIVANTE :
-- INSCRIRE le NAS sur une feuille de papier, comme suit: 044-096-857;
-- COCHER le deuxi�me, quatri�me, sixi�me et huiti�me chiffre;
-- INSCRIRE le NAS de nouveau, mais cette fois en multipliant par deux
les chiffres mentionn�s, comme suit : 084-0912-8107.
LORSQUE CETTE MULTIPLICATION DONNE UN NOMBRE � DEUX CHIFFRES, ALORS :
-- ADDITIONNER ces deux chiffres pour n'en faire qu'un seul,
-- ET additionner l'ensemble de ces chiffres, soit : 0+8+4+0+9+1+2+8+1+0+7 = 40.
SI LE TOTAL QUI EN R�SULTE EST UN MULTIPLE DE DIX, LE NAS EST VALIDE.
Donc, le NAS ci-dessus est valide, puisque le total est 40.
Si tu as besoin d'une fonction personnalis�e pour faire cela, je serai de retour
en PM...et j'y regarderai de plus pr�s si j'ai le temps.
MichD
--------------------------------------------
"Denys" a �crit dans le message de groupe de discussion :
489f5f9e-076a-474b...@s18g2000vby.googlegroups.com...
Bonjour Denis,
Merci bien mais c'est pour les num�ros d'assurance sociale, pas les
codes postaux !! :-)
En fait, je crois que ce serait une formule Mod 10 ou quelque chose
dans le genre...
Bonne journ�e
Denys
isabelle
en attendant que Denis revienne,
Sub Test_NAS()
x = Application.Substitute(Range("A1"), "-", "")
For i = 1 To Len(x)
Select Case i
Case 2, 4, 6, 8
y = Mid(x, i, 1) * 2
If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
r = r + y
Case Else
r = r + CDbl(Mid(x, i, 1))
End Select
Next
MsgBox r
End Sub
passe de bonne fête,
isabelle
Le 2010-12-23 11:10, michdenis a écrit :
> POUR VALIDER UN NAS, PROCÉDER DE LA FAÇON SUIVANTE :
> -- INSCRIRE le NAS sur une feuille de papier, comme suit: 044-096-857;
> -- COCHER le deuxième, quatrième, sixième et huitième chiffre;
> -- INSCRIRE le NAS de nouveau, mais cette fois en multipliant par deux
> les chiffres mentionnés, comme suit : 084-0912-8107.
>
> LORSQUE CETTE MULTIPLICATION DONNE UN NOMBRE À DEUX CHIFFRES, ALORS :
> -- ADDITIONNER ces deux chiffres pour n'en faire qu'un seul,
> -- ET additionner l'ensemble de ces chiffres, soit : 0+8+4+0+9+1+2+8+1+0+7 = 40.
>
> SI LE TOTAL QUI EN RÉSULTE EST UN MULTIPLE DE DIX, LE NAS EST VALIDE.
> Donc, le NAS ci-dessus est valide, puisque le total est 40.
>
> Si tu as besoin d'une fonction personnalisée pour faire cela, je serai de retour
> en PM...et j'y regarderai de plus près si j'ai le temps.
isabelle
Function nas(cellule As Range) As String
x = Application.Substitute(cellule, "-", "")
For i = 1 To Len(x)
Select Case i
Case 2, 4, 6, 8
y = Mid(x, i, 1) * 2
If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
r = r + y
Case Else
r = r + CDbl(Mid(x, i, 1))
End Select
Next
If r Mod 10 = 0 Then
nas = "VRAI"
Else
nas = "FAUX"
End If
End Function
isabelle
Denys
Voici où j'en suis....
Supposons le no suivant...en D19 '044096857, faut mettre un
apostrophe devant le 0...
en E 19 la formule suivante:
=(MID(D19,1,1)&MID(D19,2,1)*2&MID(D19,3,1)&MID(D19,4,1)*2&MID(D19,5,1)&(MID(D19,6,1)*2&MID(D19,7,1)&MID(D19,8,1)*2&MID(D19,9,1)))
en F 19 la formule suivante:
=MID(E19,1,1)+MID(E19,2,1)+MID(E19,3,1)+MID(E19,4,1)+MID(E19,5,1)+MID(E19,6,1)+MID(E19,7,1)+MID(E19,8,1)+MID(E19,9,1)+MID(E19,10,1)+MID(E19,11,1)
La réponse est 40.....
Je ne suis pas certain qu'elle est infaillible cependant, car il n'y
aura pas nécessairement toujours 11 chiffres en E19
Denys
Isabelle, merci pour les bons voeux.....j'essaie de voir sans VBA....
michdenis
tes NAS, tu n'auras pas de problème avec les 0
Dans un module standard, copie cette fonction personnalisée.
Et dans une cellule à côté de ladite colonne contenant des NAS
tu insères la formule =EstNas(A1) en supposant que A1 contient
un NAS. La fonction retourne Vrai ou Faux selon le cas.
Tu peux tester la fonction plus à fond...mais ça l'air de fonctionner !
'--------------------------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer
N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'Vérification si le résultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'--------------------------------------------------
MichD
--------------------------------------------
"Denys" a écrit dans le message de groupe de discussion :
d1e65460-f2c8-4cbe...@k30g2000vbn.googlegroups.com...
michdenis
'-----------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer
If IsEmpty(Rg) Then EstNas = False: Exit Function
N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
If Not IsNumeric(N) Then EstNas = False: Exit Function
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'V�rification si le r�sultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'-----------------------------------
MichD
--------------------------------------------
"michdenis" a �crit dans le message de groupe de discussion : if09ju$mug$1...@speranza.aioe.org...
Si tu formates ta colonne au format Texte avant d'y copier
tes NAS, tu n'auras pas de probl�me avec les 0
Dans un module standard, copie cette fonction personnalis�e.
Et dans une cellule � c�t� de ladite colonne contenant des NAS
tu ins�res la formule =EstNas(A1) en supposant que A1 contient
un NAS. La fonction retourne Vrai ou Faux selon le cas.
Tu peux tester la fonction plus � fond...mais �a l'air de fonctionner !
'--------------------------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer
N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'V�rification si le r�sultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'--------------------------------------------------
MichD
--------------------------------------------
"Denys" a �crit dans le message de groupe de discussion :
d1e65460-f2c8-4cbe...@k30g2000vbn.googlegroups.com...
Salut denis,
Voici o� j'en suis....
Supposons le no suivant...en D19 '044096857, faut mettre un
apostrophe devant le 0...
en E 19 la formule suivante:
=(MID(D19,1,1)&MID(D19,2,1)*2&MID(D19,3,1)&MID(D19,4,1)*2&MID(D19,5,1)&(MID(D19,6,1)*2&MID(D19,7,1)&MID(D19,8,1)*2&MID(D19,9,1)))
en F 19 la formule suivante:
=MID(E19,1,1)+MID(E19,2,1)+MID(E19,3,1)+MID(E19,4,1)+MID(E19,5,1)+MID(E19,6,1)+MID(E19,7,1)+MID(E19,8,1)+MID(E19,9,1)+MID(E19,10,1)+MID(E19,11,1)
La r�ponse est 40.....
Je ne suis pas certain qu'elle est infaillible cependant, car il n'y
aura pas n�cessairement toujours 11 chiffres en E19
michdenis
'---------------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer
'Si la cellule est vide...
If IsEmpty(Rg) Then EstNas = False: Exit Function
N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
'si la cha�ne de caract�res n'est pas num�rique
If Not IsNumeric(N) Then EstNas = False: Exit Function
'si la cha�ne de caract�res est diff�rente de 9
If Len(N) <> 9 Then EstNas = False: Exit Function
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'V�rification si le r�sultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'---------------------------------------
MichD
--------------------------------------------
Denys
Y a vraiment pas moyen sans VBA ???
N'empêche que je vais garder cette formule même si j'avais autre
chose...
Merci beaucoup pour ton temps....
Denys
Tatanka
Comment se faire du fric en gageant qu'on
peut deviner le dernier chiffre d'un numéro
d'assurance sociale (au Québec en tous cas) :
Sub Devine_Le_Dernier()
Dim v(1 To 8) As Integer
n = InputBox("Entrez les huit premiers chiffres" & _
Chr(13) & "de votre numéro d'assurance-sociale :")
For i = 1 To 8
If i Mod 2 = 0 Then
v(i) = 2 * Mid(n, i, 1)
If v(i) >= 10 Then
v(i) = 1 + Right(v(i), 1)
End If
Else
v(i) = Mid(n, i, 1)
End If
s = s + v(i)
Next i
MsgBox "Le dernier chiffre DOIT ÊTRE un " & _
10 * (Int(s / 10) + 1) - s & ".", _
vbInformation, "Chiffre-preuve"
End Sub
Joyeux Noël !
Serge
bcar
Soit ton numéro en A1
la formule suivante devrait répondre à ton besoin :
=SOMMEPROD(SI(MOD({1;2;3;4;5;6;7;8;9};2)=0;
MOD(2*STXT($A$1;{1;2;3;4;5;6;7;8;9};1);10)
+ENT(2*STXT($A$1;{1;2;3;4;5;6;7;8;9};1)/10);
0+STXT($A$1;{1;2;3;4;5;6;7;8;9};1)))
si le chiffre obtenu et divisible par 10 c'est un numéro valide
(utiliser la fonction MOD)
Apparemment, il n'y a même pas besoin du sommeprod, un simple somme
suffit...
LSteph
Désolé ces numéros NAS cela doit être typiquement .CA selon le retour
direct de google..
sinon j'aurais au moins essayé.
Alors je viens quand même intervenir ici car
il me semble que le Monsieur vous demande
depuis le début s'il existe une solution sans VBA.
Cordialement.
--
LSteph
On 23 déc, 18:54, isabelle <i...@v.org> wrote:
> pour une formulle perso,
>
> Function nas(cellule As Range) As String
> x = Application.Substitute(cellule, "-", "")
> For i = 1 To Len(x)
> Select Case i
> Case 2, 4, 6, 8
> y = Mid(x, i, 1) * 2
> If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
> r = r + y
> Case Else
> r = r + CDbl(Mid(x, i, 1))
> End Select
> Next
> If r Mod 10 = 0 Then
> nas = "VRAI"
> Else
> nas = "FAUX"
> End If
> End Function
>
> isabelle
>
> Le 2010-12-23 11:48, isabelle a crit :
>
>
>
> > bonjour Denys,
>
> > en attendant que Denis revienne,
>
> > Sub Test_NAS()
> > x = Application.Substitute(Range("A1"), "-", "")
> > For i = 1 To Len(x)
> > Select Case i
> > Case 2, 4, 6, 8
> > y = Mid(x, i, 1) * 2
> > If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
> > r = r + y
> > Case Else
> > r = r + CDbl(Mid(x, i, 1))
> > End Select
> > Next
> > MsgBox r
> > End Sub
>
> > passe de bonne f te,
> > isabelle
>
> > Le 2010-12-23 11:10, michdenis a crit :
> >> POUR VALIDER UN NAS, PROC DER DE LA FA ON SUIVANTE :
> >> -- INSCRIRE le NAS sur une feuille de papier, comme suit: 044-096-857;
> >> -- COCHER le deuxi me, quatri me, sixi me et huiti me chiffre;
> >> -- INSCRIRE le NAS de nouveau, mais cette fois en multipliant par deux
> >> les chiffres mentionn s, comme suit : 084-0912-8107.
>
> >> LORSQUE CETTE MULTIPLICATION DONNE UN NOMBRE DEUX CHIFFRES, ALORS :
> >> -- ADDITIONNER ces deux chiffres pour n'en faire qu'un seul,
> >> -- ET additionner l'ensemble de ces chiffres, soit :
> >> 0+8+4+0+9+1+2+8+1+0+7 = 40.
>
> >> SI LE TOTAL QUI EN R SULTE EST UN MULTIPLE DE DIX, LE NAS EST VALIDE.
> >> Donc, le NAS ci-dessus est valide, puisque le total est 40.
>
> >> Si tu as besoin d'une fonction personnalis e pour faire cela, je serai
> >> de retour
> >> en PM...et j'y regarderai de plus pr s si j'ai le temps.- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -
michdenis
à 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)
Exemple : 244-244-244
Si l'usager tape une autre chose que 9 chiffres séparés par un "-" ou un espace, la fonction Retourne #Valeur
Autrement, la formule évalue la chaîne du NAS correctement et renvoie un Vrai ou Faux selon le cas.
En d'autres termes, la chaine peut avoir
un nombre indéterminé de "-" ou " "
elle doit avoir obligatoirement 9 chiffres
Si la cellule contient plus de 9 caractères (chiffre ou lettre) elle renvoie False
Tout autre caractère provoque une erreur représenté par #VALEUR!
Pour la compréhension, je préfère de loin, une fonction personnalisée!
MichD
--------------------------------------------
"LSteph" a écrit dans le message de groupe de discussion :
7100fdb5-1dcd-4884...@k25g2000vbl.googlegroups.com...
LSteph
> Pour la compréhension
En effet, au vu de cela plus que pour la compréhension, vu l'alambiquage
d'alambic, je comprends mieux ,
sans VBA possible mais très lourd...
une sub pour ce genre de cas, prendra une fois pour toute quand on le
souhaite le temps d'obtenir la subsitution,
sinon gare aux recalculs si une telle formule même sous forme de fonction
personalisée doit oeuvrer sur de nombreuses lignes d'un tableau .
> Si l'usager tape
a fortiori, si c'est juste sur la frappe de l'utilisateur la première forme
que tu envisageais private sub, limiterait avantageusement à la cible.
--
LSteph
"michdenis" <mich...@hotmail.com> a écrit dans le message de
news:ifav6l$i58$1...@speranza.aioe.org...
bcar
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.
En admettant qu'il y ait des séparateurs, il y a (au moins) 2 autres
solutions (il est vrai que déjà ma formule est un peu longue alors avec
des substitute inclus dedans...) :
- une dans l'esprit de michdenis, ou on passerait par une cellule
intermédiaire pour convertir le nas dans un format sans séparateur
ex : A1 = 123-456-789
A2 = substitute(A1;"-";"")
A3 = la formule que j'ai proposé plus bas.
- une ou il faudrait modifier un peu les matrices de la formule que j'ai
donné de manière à ce que les séparateurs n'influent pas sur les calculs
Sinon j'ai aussi essayé de commettre un truc plus court et plus pratique
à manipuler (une seule référence à la cellule à vérifier(A1)) avec
=SOMMEPROD(INDEX({0\0;2\1;4\2;6\3;8\4;1\5;3\6;5\7;7\8;9\9};1+STXT($A$1;{1;2;3;4;5;6;7;8;9};1);1+MOD({1;2;3;4;5;6;7;8;9};2)))
ou encore
=SOMMEPROD(INDEX({0;2;4;6;8;1;3;5;7;9;0;1;2;3;4;5;6;7;8;9};1+STXT($A$1;{1;2;3;4;5;6;7;8;9};1)+SI(MOD({1;2;3;4;5;6;7;8;9};2)=0;0;10)))
mais il y a un truc qui m'échappe car ca ne fonctionne pas (en fait ça
fait juste la somme de la matrice présentée dans index).
Si quelqu'un à 2 minutes pour regarder...
en fait je ne vois pas pourquoi avec
A1=1
A2=2
A3=3
B4=sommeprod(index({2;3;4;5};A1:A3))
ne fonctionne pas (mais en formule matricielle
(je m'attendrai à avoir 2+3+4=9)
Le 27/12/2010 22:07, michdenis a écrit :
> Bonjour,
>
> à 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)
>
> Exemple : 244-244-244
> Si l'usager tape une autre chose que 9 chiffres séparés par un "-" ou un espace, la fonction Retourne #Valeur
> Autrement, la formule évalue la chaîne du NAS correctement et renvoie un Vrai ou Faux selon le cas.
>
> En d'autres termes, la chaine peut avoir
> un nombre indéterminé de "-" ou " "
> elle doit avoir obligatoirement 9 chiffres
> Si la cellule contient plus de 9 caractères (chiffre ou lettre) elle renvoie False
> Tout autre caractère provoque une erreur représenté par #VALEUR!
Oui la fonction personnalisée c'est mieux dans la plupart des cas (plus
lisible, possibilité d'y adjoindre des commentaires, facilité
d'implémentation, vérifications...)
Mais l'exercice est intéressant et il arrive parfois de devoir produire
des classeurs sans macros et il est toujours bon de savoir faire un
maximum de choses sans celles-ci
Modeste
"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
michdenis
Intéressant si tu veux devenir un fonctionnaire canadien...
;-)
MichD
--------------------------------------------
"Modeste" a écrit dans le message de groupe de discussion : 4d19cc85$0$21683$426a...@news.free.fr...
michdenis
Effectivement, si les cellules ont un format spécial NAS canadien
on peut enlever de la formule les "Substitue" ce qui diminue la
longueur de la formule.
Une particularité à propos de ce format, c'est qu'il tient compte des
derniers 9 chiffres saisis.
Exemple, on saisit : 012 345 678
si on ajoute un chiffre de trop, un 9 par exemple, le contenu de la
cellule retient : 123 456 789 . En supposant que ce chiffre donne
aussi un résultat vrai, cela pourrait être une source d'erreur....!!!
Pour ce qui est de la formule, je vais laisser Lsteph te faire ses suggestions
il semble friand de formules ! ;-)
MichD
--------------------------------------------
"bcar" a écrit dans le message de groupe de discussion : ifc84u$mka$1...@writer.imaginet.fr...
bcar
avec beaucoup d'�tapes interm�diaires), l'onglet Feuil1 lui n'est pas
correct :
- la fonction "nas" a �t� mal traduite depuis un autre langage, il est
n�cessaire de la corriger pour qu'elle fonctionne.
- la formule =DROITE(2*HEXDEC(DECHEX(ENT(A1/16)))+A1;1)="0" n'est pas
correcte non plus (par exemple avec 110 000 000). Je ne vois pas trop ce
que le cr�ateur de la formule a voulu faire
(HEXDEC(DECHEX(X))=X donc je ne vois pas l'int�r�t de cette op�ration)
Le 28/12/2010 12:39, Modeste a �crit :
> 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
Maude Este
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
}
michdenis
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$1...@speranza.aioe.org...
Maude Este
"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 !!!
michdenis
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$1...@speranza.aioe.org...
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
bcar
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$1...@speranza.aioe.org...
>
> 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
michdenis
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$1...@writer.imaginet.fr...
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$1...@speranza.aioe.org...
>
> 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
bcar
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 :
claudevba
> Bonjour =E0 tous,
>
> Existe-t-il une formule pour valider un num=E9ro d'assurance sociale
>
>
> 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!
albert
suite à la fin
(aioe refuse la ligne trop longue dans ma réponse)
"claudevba" <nospam_clau...@desjardins.com.invalid> a écrit dans le
message de news:PpednfLjiPQMbOfL...@giganews.com...
>> 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
> Bonjour !
>
> Voici une formule sans VBA, en supposant que le NAS est à la cellule E8:
>
>
> 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!
Quel est le format du NAS, texte comme 123 456 789 ou 123456789 ?
Format Standard ou Numérique ?
Je les ai tous essayés et j'ai toujours #Valeur!
Merci
--
albertri-at-videotron.ca.invalid
albert
Ça fonctionne maintenant, je ne sais pas ce que j'avais mal fait.
Joyeux Noël et Merci
Albert
"albert" <albe...@videotron.ca> a écrit dans le message de
news:n5f0fq$ijp$2...@speranza.aioe.org...