TABLE OF CONTENTS
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
Filter by Multiple Criteria ..................................................................................118
Subtotal Sorted Data..........................................................................................120
Count Filtered Records ......................................................................................122
Define Data as a Table ......................................................................................124
Modify a Table Style ..........................................................................................126
CHAPTER 7: WORKING WITH PIVOTTABLES . . . . . . . . .128
Create a PivotTable ............................................................................................128
Modify PivotTable Data and Layout ..................................................................132
Compute Subtotals and Grand Totals ................................................................134
Create a PivotTable Calculated Field ..................................................................136
Hide Rows or Columns in a PivotTable ..............................................................138
Sort a PivotTable................................................................................................139
Retrieve Values from a PivotTable ....................................................................140
CHAPTER 8: CHARTING DATA . . . . . . . . . . . . . . . . . . . . .142
Create a Chart ....................................................................................................142
Add Chart Details ..............................................................................................144
Change the Chart Type ......................................................................................148
Add a Trendline ................................................................................................150
Add and Remove Chart Data ............................................................................152
Add Error Bars ..................................................................................................154
Create a Histogram ............................................................................................156
Chart Filtered Data ............................................................................................158
Create a PivotChart ............................................................................................160
Create a Combination Chart ..............................................................................162
CHAPTER 9: WORKING WITH EXTERNAL DATA . . . . . .164
Paste Link into Word ........................................................................................164
Embed a Worksheet ..........................................................................................166
Hyperlink a Worksheet ......................................................................................168
Query a Web Site ..............................................................................................170
Import a Text File ..............................................................................................172
Import an Access Database................................................................................176
Query an Access Database ................................................................................178
AND TECHNIQUES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .182
Perform What-If Analysis ..................................................................................182
Optimize a Result with Goal Seek ......................................................................184
Solve a Formula with a Data Table ....................................................................186
Extend a Series with AutoFill ............................................................................188
Work with Multiple Windows ............................................................................190
Let Excel Read Back Your Data..........................................................................192
Add a Calculator ................................................................................................193
Change Text to Numbers....................................................................................194
Convert a Row to a Column ..............................................................................195
Consolidate Worksheets ....................................................................................196
Conditionally Format Your Worksheet ..............................................................198
Change Conditional Formatting Rules................................................................200
Paste with Paste Special ....................................................................................202
Insert Photographs into Your Worksheet ..........................................................206
CHAPTER 11: SHARING YOUR WORKBOOK
WITH OTHERS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .208
Validate with a Validation List ..........................................................................208
Validate with Data Entry Rules ..........................................................................210
Add Comments to Your Worksheet ....................................................................212
Track Changes ..................................................................................................214
Protect Your Worksheet ....................................................................................216
Save Your Workbook as a Template ..................................................................218
Choose a Format When Saving a Workbook......................................................220
Print Your Workbook ........................................................................................222
Print Multiple Areas of Your Worksheet ............................................................226
Add a Form Control to a Worksheet ..................................................................228
Assign Values to a Form Control ......................................................................230
Add a Macro to a Form Control..........................................................................232
CHAPTER 12: AUTOMATING WITH MACROS . . . . . . . . .234
Introducing Macros............................................................................................234
Set Macro Security ............................................................................................236
Create a Digital Signature ..................................................................................237
Record a Macro..................................................................................................238
Assign a Digital Signature to a Macro ..............................................................240
Run a Macro......................................................................................................242
Create and Launch a Keyboard Shortcut............................................................244
Assign a Macro to the Quick Access Toolbar ....................................................246
Delete a Macro ..................................................................................................248
APPENDIX A: EXCEL KEYBOARD SHORTCUTS . . . . . . . .250
APPENDIX B: EXCEL FUNCTION
QUICK REFERENCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .256
APPENDIX C: FORMULA BASICS . . . . . . . . . . . . . . . . . . .272
INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276
发帖
雷达卡
326154.pdf
(31.12 MB)
加好友,备注cda
京公网安备 11010802022788号
论坛法律顾问：王进律师
知识产权保护声明
免责及隐私声明
