Microsoft Office Tutorials and References
In Depth Information
Chapter 20. Pivot Tables
Chapter 20. Pivot Tables
In this chapter, we take a look at pivot tables and how to create and format them using code.
20.1 Pivot Tables
While we are assuming that the reader is familiar with the basics of Excel, it probably would not
hurt to review the concept of a pivot table (or PivotTable) quickly.
PivotTables are one of the most powerful features in Excel. They are designed to accomplish three
main tasks:
Import external data
Aggregate data; for example, sum, count, or average the data
Display the data in interesting ways
PivotTables can use data from external sources, as well as from one or more Excel tables. For
instance, the data for a PivotTable can come from an Access database. However, setting up Excel
to import external data requires that the appropriate data source drivers be installed on the user's
computer. Moreover, there are significant limitations on Excel's ability to import data through
PivotTables. For instance, all strings are limited to a length of 255 characters, which makes using
SQL to define a data source much more difficult.
All in all, importing data using a PivotTable can be problematic. Furthermore, we always have the
option of importing the required data directly to an Excel worksheet (using a variety of more
sophisticated methods, such as DAO and the GetRows method) and then creating the PivotTable
from the worksheet. Accordingly, we will restrict our discussion to using Excel data as the
PivotTable source.
Table 20-1 , which represents sales from a fictitious fast food company that has both company and
franchise stores, shows the first half of the data that we will use to build our pivot table. The actual
source table is an Excel worksheet that contains twice the number of rows as Table 20-1 , the
additional rows being the analogous data for the year 1997. (Thus, the first column in the
remainder of the table contains the year 1997.)
Table 20-1. Source Data for PivotTable (for 1998)
Year Period
Store Code
Store City
Store Type
Transactions
Sales
1998 1
BO-1
BOSTON
Company
3881
$6,248.00
1998 1
BO-2
BOSTON
Company
3789
$5,722.00
1998 1
BO-3
BOSTON
Company
3877
$6,278.00
1998 1
BO-4
BOSTON
Company
3862
$6,123.00
1998 1
BO-5
BOSTON
Franchise
4013
$6,861.00
1998 1
BO-6
BOSTON
Franchise
3620
$5,039.00
1998 2
BO-1
BOSTON
Company
3948
$6,468.00
1998 2
BO-2
BOSTON
Company
3878
$6,301.00
1998 2
BO-3
BOSTON
Company
3911
$6,390.00
1998 2
BO-4
BOSTON
Company
3926
$6,438.00
1998 2
BO-5
BOSTON
Franchise
3990
$6,767.00
 
 
 
Search JabSto ::




Custom Search