数据库函数
本节中列出了数据库函数。数据库函数用来对按照「每行一条记录」的方式组织的数据进行操作。
此处的「数据库」函数类别类别容易与 Collabora Office Base 中的「数据库」混淆。 Collabora Office Base 中的「数据库」与 Collabora Office Calc 中的「数据库」函数类别没有任何联系。
示例数据:
下面的数据会在随后的某些函数示例中使用到:
A1:E10 区域列出了应邀参加 Joe 的生日派对的孩子们。每条记录中均包含了以下信息: A 列是姓名，B 列是年级，然后是年龄、以米表示的到学校的距离、以公斤表示的体重。
|
A |
B |
C |
D |
E |
|
|
1 |
「姓名」 |
「年级」 |
「年龄」 |
「到学校的距离」 |
「体重」 |
|
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 |
「姓名」 |
「年级」 |
「年龄」 |
「到学校的距离」 |
「体重」 |
|
14 |
>600 |
||||
|
15 |
|||||
|
16 |
「DCOUNT」 |
5 |
单元格 B16 中的公式为 =DCOUNT(A1:E10, D1, A13:E14)
数据库函数的参数:
以下各项是适用于所有数据库函数的参数定义:
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 to define how Collabora Office Calc acts when searching for identical entries.
参见关于 条件计数与总和 的 wiki 页面。
DAVERAGE
DAVERAGER 返回所有行 (数据库记录) 中符合指定搜索条件的所有单元格 (字段) 的平均值。
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(Database; DatabaseField; SearchCriteria)
要计算上例中所有同龄孩子的平均体重 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DAVERAGE(A1:E10;"体重";A13:E14)」
在「年龄」下的第 14 行处依次输入「7, 8, 9,」等等。将显示每个年龄组中所有孩子的平均体重。
DCOUNT
DCOUNT 函数用于统计数据库中符合指定条件、并且「数据库字段」列中包含数字型数据的记录数量。
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(数据库; [数据库字段]; 搜索条件)
如果不指定「数据库字段」参数，那么DCOUNT 返回所有满足条件的记录数量。 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).
在上面的示例中 (请向上滚动)，我们要统计的是到学校的路程超过 600 米的孩子的数量。计算结果将被存储在 B16 单元格中。将光标置于 B16，然后输入公式「=DCOUNT(A1:E10, 0, A13:E14)」。「函数向导」可以帮助您输入单元格区域。
「数据库」是要统计的数据区域 (包括其标题): 在本例中为 A1:E10。「数据库字段」代笔了搜索条件将要作用于哪一列: 在本例中是包含了数字型距离值的那一列。「搜索条件」代表了查询条件所在的单元格区域: 在本例中为 A13:E14。
要知道二年级有多少个 7 岁以上的孩子，您可以删除 D14 单元格中的 >600，并在「年级」列的 B14 单元格中输入「2」，然后在右边的 C14 单元格中输入「>7」。结果等于 2，也就是说二年级年龄超过 7 岁的孩子有2个。此处，搜索条件的同一行中有两个条件，所以会被当作 AND 处理。
DCOUNTA
DCOUNTA 计算数据库中匹配指定查找条件并包含数字或字母数字值的行 (记录) 数。
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(数据库; [数据库字段]; 搜索条件)
如果不指定「数据库字段」参数，DCOUNTA 将返回所有满足「条件」的记录数量。 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).
在上面的示例中 (请向上滚动)，您可以查找以字母 E 或字母表中 E 后面的字母作为名字的首字母的孩子的数目。在 B16 中编辑公式「=DCOUNTA(A1:E10;"Age";A13:E14)」。删除旧的查找条件，在「名字」下的单元格 A14 中输入「>=E」。计算结果是 5。如果您现在删除第 8 行中有关 Greta 的所有数据，那么计算结果就变为 4，因为第 8 行中已经不含有任何数值，所以计算时没有包括它。名字 Greta 是文字，不是数值。请注意，数据库字段的参数必须指向含有数值的列。
DGET
DGET 返回数据库中符合指定查找条件的引用单元格的内容。如果出错，函数将返回 #VALUE!，表示找不到行，或返回 Err502，表示找到多个单元格。
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(Database; DatabaseField; SearchCriteria)
在上例中 (请向上滚动)，我们要确定名字输入在单元格 A14 中的孩子所在的年级。在单元格 B16 中输入了公式，此公式与以前的示例略有不同，因为只有一列 (一个数据库字段) 可以作为「DatabaseField」的输入。输入以下公式:
「=DGET(A1:E10;"年级";A13:E14)」
在单元格 A14 中输入「Frank」，返回结果 2。Frank 上二年级。输入「"年龄"」代替 "年级"，就可以获得 Frank 的年龄。
或者仅在单元格 C14 中输入数值「11」，并删除这行中的其他数据。按照以下所示编辑 B16 中的公式:
「=DGET(A1:E10;"名字";A13:E14)」
现在您要确定的是符合某一年龄的孩子的名字。这样您获得的结果便是: Daniel。也就是说年龄为 11 岁的孩子名字是 Daniel。
DMAX
DMAX 返回数据库 (所有记录) 中符合指定查找条件的最大单元格 (字段) 内容。
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(Database; DatabaseField; SearchCriteria)
要计算上例中每个年级最重的孩子的体重 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DMAX(A1:E10;"体重";A13:E14)」
在「年级」下依次输入「1, 2, 3,」等等。输入年级后，将显示该年级中最重的孩子的体重。
DMIN
DMIN 返回数据库中匹配指定查找条件的最小单元格 (字段) 的内容。
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(Database; DatabaseField; SearchCriteria)
要计算上例中各年级孩子距学校最近的距离 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DMIN(A1:E10;"上学路程";A13:E14)」
在「年级」下的第 14 行处依次输入「1, 2, 3,」等等。将显示每个年级到学校的最短路程。
DPRODUCT
DPRODUCT 将单元格内容符合查找条件的数据区域的所有单元格相乘。
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(Database; DatabaseField; SearchCriteria)
对于上面的生日晚会示例 (请向上滚动)，此函数不适用。
DSTDEV
DSTDEV 通过使用数据库列中符合给定条件的数据，来计算基于抽样人群的标准偏差。记录被视为一个数据抽样。这表示示例中的孩子代表所有孩子中的一种典型情况。值得注意的是，如果抽样的数据少于一千个，则无法获得具有代表性的结果。
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(Database; DatabaseField; SearchCriteria)
要计算示例中所有同龄孩子体重的标准偏差 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DSTDEV(A1:E10;"体重";A13:E14)」
在「年龄」下的第 14 行处依次输入「7, 8, 9,」等等。结果显示的是每个年龄组中所有孩子体重的标准偏差。
DSTDEVP
DSTDEVP 计算基于数据区域中匹配查找条件的所有单元格的总体样本的标准偏差。示例中的记录被视为一个完整的总体样本。
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(Database; DatabaseField; SearchCriteria)
要计算参加 Joe 生日晚会的所有同龄孩子体重的标准偏差 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DSTDEVP(A1:E10;"体重";A13:E14)」
在「年龄」下的第 14 行中依次输入「7, 8, 9,」等等。计算结果是所有已检查体重的同年龄孩子体重的标准偏差。
DSUM
DSUM 计算的是所有行 (记录) 中的数据库字段中，符合指定搜索条件的所有单元格数据的总和。
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(Database; DatabaseField; SearchCriteria)
要计算参加 Joe 生日晚会的所有二年级孩子上学路程的总和 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DSUM(A1:E10;"上学路程";A13:E14)」
在「年级」下的第 14 行处输入数字「2」。将显示二年级的所有孩子上学路程的总和 (1950)。
DVAR
DVAR 返回匹配指定查找条件的所有记录的数据库字段中所有单元格数据的方差。示例中的记录被视为一个数据抽样。如果抽样总体样本少于一千个，则无法获得具有代表性的结果。
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(Database; DatabaseField; SearchCriteria)
要计算上例中所有同龄孩子体重的方差 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DVAR(A1:E10;"体重";A13:E14)」
在「年龄」下的第 14 行处依次输入「7, 8, 9,」等等。您将看到该年龄组中所有孩子体重的方差。
DVARP
DVARP 计算所有匹配查找条件的记录的数据库字段中所有单元格值的方差。示例中的记录视为一个总体样本。
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(Database; DatabaseField; SearchCriteria)
要计算参加 Joe 生日晚会的所有同年龄孩子体重的方差 (请向上滚动)，请在单元格 B16 中输入以下公式:
「=DVARP(A1:E10;"体重";A13:E14)」
在「年龄」下的第 14 行中依次输入「7, 8, 9,」等等。将显示参加 Joe 生日晚会的所有同年龄孩子体重的方差。