|
The information in this article applies to:
- Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
- Microsoft Excel for OS/2 version 2.2, 3.0
- Microsoft Excel for the Macintosh, version 2.x, 3.0, 4.0
SUMMARY
You can use the Microsoft Excel Data Table command to summarize
information for several field names in a Database more efficiently than
setting several independent criteria and several functions. The example
below illustrates how a data table can perform this task with the DSUM
function.
MORE INFORMATION
This example summarizes information for two different products listed
in a Microsoft Excel database.
Microsoft Excel version 5.0
- Enter the following database, criteria, and data table information
into a spreadsheet:
A1: Product B1: Quantity C1: Sales
A2: bolts B2: 50 C2: $3.00
A3: bolts B3: 70 C3: $2.00
A4: nuts B4: 60 C4: $1.00
A5: nuts B5: 80 C5: $1.00
A6: B6: C6:
A7: Product B7: Quantity C7: Sales
A8: nuts B8: C8:
A9: B9: C9:
A10: =DSUM(Database,B7,Criteria) B10: Quantity C10: Sales
A11: nuts B11: C11:
A12: bolts B12: C12:
- Select the range A1:C5. From the Insert menu, choose Name, and then
choose Define. In the Names In Workbook box, type "Database" (without
the quotation marks) and choose Add.
- Clear the contents of the Refers To box by selecting it and pressing the
DELETE key. With the insertion point in the Refers To box, select
the range A7:C8 on your worksheet. In the Names In Workbook box, type
"Criteria" (without the quotation marks) and choose Add. Choose Close.
- Select the range A10:C12. From the Data menu, choose Table. In the
Row Imput Cell box, type "B7" (without the quotation marks). In the
Column Imput Cell box, type "A8" (without the quotation marks). Choose
OK.
Microsoft Excel versions 2.x, 3.0 and 4.0
- Enter the following database, criteria, and data table information
into a spreadsheet:
A1: Product B1: Quantity C1: Sales
A2: bolts B2: 50 C2: $3.00
A3: bolts B3: 70 C3: $2.00
A4: nuts B4: 60 C4: $1.00
A5: nuts B5: 80 C5: $1.00
A6: B6: C6:
A7: Product B7: Quantity C7: Sales
A8: nuts B8: C8:
A9: B9: C9:
A10: =DSUM(Database,B7,Criteria) B10: Quantity C10: Sales
A11: nuts B11: C11:
A12: bolts B12: C12:
- Select the range A1:C5. From the Data menu, choose Set Database.
- Select the range A7:C8. From the Data menu, choose Set Criteria.
- Select the range A10:C12. Choose Table from the Data menu. In the
dialog box, enter B7 as row input cell and A8 as column input cell.
Results
The total quantities and sales for nuts and bolts are returned in
cells B11:C12. The results are as follows:
B11: 140 C11: 2
B12: 120 C12: 5
REFERENCES
"Function Reference," version 4.0, pages 91-93
"User's Guide 2," version 4.0, pages 6-10
"Function Reference," version 3.0, pages 54-56
"User's Guide," version 3.0 for Windows, pages 269-272
"User's Guide," version 3.0 for the Macintosh, pages 261-264
|