Excel function learning is one against ten SUBTOTAL functions!

Newbies always like to complain, "Excel has too many functions and I can't remember them. Is there a function that can summarize the functions of many functions?" I'll be honest with you. Say, there really is! The biggest function of the function we are going to talk about today is that it can replace 11 functions. In addition, it can also change the final calculation result based on different filtering results! How about it? Doesn’t it just sound awesome? Without further ado, let’s take a look with the editor!

Ignore the filtered row summation

"Teacher Miao, I encountered a problem", Xiaobai came to me as soon as she got to work, she Said: "I have a table of totals. I don't want to print the content of certain people when printing, so I hide them with filters. However, the summing area must be changed every time I sum, which is always troublesome."

I said: "That's easy, just change the summation function. Don't use SUM, try SUBTOTAL."

Xiaobai: "What kind of function is this? I've never used it before."

I said: "This function is much more powerful than the SUM function. It can handle several summation scenarios!"

Xiaobai: "It's so powerful, then you have to teach me."

"Then let me tell you in detail~ First, let's solve the problem with your form." After saying that, I opened her form, as shown in the picture.

"Now, your table uses the SUM function for summation. Let's replace it with the SUBTOTAL function. You can take a look again." After that, I entered the formula in cell A7.

=SUBTOTAL(9,A2:A6)

"It's really changed!" Then Xiaobai filtered some more In other fields, she found that she could get the results she wanted, and she was very happy. But then she discovered the New World, "Then what does this 9 mean?"

Me: "This 9 means to ignore the unfiltered data and only sum the filtered results."

Xiaobai: "Listen to what you said, are there other numbers that represent other meanings?"

Me: "Of course, then I will tell you about the meanings of other numbers. !"

Ignore hidden row summation

We sometimes encounter this situation. There is a column of numbers and we need to hide several different numbers. The data to perform operations on. If you use SUM directly, you cannot get the correct results, as shown in the figure.

Even if you use the parameter "9" of the SUBTOTAL function you just learned, it cannot be achieved, as shown in the figure.

At this time we have to consider changing a parameter.

The following parameter is "109", come on!

Formula: =SUBTOTAL(109,A1:A5)

As shown in the figure below, change the first parameter of the SUBTOTAL function to After it is "109", you can easily get the summation result after ignoring the hidden rows! as the picture shows.

The function of parameter "109" is to sum the visible values. It can sum the hidden data or the filtered data. The parameter "9" can only be used in filtered rows and has no effect on hidden rows.

Application of other parameters of SUBTOTAL

SUBTOTAL is not limited to the field of summation, average, maximum, standard deviation, and variance are all You can find it, just change its first parameter. For example, now we want to count the maximum value of ignored hidden rows, as shown in Figure 6.

Formula: =SUBTOTAL(104,A1:A5)

##          

(before hiding)                                                                                                                                                              

##After hiding the maximum value "8", the currently visible maximum value "7" is directly obtained in cell A6.

Then why is it 104? In fact, there is a set of number representation rules in the SUBTOTAL function. Today we will talk about the other parameters, including 11 functions such as average, maximum, minimum, standard deviation, and variance. Some are commonly used and some are not commonly used. You can choose based on your own needs. Below is a comparison table of 11 parameters.

##2 102Count the number of cells containing numbersCOUNT3103Calculate the number of non-empty cellsCOUNTA45##107 Calculate sample standard deviationSTDEV##891011

Ignore filtered values ​​when calculating	Ignore hidden rows and filtered values ​​when calculating	Function	Corresponding function
##1	101	AVERAGE	AVERAGE
##104	Maximum	MAX
105	Minimum value	MIN
6	106	Multiplication	PRODUCT
##7
108	Calculate population standard deviation	STDEVP
109	SUM	SUM
##110	Calculate sample variance	VAR
111	Calculate population variance	VARP

##Expansion Part 1: Only statistical classification and summary

When we are making tabulations, we often encounter such a summary situation, sub-summarizing in the same table, as shown in the figure shown.

If you use SUM for summary, all data will be counted, as shown in the figure.

But we only want to add up the contents of each subtotal! Don't panic, just replace SUM with SUBTOTAL to get the answer we want. as the picture shows.

Why is this? In fact, SUBTOTAL can not only ignore hidden and filtered rows, but also ignore cells containing SUBTOTAL and AGGREGATE functions. Cells B3, B6, and B10 are all subtotals calculated using the SUBTOTAL function. Naturally, they will be ignored when the SUBTOTAL function is used for the final sum.

Expansion Part 2: Uninterrupted Serial Number

"After we understand the characteristics of the SUBTOTAL function, You can use it to do something, such as numbering a list."

"What, can't the list number just be pulled with the mouse?"

"It's different~my number , but it’s automatic! Whether you delete rows or hide rows, the numbers can be rearranged automatically!”

“It’s so magical, then I have to learn it hard.”

In fact, it is very Simple, suppose I have a list, and the serial number column is currently empty, as shown in the picture.

Enter the formula in cell A2:

=SUBTOTAL(103,B$2:B2), then drop down to fill in, and you will get what we want serial number. as the picture shows.

When we try to hide a row, we will find that the serial numbers are still arranged in order without interruption, as shown in the figure.

Now let’s explain the formula step by step

=SUBTOTAL(103,B$2:B2)

• 103: Looking at the above parameter comparison table, we can see that the function of 103 is to ignore hidden rows and filtered values, and count the number of non-empty cells.

• B$2:B2: The area in cell A2 is B$2:B2. The purpose is to count the number of non-empty cells in the B2:B2 area, and the result is 1. After the formula is pulled down, the area in cell A3 becomes B$2:B3, then the number of counted non-empty cells becomes two, and the result obtained is 2. as the picture shows.

By analogy, as the formula is pulled down, we can get a set of consecutive serial numbers. Combined with the characteristic that the first parameter of the SUBTOTAL function only calculates visible values, you can get a set of uninterrupted serial numbers!

What else do you know about the wonderful uses of the SUBTOTAL function? Welcome to leave a message and share it with us~ Friends who like the article may wish to click "Reading" to support us!

Related learning recommendations:

excel tutorial

The above is the detailed content of Excel function learning is one against ten SUBTOTAL functions!. For more information, please follow other related articles on the PHP Chinese website!