Practical Excel skills sharing: Make a dynamic inquiry form for employee information!

In the previous article " Practical Excel skills sharing: It turns out that calculating overtime pay is so simple! 》, we talked about Excel table statistics. Today we will talk about Excel table query and share how to create a dynamic employee information query table. After reading this tutorial, you can also make one yourself! Who is number

#008? What's the phone number?

This is not a blind date, but a quick query of employee information! There may be hundreds or even thousands of employees in your company. How can you quickly query employee information based on their employee numbers? You need to make a dynamic inquiry form for employee information! With the information dynamic query form, not only your last name and phone number, but also your appearance can be found. The final result is as follows:

Employee registration form for the unit, They are generally very long. When we want to query information about an employee, we need to drag left and right to view it, and it is easy to read the wrong line. Bottle only gives an example of ten people here, but many companies have hundreds or even thousands of people. It is very difficult to quickly check an employee's information in the employee registration form.

Next we will create a separate employee information query form in sheet2. There are almost no operating skills here. You just need to enter the name of the project you want to query. For the title and photo, use merged cells. Finally, use the "Border" in the "Font" group of the "Home" tab to add a border to the cell. Add a border.

Analysis:

The final effect we want is to enter the job number in cell D3, and then the information below is automatically displayed, so you can consider using The VLOOKUP function searches the employee registration table according to the employee number and returns the required options.

Complete process:

01

Since our job number starts with 00, if we directly enter 001, it will only Displays 1, so we first select cell D3 and set it to "Text" format.

Then set the "underline" and "centered" styles for the employee number. The results are as follows.

02

Enter the formula in cell D4:

=VLOOKUP($D$3, employee registration form!$A$1:$R$11,MATCH(C4, employee registration form!$A$1:$R$1,0),0), press Enter, you can see The employee name corresponding to job number 001 has been displayed.

Formula analysis:

• 1.MATCH(C4, employee registration form!$A$1:$R$1,0)

  means to search accurately in the A1-R1 cell area of ​​the employee registration form based on cell C4 and return the corresponding column number. Since our formula needs to pull down to the right and keep the search area unchanged, the cell range A1:R1 is an absolute reference.

• 2.VLOOKUP($D$3, employee registration form!$A$1:$R$11,MATCH(C4, employee registration form!$A$1:$R$1,0) ,0)

  means: According to the employee number entered in cell D3, search accurately in the A1-R11 cell area of ​​the employee registration form to get the row number, and then combine it with the column number obtained by the MATCH function. Finally, Returns the value of row and column intersection.

03

Since in this table, we need to fill in formulas in alternate columns, we cannot pull right directly. We first pull down the formula and get the result shown below.

Then hold down the ctrl key and select the cells in columns F, H and the empty cells in row 9.

Keep holding down the ctrl key, click on cell D4, click behind the formula in the edit bar, you can see the cursor flashing behind the formula.

Then press ctrl enter, you can see the results as shown below.

04

All dates in the current table are displayed as numbers, which is the original appearance of dates in excel. Hold down the ctrl key, select all dates, and set the format to "Short Date."

At this time all dates are displayed normally.

05

We can try to change the job number, and you can see that the following details will change accordingly change. When entering employee number 002, you can see that some information below is displayed as 0, which means that this information is an empty cell in the employee registration form.

Click "File" - "Options" - "Advanced" and uncheck "Show zeros in cells with zero values".

After clicking "OK", you can see that if the found cell is empty, an empty cell will be returned.

06

When entering a non-existent job number, all cells will display an error information.

#We can add an IFERROR function before the formula for fault tolerance.

Select cell D4 and change the formula to:

=IFERROR(VLOOKUP($D$3,Employee Registration Form!$A$1:$R$11,MATCH(C4, Employee registration form!$A$1:$R$1,0),0),""). After pressing Enter, fill the formula into other cells in the same way as before. At this point, you can see that since there is no employee with job number 013, the tables are empty.

07

Let’s make dynamic settings for photos.

Select the "Photo" cell and click "Formulas" - "Define Name".

Enter the formula in the pop-up dialog box:

=INDEX(Employee Registration Form!$D:$D,MATCH(Employee Information Query form!$D$3, employee registration form!$A:$A,0)), name it "Photo", click OK.

Formula analysis:

• 1.MATCH (Employee Information Query Form! $D$3, Employee Registration Form! $A:$A,0), use the MATCH function to find cell D3 in the employee information query table (that is, the employee number we entered) in column A of the employee registration table, and return the row number.

• 2.INDEX(Employee Registration Form!$D:$D,MATCH(Employee Information Query Form!$D$3,Employee Registration Form!$A:$A, 0)), use the INDEX function to return the value of the row where the employee number is located in column D (the row where the employee number is located is obtained by the MATCH function).

Find "Camera" in the excel custom ribbon and add it to the "Customized Quick Access Toolbar".

At this time, a camera button appears on the upper left side of the excel page.

Click "Camera" and drag the mouse in the "Photo" cell to draw a rectangular frame.

Click the formula in the edit bar and change the formula to:

=Photo. After pressing Enter, you can see that the photo corresponding to the job number is displayed. .

Now, you can try to change your job number, and the information and photos in the form will change accordingly!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Make a dynamic inquiry form for employee information!. For more information, please follow other related articles on the PHP Chinese website!