Documentation/Calc Functions/SUBTOTAL

From The Document Foundation Wiki
Jump to navigation Jump to search
This page is a translated version of the page Documentation/Calc Functions/SUBTOTAL and the translation is 3% complete.
Outdated translations are marked like this.
Other languages:
English • ‎Nederlands • ‎dansk • ‎español • ‎čeština

Template:Documentation/Calc Functions/Func Page Links/da

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

Note:
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.

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

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.

Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUBTOTAL(2; B2:B13) Here the function uses Calc's TÆL function to count the number of scores (TÆL ignores strings and 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) Here the function uses Calc's TÆLV function to count the number of scores (TÆLV includes strings and 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) Here the function uses Calc's 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) Here the function uses Calc's 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).

Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUBTOTAL(2; B2:B13) Here the function uses Calc's TÆL function to count the number of scores (TÆL ignores strings and so the asterisk in cell B5 is ignored). In this case, SUBTOTAL includes values in hidden rows. 11
=SUBTOTAL(102; B2:B13) Here the function uses Calc's TÆL function to count the number of scores (TÆL ignores strings and so the asterisk in cell B5 is ignored). In this case, SUBTOTAL ignores values in hidden rows. 10
=SUBTOTAL(5; B2:B13) Here the function uses Calc's MIN function to determine the minimum score value. In this case, SUBTOTAL includes values in hidden rows. 2
=SUBTOTAL(105; B2:B13) Here the function uses Calc's 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.

Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUBTOTAL(2; B2:B13) Here the function uses Calc's TÆL function to count the number of scores (TÆL ignores strings and 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) Here the function uses Calc's TÆLV function to count the number of scores (TÆLV includes strings and 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) Here the function uses Calc's 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

Tilsvarende Excel-funktioner:

SUBTOTAL