numéro INSEE et clé de contrôle sur Excel Résolu/Fermé

Question

Bonjour,
On me demande sous mon tableur Excel de créer une feuille de calcul qui lorsqu'on tape les 13 premiers caractères d'un n°INSEE, renvoie la valeur de la clé de contrôle.

Merci d'avance pour votre réponse rapide. Je panique sérieusement, j'ai un examen d'Informatique appliquée au traitement des données qui approche et je suis loin d'y arriver.

Cristina

eriiic · Accepted Answer

Bonjour tout le monde,

Tu as #nombre car mod() est un peu dépassé par la taille du nombre.
Tu peux t'en passer avec :
=97-ARRONDI((A2/97-ENT(A2/97))*97;0)

Et pour voir toujours ta saisie sur 13 chiffres saisi ton nombre en mettant ' devant ou met ta cellule au format texte.

eric

LePierre · Answer

Bonjour

En cherchant sur Internet j'ai trouvé ceci : 
"La clé de contrôle sur 2 chiffres est égale à 97 - (reste de la division euclidienne du NIR par 97), le NIR est le Numéro d'Inscription au Répertoire de l'Insee sur 13 chiffres, il est unique. 
Cet algorithme fonctionne aussi avec les numéros temporaires attribués par la sécurité sociale (commençant par exemple par un code sexe=8...)."
à plus

Raymond PENTIER · Answer

Bonjour, cristina, et bienvenue dans ce forum.

Mais si tu es pressée, tu aurais gagné du temps à faire toi-même ce que je viens de faire à ton intention, taper "N° INSEE" sur Google ! On obtient l'adresse https://www.google.com/search?q=N%C2%B0+INSEE&rls=com.microsoft:fr:IE-SearchBox&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=1I7HPEB_fr&gws_rd=ssl qui contient 3 370 000 réponses, dont 3 000 000 en français : tu as donc le choix !

Pour ma part, j'ai ainsi trouvé par exemple
https://codes-sources.commentcamarche.net/ pour le N° SIREN à 9 chiffres, et aussi
https://codes-sources.commentcamarche.net/ qui répond à ta question (sauf pour le cas de la Corse) mais qui est une macro en VBA.
C'était dans https://codes-sources.commentcamarche.net/

Et à une prochaine question ...

cristina newton · Answer

Merci Le Pierre et Raymond, 

Eh oui, par manque de temps je vous réponds en même temps.

LePierre, je sais comment calculer le INSEE  à la main et trouver sa clé.

Je voulais juste une formule sur Excel, Raymond, pas 1 page de code. Les recherches faites sur Google n'ont pas répondu à ma question.
Finalement j'ai trouvé seule la formule : clé = 97-MOD(nombre, 97). Le problème est que je ne sais pas comment paramétrer mon Excel car il ne veut accepter d'afficher des nombres à 13 chiffres et donc j'ai comme résultat #nombre#!

Une suggestion ? Simple, s'il vous plaît ...

Cristina

Raymond PENTIER · Answer

La (bonne) réponse de LePierre, que je salue, est parvenue alors que je rédigeais la mienne.

On peut préciser qu'Alexandre MOATTI a fait le travail pour nous sous Excel :
http://www.maths-et-physique.net/article-6061182.html

Son fichier est tellement simple (6 cellules) que je me demande pourquoi se fatiguer à crér une fonction sous macro !

cristina newton · Answer

Merci Raymond, merci Eric,

Vos réponses sont très appréciées. 

Grâce à vous deux je pense avoir trouvé la réponse à cette question. Mais (il y a toujours un "mais") je dois continuer pour créer une feuille de calcul sur Excel pour calculer la clé de contrôle des codes ISBN, ISSN et des comptes bancaires (modulo 97). 
On me dit d'utiliser la fonction MOD, je l'aurai déjà compris, mais il doit y avoir quelques formules avant pour calculer la pondération.

Soyez "tendres" avec moi lors des explications, je fais des études d'économie et non pas d'informatique, mais j'ai un examen d'informatique que je dois passer au mois de mars, dans les conditions où l'enseignement est à distance, sans prof et avec des cours très sommaires et assez mal expliqués. Alors, je galère et je persévère. Je n'ai pas le choix.

Merci d'avance,
Cristina

sam_island · Answer

Bonjour,

si on considère le n° INSEE de 13 chiffres et qu'on appelle r1, r2... r13 les 13 chiffres composant ce numéro,
la clé se calcul ainsi :
                        
97 - MOD (((r1r2r3r4)*34+(r5r6r7)*30+(r8r9r10)*27+(r11r12r13)),97)

r1r2r3r4 est la concatenation des 4 premiers chiffres du n° INSEE
r5r6r7 est la concaténation des 5eme, 6eme et 7eme chiffres
r8r9r10 est la concaténation des 8eme, 9eme et 10eme chiffres                                                    
r11r12r13 est la concaténation des 11eme, 12eme et 13eme chiffres                                                    

soit, dans un tableur genre EXCEL, si le n° INSEE est en A1, la clé :

---------------------------------------------------------------------------
=97-MOD(STXT(A1;1;4)*34+STXT(A1;5;3)*27+STXT(A1;8;3)*30+STXT(A1;11;3)*1;97)
---------------------------------------------------------------------------

si on préfère séparer les différents infos ou qu'elles le sont par la force des choses (formulaire avec plusieurs zones) :

r1 : code sexe
r2r3 : Année naissance
r4r5 : Mois naissance
r6r7 : département naissance
r8r9r10 : code commune
r11r12r13 : n° ordre

la clé se calcule ainsi :

97 - MOD (((r1)*50+(r2r3)*49+(r4r5)*81+(r6r7)*27+(r8r9r10)*30+(r11r12r13));97)

et, dans un tableur, si le n° INSEE est en A1, la clé :

-----------------------------------------------------------------------------------------------------------
=97-MOD(STXT(A1;1;1)*50+STXT(A1;2;2)*49+STXT(A1;4;2)*81+STXT(A1;6;2)*27+STXT(A1;8;3)*30+STXT(A1;11;3)*1;97)
-----------------------------------------------------------------------------------------------------------

La démonstration est inspirée de ce que j'ai trouvé sur WIKIPEDIA relativement au calcul de la clé RIB.

Je vous en fais grâce ici mais si ça vous intéresse, on en reparle  :o)

Samuel LANDIS

Numéro INSEE et clé de contrôle sur Excel

7 réponses

Discussions similaires