DATABASE TIPS
======================================================================
Microsoft(R) Product Support Services Application Note (Text File)
XE0186: DATABASE TIPS
======================================================================
Revision Date: 5/93
The following information applies to Microsoft Excel, version See
Below.
--------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the |
| accuracy and the use of this Application Note. This Application |
| Note may be copied and distributed subject to the following |
| conditions: 1) All text must be copied without modification and |
| all pages must be included; 2) If software is included, all files |
| on the disk(s) must be copied without modification [the MS-DOS(R) |
| utility DISKCOPY is appropriate for this purpose]; 3) All |
| components of this Application Note must be distributed together; |
| and 4) This Application Note may not be distributed for profit. |
| |
| Copyright 1988-1993 Microsoft Corporation. All Rights Reserved. |
| Microsoft and MS-DOS are registered trademarks and Windows |
| is a trademark of Microsoft Corporation. |
--------------------------------------------------------------------
This application note applies to Microsoft Excel versions 2.x, 3.0,
and 4.0 for Windows(TM), and to Microsoft Excel versions 2.2, 3.0, and
4.0 for the Macintosh(R).
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 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:
1. Select cells A2:E25.
2. 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.
3. 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:
1. Select your database and choose the Set Database command from the
Data menu.
2. Copy the field names from the database, and paste them in the
desired external worksheet.
3. On this external sheet, select the field names and one row beneath
the field names.
4. From the Data menu, choose Set Criteria.
5. 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.
6. 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
7. 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:
1. Type the data in columns A, B, and C.
2. Set the database to the range A4:C15, and set the criteria to the
range A1:C2.
3. To create the table, in cell E4, type the formula
=DSUM(Database,3,Criteria).
4. In cells E5:E8, type the names, and type the regions in cells
F4:H4.
5. 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).
For additional information on data tables, please see the Application
Note "Creating and Using Tables," (XE0210). To obtain this Application
Note, call Microsoft Product Support Services at (206) 454-2030. Or,
if you have a modem, you can download this and other Application Notes
by calling the Microsoft Download Service at (206) 936-MSDL.
Microsoft(R) Product Support Services Application Note (Text File)
XE0186: DATABASE TIPS
======================================================================
Revision Date: 5/93
The following information applies to Microsoft Excel, version See
Below.
--------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the |
| accuracy and the use of this Application Note. This Application |
| Note may be copied and distributed subject to the following |
| conditions: 1) All text must be copied without modification and |
| all pages must be included; 2) If software is included, all files |
| on the disk(s) must be copied without modification [the MS-DOS(R) |
| utility DISKCOPY is appropriate for this purpose]; 3) All |
| components of this Application Note must be distributed together; |
| and 4) This Application Note may not be distributed for profit. |
| |
| Copyright 1988-1993 Microsoft Corporation. All Rights Reserved. |
| Microsoft and MS-DOS are registered trademarks and Windows |
| is a trademark of Microsoft Corporation. |
--------------------------------------------------------------------
This application note applies to Microsoft Excel versions 2.x, 3.0,
and 4.0 for Windows(TM), and to Microsoft Excel versions 2.2, 3.0, and
4.0 for the Macintosh(R).
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 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:
1. Select cells A2:E25.
2. 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.
3. 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:
1. Select your database and choose the Set Database command from the
Data menu.
2. Copy the field names from the database, and paste them in the
desired external worksheet.
3. On this external sheet, select the field names and one row beneath
the field names.
4. From the Data menu, choose Set Criteria.
5. 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.
6. 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
7. 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:
1. Type the data in columns A, B, and C.
2. Set the database to the range A4:C15, and set the criteria to the
range A1:C2.
3. To create the table, in cell E4, type the formula
=DSUM(Database,3,Criteria).
4. In cells E5:E8, type the names, and type the regions in cells
F4:H4.
5. 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).
For additional information on data tables, please see the Application
Note "Creating and Using Tables," (XE0210). To obtain this Application
Note, call Microsoft Product Support Services at (206) 454-2030. Or,
if you have a modem, you can download this and other Application Notes
by calling the Microsoft Download Service at (206) 936-MSDL.
Comments
Post a Comment