friends, have you returned to work?
Today, Lao Zhu has prepared a set of function formulas related to ID cards for everyone to see how to extract the required information from the ID number.
is shown in the figure below, which is a simulated employee information table. Column A is the name, column B is the ID number, and column C is the rank.
★★★
First, let’s extract the date of birth. Enter the following formula in D2 and copy it down.
=--TEXT(MID(B2,7,8),"0-00-00")
The 8th to 15th digits of the ID number are the date and month of birth information.
first uses the MID function to extract 8 characters starting from the 7th position of cell A2, and obtains the character "19820102". Then use the TEXT function to convert this string into date-style text, and obtain the string "1982-01-02".
Since the result of the TEXT function is text content, two small minus signs -- are added, which is to calculate the negative number of the negative number. Through four arithmetic operations, it becomes a real date sequence. The purpose of this is to facilitate subsequent statistical summary.
If the formula result is displayed as a 5-digit number, don’t forget to set the cell format to date.
★★★
Next we need to calculate the age. When calculating the age, you can use the birth year and month that you have just extracted. Enter the following formula in cell E2 and copy it down.
=DATEDIF(D2,"2020-1-1","y")
DATEDIF function is used to calculate the interval between two dates. The usage is:
=DATEDIF (start date, end date, specified interval type)
In this example, the start date is the birth year and month of D2, and the end date is January 1, 2020. Specify the interval type as "Y", which means the calculation interval is a whole year. If it is "M", it means the entire month of the calculation interval. The
DATEDIF function is a hidden function. There is no screen prompt when typing, so you need to enter it manually.
If you do not use the date of birth in column D, the formula can be written like this:
=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),"2020-1-1","y ")
★★★
In addition to caring about age, leaders should be more concerned about gender. Using the following formula, you can extract the gender information of employees:
=IF(MOD(MID(B2,17,1),2), "Male", "Female")
The 17th digit in the ID number is gender information. The odd number represents male and the even number represents female.
first uses the MID function to extract 1 character starting from the 17th position of cell B2.
then uses the MOD function to calculate the remainder of dividing this character by 2.
finally uses the IF function to judge. If the remainder is not equal to 0, it returns male. If the remainder is 0, it returns female.
★★★
Next, we need to calculate the retirement age based on the extracted birth date, gender, and rank information. According to existing regulations, the retirement age for men is 60 years old, for female cadres, the retirement age is 55 years old, and for female cadres, the retirement age is 55 years old. Annual retirement is 50 years old.
G2 Enter the following formula and copy it down.
=EDATE(D2,IF(F2="Male",720,IF(F2&C2="Female Cadre",660,600)))
EDATE function is to calculate starting from the specified date based on the specified starting date and interval month. The date after N months.
In this example, the specified starting date is the birth year and month of cell D2. The formula part of
interval month is:
IF(F2="Male",720,IF(F2&C2="Female Cadre",660,600))
This part seems a little complicated, let’s dismantle it slowly:
Use IF first The function determines whether the gender of cell F2 is equal to "male". If so, it returns 720 (720 months, which is 60 years old).
If it is not equal to "male", continue to determine whether the gender of F2 is equal to the rank of C2, whether it is equal to "female cadre", if so, return 660 (660 months, that is, 55 years old), if it is not equal to "female cadre" , it returns 600 (600 months, that is, 50 years old).
★★★
The first six digits of the ID number indicate the place of residence. Next, let’s see how to determine the place of residence based on the ID number.
When determining the place of origin, we must first prepare a comparison table. Baidu has many, but the household registration code in the comparison table I downloaded does not seem to be complete.
Enter the following formula in cell G2 of the information table and copy it down.
=VLOOKUP(LEFT(B2,6)*1, ID card code comparison table!A:B,2,0)
First use LEFT(B2,6) to get the first six digits of the ID number, and then use the VLOOKUP function to Find the corresponding place of origin information in the code comparison table.
Because the LEFT function obtains text-type numbers, and our code table is numerical, we use the method of multiplying by 1 to convert it into a numerical value, otherwise the query will be wrong.
★★★
Finally, let’s look at the problem of finding duplicate ID numbers.
I2Enter the following formula and copy it down.
=IF(COUNTIF(B:B,B2&"*")1,"Duplicate","No duplicate")
The duplication check of ID card number is different from the conventional data duplication check. In the COUNTIF function , An asterisk & "*" should be added after the second parameter. When the
COUNTIF function calculates text-type numbers, it will process them as numerical types by default. However, the number precision in Excel is only 15 digits, and the ID number is 18 digits. In this way, all ID cards with the same first 15 digits will be recognized as the same. Content.
We take advantage of the fact that values in Excel do not support wildcards, and connect a wildcard asterisk &"*" after the second parameter. It is equivalent to telling Excel to find the text starting with the content of cell B2. Through this flexible method, the COUTNIF function becomes obedient.
Okay, let’s leave it at that for today. I wish my friends and family members are well.
