Database-specific functions
Although every Calc function can be used for database manipulation, the functions in Table 17 are specifically designed for use as a database. The descriptions in Table 17 use the following terms interchangeably: row and record, cell and field, and database and all rows.
Table 17. Database functions in a Calc document.
| Function | Description |
|---|---|
| DAVERAGE | Return the average of all fields that matches the search criteria. |
| DCOUNT | Count the number of records containing numeric data that match the search criteria. |
| DCOUNTA | Count the number of records containing text data that match the search criteria. |
| DGET | Return the contents of a field that matches the search criteria. |
| DMAX | Return the maximum content of a field that matches the search criteria. |
| DMIN | Return the minimum content of a field that matches the search criteria. |
| DPRODUCT | Return the product of the fields that matches the search criteria. |
| DSTDEV | Calculate the standard deviation using the fields that match the search criteria. The fields are treated as a sample. |
| DSTDEVP | Calculate the standard deviation using the fields that match the search criteria. The fields are treated as the entire population. |
| DSUM | Return the sum of all fields that matches the search criteria. |
| DVAR | Calculate the variance using the fields that match the search criteria. The fields are treated as a sample. |
| DVARP | Calculate the variance using the fields that match the search criteria. The fields are treated as the entire population. |
The syntax for the database functions are identical.
DCOUNT(database; database field; search criteria)
The database argument is the cell range that defines the database. The cell range should contain the column labels (see Listing 13). The following examples, assume that the data from Table 1 is placed in Sheet 1 and the filter criteria in Table 4 is placed in Sheet 2.
Listing 13. The database argument includes the headers.
=DCOUNT(A1:G16; "Test 2"; Sheet2.A1:G3)
The database field specifies the column on which the function operates after the search criteria is applied and the data rows are selected. The database field can be specified using the column header name or as an integer. If the column is specified as an integer, 0 specifies the entire data range, 1 specifies the first column, 2 specifies the second column, and so on. Listing 14 calculates the average test score for the rows that match the search criteria.
Listing 14. “Test 2” is column 3.
=DAVERAGE(A1:G16; "Test 2"; Sheet2.A1:G3) =DAVERAGE(A1:G16; 3; Sheet2.A1:G3)
The search criteria is the cell range containing search criteria. The search criteria is identical to the advanced filters; criteria in the same row is connected by AND and criteria in different rows is connected by OR.
Conclusion
A Calc document provides sufficient database functionality to satisfy the needs of most people. The infrequently used database functions, such as OFFSET and INDEX, are worth the time to learn and they can save yourself time in the long run.
| Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |
