Excel can be a safe comfort zone for some users, but sometimes you need access to more robust tools like R. Thankfully there's RExcel to bridge the gap. In this excerpt from Joseph Adler's R in a Nutshell you'll learn how to get safely up and running with RExcel.
If you’re familiar with Microsoft Excel, or if you work with a lot of data files in Excel format, you might want to run R directly from inside Excel. The RExcel software lets you do just that (on Microsoft Windows systems). You can find information about this software at http://rcom.univie.ac.at/. This site also includes a single installer that will install R plus all the other software you need to use RExcel.
If you already have R installed, you can install RExcel as a package from CRAN. The following set of commands will download RExcel, configure the RCOM server, install RDCOM, and launch the RExcel installer:
> install.packages("RExcelInstaller", "rcom", "rsproxy") > # configure rcom > library(rcom) > comRegisterRegistry() > library(RExcelInstaller) > # excecute the following command in R to start the installer for RDCOM > installstatconnDCOM() > # excecute the following command in R to start the installer for REXCEL > installRExcel()
Follow the prompts within the installer to install RExcel.
After you have installed RExcel, you will be able to access RExcel from a menu item. If you are using Excel 2007, you will need to select the “Add-Ins” ribbon to find this menu as shown in Figure 2.4. To use RExcel, first select the R Start menu item. As a simple test, try doing the following:
Enter a set of numeric values into a column in Excel (for example, B1:B5).
Select the values you entered.
On the RExcel menu, go to the item “Put R Var” > “Array.”
A dialog box will open, asking you to name the object that you are creating in Excel. Enter “v” and press the Enter key. This will create an array (in this case, just a vector) in R with the values that you entered with the name
Now, select a blank cell in Excel.
On the RExcel menu, go to the item “Get R Value” > “Array.”
A dialog box will open, prompting you to enter an R expression. As an example, try entering
(v - mean(v)) / sd(v). This will rescale the contents of
v, changing the mean to 0 and the standard deviation to 1.
Inspect the results that have been returned within Excel.
For some more interesting examples of how to use RExcel, take a look at the Demo Worksheets under this menu. You can use Excel functions to evaluate R expressions, use R expressions in macros, and even plot R graphics within Excel.
Learn more about this topic from R in a Nutshell.
R is rapidly becoming the standard for developing statistical software, and R in a Nutshell provides a quick and practical way to learn this increasingly popular open source language and environment. You'll not only learn how to program in R, but also how to find the right user-contributed R packages for statistical modeling, visualization, and bioinformatics.