Learn the amazing AGGREGATE function in Excel, one is worth 19!

In the previous article "Sharing practical Excel skills: Using "Find and Replace" to filter date data", we learned about several practical operations of "Find and Replace". Today we are going to talk about a magical Excel statistical function, which can actually be worth 19. It is simply an artifact! Collect it quickly.

The function I want to introduce to you today is called AGGREGATE. Although it is a function that exists in Excel 2010, not many people know about this function. This is a very regrettable thing, because the AGGREGATE function can not only implement the functions of 19 functions such as SUM, AVERAGE, COUNT, LARGE, etc. And you can ignore hidden rows, error values, null values, etc. If the range contains error values, functions such as SUM will return an error, and the AGGREGATE function is very convenient in this case.

Just talk and don’t practice fake moves, let’s take a look at AGGREGATE’s skills.

We use a score table to illustrate the basic usage of AGGREGATE. The data source is as shown in the figure:

Each student takes six tests. According to the results To get the five statistical contents of the blue area, I believe that for most friends, it is not difficult to complete this form. It is nothing more than mastering a few basic functions: AVERAGE (average score), SUM (total score), MAX (Highest score), MIN (lowest score) and COUNT (actual reference subject) are used to count the five contents respectively. There may be some new friends who don’t understand the five functions mentioned above, so just in case, you only need to learn the AGGREGATE function to realize the statistics of the above data.

The basic format of AGGREGATE is: = AGGREGATE (statistical function, ignored values, data area). Let’s take a look at how to complete the five statistical contents in the example.

1. Statistical average score

The current table average score statistical formula is: =AGGREGATE(1,,B2:G2) . Enter the formula in cell H2 and fill the entire column with the formula to get the average score of each student.

Description: When the statistical function is 1, the function implements the function of calculating the average. In this example, we do not need to specify the data to ignore statistics, so the second parameter can be omitted (two commas are written here, and one parameter is omitted in the middle). The last parameter is the data area B2:G2 to be calculated. Function usage Very simple, so is the result correct? You might as well use the AVERAGE function to verify:

You can see that the results are exactly the same!

Next let’s look at how to use the AGGREGATE function to count the total score.

2. Statistical total score

The current table total score statistical formula is: =AGGREGATE(9,,B2:G2) . Enter the formula in cell I2 and fill the entire column with the formula to get the total score of each student.

Just change the first parameter to 9, because 9 corresponds to the summation function.

Speaking of this, some friends may be worried that 1 in the first parameter represents the average and 9 represents the sum. This function has a total of 19 functions, so it will be difficult to remember.

In fact, there is no need to worry about this at all. Excel provides us with a very smart reminder function. After we enter the function, there are options corresponding to the parameter function:

Just follow this prompt and select the function you need.

3. Statistics of the highest score

After understanding this function, the last three statistical items can be easily completed. The highest score must be Select 4, so the formula in cell J2 is: =AGGREGATE(4,,B2:G2)

## 4. Statistics Minimum score

Select 5 for the lowest score, the formula in cell K2 is:

=AGGREGATE(5,,B2:G2)

5. Statistics actual reference subjects

The actual reference account is the number of numbers in the statistical data area. Use the COUNT function and select 2, so the formula is: =AGGREGATE(2,,B2:G2)

Okay, through the above five examples, friends should have a grasp of the basic usage of AGGREGATE. Although only one function is used to complete the work of five functions, compared with the previous Using five functions to complete the work respectively improves the efficiency to a certain extent, but each formula still needs to be modified before it can be used. It would be great if you could use a formula to pull down right. (Friends who feel the same can leave a message at the end of the article)

Six and five types of statistics can be done in one step

For friends who have this idea, Praise should be given. After all, we learn Excel function formulas not only to complete the work, but also to improve efficiency. So is it possible to use a formula to pull down right to complete the five statistics in the example? The answer is yes: yes! However, a pair of function combinations are required, namely choose and column.

Before revealing the formula, let’s conduct a simple analysis of the problem. In the formula we use AGGREGATE to complete five data statistics, only the first parameter, which is the statistical method, is changing, in order: 1, 9 ,4,5,2. If you want to use a formula to pull down to the right, you have to make the first parameter of the formula change in this order when pulling down to the right (no changes are needed when pulling down, because the statistical method is the same).

Usually when you need to use a formula to pull right to get sequentially changing data, you will use the column function:

The function of the column function is to get the parameter correspondence The column number, for example, column (a1) will get the column number of cell a1, which is 1. When you pull it to the right, a1 will become b1, c1..., the formula result will be in the order of 1, 2, 3... Variety.

In this example, what we need to get is not a very regular sequence, but an unordered sequence of 1, 9, 4, 5, 2. At this time, we need to use the choose function. To achieve:

The basic format of the Choose function is: =choose(select index, value 1, value 2, value 3...)

TheChoose function returns the value in the parameter list based on the number of the first parameter. For example, in the figure above, when the first parameter is 1, the first value "1" in the parameter list is returned; when the first parameter is 2, the second value "9" in the parameter list is returned. By analogy, using column as the first parameter of choose can return the specified sequence.

The above is an explanation of the function combination of choose and column. Now back to our problem, the formula that can be used for right pull-down is: =AGGREGATE(CHOOSE(COLUMN(A1), 1,9,4,5,2),,$B2:$G2)

Some novices may still feel dizzy, this is normal , I believe that through continuous learning, you will be able to use this formula freely.

7. First parameter function collection

Through the above introduction, we can see that when we use the AGGREGATE function reasonably, the work efficiency will increase. Double growth. What are the 19 functions of the first parameter of this function? You can see it clearly through the following comparison table:

In fact, these are the most commonly used ones.

8. Second parameter function collection

Next let’s take a look at what the second parameter is, or through a comparison table To understand intuitively:

1. Ignore null values ​​

Let’s take a look at how to use the second parameter through two examples. Select the content to ignore: =AGGREGATE(9,1,B2:B15)

The first parameter selects 9, which represents summation, and the second parameter Selecting 1 means ignoring hidden rows. When all the data is displayed, the result of using the AGGREGATE function is consistent with the result of using the SUM function (the total score of row 16 is summed using the SUM function). When we hide some of the rows When reading the data, you can see the difference:

After hiding rows 4, 8, and 11, the formula =AGGREGATE(9,1,B2:B15) only summarizes the currently displayed data.

Speaking of which, students who have learned the SUBTOTAL function will definitely think that SUBTOTAL also has such a function. However, the AGGREGATE function that appeared today is more powerful than the SUBTOTAL function, because SUBTOTAL cannot handle error values ​​and nested summary summary, but AGGREGATE can still handle it.

2. Ignore error values

The last example today, let’s see what happens when an error value is encountered:

As shown in the picture above, the Chinese scores of each student are obtained from the score table using the vlookup function (this function has been discussed in a tutorial before, and those who don’t know it yet can click on the link to learn about it: Insert link ). When the name is not in the score table, an error value will be obtained, such as Li Si and Zhang San. At this time, no matter we use the SUM function or the SUBTOTAL function, we cannot get the correct total score of the Chinese language score. Only AGGREGATE can ignore the error value. to the correct result. Of course, you can use iferror and other functions to process and then use SUM to sum, but this does not cover up the power of AGGREGATE.

19 kinds of statistical functions plus 7 kinds of ignored items. This incredible integration function is really not comparable to ordinary functions! AGGREGATE is the well-deserved king of statistical functions, collect it now!

Related learning recommendations: excel tutorial

The above is the detailed content of Learn the amazing AGGREGATE function in Excel, one is worth 19!. For more information, please follow other related articles on the PHP Chinese website!