Funcións de base de datos

Esta sección trata de funcións usadas cos datos organizados como unha liña de datos para un rexistro.

note

A categoría Banco de Datos pode ser confundido con unha base de datos integrada en $ [officename]. Con todo, non hai ningunha conexión entre unha base de datos en $ [officename] e na categoría de base de datos en $ [officename] Calc.


Datos de exemplo:

Os seguintes datos serán utilizados nalgúns dos exemplos FUNCIÓN DESCRICIÓN:

O intervalo A1: E10 lista os nenos convidadas para a festa de aniversario de Joe. A seguinte información é dada para cada entrada: A columna mostra o nome, a clase B, a continuación, a idade en anos, a distancia para a escola en metros eo peso en quilogramos.

A

B

C

D

E

1

Nome

Nota

Idade

Distancia á escola

Peso

2

Anxo

3

9

150

40

3

Brais

4

10

1000

42

4

Catuxa

3

10

300

51

5

Daniel

5

11

1200

48

6

Eva

2

8

650

33

7

Nota

2

7

300

42

8

Greta

1

7

200

36

9

Helena

3

9

1200

44

10

Irene

2

8

1000

42

11

12

13

Nome

Nota

Idade

Distancia á escola

Peso

14

>600

15

16

DCONTAR

5


A fórmula na cela B16 é BDCONTAR = (A1: E10; 0; A13: E14)

Parámetros das Funcións de base de datos:

Os elementos a seguir son os axustes de parámetros para todas as funcións de base 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.

Escolla - Calc do LibreOffice - Calcular para definir como actúa o Calc do LibreOffice ao buscar entradas idénticas.

See also the Wiki page about Conditional Counting and Summation.

BDMEDIA

DAVERAGE dá a media dos valores de todas as células (campos) en todas as liñas (rexistros de base de datos) que corresponden aos criterios de busca especificados.

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


Sintaxe

DAVERAGE (Base de Datos; DatabaseField; campo 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.

Exemplo

Para saber o peso medio de todos os nenos da mesma idade no exemplo anterior (role para arriba, por favor), introduza a seguinte fórmula na B16:

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

Na liña 14, en Age, escriba 7, 8, 9, e así por diante, un despois do outro. O peso medio de todos os nenos da mesma idade aparece.

BDCONTAR

DCOUNT conta o número de filas (rexistros) nunha base de datos que corresponden aos criterios de busca especificados e conteñen valores numé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".


Sintaxe

DCOUNT (Base de Datos; DatabaseField; campo de busca)

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

Exemplo

No exemplo anterior (role para arriba, por favor), queremos saber cantos fillos ten que viaxar máis de 600 metros para a escola. O resultado é para ser almacenado na cela B16. Sitúe o cursor na cela B16. Insira a fórmula = DCOUNT (A1: E10; 0; A13: E14) en B16. O Asistente de funcións axuda a intervalos de entrada.

Base de datos é o intervalo de datos a ser avaliado, incluíndo os seus cabeceiras: neste caso A1: E10. DatabaseField especifica a columna co seu criterio de busca: neste caso, o conxunto de base de datos. campo de busca é o intervalo onde podes escribir os parámetros de busca: neste caso, A13: E14.

Para saber cantas nenos na segunda serie son máis de 7 anos de idade, elimine a entrada> 600 na cela D14 e escriba 2 na cela B14 baixo Reixa, e escriba 7 en C14 célula á dereita. O resultado é 2. Dous nenos están en segundo grao e máis de 7 anos de idade. Como os dous criterios están na mesma liña, que están conectados por E.

BDCONTARA

DCOUNTA conta o número de filas (rexistros) nunha base de datos que correspondan ás condicións de busca especificadas e conteñen valores numéricos ou alfanumé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".


Sintaxe

DCOUNTA (Base de Datos; DatabaseField; campo de busca)

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

Exemplo

No exemplo anterior (role para arriba, por favor), pode buscar o número de nenos cuxo nome comeza cun E ou unha letra posterior. Edite a fórmula na B16 para ler = DCOUNTA (A1: E10;"Nome"; A13: E14) . Eliminar os vellos criterios de busca e introduza = Correo en Nome no campo A14. O resultado é 5. Se agora borrar todos os valores numéricos para Greta na liña 8, o cambio de resultados a 4. Liña 8 xa non está incluído na conta porque non contén ningún valores. O nome é Greta texto, non un valor. Nótese que o parámetro DatabaseField debe apuntar a unha columna que pode conter valores.

BDOBTER

DGET devolve o contido da cela referenciada en unha base de datos que se corresponde cos criterios de busca especificados. En caso dun erro, a función devolve # VALOR! para ningunha liña atopada, ou Err502 para máis dunha célula atopada.

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


Sintaxe

DGET (Base de Datos; DatabaseField; campo 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.

Exemplo

No exemplo anterior (role para arriba, por favor), queremos determinar o grao de un neno está, cuxo nome foi inscrito no célula A14. A fórmula é inserida na cela B16 e difire lixeiramente dos exemplos anteriores porque só unha columna (un campo de base de datos) pode ser inserido DatabaseField . Introduza a seguinte fórmula:

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

Enter the name Frank in A14, and you see the result 2. Frank is in second grade. Enter "Age" instead of "Grade" and you will get Frank's age.

Ou escriba o valor 11 na cela única C14 e eliminar as outras entradas nesta liña. Editar a fórmula en B16 como segue:

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

No canto de a nota, o nome é consultado. A resposta parece dunha soa vez: Daniel é o único neno con idade entre 11.

BDMÁX

DMAX devolve o contido máximo dunha cela (campo) nunha base de datos (todos os rexistros) que corresponda ás condicións de busca especificadas.

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


Sintaxe

DMAX (Base de Datos; DatabaseField; campo 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.

Exemplo

Para descubrir o que o neno máis pesada en cada serie pesaba no exemplo anterior (role para arriba, por favor), introduza a seguinte fórmula na B16:

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

Baixo Grao, introducir 1, 2, 3, e así por diante, un despois do outro. Despois de introducir un número de serie, o peso do neno máis pesada en que grao aparece.

BDMÍN

DMIN devolve o contido mínimo dunha cela (campo) nunha base de datos que se corresponde cos criterios de busca especificados.

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


Sintaxe

DMIN (Base de Datos; DatabaseField; campo 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.

Exemplo

Para atopar a distancia máis curta para a escola para os nenos de cada grao de exemplo anterior (role para arriba, por favor), introduza a seguinte fórmula na B16:

= DMIN (A1: E10;"Distancia á escola"; A13: E14)

Na liña 14, baixo Reixa, escriba 1, 2, 3, e así por diante, un despois do outro. A distancia máis curta para a escola para cada serie é exhibida.

BDPRODUTO

DPRODUCT multiplica todas as celas dun intervalo de datos onde o contido da cela corresponden aos 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".


Sintaxe

DPRODUCT (Base de Datos; DatabaseField; campo 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.

Exemplo

Co exemplo da festa de aniversario enriba (role para arriba, por favor), non hai ningunha aplicación significativa desta función.

BDDESVEST

DSTDEV calcula o desvío estándar dunha poboación a partir dunha mostra, utilizando os números dunha columna de base de datos que corresponden ás condicións dadas. Os rexistros son tratados como unha mostra de datos. Isto significa que os nenos do exemplo representan un corte transversal de todos os nenos. Nótese que un resultado representativo non pode ser obtida a partir dunha mostra de menos dun milleiro.

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


Sintaxe

DSTDEV (Base de Datos; DatabaseField; campo 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.

Exemplo

Para atopar o desvío estándar do peso de todos os nenos da mesma idade no exemplo (rolar para arriba, por favor), introduza a seguinte fórmula na B16:

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

Na liña 14, en Age, escriba 7, 8, 9, e así por diante, un despois do outro. O resultado mostra é a desviación estándar do peso de todos os nenos desta idade.

BDDESVESTP

DSTDEVP calcula o desvío estándar dunha poboación en base a todas as células dun intervalo de datos, que corresponden aos criterios de busca. Os rexistros do exemplo son tratados como toda a poboación.

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


Sintaxe

DSTDEVP (Base de Datos; DatabaseField; campo 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.

Exemplo

Para atopar o desvío estándar do peso de todos os nenos da mesma idade na festa de aniversario de Joe (rolar para arriba, por favor), introduza a seguinte fórmula na B16:

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

Na liña 14, en Age, escriba 7, 8, 9, e así por diante, un despois do outro. O resultado é a desviación estándar do peso para todos os nenos da mesma idade cuxo peso foi verificada.

BDSUMA

DSUM devolve o total de todas as células en un campo de base de datos en todas as liñas (rexistros) que corresponden aos criterios de busca especificados.

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


Sintaxe

DSUM (Base de Datos; DatabaseField; campo 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.

Exemplo

Para atopar a lonxitude da distancia combinada á escola de todos os nenos na festa de aniversario de Joe (rolar para arriba, por favor), que están na segunda serie, introduce a seguinte fórmula na cela B16:

= DSUM (A1: E10;"Distancia á escola"; A13: E14)

Introduza 2 na liña 14 baixo do grao. A suma (1950) das distancias á escola de todos os nenos que están na segunda serie é exhibida.

BDVAR

DVAR dá a varianza de todas as celas dun campo de base de datos en todos os rexistros que corresponden aos criterios de busca especificados. Os rexistros do exemplo son tratados como unha mostra de datos. Un resultado representativo non pode ser obtida a partir dunha mostra de poboación de menos dun milleiro.

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


Sintaxe

DVAR (Base de Datos; DatabaseField; campo 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.

Exemplo

Para atopar a variación do peso de todos os nenos da mesma idade do exemplo anterior (role para arriba, por favor), introduza a seguinte fórmula na B16:

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

Na liña 14, en Age, escriba 7, 8, 9, e así por diante, un despois do outro. Vai ver como resultado da variación dos valores de peso para todos os nenos desta idade.

BDVARP

DVARP calcula a varianza de todos os valores de células nun campo de base de datos en todos os rexistros que corresponden aos criterios de busca especificados. Os rexistros son do exemplo son tratados como unha poboación enteira.

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


Sintaxe

DVARP (Base de Datos; DatabaseField; campo 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.

Exemplo

Para atopar a variación do peso para todos os nenos da mesma idade na festa de aniversario de Joe (rolar para arriba, por favor), introduza a seguinte fórmula na B16:

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

Na liña 14, en Age, escriba 7, 8, 9, e así por diante, un despois do outro. A variación do peso valores para todos os nenos desta idade que asisten a festa de aniversario de Joe aparece.

Precisamos da súa axuda!