by Stephen L. Nelson
Get Up to Speed Fast and Analyze your Data more Effectively!
Use Excel for Statistical Funcions, Presenting Data, and more
 |
| Excel 2007 Data Analysis For Dummies |
About the Author
Stephen L. Nelson is the author of more than two dozen best-selling
books, including Quicken For Dummies and QuickBooks For Dummies (Wiley
Publishing, Inc.). In fact, Nelson’s books have sold more than 4,000,000 copies
in English and have been translated into more than ten other languages.
Nelson is a certified public accountant and a member of both the Washington
Society of CPAs and the American Institute of CPAs. He holds a Bachelor of
Science in Accounting, magna cum laude, from Central Washington University,
and a Masters in Business Administration in Finance from the University of
Washington (where, curiously, he was the youngest person ever to graduate
from the program), and a Master of Science in Taxation from Golden GateUniversity.
Nelson’s work experience includes stints as a book publisher and packager,
as the chief financial officer, treasurer, and controller of a high-technology
manufacturer, and as a senior consultant with one of the Big Five public accounting firms.
Nelson lives in the foothills east of Redmond, Washington with his wife, two
daughters, and an indeterminate number of mice.
Author’s Acknowledgments
The curious thing about writing a book is this: Although an author’s name
appears on the cover, it’s always really a team project. Take the case of
this book, for example. Truth be told, the book was really the idea of Andy
Cummings, the publisher of For Dummies technology books, and Bob Woerner,
my long-suffering acquisitions editor. I wrote the manuscript, and then a lot
of folks at Wiley expended a lot of effort into turning my rough manuscript
into a polished book. Nicole Sholly, project editor, Virginia Sanders, copy
editor, Michael Talley, technical editor, and a host of page layout technicians,
proofreaders, and graphic artists are just some of the people who helped this book come to life.
Introduction
So here’s a funny deal: You know how to use Excel. You know how to
create simple workbooks and how to print stuff. And you can even, with
just a little bit of fiddling, create cool-looking charts.
But I bet that you sometimes wish that you could do more with Excel. You
sometimes wish, I wager, that you could use Excel to really gain insights into
the information, the data, that you work with in your job.
Using Excel for data analysis is what this book is all about. This book assumes
that you want to use Excel to learn new stuff, discover new secrets, and gain
new insights into the information that you’re already working with in Excel —
or the information stored electronically in some other format, such as in your accounting system.
About This Book
This book isn’t meant to be read cover to cover like a Dan Brown page-turner.
Rather, it’s organized into tiny, no-sweat descriptions of how to do the things
that must be done. Hop around and read the chapters that interest you.
If you’re the sort of person who, perhaps because of a compulsive bent,
needs to read a book cover to cover, that’s fine. I recommend that you delve
in to the chapters on inferential statistics, however, only if you’ve taken at
least a couple of college-level statistics classes. But that caveat aside, feel
free. After all, maybe Lost is a rerun tonight.
What You Can Safely Ignore
This book provides a lot of information. That’s the nature of a how-to reference.
So I want to tell you that it’s pretty darn safe for you to blow off some
chunks of the book.
For example, in many places throughout the book I provide step-by-step
descriptions of the task. When I do so, I always start each step with a boldfaced
description of what the step entails. Underneath that bold-faced step
description, I provide detailed information about what happens after you perform
that action. Sometimes I also offer help with the mechanics of the step,
like this:
1. Press Enter.
Find the key that’s labeled Enter. Extend your index finger so that it rests
ever so gently on the Enter key. Then, in one sure, fluid motion, press
the key by using your index finger. Then release the key.
Okay, that’s kind of an extreme example. I never actually go into that much
detail. My editor won’t let me. But you get the idea. If you know how to press
Enter, you can just do that and not read further. If you need help — say with
the finger-depression part or the finding-the-right-key part — you can read the nitty-gritty details.
You can also skip the paragraphs flagged with the Technical Stuff icon. These
icons flag information that’s sort of tangential, sort of esoteric, or sort of
questionable in value . . . at least for the average reader. If you’re really interested
in digging into the meat of the subject being discussed, go ahead and
read ’em. If you’re really just trying to get through your work so that you can
get home and watch TV with your kids, skip ’em.
I might as well also say that you don’t have to read the information provided
in the paragraphs marked with a Tip icon, either. I assume that you want to
know an easier way to do something. But if you like to do things the hard way
because that improves your character and makes you tougher, go ahead and
skip the Tip icons.
How This Book Is Organized
This book is organized into five parts:
Part I: Where’s the Beef?
In Part I, I discuss how you get data into Excel workbooks so that you can
begin to analyze it. This is important stuff, but fortunately most of it is pretty
straightforward. If you’re new to data analysis and not all that fluent yet in
working with Excel, you definitely want to begin in Part I.
Part II: PivotTables and PivotCharts
In the second part of this book, I cover what are perhaps the most powerful
data analysis tools that Excel provides: its cross-tabulation capabilities using
the PivotTable and PivotChart commands.
No kidding, I don’t think any Excel data analysis skill is more useful than
knowing how to create pivot tables and pivot charts. If I could, I would give
you some sort of guarantee that the time you spent reading how to use these
tools is always worth the investment you make. Unfortunately, after consultation
with my attorney, I find that this is impossible to do.
Part III: Advanced Tools
In Part III, I discuss some of the more sophisticated tools that Excel supplies
for doing data analysis. Some of these tools are always available in Excel,
such as the statistical functions. (I use a couple of chapters to cover these.)
Some of the tools come in the form of Excel add-ins, such as the Data
Analysis and the Solver add-ins.
I don’t think that these tools are going to be of interest to most readers of this
book. But if you already know how to do all the basic stuff and you have some
good statistical and quantitative methods, training, or experience, you ought
to peruse these chapters. Some really useful whistles and bells are available to
advanced users of Excel. And it would be a shame if you didn’t at least know
what they are and the basic steps that you need to take to use them.
Part IV: The Part of Tens
In my mind, perhaps the most clever element that Dan Gookin, the author of
the original and first Dummies book, DOS For Dummies, came up with is the
part with chapters that just list information in David Letterman-ish fashion.
These chapters let us authors list useful tidbits, tips, and factoids for you.
Excel 2007 Data Analysis For Dummies includes three such chapters. In the
first, I provide some basic facts most everybody should know about statistics
and statistical analysis. In the second, I suggest ten tips for successfully and
effectively analyzing data in Excel. Finally, in the third chapter, I try to make
some useful suggestions about how you can visually analyze information and
visually present data analysis results.
The Part of Tens chapters aren’t technical. They aren’t complicated. They’re
very basic. You should be able to skim the information provided in these
chapters and come away with at least a few nuggets of useful information.
Part V: Appendix
The appendix contains a handy glossary of terms you should understand
when working with data in general and Excel specifically. From kurtosis to
histograms, these sometimes baffling terms are defined here.
Screenshot
Purchase Now !
Just with Paypal
Just with Paypal
Product details
Price
|
|
|---|---|
File Size
| 18,479 KB |
Pages
|
381 p |
File Type
|
PDF format |
ISBN
| 978-0-470-04599-2 |
Copyright
| 2007 by Wiley Publishing, Inc |
Contents at a Glance
Introduction
Part I: Where’s the Beef?
Chapter 1: Introducing Excel Tables
Chapter 2: Grabbing Data from External Sources
Chapter 3: Scrub-a-Dub-Dub: Cleaning Data
Part II: PivotTables and PivotCharts
Chapter 4: Working with PivotTables
Chapter 5: Building PivotTable Formulas
Chapter 6: Working with PivotCharts
Chapter 7: Customizing PivotCharts
Part III: Advanced Tools
Chapter 8: Using the Database Functions
Chapter 9: Using the Statistics Functions
Chapter 10: Descriptive Statistics
Chapter 11: Inferential Statistics
Chapter 12: Optimization Modeling with Solver
Part IV: The Part of Tens
Chapter 13: Almost Ten Things You Ought to Know about Statistics
Chapter 14: Almost Ten Tips for Presenting Table Results and Analyzing Data
Chapter 15: Ten Tips for Visually Analyzing and Presenting Data
Part V: Appendix
Glossary of Data Analysis and Excel Terms
Index
Table of Contents
Introduction.......
About This Book......
What You Can Safely Ignore ..........
What You Shouldn’t Ignore (Unless You’re a Masochist) ......
Three Foolish Assumptions .........
How This Book Is Organized....................
Part I: Where’s the Beef?...................
Part II: PivotTables and PivotCharts...............
Part III: Advanced Tools..........
Part IV: The Part of Tens.........
Part V: Appendix................
Special Icons ...............
Where to Next? ....
Part I: Where’s the Beef? ...............................................7
Chapter 1: Introducing Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
What Is a Table and Why Do I Care? ..............................................................9
Building Tables ...............................................................................................12
Exporting from a database..................................................................12
Building a table the hard way .............................................................12
Building a table the semi-hard way....................................................13
Analyzing Table Information.........................................................................15
Simple statistics....................................................................................17
Sorting table records ...........................................................................19
Using AutoFilter on a table..................................................................21
Undoing a filter .....................................................................................23
Turning off filter....................................................................................23
Using the custom AutoFilter ...............................................................23
Filtering a filtered table .......................................................................25
Using advanced filtering......................................................................26
Chapter 2: Grabbing Data from External Sources . . . . . . . . . . . . . . . . .31
Getting Data the Export-Import Way ...........................................................31
Exporting: The first step......................................................................32
Importing: The second step (if necessary).......................................36
Querying External Databases and Web Page Tables .................................45
Running a Web query...........................................................................45
Importing a database table .................................................................47
Querying an external database...........................................................50
It’s Sometimes a Raw Deal.............................................................................56
Chapter 3: Scrub-a-Dub-Dub: Cleaning Data . . . . . . . . . . . . . . . . . . . . .57
Editing Your Imported Workbook ................................................................57
Delete unnecessary columns..............................................................58
Delete unnecessary rows ....................................................................58
Resize columns .....................................................................................59
Resize rows ...........................................................................................60
Erase unneeded cell contents.............................................................61
Format numeric values........................................................................61
Copying worksheet data......................................................................61
Moving worksheet data .......................................................................62
Replacing data in fields........................................................................62
Cleaning Data with Text Functions ..............................................................63
What’s the big deal, Steve?..................................................................63
The answer to some of your problems..............................................65
The CLEAN function.............................................................................65
The CONCATENATE function ..............................................................66
The EXACT function.............................................................................66
The FIND function ................................................................................67
The FIXED function ..............................................................................68
The LEFT function ................................................................................68
The LEN function..................................................................................68
The LOWER function............................................................................69
The MID function..................................................................................69
The PROPER function ..........................................................................70
The REPLACE function ........................................................................70
The REPT function ...............................................................................70
The RIGHT function..............................................................................71
The SEARCH function ..........................................................................71
The SUBSTITUTE function ..................................................................72
The T function ......................................................................................72
The TEXT function ...............................................................................73
The TRIM function................................................................................73
The UPPER function.............................................................................73
The VALUE function .............................................................................74
Converting text function formulas to text.........................................74
Using Validation to Keep Data Clean ...........................................................75
Part II: PivotTables and PivotCharts .............................79
Chapter 4: Working with PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . .81
Looking at Data from Many Angles ..............................................................81
Getting Ready to Pivot...................................................................................82
Running the PivotTable Wizard....................................................................83
Fooling Around with Your Pivot Table ........................................................88
Pivoting and re-pivoting ......................................................................88
Filtering pivot table data .....................................................................88
Refreshing pivot table data .................................................................92
Sorting pivot table data .......................................................................92
Pseudo-sorting......................................................................................94
Grouping and ungrouping data items ................................................95
Selecting this, selecting that ...............................................................97
Where did that cell’s number come from?........................................97
Setting value field settings ..................................................................97
Customizing How Pivot Tables Work and Look........................................100
Setting pivot table options ................................................................100
Formatting pivot table information..................................................103
Chapter 5: Building PivotTable Formulas . . . . . . . . . . . . . . . . . . . . . . .107
Adding Another Standard Calculation.......................................................107
Creating Custom Calculations ....................................................................111
Using Calculated Fields and Items .............................................................115
Adding a calculated field ...................................................................115
Adding a calculated item...................................................................117
Removing calculated fields and items .............................................119
Reviewing calculated field and calculated item formulas.............121
Reviewing and changing solve order ...............................................122
Retrieving Data from a Pivot Table ............................................................123
Getting all the values in a pivot table ..............................................123
Getting a value from a pivot table ....................................................124
Arguments of the GETPIVOTDATA function....................................126
Chapter 6: Working with PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . .127
Why Use a PivotChart? ................................................................................127
Getting Ready to Pivot.................................................................................128
Running the PivotTable Wizard..................................................................129
Fooling Around with Your Pivot Chart ......................................................134
Pivoting and re-pivoting ....................................................................134
Filtering pivot chart data...................................................................135
Refreshing pivot chart data ..............................................................137
Grouping and ungrouping data items ..............................................138
Using Chart Commands to Create Pivot Charts.......................................139
Chapter 7: Customizing PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
Selecting a Chart Type.................................................................................143
Working with Chart Layouts .......................................................................144
Working with Chart Styles...........................................................................144
Setting Chart Options ..................................................................................144
Chart titles...........................................................................................145
Chart legend........................................................................................146
Chart data labels ................................................................................147
Chart data tables ................................................................................149
Chart axes............................................................................................150
Chart gridlines ....................................................................................152
Changing a Chart’s Location.......................................................................152
Formatting the Plot Area.............................................................................154
Formatting the Chart Area ..........................................................................155
Chart fill patterns ...............................................................................155
Chart area fonts ..................................................................................155
Formatting 3-D Charts .................................................................................156
Formatting the walls of a 3-D chart ..................................................156
Using the 3-D View command ...........................................................156
Part III: Advanced Tools ............................................157
Chapter 8: Using the Database Functions . . . . . . . . . . . . . . . . . . . . . . .159
Quickly Reviewing Functions......................................................................159
Understanding function syntax rules ..............................................160
Entering a function manually............................................................160
Entering a function with the Function command...........................161
Using the DAVERAGE Function...................................................................165
Using the DCOUNT and DCOUNTA Functions ..........................................168
Using the DGET Function ............................................................................170
Using the DMAX and DMAX Functions......................................................172
Using the DPRODUCT Function..................................................................174
Using the DSTDEV and DSTDEVP Functions ............................................174
Using the DSUM Function............................................................................176
Using the DVAR and DVARP Functions ......................................................178
Chapter 9: Using the Statistics Functions . . . . . . . . . . . . . . . . . . . . . . .181
Counting Items in a Data Set.......................................................................181
COUNT: Counting cells with values .................................................181
COUNTA: Alternative counting cells with values ...........................182
COUNTBLANK: Counting empty cells..............................................183
COUNTIF: Counting cells that match criteria .................................183
PERMUT: Counting permutations ....................................................184
COMBIN: Counting combinations.....................................................184
Means, Modes, and Medians.......................................................................184
AVEDEV: An average absolute deviation .........................................185
AVERAGE: Average .............................................................................185
AVERAGEA: An alternate average.....................................................186
TRIMMEAN: Trimming to a mean.....................................................186
MEDIAN: Median value ......................................................................187
MODE: Mode value.............................................................................187
GEOMEAN: Geometric mean.............................................................188
HARMEAN: Harmonic mean..............................................................188
Finding Values, Ranks, and Percentiles .....................................................188
MAX: Maximum value ........................................................................188
MAXA: Alternate maximum value ....................................................189
MIN: Minimum value ..........................................................................189
MINA: Alternate minimum value ......................................................189
LARGE: Finding the kth largest value...............................................189
SMALL: Finding the kth smallest value............................................190
RANK: Ranking an array value ..........................................................190
PERCENTRANK: Finding a percentile ranking ................................191
PERCENTILE: Finding a percentile ranking .....................................192
FREQUENCY: Frequency of values in a range .................................193
PROB: Probability of values ..............................................................194
Standard Deviations and Variances ...........................................................195
STDEV: Standard deviation of a sample ..........................................195
STDEVA: Alternate standard deviation of a sample .......................196
STDEVP: Standard deviation of a population .................................196
STDEVPA: Alternate standard deviation of a population ..............197
VAR: Variance of a sample .................................................................197
VARA: Alternate variance of a sample .............................................198
VARP: Variance of a population ........................................................198
VARPA: Alternate variance of a population .....................................198
COVAR: Covariance ............................................................................199
DEVSQ: Sum of the squared deviations...........................................199
Normal Distributions ...................................................................................199
NORMDIST: Probability X falls at or below a given value .............199
NORMINV: X that gives specified probability .................................200
NORMSDIST: Probability variable within
z-standard deviations.....................................................................201
NORMSINV: z-value equivalent to a probability .............................201
STANDARDIZE: z-value for a specified value...................................202
CONFIDENCE: Confidence interval for a population mean...........202
KURT: Kurtosis ...................................................................................203
SKEW: Skewness of a distribution ....................................................204
t-distributions...............................................................................................204
TDIST: Probability of given t-value...................................................204
TINV: t-value of a given probability..................................................205
TTEST: Probability two samples from same population...............205
f-distributions ...............................................................................................206
FDIST: f-distribution probability.......................................................206
FINV: f-value given f-distribution probability..................................206
FTEST: Probability data set variances not different ......................207
Binomial Distributions.................................................................................207
BINOMDIST: Binomial probability distribution ..............................207
NEGBINOMDIST: Negative binominal distribution.........................208
CRITBINOM: Cumulative binomial distribution .............................209
HYPGEOMDIST: Hypergeometric distribution................................209
Chi-Square Distributions .............................................................................210
CHIDIST: Chi-square distribution .....................................................210
CHIINV: Chi-square value for a given level of significance............211
CHITEST: Chi-square test...................................................................212
Regression Analysis .....................................................................................212
FORECAST: Forecast dependent variables using
a best-fit line ....................................................................................213
INTERCEPT: y-axis intercept of a line ..............................................213
LINEST..................................................................................................213
SLOPE: Slope of a regression line .....................................................214
STEYX: Standard error.......................................................................214
TREND..................................................................................................214
LOGEST: Exponential regression......................................................214
GROWTH: Exponential growth .........................................................215
Correlation ....................................................................................................215
CORREL: Correlation coefficient ......................................................215
PEARSON: Pearson correlation coefficient .....................................216
RSQ: r-squared value for a Pearson correlation coefficient..........216
FISHER..................................................................................................216
FISHERINV ...........................................................................................216
Some Really Esoteric Probability Distributions .......................................217
BETADIST: Cumulative beta probability density ............................217
BETAINV: Inverse cumulative beta probability density ................217
EXPONDIST: Exponential probability distribution.........................218
GAMMADIST: Gamma distribution probability ..............................218
GAMMAINV: X for a given gamma distribution probability ..........219
GAMMALN: Natural logarithm of a gamma distribution ...............219
LOGNORMDIST: Probability of lognormal distribution.................220
LOGINV: Value associated with lognormal distribution
probability .......................................................................................220
POISSON: Poisson distribution probabilities..................................220
WEIBULL: Weibull distribution .........................................................221
ZTEST: Probability of a z-test............................................................221
Chapter 10: Descriptive Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . .223
Using the Descriptive Statistics Tool.........................................................224
Creating a Histogram ...................................................................................228
Ranking by Percentile ..................................................................................231
Calculating Moving Averages......................................................................233
Exponential Smoothing ...............................................................................235
Generating Random Numbers ....................................................................238
Sampling Data...............................................................................................240
Chapter 11: Inferential Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .245
Using the t-test Data Analysis Tool ............................................................246
Performing z-test Calculations ...................................................................248
Creating a Scatter Plot.................................................................................250
Using the Regression Data Analysis Tool..................................................255
Using the Correlation Analysis Tool ..........................................................256
Using the Covariance Analysis Tool ..........................................................259
Using the ANOVA Data Analysis Tools ......................................................260
Creating an f-test Analysis...........................................................................261
Using Fourier Analysis.................................................................................262
Chapter 12: Optimization Modeling with Solver . . . . . . . . . . . . . . . . .263
Understanding Optimization Modeling .....................................................264
Optimizing your imaginary profits...................................................264
Recognizing constraints ....................................................................264
Setting Up a Solver Worksheet ...................................................................265
Solving an Optimization Modeling Problem .............................................268
Reviewing the Solver Reports ....................................................................273
The Answer Report ............................................................................273
The Sensitivity Report .......................................................................274
The Limits Report...............................................................................276
Some other notes about Solver reports ..........................................277
Working with the Solver Options ...............................................................277
Setting a limit on Solver.....................................................................278
Deciding how nit-picky to be ............................................................278
Saying when ........................................................................................279
When you assume . . . ........................................................................279
Using automatic scaling.....................................................................280
Showing iteration results...................................................................280
Tangent versus quadratic estimates................................................280
Forward versus central derivatives .................................................280
Newton versus conjugate algorithms ..............................................281
Saving and reusing model information............................................281
Understanding the Solver Error Messages ...............................................282
Solver has converged to the current solution ................................282
Solver cannot improve the current solution...................................282
Stop chosen when maximum time limit was reached ...................283
Stop chosen when maximum iteration limit was reached ............283
Set target cell values do not converge.............................................283
Solver could not find a feasible solution .........................................284
Conditions for assume linear model are not satisfied...................284
Solver encountered an error value in a target
or constraint cell.............................................................................284
There is not enough memory available to
solve the problem...........................................................................285
Part IV: The Part of Tens ............................................287
Chapter 13: Almost Ten Things You Ought to Know about Statistics .. . .289
Descriptive Statistics Are Straightforward ...............................................290
Averages Aren’t So Simple Sometimes ......................................................290
Standard Deviations Describe Dispersion ................................................291
An Observation Is an Observation ............................................................292
A Sample Is a Subset of Values ...................................................................293
Inferential Statistics Are Cool but Complicated .......................................293
Probability Distribution Functions Aren’t Always Confusing.................294
Uniform distribution ..........................................................................295
Normal distribution ...........................................................................295
Parameters Aren’t So Complicated............................................................297
Skewness and Kurtosis Describe a Probability
Distribution’s Shape.................................................................................297
Chapter 14: Almost Ten Tips for Presenting
Table Results and Analyzing Data . . . . .. . . . .299
Work Hard to Import Data...........................................................................299
Design Information Systems to Produce Rich Data .................................300
Don’t Forget about Third-Party Sources ...................................................301
Just Add It .....................................................................................................301
Always Explore Descriptive Statistics .......................................................302
Watch for Trends..........................................................................................302
Slicing and Dicing: Cross-Tabulation .........................................................303
Chart It, Baby................................................................................................303
Be Aware of Inferential Statistics ...............................................................303
Chapter 15: Ten Tips for Visually Analyzing and Presenting Data . . . ..305
Using the Right Chart Type.........................................................................305
Using Your Chart Message as the Chart Title...........................................307
Beware of Pie Charts....................................................................................307
Consider Using Pivot Charts for Small Data Sets.....................................309
Avoiding 3-D Charts .....................................................................................310
Never Use 3-D Pie Charts ............................................................................312
Be Aware of the Phantom Data Markers ...................................................313
Use Logarithmic Scaling..............................................................................313
Don’t Forget to Experiment.........................................................................316
Get Tufte........................................................................................................316
Part V: Appendix .......................................................319
Glossary of Data Analysis and Excel Terms . . . . . . . . . . . . . . . . . . . . .321
Index........................................................................331
●▬▬▬▬▬❂❂❂▬▬▬▬▬●
●▬▬❂❂▬▬●
●▬❂▬●
●❂●
═════● ●═════
