20:53:00
AGGREGATE function and ways to use
Objective : Returns an aggregate in a list or database.
The AGGREGATE function addresses the limitation of
conditional formatting. Data bars, Icon Sets and Color Scales cannot display
conditional formatting if there are errors in the range. This is because the
MIN, MAX and PERCENTILE functions do not calculate when there is an error in
the calculation range. The LARGE, SMALL, and STDEVP functions also affect the
appropriate functionality of certain conditional formatting rules for the same
reasons. By using the AGGREGATE function, you can implement those functions
because the errors will be ignored. In addition, the AGGREGATE function can
apply different aggregate functions to a list or database with the option to
ignore hidden rows and error values.
Syntax
Reference form
AGGREGATE(function_num, options, ref1, [ref2], …)Array form
AGGREGATE(function_num, options, array, [k])
The AGGREGATE function syntax has the following arguments
(argument: A value that provides information to an action, an event, a method,
a property, a function, or a procedure.):
Function_num
Required. A number 1 to 19 that specifies which function to
use.Function_num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 LARGE
15 SMALL
16 PERCENTILE.INC
17 QUARTILE.INC
18 PERCENTILE.EXC
19 QUARTILE.EXC
Options Required. A
numerical value that determines which values to ignore in the evaluation range
for the function.Option Behavior
0 or omitted Ignore nested SUBTOTAL and AGGREGATE
functions
1 Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2 Ignore error values, nested SUBTOTAL and AGGREGATE
functions
3 Ignore hidden rows, error values, nested SUBTOTAL and
AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values
Ref1 Required. The first numeric argument for
functions that take multiple numeric arguments for which you want the aggregate
value.
Ref2,... Optional.
Numeric arguments 2 to 253 for which you want the aggregate value.
For functions that take an array, ref1 is an array, an array
formula, or a reference to a range of cells for which you want the aggregate
value. Ref2 is a second argument that is required for certain functions. The
following functions require a ref2 argument: Function
LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)
Remarks
Function_num:
As soon as you type the function_num argument when you enter
the AGGREGATE function into a cell on the worksheet, you will see a list of all
functions that you can use as arguments.
Errors:
If a second ref argument is required but not provided,
AGGREGATE returns a #VALUE! error.
If one or more of the references are 3-D references,
AGGREGATE returns the #VALUE! error value.
Nested Aggregates:
If there are other AGGREGATEs within ref1, ref2,… (or nested
AGGREGATEs), these nested AGGREGATEs are ignored to avoid double counting.
If there are SUBTOTALS within refs of the AGGREGATE
function, these SUBTOTALs are ignored.
If there are AGGREGATEs within the SUBTOTAL function, these
AGGREGATEs are ignored.
Type of Range:
The AGGREGATE function is designed for columns of data, or
vertical ranges. It is not designed for rows of data, or horizontal ranges. For
example, when you subtotal a horizontal range using option 1, such as
AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value.
But, hiding a row in vertical range does affect the aggregate.
Examples 1
%2B.png)
+.png)
Example 2
+.png)
Aggregate จัดเป็นสูตร Excel หรือ ฟังก์ชั่น Excel ที่รวมฟังก์ชั่นหายอดรวม หายอดสรุปไว้ด้วยกัน แล้วให้เราเลือกใช้โดยระบุในส่วนของ function_num ซึ่งมีทั้งหมด 19 ค่า ดังที่ได้กล่าวมาด้านบน ใน Excel Help ที่เป็นภาษาอังกฤษนี้
