Читать книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller - Страница 32

Excel has a set of sophisticated tools for data analysis. They reside in the Analysis ToolPak. This ToolPak isn't available for the iPad, but a similar package, the XLMiner Analysis ToolPak, is. I mention it in the next section.

Table 2-1 lists the ToolPak tools I cover. (The one I don’t cover, Fourier Analysis, is extremely technical.) Some of the terms in the table may be unfamiliar to you, so I define them throughout this book.

TABLE 2-1 Excel's Data Analysis Tools

Tool	What It Does
Anova: Single Factor	Performs analysis of variance for two or more samples.
Anova: Two-Factor with Replication	Performs analysis of variance with two independent variables, and multiple observations in each combination of the levels of the variables.
Anova: Two-Factor without Replication	Performs analysis of variance with two independent variables, and one observation in each combination of the levels of the variables; It’s also a repeated measures analysis of variance.
Correlation	With more than two measurements on a sample of individuals, calculates a matrix of correlation coefficients for all possible pairs of the measurements.
Covariance	With more than two measurements on a sample of individuals, calculates a matrix of covariances for all possible pairs of the measurements.
Descriptive Statistics	Generates a report of central tendency, variability, and other characteristics of values in the selected range of cells.
Exponential Smoothing	In a sequence of values, calculates a prediction based on a preceding set of values and on a prior prediction for those values.
F-Test Two-Sample for Variances	Performs an F-test to compare two variances.
Histogram	Tabulates individual and cumulative frequencies for values in the selected range of cells.
Moving Average	In a sequence of values, calculates a prediction which is the average of a specified number of preceding values.
Random Number Generation	Provides a specified amount of random numbers generated from one of seven possible distributions.
Rank and Percentile	Creates a table that shows the ordinal rank and the percentage rank of each value in a set of values.
Regression	Creates a report of the regression statistics based on linear regression through a set of data containing one dependent variable and one or more independent variables.
Sampling	Creates a sample from the values in a specified range of cells.
t-Test: Two Sample	Provides three t-test tools that test the difference between two means: One assumes equal variances in the two samples; another assumes unequal variances in the two samples; the third assumes matched samples.
z-Test: Two Sample for Means	Performs a two-sample z-test to compare two means when the variances are known.

The ToolPak is an add-in. To use it, you first have to load it into Excel. To start off that process (in the Windows version), choose File | Options from the main menu. Doing this opens the Excel Options dialog box. From there, follow these steps:

1 In the Excel Options dialog box, select Add-Ins from the navigation menu on the left.Oddly enough, this step opens a list of add-ins.Near the bottom of the list, you see a drop-down list labeled Manage.

2 From this list, select Excel Add-Ins, if it’s not already selected.

3 Click Go.This step opens the Add-Ins dialog box. (See Figure 2-24.) The items in your dialog box are probably different from the ones in mine. That’s okay.

4 Select the check box next to the Analysis ToolPak option as well as the check box next to the Solver Add-In option and then click OK.

When Excel finishes loading the ToolPak and the Solver add-ins, you find the Data Analysis button and the Solver button in the Analyze area of the Data tab. It’s all the way on the right.

The installation procedure for the Analysis ToolPak on the Mac is much simpler than the one for Windows.

FIGURE 2-24: The Add-Ins dialog box.

To begin, choose Tools | Excel Add-ins from the main menu. (Figure 2-25 shows this menu choice.)

FIGURE 2-25: The Tools | Excel Add-ins menu choice on the Mac.

This opens the Add-Ins dialog box. (See Figure 2-26.) Select the check box next to Analysis ToolPak and the checkbox next to Solver Add-In then click OK. That’s it. Data Analysis… and Solver… then appear as choices on the Tools menu.

FIGURE 2-26: The Add-Ins dialog box on the Mac.

Follow these general steps for using a ToolPak data analysis tool:

1 Enter your data into an array.

2 Choose Data | Data Analysis from the main menu to open the Data Analysis dialog box.

3 In the Data Analysis dialog box, select the data analysis tool you want to work with.

4 Click OK (or just double-click the selection) to open the dialog box for the selected tool.

5 In the tool's dialog box, enter the appropriate information.I know this step is vague, but each tool is different.

6 Click OK to close the dialog box and see the results.

Here's an example to get you accustomed to using these tools. In this example, I put the Descriptive Statistics tool through its paces. This tool calculates a number of statistics that summarize a set of scores.

1 Enter your data into an array.Figure 2-27 shows an array of numbers in cells B2 through B9, with a column header in B1. The array is on a worksheet tab called Descriptive Statistics.

2 Choose Data | Data Analysis from the main menu to open the Data Analysis dialog box.

3 Click Descriptive Statistics and then click OK (or just double-click Descriptive Statistics) to open the Descriptive Statistics dialog box.FIGURE 2-27: Working with the Descriptive Statistics tool.

4 Identify the data array.In the Input Range box, enter the cells that hold the data. In this example, that's B1 through B9. The easiest way to do this is to move the cursor to the top cell (B1), press the Shift key, and click in the bottom cell (B9). That puts the absolute reference format $B$1:$B$9 into the input range field.

5 Select the Columns radio button to indicate that the data are organized by columns.

6 Select the Labels in First Row check box because the input range includes the column heading.

7 Select the New Worksheet Ply radio button, if it isn’t already selected.This step tells Excel to create a new tabbed sheet within the current worksheet and to then send the results to the newly created sheet.

8 Select the Summary Statistics check box and leave the others deselected.

9 Click OK.The new tabbed sheet (ply) opens, displaying statistics that summarize the data. Figure 2-28 shows the new ply, after you widen column A.

For now, I don't tell you the meaning of each individual statistic in the Summary Statistics display. I leave that for Chapter 7, where I delve more deeply into descriptive statistics.

FIGURE 2-28: The output of the Descriptive Statistics tool.