資料庫函式
本節將介紹的函式都針對作為一個資料列 (一項記錄) 來管理的資料。
Database 分類可能會和 Collabora Office 中整合的資料庫相混淆。但是，Collabora Office 中的資料庫與 Collabora Office Calc 中的 Database 分類之間並無關聯。
範例資料：
某些函式的描述範例中將用到以下資料：
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 |
年級 |
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;0;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.
See also the Wiki page about Conditional Counting and Summation.
DAVERAGE
DAVERAGE 傳回符合指定搜尋條件的所有列 (資料庫記錄) 中所有儲存格 (欄位) 的平均值。
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(Database; DatabaseField; SearchCriteria)
If the DatabaseField argument is omitted, DCOUNT returns the count of all records that satisfy Criteria. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (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 中。然後在 B16 中輸入公式 =DCOUNT(A1:E10;0;A13:E14)。「函式精靈」會協助您輸入範圍。
Database 是包括標題在內所要計算的資料範圍：在本例中為 A1:E10。DatabaseField 可指定用於搜尋條件的欄：在本例中為整個資料庫。SearchCriteria 是可輸入搜尋參數的範圍：在本例中為 A13:E14。
若要瞭解二年級有多少兒童的年齡超過 7 歲，請刪除儲存格 D14 中的 >600 項目，並在 [年級] 下的儲存格 B14 中輸入 2，然後在右側的儲存格 C14 中輸入 >7。結果為 2。表示二年級有兩名兒童的年齡超過 7 歲。由於同一列中有兩個條件，因此會以 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(Database; DatabaseField; SearchCriteria)
If the DatabaseField argument is omitted, DCOUNTA returns the count of all records that satisfy Criteria. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (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 或後續字母的兒童人數。編輯 B16 中的公式可讀取 =DCOUNTA(A1:E10;"姓名";A13:E14)。刪除舊的搜尋條件，然後在欄位 A14 的 [姓名] 下輸入 >=E。結果為 5。若現在刪除列 8 中 Greta 的所有數值，結果會變成 4。由於列 8 不包含任何數值，因此計數中不再包含此列。姓名 Greta 是文字，而非數值。請注意，DatabaseField 參數必須指向可包含數值的欄。
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 中輸入姓名的兒童年級。由於 DatabaseField 僅可輸入一欄 (一個資料庫欄位)，因此在儲存格 B16 中輸入之公式會與之前的範例略有不同。請輸入下列公式：
=DGET(A1:E10;"年級";A13:E14)
在 A14 中輸入姓名 Frank，會顯示結果 2。表示 Frank 就讀二年級。輸入 [年齡] 取代 [年級]，則會得到 Frank 的年齡。
或僅在儲存格 C14 中輸入值 11，然後刪除此列中的其他項目。請以下列方式編輯 B16 中的公式：
=DGET(A1:E10;"姓名";A13:E14)
現在問的則是名字而不是年級。此時顯示的答案等於：Daniel 是唯一一個 11 歲的小朋友。
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 生日派對之所有兒童的體重值變異數。