Microsoft Office Tutorials and References
In Depth Information
Table 4.2 Auto sales example
Auto Sales Data—01/01/2005—01/31/2005
Rcd No.
Slsprn
Date
Make
Model
Amt paid
Rebates
Sales com
1
Bill
01/02/05
Ford
Wgn
24000
2500
2150
2
Henry
01/02/05
Toyota
Sdn
26500
1000
2550
3
Harriet
01/03/05
Audi
Sdn
34000
0
3400
4
Ahmad
01/06/05
Audi
Cpe
37000
0
5550
5
Ahmad
01/06/05
Ford
Sdn
17500
2000
2325
6
Henry
01/08/05
Toyota
Trk
24500
1500
2300
7
Lupe
01/10/05
Ford
Wgn
23000
2500
2050
8
Piego
01/12/05
Ford
Sdn
14500
500
1400
9
Kenji
01/13/05
Toyota
Trk
27000
1200
2580
10
Ahmad
01/14/05
Audi
Cpe
38000
0
5700
11
Kenji
01/16/05
Toyota
Trk
28500
1500
2700
12
Bill
01/16/05
Toyota
Sdn
23000
2000
2100
13
Kenji
01/18/05
Ford
Wgn
21500
1500
2000
14
Ahmad
01/19/05
Audi
Sdn
38000
0
5700
15
Bill
01/19/05
Ford
Wgn
23000
1000
2200
16
Kenji
01/21/05
Toyota
Trk
26500
1500
2500
17
Lupe
01/24/05
Ford
Sdn
13500
500
1300
18
Piego
01/25/05
Ford
Sdn
12500
500
1200
19
Bill
01/26/05
Toyota
Trk
22000
1000
2100
20
Ahmad
01/29/05
Audi
Cpe
36500
0
5475
21
Bill
01/31/05
Ford
Sdn
12500
500
1200
22
Piego
01/31/05
Ford
Sdn
13000
500
1250
4.4.2 Filtering Data
Now let’s take a look at the use of filters— Filter and Advanced Filter . Recall that
filtering differs from sorting in that it filters out records that do not match user pro-
vided criteria, while sorts rearrange records according to a key. Consider an Excel
database that contains an entire quarter’s sales transactions related to an auto deal-
ership’s sales force. The database documents the details of individual sales of autos
as they occur. Such data will likely include the name of the salesperson, the vehicle
sold, the amount paid for the vehicle, the commission earned by the salesperson, any
rebates or bonuses the buyer receives on the sale, the amount of time from first con-
tact with the customer until sale, etc. Table 4.2 shows an example of this database.
There are 22 records and 8 fields and many queries that we can perform on this
database.
4.4.3 Filter
Let us begin by using the Filter tool in the Data ribbon. As we have done before,
we must capture the range in the worksheet containing the database to be used in
the queries. In this case, we have a choice. We can either capture the entire range
 
Search JabSto ::




Custom Search