Data Analysis with Microsoft Excel book
Ddownload free
Chapter 1
GETTING STARTED WITH EXCEL 1
Getting Started 2
Special Files for This Book 2
Installing the StatPlus Files 2
Excel and Spreadsheets 4
Launching Excel 5
Viewing the Excel Window 6
Running Excel Commands 7
Excel Workbooks and Worksheets 10
Opening a Workbook 10
Scrolling through a Workbook 11
Worksheet Cells 14
Selecting a Cell 14
Moving Cells 16
Printing from Excel 18
Previewing the Print Job 18
Setting Up the Page 19
Printing the Page 21
Saving Your Work 22
Excel Add-Ins 24
Loading the StatPlus Add-In 24
Loading the Data Analysis ToolPak 28
Unloading an Add-In 30
Features of StatPlus 30
Using StatPlus Modules 30
Hidden Data 31
Linked Formulas 32
Setup Options 32
Exiting Excel 34
Chapter 2
WORKING WITH DATA 35
Data Entry 36
Entering Data from the Keyboard 36
Entering Data with Autofll 37
Inserting New Data 40
Data Formats 41
Formulas and Functions 45
Inserting a Simple Formula 46
Inserting an Excel Function 47
Cell References 50
Range Names 51
Sorting Data 54
Querying Data 55
Using the AutoFilter 56
Using the Advanced Filter 59
Using Calculated Values 62
Importing Data from Text Files 63
Importing Data from Databases 68
Using Excel’s Database Query Wizard 68
Specifying Criteria and Sorting Data 71
Exercises 75
Chapter 3
WORKING WITH CHARTS 81
Introducing Excel Charts 82
Introducing Scatter Plots 86
Editing a Chart 91
Resizing and Moving an Embedded
Chart 91
Moving a Chart to a Chart Sheet 93
Working with Chart and Axis Titles 94
Editing the Chart Axes 97
Working with Gridlines and Legends 100
Editing Plot Symbols 102
Identifying Data Points 105
Selecting a Data Row 106
Labeling Data Points 107
Formatting Labels 109
Creating Bubble Plots 110
Breaking a Scatter Plot into
Categories 117
Plotting Several Variables 120
Exercises 123
Chapter 4
DESCRIBING YOUR DATA 128
Variables and Descriptive Statistics 129
Frequency Tables 131
Creating a Frequency Table 132
Using Bins in a Frequency Table 134
Def ning Your Own Bin Values 136
Working with Histograms 138
Creating a Histogram 138
Shapes of Distributions 141
Breaking a Histogram into Categories 143
Working with Stem and Leaf Plots 146
Distribution Statistics 151
Percentiles and Quartiles 151
Measures of the Center: Means, Medians,
and the Mode 154
Measures of Variability 159
Measures of Shape: Skewness and
Kurtosis 162
Outliers 164
Working with Boxplots 165
Concept Tutorials: Boxplots 166
Exercises 175
Chapter 5
PROBABILITY DISTRIBUTIONS 182
Probability 183
Probability Distributions 184
Discrete Probability Distributions 185
Continuous Probability Distributions 186
Concept Tutorials: PDFs 187
Random Variables and Random Samples 189
Concept Tutorials: Random Samples 190
The Normal Distribution 193
Concept Tutorials:
The Normal Distribution 194
Excel Worksheet Functions 196
Using Excel to Generate Random
Normal Data 197
Charting Random Normal Data 199
The Normal Probability Plot 201
Parameters and Estimators 205
The Sampling Distribution 206
Concept Tutorials:
Sampling Distributions 211
The Standard Error 212
The Central Limit Theorem 212
Concept Tutorials:
The Central Limit Theorem 213
Exercises 218
Chapter 6
STATISTICAL INFERENCE 224
Confdence Intervals 225
z Test Statistic and z Values 225
Calculating the Confdence Interval
with Excel 228
Interpreting the Confdence Interval 229
Concept Tutorials:
The Confdence Interval 229
Hypothesis Testing 232
Types of Error 233
An Example of Hypothesis Testing 234
Acceptance and Rejection Regions 234
p Values 235
Concept Tutorials: Hypothesis Testing 236
Additional Thoughts about
Hypothesis Testing 239
The t Distribution 240
Concept Tutorials: The t Distribution 241
Working with the t Statistic 242
Constructing a t Confdence Interval 243
The Robustness of t 243
Applying the t Test to Paired Data 244
Applying a Nonparametric Test to
Paired Data 250
The Wilcoxon Signed Rank Test 250
The Sign Test 253
The Two-Sample t Test 255
Comparing the Pooled and Unpooled
Test Statistics 256
Working with the Two-Sample
t Statistic 256
Testing for Equality of Variance 258
Applying the t Test to Two-Sample Data 259
Applying a Nonparametric Test to
Two-Sample Data 265
Final Thoughts about Statistical Inference 267
Exercises 268
Chapter 7
TABLES 275
PivotTables 276
Removing Categories from a
PivotTable 280
Changing the Values Displayed
by the PivotTable 282
Displaying Categorical Data in a
Bar Chart 283
Displaying Categorical Data in a
Pie Chart 285
Two-Way Tables 288
Computing Expected Counts 291
The Pearson Chi-Square Statistic 293
Concept Tutorials: The x2 Distribution 293
Working with the x2 Distribution in
Excel 296
Breaking Down the Chi-Square
Statistic 297
Other Table Statistics 297
Validity of the Chi-Square Test with Small
Frequencies 299
Tables with Ordinal Variables 302
Testing for a Relationship between
Two Ordinal Variables 303
Custom Sort Order 307
Exercises 309
Chapter 8
REGRESSION AND CORRELATION 313
Simple Linear Regression 314
The Regression Equation 314
Fitting the Regression Line 315
Regression Functions in Excel 316
Exploring Regression 317
Performing a Regression Analysis 318
Plotting Regression Data 320
Calculating Regression Statistics 323
Interpreting Regression Statistics 325
Interpreting the Analysis of Variance
Table 326
Parameter Estimates and Statistics 327
Residuals and Predicted Values 328
Checking the Regression Model 329
Testing the Straight-Line Assumption 329
Testing for Normal Distribution of
the Residuals 331
Testing for Constant Variance in
the Residuals 332
Testing for the Independence of
Residuals 332
Correlation 335
Correlation and Slope 336
Correlation and Causality 336
Spearman’s Rank Correlation
Coeffcient s 337
Correlation Functions in Excel 337
Creating a Correlation Matrix 338
Correlation with a Two-Valued
Variable 342
Adjusting Multiple p Values with
Bonferroni 342
Creating a Scatter Plot Matrix 343
Exercises 345
Chapter 9
MULTIPLE REGRESSION 352
Regression Models with Multiple
Parameters 353
Concept Tutorials:
The F Distribution 353
Using Regression for Prediction 355
Regression Example: Predicting Grades 356
Interpreting the Regression
Output 358
Multiple Correlation 359
Coeff cients and the Prediction
Equation 361
t Tests for the Coeffcients 362
Testing Regression Assumptions 363
Observed versus Predicted Values 363
Plotting Residuals versus Predicted
Values 366
Plotting Residuals versus Predictor
Variables 368
Normal Errors and the Normal Plot 370
Summary of Calc Analysis 371
Regression Example:
Sex Discrimination 371
Regression on Male Faculty 372
Using a SPLOM to See Relationships 373
Correlation Matrix of Variables 374
Multiple Regression 376
Interpreting the Regression Output 377
Residual Analysis of Discrimination
Data 377
Normal Plot of Residuals 378
Are Female Faculty Underpaid? 380
Drawing Conclusions 385
Exercises 386
Chapter 10
ANALYSIS OF VARIANCE 392
One-Way Analysis of Variance 393
Analysis of Variance Example:
Comparing Hotel Prices 393
Graphing the Data to Verify
ANOVA Assumptions 395
Computing the Analysis of
Variance 397
Interpreting the Analysis of Variance
Table 399
Comparing Means 402
Using the Bonferroni Correction
Factor 403
When to Use Bonferroni 404
Comparing Means with a Boxplot 405
One-Way Analysis of Variance and
Regression 406
Indicator Variables 406
Fitting the Effects Model 408
Two-Way Analysis of Variance 410
A Two-Factor Example 410
Two-Way Analysis Example:
Comparing Soft Drinks 413
Graphing the Data to Verify
Assumptions 414
The Interaction Plot 417
Using Excel to Perform a Two-Way
Analysis of Variance 419
Interpreting the Analysis of Variance
Table 422
Summary 424
Exercises 424
Chapter 11
TIME SERIES 431
Time Series Concepts 432
Time Series Example: The Rise in Global
Temperatures 432
Plotting the Global Temperature Time
Series 433
Analyzing the Change in Global
Temperature 436
Looking at Lagged Values 438
The Autocorrelation Function 440
Applying the ACF to Annual Mean
Temperature 441
Other ACF Patterns 443
Applying the ACF to the Change in
Temperature 444
Moving Averages 445
Simple Exponential Smoothing 448
Forecasting with Exponential
Smoothing 450
Assessing the Accuracy of the
Forecast 450
Concept Tutorials: One-Parameter
Exponential Smoothing 451
Choosing a Value for w 455
Two-Parameter Exponential Smoothing 457
Calculating the Smoothed Values 458
Concept Tutorials: Two-Parameter
Exponential Smoothing 459
Seasonality 462
Multiplicative Seasonality 462
Additive Seasonality 464
Seasonal Example: Liquor Sales 464
Examining Seasonality with a
Boxplot 467
Examining Seasonality with a Line
Plot 468
Applying the ACF to Seasonal Data 470
Adjusting for Seasonality 471
Three-Parameter Exponential
Smoothing 473
Forecasting Liquor Sales 474
Optimizing the Exponential Smoothing
Constant (optional) 479
Exercises 482
Chapter 12
QUALITY CONTROL 487
Statistical Quality Control 488
Controlled Variation 489
Uncontrolled Variation 489
Control Charts 490
Control Charts and Hypothesis
Testing 492
Variable and Attribute Charts 493
Using Subgroups 493
The x Chart 493
Calculating Control Limits When s Is
Known 494
x Chart Example: Teaching Scores 495
Calculating Control Limits When s Is Unknown 498
x Chart Example: A Coating Process 500
The Range Chart 502
The C Chart 504
C Chart Example: Factory Accidents 504
The P Chart 506
P Chart Example: Steel Rod Defects 507
Control Charts for Individual
Observations
The Pareto Chart
0 মন্তব্য(গুলি):
একটি মন্তব্য পোস্ট করুন