Documentation/Calc Functions/SUM.FLERE.HVIS

From The Document Foundation Wiki
Jump to navigation Jump to search
This page is a translated version of the page Documentation/Calc Functions/SUMIFS and the translation is 2% 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:

SUM.FLERE.HVIS

Kategori:

Matematisk

Resume:

Returnerer summen af cellernes værdi i et område, der opfylder flere kriterier i flere områder.

The criteria passed to SUM.FLERE.HVIS can utilize wildcards or regular expressions.

Syntaks:

SUM.FLERE.HVIS(SumRange; Range 1; Criterion 1[; Range 2; Criterion 2[; … [; Range 127; Criterion 127]]])

Returnerer:

Returnerer et reelt tal, som er summen af de områder, der opfylder deres tilsvarende kriterier.

Argumenter:

SumRange argument

Sum-område (obligatorisk) Det er et celleområde, navnet på en navngivet område eller etiketten på en kolonne eller række, der indeholder værdier til beregning af summen.

  • A reference to a cell range (for example, A1:A25), which may not utilize the reference concatenation operator (~).
  • The name of a named range.
  • The name of a database range.

Range arguments

Range 1 specifies the set of cells to be matched against Criterion 1 and takes one of the forms listed for SumRange. Range 1 should have the same dimensions as SumRange.

Range 2, ..., Range 127 have the same meaning as Range 1.

Criterion arguments

Criterium 1 (obligatorisk) Et strengudtryk, der repræsenterer en logisk betingelse, eller en cellereference til et sådant udtryk. Udtrykket kan indeholde tekst, tal, regulære udtryk eller jokertegn (hvis det er aktiveret under beregningsindstillinger).

  • A number, such as 34.5. Dates and logical values (SAND or FALSK) are treated as numbers.
  • An expression, such as 2/3, KVROD($D$1), or DATO(2021; 11; 1).
  • A text string, such as "golf" or "<>10".

SUM.FLERE.HVIS looks for cells in Range 1 that are equal to Criterion 1, unless Criterion 1 is a text string that starts with a comparator (>, <, >=, <=, =, or <>). In the latter case SUM.FLERE.HVIS compares the cells in Range 1 with the remainder of the text string (interpreted as a number if possible and text otherwise). For example, the condition ">4.5" tests if the content of each cell is greater than the number 4.5, the condition "<dog" tests if the content of each cell comes alphabetically before the text "dog", and the condition "<>2021-11-01" tests if the content of each cell is not equal to the specified date.

Criterion 1 supports the following specific behaviors:

  • The string "=" matches empty cells. For example the formula =SUM.FLERE.HVIS(A1:A10; B1:B10; "=") returns the sum of all values in the range A1:A10 if all cells in the range B1:B10 are empty. Note that "=0" does not match empty cells.
  • The string "<>" matches non-empty cells. For example the formula =SUM.FLERE.HVIS(A1:A10; C1:C10; "<>") returns the sum of all values in the range A1:A10 if there are no empty cells in the range C1:C10.
  • If the value after the <> comparator is not empty, then Criterion 1 matches any cell content except that value, including empty cells.

Criterion 2, ..., Criterion 127 have the same meaning as Criterion 1.

Error conditions

Område 2 (Valgfri) Område 2 og alle de følgende betyder det samme som Område 1.

Kriterium 2 (valgfri) Kriterium 2 og alle følgende betyder det samme som Kriterium 1.

  • Sum-område og område 1, område 2... skal have den samme størrelse, eller returnerer funktionen en værdifejl.
  • Funktionen kan have op til 255 argumenter, hvilket betyder, at du kan specificere 127 kriterieområder og deres kriterier.

Yderligere oplysninger:

Details specific to SUM.FLERE.HVIS function

  • Søgningen understøtter jokertegn eller regulære udtryk. Når regulære udtryk er aktiveret, kan du for eksempel indtaste "alle.*", for at finde den første forekomst af "alle" fulgt af vilkårlige tegn. Hvis du vil søge efter en tekst, der også er et regulært udtryk, skal du enten sætte tegnet "\" foran hvert tegn eller indramme teksten med \Q...\E.

Du kan slå den automatiske evaluering af jokertegn eller regulære udtryk til og fra under Funktioner > Indstillinger > LibreOffice Calc > Beregn.

  • Under brug af funktioner, hvor et eller flere argumenter er søgekriteriestrenge, der repræsenterer et regulært udtryk, er det første forsøg at konvertere kriteriestrengene til tal. ".0" bliver fx konverteret til 0.0 osv. Hvis det lykkes, bliver matchet ikke et match med et regulært udtryk, men et numerisk match. Et skift til en lokalisering, hvor decimalskilletegnet ikke er punktum, får konverteringen til regulært udtryk til at virke. Til at gennemtvinge evalueringen af det regulære udtryk i stedet for et numerisk udtryk bruger du et udtryk, der ikke fejllæses som numerisk som fx ".[0]", ".\0" eller "(?i).0".
  • Den logiske relation mellem kriterierne kan defineres som et logisk OG (konjunktion). Med andre ord: hvis og kun hvis alle givne kriterier opfyldes, tages en værdi fra den tilsvarende celle af den givne Sum-område til beregningen.

Template:Documentation/Calc Functions/regular expressions information/da

Template:Documentation/Calc Functions/wildcards information/da

Eksempler:

Stationery sales examples

Betragt denne tabel

A B C
1 Produktnavn Salg Indtægt
2 pencil 20 65
3 pen 35 85
4 notebook 20 190
5 bog 17 180
6 penalhus nej nej
A B C
1 Product Sales Revenue
2 Pencil 20 $65
3 Pen 35 $85
4 Notebook 20 $190
5 Book 17 $180
6 Pencil case N/A N/A

I alle eksempler herunder indeholder beregningområderne række 6, som ignoreres, fordi den indeholder tekst.

Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUM.FLERE.HVIS(B2:B6; B2:B6; ">=20") Here the function sums the numeric Sales data that are greater than or equal to 20. 75
=SUM.FLERE.HVIS(C2:C6; B2:B6; ">=20"; C2:C6; ">70") Here the function locates entries greater than or equal to 20 in the Sales data that also have a value greater than $70 in the Revenue data, and sums the corresponding numeric entries in the Revenue data. $275
=SUM.FLERE.HVIS(C2:C6; B2:B6; ">"&MIN(B2:B6); B2:B6; "<"&MAKS(B2:B6)) Here the function calculates the sum of the numeric entries in the Revenue data that correspond to all values in the Sales data except the minimum and maximum. $255
=SUM.FLERE.HVIS(C2:C6; A2:A6; "pen.*"; B2:B6; "<"&MAKS(B2:B6)) This example will only work as described here if regular expressions are enabled. Here the function locates entries in the Product data that begin with the characters "pen" and have a value in the Sales data that is not the maximum, and sums the corresponding numeric entries in the Revenue data. $65
=SUM.FLERE.HVIS(C2:C6; A2:A6; E2&".*"; B2:B6; "<"&MAKS(B2:B6)) where cell E2 contains the string "pen" (entered without typing the double quotes). This example will only work as described here if regular expressions are enabled. If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the SUM.FLERE.HVIS function. Here the link to the cell is substituted with its content, giving the same result as the previous example. $65

Sporting equipment sales examples

The examples in this subsection are based on a small database of sales data for sports equipment, with the data organized as in the following table.

A B C D E
1 Date Sales Value Category Region Employee
2 2021-10-02 $1,508 Golf East Hans
3 2021-10-02 $410 Tennis North Kurt
4 2021-10-02 $2,340 Sailing South Ute
5 2021-10-03 $4,872 Tennis East Brigitte
6 2021-10-06 $3,821 Tennis South Fritz
7 2021-10-06 $2,623 Tennis East Fritz
8 2021-10-07 $3,739 Golf South Fritz
9 2021-10-08 $4,195 Golf West Ute
10 2021-10-10 $2,023 Golf East Hans
Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUM.FLERE.HVIS(B2:B10; B2:B10; ">=4000") Here the function sums the numeric entries in the Sales Value data that are greater than or equal to $4,000. This formula is equivalent to both =SUM.HVIS(B2:B10; ">=4000") and =SUM.HVIS(B2:B10; ">=4000"; B2:B10). $9,067
=SUM.FLERE.HVIS(B2:B10; E2:E10; "ute") Here the function locates the entries for Ute in the Employee data and sums the corresponding numeric entries in the Sales Value data. This formula is equivalent to =SUM.HVIS(E2:E10; "ute"; B2:B10) and this re-ordering of arguments highlights a key difference between the two functions - SumRange is the third argument for SUM.HVIS and the first argument for SUMIFS. $6,535
=SUM.FLERE.HVIS(B2:B10; CategoryData; "golf") where the named range CategoryData has been created to cover the cell range C2:C10. Here the function locates the entries for Golf in the Category data and sums the corresponding numeric entries in the Sales Value data. $11,465
=SUM.FLERE.HVIS(B2:B10; D2:D10; F1; E2:E10; F2) where cells F1 and F2 contain the text strings ">=south" and "ute" respectively (both entered without typing the double quotes). Here the function locates the entries for South and West in the Region data that also have Ute in the Employee data, and sums the corresponding numeric entries in the Sales Value data. $6,535
=SUM.FLERE.HVIS(B2:B10; A2:A10; DATO(2021; 10; 2); C2:C10; "tennis") Here the function locates the entries for 2021-10-02 in the Date column that also have Tennis in the Category data, and sums the corresponding numeric entries in the Sales Value data. $410
=SUM.FLERE.HVIS(B2:B10; A2:A10; ">="&DATO(2021; 10; 6); E2:E10; "<>"&E8) Here the function locates the entries dated on or after 2021-10-06 in the Date column that do not have Fritz in the Employee data, and sums the corresponding numeric entries in the Sales Value data. $6,218
=SUM.FLERE.HVIS(B2:B10; C2:C10; "tennis"; D2:D10; "east"; E2:E10; "fritz") Here the function locates the entries that have Tennis in the Category data, East in the Region data and Fritz in the Employee data. It sums the corresponding numeric entries in the Sales Value data. $2,623
=SUM.FLERE.HVIS(B2:B10; D2:D10; "????"; E2:E10; "*e") This example will only work as described here if wildcards are enabled. Here the function locates the four-character entries in the Region data (East and West) that also have an entry in the Employee data that ends with the letter "e" or "E" (Brigitte and Ute). It sums the corresponding numeric entries in the Sales Value data. $9,067
=SUM.FLERE.HVIS(B2:B10; C2:C10; "^t.*"; D2:D10; ".*h") This example will only work as described here if regular expressions are enabled. Here the function locates the entries for Tennis in the Category data that start with the letter "t" or "T" (Tennis) that also have an entry in the Region data that ends with the letter "h" or "H" (North and South). It sums the corresponding numeric entries in the Sales Value data. $4,231
=SUM.FLERE.HVIS(B2:B10; E2:E10; "(?-i)ute") This example will only work as described here if regular expressions are enabled. The "(?-i)" mode modifier within the regular expression changes to a case-sensitive match and so no entries are found in the Employee data. Contrast this with the second example in this table. $0

Flere eksempler

Find flere eksempler ved at downloade og se dette For more examples, download and view this Calc-regneark.

Beslægtede LibreOffice-funktioner:

MIDDEL.FLERE.HVIS

TÆL.FLERE.HVIS

SUM.HVIS

ODF standard:

Section 6.16.63, part 2

Tilsvarende Excel-funktioner:

SUM.FLERE.HVIS