Numéros IBAN et Modulo 97

Bonjour,

D’après ce que j’ai compris, afin de contrôler un numéro IBAN (par exemple celui placé en A1 dans le fichier ci-joint), il faut commencer par déplacer les 4 premiers signes à la fin (comme ça se fait dans la cellule A2) ; donner à chaque signe une certaine valeur (comme ça se fait dans le tableau A4:B24, aux chiffres on donne leur propre valeur et aux lettres une valeur selon le tableau D4:E29) puis mettre bout à bout tous ces nombres afin de contrôler leur modulo 97. Afin de mettre bout à bout tous ces nombres, j’ai créé la macro placée derrière le bouton ‘’Actualiser C1’’ et en C2, j’ai placé une formule =MOD.

Ma question est alors : savez-vous pourquoi la formule placée en C2 ne fonctionne pas (elle fonctionne si l’on diminue le nombre de chiffres en C1 à 14) et comment contourner ce problème ?

Mon tableau est prévu pour les numéros IBAN suisses, mais je serais intéressé à pouvoir éventuellement contrôler d’autres numéros européens.

Bonjour Yvouilles

je ne suis pas arrivé à trouver une formule cause cellule format différent

mais, j'ai trouver sur le net une function qui fonctionne à tester merci à lui

crdlt,

André

Bonjour Yvouille,

Alors malheureusement pour toi, j'ai une mauvaise nouvelle. Ce problème est dû aux limitations d'Excel. En effet, les nombres ne peuvent pas faire plus de 16 chiffres (les suivants sont mis à 0).

Tu peux retrouver les détails sur la page officielle de Microsoft : https://support.office.com/fr-fr/article/Sp%C3%A9cifications-et-limites-relatives-%C3%A0-Excel-1672b34d-7043-467e-8e27-269d656771c3

Dans le paragraphe "Spécifications et limites relatives au calcul".

Par ailleurs, étant un puriste d'Excel, je te mets à disposition ta feuille de calcul avec le même résultat réalisée sans macro, juste avec des fonctions standards.

Navré pour toi... Il faudrait voir si certaines propriétés de la fonction modulo permettraient de faire le calcul en plusieurs fois.

J'avoue que je n'ai pas cherché.

A+

Charles

Salut André,

Merci pour ta réponse ma foi très utile

Dans sa macro, ce gars semble créer le même numéro que moi en C1 (sa variable ‘’IBAN_final’’) puis il effectue le calcul ci-dessous :

IBAN_modulo = IBAN_final - Fix(IBAN_final / 97) * 97

Dans l’aide, j’ai découvert que la fonction ‘’Fix’’ qu’il utilise est une variante de la fonction ‘’Ent’’, mais si je connais la version Excel de ‘’Ent’’, je ne connais pas la version Excel de ‘’Fix’’.

J’ai donc tenté le calcul =C1*1-ENT(C1*1/97)*97 en C2, mais ça ne donne pas le résultat voulu de 1.

Sais-tu quelle est la version Excel de ‘’Fix’’ afin que je puisse faire un essai avec cette autre fonction ou quel autre calcul je devrais faire en C2 afin d’obtenir le résultat attendu de 1 ?

Chaleureusement.

EDIT : Je viens de constater que Charles m'a également fourni une réponse. Merci à toi aussi, je vais voir cela tranquillement.

Bon, j'ai finalement investigué et ai trouvé une solution sans VBA !

En effet, j'ai pu contourner la limitation d'Excel en utilisant une propriété des modulos.

J'ai ainsi pu établir un tableau qui fait tous les calculs de modulos par bloc de 8 caractères et calcule le résultat final à la fin.

Maintenant, il ne reste qu'à ajouter une ligne avec la chaîne à analyser pour savoir si l'IBAN et valide ou non.

Dans l'exemple fourni, l'IBAN en CH n'est pas valide, tout simplement.

J'ai testé avec un certain nombre d'exemple de plusieurs pays de ce fichier : https://www.swift.com/sites/default/files/resources/swift_standards_ibanregistry.pdf

Mon fichier en PJ.

Charles

Salut Charles,

Ton travail est très intéressant. Merci beaucoup pour ton aide.

A la longue, j’aurais été intéressé à trouver le numéro de contrôle nécessaire à un numéro de compte bien précis.

Avec ton travail je peux y arriver. Dans le fichier ci-joint, par exemple, j’ai cherché le numéro de contrôle (les deux chiffres qui suivent le code du pays) qui correspond au compte 3456 7890 C123 4567 8 et j’ai trouvé 95.

En plaçant à la suite de tes formules un code VBA, qui proposera une incrémentation du nombre de contrôle jusqu’à trouver le bon, sera un jeu d’enfant.

Encore merci pour ton aide et bonne continuation.

Bonjour,

Sujet très intéressant !

Vous êtes balaises ...

Utilisez CODE :

CODE(STXT($C2;D$1;1))-55

plutôt que RECHERCHEH

comme ceci :

Et pour les français, avec uniquement des chiffres, la clé est issue du MOD97

313cle-rib.xlsx (9.79 Ko)

J'aime bien ce genre de post qui pousse à réfléchir à des solutions intéressantes. (je surveillerai dorénavant encore plus les questions d'Yvouille qui sont toujours nouvelles et pertinentes)

Voici une fonction que j'ai bricolée pour calculer le modulo d'un très grand nombre.

Je me suis posé la question s'il fallait tronçonner en 4 ou en 3 ou en 2 et in fine j'ai tronçonné en autant de chiffres qu'il y a dans le nombre initial ! j'ai alors juste reproduit ci-dessous ce que nous faisions l'école primaire !

Function CalculMOD(cel As Range, diviseur As Integer) As Integer
    chaine = Replace(cel.Value, " ", "")
    CalculMOD = 0
    For i = 1 To Len(chaine)
        CalculMOD = (10 * CalculMOD + Mid(chaine, i, 1)) Mod diviseur
    Next
End Function

et pour terminer ma tempête de cervelle (brainstorming en frenchie)

Function VerifIBAN(cel As Range) As Boolean
    chaine = Replace(Mid(cel.Value, 5, Len(cel.Value)) & Left(cel.Value, 4), " ", "")
    resultat = 0
    For i = 1 To Len(chaine)
        caractere = Mid(chaine, i, 1)
        If IsNumeric(caractere) Then
            resultat = (10 * resultat + caractere) Mod 97
        Else
            resultat = (100 * resultat + (Asc(caractere) - 55)) Mod 97
        End If
    Next
    VerifIBAN = (resultat = 1)
End Function
189verif-iban.xlsm (57.11 Ko)

Puisqu'on me pousse à bout !

Je reprends l'idée de Steelson et fait une version complète sans macro !

Du coup, j'ai simplifié, mis de l'ordre et fait au plus propre.

A vous de juger !

98iban-testeur.xlsx (21.96 Ko)

Au temps pour moi, j'avais mal testé la résolution de la clé...

L'idée de la somme était presque juste... c'était une soustraction + un dernier modulo.

Voici la bonne dernière version. Le plus simple étant de tester avec tous ceux qui fonctionne déjà, de changer la valeur de la clé et de vérifier que la formule trouve le bon résultat, c'est à dire la valeur précédemment présente.

211iban-testeur.xlsx (49.26 Ko)

Hé oui ... partant de là, une fonction matricielle doit pouvoir compacter le tout !

Bonjour tout le monde,

Après que j’aie indiqué hier dimanche à 9:15 que j’avais ma solution, je ne suis plus revenu voir mon fil pour quelques heures. Lorsque j’y suis finalement retourné, j’ai atterri sur la deuxième page de ce fil et j’ai complètement zappé les réponses de Steelson. J’en suis absolument honteux et désolé.

Mais d’un autre côté je dois avouer que ça dépasse largement mon attente et que je suis un peu largué.

Dans tous les cas, merci à tous les intervenants qui m’ont permis d’obtenir la petite solution que j’attendais

Chaleureusement.

j’ai atterri sur la deuxième page de ce fil et j’ai complètement zappé les réponses de Steelson.

Je me suis souvent fait piéger, plutôt dans l'autre sens du reste : lire via la notification la réponse juste après, y répondre et voir ensuite qu'il y avait une autre page !

En tous cas ce fut un excellent travail collaboratif avec toi et Charles

Rechercher des sujets similaires à "numeros iban modulo"