Returns an aggregate in a list or database using a specified function with the option to ignore hidden rows and error values.
構文
AGGREGATE(function_num, options, ref1, [ref2], ...)引数
function_num必須
A number from 1 to 19 that specifies which function to use for the calculation.
options必須
A number that determines which values to ignore in the range, such as nested subtotals or error values.
ref1必須
The first numeric argument for which you want the aggregate value.
The AGGREGATE function is a versatile tool that can perform various calculations like SUM, AVERAGE, or MAX while providing control over how to handle hidden rows, nested subtotal functions, and error values. It is particularly useful for creating robust reports that remain accurate even when data is filtered or contains errors.
=AGGREGATE(9, 6, A1:A10)→Sum of valuesCalculates the sum (9) of the range A1:A10 while ignoring error values (6).
Select the function number
Choose the function you need (e.g., 9 for SUM, 1 for AVERAGE) and enter it as the first argument.
Define the ignore options
Select an option number that dictates how the function handles hidden rows or errors, such as 6 to ignore error values.
AGGREGATE is more powerful because it can ignore error values and perform more types of calculations than SUBTOTAL.