Statistical Analysis with Excel For Dummies. Joseph Schmuller
Читать онлайн книгу.
FIGURE 3-21: A bar chart of the data in Table 3-2.
To create this graph, follow these steps:
1 Enter your data into a worksheet.Figure 3-22 shows the data entered into a worksheet.
2 Select the data that go into the chart.For this example, the data are cells A1 through B8.
3 Choose Insert | Recommended Charts from the main menu and then choose the chart you like from the list on the left side of the screen.I selected the first option: Clustered Bar. Figure 3-23 shows the result.
4 Modify the chart.The first modification is to change the chart title. One way to do this is to click the current title and type the new title. Next, I add the axis titles. To do this, I click the Chart Elements button, that button labeled with a plus sign (+). Selecting the Axis Labels check box on the menu that appears adds generic axis titles, which I then change. Finally, I bold the font on the axis titles as well as the axis numbers. The easiest way to do that is to select an element and press Ctrl+B.
FIGURE 3-22: Table 3-2 data in a worksheet.
FIGURE 3-23: The initial Excel bar chart.
The Plot Thickens
You use an important statistical technique called linear regression to determine the relationship between one variable, x, and another variable, y. For more information on linear regression, see Chapter 14.
The basis of the technique is a graph that shows individuals measured on both x and y. The graph represents each individual as a point. Because the points seem to scatter around the graph, the graph is called a scatterplot.
Suppose you're trying to find out how well a test of aptitude for sales predicts salespeople's productivity. You administer the test to a sample of salespersons and you tabulate how much money they make in commissions over a 2-month period. Each person's pair of scores (test score and commissions) locates that person within the scatterplot.
To create a scatterplot, follow these steps:
1 Enter your data into a worksheet.Figure 3-24 shows the entered data.FIGURE 3-24: Your scatterplot data.
2 Select the data that go into the chart.In the background of Figure 3-25, you can see the selected cells — B2 through C21. (Including B1 creates the same chart, but with an incorrect title.) The cells in Column A are just placeholders that organize the data.
3 Choose Insert | Recommended Charts from the main menu and then select the chart type from the list on the left of the screen.I chose the first option, resulting in the chart shown in Figure 3-25.
4 Modify the chart.I clicked the generic chart title and typed a new title. Next, I clicked the Chart Elements tool (labeled with a plus sign) and used the resulting menu to add generic axis titles to the chart. I then typed new titles. Finally, I selected each axis title and typed Ctrl+B to turn the font bold. I did that for the chart title, too. The result is the scatterplot in Figure 3-26.
FIGURE 3-25: The initial scatterplot.
FIGURE 3-26: The almost-finished scatterplot.
For the other graphs, that would just about do it, but this one's special. Right-clicking any of the points in the scatterplot opens the pop-up menu shown in Figure 3-27.
FIGURE 3-27: Right-clicking any point on the scatterplot opens this menu.
Choosing Add Trendline opens the Format Trendline pane. (See Figure 3-28.) I selected the Linear radio button (the default) and clicked the two check boxes at the bottom. (You have to scroll down to see them.) They’re labeled Display Equation on Chart and Display R-Squared Value on Chart.
FIGURE 3-28: The Format Trendline pane.
Clicking the Close button closes the Format Trendline pane. A couple of additional items are now on the scatterplot, as Figure 3-29 shows. A line passes through the points. Excel refers to it as a trendline, but it's more accurately called a regression line. A couple of equations are there, too. (For clarity, I dragged them from their original locations.) What do they mean? What are those numbers all about?
FIGURE 3-29: The scatterplot, with additional information.
Read Chapter 14 to find out.
Finding Another Use for the Scatter Chart
In addition to the application in the preceding section, you use the scatter chart to create something like a line chart. The conventional line chart works when the values on the x-axis are equally spaced, as is the case for the data in Table 3-1.
Suppose that your data look like the values in Figure 3-30. Veterans of earlier Excel versions (Excel 2010 and before) might remember having to figure out that a scatter chart with lines and markers was the best way to visualize these data.
Excel now figures this out for you. Selecting the data and choosing Insert | Recommended Charts presents the Scatter with Straight Lines and Markers as the first option — although Excel labels it simply as Scatter. (See Figure 3-31.) This puts you on the road to the appropriate chart.
FIGURE 3-30: These data suggest a line chart, but the x-values aren’t equally spaced.