Databázové funkcie

Táto sekcia sa zaoberá funkciami pre prácu s dátami usporiadanými tak, že každý riadok má význam jedného záznamu.

note

Kategória Databáza môže byť zamieňaná s databázou integrovanou v LibreOffice. Medzi databázou v LibreOffice a kategóriou Databáza v LibreOffice Calc však nie je žiadna spojitosť.


Example Data:

Nasledujúce údaje budú použité v niektorých príkladoch a popisoch funkcií.

Oblasť A1:E10 obsahuje deti pozvané na Jankov narodeninový večierok. V každom zázname sú uvedené tieto informácie: meno (stĺpec A), trieda (stĺpec B), vek (počet rokov), vzdialenosť od školy v metroch a váha v kilogramoch.

A

B

C

D

E

1

Meno

Trieda

Vek

Vzdialenosť do školy

Váha

2

y

3

9

150

40

3

Alžbeta

4

10

1000

42

4

Karol

3

10

300

51

5

Daniel

5

11

1200

48

6

Eva

2

8

650

33

7

Trieda

2

7

300

42

8

Margaréta

1

7

200

36

9

Juraj

3

9

1200

44

10

Irena

2

8

1000

42

11

12

13

Meno

Trieda

Vek

Vzdialenosť do školy

Váha

14

>600

15

16

DCOUNT

5


Vzorec v bunke B16 je =DCOUNT(A1:E10;0;A13:E14)

Parametre databázových funkcií:

Nasledujú definície parametrov pre všetky databázové funkcie:

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 vracia priemer hodnôt všetkých buniek (polí) v každom riadku (databázových záznamoch), ktoré odpovedajú zadaným kritériám.

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(Databáza; Pole databázy; Kritériá databázy)

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.

Example

Pre nájdenie priemernej váhy detí rovnakého veku vo vyššie uvedenom príklade, vložte nasledujúci vzorec do B16:

=DAVERAGE(A1:E10;"Váha";A13:E14)

V riadku 14, pod 'Vekom', vložte 7, 8, 9, a tak ďalej, jeden za druhý. Objaví sa priemerná váha detí daného veku.

DCOUNT

DCOUNT počíta v databáze počet riadkov (záznamov), ktoré odpovedajú kritériám vyhľadávania a obsahujú číselné hodnoty.

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(Databáza; [Pole databázy]; Kritériá vyhľadávania)

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

Example

V príklade vyššie chceme vedieť, koľko detí musí chodiť do školy ďalej ako 600 metrov. Výsledok by mal byť uložený v bunke B16. Nastavte kurzor na bunku B16. Vložte do nej vzorec =DCOUNT(A1:E10;0;A13:E14). Sprievodca funkciou vám pomôže vložiť rozsahy.

Databáza je oblasť dát, ktoré sa majú vyhodnotiť, vrátane ich hlavičiek. Pole databázy určuje stĺpec pre kritériá vyhľadávania: v tomto prípade celá databáza. Kritériá vyhľadávania je oblasť, kde je možné vložiť parametre vyhľadávania: v tomto prípade A13:E14.

Aby ste sa dozvedeli, koľko detí v druhej triede je starších ako 7 rokov, zmažte údaj >600 v bunke D14 a vložte 2 do bunky B14 pod Trieda, a vložte >7 do bunky C14. Výsledok je 2. Dvom deťom z druhej triedy je viac ako 7 rokov. Pretože sú obidve kritériá v rovnakom riadku, sú spojené pomocou A.

DPRODUCT

DPRODUCT násobí všetky bunky oblasti, kde obsah buniek odpovedá kritériám vyhľadávania.

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(Databáza; Pole databázy; Kritériá vyhľadávania)

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.

Example

V príklade narodeninového večierka (posuňte sa hore prosím) sa táto funkcia nedá zmysluplne aplikovať.

DSTDEV

DSTDEV vypočíta smerodajnú odchýlku v štatistickom súbore založenom na vzorke, s použitím čísel v záznamoch databázy, ktoré zodpovedajú daným podmienkam. Záznamy sú chápané ako vzorka dát. To znamená, že deti v príklade reprezentujú výber všetkých detí. Reprezentatívny výsledok nie je možné získať zo vzorky menšej ako jeden tisíc.

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(Databáza; Pole databázy; Kritériá vyhľadávania)

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.

Example

Pre nájdenie smerodajnej odchýlky váhy všetkých detí rovnakého veku v príklade vyššie, vložte nasledujúci vzorec do B16:

=DSTDEV(A1:E10;"Váha";A13:E14)

V riadku 14 pod 'Vekom' vložte 7, 8, 9, a tak ďalej jeden za druhým. Výsledkom bude štandardná odchýlka váhy všetkých detí v danom veku.

DSTDEVP

DSTDEVP vypočíta štandardnú odchýlku v štatistickom súbore založenom na všetkých bunkách dátovej oblasti, ktorá zodpovedá kritériám vyhľadávania. Záznamy v príklade sú chápané ako kompletný štatistický súbor.

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(Databáza

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.

Example

Pre nájdenie smerodajnej odchýlky váhy všetkých detí rovnakého veku v príklade vyššie, vložte nasledujúci vzorec do B16:

=DSTDEVP(A1:E10"Váha";A13:E14)

Na riadok 14 pod 'Vekom' vložte 7, 8, 9, a tak ďalej jeden za druhým. Výsledkom bude smerodajná odchýlka váhy všetkých detí v danom veku, ktorých váha bola kontrolovaná.

DSUM

DSUM vráti súčet všetkých buniek v databázovom poli vo všetkých riadkoch (záznamoch), ktoré zodpovedajú kritériám vyhľadávania.

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(Databáza

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.

Example

Pre nájdenie celkovej vzdialenosti od školy pre všetky deti z druhej triedy na narodeninovej oslave v príklade vyššie, vložte nasledujúci vzorec do B16:

=DSUM(A1:E10;"Vzdialenosť do školy";A13:E14)

Vložte 2 v riadku 14 pod "Triedou". Zobrazí sa súčet (1950) vzdialeností do školy pre všetky deti z druhej triedy.

DVAR

DVAR vráti rozptyl všetkých buniek v poli databázy vo všetkých záznamoch, ktoré zodpovedajú kritériám vyhľadávania. Záznamy v príklade sú chápané ako vzorka dát. Reprezentatívny výsledok nie je možné získať zo vzorky menšej ako jeden tisíc.

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(Databáza

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.

Example

Pre nájdenie rozptylu váhy všetkých detí rovnakého veku v príklade vyššie, vložte nasledujúci vzorec doB16:

=DVAR(A1:E10"Váha";A13:E14)

Na riadok 14 pod 'Vekom' vložte 7, 8, 9, a tak ďalej, jeden za druhým. Výsledkom bude rozptyl váhy všetkých detí v danom veku.

DVARP

DVARP vypočíta rozptyl všetkých buniek v poli databázy vo všetkých záznamoch, ktoré zodpovedajú kritériám vyhľadávania. Záznamy v príklade sú chápané ako kompletný štatistický súbor.

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(Databáza

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.

Example

Pre nájdenie rozptylu váhy všetkých detí rovnakého veku na narodeninovej oslave v príklade vyššie, vložte nasledujúci vzorec do B16:

=DVARP(A1:E10;"Váha";A13:E14)

Na riadok 14 pod 'Vekom' vložte 7, 8, 9, a tak ďalej, jeden za druhým. Zobrazí sa výsledný rozptyl váhy všetkých detí v danom veku, ktoré prišli na narodeninovú oslavu.

DCOUNTA

Funkcia DCOUNTA vracia počet riadkov (záznamov) v databáze, ktorá vyhovuje kritériám vyhľadávania a obsahuje číselné či alfanumerické hodnoty.

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(Databáza; [Pole databázy]; Kritériá vyhľadávania)

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

Example

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

Funkcia DGET vracia obsah odkazovaných buniek v databáze, ktoré vyhovujú zadaným kritériám. V prípade chyby funkcia vracia buď #VALUE! pokiaľ nie je riadok nájdený, alebo Err502, ak je nájdená viac ako jedna bunka.

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(Databáza; Pole databázy; Kritériá vyhľadávania)

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.

Example

V predchádzajúcom príklade (posuňte sa hore prosím) chceme určiť, akú triedu navštevuje dieťa, ktorého meno bolo zadané do bunky A14. Vzorec je zadaný do bunky B16 a od predchádzajúcich sa líši iba nepatrne, pretože do Pola databázy môže byť zadaný iba jeden stĺpec (jedna položka databázy). Zadajte nasledujúci vzorec:

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

Do bunky A14 zadajte meno František a dostanete výsledok 2. František chodí do druhej triedy. Namiesto "Trieda" zadajte "Vek" a dostanete Františkov vek.

Ďalej je možné zadať hodnotu 11 do bunky C14 a odstrániť ostatné hodnoty na tomto riadku. Upravte vzorec v bunke B16 nasledujúcim spôsobom:

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

Namiesto triedy sa vyhľadá meno. Výsledok sa zobrazí okamžite: jediným dieťaťom vo veku 11 rokov je Daniel.

DMAX

Funkcia DMAX vracia najvyššiu hodnotu bunky (položky) v databázach (všetky záznamy), ktorá vyhovuje zadaným kritériám.

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(Databáza; Pole databázy; Kritériá vyhľadávania)

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.

Example

Pre zistenie, koľko váži najťažšie dieťa v každej triede v príklade vyššie, vložte nasledujúci vzorec do B16:

=DMAX(A1:E10;"Váha";A13:E14)

Pod 'Triedu' vložte 1, 2, 3, a tak ďalej jednu za druhú. Po vložení čísla triedy sa objaví váha najťažšieho dieťaťa v triede.

DMIN

Funkcia DMIN vracia minimálny obsah bunky (pola) v záznamoch databázy, ktoré odpovedajú kritériám vyhľadávania.

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(Databáza; Pole databázy; Kritériá vyhľadávania)

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.

Example

Na nájdenie najkratšej vzdialenosti do školy pre deti v každej triede z vyššie uvedeného príkladu, vložte nasledujúci vzorec do B16:

=DMIN(A1:E10;"Vzdialenosť do školy";A13:E14)

Do riadku 14, pod 'Triedu', vložte 1, 2, 3, a tak ďalej, jednu za druhú. Objaví sa najkratšia vzdialenosť od školy pre každú triedu.

Please support us!