Contents
Index 🔎︎

# Funkcije baze podataka

This section deals with functions used with data organized as one row of data for one record.

The Database category may be confused with a database integrated in LibreOffice. However, there is no connection between a database in LibreOffice and the Database category in LibreOffice Calc.

### Example Data:

The following data will be used in some of the function description examples:

The range A1:E10 lists the children invited to Joe's birthday party. The following information is given for each entry: column A shows the name, B the grade, then age in years, distance to school in meters and weight in kilograms.

| A | B | C | D | E
1 | -3,48 | -10000 | -3,48 | Distance to School | Primljeno
2 | -3,48 | 3 | 9 | 150 | 40
3 | -10000 | 4 | 10 | 1000 | 42
4 | Primljeno | 3 | 10 | 300 | 51
5 | deponirani | 5 | 11 | 1200 | 48
6 | -3,48 | 2 | 8 | 650 | 33
7 | -10000 | 2 | 7 | 300 | 42
8 | -10000 | 1 | 7 | 200 | 36
9 | -10000 | 3 | 9 | 1200 | 44
10 | Primljeno | 2 | 8 | 1000 | 42
11 |  |  |  |  |
12 |  |  |  |  |
13 | -3,48 | -10000 | -3,48 | Distance to School | Primljeno
14 |  |  |  | >600 |
15 |  |  |  |  |
16 | -3,48 | 5 |  |  |

The formula in cell B16 is =DCOUNT(A1:E10;D1;A13:E14)

### Database Function Parameters:

The following items are the parameter definitions for all database functions:

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 - PreferencesTools - Options - LibreOffice Calc - Calculate to define how LibreOffice Calc acts when searching for identical entries.

See also the Wiki page about Conditional Counting and Summation.

## DAVERAGE

DAVERAGE vraca prosjeke vrijednosti svih celija (polja) u svim redovima (slogovi baze podataka) koji odgovaraju specificiranom kriteriju pretrage.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DAVERAGE(Database; Database_field; Search_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.

#### Example

Da bi pronašli prosjecnu težinu sve djece istih godina u primjeru gore (pomjerite gore), unesite slijedecu formulu u B16:

=DAVERAGE(A1:E10; "Weight"; A13:E14)

U red 14, pod Age (Godine), unesite 7, 8, 9, itd, jedno za drugim. Pojavi se prosjecna težina sve djece iste starosti.

## DCOUNT

DCOUNTA racuna broj redova (slogova) u bazi podataka koji se slaže sa specificiranim uslovima pretrage i sadrži numercke ili alfanumericke vrijednosti.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DCOUNT(Database; Database_field; Search_criteria)

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

U primjeru gore (pomjerite gore), Želimo znati koliko djece mora da putuje duže od 600 metara do škole. Rezultat ce se pohraniti u celiju B16. Postavite kursor na celiju B16. Unesite formulu =DCOUNT(A1:E10; 0; A13:E14) u B16. Čarobnjak za funkcije vam pomaže da unesete raspon.

Baza podataka je raspon podataka za procjenu, ukljucujuci zaglavlja: u ovom slucaju A1:E10. Database_field odreduje kolonu za kriterij pretrage: u ovom slucaju, cijela baza podataka. Search_criteria je raspon u kojem možete unijeti parametre za pretragu: u ovom slucaju, A13:E14.

Da bi saznali koliko djece u drugom razredu ima više od 7 godina, izbrišite unos >600 u celiji D14 i unesite 2 u celiju B14 pod Grade i unesite >7 u celiju C14 na desno. Rezultat je 2. Dvoje djece starije od 7 je u drugom razredu. Kako su oba kriterija u istom redu, povezani su sa AND.

## DCOUNTA

DCOUNTA racuna broj redova (slogova) u bazi podataka koji se slaže sa specificiranim uslovima pretrage i sadrži numercke ili alfanumericke vrijednosti.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DCOUNTA(Database; Database_field; Search_criteria)

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

U primjeru gore (pomjerite gore), možete tražiti broj djece cija imena pocinju sa E ili narednim slovom. Uredite formulu u B16 da bi procitali =DCOUNTA(A1:E10; "Age"; A13:E14). Obrišite stari kriterij pretrage i unesite >=E pod Name (Ime) u polje A14. Rezultat je 5. Ako sada izbrišete sve numericke vrijednosti za Greta u redu 8, rezultat se mijenja na 4. Row 8 is no longer includRed 8 više nije ukljucen u racunanje jer ne sadrži nikakve vrijednosti. Ime Greta je tekst a ne vrijednost. Primjetite da Database_field parametar mora ukazati na kolonu koja sadrži vrijednosti.

## DGET

DGET vraca sadržaje referirane celije u bazi podataka koji odgovara specificiranom kriteriju pretrage. U slucaju greške, funkcija vraca ili #VALUE! za novu nadenu vrijednost ili Err502 za nadenih vide celija.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DGET(Database; Database_field; Search_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.

#### Example

U primjeru gore (pomjerite gore), Želimo odrediti koji je razred dijete cije je ima unešeno u celiju A14. Formula je unešena u celiju B16 i malo se razlikuje od ranijih primjera jer samo jedna kolona (jedno polje baze podataka) može unijeti za Database_field. Unesite slijedecu formulu:

=DGET(A1:E10; "Grade"; A13:E14)

Unesite ime Frank u A14 i vidjecete rezultat 2. Frank je u drugom razredu. Unesite "Age" umjesto "Grade" i dobicete Frankove godine.

Ili unesite vrijednost 11 samo u celiju C14 i izbrišite ostale unose u ovom redu. Uredite formulu u B16 kako slijedi:

=DGET(A1:E10; "Name"; A13:E14)

Umjesto razreda, traženo je ime. Odgovor se odmah pojavi: Daniel je jedino dijete koje ima 11 godina.

## DMAX

DMAX vraca maksimalni sadržaj celije (polja) u bazi podataka (sve slogove) koji se slažu sa specificiranim uslovima pretrage

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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(Database; Database_field; Search_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.

#### Example

Da bi saznali koliko najteže dijete u svakom razredu teži u prethodnom primjeru (pomjerite gore), unesite slijedecu formulu u B16:

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

Pod Grade (Razred) unesite 1, 2, 3, itd, jedno za drugim. Nakon unosa broja razreda, pojavi se težina najtežeg djeteta u tom razredu.

## DMIN

DMIN vraca sadržaj minimalne vrijednosti celije (polja) u bazi podataka koji odgovara specificiranom kriteriju pretrage.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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(Database; Database_field; Search_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.

#### Example

Da bi našli najkracu distancu do škole za djecu u svakom razredu u primjeru gore (pomjerite gore), unesite slijedecu formulu u B16:

=DMIN(A1:E10; "Distance to School"; A13:E14)

U redu 14, pod Grade (Razred) unesite 1, 2, 3, itd, jedno za drugim. Pojavi se najkraca distanca do škole za svaki razred.

## DPRODUCT

DPRODUCT množi sve celije domene podataka gdje sadržaj celije odgovara kriteriju pretrage.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DPRODUCT(Database; Database_field; Search_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.

#### Example

U primjeru sa rodendanskom zabavom gore (pomjerite gore), primjena ove funkcije nema smisla.

## DSTDEV

DSTDEV racuna standardnu devijaciju populacije baziranu na uzorku, koristeci brojeve u koloni baze podataka koji odgovaraju datim uslovima. Slogovi se tretiraju kao uzorak. To znaci da djeca u ovom primjeru predstavljaju presjek sve djece. Zapamtite da se reprezentativan rezultat ne može dobiti od uzorka manjeg o jedne hiljade.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DSTDEV(Database; Database_field; Search_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.

#### Example

Da bi našli standardnu devijaciju težine za svu djecu istih godina u primjeru gore (pomjerite gore), unesite slijedecu formulu u B16:

=DSTDEV(A1:E10; "Weight"; A13:E14)

U redu 14, pod Age (Godine), unesite 7, 8, 9, itd, jedno za drugim. Prikazani rezultat je standardna devijacija za težinu sve djece istih godina.

## DSTDEVP

DSTDEVP racuna standardnu devijaciju populacije baziranu na svim celijama domene podataka koji odgovaraju kriteriju pretraživanja. Slogovi iz primjera se tretiraju kao cijela populacija.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DSTDEVP(Database; Database_field; Search_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.

#### Example

Da bi našli standardnu devijaciju težine za svu djecu istih godina na rodendanskoj zabavi kod Joe-a (pomjerite gore), unesite slijedecu formulu u B16:

=DSTDEVP(A1:E10; "Weight"; A13:E14)

U redu 14, pod Age, unesite 7, 8, 9, itd, jedno za drugim. Rezultat je standardna devijacija težine za svu djecu istih godina cija je težina provjerena.

## DSUM

DSUM vraca total svih celija u polju baze podataka u svim redovima (slogovima) koji odgovaraju specificiranom kriteriju pretrage.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DSUM(Database; Database_field; Search_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.

#### Example

Da bi našli dužinu kombinirane distance do škole za svu djecu na rodendanskoj zabavi kod Joe-a (pomjerite gore) koja su u drugom razredu, unesite slijedecu formulu u B16:

=DSUM(A1:E10; "Distance to School"; A13:E14)

Unesite 2 u red 14 pod Grade (Razred). Zbir (1950) je zbir svih distanci do škole za svu djecu koja su u drugom razredu.

## DVAR

DVAR vraca varijancu svih celija u polju baze podataka u svim slogovima koji odgovaraju kriteriju pretrage. Slogovi iz primjera se tretiraju kao uzorci. Reprezentativan rezultat se ne može dobiti iz uzorka populacije manjeg od jedne hiljade.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DVAR(Database; Database_field; Search_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.

#### Example

Da bi našli varijancu težine težine za svu djecu istih godina u primjeru gore (pomjerite gore), unesite slijedecu formulu u B16:

=DVAR(A1:E10; "Weight"; A13:E14)

U red 14, pod Age, unesite 7, 8, 9, itd, jedno za drugim. Kao rezultat cete dobiti varijancu vrijednosti težine za svu djecu ovih godina.

## DVARP

DVARP racuna varijancu svih vrijednosti celija u polju baze podataka u svim slogovima koji odgovaraju specificiranom kriteriju pretrage. Slogovi u primjeru se tretiraju kao cijela populacija.

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 - PreferencesTools - Options - LibreOffice Calc - Calculate.

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

DVARP(Database; Database_field; Search_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.

#### Example

Da bi našli varijancu težine za svu djecu istih godina koja su na rodendanskoj zabavi kod Joe-a (pomjerite gore), unesite slijedecu formulu u B16:

=DVARP(A1:E10; "Weight"; A13:E14)

U redu 14, pod Age (Godine), unesite 7, 8, 9, itd, jedno za drugim. Pojavi se varijanca težine za svu djecu istih godina na rodendanskoj zabavi kod Joe-a.

Related Topics

Functions by Category