Grouping

You can group the items in a field in any way you like. For example, NSW, QLD, and VIC (New South Wales, Queensland, and Victoria, respectively) could be grouped as EasternStates. This can be very useful when you have many items in a field. You can also group dates, which have a predefined group structure including years, quarters, and months.

If you bring the Date field from the source data into the PivotTable as a row field, as shown in Figure 7-9, you will have nearly 350 rows in the Table because there are that many unique dates.

HI Ebi-:i:n-

A

B

c _

D

E '

F

_G_H_

I

-i

1

Sum of NumberSold Column Labels *

2

Row Labels

- NSW

NT

OLD

SA TAS

VIC VJA

Grand Toca!

J

Jan 01 2006

903

331

1234

4

Jan 02, 2006

293

299

s

Jan OS 2006

612

612

6

Jan OS. 2006

107

907

1014

T

Jan 10. 2006

770

770

1

Jan 14, 2006

223

132

J55

9

Jan 15.2006

669

069

10

Jan 17. 2006

561

SB1

11

Jan 21. 2006

624

624

12

Jan 22. 2006

193

193

13

Jan 23. 2005

255

255

14

Jan 27. 2006

B

311

317

15

Jan 28. 2006

9

703

715

16

Jan 29. 2006

441

441

17

Jan 30.2006

936

936

16

Feb 03 2006

901

901

19

Feb 04 2006

531

631

20

Feb 06 2006

161

1B1

21

Feb 10, 2006

748

74S

22

Feb 11.2006

593

593

23

Feb 12 2006

B45

845

24

Feb 13 2006

742

742

25

Feb 17 2006

004

804

26

Feb 18, 2006

063

863

28

Feb 19 2006 Feb 21 2006

27

996

27 996

29

Feb 25, 2006

.217

203

423

3ii

Feb 26, 2006

274

771

1045

31

Feb 28 2006

523

523

32 [<

Mar 04 2006 ► H Sheet}

J

272

165

34

1371

H'l

You can group the Date items to get a more meaningful summary. You can do this manually by selecting a cell in the PivotTable containing a date item, right-clicking the cell, and clicking Group. The dialog box shown in Figure 7-10 appears, where you can select both Months and Years.

Grouping .SE^

Auto

0 staring at: ycuyaroe 0 Ending at: Jy01J2Q0a

5econds

Minutes

Hours

Days

Monthfi

Quarters i'ifars, \

Figure 7-10

When you click OK, you will see the result shown in Figure 7-11.

^ I t>r ot* ab1=L - '

_ ^

A

B

c

ifr

E

F

G

- SJ

1 B—1

1

Sum of NumberSold Column Labels -

2

Raw Labels

- :nsw

Nt

OLD

sa

tas

i/IC

wa

Grand Total

3

- 2906

4

Jan

1784

073

1779

973

1144

2120

936

9315-

e

Feb

1202

4094

845

206

1634

1341

9322

6

Mar

565

869

2204

567

456

1357

295

6313

7

Apr

1614

1085

679

1376

223

2326

1476

8778

6

May

2776

3133

2887

755

286

325

10162

9

Jm

1726

1477

£05

626

2442

263

7039

10

Jul

1020

1681

778

448

695

1120

i£ia

7358

11

Aug

2015

1236

1790

843

553

2371

8810

12

Sep

2361

1265

2212

347

1680

405

1397

9657

13

Oct

1322

1196

88

2038

1375

12151

97

7331

14

Nov

744

2252

641

2595

864

575

423

8034

15

Dec

1662

2454

2431

2519

528

1699

3533

14826

16

-2D07

17

Jart

1244

1631

672

5-188

1739

1261

780

10615

18

Feb

929

2219

£73

¿01

1020

813

42

5897

19

Mar

664

1717

1337

1442

729

1243

664

7795

20

Apr

2383

5M

548

1006

969

5490

51

May

1745

961

755

2376

2445

3953

12235

22

Jun

930

560

B66

220

1202

831

3051

7660

23

Jut

73

1762

3 564

1997

2064

1130

10690

2.4

Aus

751

885

1695

604

481

1556

107

6079

25

Sep

119

3402

495

3358

443:

1444

1801

11073

26

Oct

364

1262

1165

1506

642

135E

996

7309

27

N'.y

936

1009

2065

122

499

t596

920

7147

28

Dec

1504

884

1613

1532

2225

2120

3878

29

Grand Total

30423 35590 27255 32273 23055 32910 27769

208774

30

H

' " Sheets

M

The following code can be used to perform the same grouping operation:

Sub GroupDates()

Dim pvc As PivotCache Dim pvt As PivotTable Dim rng As Range

'Add New Worksheet Worksheets.Add Before:=Sheets(1)

'Get reference to existing PivotCache Set pvc = ActiveWorkbook.PivotCaches(l)

'Add PivotTable

Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc, _

TableDestination:=Range("A3"))

'Define fields in PivotTable With pvt

.PivotFields("Date").Orientation = xlRowField .PivotFields("State").Orientation = xlColumnField

.AddDataField .PivotFields("NumberSold"), "Sum of NumberSold", xlSum 'Locate the first Date

Set rng = .PivotFields("Date").DataRange.Cells(1,1)

'Group all Dates by Month & Year rng.Group Start:=True, End:=True, _

Periods:=Array(False, False, False, False, True, False, True)

End With End Sub

The grouping is carried out on the Range object underneath the labels for the field or its items. You need to select one of the labels containing an item name. If you choose a number of item names, you will group just those selected items.

GroupDates creates an object variable, rng, referring to the cell containing the first Date item. The Group method is applied to this cell, using the parameters that apply to dates. The Start and End parameters define the start date and end date to be included. When they are set to True, all dates are included. The Periods parameter array corresponds to the choices in the Grouping dialog box, selecting Months and Years.

The following code ungroups the dates:

Sub UnGroupDates()

Dim rng As Range

'Get reference to data

Set rng = ActiveSheet.PivotTables(1).PivotFields("Date";

i.DataRange

'Ungroup

rng.Ungroup

End Sub

Note that you have used the DataRange property of the PivotField object to locate the dates. The DataLabel property, which you could use to locate the dates when grouping them in previous versions of Excel, does not work in Excel 2007.

You can regroup them with the following code:

Sub ReGroupDates() Dim rng As Range

'Get reference to single cell in data

Set rng = ActiveSheet.PivotTables(1).PivotFields("Date").DataRange.Cells(1, 1)

'Group all dates by Month & Year rng.Group Start:=True, End:=True, _

Periods:=Array(False, False, False, False, True, False, True)

End Sub

The DataRange property has been used to refer to the items to be grouped. You can't refer to all the cells in the range, as mentioned previously, so the Cells property is used to refer to the first cell in the range.

Was this article helpful?

0 0

Post a comment