Funkcije baze podataka

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

Note Icon 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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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 regular expressions. 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 precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in - LibreOffice Calc - Calculate.

Sintaksa

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.

Primjer

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.