Share via

WorksheetFunction.Aggregate Method (Excel)

  • 아티클

Returns an aggregate in a list or database.

Version Information

추가된 버전: Excel 2010

Syntax

식 .Aggregate(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

식 A variable that represents a WorksheetFunction object.

Parameters

Name

Required/Optional

Data Type

Description

Arg1

필수

Double

Function_num - A number from 1 to 19 that specifies which function to use.

Function_numFunction
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
12MEDIAN
13MODE.SNGL
14LARGE
15SMALL
16PERCENTILE.INC
17QUARTILE.INC
18PERCENTILE.EXC
19QUARTILE.EXC

Arg2

필수

Double

Options - A numerical value that determines which values to ignore in the evaluation range for the function.

OptionBehavior
0 or omittedIgnore nested SUBTOTAL and AGGREGATE functions
1Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2Ignore error values, nested SUBTOTAL and AGGREGATE functions
3Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4Ignore nothing
5Ignore hidden rows
6Ignore error values
7Ignore hidden rows and error values

Arg3

필수

Range

Ref1 - The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.

Arg4 - Arg 30

선택

Variant

Ref2 - Ref30 - Numeric arguments 2 to 30 for which you want the aggregate value.

Return Value

Double

Remarks

  • The following constraints apply to the Ref arguments (Arg3 - Arg 30) based on the Function_num value.

    Function_num

    Ref1

    Ref2

    Ref3, Ref4, …

    1-13

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references


    Invalid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays


    Invalid types:

    • Actual data

    • Arrays

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references


    Invalid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays


    Invalid types:

    • Actual data

    • Arrays

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references


    Invalid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays


    Invalid types:

    • Actual data

    • Arrays

    14-17

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays

    No references are allowed

  • 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.

참고 항목

개념

WorksheetFunction Object

WorksheetFunction Object Members