Databases Reference
In-Depth Information
5
Data Mining in Excel
5.1
INTRODUCTION
Having information is one thing, finding it is another. This chapter is dedicated to helping developers
create tools that allow users to rapidly find the information they need. Having a Worksheet that
contains all the data related to a particular project, experiment, or method is only the first step
toward useful utilization of that information. It can be a daunting task to manually look through a
large Worksheet and extricate entries of data whose parameters fall within a certain set of rules.
Table 5.1 shows parameters utilized when testing electronically actuated valves. The trigger voltage
is the voltage that is first applied to a valve to actuate it. After a valve has been activated, the trigger
voltage will be lowered to a hold in voltage after a specified period of time, usually in milliseconds
(ms). The hold in voltage is usually some fraction of the trigger voltage (usually about a third). The
purpose of the hold in voltage is to continue to keep the valve actuated, but to do so drawing less
current. Although the valve could be driven at the trigger voltage for the entire period of its activation,
doing so would be wasteful of energy. In addition, oftentimes, valves will get extremely hot if driven
at their trigger voltage for an extended period of time. This will decrease the life of the valve, which
is an important consideration, especially if the valve is located in an area that makes its replacement
difficult. The applied vacuum is the maximum amount of vacuum present in the lines connected to the
valve. The higher the vacuum, the more difficulty the valve will have in either opening or closing
because it will have to exert force against the suction pressure that exists in the lines. The valve state
is simply whether the valve was determined to be open or closed when the following trigger voltage,
hold in voltage, and vacuum were applied to the valve for testing purposes.
Table 5.2 shows the ranges between which each valve actuation parameter may vary. Now,
suppose the user wished to know when test conditions occurred that the valve stayed open (did not
actuate) and the following conditions occurred:
1.
Trigger Voltage > 16.5 V
2.
Hold in Voltage > 6.5 V
3.
Applied Vacuum < 60 psi
For the information in Table 5.1, this is not difficult to determine manually. There are only 10 items
in the table. What happens when there are
of items in a table? It is impossible to do this
by hand. If this information is already in a database, then that database can be queried utilizing
the aforesaid conditions. It may not be prudent to put this information into a database unless it
meets or does not meet certain conditions. In this chapter, the reader will be shown how to create
tools to solve problems just like the one shown.
Pulling parameters out of a pool of information that meet a certain set of specified criteria is called
“data mining.” A number of tools exist to perform data-mining operations. Often, however, users need
some quick and easy methods to extract some information and omit other pieces of information. Having
the ability to perform such operations within Excel by utilizing macros gives the user the ability to limit
the amount of extraneous information that will be archived or included within a report.
Once information can be qualified as to meeting or not meeting a certain set of selection criteria, it
must be decided what should be done with the information that falls within the specified criteria. The
information can be treated in one of two ways. The information can be isolated from the pool it resides
in, that is, the information can be extracted and written into a Worksheet, Workbook, or file. The other
option is to leave the information within the pool it resides in and call attention to it in some manner,
thousands
157
