Documentation/Calc Functions/SUBTOTAL

    From The Document Foundation Wiki
    This page is a translated version of the page Documentation/Calc Functions/SUBTOTAL and the translation is 2% complete.
    Outdated translations are marked like this.

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    Funktionsnavn:

    SUBTOTAL

    Kategori:

    Matematisk

    Resume:

    Beregner subtotaler. Hvis et område allerede indeholder subtotaler, bruges de ikke til videre beregninger. Brug denne funktion med Autofiltre til kun at tage de filtrerede poster i betragtning.

    If the data range processed by SUBTOTAL already contains any subtotals, these are ignored to avoid counting values twice. If any row within the data range is filtered out, its data is ignored by SUBTOTAL. SUBTOTAL can optionally include or exclude data from rows within the data range that have been manually hidden.

    Note pin.svg

    Bemærk:

    Calc provides a powerful and more comprehensive alternative to the SUBTOTAL function, accessed by selecting Data ▸ Subtotals on the Menu bar. More details can be found in the system help by searching for "subtotals tool".


    Syntaks:

    SUBTOTAL(Function; Range)

    Returnerer:

    Returnerer en reelt værdi, som er subtotalen på en liste eller i en database.

    Argumenter:

    Funktion er et heltal eller en reference til cellen, der indeholder det heltal. der står for en af følgende funktioner:

    Mapping of Function argument values
    Include hidden rows Exclude hidden rows Subtotal function
    1 101 MIDDEL
    2 102 TÆL
    3 103 TÆLV
    4 104 MAKS
    5 105 MIN
    6 106 PRODUKT
    7 107 STDAFV
    8 108 STDAFVP
    9 109 SUM
    10 110 VARIANS
    11 111 VARIANSP

    Område er referencen til den celle, der medtages.

    • A simple reference to a cell range containing real numbers (for example, A1:A25).
    • The name of a named range, comprising cells containing real numbers.
    • The name of a database range, comprising cells containing real numbers.

    In addition, the following forms for the Range argument do not cause an error but are not likely to be particularly useful:

    • A real number, or an expression that evaluates to a real number. For example, the formula =SUBTOTAL(1; 3) returns 3.
    • A reference to a single cell containing a real number. For example, if cell D1 contains the number 123, the formula =SUBTOTAL(1; D1) returns 123.
    • An inline array of real numbers (such as {1,1; 2,2; 3,3; 4,4}). For example, the formula =SUBTOTAL(6; {1; 2; 3; 4; 5}) returns 120.
    • Hvis tallet ikke er 1-11 eller 101-111, returnerer funktionen en værdifejl.
    • Hvis en vilkårlig celle i området ikke indeholder en numerisk værdi, ignoreres denne celle.
    • If Function in non-numeric, then SUBTOTAL reports a #VÆRDI! error.
    • If Function is a non-integer value, then SUBTOTAL truncates it to an integer.
    • If Function is neither in the range [1, 11] nor in the range [101, 111], then SUBTOTAL reports an invalid argument error (Fejl:502).

    Yderligere oplysninger:

    • Ingen
    • Hiding columns has no impact on the results produced by the SUBTOTAL function.
    • See their individual pages for more details about each of the eleven possible subtotal functions.

    Eksempler:

    Du har en tabel i celleområdet A1:B6, der indeholder en materialeoversigt for 10 studerende. Række 2 (Pen) er skjult manuelt. Du vil se det viste, samlede antal; dvs. bare subtotalen af de filtrerede rækker. I dette tilfælde er den korrekte formel:

    A B
    1 Student Score
    2 Ann 2
    3 Brian 10
    4 Edward 8
    5 James *
    6 Jeffrey 5
    7 Linette 10
    8 Monica 8
    9 Ray 3
    10 Robert 4
    11 Tamara 6
    12 Victoria 7
    13 William 9


    The students' names are listed in alphabetical order in cells A2:A13 and their scores are listed in cells B2:B13. The asterisk (*) in cell B5 indicates that James was absent and unable to take the test. The following examples are based on the data in the above table.

    Formel Beskrivelse Returnerer
    =SUBTOTAL(2; B2:B13)

    Use the TÆL function to count the number of scores. TÆL ignores strings, so the asterisk in cell B5 is ignored. As there are no hidden rows in the data, the formula =SUBTOTAL(102; B2:B13) returns the same value.

    		11
    =SUBTOTAL(3; B2:B13)

    Use the TÆLV function to count the number of scores. TÆLV includes strings, so the asterisk in cell B5 is counted. As there are no hidden rows in the data, the formula =SUBTOTAL(103; B2:B13) returns the same value.

    		12
    =SUBTOTAL(4; B2:B13)

    Use the MAKS function to determine the maximum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(104; B2:B13) returns the same value.

    		10
    =SUBTOTAL(5; B2:B13)

    Use the MIN function to determine the minimum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(105; B2:B13) returns the same value.

    		2

    For the following examples the same data table is used but row 2 is hidden (by right-clicking on the row number and selecting Hide Rows from the context menu).

    Formel Beskrivelse Returnerer
    =SUBTOTAL(2; B2:B13)

    Use the TÆL function to count the number of scores. TÆL ignores strings, so the asterisk in cell B5 is ignored. In this case, SUBTOTAL includes values in hidden rows.

    		11
    =SUBTOTAL(102; B2:B13)

    Use the TÆL function to count the number of scores. TÆL ignores strings, so the asterisk in cell B5 is ignored. In this case, SUBTOTAL ignores values in hidden rows.

    		10
    =SUBTOTAL(5; B2:B13)

    Use the MIN function to determine the minimum score value. In this case, SUBTOTAL includes values in hidden rows.

    		2
    =SUBTOTAL(105; B2:B13)

    Use the MIN function to determine the minimum score value. In this case, SUBTOTAL ignores values in hidden rows.

    		3

    For the final examples, the data table is reverted to its original state by unhiding row 2 (by selecting the row numbers for rows 1 and 3, right-clicking on either of the selected row numbers, and selecting Show Rows from the context menu). Then AutoFilters are applied by left-clicking within the cell range A1:B13 and then selecting Data ▸ AutoFilter on the Menu bar. Finally, press the down arrow in the Score column heading, untick the check box next to the number 10 near the bottom of the dialog, and press the Accept button. Rows 3 and 7 are now filtered out.

    Formel Beskrivelse Returnerer
    =SUBTOTAL(2; B2:B13)

    Use the TÆL function to count the number of scores. TÆL ignores strings, so the asterisk in cell B5 is ignored. As there are no hidden rows in the data, the formula =SUBTOTAL(102; B2:B13) returns the same value.

    		9
    =SUBTOTAL(3; B2:B13)

    Use the TÆLV function to count the number of scores. TÆLV includes strings, so the asterisk in cell B5 is counted. As there are no hidden rows in the data, the formula =SUBTOTAL(103; B2:B13) returns the same value.

    		10
    =SUBTOTAL(4; B2:B13)

    Use the MAKS function to determine the maximum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(104; B2:B13) returns the same value.

    		9

    Beslægtede LibreOffice-funktioner:

    MIDDEL

    TÆL

    TÆLV

    MAKS

    MIN

    PRODUKT

    STDAFV

    STDAFVP

    SUM

    VARIANS

    VARIANSP

    ODF standard:

    Section 6.16.60, part 2

    Relaterede (eller lignende) Excel-funktioner:

    SUBTOTAL


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/SUBTOTAL/da&oldid=733329"