Excel case sharing: Use function formulas to check the authenticity of ID numbers

In the previous article "Excel case sharing: 5 efficient techniques that can be realized by just "drag and drop"", we learned 5 efficient techniques that can be realized by just "drag and drop" in Excel tables . Today we are going to talk about Excel’s function formula for checking the authenticity of ID card numbers, come and take a look!

Talking about horrifying things like fake vaccines and tainted milk powder, I am really angry and helpless. For such things, as ordinary people, we can only hope that the relevant departments We can strengthen management and inspection efforts to prevent such vicious incidents from happening again. At the same time, you must be more careful about your work and check repeatedly in many error-prone links to avoid some adverse consequences. For example, the ID number in the employee file is a disaster area that is very prone to problems!

Today we will share with you a formula for checking whether there are errors in the ID number.

Generally speaking, there will be two types of errors in ID card numbers, length errors and content errors.

Most of the reasons for errors occur during the input process. For length errors, they are currently 18-digit numbers. If there is an error in the length, it is easier to find, but if one of the numbers appears Errors are very difficult to detect and may cause serious consequences in the future. Therefore, the focus today is to verify the correctness of the content.

1. Principle of composition of ID number

First of all, you need to understand a little about the encoding rules of ID number:

The 18-digit ID number consists of: dddddddyyyymmddxxsp, a total of 18 digits, of which: 1-6 digits are the area code, 7-14 are the date of birth, 15-17 are the gender code, and the last digit It is a check digit based on the previous 17 digits.

The check digit calculation rules are more complicated:

(1) Weighted summation formula of the first seventeen-digit ontology code

S ​​= Sum (Ai * Wi), i = 0, ..., 16, first sum the weights of the first 17 digits

Ai: represents the digital value of the ID number at the i-th position

Wi: represents the weighting factor at the i-th position

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

(2) Calculation module

Y = mod(S, 11)

(3) Obtain the corresponding check code through the modulus

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

Check code: 1 0 The check code is calculated according to the rules and then compared with the last digit. If it is inconsistent, there is a problem. It is quite difficult to understand this rule. Let's take an example to see:

1. Multiply the previous 17 digits of the ID number by different coefficients. The coefficients from the first to the seventeenth digit are:

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

2. Add the results of multiplying the 17-digit numbers and the coefficients.

3. Divide the sum by 11 and see what the remainder is?

4. The remainder can only have 11 numbers: 0-1-2-3-4-5-6-7-8-9-10. The corresponding last ID number is 1-0-X-9-8-7-6-5-4-3-2.

5. Through comparison of the check codes, it is known that if the remainder is 3, the 18th digit of the ID card will appear as 9. If the corresponding number is 10, the last number on the ID card is the Roman numeral 2.

For example: A man’s ID number is [53010219200508011x]. Let’s see if this ID card is a legal ID card.

First we get the sum of the products of the first 17 digits

[

(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)】 is 189, and then divide 189 by 11 to get the result 189/11=17----2 , which means the remainder is 2. Finally, through the corresponding rules, we can know that the check code corresponding to the remainder 2 is X, so we can determine that this is a correct ID number.

2. Check the authenticity of the ID card number using Excel Now that the principle is clear, the question now is how to use Excel formulas for this calculation process To express it, I will share the formula with you here:

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

Note that this is an array formula. After entering or pasting the formula, you need to hold down the Ctrl and shift keys at the same time and press Enter.

The formula looks very complicated. When using it, you only need to modify the two A2s to the positions corresponding to the ID numbers in your form.

Let’s briefly talk about the idea of ​​​​the formula. The core part is

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

This string is used to calculate the previous 17 digits according to the check code rules, and then compare it with RIGHT (A2). RIGHT The function of (A2) is to extract the rightmost bit from A2, and use the if function to judge based on the comparison result.

We can use this formula again to check whether the ID number in the picture at the beginning of the article is correct:

The result is false.

You might as well use this formula to check whether there are wrong ID numbers in your form. Correct the numbers that are judged to be wrong in a timely manner to nip the problem in the bud!

Related learning recommendations: excel tutorial

The above is the detailed content of Excel case sharing: Use function formulas to check the authenticity of ID numbers. For more information, please follow other related articles on the PHP Chinese website!