Description
Microsoft Excel Advanced Filtering
Excel’s Advanced Filter Tool
Complex Queries with the Advanced Filter
Objective
To illustrate how Excel’s advanced filter tool can be used to execute more complex “or” queries on the Catalogs database.
CATALOG.XLS
? The HyTex Company would now like to perform
more advanced queries on the data in this file by using Excel’s advanced filter tool. ? How might we proceed?
Excel’s Advanced Filter Tool
? Excel’s advanced filter tool can be used only after
creating a “criteria range”. ? The “criteria range” essentially spells out the query. It consists of a top row of field names and one or more rows of conditions. ? Each row of the conditions becomes an “or” part of the query.
Creating a Criteria Range
? We begin by copying the row of field names to
any unused are of the Data sheet. This row becomes the top row of the criteria range. ? Then we (manually) enter conditions in the cells just below those field names. ? The key is that the conditions in a given row are “and” conditions, whereas conditions across rows are treated in an “or” manner. ? An example appears on the following slide.
Creating a Criteria Range -continued
? The first row specifies that we want all customers
who are married and have salary at least $80,000 and have at least 2 children. ? The second row specifies that we want all customers who have salary $100,000 and received at least 12 catalogs.
Running the Query
? Once the criteria range is created, we run the
query by using the Data/Filter/Advanced Filter menu item. ? The associated dialog box is completed as follows.
Running the Query -- continued
? If we select the top option (Filter the list, in-place),
the query acts just like an autofilter query, the records that do not match are temporarily hidden from view. ? We prefer the second option, which places the query results in a separate output range and keeps the original database intact. ? We need only specify the top left cell of the output range, here cell W3.
Running the Query -- continued
? Also, if we check the box at the bottom, then any
customers who meet query conditions and are identical on all fields are listed only once in the output range. ? Here are some of the results.
Another Example
? Suppose we want the customers who are either
(1) male with salary between $40,000 and $50,000, or (2) female with salary over $70,000. ? How should this condition be entered in the criteria range? It is tempting to enter the label ”>40000,=(MEDIAN($F$4:$F$1003)+1000) under any field name such as HighSalary. ? Even better we can calculate the median salary in an unused cell, cell L8,say, and replace the formula above with the formula =F4>(=$L$8).
Computed Queries -- continued
? Note that the expression to the right of the first
equal sign is a condition. Therefore the result of either formula will be TRUE or FALSE. ? The setup is shown below. Cell L4 contains the formula =F4>(=$L$8) and cell L8 contains the formula =MEDIAN(F4:F1003)+1000 and the criteria range is L3:L4.
?
The resulting query returns 485 records with a salary greater than or equal to $54,700.
Computed Queries -- continued
? The rules for computed queries are rather
complicated, so we list relevant guidelines here.
? The column headings above a compound criterion
must not be the same as the field name in the database. This is why we used HighSalary, not Salary, in the criteria range. ? Reference to cells outside the database range should be absolute. This is why we put dollar signs around L8 in the formula in cell L4. ? Reference to cells inside the database range should be relative. This is why we made the leftmost F4 in the formulas relative.
Computed Queries -- continued
? Once your understand the logic these rules make
sense. ? As we saw in the setup the first salary, the one in cell F4, does not meet the criterion. This is why we see FALSE in cell L4. However when we run the query, Excel recognizes that the cell reference F4 in the formula is relative and thus substitutes each salary into the formula. ? We want it to compare each salary to an absolute value therefore the right side of the formula is absolute.
doc_624604031.ppt
Microsoft Excel Advanced Filtering
Excel’s Advanced Filter Tool
Complex Queries with the Advanced Filter
Objective
To illustrate how Excel’s advanced filter tool can be used to execute more complex “or” queries on the Catalogs database.
CATALOG.XLS
? The HyTex Company would now like to perform
more advanced queries on the data in this file by using Excel’s advanced filter tool. ? How might we proceed?
Excel’s Advanced Filter Tool
? Excel’s advanced filter tool can be used only after
creating a “criteria range”. ? The “criteria range” essentially spells out the query. It consists of a top row of field names and one or more rows of conditions. ? Each row of the conditions becomes an “or” part of the query.
Creating a Criteria Range
? We begin by copying the row of field names to
any unused are of the Data sheet. This row becomes the top row of the criteria range. ? Then we (manually) enter conditions in the cells just below those field names. ? The key is that the conditions in a given row are “and” conditions, whereas conditions across rows are treated in an “or” manner. ? An example appears on the following slide.
Creating a Criteria Range -continued
? The first row specifies that we want all customers
who are married and have salary at least $80,000 and have at least 2 children. ? The second row specifies that we want all customers who have salary $100,000 and received at least 12 catalogs.
Running the Query
? Once the criteria range is created, we run the
query by using the Data/Filter/Advanced Filter menu item. ? The associated dialog box is completed as follows.
Running the Query -- continued
? If we select the top option (Filter the list, in-place),
the query acts just like an autofilter query, the records that do not match are temporarily hidden from view. ? We prefer the second option, which places the query results in a separate output range and keeps the original database intact. ? We need only specify the top left cell of the output range, here cell W3.
Running the Query -- continued
? Also, if we check the box at the bottom, then any
customers who meet query conditions and are identical on all fields are listed only once in the output range. ? Here are some of the results.
Another Example
? Suppose we want the customers who are either
(1) male with salary between $40,000 and $50,000, or (2) female with salary over $70,000. ? How should this condition be entered in the criteria range? It is tempting to enter the label ”>40000,=(MEDIAN($F$4:$F$1003)+1000) under any field name such as HighSalary. ? Even better we can calculate the median salary in an unused cell, cell L8,say, and replace the formula above with the formula =F4>(=$L$8).
Computed Queries -- continued
? Note that the expression to the right of the first
equal sign is a condition. Therefore the result of either formula will be TRUE or FALSE. ? The setup is shown below. Cell L4 contains the formula =F4>(=$L$8) and cell L8 contains the formula =MEDIAN(F4:F1003)+1000 and the criteria range is L3:L4.
?
The resulting query returns 485 records with a salary greater than or equal to $54,700.
Computed Queries -- continued
? The rules for computed queries are rather
complicated, so we list relevant guidelines here.
? The column headings above a compound criterion
must not be the same as the field name in the database. This is why we used HighSalary, not Salary, in the criteria range. ? Reference to cells outside the database range should be absolute. This is why we put dollar signs around L8 in the formula in cell L4. ? Reference to cells inside the database range should be relative. This is why we made the leftmost F4 in the formulas relative.
Computed Queries -- continued
? Once your understand the logic these rules make
sense. ? As we saw in the setup the first salary, the one in cell F4, does not meet the criterion. This is why we see FALSE in cell L4. However when we run the query, Excel recognizes that the cell reference F4 in the formula is relative and thus substitutes each salary into the formula. ? We want it to compare each salary to an absolute value therefore the right side of the formula is absolute.
doc_624604031.ppt