Microsoft Office Tutorials and References
In Depth Information
20.2 The PivotTable Wizard
1998 2
NY-4
NEW YORK
Company
7285
$24,112.00
1998 2
NY-5
NEW YORK
Franchise
7749
$26,325.00
1998 2
NY-6
NEW YORK
Franchise
6881
$23,123.00
1998 3
NY-1
NEW YORK
Company
7256
$23,330.00
1998 3
NY-2
NEW YORK
Franchise
7330
$24,258.00
1998 3
NY-3
NEW YORK
Franchise
7212
$23,386.00
1998 3
NY-4
NEW YORK
Company
7480
$24,619.00
1998 3
NY-5
NEW YORK
Franchise
6771
$22,189.00
1998 3
NY-6
NEW YORK
Franchise
6954
$23,188.00
1998 4
NY-1
NEW YORK
Company
7086
$22,703.00
1998 4
NY-2
NEW YORK
Franchise
7275
$24,245.00
1998 4
NY-3
NEW YORK
Franchise
7121
$23,025.00
1998 4
NY-4
NEW YORK
Company
7562
$25,329.00
1998 4
NY-5
NEW YORK
Franchise
6569
$20,845.00
1998 4
NY-6
NEW YORK
Franchise
6973
$23,220.00
The Period column in Table 20-1 is the time period. For simplicity, we consider only four time
periods. The Store Code column gives the store code, used to uniquely identify a store. The Store
City gives the city in which the store is located. The Store Type column indicates whether the
store is owned by the company or is franchised. The Transactions column gives the number of
transactions for that time period. The Sales column gives the total sales for that store during that
period.
Note that there is one, and only one, row for each time period/store code. (In database language,
the time period/store code forms a key for the data.)
Our goal is to create a PivotTable from the data in Table 20-1 . Of course, before creating a
PivotTable, we need to identify the type of aggregate data in which we are interested. Clearly, we
want total sales and transaction counts. The question is: "Over what groupings?"
The best approach is first to identify the most refined (or smallest ) grouping for the aggregate data.
In this case, it is store type/store location/time period. For example, we want the total sales for all
company stores in New York during period 1.
In addition, we will want aggregates for larger groupingsā€”for example, total sales for all
company stores in New York over all periods and total sales for New York.
Finally, we want separate totals for the years 1998 and 1997.
20.2 The PivotTable Wizard
Let us first walk through the PivotTable wizard to create our PivotTable. Then we will create the
same PivotTable using code.
The first step is to select the source data and start the wizard by selecting PivotTable Report under
the Data menu. This will produce the first wizard dialog, as shown in Figure 20-1 . (These figures
are for Excel 97 and 2000. The Excel XP wizard has a somewhat different appearance.)
Figure 20-1. Step 1 in the PivotTable wizard
 
 
 
Search JabSto ::




Custom Search