About the Book
Sharpen your data analysis skills with tools
from the Microsoft Office suite.
Master the tools that transform the information
in spreadsheets, databases, and servers into faster, better business decisions.
Packed with expert insights and practical Your Turn exercises, this essential
guide shows how to use the data analysis capabilities in Microsoft®
Office applications to capture data, analyze trends, identify risks—and
seize opportunities. It covers everything from mastering the basics of
sorting, filtering, and formatting data to performing sophisticated what-if
scenarios, analyzing multidimensional data, and even creating automated
data-analysis solutions.
Discover how to turn data into results!
• Help numbers tell the story with compelling,
well-designed Excel charts, graphs, and reports
• Use PivotTable® and PivotChart®
dynamic views to detect patterns and evaluate trends
• Use Data Analyzer to perform detailed
analyses and present information in dynamic pie charts, bar graphs, and
other views
• Publish and analyze data over the Web
using Office Web Components
• Learn techniques to interpret—and exchange—XML
data
• Analyze large databases using the OLAP
features in Microsoft Excel
• Write code to automate data analysis
and reporting solutions
• Implement a database maintenance plan
that helps keep data accurate, accessible, and secure
Get tools and sample files on CD, including:
• Add-ins for Microsoft Access and Excel,
including Report Manager, XML Spreadsheet, and OLAP CubeCellValue
• Dozens of sample PivotTables and PivotCharts
to experiment with
• Office XP Web Component Toolpack
• Multimedia Data Analyzer tutorial
• Fully searchable eBook
• More code, databases, and extras to
put your data to work!
Related Books
Microsoft® Excel 2000
Step by Step
Running Microsoft® Excel 2000
Table of Contents
Foreword
ix
Acknowledgments
xi
Introduction
xiii
1 Making Sense of Data
1
The Types
of Data That You Can Analyze 2
Using
Data to Make Decisions 4
Developing
a Data Analysis Strategy 5
Recording Data 8
Troubleshooting Data Compatibility Issues 9
Understanding
Microsoft Data Analysis Software and Features 10
Data Storage 10
Microsoft Excel 12
Microsoft Access 13
Microsoft Office Web Components 13
Microsoft SQL Server 2000 14
Microsoft SQL Server 2000 Desktop Engine 15
Microsoft SQL Server 2000 Analysis Services 16
Microsoft Data Analyzer 16
Summary
17
2 Basic Data Analysis Techniques
19
Sorting
Data 20
Summarizing
Data 22
Trending
Data 23
Filtering
Data 24
Formatting
Data 25
Importing,
Exporting, and Querying Data 27
Importing Data 27
Exporting Data 27
Querying Data 28
Charting
Data 28
Pivoting
Data 30
Working
with Data Lists 31
Working
with Relational and Multidimensional Data 32
Working with Relational Data 33
Working with Multidimensional Data 35
Working
with XML Data 37
Elements and Attributes 37
Namespaces 38
Summary
39
3 Analyzing Data with Microsoft
Excel 41
Sorting
and Filtering Data 42
Sorting Data 42
Filtering Data 44
Using
Worksheet Functions 49
Applying
Conditional Formatting 54
Working
with Charts 57
Importing
External Data 61
Filtering Imported Data 62
Querying
External Data 63
Querying Data on the Web 67
Analyzing
Data in Lists 70
Using
the Analysis ToolPak 73
The Descriptive Statistics Tool 76
The Histogram Tool 78
The Moving Average Tool 81
The Rank and Percentile Tool 83
The Sampling Tool 84
Using
the Solver Add-In 89
Summary
93
4 Analyzing Data with PivotTable
and PivotChart Reports 95
Understanding
PivotTable and PivotChart Reports 96
Analyzing
Data with PivotTable Reports 105
Using the PivotTable Field List 110
Using the PivotTable Toolbar and Shortcut Menu 112
Field Settings 116
Filtering PivotTable Report Fields 118
Creating and Using Calculated Fields and Calculated Items 120
Analyzing
Data with PivotChart Reports 124
Summary
127
5 Analyzing Data with Microsoft
Access 129
Understanding
Relational and Nonrelational Data 130
Connecting
to External Data 132
Importing Data 133
Linking to External Data 136
Sorting
and Filtering Data 138
Simple Sorting 138
Filtering Data 139
Advanced Filter/Sort 144
Querying
Data 146
Creating
Reports 150
Analyzing
Data with PivotTable and PivotChart Views 153
Using
Access to Integrate with SQL Server Databases 155
Analyzing
Access Data over the Web 156
Summary
157
6 Analyzing Data with the
Office Web Components 159
Introducing
the Office Web Components 160
Creating
Office Web Components 161
Using
the Spreadsheet Component 162
Analyzing Data in the Spreadsheet Component 167
Office XP Spreadsheet Component Features 170
Using
the PivotTable Component 172
Analyzing PivotTable Component Data 175
Additional Office XP PivotTable Component Features 181
Using
the Chart Component 182
Analyzing Chart Component Data 185
Additional Office XP Chart Component Features 191
Summary
192
7 Introducing Online Analytical
Processing 193
The Case
for OLAP 194
What
Is OLAP? 195
Using
OLAP Data to Make Better Business Decisions 199
Understanding
Online Transaction Processing 201
Data
Storage Options 203
Using
Microsoft Office to Analyze OLAP Data 204
Summary
206
8 Analyzing OLAP Data with
Microsoft Excel 207
Connecting
to OLAP Data 208
Connecting to OLAP Data with Microsoft Excel 208
Connecting to OLAP Data with the Office Web Components 211
Working
with OLAP Data 214
Creating
Offline Cubes 218
Summary
224
9 Analyzing OLAP Data with
Microsoft Data Analyzer 225
Introducing
Microsoft Data Analyzer 226
Exploring the Data Analyzer User Interface 227
Connecting
to OLAP Data 232
Viewing
and Analyzing Data with Data Analyzer 235
Creating
Custom Measures 245
Making
Better Business Decisions 251
Using the BusinessCenter 252
Exporting Data Analyzer Views to Excel, PowerPoint, or the Web 254
Summary
258
10 Working with XML Data
in Excel and Access 259
The Case
for XML 260
Making
Sense of XML Data 261
Basic XML Terminology 262
Basic XML Rules 264
Analyzing
XML Data with Microsoft Excel 266
Understanding and Working with the XML Spreadsheet Flattener 266
Understanding and Working with the XML Spreadsheet Schema 270
Understanding and Working with XML Data in Excel Web Queries 275
Working
with XML Data with Microsoft Access 277
Summary
280
11 Extending Office Data
Analysis Features with Code 283
Understanding
Office Code: Macros and Procedures 284
Understanding
Office Code: Programmatic Object Models 289
The Visual Basic Editor 290
The Object Browser and Online Help 291
Working with the Application Object 294
Extending
the Data Analysis Features in Microsoft Excel 297
Sort, Filter, and Subtotal Lists of Records 297
Insert Standard Excel Worksheet Functions and Analysis ToolPak Functions
302
Conditionally Format Worksheet Cells 306
Create PivotTable Reports and PivotChart Reports 308
Opening and Saving XML Data in Excel 311
Programming
the Data Analysis Features in Microsoft Access 313
Extending
the Data Analysis Features in the Microsoft Office Web Components
315
Extending
the Data Analysis Features in Microsoft Data Analyzer 321
Summary
326
12 Maintaining Data Reporting
and Analysis Systems 329
Creating
and Implementing Database Maintenance Plans 330
Maintaining
SQL Server 2000 Databases 331
Documenting and Re-Creating SQL Server Databases 333
Backing Up and Restoring SQL Server Databases 334
Using the Database Maintenance Plan Wizard 335
Securing SQL Server Databases 336
Maintaining
SQL Server 2000 Desktop Engine Databases 337
Maintaining
Access Databases 337
Documenting Access Databases 337
Backing Up and Restoring Access Databases 338
Using the Table Analyzer Wizard 339
Using the Performance Analyzer 339
Compacting and Repairing Databases 339
Securing Access Databases 340
Maintaining
SQL Server 2000 Analysis Services Systems 341
Archiving and Restoring OLAP Cubes 341
Processing OLAP Cubes 341
Additional
Reading 343
Summary
343
A Data Analysis Quick Reference
345
Microsoft
Excel 345
Working
with OLAP Data in Excel 349
PivotTable
Reports and PivotChart Reports 351
Microsoft
Access 353
Microsoft
Office Web Components 356
Microsoft
Data Analyzer 358
B Additional Tools and
Resources 363
CD Extras
363
Data Analyzer 3.5 Tutorial 363
Sample PivotTable Reports 364
Microsoft Office XP Web Component Toolpack 364
Online
Data Analysis Resources 365
OLAP CubeCellValue for Microsoft Excel 2002 365
Access Links for Microsoft Excel 2002 365
Report Manager for Microsoft Excel 2002 366
Template Wizard with Data Tracking for Microsoft Excel 2002 367
XML Spreadsheet Converter for Microsoft Access 2002 367
INDEX 369 |
