Andmebaasifunktsioonid

See alajaotus kĂ€sitleb ridade kaupa kirjeteks ĂŒhendatud andmete puhul kasutatavaid funktsioone.

MĂ€rkuse ikoon

Andmebaasi kategooriat vÔidakse ajada segi selle andmebaasiga, mis on integreeritud LibreOffice'iga. Tegelikult ei ole LibreOffice'i andmebaasi ja LibreOffice Calci andmebaasi kategooria vahel mingit seost.


NĂ€idisandmed:

JÀrgnevaid andmeid kasutatakse osade funktsioonide kirjeldamisel nÀidetena:

Vahemik A1:E10 sisaldab loendit lastest, kes on kutsutud Joe sĂŒnnipĂ€evale. Iga kirje sisaldab jĂ€rgnevat teavet: veerg A nĂ€itab nime, B klassi, siis tulevad vanus aastates, koolitee pikkus meetrites ja lapse kaal kilogrammides.

A

B

C

D

E

1

Nimi

Klass

Vanus

Koolitee pikkus

Kaal

2

Andy

3

9

150

40

3

Betty

4

10

1000

42

4

Charles

3

10

300

51

5

Daniel

5

11

1200

48

6

Eva

2

8

650

33

7

Klass

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

Nimi

Klass

Vanus

Koolitee pikkus

Kaal

14

>600

15

16

DCOUNT

5


Valem lahtris B16 on =DCOUNT(A1:E10;0;A13:E14)

Andmebaasifunktsiooni argumendid:

JÀrgnevas loetelus on kÔikide andmebaasifunktsioonide argumentide definitsioonid:

Andmebaas on lahtrite vahemik, mis mÀÀrab andmebaasi.

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.

Otsingukriteeriumid on lahter, mis sisaldab otsingukriteeriumeid. Kui ĂŒhele reale kirjutada mitu kriteeriumit, ĂŒhendatakse need JA-tehtega. Kui kriteeriumid kirjutada eraldi ridadele, ĂŒhendatakse need VÕI-tehtega. Otsingukriteeriumite vahemikus sisalduvaid tĂŒhje lahtreid eiratakse.

Et mÀÀrata, kuidas LibreOffice Calc peaks identseid kirjeid otsides kÀituma, vali - LibreOffice Calc - Arvutamine.

See also the Wiki page about Conditional Counting and Summation.

DAVERAGE

DAVERAGE tagastab kÔikide ridade (andmebaasi kirjete) hulgast mÀÀratud otsingukriteeriumitele vastavate lahtrite (vÀljade) vÀÀrtuste keskmise.

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.

Syntax

DAVERAGE(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, kui suur on ĂŒlaltoodud nĂ€ite (keri ĂŒlespoole, palun) kĂ”ikide samaealiste laste keskmine kaal, sisestame lahtrisse B16 jĂ€rgmise valemi:

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

Reale 14, Vanuse alla sisestame ĂŒksteise jĂ€rel vÀÀrtused 7, 8, 9 jne. Vastuse vĂ€ljale ilmub sama vanusega laste keskmine kaal.

DCOUNT

DCOUNT loendab andmebaasi read (kirjed), mis vastavad mÀÀratud otsingukriteeriumitele ja sisaldavad arvulisi vÀÀrtusi.

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.

Syntax

DCOUNT(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Ülaltoodud nĂ€ite (keri ĂŒlespoole, palun) pĂ”hjal me tahame teada, kui paljude laste koolitee on pikem kui 600 meetrit. Tulemuse kirjutame lahtrisse B16. Asetame kursori lahtrisse B16. Sisestame lahtrisse B16 valemi =DCOUNT(A1:E10;A1:E10;A13:E14). Dialoog FunktsiooninĂ”ustaja on abiks sisendvahemike mÀÀramisel.

Andmebaas on arvutatavate andmete vahemik koos pÀistega: kÀesoleva nÀite korral A1:E10. Andmebaasi vÀli mÀÀrab otsingukriteeriumite veeru: kÀesoleva nÀite korral terve andmebaasi. Otsingukriteeriumid on vahemik, kuhu saab sisestada otsinguparameetrid: kÀesoleva nÀite korral A13:E14.

Uurime nĂ€iteks, mitu last teises klassis on ĂŒle 7 aasta vanad, selleks kustutame kirje >600 lahtris D14 ja sisestame 2 lahtrisse B14, Klassi alla ning sisestame >7 lahtrisse C14 temast paremal. Vastus on 2. Kaks last on teises klassis ja ĂŒle 7 aasta vanad. Kuna mĂ”lemad tingimused on ĂŒhes reas, on nad ĂŒhendatud loogilise JA-ga.

DCOUNTA

DCOUNTA loendab andmebaasi read (kirjed), mis vastavad mÀÀratud otsingukriteeriumitele ja sisaldavad arvulisi vÔi tÀhti ja numbreid sisaldavaid vÀÀrtusi.

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.

Syntax

DCOUNTA(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Ülaltoodud nĂ€ites saad otsida nende laste arvu, kelle nimi algab E-tĂ€hega vĂ”i mĂ”ne sellele jĂ€rgneva tĂ€hega. Muuda valemit lahtris B16 nii, et see oleks =DCOUNTA(A1:E10;"Nimi";A13:E14). Kustuta eelmised otsingukriteeriumid ja sisesta lahtrisse A14 nime alla >=E. Tulemus on 5. Kui kustutad nĂŒĂŒd reast 8 kĂ”ik Greta kohta kĂ€ivad arvvÀÀrtused, on tulemuseks 4. Rida 8 ei vĂ”eta enam loendamisel arvesse, kuna see ei sisalda ĂŒhtki vÀÀrtust. Nimi "Greta" on tekst, mitte vÀÀrtus. Pane tĂ€hele, et andmebaasi vĂ€lja atribuut peab viitama veerule, mis saab sisaldada vÀÀrtusi.

DGET

DGET tagastab andmebaasi kuuluva viidatud lahtri, mis vastab mÀÀratud otsingukriteeriumitele, sisu. Vea korral tagastab funktsioon kas #VALUE!, kui ĂŒhtegi rida ei leitud, vĂ”i Err502, kui leiti rohkem kui ĂŒks lahter.

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.

Syntax

DGET(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Uurime ĂŒlaltoodud nĂ€ite (keri ĂŒlespoole, palun) pĂ”hjal, mis klassis kĂ€ib laps, kelle nimi on lahtris A14. Valemi sisestame jĂ€llegi lahtrisse B16 ja see erineb veidike varasematest nĂ€idetest, kuna ainult ĂŒks veerg (ĂŒks andmebaasi vĂ€li) tohib olla mÀÀratud andmebaasi vĂ€ljana. Sisestame jĂ€rgneva valemi:

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

NĂŒĂŒd sisestame nime Frank lahtrisse A14 ja nĂ€eme, et tulemus on 2. Frank on teises klassis. Sisestame "Klass" asemele Vanus ja saame vastuseks Franki vanuse.

Sisestame vÀÀrtuse 11 ainult lahtrisse C14 ja kustutame reast kĂ”ik ĂŒlejÀÀnu. Muudame valemit lahtris B16 jĂ€rgnevalt:

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

Klassi asemel kĂŒsime nĂŒĂŒd nime. Vastuseks saamegi Daniel: Daniel on ainus laps vanusega 11 aastat.

DMAX

DMAX tagastab selle andmebaasi (kÔikide kirjete hulka) kuuluva ja mÀÀratud otsingutingimustele vastava lahtri (vÀlja) sisu, mis on teistega vÔrreldes suurim.

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.

Syntax

DMAX(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, kui palju kaalub raskeim laps ĂŒlaltoodud nĂ€ite (keri ĂŒlespoole, palun) igas klassis, sisestame lahtrisse B16 valemi:

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

NĂŒĂŒd sisestame vĂ€ljale 'Klass' ĂŒksteise jĂ€rel vÀÀrtused 1, 2, 3, jne. PĂ€rast iga klassi numbri sisestamist ilmub vastusevĂ€ljale klassi raskeima lapse kaal.

DMIN

DMIN tagastab selle andmebaasi (kÔikide kirjete hulka) kuuluva ja mÀÀratud otsingutingimustele vastava lahtri (vÀlja) sisu, mis on teistega vÔrreldes vÀhim.

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.

Syntax

DMIN(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, kui pikk on igas ĂŒlaltoodud nĂ€ite (keri ĂŒlespoole, palun) klassis selle Ă”pilaste lĂŒhim koolitee, sisestame lahtrisse B16 jĂ€rgmise valemi:

=DMIN(A1:E10;"Koolitee pikkus";A13:E14)

Reale 14, Klassi alla, sisestame jĂ€rgemööda vÀÀrtused 1, 2, 3, jne. Vastuse vĂ€ljale ilmub iga klassi lĂŒhima koolitee pikkus.

DPRODUCT

DPRODUCT korrutab omavahel kÔik need andmevahemiku lahtrid, mille sisu vastab otsingukriteeriumitele.

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.

Syntax

DPRODUCT(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Ülaltoodud sĂŒnnipĂ€evaliste nĂ€ite (keri ĂŒlespoole, palun) jaoks ei ole sellel funktsioonil ĂŒhtegi mĂ”istlikku rakendust.

DSTDEV

DSTDEV arvutab valimil baseeruva populatsiooni, mis koosneb mÀÀratud tingimustele vastavatest andmebaasi veeru arvudest, standardhĂ€lbe. Kirjeid kĂ€sitletakse kui andmete valimit. "Meie" lapsed esindavad justkui lĂ€bilĂ”iget kĂ”ikidest lastest (tĂ”epĂ€rase resultaadi saamiseks peab valimi populatsiooni suurus olema ĂŒle tuhande).

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.

Syntax

DSTDEV(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, kui suur on ĂŒlaltoodud nĂ€ites kĂ”ikide samaealiste laste kaalu standardhĂ€lve, sisestame lahtrisse B16 jĂ€rgmise valemi:

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

Reale 14, Vanuse alla sisestame ĂŒksteise jĂ€rel vÀÀrtused 7, 8, 9 jne. Vastuse vĂ€ljale ilmub sama vanusega laste kaalude standardhĂ€lve.

DSTDEVP

DSTDEVP arvutab populatsiooni, mis koosneb mÀÀratud tingimustele vastavatest andmevahemiku lahtritest, standardhÀlbe. Kirjeid meie nÀitest kÀsitletakse kui terviklikku populatsiooni.

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.

Syntax

DSTDEVP(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, kui suur on kĂ”ikide samaealiste ĂŒlaltoodud nĂ€ites (keri ĂŒlespoole, palun) Joe sĂŒnnipĂ€evale kustutud laste kaalu standardhĂ€lve, sisestame lahtrisse B16 jĂ€rgmise valemi:

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

Reale 14, Vanuse alla, sisestame ĂŒksteise jĂ€rel arvud 7, 8, 9 jne. Iga sisendi kohta nĂ€idatakse kĂ”igi samaealiste laste, kelle kaalu me kontrollisime, kaalude standardhĂ€lvet.

DSUM

DSUM tagastab andmebaasi vÀlja kÔikide ridade (kirjete) kÔikide otsingukriteeriumitele vastavate lahtrite summa.

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.

Syntax

DSUM(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, kui pikk on Joe sĂŒnnipĂ€evale (keri ĂŒlespoole, palun) kutsutud teise klassi laste summaarne koolitee, sisestame lahtrisse B16 jĂ€rgmise valemi:

=DSUM(A1:E10;"Koolitee pikkus";A13:E14)

Reale 14, Klassi alla, sisestame 2. Vastuseks nÀidatakse kÔikide teises klassis kÀivate laste kooliteede pikkuste summat (1950).

DVAR

DVAR tagastab kĂ”ikide andmebaasi vĂ€lja lahtrite dispersiooni kĂ”ikide kirjete hulgas, mis vastavad mÀÀratud otsingukriteeriumitele. Kirjeid meie nĂ€itest kĂ€sitletakse kui andmete valimit. TĂ”epĂ€rase resultaadi saamiseks peab valimi populatsiooni suurus olema ĂŒle tuhande.

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.

Syntax

DVAR(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, milline on ĂŒlaltoodud nĂ€ite (keri ĂŒlespoole, palun) kĂ”igi samaealiste laste kaalude dispersioon, sisestame lahtrisse B16 jĂ€rgmise valemi:

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

Reale 14, Vanuse alla, sisestame ĂŒksteise jĂ€rel vÀÀrtused 7, 8, 9 jne. Vastusena nĂ€idatakse vastava vanusega laste kaalude dispersiooni.

DVARP

DVARP arvutab kÔikide andmebaasi vÀlja lahtrite vÀÀrtuste dispersiooni kÔikide kirjete hulgas, mis vastavad mÀÀratud otsingukriteeriumitele. Kirjeid meie nÀitest kÀsitletakse kui terviklikku populatsiooni.

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.

Syntax

DVARP(andmebaas; andmebaasi vÀli; otsingukriteeriumid)

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

Et leida, milline on kĂ”igi Joe sĂŒnnipĂ€evale (keri ĂŒlespoole, palun) kutsutud samaealiste laste kaalude dispersioon, sisestame lahtrisse B16 jĂ€rgmise valemi:

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

Reale 14, Vanuse alla, sisestame ĂŒksteise jĂ€rel vÀÀrtused 7, 8, 9 jne. Vastusena nĂ€idatakse Joe sĂŒnnipĂ€evale kutsutud vastava vanusega laste kaalude dispersiooni.