Andmebaasifunktsioonid
This section deals with functions used with data organized as one row of data for one record.
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 |
Name |
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 |
Frank |
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 |
Name |
Klass |
Vanus |
Koolitee pikkus |
Kaal |
|
14 |
>600 |
||||
|
15 |
|||||
|
16 |
DCOUNT |
5 |
The formula in cell B16 is =DCOUNT(A1:E10;D1;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.
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 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.
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 .
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".
DAVERAGE(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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 counts the number of rows (records) in a database that match the specified search criteria and contain numerical values in the DatabaseField column.
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 .
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".
DCOUNT(Database; [DatabaseField]; SearchCriteria)
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.
In the example above (scroll up, please), we want to know how many children have to travel more than 600 meters to school. The result is to be stored in cell B16. Set the cursor in cell B16. Enter the formula =DCOUNT(A1:E10;D1;A13:E14) in B16. The Function Wizard helps you to input ranges.
Database is the range of data to be evaluated, including its headers: in this case A1:E10. DatabaseField specifies the column for the search criteria: in this case, the column with the numerical distance values. SearchCriteria is the range where you can enter the search parameters: in this case, 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 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 .
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".
DCOUNTA(Database; [DatabaseField]; SearchCriteria)
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.
In the example above (scroll up, please), you can search for the number of children whose name starts with an E or a subsequent letter. Edit the formula in B16 to read =DCOUNTA(A1:E10;"Name";A13:E14). Delete the old search criteria and enter >=E under Name in field A14. The result is 5. If you now delete all number values for Greta in row 8, the result changes to 4. Row 8 is no longer included in the count because it does not contain any values. The name Greta is text, not a value. Note that the DatabaseField parameter must point to a column that can contain values.
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 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 .
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".
DGET(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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 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 .
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".
DMAX(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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 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 .
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".
DMIN(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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 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 .
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".
DPRODUCT(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
Ă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 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 .
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".
DSTDEV(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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.
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 .
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".
DSTDEVP(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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.
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 .
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".
DSUM(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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 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 .
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".
DVAR(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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 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 .
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".
DVARP(andmebaas; andmebaasi vÀli; otsingukriteeriumid)
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.