Establishing a small personnel data management system with Excel

Objective:

In real life, many small businesses do not have a special personnel management software for the personnel department. In fact, a set of specialized personnel management software is expensive and not necessarily suitable for the actual needs of the enterprise.

Do-it-Yourself, using Excel to establish a suitable for the specific needs of the enterprise personnel Management system is a good choice. Excel is powerful, and easy to operate, the monthly accurate statistics of enterprise staff changes, the end of the analysis of a large number of personnel data and so on complex work can be easily completed through Excel. Now we offer our readers an example of a small personnel data management system through Excel.

Body:

First, we open a new Excel table to establish a personnel information base framework, the project settings you can be based on the actual situation of the unit. As shown in figure (i):

Figure one opens a new Excel table to establish a personnel information base framework

Next please do not rush to input personnel information, we want to some information items for function settings, so that the system can automatically generate relevant information, which will make our work has a multiplier effect.

1, sex, birth month day, age of automatic filling function set

Let's start with a function setting for "sex" "Age". When we enter a person's ID card number, the system will automatically generate "gender", "date of birth" and "age", which reduces the amount of work we input. Please select the gender, month of birth, age information entry cell to enter the following formula:

(1) Gender: =if (MOD (IF (LEN (E3) =15,mid (e3,15,1), MID (e3,17,1)), 2 = 1, "Male", "female")

Meaning: "LEN (E3) =15" means to see if there are 15 characters in the E3;

Mid (e3,15,1) indicates that 1-bit characters are extracted from the 15th bit in the E3;

"MOD (MID (), 2) =1" indicates that the extracted character is divided by over 2 digits by 1;

"If" (E3) =15,mid (e3,15,1), MID (e3,17,1) means to see if the E3 is 15 characters long, 1 characters from the 15th character if enough, 1 characters from the 17th character if not 15 characters. Our ID number is usually 15 or 18 digits.

The IF (MOD (E3) =15,mid (e3,15,1), MID (e3,17,1), 2) = 1, "Male", "female" "indicates the character divided by 2 if the remainder is 1 showing the male, otherwise the female is displayed.

(2) Date of birth: =date (Mid (e3,7,4), Mid (e3,11,2), Mid (e3,13,2))

Meaning: DATE (year,month,day);

Mid (e3,7,4) indicates that the year is 4 consecutive characters from the 7th character in the E3, with a similar representation the date of birth of a person can be represented by a function setting, and if it is convenient to look at, we can format the formats of the formats for adulthood, months, and days for the cell format, The results shown will be very easy to understand.