✓ Schweizer Online-Shop
Getting Great Results with Excel Pivot Tables, Powerquery and Powerpivot
Erhältlich:Nicht auf LagerBuch (Softcover): Fachbuch

Getting Great Results with Excel Pivot Tables, Powerquery and Powerpivot

von Thomas Fragale

Unsere Artikel-Nr.
997LVND

Beschreibung

Get more out of your data with step-by-step tutorials for the Excel features you need to know. Excel is still the most popular tool for organizing and analyzing data, and today's professionals are expected to have a high degree of fluency with it. Complex Excel tools like Pivot Tables, PowerQuery, and PowerPivot can help you manage and report on data the way you need to. Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot. offers a fresh look at how these tools can help you. Author and Microsoft Certified Trainer Thomas Fragale breaks down the topics into easy-to-use steps and screenshots, so you'll be able to put your advanced Excel skills into practice right away. Using Pivot Tables, PowerQuery, and PowerPivot, you can import, sort, transform, summarize, and present your data, all without having to be a programmer. This book takes the technical jargon out of using these features, so you can do your job more efficiently, bring value to your teams, and advance your career. The plain-English instructions inside will help anyone learn to get quick, meaningful results from your data, without having a degree in computing. Get easy-to-understand walkthroughs for analyzing data and creating dashboards in Microsoft Excel. Learn how to organize data in Excel and use advanced features to find patterns and insights. Summarize any kind of data faster and easier, leaving you more time for other tasks. Turn raw numbers into new knowledge, reports, and charts that tell coworkers and customers what they need to know. This book is great for anybody who has tons of raw data and needs to make sense of it. Managers, salespeople, finance professionals, marketers-along with anyone else who works with large amounts of data-will love this quick and easy guide to Pivot Tables, PowerQuery, and PowerPivot.

Spezifikationen

SpracheEnglisch
AutorThomas Fragale
Erscheinungsjahr2024
FormatBuch (Softcover)
Anzahl Seiten304

Inhaltsverzeichnis

Foreword xxi
 
Introduction xxiii
 
Chapter 1 Preparing the Data for an Excel Pivot Table 1
 
What Is Data? 2
 
What the Data Should Look Like 3
 
Types of Data That Can Be Used in Excel Pivot Tables 6
 
Using Excel Data 7
 
Importing Data from External Data Sources into Excel 9
 
Importing Data from a Text/CSV File 10
 
Importing Data from an Access Database 13
 
Importing Data from a Web Page 13
 
Connecting to an ODBC Database 16
 
Importing Data from a Different Excel Workbook 17
 
Refreshing the Data 18
 
Using Power Query to Clean the Data 20
 
Using the Queries & Connections Window 21
 
Analyzing the Table 23
 
Splitting a Column 24
 
Merging Columns 25
 
Changing Data Types 26
 
Removing Columns and Rows 26
 
Undoing Steps in Power Query 28
 
Replacing Values 29
 
Trimming Spaces from the Beginning and End of the Data in a Column 30
 
Combining Tables Using the Append Query 31
 
Combining Tables Using the Merge Query 32
 
Creating Subtotals in Your Data Using the GroupBy Query 34
 
Using Power Query to Create Calculations 36
 
Calculating Age/Years of Service 37
 
Using the Built-in Date Functions 38
 
Using the Built-In Time Functions 39
 
Using a Custom Column for Other Calculations 40
 
Calculating a Line Total 40
 
Calculating the Days to Ship 41
 
Calculating the New Total 41
 
Changing or Viewing an Existing Custom Column 43
 
Using a Conditional Column for Calculations 44
 
Calculating a Reorder 44
 
Changing or Viewing an Existing Conditional Column 45
 
Summary 46
 
Chapter 2 Summarizing and Presenting Data with a Pivot Table 47
 
What Is a Pivot Table? 47
 
Making a Pivot Table from Scratch 48
 
The PivotTable Fields Window 50
 
Summarizing Data on One Field 53
 
The Default Calculation in the Values Section 55
 
Showing the Detail with a Drill Down 55
 
Creating a Cross Tab or Cross Reference 56
 
Adding More Layers of Detail to the Pivot Table 57
 
Creating and Managing Groups 57
 
Changing the Grouping Order 58
 
Adding Groupings to Columns 58
 
Creating Your Own Group 58
 
Removing Your Own Group 59
 
Using Recommended PivotTables and Analyzing Data 60
 
Creating a Pivot Table Using Recommended PivotTables 60
 
Creating a Pivot Table Using Analyze Data 61
 
Making the Pivot Table Look Better 62
 
Formatting Numbers 63
 
Managing Subtotals 63
 
Calculating Grand Totals 64
 
Working with Blank Rows 65
 
Changing the Layout 65
 
Applying Styles 67
 
Replacing Spaces and Other Options 68
 
Using Conditional Formatting 71
 
Summary 72
 
Chapter 3 Using Calculations in Pivot Tables 73
 
Using Built-In Calculations in Your Pivot Table 73
 
Adding Calculation Fields to the Pivot Table 74
 
Changing the Calculation Type of a Field 75
 
Changing the Order of the Calculations 78
 
The Calculations in the Summarize Values By Tab 78
 
The Calculations in the Show Values As Tab 79
 
Removing Calculations from the Pivot Table 86
 
Delaying Calculations in a Pivot Table 86
 
Changing the Way Errors Display in the Pivot Table 87
 
Creating Custom Calculations 89
 
Calculated Fields 89
 
To Change or Delete a Calculated Field 92
 
Calculated Items 93
 
To Change or Delete a Calculated Item 95
 
The Solve Order 96
 
Documenting Your Calculated Items and Fields 98
 
Limitations of Calculated Fields and Items 99
 
Refreshing Values on the Pivot Table 100
 
Manual Refresh 101
 
Automatic Refresh 102
 
Changing the Data Source 103
 
Using Numbers from the Pivot Table in Other Calculations 105
 
Enabling the GETPIVOTDATA Function 105
 
Using the GETPIVOTDATA Function 106
 
Summary 107
 
Chapter 4 Sorting and Filtering the Pivot Table 109
 
Sorting the Pivot Table 110
 
Sorting by a Column 110
 
Sorting by a Row 112
 
Putting the Pivot Table in Ascending Order 113
 
Creating Your Own Sort Order 113
 
Creating Your Own Sort Order by Dragging Cells 113
 
Creating Your Own Sort Order by Using a Custom List 114
 
Sorting a Pivot Table with Multiple Fields 119
 
Filtering the Pivot Table 121
 
Filter by Selection 122
 
Clearing the Filter 123
 
Using AutoFilters 123
 
Label Filters 125
 
Value Filters 127
 
Top 10 Filters 129
 
Date Filters 131
 
Showing Items with No Data 132
 
Using the Filters Section 134
 
Creating New Sheets from the Field in the Filters Section 136
 
Using Slicers and Timelines 137
 
Creating a Timeline 140
 
Updating Multiple Pivot Tables Using Slicers and Timelines 142
 
Removing a Slicer or a Timeline Window 143
 
Summary 143
 
Chapter 5 Making the Pivot Table More Visual with Charts 145
 
Creating a Chart from a Pivot Table 146
 
PivotChart Features 148
 
The Design Tab and the Format Tab for a Chart 151
 
Managing and Modifying Chart Elements 153
 
The Chart Title 154
 
Data Labels 156
 
Changing the Number Format of the Data Labels 156
 
Changing the Data Labels to Display Vertically 158
 
Trendlines 161
 
Adding a Trendline to a Chart 161
 
Creating a Forecast from a Trendline 162
 
Formatting the Numbers of the Y-Axis or X-Axis 164
 
Creating and Managing a Pie Chart 166
 
Changing the Data Labels on the Pie Chart to Percents 166
 
Separating a Section from a Pie Chart 168
 
Changing the Pie Chart to a Doughnut Chart 168
 
Creating a Combo Chart 169
 
Creating and Using Chart Templates 171
 
Summary 174
 
Chapter 6 Summarizing Data by Date and Time 175
 
Summarizing a Pivot Table by the Built-in Date Periods 176
 
Summarizing a Pivot Table by a Single Period of Time 176
 
Summarizing a Pivot Table by More Than One Time Period 180
 
Showing/Hiding the Totals for Each Time Period 180
 
Expanding/Collapsing Time Periods in the Pivot Table 181
 
Using Slicers to Filter the Pivot Table by Date and Time 181
 
Displaying Earliest and Most Recent Dates 184
 
Summarizing the Pivot Table by Number of Days 186
 
Summarizing the Pivot Table Using Custom Date Calculations 188
 
Summarizing the Pivot Table by Week 189
 
Using the Day of the Week in a Pivot Table 191
 
Using a Fiscal Period in a Pivot Table 194
 
Summary 197
 
Chapter 7 Creating a Pivot Table from Multiple Spreadsheets 199
 
Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard 200
 
Creating a Pivot Table Using the Data Model 205
 
Adding, Changing, or Deleting Relationships 210
 
Adding Additional Tables into the Data Model 211
 
Creating and Managing Sets 213
 
Changing or Deleting a Set 216
 
Summary 217
 
Chapter 8 Improving a Pivot Table with Power Pivot 219
 
Activating the Power Pivot Add-In 220
 
Using the Power Pivot Add-In 222
 
Adding Tables to the Data Model 223
 
Adding Formatted Excel Tables into the Data Model 224
 
Adding Tables from Other Sources into the Data Model 224
 
Joining Tables 227
 
Changing, Deleting, and Managing the Relationships 229
 
Creating a Pivot Table from the Power Pivot for Excel Window 230
 
Adding Calculations to a Pivot Table Using the Data Model 232
 
Adding a Calculated Column to a Table in the Data Model 232
 
Adding a Measure into the Data Model 234
 
Creating a KPI 236
 
Summary 239
 
Chapter 9 Pulling It All Together: Creating a Dashboard from Pivot Tables 241
 
Looking at a Finished Dashboard 242
 
Creating Your Own Dashboard 243
 
Adding Charts to Your Dashboard 244
 
Adding Slicers and Timelines to Your Dashboard 245
 
Displaying Totals and Percent of Totals 247
 
Working with Form Controls, Macros, and VBA Code 248
 
The Developer Tab 249
 
Adding Command Buttons 250
 
Saving a Workbook with a Macro 253
 
Adding a Combo Box 255
 
Adding Finishing Touches to Your Dashboard 260
 
Hiding Sheets 261
 
Hiding the Pivot Table, Gridlines, Column Headings, and the Formula 262
 
Summary 264
 
Index 265

Bewertungen

Produkt bewerten

Für dieses Produkt liegt noch keine Bewertung vor. Wenn Sie es gekauft haben, teilen Sie gerne Ihre Erfahrung.

Zahlungsarten

payment-einzahlungsscheinpayment-twintpayment-mastercardpayment-visapost-finance-paypayment-apple-paypayment-google-paysamsung-pay

Rechnung (Einzahlungsschein mit QR-Code), Twint, Kreditkarte, PostFinance, Apple, Google oder Samsung Pay.

Versand

Kostenlos: Schweiz & Liechtenstein

Rückgabe

30 Tage Rückgaberecht

Sie können das Produkt innerhalb von 30 Wochentagen ohne Angabe von Gründen zurücksenden, sofern es unbeschädigt und in der Originalverpackung ist.

Weitere Hinweise finden Sie in unseren AGB unter D.1.