Address: http://bbs.esnai.com/thread-4702567-1-1.html
Filtering in Excel is a very common function. However, I do not know whether it is intentional or negligent. Excel does not directly provide some statistical functions after filtering, such as sum and average. One of the main functions of filtering is to conveniently and quickly perform transformations. Common summation in the bottom row of the data cannot be achieved, and the calculated value is inaccurate.
Table 1: Enter a simple chart with 10 categories and a total of the following rows.
Table 2: Filter and select "If You Are the One" and "Total" in the name. At this time, the total value (980) is still the total value of 10 items in table 1, rather than the total value of a single item (420) that looks like you are the one ).
Table 3: at this time, you need to use the subtotal function and enter the formula: = SUBTOTAL (109, $ C $2: $ C $11) in the cell C13)
Table 4: filter again at this time. Select the "If You Are the One" option in the name to get the correct result.
Table 5: In addition to summation, the subtotal function can also be used for counting, minimum, maximum, and average calculation statistics. However, when performing multiclass classification statistics at the same time, you need to select the corresponding name item during filtering.
Note: SUBTOTAL Function
Category: Mathematics and trigonometric Functions
Return to the data list or Category summary in the database. Generally, you can use the "Category summary" command in the "data" menu to easily create a data list with Category summary. Once a Category summary is created, you can modify the data list by editing the SUBTOTAL function.
Syntax
SUBTOTAL (_ num, ref1, ref2 ,...)
_ Num is a number that specifies the function used for classification and summary calculation in the data list.
Ref1 and ref2 are the regions or references for classified summary calculation. They can range from 1 to 29.
Note:
1. The subtotal function is only applicable to vertical areas, but not to data rows or horizontal areas.
2. _ num:
2.1 101 average (average)
2.2 102 count (numeric count)
2.3 103 counta (Inclusion text count)
2.4 104 max (maximum)
2.5 105 min (minimum)
2.6 106 product (product of all numbers)
2.7 107 STDev (estimated standard deviation of the sample, reflecting the degree of discretization between the deviation and the average value)
2.8 108 stdevp (returns the standard deviation of the entire sample population. It reflects the degree of discretization between the sample population and the average value)
2.9 109 sum (SUM)
2.10 110 VAR (Estimation of variance Based on given samples)
2.11 111 varp (calculate the variance Based on the given sample population)
3. If ref1, ref2 ,... Other classification summaries (nested classification summaries) are ignored to avoid repeated calculation. That is, the results obtained by subtotal in the data area will be ignored!
This function is also applicable to charts that prefer "subtotal. We know that when subtotal exists, calculating the total is the most likely to produce repeated items, but using the subtotal function does not have this drawback. For example, in table 6, the subtotal function is used for the subtotal and Category summary, and there is obviously no repeated calculation.
Continue to use table 6 for filtering. Select "If You Are the One" and "subtotal" in the name to obtain table 7.
4. The _ num number can be from 1 to 11, or from 101 to 111. The difference between the two is that if a row is manually hidden, the former will still calculate the value of the manually hidden row, while the latter will ignore the numeric values that manually hide rows. However, the values automatically hidden after filtering are ignored.