目录

CHAPTER 1: GETTING STARTED . . . . . . . . . . . . . . . . . . . . .2
Introduction to Data Analysis with Excel ..............................................................2
Understanding the Excel Window..........................................................................3
Enter Data ............................................................................................................4
Format Numbers....................................................................................................6
Format Cells ........................................................................................................10
Select Data ..........................................................................................................14
Copy, Cut, and Paste Cells....................................................................................16
Copy with the Office Clipboard ............................................................................18
Insert and Delete Cells ........................................................................................20
Find and Replace ................................................................................................22
Find and Replace Formats ..................................................................................24
CHAPTER 2: CREATING FORMULAS . . . . . . . . . . . . . . . . .26
Understanding Formulas ....................................................................................26
Create Formulas ..................................................................................................28
Edit Formulas ......................................................................................................30
Name Cells and Ranges ......................................................................................32
Define and Display Constants ..............................................................................34
Create Formulas that Include Names....................................................................36
Check Formulas for Errors ..................................................................................38
Trace Precedents and Dependents ........................................................................40
CHAPTER 3: CREATING AND USING FUNCTIONS . . . . . .42
Understanding the Function Wizard ....................................................................42
Round a Number ................................................................................................44
Determine the Nth Largest Value ........................................................................46
Create a Conditional Formula ..............................................................................48
Calculate a Conditional Sum ................................................................................50
Calculate Products and Square Roots ..................................................................52
Look Up Information ..........................................................................................54
Determine the Location of a Value ......................................................................56
Perform Time Calculations ..................................................................................58
Perform Date Calculations....................................................................................60

CHAPTER 4: USING FINANCIAL FUNCTIONS . . . . . . . . . .62
Calculate Future Value ........................................................................................62
Calculate Present Value........................................................................................64
Calculate Loan Payments ....................................................................................66
Calculate Principal or Interest ..............................................................................68
Calculate the Interest Rate ..................................................................................70
Calculate the Internal Rate of Return ..................................................................72
Calculate Straight-Line Depreciation ....................................................................74
Calculate Declining Balance Depreciation ............................................................76
Calculate Double-Declining Balance Depreciation ................................................78
Calculate Sum-of-the-Years-Digits Depreciation ..................................................80
CHAPTER 5: USING STATISTICAL FUNCTIONS
AND TOOLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .82
Calculate an Average ..........................................................................................82
Calculate the Median or the Mode........................................................................84
Calculate Rank ....................................................................................................86
Calculate Frequency ............................................................................................88
Calculate Variance and Standard Deviation..........................................................90
Find the Correlation ............................................................................................92
Install Excel Add-Ins ..........................................................................................94
Calculate a Moving Average ................................................................................96
Compare Variances ..............................................................................................98
Using the Data Analysis Toolpak to Determine Rank and Percentile ................100
Calculate Descriptive Statistics ..........................................................................102
CHAPTER 6: ORGANIZING WORKSHEET DATA . . . . . . .104
Enter Data with a Form ....................................................................................104
Filter Duplicate Records ....................................................................................106
Perform Simple Sorts and Filters ......................................................................108
Perform Complex Sorts ......................................................................................110
Sort by Cell Color, Font Color, or Icon ................................................................112
Perform Complex Filters ....................................................................................114
Enter Criteria to Find Records............................................................................116