Master the skills of dynamically merging worksheets in Excel in one article

This article brings you relevant knowledge about excel, which mainly introduces the skills of dynamically merging worksheets, how to merge data in different worksheets together, let’s do it together Take a look, hope it helps everyone.

Related learning recommendations: excel tutorial

Share a technique for dynamically merging worksheets.

Many times, our data is stored in different worksheets according to departments or months. When we want to analyze and summarize these data, we need to merge the data in different worksheets first. Just come together.

Just like the data shown in the figure below, the three worksheets contain sales records of a certain brand of goods in different regions. The distribution order of each column in each worksheet is different, and the data in some columns are completely different items from other worksheets.

Next, let’s use Excel 2019 as an example to talk about how to dynamically merge the data in these worksheets together, as shown in the figure below Effect.

Step 1:

Create a new worksheet, rename it to "Summary Table", and then save it.

Step 2:

Under the [Data] tab, select [Get Data] → [From File] [From Workbook].

Find the location where the workbook is stored and import it according to the prompts.

In the [Navigator] window, click the workbook name, and then click [Convert Data] to load the data into the data query editor.

Some friends may have noticed that there are only three data tables and one summary table in the workbook, but at this step, there will be several more inexplicable worksheets. Name:

What is going on?

Actually, these are some hidden names. If we perform filtering, advanced filtering, insert a super table or set a print area in Excel, Excel will automatically generate these hidden names.

Step 3:

In the data query editor, we need to filter out these, click the filter button of the [Kind] field, and Select the "Sheet" type in the filter menu.

In addition, you also need to filter out the "Summary Table" in the [Name] field, otherwise you will find that the data will double after merging, adding many duplicate records.

Step 4:

As we said before, the fields in each worksheet are distributed in different orders, and some fields in the worksheet are other worksheet It is not in the table, so we have to deal with it specially.

Select the step name "Source" in the [Query Settings] pane, and then change null in the formula to true in the edit bar.

After this modification, the system can automatically recognize the field names and automatically classify them.

Step 5:

Then select the step name "Filtered Rows" in the [Query Settings] pane, hold down the Ctrl key, and then click Click the titles of the [Name] and [Date] fields to select these two columns, and right-click → [Delete other columns].

Next click the expand button of the [Date] field to expand the data.

Step 6:

Click the title of the date field, set the format to "Date", and then click [Close and Upload] → [ Close and upload to] to upload the data into the worksheet.

At this point, our merger is complete. As long as the columns with the same title in each worksheet are automatically classified into the same column, the columns with different titles in each worksheet will also be automatically arranged in sequence.

If our data is updated in the future, or a new worksheet is added, just right-click in any cell of the summary table and refresh it, and it will be OK. No other operations are required.

Related learning recommendations: excel tutorial

The above is the detailed content of Master the skills of dynamically merging worksheets in Excel in one article. For more information, please follow other related articles on the PHP Chinese website!