Databasfunktioner

I det hÀr avsnittet beskrivs funktioner som anvÀnds med data som organiserats i en rad för en post.

note

Databaskategorin kan förvÀxlas med en databas som integrerats i LibreOffice. Det finns emellertid ingen koppling mellan en databas i LibreOffice och databaskategorin i LibreOffice Calc.


Exempeldata:

Följande data kommer att anvÀndas i nÄgra av de exempel som beskriver funktionerna:

OmrÄdet A1:E10 innehÄller en lista över barn som Àr bjudna till Johans födelsedagskalas. Följande information Àr angiven för varje post: kolumn A visar namn, B visar Ärskurs, sedan visas Älder i Är, skolvÀg i meter och kroppsvikt i kilo.

A

B

C

D

E

1

Namn

Grad

Ålder

AvstÄnd till skolan

Vikt

2

Anders

3

9

150

40

3

Elisabeth

4

10

1000

42

4

Karl

3

10

300

51

5

Daniel

5

11

1200

48

6

Eva

2

8

650

33

7

Grad

2

7

300

42

8

Greta

1

7

200

36

9

Harry

3

9

1200

44

10

Irene

2

8

1000

42

11

12

13

Namn

Grad

Ålder

AvstÄnd till skolan

Vikt

14

>600

15

16

DANTAL

5


Formeln i cell B16 Àr =DANTAL (A1:E10;0;A13:E14)

Parametrar för databasfunktioner:

Följande objekt Àr parameterdefinitioner för alla databasfunktioner:

Database is the cell range defining the database. The first row of the range contains the field names, and following rows are records with corresponding field values.

DatabaseField specifies the column where the function operates on after the search criteria of the first parameter is applied and the data rows are selected. It is not related to the search criteria itself. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

SearchCriteria is the cell range containing search criteria. Like Database, its first row is also field names, and following rows are conditions for related fields. If you write several criteria in one row they are connected by AND. If you write the criteria in different rows they are connected by OR. Empty cells in the search criteria range will be ignored.

Choose - LibreOffice Calc - Calculate to define how LibreOffice Calc acts when searching for identical entries.

See also the Wiki page about Conditional Counting and Summation.

DANTAL

DANTAL rÀknar antalet rader (poster) i en databas som matchar angivna sökvillkor, och innehÄller numeriska vÀrden.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DANTAL(Databas; [DatabasfÀlt]; Sökkriterier)

If the DatabaseField argument is omitted, DCOUNT returns the count of all records that satisfy Criteria. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

If you choose to omit the DatabaseField argument, your formula should be of the form =DCOUNT(Database; ; SearchCriteria).

Example

I exemplet ovan (blÀddra uppÄt) har vi tagit reda pÄ hur mÄnga av barnen som bor mer Àn 600 meter frÄn skolan. Resultatet sparas i cell B16. Placera markören i cell B16. Ange formeln =DANTAL(A1:E10;0;A13:E14) i B16. Med hjÀlp av Funktionsguiden matar du in omrÄden.

Databas Àr det dataomrÄde som ska berÀknas, inklusive rubrikerna, i det hÀr fallet A1:E10. DatabasfÀlt anger kolumnen för sökriterierna, i det hÀr fallet hela databasen. Sökkriterier Àr det omrÄde dÀr du kan ange sökparametrarna, i det hÀr fallet A13:E14.

Om du vill veta hur mÄnga barn som Àr över sju Är i andra Ärskursen, raderar du posten >600 i cell D14 och anger 2 i cell B14 under Grad. Ange sedan >7 i cell C14 till höger. Resultatet blir 2. TvÄ barn gÄr i andra Ärskurs och Àr över sju Är. Eftersom bÄda kriterierna Àr i samma rad sammankopplas de av OCH.

DANTALV

DANTALV rÀknar antalet rader (poster) i en databas som matchar angivna sökvillkor, och innehÄller numeriska eller alfanumeriska vÀrden.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DANTALV(Databas; [DatabasfÀlt]; Sökkriterier)

If the DatabaseField argument is omitted, DCOUNTA returns the count of all records that satisfy Criteria. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

If you choose to omit the DatabaseField argument, your formula should be of the form =DCOUNTA(Database; ; SearchCriteria).

Example

I exemplet ovan (blÀddra uppÄt) kan du ta reda pÄ hur mÄnga barn som har ett namn som börjar pÄ bokstaven E eller nÄgon efterföljande bokstav. Ange följande formel i B16: =DANTALV(A1:E10;"Namn";A13:E14). Ta bort gamla sökkriterier och ange >=E under Namn i fÀltet A14. Resultatet blir 5. Om du tar bort alla nummervÀrden för Greta pÄ rad 8 Àndras resultatet till 4. Rad 8 berÀknas inte lÀngre eftersom den inte innehÄller nÄgra vÀrden. Namnet Greta Àr text, inte ett vÀrde. Observera att parametern DatabasfÀlt mÄste peka pÄ en kolumn som kan innehÄlla vÀrden.

DHÄMTA

DHÄMTA returnerar innehĂ„llet i den refererade cellen i databasen som matchar angivet sökkriterium. Om ett fel intrĂ€ffar returneras antingen #VÄRDEFEL! om ingen rad hittas, eller Err502 om fler Ă€n en cell hittas.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DHÄMTA (Databas; DatabasfĂ€lt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

I exemplet ovan (blÀddra uppÄt) tar vi reda pÄ vilken Ärskull ett barn, vars namn angavs i cell C14, tillhör. Formeln anges i cell B16 och skiljer sig nÄgot frÄn tidigare exempel eftersom endast en kolumn (ett databasfÀlt) kan anges som DatabasfÀlt. Ange följande formel:

=DHÄMTA(A1:E10;"Grad";A13:E14)

Om du anger namnet Frank i A14 blir resultatet 2. Frank tillhör den andra graden. Om du anger "Ålder" i stĂ€llet för "Grad" fĂ„r du reda pĂ„ Franks Ă„lder.

Du kan Àven ange vÀrdet 11 endast i cell C14 och ta bort andra poster pÄ den hÀr raden. SÄ hÀr redigerar du formeln i B16:

=DHÄMTA(A1:E10;"Namn";A13:E14)

Nu frÄgar du alltsÄ efter namnet, och inte klassen. Svaret fÄr du direkt: Daniel Àr det enda barnet som Àr 11 Är gammalt.

DMAX

DMAX returnerar maxinnehÄllet i en cell (fÀlt) i en databas (alla poster) som matchar angivna sökkriterier.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DMAX (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill veta hur mycket det tyngsta barnet i varje Ärskurs vÀger i exemplet ovan (blÀddra uppÄt) skriver du följande formel i B16:

=DMAX(A1:E10;"Vikt";A13:E14)

Ange 1, 2, 3, osv. efter varandra under Grad. Om du anger gradnummer visas barnet som vÀger mest inom den graden.

DMEDEL

DMEDEL returnerar medelvÀrdet av vÀrdena i alla celler (fÀlt) i alla rader (databasposter) som matchar angivet sökkriterium.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DMEDEL (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill visa medelvikten för alla barn i samma Älder i exemplet ovan (blÀddra uppÄt) skriver du följande formel i B16:

=DMEDEL(A1:E10;"Vikt";A13:E14)

Ange 7, 8, 9, osv. efter varandra pĂ„ rad 14 under Ålder. Medelvikten för alla barnen i samma Ă„rskull visas.

DMIN

DMIN returnerar minimiinnehÄllet i en cell (fÀlt) i en databas som matchar angivna sökkriterier.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DMIN (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill ta reda pÄ den kortaste skolvÀgen för barnen i varje Ärskurs i exemplet ovan (blÀddra uppÄt) skriver du följande formel i B16:

=DMIN(A1:E10;"AvstÄnd till skolan";A13:E14)

Ange 1, 2, 3, osv. efter varandra pÄ rad 14 under Grad. Den kortaste skolvÀgen för barnen i varje Ärskurs visas.

DPRODUKT

DPRODUKT multiplicerar alla celler i ett dataomrÄde dÀr cellinnehÄllet matchar sökkriteriet.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DPRODUKT (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Funktionen gÄr inte att tillÀmpa meningsfullt pÄ födelsedagskalaset i exemplet ovan (blÀddra uppÄt).

DSTDAV

DSTDAV berÀknar standardavvikelsen för en population baserad pÄ ett stickprov, och anvÀnder de siffror i en databaskolumn som matchar angivna villkor. Posterna behandlas som stickprov. Det innebÀr att barnen i exemplet representerar ett tvÀrsnitt av alla barn. Observera dock att representativa resultat inte kan fÄs frÄn ett urval pÄ mindre Àn tusen personer.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DSTDAV (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill visa standardavvikelsen för vikten hos alla barn i samma Älder i exemplet (blÀddra uppÄt) skriver du följande formel i B16:

=DSTDAV(A1:E10;"Vikt";A13:E14)

Ange 7, 8, 9, osv. efter varandra pĂ„ rad 14 under Ålder. Resultatet som visas Ă€r standardavvikelsen för barns vikt i samma Ă„lder.

DSTDAVP

DSTDAVP berÀknar standardavvikelsen för en population baserad pÄ alla celler i ett dataomrÄde som matchar sökkriterierna. Dataposterna i exemplet behandlas som hela populationen.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DSTDAVP (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill visa standardavvikelsen för vikten hos alla barn i samma Älder pÄ Johans födelsedagskalas (blÀddra uppÄt) skriver du följande formel i B16:

=DSTDAVP(A1:E10;"Vikt";A13:E14)

Ange 7, 8, 9, osv. efter varandra pĂ„ rad 14 under Ålder. Resultatet Ă€r standardavvikelsen för vikten hos alla jĂ€mnĂ„riga barn som undersökts.

DSUMMA

DSUMMA returnerar summan för alla celler i ett databasfÀlt i alla rader (poster) som matchar angivna sökkriterier.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DSUMMA (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill ta reda pÄ den sammanlagda skolvÀgen för alla barn pÄ Johans födelsedagskalas som gÄr i andra klass (blÀddra uppÄt), anger du följande formel i B16:

=DSUMMA(A1:E10;"AvstÄnd till skolan";A13:E14)

Ange 2 pÄ rad 14 under Grad. Summan (1950) för skolavstÄndet för alla barn som gÄr i Ärskurs 2 visas.

DVARIANS

DVARIANS returnerar variansen för alla celler i ett databasfÀlt, i alla poster som matchar angivna sökkriterier. Posterna i exemplet behandlas som ett stickprov. Det gÄr inte att fÄ representativa resultat frÄn ett urval pÄ mindre Àn tusen personer.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DVARIANS (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill visa variansen för alla barn i samma Älder i exemplet ovan (blÀddra uppÄt) skriver du följande formel i B16:

=DVARIANS(A1:E10;"Vikt";A13:E14)

Ange 7, 8, 9, osv. efter varandra pĂ„ rad 14 under Ålder. DĂ„ visas variansresultatet för barns vikt i samma Ă„lder.

DVARIANSP

DVARIANSP berÀknar variansen för alla celler i ett databasfÀlt, i alla poster som matchar angivna sökkriterier. Dataposterna i exemplet behandlas som en hel population.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

DVARIANSP (Databas; DatabasfÀlt; Sökkriterier)

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

Om du vill visa variansen för vikten hos alla barn i samma Älder pÄ Johans födelsedagskalas (blÀddra uppÄt) skriver du följande formel i B16:

=DVARIANSP(A1:E10;"Vikt";A13:E14)

Ange 7, 8, 9, osv. efter varandra pĂ„ rad 14 under Ålder. Variansen för vikten visas för alla barn i samma Ă„lder som var pĂ„ Johans födelsedagskalas.

Please support us!