Funciones de bases de datos

Esta seición trata de funciones utilizaes con datos entamaos como una filera de datos pa un rexistru.

note

La categoría de la Base de Datos pue confundise cola base de datos integrada en LibreOffice. Sicasí, nun esiste conexón ente la base de datos en LibreOffice y la categoría de Base de Datos en LibreOffice Calc.


Datos d'exemplu:

En dalgunos de los exemplos de descripción de les funciones utilizarán los datos siguientes:

La área A1:Y10 contién los neños convidaos a la fiesta de cumpleaños de Joe. Cada entrada contién la información siguiente: la columna A contién el nome; la columna B, el cursu; de siguío tán la edá n'años, la distancia al colexu en metros y el pesu en kilogramos.

A

B

C

D

E

1

Nome

Grau

Edá

Distancia a la escuela

Pesu

2

Andrés

3

9

150

40

3

Bea

4

10

1000

42

4

Carlos

3

10

300

51

5

Daniel

5

11

1200

48

6

Eva

2

8

650

33

7

Grau

2

7

300

42

8

Greta

1

7

200

36

9

Asur

3

9

1200

44

10

Irene

2

8

1000

42

11

12

13

Nome

Grau

Edá

Distancia a la escuela

Pesu

14

>600

15

16

BDCONTAR

5


La fórmula de la caxella B16 ye =BDCONTAR(A1:Y10;0;A13:Y14)

Parámetros de les funciones de base de datos:

Los siguientes items son les definiciones de parámetros pa toles funciones de bases de datos.

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.

BDCONTAR

BDCONTAR cunta'l númberu de fileres (rexistros) d'una base de datos que coinciden coles condiciones de busca especificaes y contienen valores numbéricos.

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


Sintaxis

BDCONTAR(Base de datos; Riolo Base de Datos; Busca Avanzada)

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

Exemplu

Nel exemplu de riba (muévase escontra riba por favor ), nós queremos saber cuantos neños tienen de viaxar más de 600 metros a la escuela. La resultancia almacenar na caxella B16. Coloque'l cursor na caxella B16. Ingrese la fórmula =BDCONTA(A1:Y10;0;A13:Y14) en B16. El Asistente pa Funciones ayudarálu a ingresar los rangos .

Base de Datos ye'l rangu de datos a ser evaluáu, incluyendo les sos testeres: nesti casu A1:Y10. CampoBasedeDatos especifique la columna pal criteriu de busca. : nésti casu, tola base de datos . CriteriodeBúsqueda ye'l rangu onde pue ingresar los parámetros de busca : nesti casu, A13:Y14.

Pa saber cuántos neños del segundu añu tienen más de 7 años, borre la entrada >600 de la caxella D14 y escriba 2 na caxella B14 debaxo de Nivel; de siguío, escriba 7 na caxella C14 a la drecha. La resultancia ye 2. Dos neños tán en segundu añu y la so edá ye mayor que 7. Como dambos criterios tán na mesma filera, tan coneutaos por un Y.

BDCONTARA

BDCONTARA cunta'l númberu de fileres (rexistros) d'una base de datos que coinciden coles condiciones de busca especificaes y que contienen valores numbéricos o alfanumbéricos.

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


Sintaxis

DCOUNTA(Base de Datos; Riolo Base de Datos; Busca Avanzada)

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

Exemplu

Nel exemplu enriba (Mueva escontra riba, por favor), usté quier guetar el númberu de neños que'l so nome empieza con Y o una lletra subsecuente. Edite formular en B16 pa lleer =DCOUNTA(A1:Y10;"Name";A13:Y14). Desanicie l'antigua criteriu de busca ya introduza >=Y sol Nome nel campu A14. La resultancia ye 5. Agora, si usté desanicia tolos valores numbéricos pa Greta na filera 8, la resultancia camudara a 4. La filera 8 non sera incluyida na cuenta porque nun contién nengún valor numbéricu. El nome Greta ye testu, non un valor. Nótese que'l Campu Base de Datos tien d'apuntar a dalguna columna que contenga valores numbéricos.

BDDESVEST

BDDESVEST calcula la esviación estándar d'una población a partir d'una amuesa, por aciu l'usu de les cifres d'una columna de la base de datos que cumplen les condiciones especificaes. Los rexistros trátense como una amuesa de los datos. Esto ye, los neños del exemplu representen una seición tresversal de tolos neños. Tenga en cuenta que con una amuesa inferior a menos de mil individuos nun ye posible llograr una resultancia representativa.

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


Sintaxis

BDDSTDEV(Base de Datos; Riolo Base de Datos; Busca por Criterios)

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.

Exemplu

Pa pescudar la esviación estándar del pesu de tolos neños de la mesma edá nel exemplu anterior, escriba la siguiente fórmula en B16:

=BDDESVEST(A1:Y10;"Pesu";A13:Y14)

Na filera 14, embaxo del Añu, introduza 7, 8, 9, y asina sucesivamente, unu tres otru. Amosárase como resultáu la esviación estándar del pesu de tolos neños d'esa edá.

BDDESVESTP

BDDESVESTP calcula la esviación estándar d'una población a partir de toles caxelles d'una área de datos que cumplan los criterios de busca. Los rexistros del exemplu trátense como la población total.

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


Sintaxis

BDDESVESTP(Base de Datos; Riolo Base de Datos; Busca por Criterios)

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.

Exemplu

Pa pescudar la esviación estándar del pesu de tolos neños de la mesma edá nel cumpleaños de Luis, escriba la siguiente fórmula en B16:

=BDDESVESTP(A1:Y10;"Pesu";A13:Y14)

Na filera 14, debaxo de la edá, escriba 7, 8, 9, y asina sucesivamente, unu tres otru. La resultancia va ser la esviación estándar de tolos neños de la mesma edá que'l so pesu verificar.

BDEXTRAER

BDEXTRAER devuelve'l conteníu de la caxella a la que se fai referencia nuna base de datos que coincide colos criterios de busca especificaos. Si detéctase un fallu, la función devuelve #VALOR! si nun s'atopa nenguna filera, o Err502 si atópase más d'una caxella.

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


Sintaxis

BDEXTRAER(Base de Datos; Campu de la Base de Datos; Criterios de busca)

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.

Exemplu

Nel exemplu de riba (Mueva escontra riba, por favor), queremos determinar en que grau esta cada neñu, que'l so nome foi introducíu na caxella A14. Formular esta na caxella B14 y difier llixeramente de los exemplos anteriores porque solo utilizárase una columna (un campu de base de datos) pa Riolo Base de Datos. Ingrese la siguiente formula:

=BDEXTRAER(A1:Y10;"Grau";A13:Y14)

Coloque'l nome Frank na caxella A14, y vera que la resultancia ye 2. Frank esta nel segundu grau. Coloque "Edá" en ves de "Grau" y va llograr la edá de Frank.

O coloque'l númberu 11 solo na caxella C14, y desanicie el restu de les entraes nesa filera, Edite formular na caxella B14 como de siguío:

=BDEXTRAER(A1:Y10;"Nome";A13:Y14)

El sistema busca agora por nome y non por clase. La resultancia amuésase darréu: Daniel ye l'únicu neñu de 11 años.

BDMAX

BDMAX devuelve'l conteníu máximo d'una caxella (campu) d'una base de datos (tolos rexistros) que coincida col valor buscáu especificáu.

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


Sintaxis

BDMAX(Base de Datos; Riolo Base de Datos; Criteriu de Busca)

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.

Exemplu

Pa pescudar cuántu pesaba'l neñu más pesáu de cada cursu nel exemplu anterior, escriba la siguiente fórmula en B16:

=BDMAX(A1:Y10;"Pesu";A13:Y14)

Sol Grau, coloque 1, 2, 3, y asina sucesivamente, unu tres otru, Dempués d'introducir un númberu de grau, el pesu del neñu mas pesáu nesi grau va apaecer.

BDMIN

BDMIN devuelve'l conteníu mínimo d'una caxella (campu) d'una base de datos que coincida col valor buscáu especificáu.

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


Sintaxis

BDMIN(Base de Datos; Riolo Base de Datos; Busca por Criterios)

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.

Exemplu

Pa pescudar la distancia más curtia a la escuela pa los neños de cada cursu nel exemplu anterior, escriba la siguiente fórmula en B16:

=BDMIN(A1:Y10;"Distancia a la Escuela";A13:Y14)

Na filera 14, sol Grau, coloque 1, 2, 3, y asina sucesivamente, unu tres otru. La distancia mas corta a la escuela pa cada grau va apaecer.

BDPRODUCTO

BDPRODUCTO multiplica toles caxelles d'una área de datos que'l so conteníu coincida colos criterios de busca.

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


Sintaxis

DPRODUCT(Base de Datos; Riolo Base de Datos; Criterios de Busca)

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.

Exemplu

L'exemplu de la fiesta de cumpleaños nun dexa nenguna aplicación significativa d'esta función.

BDPROMEDIO

BDPROMEDIO devuelve'l promediu de los valores de toles caxelles (campos) en toles fileres (rexistros de bases de datos) que coinciden colos criterios de busca especificaos.

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


Sintaxis

BDPROMEDIO(Base de Datos; Campu de Base de Datos; Busca por Criterios)

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.

Exemplu

Pa pescudar el pesu mediu de tolos neños de la mesma edá nel exemplu anterior, escriba la siguiente fórmula en B16:

=BDPROMEDIO(A1:Y10;"Pesu";A13:Y14)

Na filera 14, de baxu d'Edá, introduza 7, 8, 9, y asina sucesivamente, unu tres otru. Va Apaecer el promediu de pesu de tolos neños de la mesma edá.

BDSUMA

BDSUMA devuelve'l total de toles caxelles nun campu de base de datos en toles fileres (rexistros) que cumplan los criterios de busca especificaos.

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


Sintaxis

BDSUM(Base de Datos; Riolo Base de Datos; Busca por Criterios)

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.

Exemplu

Pa pescudar la distancia a la escuela total combinada pa tolos neños de la fiesta de cumpleaños de Luis que tán en segundu cursu, escriba la siguiente fórmula en B16:

=BDSUMA(A1:Y10;"Distancia a la escuela";A13:Y14)

Ingresa 2 na filera 14 debaxo de Grau. La suma (1950) de la distancia de la escuela de tolos neños que van en segundu grau va esplegase.

BDVAR

BDVAR devuelve la variancia pa toles caxelles d'un campu de base de datos en tolos rexistros que cumplan los criterios de busca especificaos. Los rexistros del exemplu trátense como una amuesa de los datos. Tenga en cuenta que con una amuesa inferior a mil individuos nun ye posible llograr una resultancia representativa.

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


Sintaxis

BDVAR(Base de Datos; Campu de la Base de Datos; Criterios de busca)

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.

Exemplu

Pa pescudar la varianza del pesu de tolos neños de la mesma edá nel exemplu anterior, escriba la siguiente fórmula en B16:

=BDVAR(A1:Y10;"Pesu";A13:Y14)

Na filera 14, debaxo d'Edá, ingresa 7, 8, 9, y asina, una despues de la otra. Lo veres como una resultancia de la varianza de los valores de pesu pa tolos neños de la mesma edá.

BDVARP

BDVARP calcula la variancia pa toles caxelles d'un campu de base de datos en tolos rexistros que cumplen los criterios de busca especificaos. Los rexistros del exemplu trátense como la población total.

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


Sintaxis

BDVARP(Base de Datos; Campu de la Base de Datos; Criterios de busca)

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.

Exemplu

Pa pescudar la varianza del peso promediu de tolos neños de la mesma edá nel cumpleaños de Luis, escriba la siguiente fórmula en B16:

=BDVARP(A1:Y10;"Pesu";A13:Y14)

Na filera 14, so Edá, ingresa 7, 8, 9, y asina, una dempués de la otra. La varianza de los valores de pesu pa tolos neños d'esta edá asistiendo al cumpleaños de Joe van apaecer.

Please support us!