Andmebaasifunktsioonid

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

note

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:

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.

DAVERAGE

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

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (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).

NĂ€ide

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

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (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).

NĂ€ide

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

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

Ü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).

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

Et leida, kui suur on kĂ”ikide samaealiste ĂŒlaltoodud nĂ€ite (keri ĂŒlespoole, palun) 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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

Et leida, kui suur on kĂ”ikide samaealiste ĂŒlaltoodud nĂ€ites (keri ĂŒlespoole, palun) Joe sĂŒnnipĂ€evale kustutud laste kaalu standardhĂ€lve, sSisestame 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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Otsing toetab metamĂ€rke vĂ”i regulaaravaldisi. Kui regulaaravaldised on sisse lĂŒlitatud, vĂ”ib nĂ€iteks sisestada "all.*", et leida esimese koha, kus esineb tĂ€heĂŒhend "all", millele jĂ€rgnevad suvalised mĂ€rgid. Kui on vaja otsida teksti, mis on ise regulaaravaldis, siis tuleb kas iga sĂŒmboli ette lisada lĂ€ngkriips (\) vĂ”i lisada otsitava regulaaravaldise ette \Q ja jĂ€rele \E. MetamĂ€rkide ja regulaaravaldiste automaatset töötlemist saab lĂŒlitada sisse ja vĂ€lja, valides - LibreOffice Calc - Arvutamine.

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


SĂŒntaks

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 (from the first row of Database range), place quotation marks around the header name.

NĂ€ide

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.

Palun toeta meid!