Merge excel data from multiple different worksheets into one overall table

How to summarize multiple different worksheets in excel into one master table

We need to put multiple excel tables in the same folder and create a new excel in this folder. as the picture shows:

2

Use Microsoft Excel to open the newly created excel sheet, right-click sheet1, find "View Code", and click in. After entering, you will see the macro calculation interface. as the picture shows:

3

Then we copy the following macro calculation codes, and then find "Run Subprocess/User Form" under "Run" on the toolbar. The code is as follows, as shown in the figure:

Sub merges all worksheets in all workbooks in the current directory ()

Dim MyPath, MyName, AWbName

Dim Wb As Workbook, WbN As String

Dim G As Long

Dim Num As Long

Dim BOX As String

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & ＂\＂ & ＂*.xls＂)

AWbName = ActiveWorkbook.Name

Num = 0

Do While MyName "＂"

If MyName AWbName Then

Set Wb = Workbooks.Open(MyPath & "\＂" & MyName)

Num = Num 1

With Workbooks(1).ActiveSheet

.Cells(.Range("B65536").End(xlUp).Row 2, 1) = Left(MyName, Len(MyName) - 4)

For G = 1 To Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range(＂B65536＂).End(xlUp).Row 1, 1)

Next

WbN = WbN & Chr(13) & Wb.Name

Wb.Close False

End With

End If

MyName = Dir

Loop

Range(＂B1＂).Select

Application.ScreenUpdating = True

MsgBox "A total of "& Num &" all worksheets under "& Num &" workbooks have been merged. As follows: "& Chr(13) & WbN, vbInformation, "Prompt"

End Sub

4

After running, wait for about 10 seconds. When the operation is completed, that is, after the merger is completed, there will be a prompt, just click OK. Looking at the merged data, there are more than 5,000 rows, which is the result of merging data from 17 excel tables in the same folder. The effect is shown in the figure.

END

Precautions

Kingsoft’s WPS does not have a macro calculation function. This can only be achieved using Microsoft Excel.

How to classify and summarize Excel tables

When you need to classify and calculate data in Excel, in addition to using pivot tables, you can also use the subtotal command. Unlike a PivotTable, it can insert summary rows directly into the data area, so you can see both data details and summaries at the same time. The following is how to use classification and summary:

Before performing classification and aggregation, ensure that the data has the following format, that is, the first row of the data area is the title row, there are no empty rows and empty columns in the data area, and the data area is surrounded by empty rows and empty columns, as shown in the following figure Sales data of various commodities in some cities. In addition, if the data range has been set to an Excel 2003 list or Excel 2007 table before applying subtotals, it needs to be converted to a normal range. Because subtotals cannot be used with Excel 2003 lists or Excel 2007 tables.

1. Only classify and summarize a certain column

For example, in the above example, the sales volume in each city needs to be classified and summarized. The method is as follows:

1. First sort the data by the column that needs to be classified and summarized (in this case, the "City" column).

Select any cell in the "City" column and click the sort button in the toolbar such as "A→Z" in Excel 2003. In Excel 2007, select the "Data" tab in the ribbon and click the "A→Z" button in the "Sort and Filter" group.

2. Select a cell in the data area and click the menu "Data → Subtotal" in Excel 2003. If this is Excel 2007, on the Data tab, in the Outline group, click Subtotals.

3. In the pop-up "Classification and Summary" dialog box, select "City" under "Category Field" and select a certain summary method in "Summary Method". The available summary methods are "And", "Count", "Average", etc. In this example, the default "Sum" is selected. Under Selected Summary Items, select only Sales.

4. Click OK and Excel will classify and summarize by city.

2. Classify and summarize multiple columns

As in the above example, the "City" column and the "Product Name" column need to be classified and summarized at the same time, and nested classification summaries can be inserted.

1. Sort the data in multiple columns, that is, sort by multiple keywords.

First select a cell in the data area.

In Excel 2003, click the menu "Data → Sort". The "Sort" dialog box pops up, in which "City" is selected as the primary keyword, "Product Name" is selected as the secondary keyword, and the default is selected for other keywords.

If it is Excel 2007, click the "Sort" command in the "Sort and Filter" group of the "Data" tab, and in the pop-up "Sort" dialog box, click the "Add Condition" button to add a secondary key Sort by word, then select "city" as the primary keyword, "product name" as the secondary keyword, and select the default for other keywords.

2. Perform subtotals on the "City" column (external subtotals).

Open the "Classification Summary" dialog box as described above, select "City" under "Classification Field", select the default "And" in "Summary Method", and select only "Sales" under "Selected Summary Items" Forehead". Click OK.

3. Perform sub-totals (nested sub-totals) on the "Product Name" column.

Open the "Classification Total" dialog box again, select "Product Name" under "Category Field", deselect "Replace Current Classification Total", and click "OK".

At this time, Excel will classify and summarize the "sales" by the "City" column and the "Product Name" column.

If you do not need to display detailed data, you can click the outline display symbol on the left, such as the number in the upper right corner and the minus sign on the left in this example to hide the detailed data.

3. Delete classification summary

In the "Classification Summary" dialog box, click "Delete All".

How to summarize the data in each table into multiple worksheets in an excel table

For example, A2 in sheet0 starts with the license plate number, then B2 is the fuel consumption on the 1st, C2 is the fuel consumption on the 2nd, D2 is the fuel consumption on the 3rd,..., and so on, AE2 is 30 Daily fuel consumption. The operation is as follows:

1. In sheet0, enter the formula: B2=VLOOKUP(A2,Sheet1!A:B,2,0),C2=VLOOKUP(A2,Sheet2!A:B,2,0),D2=VLOOKUP (A2,Sheet3!A:B,2,0),...AE2=VLOOKUP(A2,Sheet30!A:B,2,0).

2. In sheet0, select B2 to AE2 (that is, all cells where formulas are set) and drag down until you reach the row with the license plate number in column A.

In this case, as long as you enter relevant data in different sheets, the master sheet will automatically recognize it, but the premise is that the license plate number must be consistent, otherwise it cannot be automatically recognized. You can try it first to ensure it works.

The above is the detailed content of Merge excel data from multiple different worksheets into one overall table. For more information, please follow other related articles on the PHP Chinese website!