Pivot Table Data Crunching - Jelen_ Bill.pdf - Pivot Table Data Crunching (3018) - superktos86

Contents

Glance

Introduction

Pivot Table Fundamentals

Creating a Basic Pivot Table

Customizing a Pivot Table

Grouping, Sorting, and Filtering Pivot Data

MrExcel

LIBRARY

Performing Calculations Within Pivot Tables

Using Pivot Charts and Other Visualizations

Analyzing Disparate Data Sources with Pivot Tables

Pivot Table

Data Crunching

Microsoft ® Excel ® 2010

Sharing Pivot Tables with Others

Working with and Analyzing OLAP Data

Mashing Up Data with PowerPivot

Enhancing Pivot Table Reports with Macros

Using VBA to Create Pivot Tables

Advanced Pivot Table Tips and Techniques

Dr. Jekyll and Mr. GetPivotData

Finding Pivot Table Commands on the Ribbon

Index

Bill Jelen

Michael Alexander

800 East 96th Street,

Indianapolis, Indiana 46240

USA

Pivot Table Data Crunching

Executive Editor

Greg Wiegand

Acquistions Editor

Loretta Yates

Development Editor

Sondra Scott

Technical Editor

Bob Umlas

Managing Editor

Sandra Schroeder

Project Editor

Seth Kerney

Copy Editor

Jovana San Nicolas-Shirley

Indexer

Tim Wright

Production

Jake McFarland

Cover Designer

Anne Jones

Book Designer

Anne Jones

stored in a retrieval system, or transmitted by any means, elec-

tronic, mechanical, photocopying, recording, or otherwise, within

of this book, the publisher and author assume no responsibility

for errors or omissions. Nor is any liability assumed for damages

resulting from the use of the information contained herein.

ISBN-10: 0-7897-4313-2

ISBN-13: 978-0-7897-4313-8

Printed in the United States of America

First Printing: September 2010

Trademarks

All terms mentioned in this book that are known to be trade-

marks or service marks have been appropriately capitalized. Que

Publishing cannot attest to the accuracy of this information. Use of

a term in this book should not be regarded as affecting the validity

of any trademark or service mark.

Warning and Disclaimer

Every effort has been made to make this book as complete and

as accurate as possible, but no warranty or fitness is implied. The

information provided is on an “as is” basis. The author and the

publisher shall have neither liability nor responsibility to any per-

son or entity with respect to any loss or damages arising from the

information contained in this book.

Bulk Sales

Que Publishing offers excellent discounts on this book when

ordered in quantity for bulk purchases or special sales. For more

information, please contact

U.S. Corporate and Government Sales

1-800-382-3419

corp s@p son chgroup com

For sales outside of the United States, please contact

International Sales

1-317-428-3341

in rn ion l@p son chgroup com

C o n t e n t s

I n t r o d u c t i o n .

........................................................................................... 1

Pivot Table Fundamentals........................................................................11

What Is a Pivot Table?................................................................................................................... 11

Why Should You Use a Pivot Table? . .............................................................................................12

When Should You Use a Pivot Table?............................................................................................ 13

Anatomy of a Pivot Table. ............................................................................................................ 14

Values Area. ........................................................................................................................... 14

Row Area . .............................................................................................................................. 15

Column Area . ......................................................................................................................... 15

Report Filter Area . ................................................................................................................. 16

Pivot Tables Behind the Scenes . .................................................................................................. 16

Limitations of Pivot Table Reports . .............................................................................................. 17

A Word About Compatibility. ................................................................................................. 18

Next Steps. ................................................................................................................................... 19

Creating a Basic Pivot Table .....................................................................21

Preparing Data for Pivot Table Reporting ..................................................................................... 21

Ensure Data Is in a Tabular Layout. ........................................................................................ 22

Avoid Storing Data in Section Headings . ............................................................................... 22

Avoid Repeating Groups as Columns . .................................................................................... 23

Eliminate Gaps and Blank Cells in the Data Source . ............................................................... 24

Apply Appropriate Type Formatting to Fields . ....................................................................... 24

Summary of Good Data Source Design . ................................................................................. 24

Creating a Basic Pivot Table . ........................................................................................................ 26

Adding Fields to the Report . ...................................................................................................29

Adding Layers to a Pivot Table ............................................................................................... 32

Rearranging a Pivot Table . .....................................................................................................32

Creating a Report Filter. ......................................................................................................... 34

Introducing Slicers . ...................................................................................................................... 35

Keeping Up with Changes in the Data Source. ....................................................................... 39

Changes Have Been Made to Existing Data Sources. .............................................................. 40

Data Source’s Range Has Expanded . ...................................................................................... 40

Sharing the Pivot Cache . .............................................................................................................. 40

Side Effects of Sharing a Pivot Cache ...................................................................................... 41

Saving Time with New Pivot Table Tools . .................................................................................... 42

Deferring Layout Updates. ......................................................................................................42

Starting Over with One Click . ..................................................................................................43

Relocating a Pivot Table . ....................................................................................................... 43

Next Steps. ................................................................................................................................... 44

Pivot Table Data Crunching: Microsoft Excel 2010

Customizing a Pivot Table ........................................................................45

Making Common Cosmetic Changes. ........................................................................................... 46

Applying a Table Style to Restore Gridlines ............................................................................ 47

Changing the Number Format to Add Thousands Separators................................................. 48

Replacing Blanks with Zeros ................................................................................................... 49

Changing a Field Name. ......................................................................................................... 50

Making Layout Changes. .............................................................................................................. 51

Using the New Compact Layout.............................................................................................. 52

Using the Outline Form Layout . ............................................................................................. 53

Using the Traditional Tabular Layout. .................................................................................... 54

Controlling Blank Lines, Grand Totals, Subtotals, and Other Settings..................................... 57

Customizing the Pivot Table Appearance with Styles and Themes . ............................................. 58

Customizing a Style . .............................................................................................................. 60

Choosing a Default Style for Future Pivot Tables .................................................................... 61

Modifying Styles with Document Themes . ............................................................................ 62

Changing Summary Calculations . .................................................................................................63

Understanding Why One Blank Cell Causes a Count ............................................................... 63

Using Functions Other Than Count or Sum . ........................................................................... 64

Adding and Removing Subtotals . .................................................................................................65

Suppress Subtotals When You Have Many Row Fields............................................................ 65

Adding Multiple Subtotals for One Field . ............................................................................... 66

Using Running Total, % of, Rank Options . ................................................................................... 66

Tracking YTD Numbers with Running Total In ........................................................................ 70

Tracking Percent of Running Total. ........................................................................................ 70

Tracking Percent of Parent Item . ........................................................................................... 71

Display Change from a Previous Field . ................................................................................... 72

Showing Rank . ...................................................................................................................... 72

Using % of to Compare One Line to Another Line. ................................................................. 74

Track Relative Importance with the Index Option .................................................................. 74

Next Steps. ................................................................................................................................... 78

Grouping, Sorting, and Filtering Pivot Data...............................................79

Grouping Pivot Fields. .................................................................................................................. 80

Grouping Date Fields . ............................................................................................................ 80

Including Years When Grouping by Months . ......................................................................... 81

Grouping Date Fields by Week. .............................................................................................. 82

Grouping Two Date Fields in One Report . .............................................................................. 83

Grouping Numeric Fields . .......................................................................................................85

Ungrouping . .......................................................................................................................... 85

Looking at the PivotTable Field List . ............................................................................................ 85

Docking and Undocking the PivotTable Field List . ................................................................. 86

Rearranging the PivotTable Field List . ................................................................................... 87

Rearranging the PivotTable Field Lis . .................................................................................... 87

Using the Areas Section Drop-Downs . ................................................................................... 88

Using the Fields Drop-Down ................................................................................................... 89

Pivot Table Data Crunching - Jelen_ Bill.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: