The Pivot Table 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

Excel Vba Form Weights And Measures

Note that this dialog allows us to select the data source for the PivotTable data. Clicking the Next button produces the dialog in Figure 20-2.

Figure 20-2. Step 2 in the PivotTable wizard

Figure 20-2. Step 2 in the PivotTable wizard

Since we selected the correct source range before starting the wizard, Excel has correctly identified that range in Figure 20-2, so we can simply hit the Next button, which produces the dialog in Figure 20-3.

Figure 20-3. Step 3 in the PivotTable wizard

PivolToble Wiiorcf Step 3 of i a as

Cons-truE t yojr Sivstfable by ct"sggrg lis field buttons on ttie ncfit to ttis ciiajam on the t?Fi

2»UMM

ROW

DATA

[Sfrr? Pit stcre C'tv

Istcre T-jp

< BSCk [ »

Lrlstf

This dialog is where we format the PivotTable by deciding which columns of the original source table become pages in the PivotTable, which become rows, which become columns, and which become data (for aggregation). The procedure is to drag the buttons on the right to the proper location—row, column, page, or data. (We want one page for each of the two years.)

For our example, we drag the buttons to the locations shown in Figure 20-4. Note that the only button not used is Store Code. This field is the aggregate field, that is, we will sum over all store codes.

Figure 20-4. Step 4 in the PivotTable wizard

Figure 20-4. Step 4 in the PivotTable wizard

Excel Vba Form Weights And Measures

Clicking the Next button takes us to the dialog in Figure 20-5, where we choose the location for the PivotTable. We choose a new worksheet.

Figure 20-5. Step 5 in the PivotTable wizard

Figure 20-5. Step 5 in the PivotTable wizard

Clicking the Finish button produces the PivotTable in Figure 20-6.

Figure 20-6. The PivotTable

A

U

C

J

L

1

O

H

:

Ytjur

> -

3

Period I

4

Sxne C ty

Stc T>p<

Data

1

2

3

4

Grand Tola

BOSTON

'lofflpeny

Sum cr Transact) ore

2S24fi

267'4

2357 :

2S602

' I4236

n

Sum rf.ijtes

44R73

4«3?7

4C5?SR

46?; J

' B4O04

7

Franchise

rj Trans&rbnris

13033

1

13275

13210

54420

8

Sunn f Sales

218'5

21733

I $53 2

13325

3:512

U

U J JI ON burn or lrensactiois

422^1

426S.S

4- 34!

41312

'5:656

1C

BOSTON Sum ul Salsa

664 94

eseee

6'.B98

34548

234596

11

LOS ANO:

Ccnusny

Sum cf Transactions

sasse

35935

35392

35001

"41219

12

Sum c f Ssivi

12347S

12^564

126340

525416

504:500

13

frar-i l ue

iium cf Transitions

7 lSSi

729^7

75313

74.592

234541

1-1

Sum LrSdet

2674 21

770325 274531

1034561

LOS AM5ELES Sum of Transatfions

1C6171

inssss

11*311

' 3939:1

iSiTfiO

LOS ANGELES S.irm of Sal«

385903

597833

4053C5 33S919

153935-

17

NEW fOF

Company

Sum cf Tranc^hon:

248'5

26104

27315

26554

' 34580

IS

Sum crsaes

7WS3

8525 1

673C5

S3053

33S303

1?

Frin:hs=

Sum uf Transactions

?3273

5135 1

5JB22

51213

21C664

v:

:!-um c f Sates

176353

162461

170537

63C797

SI

NzW YORK Slit of ran:actcn:.

80455

7S637

78072

515253

22

NLW YORK i.Lir or

.-alii

26/.''2

253442 25M)U4

10J61U0

22

Tola SLrn of TTflrKatL m£

226301

231995

232DS5

229277

oi sees

Tola Si .m of

7C6SiH

7342J5

72S3S5 770001

Note that the page button is labeled Year. Selecting one of All, 1998, or 1997 from the drop-down list box next to this button will confine the data to that selection. Thus, the pivot table has three pages: 1997, 1998, and combined (or All).

Note also that the columns are labeled by periods and the rows are labeled by both Store City and Store Type, as requested. In addition, Excel has created a new field called Data that is used as row labels. In this case, Excel correctly guessed that we want sums, but if Excel had guessed incorrectly we could make a change manually.

In summary, we can see that the main components of a pivot table are the pages, rows, columns, and data fields.

Rather than pursue further development of this PivotTable using the Excel interface, let us now switch to using code.

0 0

Post a comment