Excel data subtotals meet a variety of data collation requirements

As shown in Figure 1, we often have access to excel two-dimensional data tables in our daily work, and we often need to subtotal the data by using a column data field in the table (such as "Project Type").

Here we will introduce different solutions for three different kinds of aggregate requirements. These three types of requirements are: both want to classify and print, do not want to classify printing just want to see the details of various types of data and statistics, do not want to disrupt the normal flow of data table data order but want to see the statistics of the various types of data at any time (this assumes that the results are saved in another worksheet).

(Figure 1)

Need one, both want to subtotal, and want to classify print

Solution: Use the "subtotals" feature built into Excel directly.

1. Select any cell in the Project Type column and click the Sort ascending or descending sort button on the Standard toolbar to sort the data.

Note: When using the subtotals feature, be sure to sort by Category objects!

2. Perform the data → subtotal command to open the Subtotals dialog box (Figure 2).

Figure 2

3, set the category field to the project type, the summary method to sum, the selected summary item to area, and the cost, and then select the per-group data paging option. Finally, determine the return.

4, the subtotal completed (Figure 3 is the "comprehensive office" class print preview results).

Figure 3

Requirements two, do not want to classify printing, just want to see the details of various types of data and statistics

Solution: Use Excel's own AutoFilter feature to implement it.

1, any selection of a cell in the data table, to perform the "data → filter → AutoFilter" command, into the "AutoFilter" state.

2. Select F203, G203 cells (this assumes a total of 200 data in the table), enter the formula: =subtotal (9,f3:f202) and =subtotal (9,g3:g202).

Tip: This function has a special function, that is, after the automatic filtering, the hidden rows of data will not be counted to achieve the purpose of sorting statistics.

3, in the future need to view a certain category (such as "economic housing") data details and statistics, click on the "project type" to the right of the Drop-down button, in the following pop-up shortcut menu (as shown in Figure 4) can be.

Figure 4

Requirements three, if we do not want to disrupt the normal flow data table data order, but want to view the statistical results of various types of data at any time (this assumes that the results are saved in another worksheet)

Solution: Use Excel's functions to implement.

1, switch to the SHEET2 worksheet, modeled after the Figure 5 style, make a statistical table.

2, select B3, C3, D3 cells, input formula: =countif (sheet1! $E $: $E $202,a3), =SUMIF (sheet1! $E $: $E $202,a3,sheet1! $F $: $F $202), =SUMIF (sheet1! $E $: $E $202,a3,sheet1! $G $: $G $202).

3, while selecting B3, C3, D3 cell, move the mouse to the lower right corner of the D3 cell into a fine cross, press and hold the left button down to the D10 cell, copy the formula above to B4 to D10 cell range.

4, select B11 cell, enter formula: =sum (B3:B10), and imitate the above operation, copy this formula to C11 and D11 cells.

After confirmation, the statistics are immediately presented to us (Figure 5).

Figure 5