Partage de cas Excel : utilisez des formules de fonction pour vérifier l'authenticité des numéros d'identification

Dans l'article précédent "Partage de cas Excel : 5 techniques efficaces qui peuvent être réalisées par simple "glisser-déposer"", nous avons appris 5 techniques efficaces qui peuvent être réalisées par simplement "glisser-déposer" dans des tableaux Excel. Aujourd'hui, nous allons parler de la formule fonctionnelle d'Excel pour vérifier l'authenticité des numéros de carte d'identité, venez jeter un œil !

En parlant de choses horribles comme de faux vaccins et de lait en poudre toxique, je suis vraiment en colère et impuissant face à ce genre de choses, en tant que gens ordinaires, nous ne pouvons qu'espérer que les départements concernés pourront accroître les efforts de gestion et d'inspection pour prévenir. que cela ne se reproduise pas. Dans le même temps, vous devez être plus prudent dans votre travail et vérifier à plusieurs reprises de nombreux liens sujets aux erreurs pour éviter certaines conséquences néfastes. Par exemple, le numéro d'identification dans le dossier de l'employé est une zone sinistrée très sujette aux problèmes !

Aujourd'hui, nous partagerons avec vous une formule pour vérifier s'il y a des erreurs dans le numéro d'identification.

De manière générale, il y aura deux types d'erreurs dans les numéros d'identification, les erreurs de longueur et les erreurs de contenu.

La plupart des raisons d'erreurs surviennent lors du processus de saisie. Pour les erreurs de longueur, il s'agit actuellement de tous les nombres à 18 chiffres. S'il y a une erreur dans la longueur, elle est plus facile à trouver, mais s'il y a une erreur dans l'un d'entre eux. des chiffres, il est alors très difficile à trouver et peut entraîner de graves conséquences à l'avenir. Par conséquent, l'accent est mis aujourd'hui sur la vérification de l'exactitude du contenu.

1. Principe de composition du numéro d'identification

Tout d'abord, vous devez comprendre un peu les règles d'encodage du numéro d'identification :

La composition du numéro d'identification à 18 chiffres : jjdddddyaaammddxxsp , un total de 18 chiffres, dont : 1 à 6 chiffres C'est l'indicatif régional, 7 à 14 est la date de naissance, les trois chiffres 15 à 17 sont le code de sexe et le dernier chiffre est le chiffre de contrôle obtenu sur la base sur les 17 chiffres précédents.

Les règles de calcul des chiffres de contrôle sont plus compliquées :

(1) Formule de sommation pondérée du premier code d'ontologie à dix-sept chiffres

S = Sum(Ai * Wi), i = 0, ... , 16 , Tout d'abord, additionnez les poids des 17 premiers chiffres

Ai : représente la valeur numérique du numéro de carte d'identité à la i-ème position

Wi : représente le facteur de pondération à la i-ème position

Wi : 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

(2) Calculer le module

Y = mod(S, 11)

(3) Obtenir le code de contrôle correspondant

Y via le modulo : 0 1 2 3 4 5 6 7 8 9 10

Vérifiez le code : 1 0 Comparez les chiffres S'ils sont incohérents, il y a un problème. Il est assez difficile de comprendre cette règle. Prenons un exemple :

1 Multipliez les 17 chiffres précédents du numéro d'identification par différents coefficients. Les coefficients de la première à la dix-septième position sont :

7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2.

2. Additionnez les résultats de la multiplication de ces nombres et coefficients à 17 chiffres.

3. Divisez la somme par 11, quel est le reste ?

4. Le reste ne peut avoir que 11 chiffres : 0-1-2-3-4-5-6-7-8-9-10. Le dernier numéro d'identification correspondant est 1-0-X-9-8-7-6-5-4-3-2.

5. Grâce à la comparaison des codes de vérification, nous savons que si le reste est 3, le 18ème chiffre de la carte d'identité apparaîtra comme 9. Si le chiffre correspondant est 10, le dernier chiffre sur la carte d'identité est le chiffre romain 2.

Par exemple : le numéro de carte d'identité d'un homme est [53010219200508011x], voyons si cette carte d'identité est une carte d'identité légale.

Nous obtenons d'abord la somme des produits des 17 premiers chiffres

【

(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+ (2*4 )+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8 *5)+ (0*8)+(1*4)+(1*2)】 vaut 189, puis divisez 189 par 11 pour obtenir le résultat 189/11=17----2, c'est-à-dire que le nombre restant est 2. Enfin, grâce aux règles correspondantes, nous pouvons savoir que le code de contrôle correspondant au reste 2 est X, nous pouvons donc déterminer qu'il s'agit d'un numéro d'identification correct.

2. Utilisez Excel pour vérifier l'authenticité du numéro de carte d'identitéLe problème est maintenant de savoir comment exprimer ce processus de calcul à l'aide de formules Excel. Ici, je vais partager les formules avec vous :

.

=IF(RIGHT(A2)=MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假")

Notez qu'il s'agit d'une formule matricielle. Après avoir saisi ou collé la formule, vous devez maintenir les touches Ctrl et Maj enfoncées en même temps et appuyer sur Entrée.

La formule semble très compliquée. Lors de son utilisation, il vous suffit de modifier les deux A2 aux positions correspondant au numéro d'identification dans votre formulaire.

Parlons brièvement de l'idée de​​la formule. La partie centrale est

.

MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)

La fonction de cette chaîne est de calculer les 17 chiffres précédents selon les règles du code de contrôle, puis de les comparer avec RIGHT(A2). La fonction de RIGHT(A2) est d'extraire le chiffre le plus à droite de A2. Sortez et utilisez la fonction if pour juger en fonction des résultats de la comparaison.

Nous pouvons à nouveau utiliser cette formule pour vérifier si le numéro d'identification dans l'image au début de l'article est correct :

Le résultat est faux.

Autant utiliser cette formule pour vérifier s'il y a un numéro d'identification incorrect dans votre formulaire. Corrigez le numéro qui s'avère incorrect en temps opportun pour étouffer le problème dans l'œuf !

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!