Table of Contents
Introduction xi
Who Should Read This Book xi
How This Book Is Organized xi
Part I: PivotTable Fundamentals
Chapter 1: Understanding PivotTables and Charts 3
Understanding How PivotTables Work 3
Working with PivotTables 5
Creating a PivotTable Report 6
Creating a PivotTable Report with Multiple Columns 10
Using the PivotTable and PivotChart Wizard 12
Creating a PivotChart 16
Chapter 2: Understanding Data Sources for PivotTables 21
Using Excel Data from the Same Workbook 22
Using Excel Data from Another Workbook 26
Using Data from Other Sources 27
Using Data from an Existing Data Connection 30
Creating a PivotTable Report from Data in an Access Database 31
Using Other External Data Sources 37
Using Multiple Consolidation Ranges 38
Letting Excel Create a Single-Page (Report Filter) Field 41
Creating Your Own Page (Report Filter) Fields 42
Creating a Single-Page Field PivotTable Report from Multiple Consolidation Ranges 46
Creating a Multiple Page-Field PivotTable Report from Multiple Consolidation Ranges 48
Basing a PivotTable on Another PivotTable Report 52
Chapter 3: Using PivotTable Tools and Formatting 53
Understanding the PivotTable Field List 53
Using the Field List 56
Setting Field List Options 58
Using Classic PivotTable Layout 60
Using the PivotTable Ribbons 65
Setting PivotTable Options 66
Formatting PivotTables 74
Applying PivotTable Styles 74
Creating Custom PivotTable Styles 75
Formatting Value Cells 77
Setting Report Layout 79
Changing Other Formatting 81
Applying Formatting to a PivotTable Report 82
Part II: PivotTables and Charts: Going Beyond the Basics
Chapter 4: Working with PivotTable Components 87
Using Report Areas 87
The Field List 88
Moving a Field to a Different Area 89
Removing a Field 89
Using the Row Labels Area 90
Using Multiple Row Fields 92
Moving Fields Within an Area 94
Using More Than Two Fields in the Row Area 94
Using the Column Labels Area 95
Creating a PivotTable with Two Column Fields and Two Row Fields 96
Filtering and Sorting a PivotTable on Row and Column Fields 102
Using the Value Area 104
Using Multiple Value Fields 104
Using the Report Filter Area 106
Creating a PivotTable with Three Report Filter Fields 109
Working with Field Settings 113
Understanding Settings for Value Fields 113
Using Different Summary Functions 115
Working with Settings for Row and Column Fields 120
Subtotal and Filter Options 121
Layout and Print Options 122
Working with Settings for Report Filter Fields 125
Setting Advanced Value Field Options 126
Using Value and Label Filters 129
Using a Value Filter and Custom Sort 134
Chapter 5: More About PivotTable Components 137
Working with Calculated Fields and Items 137
Calculated Fields 137
Creating and Using a Calculated Field 140
Working with Calculated Items 144
Creating and Using a Calculated Item 146
Showing and Hiding Detail 149
Viewing Detail for Value Items 149
Viewing Details for Field Items 150
Grouping PivotTable Items 153
Grouping Numeric Items 154
Grouping Dates 158
Grouping Other Items 160
Grouping Category Data 160
Chapter 6: Understanding and Using PivotCharts 165
Understanding PivotCharts 166
Creating a PivotChart 166
Creating a PivotChart from an Existing PivotTable 166
Creating a PivotChart from Scratch 167
Creating a PivotTable and PivotChart Together 169
Understanding the Parts of a Chart 172
Working with the PivotChart Ribbon 173
Understanding and Changing PivotChart Types 174
Understanding a PivotChart’s Structure 176
A Simple PivotChart 176
A PivotChart with Two Row Fields 178
A PivotChart with Two Column Fields 180
Creating a 3-D PivotChart 181
Using the PivotChart Filter Pane 186
Part III: Getting the Most out of PivotTables and Charts
Chapter 7: Using PivotTables with Multidimensional Data 191
Using Multidimensional Data 191
Understanding Multidimensional Data 192
Flat Data 193
Relational Data 193
Multidimensional Data 194
Where Are Multidimensional Data Stored? 201
Multidimensional Data Terminology 202
Creating a PivotTable from an Online Cube 203
Creating an Offline Cube File from an OLAP Server Database 207
Working with OLAP PivotTables 210
Chapter 8: Getting Hard Data from a PivotTable 211
Understanding the GETPIVOTDATA Function 211
GETPIVOTDATA Function Basics 212
A GetPivotData Shortcut 214
Referencing PivotTable Cells by Address 214
Page Fields and the GETPIVOTDATA Function 215
GETPIVOTDATA and OLAP Data 216
Using GETPIVOTDATA to Analyze PivotTable Data 217
Copying and Moving PivotTables 221
Chapter 9: PivotTable Alternatives 225
Working with Subtotals 225
Nesting Subtotals 229
Hiding and Showing Subtotal Detail 233
Working with Database Functions 234
Defining Criteria 235
Working with Filters 238
Chapter 10: Programming PivotTables with VBA 241
Understanding the PivotTable Object Model 242
Referencing and Creating PivotTables 244
Referencing an Existing PivotTable 244
Creating a New PivotTable in Code 245
Using the PivotTableWizard Method 246
Creating a New PivotTable Without the PivotTableWizard Method 249
Working with the PivotTable Object 251
Adding and Removing Row, Column, and Filter Fields 254
Adding and Removing Value Fields 255
Creating a PivotTable Using VBA Code 255
Working with PivotTable Fields 257
Creating and Changing Filters 258
Changing a Field’s Position 258
Creating Calculated Fields and Items 259
Hide and Show Field Items 260
Using AutoShow and AutoSort 260
Changing a Field’s Summary Calculation 261
Changing the Display Format of a Field 262
Creating a PivotChart in Code 264
Appendix A: Troubleshooting PivotTables and PivotCharts 267
Appendix B: Excel Version Differences for PivotTables 271
Appendix C: An Excel Chart Primer 273
Index 289
发帖
雷达卡
228050.pdf
(14.59 MB, 需要: 5 个论坛币)
加好友,备注cda
京公网安备 11010802022788号
论坛法律顾问：王进律师
知识产权保护声明
免责及隐私声明
