Funkcije baze podataka

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

note

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.

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, place quotation marks around the header name.

SearchCriteria is the cell range containing search criteria. 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.

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

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, 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 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

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, place quotation marks around the header name.

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

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, place quotation marks around the header name.

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

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, 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 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(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, 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 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(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, 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 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

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, 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 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

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, 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 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

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, 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 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

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, 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 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

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, 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 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

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

Please support us!