Documentation/Calc Functions/AGGREGATE/de

    From The Document Foundation Wiki
    This page is a translated version of the page Documentation/Calc Functions/AGGREGATE and the translation is 2% complete.


    Function name:

    AGGREGAT

    Category:

    Mathematical

    Summary:

    AGGREGAT is a powerful but complex function that calculates a single number by applying a selected aggregate function to a specified set of data. Nineteen different aggregate functions are available.

    Unlike many other functions that perform similar calculations, AGGREGAT provides options to omit certain unwanted data types from its processing. Options are available to ignore errors, hidden rows, and nested TEILERGEBNIS and other AGGREGAT function results.

    Syntax:

    AGGREGAT(Function; Option; Number1[; Number2[; ... [; Number253]]])

    or

    AGGREGAT(Function; Option; Array; k)

    The first syntax is intended for use with Function values 1 to 13, while the second syntax is intended for use with Function values 14 to 19.

    Returns:

    Returns a numeric value that is the result of applying the selected aggregate function and ignore options to the specified set of data.

    Arguments:

    Function is an integer value in the range [1, 19], or a reference to a cell containing that number, which specifies the aggregate function to be used. The following table shows the mapping of these values to aggregate functions.

    Funktionsindex Function applied
    1 MITTELWERT
    2 ANZAHL
    3 ANZAHL2
    4 MAX
    5 MIN
    6 PRODUKT
    7 STABW.S
    8 STABW.N
    9 SUMME
    10 VAR.S
    11 VAR.P
    12 MEDIAN
    13 MODUS.EINF
    14 KGRÖSSTE
    15 KKLEINSTE
    16 QUANTIL.INKL
    17 QUARTILE.INKL
    18 QUANTIL.EXKL
    19 QUARTILE.EXKL

    Option is an integer value in the range [0, 7], or a reference to a cell containing that number, which specifies the ignore option to be used. The following table shows which data types are ignored for each value.

    Option index Option applied
    0 Ignore only nested TEILERGEBNIS and AGGREGAT functions
    1 Ignore only hidden rows, nested TEILERGEBNIS and AGGREGAT functions
    2 Ignore only errors, nested TEILERGEBNIS and AGGREGAT functions
    3 Ignore hidden rows, errors, nested TEILERGEBNIS and AGGREGAT functions
    4 Ignore nothing
    5 Ignore only hidden rows
    6 Ignore only errors
    7 Ignore only hidden rows and errors

    Number1, Number2, ..., Number253 are the numeric arguments to be processed when Function is in the range [1, 13]. These are similar to those of the standalone functions used for aggregation, see for example MITTELWERT, ANZAHL, and MAX. Each argument may take one of the following forms:

    • A number, or an expression that evaluates to a number.
    • A reference to a single cell containing a number.
    • A simple reference to a cell range containing numbers (for example, A1:B9).
    • The name of a named range, comprising cells containing numbers.
    • The name of a database range, comprising cells containing numbers.
    • An inline array of numbers (for example, {1, 2, 3, 4}).
    • The text of the heading label above a column of numbers, enclosed in single quotation marks - only available if Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate ▸ Automatically find column and row labels option is enabled (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate ▸ Automatically find column and row labels on macOS).

    Array contains the numeric arguments to be processed when Function is in the range [14, 19]. This argument is similar to the Data argument of the standalone functions used for aggregation, see KGRÖSSTE, QUANTIL.EXKL, QUANTIL.INKL, QUARTILE.INKL, QUARTILE.EXKL, and KKLEINSTE. The Array argument may take one of the forms listed above for Number1, Number2, ..., Number253.

    k is required when Function is in the range [14, 19] and specifies the second argument required by the standalone aggregation function. These are as follows:

    • KGRÖSSTE requires a RankC argument (positive integer).
    • KKLEINSTE requires a RankC argument (positive integer).
    • QUANTIL.INKL requires an Alpha argument (real number in the range [0, 1]).
    • QUANTIL.EXKL requires an Alpha argument (real number in the range (0, 1)).
    • QUARTILE.INKL requires a Type argument (integer in the range [0, 4]).
    • QUARTILE.EXKL requires a Type argument (integer in the range [1, 3]).

    The following error conditions may be encountered:

    • If either Function or Option is non-numeric, then AGGREGAT reports an invalid argument error (Fehler:502).
    • If either Function or Option is not an integer, the value is truncated to its floor value.
    • If Function is less than 1 or greater than 19, then AGGREGAT reports an invalid argument error (Fehler:502).
    • If Option is less than 0 or greater than 7, then AGGREGAT reports an invalid argument error (Fehler:502).
    • Other errors may be generated depending on the aggregate function selected and the data supplied. For example, a #DIV/0! error is generated if Function is set to 7 (STABW.S) and only one data value is supplied. Another example occurs when a #WERT! error is generated if Function is set to 13 (MODUS.EINF) and the supplied data contains no duplicate data points. More information about such error conditions can be found on the wiki pages for the standalone versions of the aggregate functions.

    Additional details:

    The AGGREGAT function is best used for columns of data, rather than rows of data, because hiding columns does not have any effect on the results of AGGREGAT.

    Examples:

    The following table shows sample data that is used in the subsequent examples of the AGGREGAT function.

    A B C
    1 ColumnOne ColumnTwo ColumnThree
    2 34 11 12
    3 10 56 35
    4 #DIV/0! 5 3
    5 20 8 1
    6 0 8 9
    7 #WERT! 20 21
    8 5 7 8
    9 14 0 5
    Formula Description Returns
    =AGGREGAT(1; 4; 1; 2; 3; 4; 5; 6) Here the function returns the average value (Function set to 1) of the six supplied numbers (1 to 6, specified in the third to eighth arguments), ignoring nothing (Option set to 4). 3,5
    =AGGREGAT(4; 2; A2:A9)

    Here the function returns the maximum value (Function set to 4) across the cell range A2:A9, ignoring hidden rows, and nested TEILERGEBNIS and AGGREGAT functions (Option set to 2).

    This example demonstrates the power of AGGREGAT in comparison to the equivalent standalone functions. The formula =MAX(A2:A9) returns a #DIV/0! error, propagating the error in cell A4. If this error is fixed by some change to cell A4, then the formula =MAX(A2:A9) returns a #WERT! error, propagating the error in A7. Using AGGREGAT avoids some of the data cleansing activity that might otherwise be required.

    || 34
    =AGGREGAT(9; 5; A5:C5) Here the function returns the sum (Function set to 9) across the cell range A5:C5, ignoring only hidden rows (Option set to 5). Note that the result remains unchanged as some or all of columns A-C are hidden. 29
    =AGGREGAT(9; 5; B2:B9) Here the function returns the sum (Function set to 9) across the cell range B2:B9, ignoring only hidden rows (Option set to 5). Note that the result changes as any of rows 2:9 are hidden - for example, hiding row 7 changes the result to 95. 115
    =AGGREGAT(2; 3; Sheet1.B2:Sheet3.B9) It is assumed that the document contains three sheets – with adjacent tabs Sheet1, Sheet2, and Sheet3 in the Sheet tabs area. It is further assumed that all these three tabs contain the same data in cells A1 to C9.

    Here the function returns the count (Function set to 2) across the three dimensional cell range Sheet1.B2:Sheet3.B9, ignoring hidden rows, errors, and nested TEILERGEBNIS and AGGREGAT functions (Option set to 3).
    24
    =AGGREGAT(E3; E5; 'ColumnTwo') where cells E3 and E5 contain the numbers 13.3 and 4.7 respectively.

    It is assumed that the option Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate ▸ Automatically find column and row labels is enabled (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate ▸ Automatically find column and row labels on macOS).

    Here the function returns the mode (Function set to truncated value of 13) of the values in ColumnTwo (cells B2:B9), ignoring nothing (Option set to truncated value of 4).

    || 8
    =AGGREGAT(15; 4; C2:C9, 3)

    Here the function returns the third (k set to 3) smallest (Function set to 15) of the values in the cell range C2:C9, ignoring nothing (Option set to 4).

    || 5

    Related LibreOffice functions:

    MITTELWERT

    ANZAHL

    ANZAHL2

    KGRÖSSTE

    MAX

    MEDIAN

    MIN

    MODUS.EINF

    QUANTIL.EXKL

    QUANTIL.INKL

    PRODUKT

    QUARTILE.EXKL

    QUARTILE.INKL

    KKLEINSTE

    STABW.N

    STABW.S

    SUMME

    VAR.P

    VAR.S

    ODF standard:

    None

    Equivalent Excel functions:

    AGGREGAT