 |
|
|
|
|
Download the associated Workbook for this lesson
Excel includes twelve of the Database functions and they are referred to as the Dfunctions. These functions are ideal for extracting and working with data that is in a database, list or table. They are also not used anywhere enough by most Excel users. We highly recommend learning as much as possible about these functions as they can be invaluable when you need to extract or work with specific data from a database list or table. The help within Excel gives a lot of information on Database Functions and is well worth reading. Although there are twelve of these functions you really only need to know how to use any one of them, as they are all very similar and all have the same syntax.
Dfunction(database,field,criteria) All arguments are mandatory.
database: is simply the range of cells that make up your database, list or table. It must include your headings!
field: is the column within the database, list or table (database) to use in the function. Field can be given as text or the column number of the column in the database, list or table (database) .
criteria: Is a range of cells that specifies the conditions that must be met by the cells within the database to be used in the particular Dfunction. The criteria cells must contain the column heading(s) of column(s) you wish to include in the particular Dfunction.
These functions can best be seen by real examples. We have attached a Workbook that shows various ways these can be used. Use these examples in conjunction with the help on Database Functions. Some of the examples also use the Validation feature in Excel which is found under Data>Validation.
We have found this feature very good when needing to extract data as we can use it to create a "pick from list" in our criteria cells.
Array formulas are a very powerful feature of Excel and as so they should only be used when really needed. Too many of them and your Workbook can slow down to a crawl. You will find that once you get the general gist of array formulas, the temptation will be there to use them often. We highly recommend resisting this urge for reasons already mentioned. In most cases the desired result can be obtained by one of the Dfunctions and are a far superior choice.
The Excel Help defines array formulas as:
Performs multiple calculations and then returns either a single result or multiple results. Array formulas act on one or more sets of values, known as array arguments. Each array argument must be rectangular, and each argument must have the same number of rows and/or the same number of columns as the other arguments. To return multiple results, the formula must be entered into multiple cells.
To enter an array formula, press CTRL+SHIFT+ENTER. Microsoft Excel encloses array formulas between braces ( { } ).
One other important note here is that, an Array formula CANNOT refer to an entire column.
There are a number of examples of array formulas on the www.ozgrid.com website under Array Formulas that explain simple ways these can be used. If you haven't read this yet then you really should. You will also know from using the Conditional Sum Wizard that it can aid you in writing simple Array formulas.
Let's use one of the examples from our website to explain how they work.
To get the total cost of male Spaniels sold:
=SUM(IF($A$2:$A$200="Male",IF($B$2:$B$200="Spaniel",$E$2:$E$200,0),0))
We can see by reading the formula we only want to sum the values that refer to male Spaniels sold. This is where our two IF functions come into play! We will explain this to you in hopefully simple terms.
When entered as an Array formula (Ctrl+Shift+Enter) IF($A$2:$A$200="Male" will return TRUE for all cells within the range "$A$2:$A$200" that contain the text "Male".
For each cell that does return TRUE ("Male") it will then look at the next TRUE part of the IF function, ie: IF($B$2:$B$200="Spaniel".
If it does not equal "Male" it returns FALSE and so returns the FALSE part of the IF function ie; 0 (zero).
Once it is in the TRUE part of the IF function, ie: IF($B$2:$B$200="Spaniel" it does the same, except this time it looks for "Spaniel".
If this is TRUE it moves in the TRUE part of this IF function ie; $E$2:$E$200 which is part of the SUM function. So it sums the cell on the same row as Male and Spaniel (TRUE and TRUE). Again if it does not equal "Spaniel" it moves onto the FALSE part of the IF function which is again 0 (zero) ie; "),0"
So what it actually does is loop through all the cells in the range "$A$2:$A$200 and $B$2:$B$200 and returns to the SUM function either 0 (zero) or the corresponding cell in $E$2:$E$200. This is why having too may Array formulas can slow down a workbook. Just imagine having 20 Array formulas each with three or more arguments and each argument is referencing 5000 cells in a range. That is (3*5000)*20 which is 300,000 cells Excel MUST loop through each cell each time the Worksheet recalculates.
The best way to apply array formulas in most cases it to get the desired result then copy and PasteSpecial as values only over the top of itself. The quickest way to do this for any cell that contains a formula is to
Select the cell containing the formula
Right click on any cell border and drag it across, up or down one cell.
Now drag back and release and select Copy Here as Values Only
So while Excel is generally known for its capabilities of manipulating numbers it is also very capable of working with tables of Text and/or Numbers. We have had a good overall look at being able to retrieve and work with data extracted from a database and/or table. Excel is not usually thought of as a database, but it is used as one in nearly every spreadsheet. However having said this Excel is not designed to be used solely as a database and can (but not always) run into limitations if amount of data becomes very large.
Again we cannot stress enough the huge benefits that will become apparent if you take the time to learn the Dfunctions in detail! As we have mention the help on these functions is very thorough and well worth the time to read.