Example summary of eight uses of the AGGREGATE function in Excel

This article brings you relevant knowledge about excel, which mainly introduces the relevant content about the AGGREGATE function. The usage of this function is similar to the SUBTOTAL function, but it is more functional than the SUBTOTAL function. It's powerful, let's take a look at it, I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

The usage of the AGGREGATE function is similar to the SUBTOTAL function, but it is more powerful than the SUBTOTAL function. , not only can realize the functions of 19 functions such as SUM, AVERAGE, COUNT, LARGE, MAX, etc., but also can ignore hidden rows, error values, null values, etc., and supports constant arrays.

The first parameter of this function is a number between 1 and 19, used to specify the summary method to be used:

The second parameter is the A number between 0 and 7, specifying which types of values ​​to ignore in the calculation area:

Next let’s talk about some typical uses of this function:

1. Multiple discontinuous areas ignore error values ​​and directly sum them

The power of this function is that the 2nd parameter can be specified to ignore error values ​​and directly count

As follows Figure, the blue area contains different error values, now we need to sum these discontinuous areas.

The formula is:

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2. Ignore error values ​​in filtering state

As shown below, the filtered data area contains error values. How to count the visible cells?

The formula is:

=AGGREGATE(9,7,B6:B18)

##First parameter Use 9 for the sum, and use 7 for the second parameter to ignore hidden rows and error values.

3. One formula solves multiple statistical effects

As shown below, the filtered data is in the cell area A3:B14. The maximum and maximum values ​​of the visible area and all data must be counted respectively. Minimum, average, sum, count and median.

Just one formula is enough:

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

Note that it is an area array formula. First select the c17:d22 area, then write the formula in the edit bar, and finally press ctrl shift enter to enter it.

4. You all know how to sum up upwards, even if it is filtered, what about downwards?

=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)

In addition to the downward summing direction, there are also hidden and error values. This is a statistical effect that cannot be achieved by subtotal sum(if)

(The input method is to select the area to position the null value and then write in the edit bar Complete the formula ctrl enter batch filling)

5. The beginning is the key point - conditional extreme value statistics

This function has realized the statistics of maxifs and minifs functions that were only available in 2016 five years in advance. Effect, and no need for three keys.

As shown below, to calculate the minimum value corresponding to 1 workshop, the formula is:

=AGGREGATE(15,6,B4:B15/(A4:A15="1 workshop" ),1)

The first parameter in the formula uses 15, which means using the SMALL function, and the second parameter uses 6, which means ignoring the error value. The area to be counted is the B4:B15/(A4:A15="1 workshop")

A4:A15="1 workshop" section. First compare whether the workshops in column A are equal to the specified conditions. If the cell range A4:A15 is equal to "1 workshop", the logical value TRUE is returned, otherwise the logical value FALSE is returned. Then divide this memory array by B4:B15, the result is:

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;19;47}

Finally, the AGGREGATE function ignores the error value inside and gets the first minimum value.

If you want to calculate the third minimum value corresponding to workshop 1, you only need to change the last 1 to 3.

If we want to calculate the maximum value corresponding to workshop 1, we can modify the first parameter and use 14, which is the kth maximum value.

6. One-to-many query

If you want a one-to-many query, many people think of the three-key combination of the INDEX SAMLL IF function. In fact, it is also possible to replace it with aggregate function.

As shown below, to extract all the work numbers of the second workshop, you can use the following formula:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW( $3:$12)/(A$3:A$12=D$3),ROW(A1))),"")

The idea of ​​this formula is basically the same as the fifth formula.

7. Count the maximum value in the same cell

As shown below, the assessment status of multiple people in column B is written in the same cell, and the maximum value must be counted. The formula is:

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)

For the MID(B4,ROW($1:$50),COLUMN(A:AZ)) part of the formula, use the MID function, starting from the 1st to 50th characters, and extract each A string with a length of 1~50 will result in a huge memory array. Use two more negative signs to turn the text in the memory array into an error value, and the value is still its own value.

Finally use the AGGREGATE function to ignore the error values ​​in the memory array and calculate the first minimum value among them.

8. Simultaneously count the maximum and minimum values ​​of the specified conditions

As shown in the figure below, it is necessary to count the maximum and minimum values ​​corresponding to 1 workshop at the same time.

First select cells F4:G4 at the same time, enter the following formula in the edit bar, press Ctrl Shift and press Enter.

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

AGGREGATE The first parameter uses the constant array {16,15}, which indicates the calculation rules of using the maximum value and the minimum value respectively.

The final result is also a memory array, so two cells must be selected for input at the same time.

The characteristic of this function is that when the first parameter is 14~19, you can use the fourth parameter. At this time, the fourth parameter supports arrays, so you can play various applications to replace the direct SMALL, LARGE and other functions that ignore error values.

Related learning recommendations: excel tutorial

The above is the detailed content of Example summary of eight uses of the AGGREGATE function in Excel. For more information, please follow other related articles on the PHP Chinese website!