Bill Jelen, Michael Alexander

Excel 2016 Pivot Table Data Crunching

• Broschiertes Buch

Produktbeschreibung

A practical, realistic, easy-to-understand guide to Excel's most powerful and underutilized features: PivotTables and PivotCharts. Even for students who have never created a pivot table before, this book will help them leverage all their amazing flexibility and analytical power. Drawing on more than 40 combined years of Excel experience, Bill Jelen and Michael Alexander offer practical "recipes” for solving real business problems, help learners avoid common mistakes, and present tips and tricks found nowhere else.

Produktdetails

• Verlag: Pearson Education (US)
• Seitenzahl: 432
• Erscheinungstermin: 5. November 2015
• Englisch
• Abmessung: 231mm x 177mm x 27mm
• Gewicht: 692g
• ISBN-13: 9780789756299
• ISBN-10: 0789756293
• Artikelnr.: 42998086

Autorenporträt

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,900 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 49 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches, working as a financial analyst for the finance, marketing, accounting, and operations departments of a $500 million public company. He lives in Merritt Island, Florida, with his wife, Mary Ellen. Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years of experience consulting and developing Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares basic Access and Excel tips to the Office community.

Inhaltsangabe

>
Introduction
What You Will Learn from This Book
.................................................................1 What Is
New in Excel 2016's Pivot Tables
...............................................................2 Skills
Required to Use This Book
.........................................................................3
Invention of the Pivot
Table........................................................................4
Sample Files Used in This Book
...........................................................6 Conventions
Used in This Book
............................................................6 Referring to
Versions
..............................................................................7
Referring to Ribbon
Commands.................................................7 Special Elements
.....................................................................7
1 Pivot Table Fundamentals ............................................ 9
Defining a Pivot Table
.................................................................9 Why You
Should Use a Pivot Table .............................................10
Advantages of Using a Pivot Table
.........................................11 When to Use a Pivot Table
......................................................12 Anatomy of a Pivot
Table .........................................................12 Values
Area
...........................................................................12
Rows
Area........................................................................................13
Columns Area
...................................................................14
Filters Area
........................................................................14
Pivot Tables Behind the Scenes ..........................................14
Pivot Table Backward Compatibility
.................................................15 A Word About
Compatibility .................................................16 Next
Steps............................................................................17
2 Creating a Basic Pivot Table ........................................19
Preparing Data for Pivot Table Reporting
..............................................19 Ensuring That Data Is in a
Tabular Layout..........................................20 Avoiding Storing
Data in Section Headings ...................................20 Avoiding
Repeating Groups as Columns .............................................21
Eliminating Gaps and Blank Cells in the Data Source
...............................22 Applying Appropriate Type Formatting to
Fields .......................................22 Summary of Good Data
Source Design ........................................22 How to Create a
Basic Pivot Table ......................................................24
Adding Fields to a Report
.......................................................26 Fundamentals of
Laying Out a Pivot Table Report ........................................27
Adding Layers to a Pivot Table
...........................................................28 Rearranging a
Pivot Table ...........................................................29
Creating a Report
Filter.......................................................31
Understanding the Recommended Pivot Table
Feature...........................31 Using Slicers
......................................................................................33
Creating a Standard Slicer
............................................................33 Creating a
Timeline Slicer
...............................................................36 Keeping
Up with Changes in the Data Source
.............................................39 Dealing with Changes Made
to the Existing Data Source ............................39 Dealing with an
Expanded Data Source Range Due to the Addition of Rows or Columns
..............39 Sharing the Pivot Cache
.........................................40 Saving Time with New Pivot
Table Tools ............................................41 Deferring Layout
Updates ......................................................41 Starting
Over with One Click ..................................................42
Relocating a Pivot Table
.................................................43 Next
Steps.......................................................................43
3 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
.....................................................51 Making Report
Layout Changes ..........................................52 Using the
Compact Layout .............................................52 Using the
Outline Layout ...................................................54 Using
the Traditional Tabular Layout
...........................................55 Controlling Blank Lines,
Grand Totals, and Other Settings ....................................57
Customizing a Pivot Table's Appearance with Styles and Themes
......................................60 Customizing a Style
.................................................................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 ...............................65 Adding
and Removing Subtotals
....................................................65 Suppressing
Subtotals with Many Row Fields
...........................................66 Adding Multiple Subtotals for
One Field .......................................67 Changing the
Calculation in a Value Field .......................................67
Showing Percentage of Total
.........................................................70 Using % Of to
Compare One Line to Another Line ......................................71
Showing Rank
..........................................................................71
Tracking Running Total and Percentage of Running Total
.............................72 Displaying a Change from a Previous Field
..........................................................73 Tracking the
Percentage of a Parent Item
..............................................73 Tracking Relative
Importance with the Index Option ...................................74 Next
Steps...............................................................75
4 Grouping, Sorting, and Filtering Pivot Data ..........................77
Automatically Grouping Dates
...................................................77 Undoing Automatic
Grouping ...........................................78 Understanding How
Excel 2016 Decides What to Group ...................................78
Grouping Date Fields Manually
....................................................79 Including Years When
Grouping by Months ....................................80 Grouping Date
Fields by Week ...............................................81 Grouping
Numeric Fields ............................................82 Using the
PivotTable Fields List ..............................................85
Docking and Undocking the PivotTable Fields List
................................87 Rearranging the PivotTable Fields
List...................................87 Using the Areas Section
Drop-Downs ..................................88 Sorting in a Pivot Table
................................................89 Sorting Customers into
High-to-Low Sequence Based on Revenue ..................89 Using a Manual
Sort Sequence ..............................................92 Using a
Custom List for Sorting
..................................................93 Filtering a Pivot
Table: An Overview ...................................................95
Using Filters for Row and Column Fields
.........................................96 Filtering Using the Check Boxes
..................................................96 Filtering Using the
Search Box ......................................................97
Filtering Using the Label Filters Option
......................................98 Filtering a Label Column Using
Information in a Values Column ..........................99 Creating a
Top-Five Report Using the Top 10 Filter
...........................................101 Filtering Using the Date
Filters in the Label Drop-down .........................................103
Filtering Using the Filters Area ................................104 Adding
Fields to the Filters Area .............................................104
Choosing One Item from a Filter
...................................................105 Choosing Multiple
Items from a Filter ....................................................105
Replicating a Pivot Table Report for Each Item in a Filter
............................................105 Filtering Using Slicers and
Timelines .........................................107 Using Timelines to
Filter by Date .....................................................109
Driving Multiple Pivot Tables from One Set of Slicers
.......................................110 Next
Steps......................................................................................112
5 Performing Calculations in Pivot Tables ...........................113
Introducing Calculated Fields and Calculated Items
............................................113 Method 1: Manually Add a
Calculated Field to the Data Source ..........................114 Method 2:
Use a Formula Outside a Pivot Table to Create a Calculated Field
.............................115 Method 3: Insert a Calculated Field
Directly into a Pivot Table ............................116 Creating a
Calculated Field ...................................116 Creating a
Calculated Item ...........................................124
Understanding the Rules and Shortcomings of Pivot Table Calculations
..................127 Remembering the Order of Operator Precedence
......................................128 Using Cell References and Named
Ranges ...........................................129 Using Worksheet
Functions
....................................................................129
Using Constants
...........................................................................129
Referencing Totals
..........................................................................129
Rules Specific to Calculated Fields
......................................................129 Rules Specific to
Calculated Items
...........................................................131 Managing and
Maintaining Pivot Table Calculations
......................................131 Editing and Deleting Pivot Table
Calculations ......................................131 Changing the Solve
Order of Calculated Items .........................................132
Documenting Formulas
........................................................133 Next
Steps............................................................................134
6 Using Pivot Charts and Other Visualizations .........................135
What Is a Pivot Chart...Really?
..........................................................135 Creating a
Pivot Chart ..........................................................136
Understanding Pivot Field Buttons
..............................................138 Keeping Pivot Chart Rules
in Mind ....................................................139 Changes in
the Underlying Pivot Table Affect a Pivot Chart
.................................139 Placement of Data Fields in a Pivot
Table Might Not Be Best Suited for a Pivot Chart .....................139 A
Few Formatting Limitations Still Exist in Excel 2016
..................................141 Examining Alternatives to Using Pivot
Charts .......................................................145 Method 1:
Turn the Pivot Table into Hard Values
...................................145 Method 2: Delete the Underlying
Pivot Table ..........................................146 Method 3:
Distribute a Picture of the Pivot Chart
....................................146 Method 4: Use Cells Linked Back to
the Pivot Table as the Source Data for the Chart .............147 Using
Conditional Formatting with Pivot Tables
...............................................149 An Example of Using
Conditional Formatting ...........................................149
Preprogrammed Scenarios for Condition Levels
........................................151 Creating Custom Conditional
Formatting Rules .............................................152 Next
Steps...................................................................................................156
7 Analyzing Disparate Data Sources with Pivot Tables
................................157 Using the Internal Data Model
..................................................158 Building Out Your
First Data Model
............................................................158 Managing
Relationships in the Data Model ......................................162
Adding a New Table to the Data Model
.................................................163 Removing a Table from
the Data Model ...................................................165
Creating a New Pivot Table Using the Data Model
........................................166 Limitations of the Internal
Data Model
...............................................................167 Building
a Pivot Table Using External Data Sources
.............................................168 Building a Pivot Table
with Microsoft Access
Data....................................................169 Building a
Pivot Table with SQL Server Data
......................................................171 Leveraging Power
Query to Extract and Transform Data
.............................................174 Power Query
Basics..................................................................175
Understanding Query Steps
..........................................................181 Refreshing
Power Query Data ....................................................183
Managing Existing Queries
................................................................183
Understanding Column-Level Actions
............................................185 Understanding Table Actions
..........................................................187 Power Query
Connection Types ......................................................188
Next
Steps..........................................................................................192
8 Sharing Pivot Tables with Others
........................................193 Designing a Workbook as an
Interactive Web Page
.......................................................193 Sharing a Link
to a Web Workbook
................................................................196 Sharing
with Power BI
...............................................................................196
Preparing Data for Power BI
...........................................................197 Importing
Data to Power BI
.........................................................................197
Building a Report in Power BI
...................................................199 Using Q&A to Query
Data ........................................................200 Sharing
Your Dashboard
..............................................................202 Next
Steps........................................................................202
9 Working with and Analyzing OLAP Data ..........................203
Introduction to OLAP
.............................................................................203
Connecting to an OLAP Cube
.....................................................204 Understanding the
Structure of an OLAP Cube
...................................................207 Understanding the
Limitations of OLAP Pivot Tables
.........................................208 Creating an Offline Cube
...................................................209 Breaking Out of the
Pivot Table Mold with Cube Functions ................................211
Exploring Cube Functions
............................................................212 Adding
Calculations to OLAP Pivot Tables
.....................................................213 Creating
Calculated Measures
............................................................214 Creating
Calculated Members ..................................................217
Managing OLAP Calculations
............................................................220 Performing
What-If Analysis with OLAP Data .....................................220
Next
Steps...............................................................................222
10 Mashing Up Data with Power Pivot ...................................223
Understanding the Benefits and Drawbacks of Power Pivot and the Data Model
............223 Merging Data from Multiple Tables Without Using VLOOKUP
..................................223 Importing 100 Million Rows into a
Workbook .....................................................224 Creating
Better Calculations Using the DAX Formula Language
...........................224 Other Benefits of the Power Pivot Data Model
in All Editions of Excel .........................224 Benefits of the Full
Power Pivot Add-in with Excel Pro Plus
...................................225 Understanding the Limitations of the
Data Model .................................225 Joining Multiple Tables
Using the Data Model in Regular Excel 2016
..............................226 Preparing Data for Use in the Data Model
..................................227 Adding the First Table to the Data
Model ...................................................228 Adding the
Second Table and Defining a Relationship
......................................229 Tell Me Again-Why Is This Better
Than Doing a VLOOKUP? ..............................230 Creating a New
Pivot Table from an Existing Data Model
....................................232 Getting a Distinct Count
...........................................................232 Using the
Power Pivot Add-in Excel 2016 Pro Plus
.....................................234 Enabling Power Pivot
....................................................................234
Importing a Text File Using Power Query
............................................235 Adding Excel Data by
Linking
..................................................................236
Defining Relationships
...........................................................................236
Adding Calculated Columns Using DAX
......................................................237 Building a Pivot
Table
....................................................................237
Understanding Differences Between Power Pivot and Regular Pivot Tables
............................238 Using DAX Calculations
.............................................................239 Using DAX
Calculations for Calculated Columns
................................................239 Using DAX to Create a
Calculated Field in a Pivot Table ...........................240 Filtering
with DAX Calculated Fields
...............................................240 Defining a DAX
Calculated Field ..................................................240
Using Time Intelligence
.............................................................242 Next
Steps.....................................................................................243
11 Dashboarding with Power View and 3D Map ..........................245
Preparing Data for Power View
....................................................245 Creating a Power
View Dashboard
.............................................................247 Every New
Dashboard Element Starts as a Table
................................................249 Subtlety Should Be
Power View's Middle Name .........................................249
Converting a Table to a Chart
.............................................................250 Adding
Drill-down to a Chart ........................................251 Beginning
a New Element by Dragging a Field to a Blank Spot on the Canvas
..............252
Filtering One Chart with Another One
...............................................252 Adding a Real Slicer
..............................................................................253
Understanding the Filters Pane
..................................................................254 Using
Tile Boxes to Filter a Chart or a Group of Charts
.....................................255 Replicating Charts Using Multiples
.................................................256 Showing Data on a
Map......................................................257 Using Images
..........................................................................258
Changing a Calculation
..........................................................................259
Animating a Scatter Chart over Time
.............................................259 Some Closing Tips on Power
View ......................................................261 Analyzing
Geographic Data with 3D Map
................................................261 Preparing Data for 3D
Map ............................................261 Geocoding Data
.........................................................................262
Building a Column Chart in 3D Map
...............................................264 Navigating Through the
Map.......................................................264 Labeling
Individual Points
....................................................................266
Building Pie or Bubble Charts on a Map............................266 Using
Heat Maps and Region Maps ........................................266
Exploring 3D Map Settings
............................................................267 Fine-Tuning
3D Map
.....................................................................268
Animating Data over Time
........................................................269 Building a Tour
................................................................................270
Creating a Video from 3D
Map...................................................271 Next
Steps.........................................................................274
12 Enhancing Pivot Table Reports with Macros ................275 Why Use
Macros with Pivot Table Reports ................................275
Recording a Macro
..................................................................276
Creating a User Interface with Form Controls
.................................278 Altering a Recorded Macro to Add
Functionality.................................280 Inserting a Scrollbar
Form Control ............................................281 Next
Steps......................................................288
13 Using VBA to Create Pivot Tables.................................289
Enabling VBA in Your Copy of Excel .....................................289
Using a File Format That Enables Macros
..................................290 Visual Basic Editor
.................................................................291 Visual
Basic Tools .........................................................291
The Macro Recorder
..................................................................292
Understanding Object-Oriented Code
................................................292 Learning Tricks of the
Trade ........................................................293 Writing
Code to Handle a Data Range of Any Size ..............................293
Using Super-Variables: Object Variables
.................................................294 Using With and End
With to Shorten Code ................................................295
Understanding Versions
.....................................................................295
Building a Pivot Table in Excel VBA
......................................296 Adding Fields to the Data Area
..............................................................298
Formatting the Pivot Table
..........................................................299 Dealing with
Limitations of Pivot Tables
.................................................301 Filling Blank Cells in
the Data Area ....................................................301
Filling Blank Cells in the Row Area
............................................302 Preventing Errors from
Inserting or Deleting Cells ...............................302 Controlling
Totals ................................................................302
Converting a Pivot Table to Values
.................................................304 Pivot Table 201:
Creating a Report Showing Revenue by Category ..............307 Ensuring
That Tabular Layout Is Utilized....................................309
Rolling Daily Dates Up to Years ........................................309
Eliminating Blank Cells
.............................................................311
Controlling the Sort Order with AutoSort
.........................................312 Changing the Default Number
Format ................................................312 Suppressing
Subtotals for Multiple Row Fields ...............................313
Handling Final Formatting
.............................................................................315
Adding Subtotals to Get Page Breaks
..............................................315 Putting It All Together
.........................................................317 Calculating
with a Pivot Table
.............................................................................319
Addressing Issues with Two or More Data Fields
..................................319 Using Calculations Other Than Sum
............................................................321 Using
Calculated Data Fields
......................................................323 Using Calculated
Items .................................................................324
Calculating Groups
..........................................................................326
Using Show Values As to Perform Other Calculations
...................................327 Using Advanced Pivot Table
Techniques .......................................329 Using AutoShow to
Produce Executive Overviews .........................................329
Using ShowDetail to Filter a Recordset
..............................................332 Creating Reports for Each
Region or Model ................................................334
Manually Filtering Two or More Items in a Pivot Field
.....................................338 Using the Conceptual Filters
.................................................339 Using the Search
Filter
.....................................................................342
Setting Up Slicers to Filter a Pivot Table
.............................................343 Using the Data Model in
Excel 2016 ...................................................345 Adding