R Through Excel: A Spreadsheet Interface for Statistics, Data Analysis, and Graphics

369 Pages·2009·21.18 MB·English

by  Richard M. Heiberger

Use R!
Advisors:
RobertGentleman
KurtHornik
GiovanniParmigiani
Forothertitlespublishedinthisseries,goto
http://www.springer.com/series/6991
·
Richard M. Heiberger Erich Neuwirth
R Through Excel
A Spreadsheet Interface for Statistics,
Data Analysis, and Graphics
123
RichardM.Heiberger ErichNeuwirth
DepartmentofStatistics UniversityofVienna
TempleUniversity Fakulta¨tfu¨rInformatik
PhiladelphiaPA19122 Dr.-Karl-Lueger-Ring1
USA A-1010Vienna
[email protected] Austria
[email protected]
SeriesEditors
RobertGentleman KurtHornik
PrograminComputationalBiology Wirtschaftsuniversita¨tWien,Vienna
DivisionofPublicHealthSciences Austria
FredHutchinsonCancerResearchCenter
1100FairviewAvenue
N.M2-B876
Seattle,Washington98109
USA
GiovanniParmigiani
JohnsHopkinsUniversity
Baltimore,MD
USA
ISBN978-1-4419-0051-7 e-ISBN978-1-4419-0052-4
DOI10.1007/978-1-4419-0052-4
SpringerDordrechtHeidelbergLondonNewYork
LibraryofCongressControlNumber:2009929743
(cid:2)c SpringerScience+BusinessMedia,LLC2009
Allrightsreserved.Thisworkmaynotbetranslatedorcopiedinwholeorinpartwithoutthewritten
permission of the publisher (Springer Science+Business Media, LLC, 233 Spring Street, New York,
NY10013,USA),exceptforbriefexcerptsinconnectionwithreviewsorscholarlyanalysis.Usein
connection with any form of information storage and retrieval, electronic adaptation, computer
software,orbysimilarordissimilarmethodologynowknownorhereafterdevelopedisforbidden.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if
they are not identified as such, is not to be taken as an expression of opinion as to whether or not
theyaresubjecttoproprietaryrights.
Printedonacid-freepaper
SpringerispartofSpringerScience+BusinessMedia(www.springer.com)
Let’snotkidourselves:Themostwidelyused
pieceofsoftwareforstatisticsisExcel.
BrianD.Ripley
“StatisticalMethodsNeedSoftware:AView
ofStatistical Computing.” Openinglecture
RoyalStatistical Society2002,Plymouth
(September2002).

Preface
Abstract MS Excel, the most widely available spreadsheet on MS Windows ma-
chines,isoftenusedfordatacollection,manipulation,andstorage.Elementaryand
medium-complexitymathematicalandstatisticalfunctionsareincludedwithExcel.
More advanced and highly reliable statistical analysis in Excel requires an add-in
package. R is one of the best statistics programs available. It is an extensible sys-
tem of software facilities for data manipulation, statistical analysis, and graphical
display. With RExcel, the entire R environment (including more than a thousand
contributedpackages)canbetreatedasanextensionofExcel.
This book is a supplementary text to any introductory course in statistics. The
book supports the instructor by giving students step-by-step screenshots showing
accesstostate-of-the-artstatisticalcomputationsinRdirectlyfromthemenubarin
Excel.
Thebookcanalsobeusedasacomputationalintroductionbydataanalystswho
alreadyhavebasicstatisticalskills.
Risaprogramforstatisticalanalysisandgraphicaldisplayofdata.
Risoneofthebestprogramsforstatisticalanalysisandgraphicaldisplayofdata.
It is maintained and distributed by an international team of statisticians and com-
puter scientists working in universities and industry. R is one of the major tools
usedinstatisticalresearchandinapplicationsofstatisticsinscience,socialscience,
economics,andbusiness.Rusedinbothacademiaandindustry.
Amongotherthings,Rhas
• datahandlingandstoragefacilities.
• asuiteofoperatorsforcalculationsonarrays,inparticularmatrices.
• alarge,coherent,integratedcollectionofintermediatetoolsfordataanalysis.
vii
viii Preface
• graphical facilities for data analysis and display either directly at the computer
oronhardcopy.
• awell-developed,powerful,andeffectiveprogramminglanguage(calledS)that
includes conditionals, loops, user-defined functions (including recursive func-
tions), functions for creating complex data structures, and input and output fa-
cilities.(Indeed,mostofthesystem-suppliedfunctionsarethemselveswrittenin
theSlanguage.)
• Alargeselectionofdemonstrationdatasetsusedintheillustrationofmanysta-
tisticalmethods.
Excelisthemostwidelyusedspreadsheetprogram.
MicrosoftExcel(cid:2)R [Microsoft,2008a]isthemostwidelyavailablespreadsheet.En-
tering data, cleaning data, and simple data processing (including simple statistics)
are very easily done on spreadsheets. As a consequence, much statistical data is
availableas,orevencreatedin,Excelworksheets.
Spreadsheets have a different paradigm for representing mathematical formu-
las than statistical (and mathematical) programming languages. The spreadsheet
paradigm is much more visual and action-oriented than the functional or proce-
dural paradigm of statistical programming languages. This problem of different
paradigms can be overcome. In this book, we illustrate some of the ways the two
paradigmscanbemadetoworkwitheachother.[NeuwirthandArganbright,2004]
discussindetailhowtorepresentthedevelopmentandstructureofspreadsheetsin
printedformandhowspreadsheetscanbeusedtodoseriousmathematicalwork.
Exceliseasytouse,butstatisticianshavefoundithassomedeficiencies inthe
area of numerical precision. Statistical software is usually perceived as difficult to
learn.Thiscanbeamajorobstacleforpotentialusersofadvancedstatisticalmeth-
ods.Asthisbookshows,usingRwithinExcelallowsaccesstoboththeeasy-to-use
toolsfordataentryandmanipulationavailableinExcelandthepowerandprecision
oftheadvancedstatisticalmethodsavailableviaR.
RExcelisaninterfaceprogramthatusesRasanadd-in toExcel.
RExcel is an add-in to Excel on MS Windows [Microsoft,2008b] machines that
allows the use of R as a “helper application” for Excel. Data can be transferred
betweenExcelandR(inbothdirections),andExcelcancallRfunctionstoperform
calculationsandthentransfertheresultstoExcel.
RExceloffersthefollowingfeatures:
• allows the use of R functions in Excel cell formulas, effectively controlling R
calculationsfromExcel’sautomaticrecalculationmechanism.
• connectsRdataframesandExceldatalists.
Preface ix
• handlesmissingdata.
• allowsthecreationofastandaloneRExcelapplicationthathidesRalmostcom-
pletelyfromtheuserandusesExcelasthemaininterfacetoR.(Instructionsare
giveninRExcel’shelpfile.)
• if R Commander [Fox et al., 2007] is available, RExcel optionally places the
RcmdrmenusonanExcelmenubar.AnymenuitemintegratedintotheRcmdr
menuusinganRcmdrpluginwillalsobeavailableontheRExcelRcmdrmenu.
• works with the statconn (D)COM server (previously called R(D)COM server)
server,turningRintoan(invisible)backgroundserverforExcel.
• works with the rcom package, turning R into a (visible) foreground server for
Excel.Usingthisconfiguration,theusercanaccessthesameinstanceofReither
fromExcelorfromthecommandlineinanRGUIConsolewindow.
• supportsRprocessesrunningunderthecontrolofRServerManager.Rserveris
attached to Excel from a server pool. Different instances of Excel (running on
different machines) may access the same R process with the same data. We do
notusethiscapabilityinthisbook.
TheRExcelinterfaceisdescribedin[BaierandNeuwirth,2007].RExcelisbuilt
on the the rcom and statconn (D)COM (previously called R(D)COM) packages,
which we use for communication between R and the Microsoft Office software
[Baier,2007].BasicinformationontheinstallationofR,RExcel,andRcmdrisin
AppendixA.FullinformationonRExcelisavailableathttp://rcom.univie.ac.at/.
RcmdrisanRpackagethatprovidesGUImenuaccesstoR.
Rcmdr (R Commander) is a platform-independent menu interface to R. The menu
itemsimplementedbyRcmdrareprimarilydesignedforintroductorycourses.They
canbeextendedbytheRcmdrpluginfacilitytoprovideaclickablegraphicaluser
interface(GUI)toanystatisticalprocedurecodedinR.
Audience
Therearetwoaudiencesforthisbook:
1. studentslearningstatistics.
2. peopleanalyzingdata.