Discussion
Database functions, or Dfunctions, are used to provide calculations based on criteria.
For example, from an employee table of all departments, you may want to calculate the salaries of employees just in the sales department. Although you can physically filter the data to display the records from the sales department and total the found records, you can also use a database function to find the answer without filtering the data.
A database function includes three arguments: database, field, and criteria. The database argument is the table (including the Header Row). The field argument is name of the column you want to use in the computation. The criteria argument refers to the cells that define the conditions for the function. For example, in the Dfunction
=DSUM(A9:F37,F9,A1:F2), A9:F37 is the database argument (and includes the column labels), F9 is the field argument (the column label of the field you want to calculate), and A1:F2 is the criteria argument.
Naming cells makes it easier to enter, copy, or edit database functions. When the database function refers to an Excel table, the Table Name (which refers to the table data without the Header Row) and Excel’s structured referencing make it easier to use database functions. The only range for which you need to create a name is the Criteria Range. Using names and structured referencing the above Dfunction could be entered as =DSUM(EmpData[#All],“Salary”,Criteria1).
Using a database function
Villanova UNIT Training© Page 67
The field argument (the column to be calculated) can be entered as the cell address of the field label, the column number of the field in the database, or the text of the field label enclosed in double quotation marks. Therefore, F9, 6, or“Salary” can all be used to identify the same field.
If you change or delete the criteria referenced in theDfunction, the function automatically recalculates. To create database functions for several values in a field, you must create a separate criteria range for each value. Therefore, to display the salaries of two different departments, you will need to create two separate Department criteria ranges. Each criteria range only needs two cells, the Department heading and a cell below it in which to type the department name.
Procedures
1. Select the cell where you want the result of the formula to appear.
2. Click the Insert Function button on the Formula Bar.
3. Select the Or select a category list.
4. Select Database.
5. Select the name of the database function you want to use in the Select a function list box.
6. Select .
7. Select the table range (including the Header Row) or type the table name (with [#All] qualifier) in the Database box.
8. Select the Field box.
9. Enter the address or name of the column label of the field you want to use in the formula.
10. Select the Criteria box.
11. Enter the address or name of the criteria range.
12. Select .
Page 68 Villanova UNIT Training©
Step-by-Step
Use a database function.
If necessary, copy the column labels to row 1 and delete any previous criteria in the criteria range.
Type production in cell D2 to enter the criteria. Display the Name Box list on the formula bar to view the cell names. The table name is EmpData (which refers to just the table data without the Header Row), the one-row (And) criteria range is named Criteria1, and the two-row (Or) criteria range is named Criteria2.
Steps Practice Data
1. Select the cell where you want the result of the formula to appear.
The cell is selected.
Click cell F4
2. Click the Insert Function button on the Formula Bar.
The Insert Function dialog box opens.
Click on the Formula Bar
3. Select the Or select a category list.
A list of function categories is displayed.
Click Or select a category
4. Select Database.
A list of all available database functions is displayed in the Select a function list box.
Click Database
5. Select the name of the database function you want to use in the Select a function list box.
The function and its arguments appear at the bottom of the Insert Function dialog box.
Scroll as necessary and click DSUM
6. Select OK.
The Insert Function dialog box closes and the Function Arguments dialog box opens with the insertion point in the Database box.
Click
7. Select the table range (including the Header Row) or type the table name (with [#All] qualifier) in the Database box.
The database reference appears in the formula on the formula bar.
Type EmpData[#All]
Villanova UNIT Training© Page 69
Steps Practice Data
8. Select the Field box.
The insertion point appears in the Field box.
Click in the Field box
9. Enter the address or name of the column label of the field you want to use in the formula.
The field reference appears in the formula on the formula bar (Excel will automatically add quotes around the name).
Type Salary
10. Select the Criteria box.
The insertion point appears in the Criteria box.
Click in the Criteria box
11. Enter the address or name of the criteria range.
The range address appears in the formula on the formula bar.
Type Criteria1
12. Select OK.
The Function Arguments box closes and the result of the function appears in the cell.
Click
The correct answer is $466,000, which represents the total salary for people in the Production department.
Change the criteria in cell D2 to Sales. Notice that the DSUM function in cell F4 recalculates.
Close EMPLOY8.XLSX.