Microsoft Excel contains and number of database
functions that are available and will assist you in extracting information from your database.
In this segment you will learn how to find and activate the function, set up spreadsheets
to facilitate using the database functions, and using these functions. Here we have a simple database where we have
some information about trees, the name, its height, age, yield and profit and what we
want to do is extract some information, so count how many trees meet certain conditions,
add up certain information depending on some conditions. The important thing about database
functions is that it allows you to specify the criteria within the spreadsheet itself. So just to give you an example, what we need
to do is take the headings that we have here and copy them and paste them here. And now
what we can do is specify certain criteria t pull through. So lets say I want to look
at apple trees and I want the height lets say to be bigger then 10. This will now allow
you to use functions to use these criteria to pull information out of this database. Lets try a simple function first for now.
We just want to count how many of the trees here are apple trees which are bigger then
10. SO if you click on the function wizard, and you go to the database section, you will
see that there is one called DCOUNT. When I click OK, the first criteria is where is
the database. So we just highlight the entire database and you make it absolute by clicking
the F4 key. It then asks for the field. And you can see it says a field is either the
label of the column in double quotation marks, or a number that represents the columns position
in that list. In this case I am going to use “height”, so I put double quotes, height.
Now the criteria it says is the range of cells that contain the conditions you specify. The
range includes a column label and one cell below the label for a condition. So in that
case I want to highlight this section over there and what we are saying is look at these
conditions and apply that to the database. So when I say OK you will see it tells me
2, which when you look here, here’s a apple tree but it is 8 so it does not meet the one
condition, here’s an apple 14, here’s an apple 18, so there are 2 trees that meet these conditions. Now that you have this formula set up, it
is quite convenient, because this at the moment looks a those conditions, but lets say I go
here, and change that to pear, when I click ENTER, you will see that that goes to one,
because there is only one tree that is a pear tree and is bigger than the 10. Similarly
we could go here, and change that to be bigger than 8, and that will change to 2. So you’ve
got a function that goes through the database, and checks to certain criteria that exists
in the spreadsheet and gives you the result. Now lets introduce another complexity. Lets
say we want to know how many pear trees are bigger then 8, and we want to know how many
apple trees are bigger then 10. In order to make this function work, we click on FX, we
will DCOUNT them, again we specify the database, the field in this case instead of typing,
“height” I can also say 2, which is the second column, and now for the criteria, I can highlight
all of these and say OK. Now what we will see is it gives me an answer of 4, and that’s
because there are 2 pear trees bigger then 8 and there are 2 apple trees bigger then
10. The trick to understanding what this is doing is that anything that is included along
the row is the equivalent of an AND function ad anything that is included down a column
is the equivalent of an OR function. So you will see here, we are saying it is a pear
tree AND it must be bigger then 8. If we go down, we are saying it can be a pear tree
OR an apple tree. So for example, we can actually introduce another condition. Lets say we wanted
it to be a pear tree bigger then 8 but less than 11. I can then come here, type the word
height and put the function less then 11. So now when I do the function you will see
I highlight the whole section and what it is going to do is look here a say it must
be pear AND bigger then 8 AND less then 11OR an apple that also bigger then 10 . So lets
just do that function, again the database, the field is number 2, the criteria, in this
case I am going to highlight the entire area and when I say OK you will see it gives me
3 now. And the reason it gives me 3 is because there is 1 pear tree that bigger then 8 but
less then 11 because this one does not match, and the apple bigger then 10 is 1, 2, hence
the answer is 3. So it is incredibly important to remember
that you can specify AND type criteria along the rows, and OR type criteria down the columns,
and you can duplicate the columns if there is more criteria you want to put in. We can now work though some of the other database
functions, and it will hopefully show you how this all works. Lets say we want to find
out which tree between apple and pear have the maximum profit. Click on a cell, activate
the function wizard, and go to the database functions, to the DMAX function. Again we
need to identify the database, specify the field in that case it will be profit so click
that and the criteria I want to use here is just pear and apple. So I’m going to just
highlight this section, so look either pear or apple. When I say OK, you will see it gives
me a number of 105 which is the maximum profit between pear and apple trees. Another useful function is the DSUM function.
What we want to know is what is the total profit for the pear trees. So I click on a
cell, activate the function wizard, find DSUM, say OK, show it where the database is, tell
it what field we are looking at, in this case it is the profit field, and specify the criteria
and in this case I just highlight these 2, and when I say OK it gives us 172.8, which
is the total of that plus that. There are other database functions s just
to give you an idea, we can have a quick look through here. They all use the same criteria,
whatever the function is you show where the database is, you specify the field, either
with a number or the name, and then you give the criteria in this sort of format. So using
DAVERAGE you can get an average of a database selection, you can count all the numbers,
you can count the non blanks, you can DGET particular information out of a database,
you can get the maximum number, the minimum number, you can get it to multiple all the
numbers it finds, you can get a standard deviation based on a sample or a standard deviation
based on the entire population. We have been through the DSUM, you can get a variance and
the variance based on a population. So using these functions you can extract all
sorts of useful information out of a database.

Daniel Ostrander

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *