Suchen und Finden
Excel 2007 PivotTables Recipes - A Problem-Solution Approach
von: Debra Dalgleish
Apress, 2008
ISBN: 9781430205043 , 272 Seiten
Format: PDF, OL
Kopierschutz: Wasserzeichen
Preis: 35,30 EUR
Contents at a Glance
4
Contents
5
About the Author
13
About the Technical Reviewer
14
Acknowledgments
15
Introduction
16
Creating a Pivot Table
19
1.1. Planning a Pivot Table: Getting Started
19
1.2. Planning a Shared Pivot Table
20
1.3. Preparing the Source Data: Using Excel Data
22
1.4. Preparing the Source Data: Creating an Excel Table
24
1.5. Preparing the Source Data: Excel Field Names Not Valid
26
1.6. Preparing the Source Data: Using Filtered Excel Data
26
1.7. Preparing the Source Data: Using an Excel Table with Monthly Columns
27
1.8. Preparing the Source Data: Using an Access Query
31
1.9. Preparing the Source Data: Using a Text File
32
1.10. Preparing the Source Data: Using an OLAP Cube
32
1.11. Creating the Pivot Table: Using Excel Data as the Source
33
1.12. Creating the Pivot Table: Using Excel Data on Separate Sheets
33
1.13. Creating the Pivot Table: Using the PivotTable Field List
36
1.14. Creating the Pivot Table: Changing the Field List Order
38
Sorting and Filtering Pivot Table Data
39
2.1. Sorting a Pivot Field: Sorting Row Labels
39
2.2. Sorting a Pivot Field: New Items Out of Order
41
2.3. Sorting a Pivot Field: Sorting Items Left to Right
42
2.4. Sorting a Pivot Field: Sorting Items in a Custom Order
43
2.5. Sorting a Pivot Field: Items Won’t Sort Correctly
45
2.6. Filtering a Pivot Field: Filtering Row Label Text
46
2.7. Filtering a Pivot Field: Applying Multiple Filters to a Field
47
2.8. Filtering a Pivot Field: Filtering Row Label Dates
49
2.9. Filtering a Pivot Field: Filtering Values for Row Fields
50
2.10. Filtering a Pivot Field: Filtering for Nonconsecutive Dates
51
2.11. Filtering a Pivot Field: Including New Items in a Manual Filter
52
2.12. Filtering a Pivot Field: Filtering by Selection
53
2.13. Filtering a Pivot Field: Filtering for Top Items
54
2.14. Using Report Filters: Hiding Report Filter Items
55
2.15. Using Report Filters: Filtering for a Date Range
56
2.16. Using Report Filters: Filtering for Future Dates
56
Calculations in a Pivot Table
58
3.1. Using Summary Functions: Defaulting to Sum or Count
58
3.2. Using Summary Functions: Counting Blank Cells
62
3.3. Using Custom Calculations: Difference From
63
3.4. Using Custom Calculations: % Of
65
3.5. Using Custom Calculations: % Difference From
66
3.6. Using Custom Calculations: Running Total
67
3.7. Using Custom Calculations:% of Row
69
3.8. Using Custom Calculations: % of Column
70
3.9. Using Custom Calculations: % of Total
71
3.10. Using Custom Calculations: Index
72
3.11. Using Formulas: Calculated Field vs. Calculated Item
73
3.12. Using Formulas: Adding Items With a Calculated Item
74
3.13. Using Formulas: Modifying a Calculated Item
75
3.14. Using Formulas: Removing a Calculated Item
76
3.15. Using Formulas: Using Index Numbers in a Calculated Item
76
3.16. Using Formulas: Modifying a Calculated Item Formula in Cell
77
3.17. Using Formulas: Creating a Calculated Field
78
3.18. Using Formulas: Modifying a Calculated Field
79
3.19. Using Formulas: Removing a Calculated Field
80
3.20. Using Formulas: Determining the Type of Formula
80
3.21. Using Formulas: Adding a Calculated Item to a Field with Grouped Items
81
3.22. Using Formulas: Calculating the Difference Between Amounts
81
3.23. Using Formulas: Correcting the Grand Total for a Calculated Field
82
3.24. Using Formulas: Calculated Field—Count of Unique Items
83
3.25. Using Formulas: Correcting Results in a Calculated Field
84
3.26. Using Formulas: Listing All Formulas
84
3.27. Using Formulas: Accidentally Creating a Calculated Item
84
3.28. Using Formulas: Solve Order
85
Formatting a Pivot Table
87
4.1. Using PivotTable Styles: Applying a Predefined Format
87
4.2. Using PivotTable Styles: Removing a PivotTable Style
89
4.3. Using PivotTable Styles: Changing the Default Style
90
4.4. Using PivotTable Styles: Creating a Custom Style
90
4.5. Using PivotTable Styles: Copying a Custom Style to a Different Workbook
92
4.6. Using Themes: Impacting PivotTable Styles
93
4.7. Using the Enable Selection Option
94
4.8. Losing Formatting When Refreshing the Pivot Table
95
4.9. Hiding Error Values on Worksheet
95
4.10. Showing Zero in Empty Values Cells
96
4.11. Hiding Buttons and Labels
97
4.12. Applying Conditional Formatting: Using a Color Scale
97
4.13. Applying Conditional Formatting: Using an Icon Set
98
4.14. Applying Conditional Formatting: Using Bottom 10 Items
100
4.15. Applying Conditional Formatting: Formatting Cells Between Two Values
101
4.16. Applying Conditional Formatting: Formatting Labels in a Date Period
102
4.17. Applying Conditional Formatting: Using Data Bars
103
4.18. Applying Conditional Formatting: Changing the Data Range
105
4.19. Applying Conditional Formatting: Changing the Order of Rules
107
4.20. Removing Conditional Formatting
108
4.21. Creating Custom Number Formats in the Source Data
108
4.22. Changing the Report Layout
109
4.23. Increasing the Row Labels Indentation
110
4.24. Repeating Row Labels
111
4.25. Separating Field Items with Blank Rows
112
4.26. Centering Field Labels Vertically
112
4.27. Changing Alignment for Merged Labels
113
4.28. Displaying Line Breaks in Pivot Table Cells
113
4.29. Freezing Heading Rows
114
4.30. Applying Number Formatting to Report Filter Fields
114
4.31. Displaying Hyperlinks
114
4.32. Changing Subtotal Label Text
115
4.33. Formatting Date Field Subtotal Labels
115
4.34. Changing the Grand Total Label Text
116
Grouping and Totaling Pivot Table Data
117
5.1. Grouping: Error Message When Grouping Dates
117
5.2. Grouping: Error Message When Grouping Numbers
118
5.3. Grouping the Items in a Report Filter
120
5.4. Grouping: Error Message About Calculated Items
121
5.5. Grouping Text Items
122
5.6. Grouping Dates by Month
123
5.7. Grouping Dates Using the Starting Date
123
5.8. Grouping Dates by Fiscal Quarter
124
5.9. Grouping Dates by Week
124
5.10. Grouping Dates by Months and Weeks
126
5.11. Grouping Dates in One Pivot Table Affects Another Pivot Table
126
5.12. Grouping Dates Outside the Range
128
5.13. Summarizing Formatted Dates
128
5.14. Creating Multiple Values for a Field
129
5.15. Displaying Multiple Value Fields Vertically
130
5.16. Displaying Subtotals at the Bottom of a Group
131
5.17. Preventing Subtotals from Appearing
132
5.18. Creating Multiple Subtotals
133
5.19. Showing Subtotals for Inner Row Labels
134
5.20. Simulating an Additional Grand Total
135
5.21. Hiding Specific Grand Totals
136
5.22. Totaling Hours in a Time Field
137
5.23. Displaying Hundredths of Seconds
137
Modifying a Pivot Table
138
6.1. Using Report Filters: Shifting Up When Adding Report Filters
138
6.2. Using Report Filters: Arranging Fields Horizontally
139
6.3. Using Values Fields: Changing Content in the Values Area
141
6.4. Using Values Fields: Renaming Fields
142
6.5. Using Values Fields: Arranging Vertically
142
6.6. Using Values Fields: Fixing Source Data Number Fields
143
6.7. Using Values Fields: Showing Text in the Values Area
143
6.8. Using Pivot Fields: Adding Comments to Pivot Table Cells
144
6.9. Using Pivot Fields: Collapsing Row Labels
145
6.10. Using Pivot Fields: Collapsing All Items in the Selected Field
146
6.11. Using Pivot Fields: Changing Field Names in the Source Data
147
6.12. Using Pivot Fields: Clearing Old Items from Filter Lists
147
6.13. Using Pivot Fields: Changing (Blank) Row and Column Labels
148
6.14. Using Pivot Items: Showing All Months for Grouped Dates
149
6.15. Using Pivot Items: Showing All Field Items
149
6.16. Using Pivot Items: Hiding Items with No Data
150
6.17. Using Pivot Items: Ignoring Trailing Spaces When Summarizing Data
151
6.18. Using a Pivot Table: Allowing Drag-and-Drop
152
6.19. Using a Pivot Table: Deleting the Entire Table
152
Updating a Pivot Table
154
7.1. Using Source Data: Locating the Source Excel Table
154
7.2. Using Source Data: Automatically Including New Data
156
7.3. Using Source Data: Automatically Including New Data in an External Data Range
158
7.4. Using Source Data: Moving the Source Excel Table
159
7.5. Using Source Data: Changing the Source Excel Table
160
7.6. Using Source Data: Locating the Source Access File
161
7.7. Using Source Data: Changing the Source Access File
161
7.8. Using Source Data: Changing the Source CSV File
162
7.9. Refreshing When a File Opens
164
7.10. Preventing a Refresh When a File Opens
164
7.11. Refreshing Every 30 Minutes
165
7.12. Refreshing All Pivot Tables in a Workbook
166
7.13. Stopping a Refresh in Progress
166
7.14. Creating an OLAP-Based Pivot Table Causes Client Safety Options Error Message
167
7.15. Refreshing a Pivot Table on a Protected Sheet
167
7.16. Refreshing When Two Tables Overlap
168
7.17. Refreshing Pivot Tables After Queries Have Been Executed
168
7.18. Refreshing Pivot Tables: Defer Layout Update
169
Pivot Table Security, Limits, and Performance
170
8.1. Security: Storing a Database Password
170
8.2. Security: Enabling Data Connections
171
8.3. Protection: Preventing Changes to a Pivot Table
172
8.4. Protection: Disabling Show Report Filter Pages
175
8.5. Privacy: Preventing Viewing of Others’Data
175
8.6. Understanding Limits: 16,384 Items in the Column Area
177
8.7. Understanding Limits: Number of Records in the Source Data
177
8.8. Improving Performance When Changing Layout
178
8.9. Reducing File Size: Excel Data Source
179
Printing and Extracting Pivot Table Data
181
9.1. Repeating Pivot Table Headings
181
9.2. Setting the Print Area to Fit the Pivot Table
184
9.3. Printing the Pivot Table for Each Report Filter Item
184
9.4. Printing Field Items: Starting Each Item on a New Page
186
9.5. Printing in Black and White
187
9.6. Extracting Underlying Data for a Value Cell
187
9.7. Re-creating the Source Data Table
188
9.8. Formatting the Extracted Data
189
9.9. Deleting Sheets Created by Extracted Data
190
9.10. Using GetPivotData: Automatically Inserting a Formula
190
9.11. Using GetPivotData: Turning Off Automatic Insertion of Formulas
192
9.12. Using GetPivotData: Referencing Pivot Tables in OtherWorkbooks
193
9.13. Using GetPivotData: Using Cell References Instead of Text Strings
193
9.14. Using GetPivotData: Using Cell References in an OLAP- Based Pivot Table
194
9.15. Using GetPivotData: Using Cell References for Value Fields
195
9.16. Using GetPivotData: Extracting Data for Blank Field Items
196
9.17. Using GetPivotData: Preventing Errors for Missing Items
196
9.18. Using GetPivotData: Preventing Errors for Custom Subtotals
197
9.19. Using GetPivotData: Preventing Errors for Date References
199
9.20. Using GetPivotData: Referring to a Pivot Table
200
9.21. Creating Customized Pivot Table Copies
201
Pivot Charts
203
10.1. Planning and Creating a Pivot Chart
203
10.2. Quickly Creating a Pivot Chart
206
10.3. Creating a Normal Chart from Pivot Table Data
208
10.4. Filtering the Pivot Chart
209
10.5. Changing the Series Order
211
10.6. Changing Pivot Chart Layout Affects Pivot Table
211
10.7. Changing Number Format in Pivot Table Affects Pivot Chart
212
10.8. Formatting the Data Table
212
10.9. Including Grand Totals in a Pivot Chart
212
10.10. Converting a Pivot Chart to a Static Chart
213
10.11. Showing Field Names on the Pivot Chart
213
10.12. Refreshing the Pivot Chart
215
10.13. Creating Multiple Series for Years
215
10.14. Locating the Source Pivot Table
216
10.15. Creating a Combination Pivot Chart
217
10.16. Moving a Pivot Chart from a Chart Sheet
217
10.17. Removing a Pivot Chart
218
Programming a Pivot Table
219
11.1. Using Sample Code
219
11.2. Recording a Macro While Printing a Pivot Table
222
11.3. Modifying Recorded Code
226
11.4. Changing the Summary Function for All Value Fields
227
11.5. Naming and Formatting the Show Details Sheet
228
11.6. Automatically Deleting Worksheets When Closing a Workbook
230
11.7. Changing the Report Filter Selection in Related Tables
232
11.8. Removing Filters in a Pivot Field
234
11.9. Changing Content in the Values Area
236
11.10. Identifying a Pivot Table’s Pivot Cache
237
11.11. Changing a Pivot Table’s Pivot Cache
238
11.12. Refreshing a Pivot Table on a Protected Sheet
239
11.13. Refreshing Automatically When Source Data Changes
240
11.14. Setting a Minimum Width for Data Bars
240
11.15. Preventing Selection of (All) in a Report Filter
241
11.16. Disabling Pivot Field Drop-Downs
242
11.17. Preventing Layout Changes in a Pivot Table
243
11.18. Resetting the Print Area to Include the Entire Pivot Table
245
11.19. Printing the Pivot Table for Each Report Filter Field
246
11.20. Scrolling Through Report Filter Items on a Pivot Chart
247
Index
251
Alle Preise verstehen sich inklusive der gesetzlichen MwSt.