[Original] How to associate data in multiple Excel worksheet (sheet)

How to associate data in multiple Excel worksheet (sheet)

Author: Han Yufeng 

Not long ago, a colleague asked me if I could integrate the data in the two Excel files through the employee ID. After reading the materials, I have the following experiences and will share them with you. Some people may say, "Is it necessary to write such details ?"
I think: Most people who use Excel to process data are not as good as programmers as they can import databases for reprocessing and understand how to write SQL statements. Therefore, I have made a detailed illustration of each step.

I,
Check whether the database query function is installed in Excel 2003.

Click "data"> "Import external data"> "Create Database Query" in the menu bar ",

If the following prompt is displayed, the installation is not described.

In the control panel, find "add or delete programs" → find Office 2003
→
Click "change", as shown in figure

II,
Preparation before data import

1. If your data format has a header, you should first create a new book1.xls to copy the information of the Department staff.

2. Copy another data group to book1.xls.
For example

3. Save book1.xls
To C
The root directory should not be stored on the desktop as far as possible, so as not to be difficult to find during import.

III,
Start data import

1,
Import data now. To verify the correctness of the data, we will
Sheet3
And browse the imported data. Switch to sheet3
,

2,
Click "data"> "Import external data"> "Create Database Query ",

3,
In the displayed dialog box, select "Excel files *" →
Remove the following check box for "use query wizard" (you only need to select it once. If you enter it again, the check box will not be checked, and the system will automatically remember your settings)
→
Click "OK"

4,
On the following page, find
Book1.xls in the root directory.

5,
In the next picture, nothing can be seen. Don't worry. Click "option" (This step only needs to be selected once. You do not need to select this option next time. The system will automatically remember your settings)

6,
Select system table, as shown in figure

7,
Now we can see sheet1 $, sheet2 $, and sheet3 $
. Double-click "sheet1 $" and "sheet2 $". When they appear, click "close ". (Do not click "add" because we need to manually create an association. If you click "add", a prompt box will appear, and a "dikar product" record will appear in the data browsing area)

8,
Create a Data Association to associate sheet1
"Employee ID" and sheet2
Place the "employee ID" on the equal sign: Click the left mouse button to hold sheet1 $
And drag it to sheet2 $
Above the "employee ID" in,

You can also create a connection by clicking "table"> "connection" in the menu, as shown below:

In the dialog box, select "sheet1 $. employee ID" = "sheet2 $. employee ID" → click "add" → close the dialog box after the connection appears,

9,
Add your field to query the data you want: Double-click sheet1 $ and sheet2 $
In the data browsing area, corresponding values are displayed in the order you click.

10,
How to remove fields you don't want: Click the header of the field
→
Press the "delete" key on the keyboard to remove this field.

11,
Return the queried data to excel
There are two methods: one is to "file"> click "return data to excel" in the menu; the other is to directly click the fourth button in the toolbar.

12,
Select a place to officially import data

IV,
End -- sorting

After completing the preceding steps, we can see: sheet3 and sheet1
The order of employees is different.

Sheet3
You only need to create a new order to reach
In the same order: Click sheet3
"Department" →
Click Excel
"Data" in the menu →
"Sort ",

In the displayed dialog box, select "main keyword" as "department" and "ASCENDING" →
Reselect
"Secondary keyword" is "employee ID", "ASCENDING" →
Click "OK"

Finally, put sheet3
Copy "seniority" in to sheet1
In the "seniority", the success !! Not easy .........