AppNote XE0186: Database Tips |
The information in this article applies to:
- Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a
- Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0
SUMMARY
This article discusses how to maintain database information, sort a database, analyze database information, set ranges to extract data and summarize a database with a table.
GENERAL INFORMATION
A database is a structured table of information. When information is stored in a database, you can quickly and easily locate and retrieve individual pieces of information. This Application Note describes how you can more productively use the database features of Microsoft Excel.
SORTING A DATABASE
To keep records intact when you sort a database, select the entire cell range to be sorted (excluding the field names) before you choose Sort from the Data menu. Selecting the entire cell range allows you to sort using a column as a sort key while keeping the information in each row intact. For example, if your database is in cells A1:D25 with your field names in A1:D1, select cells A2:D25 to sort your records.
In Microsoft Excel versions 2.x, if you sort your database frequently, define a separate name (for example, Data_Sort) to refer to the records alone, omitting the field names. After you define Data_Sort, you can quickly select the records you want to sort by choosing Go To from the Formula menu and typing "DATA_SORT" (without the quotation marks). As you add new records to your database, you will need to redefine Data_Sort to include additional rows.
You can also use this method in Microsoft Excel versions 3.0 and 4.0. However, there is another method that is more efficient. When you define Data_Sort, type the following formula in the Refers To box in the Define Name dialog box:
=OFFSET(Database,1,0,ROWS(Database)-1)
This formula sets the Data_Sort range to be offset one row from the first row of your database, thereby omitting field names. Also, the size of the range will be automatically adjusted when your database range changes due to record additions and deletions.
Sort Order
Microsoft Excel always places any blank cells at the bottom of a selection, regardless of whether Ascending or Descending sort order is selected. The sort order for text entries, from left to right and top to bottom, is as follows:
Microsoft Excel versions 2.2, 3.0, and 4.0
for the Macintosh and versions 2.x and 3.0 for Windows
------------------------------------------------------
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ' ( ) * + , - . / : ; < = >
? @ A B C D E F G H I J K L M N O P Q R S T W U V W X Y Z [ \ ] ^ _
` { | } ~
Microsoft Excel version 4.0 for Windows:
----------------------------------------
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ' ( ) * + , - . / : ; < = >
? @ [ \ ] ^ _ ` { | } ~ A B C D E F G H I J K L M N O P Q R S T W U
V W X Y Z
NOTE: The sort order is not case sensitive. (For example, "A" and "a" are treated the same and will appear in the same order as they did in the original selection. If "a", "A", and "a" appear in three separate cells, choosing Sort will have no effect.)
Sorting on More Than Three Fields
To perform a sort on more than three fields (keys) at one time, you must do multiple sorts, working backwards from the least-significant sort key. For example, assume you want to sort the range A2:E25 with the data in column E sorted within the data in column D, which is sorted within the data in column C and so on, back to column A. To perform this type of sort, do the following:
- Select cells A2:E25.
- From the Data menu, choose Sort. For the 1st Key, enter $D$2, and for the 2nd Key, enter $E$2. Choose OK. The data in column E will now be sorted within the data in column D.
- With the range still selected, from the Data menu, choose Sort. For the 1st Key, enter $A$2, enter $B$2 for the 2nd Key, and $C$2 for the 3rd Key. Choose OK.
This last sort will keep the sorted order of columns D and E while sorting column C within column B within column A. Using this technique (starting with the least significant and working back to the most significant), you can sort using any number of keys.
MAINTAINING DATABASE INFORMATION
You can insert and delete records in a database either manually or with a form. To access a data form, choose Form from the Data menu.
Inserting Records
If you are not using a data form (which automatically redefines the database range when a new record of information is entered), you should define the database to include a blank row at the bottom of your data. Then, before you add a new record to the database, insert a new row by selecting the blank row after your last record and choosing Insert from the Edit menu. Using this method, you will not have to redefine the database every time a record is added. Note that if you have data to the right or the left of your database range, this procedure may displace it. Alternatively, rather than select the entire row, you can limit your selection to only those cells below your last record. When you choose Insert from the Edit menu, select the Shift Cells Down option.
Deleting Records
To delete the records that match your criteria, choose Delete from the Data menu (there is no keyboard command equivalent for this command). Microsoft Excel will erase the entries in every field of each record that matches the criteria.
Warning: Using the Delete command when your criteria range contains a totally blank row will erase the entire database because a blank criterion matches every record.
ANALYZING AND REPORTING DATABASE INFORMATION
To find and extract database information, you must have a valid criteria and extract range outside your database range.
Setting Your Criteria Range
A valid criteria range must contain at least two cells (two rows by one column):
- If you are using comparison criteria, the top row will contain your database field labels. The field labels used in your criteria range must be identical to the labels in your database range. To ensure that these labels are identical, copy the desired field labels from your database range and paste them in your criteria range.
- If you are using computed criteria, the top row must contain a name other than a database field label.
In the cells directly beneath the labels or names, enter the data you want to match or the formula you want to compute. To set your criteria range, select the top row containing your labels or names and the cells beneath them, and choose Set Criteria from the Data menu.
Entering Multiple Criteria
You can specify "and" and "or" relationships based on where you position different criteria within a criteria range. You can also use the AND() and OR() functions for computed criteria.
Finding Records That Match Criteria X and Criteria Y:
When two or more entries are on the same row of a criteria range, Microsoft Excel selects the records that meet all the criteria. To specify two criteria for the same field, duplicate the field label in the criteria range. For example, suppose you want to select those records with entries in the Income field that fall between $18,000 and $32,000. To do this, you must specify two criteria that relate to the Income field, one to look for data that is greater than $18,000 and the other to look for data that is less than $32,000. In this case, two criteria are placed under separate instances of the Income field heading. Since both entries are on the same row of the criteria range and both are beneath an Income field heading, Microsoft Excel combines them and finds only those records with an income that is both greater than $18,000 and less than $32,000.
In the following worksheet, the database is defined as A1:C5, the criteria range is defined as E1:H2, and the extract range is defined as E4:G4. When you choose Find from the Data menu, the records for Green and Simpson will be highlighted because each has an income greater than $18,000 but less than $32,000. Similarly, when you choose Extract from the Data menu, the records for Green and Simpson will be extracted to the extract range.
Alternatively, you can use a computed criterion to find and extract the records. In cell D1, type the name SALARY. In cell D2, enter the formula:
=AND(B2>18000,B2<32000)
The first cell reference in the Income field must be used in the formula and it must be relative. If you then select cells D1:D2 and choose Set Criteria from the Data menu, when you find or extract data, the formula will be applied to each individual record, and only the records in the database that return TRUE for both conditions will be found or extracted.
A B C D E F G H ---------------------------------------------------------------------- 1 Name Income Department Name Income Income Department 2 Green $23,000 Programming >18000 <32000 3 Black $8,000 Tech Support 4 Simpson $25,000 Programming Name Income Department 5 Thompson $35,000 Tester
Finding Records That Match Criteria X or Criteria Y:
When you want to find records that meet one of two or more criteria, place your criterion entries into separate rows and then include those rows in the criteria range. Microsoft Excel treats entries in separate rows as an "or" clause, finding or extracting records that match one condition or another.
Using the following sample database (the database is defined as A1:C5, the criteria range is defined as E1:G3, and the extract range is E4:G4), suppose you want to select those records where either the Income field is greater than $24,000 or the Department field is equal to Tech Support. The criteria range is set up such that you will find or extract the records Black, Simpson, and Thompson; the latter two have an income greater than $24,000 and Black's Department is Tech Support.
Alternatively, you can use a computed criterion to find and extract the records. In cell D1, enter the name INCDEPT. In cell D2, enter the formula:
=OR(B2>24000,C2="Tech Support")
Since you are testing two separate fields, the first cell reference within both fields must be used in the formula and they must be relative references. If you then select cells D1:D2 and choose Set Criteria from the Data menu, when you find or extract data, the formula will be applied to each individual record, and only the records in the database that return TRUE for either one of the conditions will be found or extracted.
A B C D E F G ------------------------------------------------------------- 1 Name Income Department Name Income Department 2 Green $23,000 Programming >24000 3 Black $8,000 Tech Support Tech Support 4 Simpson $25,000 Programming Name Income Department 5 Thompson $35,000 Testing
Specifying Dates as Criteria
If you want to extract all records in a database that match a specific date, enter that date in the criteria range under the appropriate field label. If you want to extract all records that are earlier than or later than a specific date, or if you want to extract all records that fall within a specified date range, the method to use varies according to the version of Microsoft Excel you are using.
Microsoft Excel Version 4.0
---------------------------
In Microsoft Excel version 4.0, use the comparison operators such as
the less than (<) and greater than (>) symbols, followed by the date.
For example, the following criteria in cells F1:F2 will extract all
records in a database with an entry in the Date field earlier than
3/1/93:
F
1 Date
2 <3/1/93
Microsoft Excel Versions 3.0 and Earlier
----------------------------------------
In Microsoft Excel versions 3.0 and earlier, when you enter a date
with a comparison operator, the date is evaluated as text rather than
as its underlying serial value. As a result, an attempt to find or
extract records that are greater than or less than a specified date
will result in no records being found or extracted. To use a
comparison operator with a date, the date must either be the serial
value,
F
-------------
1 Date
2 <3/1/93
a formula that evaluates to a serial value,
F
-------------
1 Date
2 "<"&DATE(93,3,1)
or a cell reference that contains a date:
F G
1 Date 3/1/93
2 ="<"&G1
NOTE: When you use a formula or cell reference, the comparison
operator is entered as text with the formula or reference concatenated
to the end.
If you are using the 1900 date system (the default date system for
Microsoft Excel for Windows), when you enter the formula and cell
references, <34029 will be displayed in the cell. The formula,
DATE(93,3,1) evaluates to 34029, as does the cell reference, G1. If
you are using the 1904 date system (the default date system for
Microsoft Excel for the Macintosh), the serial value for 3/1/93 is
32567. In each of these cases, the criteria range is defined as F1:F2.
Setting Your Extract Range
When you extract information from a database, your extract range must be separate from your database and criteria ranges. The field names in the extract range must be in a single row (as must the field names at the top of the database and criteria ranges). To ensure that field names are identical, use the Copy and Paste commands. Microsoft Excel will not recognize the extract range if a field name does not exactly match a field name in the database.
When you extract information from a database in Microsoft Excel versions 2.x, in your extract range, select the field names for the fields you want to extract, and choose Extract from the Data menu.
You can also use this method in Microsoft Excel versions 3.0 and 4.0, although in these versions of Microsoft Excel you have the option of setting the extract range from the Data menu. Because this method creates the reserved defined name "Extract" on the spreadsheet, you do not have to select the extract field names prior to extracting records. If you select only the field names when you extract information, or if you set an extract range in version 3.0 or 4.0 that refers to the field names alone, each record that matches the current criteria will be extracted and displayed on the document after you choose Extract from the Data menu.
Caution: When you select the field names and choose Extract from the Data menu or when you've set your extract range by selecting only the field names and choosing Set Extract from the Data menu, any data between the field names in your extract range and the last row in your worksheet will be cleared. This will occur even when no data records are extracted. To avoid erasing your data, either create your extract range below or to the right of your data, or restrict the size of your extract range.
Restricting the Extract Range:
You can restrict the amount of extracted information in several ways.
In Microsoft Excel versions 2.x, selecting a range that extends below the extract field names will restrict the possible number of extracted records to the selected area. Selecting the field names and the five rows beneath them, for example, will restrict any extraction to five records.
NOTE: If more records meet the criteria but cannot be displayed due to this restriction, Microsoft Excel will display an alert message stating that the extract range is full.
In Microsoft Excel versions 3.0 and 4.0, you can use the method above to select the extract range or, once you select your restricted range, you can choose Set Extract from the Data menu. This command will define the selected range with the reserved name "Extract." When you define the extract range with Set Extract, you can extract records without first having to select an extract range (because the extract range is already defined).
NOTE: If you set an extract range that includes, for example, five additional rows beneath the field names, an extraction will produce a maximum of five retrieved records. If more records meet the criteria than fit in the selected area, Microsoft Excel will display an alert message stating that the extract range is full.
Setting an External Extract Range:
In Microsoft Excel versions 2.x, 3.0, and 4.0, to extract database information to an external worksheet, do the following:
- Select your database and choose the Set Database command from the Data menu.
- Copy the field names from the database, and paste them in the desired external worksheet.
- On this external sheet, select the field names and one row beneath the field names.
- From the Data menu, choose Set Criteria.
- Copy the field names again and paste them in another location in the external worksheet. This area will be called the extract range and is where the extracted information will be returned. If you are using Microsoft Excel version 3.0 or 4.0, select these field names and choose Set Extract from the Data menu.
- From the Formula menu, choose Define Name. In the Name box, type DATABASE. In the Refers To box, type the name of the worksheet within single quotation marks, and type an exclamation point followed by the word "Database." For example:
='Worksheet1'!Database
- When you are ready to extract the information:
If you are using Microsoft Excel versions 2.x, select the field names and choose Extract from the Data menu.
If you are using Microsoft Excel versions 3.0 or 4.0, choose Extract from the Data menu.
NOTE: You do not need to select the field names if you have set them as the extract range as mentioned in step 5 above.
Important: When you extract information to an external worksheet, both the worksheet containing the database and the external worksheet must be open. If the worksheet containing the database is not open, an error message will appear stating that the database range is not valid.
Database Functions
When you use database functions such as DSUM() and DCOUNT(), you can use any range that has a defined name for the criteria argument. To specify a range other than the defined criteria range, follow the same rules for setting the criteria range in a database (as specified in the "Setting Your Criteria Range" section in this Application Note). Instead of choosing Set Criteria from the Data menu to define the criteria, choose Define Name from the Formula menu. Assign a unique name to the range and use this name for the criteria argument in your database function.
The second argument in a database function, the field argument, can be an index number. For example, if you want to find the sum of the entries in the Salary field, and if this field is the second field or column in your database, you could use either of the following formulas:
=DSUM(database,"salary",criteria) -or- =DSUM(database,2,criteria)
NOTE: For the Find and Extract commands to work correctly, you must choose Set Criteria to set a criteria range.
Summarizing a Database with a Table
You can use a table to create a summary of totals for a database on a worksheet. Microsoft Excel includes functions that operate on a database and restricts their operation to the records that meet the criteria you enter in the worksheet. To use the same database functions repeatedly on a database, but with different criteria each time, use the Table command on the Data menu to create a data table. The data table input values are substituted into the criteria range, and the results of performing the database function with the different criteria are displayed in the table.
The following sample worksheet uses the DSUM() function in a two-input table. In the worksheet below, the range A1:C2 is defined as "Criteria", A4:C15 is defined as "Database", and E4:H8 is the area occupied by the table. The purpose of this table is to simultaneously determine gross sales by sales-person and by region.
A B C D E F G H ------------------------------------------------------------------ 1 Region Salesperson Sales 2 3 4 Region Salesperson Sales 618 MW NW SW 5 NW Jones $50.00 Brown $80.00 $75.00 $45.00 6 SW Smith $75.00 Jones $25.00 $50.00 $65.00 7 SW Smith $33.00 Owen $70.00 $0.00 $90.00 8 SW Brown $45.00 Smith $10.00 $0.00 $108.00 9 NW Brown $75.00 10 MW Brown $80.00 11 MW Jones $25.00 12 SW Jones $65.00 13 MW Smith $10.00 14 SW Owen $90.00 15 MW Owen $70.00
To create a table similar to the previous table:
- Type the data in columns A, B, and C.
- Set the database to the range A4:C15, and set the criteria to the range A1:C2.
- To create the table, in cell E4, type the formula =DSUM(Database,3,Criteria).
- In cells E5:E8, type the names, and type the regions in cells F4:H4.
- Select the range of your table, E4:H8, and choose Table from the Data menu.
This will bring up a dialog box asking for the Row and Column Input Cell. Because the different regions are listed in the top row of the table, and the cell in the criteria range in which these regions should be entered is $A$2, $A$2 must be the Row Input Cell. Following the same reasoning, the Column Input Cell must be $B$2. Enter these cell references in the appropriate boxes in the Table dialog box and choose OK.
NOTE: If you do not want the value of the formula (618) in cell E4 to show, select cell E4, choose Number from the Format menu, and type the format ;;; (this will hide all values returned by the DSUM() function in that cell).
Additional query words: noupd
Keywords : kbappnote
Version : WINDOWS:2.0,3.0,4.0,4.0a; MACINTOSH:2.2,3.0
Platform : MACINTOSH WINDOWS
Issue type :
Technology :
Last Reviewed: April 5, 1999 |
