Before the classification summary, you need to ensure that the data has the following format, that is, the first line of the data area of the header line, the data area is no empty rows and empty columns, the data area is surrounded by empty rows and empty columns, the following chart is a number of commodities in a number of cities in the sales data. In addition, if the data area has been set up as an Excel
2003 list or an Excel
2007 table before applying the classification summary, you need to convert it to a normal area. This is because you cannot use categorical summaries for Excel
2003 lists or Excel
2007 tables.
Only a classified summary of a column
For example, the above example of the need for sales in various cities to be categorized as a summary of the following:
1.
First of all, the data need to be categorized according to the summary of the column (in this case, the "city" column) for sorting.
Select the "city" in any cell in the column, in Excel
2003, click the toolbar in the sort button such as "A ?ú Z". In Excel
2007, select the ribbon in the "Data" tab, in the "Sorting and Filtering" group click "A ?ú Z" button.
2.
Select a cell in the data area, in Excel
2003, click the menu "Data ?ú Categorized Summary". If Excel
2007, then in the "Data" tab of the "hierarchical display" group click "Classification Summary".
3.
In the pop-up "Classification Summary" dialog box, in the "Classification Field" under the "City", in the "Summary Method". "Summary mode" in the selection of a summary method, the available summary methods are "sum", "count", "average value "and so on, in this case, select the default "sum". In the "Selected Summary Items" under only select "Sales".
4. Click OK, Excel will be categorized by city summary.
Two, multi-column classification summary
Such as the above example of the need for both the "City" column and "Trade Name" column for classification summary, you can insert a nested classification summary.
1. Sort the data in multiple columns, that is, multi-keyword sorting.
First select a cell in the data area.
In Excel
2003, click the menu "Data ?ú Sort". Pop-up "Sort" dialog box, where the main keyword selection "City", the secondary keyword selection "Trade Name", other options by default.
If Excel
2007, in the "Data" tab of the "Sorting and Filtering" group click on the "Sort" command, in the pop-up "Sort" dialog box, click the "Add Conditions" button to add a secondary keyword sorting conditions, and then the main keyword selection "City", the secondary keyword selection "Product Name", and other choices by default.
2. "City" column for classification summary (external classification summary).
Press the above method to open the "Classification Summary" dialog box, in the "Classification Field" under the "City", in the "Summary Mode " in the "Summary Method" select the default "sum", in the "Selected Summary Items" under the only select "Sales". Click "OK".
3. "Trade names" column for classification summary (nested classification summary).
Open the "Classification Summary" dialog box again, in the "Classification Field" under the "Trade Name", deselect "Replacement Current Category Summary", click "OK".
Then Excel will be "city" column and "trade name" column on the "sales" of the classification summary.
If you do not need to show the breakdown of data, you can click on the left side of the hierarchical display of symbols, such as the example of the upper-right corner of the number and the left side of the minus sign to hide the breakdown of data.
Three, delete the classification summary
In the "Classification Summary" dialog box, click "Delete All" can be.