In the previous article "Excel Case Sharing: Using Function Formulas to Verify the Authenticity of the ID Number", we learned the function formula of Excel to verify the authenticity of the ID number. Today we will talk about summing across worksheets in Excel, and introduce to you 4 methods. For different tables, you can quickly merge data across worksheets in Excel, faster than you can imagine!
We all know that in the same worksheet, if you want to get the total result of multiple values, you usually use the SUM function or the higher-order SUMIF and SUMIFS, but if the data is in different In the worksheet, should we implement cross-sheet summation statistics in Excel? Today, I will introduce to you two methods to help achieve cross-sheet totaling of values based on two cases of consistent rows and inconsistent rows.
1. Totals of multiple tables with consistent rows and rows in Excel
As shown in the figure, you need to add each department in the three worksheets from January to March The sales data of each product are aggregated into a summary table. The row, column, field names and sort order in these three tables are consistent.
Let me introduce two methods to you.
The first method:
1. Copy and paste January’s data into the summary table
2. Copy February’s data , then select cell B2 in the summary table, right-click and paste special.
For operation, select Add and OK to add the data for January and February together.
3. In the same way, copy the data for March, then selectively paste it in the summary table, and select Add for calculation.
In this way, the total data of the three tables is obtained in the summary table.
Summary:
Use the special paste operation to quickly add the values in multiple areas to achieve the goal.
Advantages: Easy to operate and easy to use.
Disadvantages: You must select areas of the worksheet one by one to copy and paste. If there are too many worksheets, the operation is cumbersome.
Second method:
1. Enter =SUM in cell B2 of the summary worksheet to bring up the function editing interface.
2. Select the worksheet January, hold down the Shift key, and then select the worksheet March. In this way, all worksheets from January to March are selected, and then click cell B2.
3. Click enter to complete the formula input. The formula is: =SUM("January:March"!B2). Then in the summary table, the formulas are filled to the right and down.
Summary:
Select the first worksheet, hold down the Shift key and then select the last worksheet to form a group of consecutive worksheets and then use The SUM function sums the same cells in this group. This kind of worksheet group is very convenient when we perform batch operations on the same worksheet.
Advantages: The method is fast and multiple worksheets can be operated quickly.
Disadvantages: Each worksheet's row, column, field names and sorting order must be exactly the same.
2. Totals of multiple tables with inconsistent rows and rows in Excel
It is impossible for us to encounter the above situation 100% during the work process If the table templates are completely consistent, what if the rows and rows are inconsistent?
As shown below, we need to count the sales of different products sold by different service providers from January to March. The rows and columns of each table are not completely consistent, and the order is also different.
Here we also introduce two methods to you.
The first method:
1. Select any blank cell under the summary worksheet, click the Consolidate Calculation in the Data Tools group under the Data tab .
2. In the combined calculation window, click the button next to Browse and select the areas to be calculated in the three worksheets in turn.
3. Click Add, and the newly added cell areas will appear in all reference location areas below. After all are added, click on the first row and leftmost column to confirm .
Tips: When selecting the first worksheet area, select an area that can include three worksheet cells. For example, the above example is $ A$1:$E$5, so when you click on other worksheets, the reference position will select $A$1:$E$5 by default, so you don’t have to reframe the range for each worksheet.
4. All the data has been summarized in the worksheet, and finally the format can be modified.
Summary:
The combined calculation is summarized according to the standards of the first row and the leftmost column, and the two fields are completely consistent. The total can be added up. If there is any inconsistency, it will be automatically displayed in a new row or column.
Advantages: Easy to operate, suitable for fields with inconsistent rows and columns
Disadvantages: When the source data changes, the results cannot be automatically refreshed. If data analysis is required, it is impossible to determine where the data comes from. month.
Second method:
1. Enter the ALT D P shortcut key on the worksheet to pop up the Pivot Table and Pivot Chart Wizard. Select the multiple consolidated data area and pivot table, click Next
2. Click the custom page field and select Next.
3. In the selected area, select the area that needs to be summarized in January and add it to all areas.
The page field created in the pivot table must be 1, and enter January in field 1.
In the same way, add February and March in sequence.
Tips: Just like consolidated calculations, here we can also select a range of three worksheet cells when selecting the first worksheet range. range, clicking other worksheets will include these cell areas by default, but the pivot table will be blank, then we can filter out the blanks.
Note: In all areas, clicking on the field below each area will display the newly added field names in the corresponding area for easy inspection to avoid duplicate or incorrect field name input. Of course, if you don’t want to add field names here, the merge can be successful.
4. The pivot table display position is in the new worksheet.
#5. In this way, we will get a pivot table to complete the total of multiple tables.
Summary:
The method of multiple merged pivot tables can summarize multiple worksheets as the same data source for pivoting. For table calculations, our newly added fields are displayed on page 1, and data can be analyzed and merged completely according to the pivot table method. And if the data in other worksheets changes, we can get the new total by refreshing the data table.
Advantages: Advanced analysis of data and linked updates of data sources can be achieved.
Disadvantages: The operation is relatively complicated.
The four methods introduced above each have their own advantages and disadvantages, and are applicable to different situations. I hope you can choose a method that is more suitable for you based on your actual needs.
Related learning recommendations: excel tutorial
The above is the detailed content of Practical Excel skills sharing: Four methods for summing across worksheets. For more information, please follow other related articles on the PHP Chinese website!
