下载：用Excel进行科学数据分析---高级教程（英文）-经管之家官网！

Robert de Levie, Advanced Excel for Scientific Data Analysis,
Oxford University Press, 638pages, 2004. ISBN: 019517089X | PDF | 27,3 MB
Excel is by far the most widely distributed data analysis software but few users are aware of its full powers. Advanced Excel For Scientific Data Analysis takes off from where most books dealing with scientific applications of excel end. It focuses on three areas-least squares, Fourier transformation, and digital simulation-and illustrates these with extensive examples, often taken from the literature. It also includes and describes a number of sample macros and functions to facilitate common data analysis tasks. These macros and functions are provided in uncompiled, computer-readable, easily modifiable form; readers can therefore use them as starting points for making their own personalized data analysis tools. Detailed descriptions and sample applications of standard and specialized uses of least squares for fitting data to a variety of functions, including resolving multi-component spectra; standard processes such as calibration curves and extrapolation; custom macros for general "error" propagation, standard deviations of Solver results, weighted or equidistant least squares, Gram-Schmidt orthogonalization, Fourier transformation, convolution and deconvolution, time-frequency analysis, and data mapping. There are also worked examples showing how to use centering, the covariance matrix, imprecision contours, and Wiener filtering and custom functions for bisections, Lagrange interpolation, Euler and Runge-Kutta integration.
CONTENTS
CHAPTER 1 WRITING FORMULAS 25
1.1 The Basics Of Writing Formulae 26
1.2 Tool for using this chapter effectively: Viewing the formula instead of the end
result 26
1.2.a The “A1” vs. the “R1C1“ style of cell references 28
1.2.b Writing a simple formula that references cells 29
1.3 Types Of References Allowed In A Formula 30
1.3.a Referencing cells from another worksheet 30
1.3.b Referencing a block of cells 30
1.3.c Referencing non–adjacent cells 31
1.3.d Referencing entire rows 32
1.3.e Referencing entire columns 32
1.3.f Referencing corresponding blocks of cells/rows/columns from a set of
worksheets 33
CHAPTER 2 COPYING/CUTTING AND PASTING FORMULAE 35
2.1 Copying And Pasting A Formula To Other Cells In The Same Column 36
2.2 Copying And Pasting A Formula To Other Cells In The Same Row 37
2.3 Copying And Pasting A Formula To Other Cells In A Different Row And Column
38
2.4 Controlling Cell Reference Behavior When Copying And Pasting Formulae (Use
Of The “$” Key) 39
2.4.a Using the “$” sign in different permutations and computations in a
formula 41
2.5 Copying And Pasting Formulas From One Worksheet To Another 42
2.6 Pasting One Formula To Many Cells, Columns, Rows 43
2.7 Pasting Several Formulas To A Symmetric But Larger Range 43
2.8 Defining And Referencing A “Named Range” 43
Adding several named ranges in one step 46
Using a named range 47
2.9 Selecting All Cells With Formulas That Evaluate To A Similar Number Type 48
2.10 Special Paste Options 48
2.10.a Pasting only the formula (but not the formatting and comments) 48
2.10.b Pasting the result of a formula, but not the formula itself 48
2.11 Cutting And Pasting Formulae 49
2.11.a The difference between “copying and pasting” formulas and “cutting and
pasting” formulas 49
2.12 Creating A Table Of Formulas Using Data/Table 50
2.13 Saving Time By Writing, Copying And Pasting Formulas On Several Worksheets
Simultaneously 50
CHAPTER 3 PASTE SPECIAL 52
3.1 Pasting The Result Of A Formula, But Not The Formula 53
3.2 Other Selective Pasting Options 56
3.2.a Pasting only the formula (but not the formatting and comments) 56
3.2.b Pasting only formats 56
3.2.c Pasting data validation schemes 57
3.2.d Pasting all but the borders 57
3.2.e Pasting comments only 57
3.3 Performing An Algebraic “Operation” When Pasting One Column/Row/Range On
To Another 58
3.3.a Multiplying/dividing/subtracting/adding all cells in a range by a number
58
3.3.b Multiplying/dividing the cell values in cells in several “pasted on”
columns with the values of the copied range 59
3.4 Switching Rows To Columns 59
CHAPTER 4 INSERTING FUNCTIONS 61
4.1 Basics 61
4.2 A Simple Function 64
4.3 Functions That Need Multiple Range References 67
4.4 Writing A “Function Within A Function” 69
4.5 New Function-Related Features In The XP Version Of Excel 73
Searching for a function 73
4.5.a Enhanced Formula Bar 73
4.5.b Error Checking and Debugging 74
CHAPTER 5 TRACING CELL REFERENCES & DEBUGGING FORMULA
ERRORS 76
5.1 Tracing the cell references used in a formula 76
5.2 Tracing the formulas in which a particular cell is referenced 78
5.3 The Auditing Toolbar 79
5.4 Watch window (only available in the XP version of Excel) 80
5.5 Error checking and Formula Evaluator (only available in the XP version of Excel)
81
5.6 Formula Auditing Mode (only available in the XP version of Excel) 84
5.7 Cell-specific Error Checking and Debugging 85
5.8 Error Checking Options 86
CHAPTER 6 FUNCTIONS FOR BASIC STATISTICS 89
6.1 “Averaged” Measures Of Central Tendency 90
6.1.a AVERAGE 90
6.1.b TRIMMEAN (“Trimmed mean”) 91
6.1.c HARMEAN (“Harmonic mean”) 92
6.1.d GEOMEAN (“Geometric mean”) 93
6.2 Location Measures Of Central Tendency (Mode, Median) 94
6.2.a MEDIAN 95
6.2.b MODE 95
6.3 Other Location Parameters (Maximum, Percentiles, Quartiles, Other) 95
6.3.a QUARTILE 96
6.3.b PERCENTILE 96
6.3.c Maximum, Minimum and “Kth Largest” 97
MAX (“Maximum value”) 97
MIN (“Minimum value”) 98
LARGE 98
SMALL 99
6.3.d Rank or relative standing of each cell within the range of a series 99
PERCENTRANK 99
RANK 100
6.4 Measures Of Dispersion (Standard Deviation & Variance) 100
Sample dispersion: STDEV, VAR 100
Population dispersion: STDEVP, VARP 101
6.5 Shape Attributes Of The Density Function (Skewness, Kurtosis) 102
6.5.a Skewness 102
6.5.b Kurtosis 104
6.6 Functions Ending With An “A” Suffix 105
CHAPTER 7 PROBABILITY DENSITY FUNCTIONS AND CONFIDENCE
INTERVALS 109
7.1 Probability Density Functions (PDF), Cumulative Density Functions (CDF), and
Inverse functions 110
7.1.a Probability Density Function (PDF) 110
7.1.b Cumulative Density Function (CDF) 111
The CDF and Confidence Intervals 112
7.1.c Inverse mapping functions 114
7.2 Normal Density Function 115
Symmetry 116
Convenience of using the Normal Density Function 117
Are all large-sample series Normally Distributed? 117
Statistics & Econometrics: Dependence of Methodologies on the assumption
of Normality 118
The Standard Normal and its power 119
7.2.a The Probability Density Function (PDF) and Cumulative Density Function
(CDF) 119
7.2.b Inverse function 121
7.2.c Confidence Intervals 121
95% Confidence Interval 121
90% Confidence Interval 122
7.3 Standard Normal or Z–Density Function 123
Inverse function 124
Confidence Intervals 124
7.4 T–Density Function 125
Inverse function 126
Confidence Intervals 126
7.4.a One–tailed Confidence Intervals 127
95% Confidence Interval 127
90% Confidence Interval 127
7.5 F–Density Function 129
Inverse function 129
One–tailed Confidence Intervals 130
7.6 Chi-Square Density Function 130
Inverse function 131
One–tailed Confidence Intervals 131
7.7 Other Continuous Density Functions: Beta, Gamma, Exponential, Poisson,
Weibull & Fisher 132
7.7.a Beta Density Function 132
Inverse Function 133
Confidence Intervals 134
7.7.b Gamma Density Function 134
Inverse Function 135
Confidence Intervals 136
7.7.c Exponential Density Function 136
7.7.d Fisher Density Function 138
7.7.e Poisson Density Function 138
7.7.f Weibull Density Function 138
7.7.g Discrete probabilities— Binomial, Hypergeometric & Negative Binomial
139
Binomial Density Function 139
Hypergeometric Density Function 139
Negative Binomial 139
7.8 List of Density Function 140
7.9 Some Inverse Function 141
................