Statistical Analysis with Excel For Dummies. Joseph Schmuller

Чтение книги онлайн.

      FIGURE 3-1: Graphing the data in Table 3-1.

Which one would have a greater and more lasting impact? Although the table is certainly informative, most people would argue that the graph gets the point across better and more memorably. (Eyes that glaze over when looking at numbers often shine brighter when looking at pictures.)

      The graph shows you trends you might not see as quickly on the table. (Satellite services rose fastest. Commercial launches, not so much.) Bottom line: Tables are good; graphs are better.

      Graphs help bring concepts to life that might otherwise be difficult to understand. In fact, I do that throughout the book. I illustrate points by, well, illustrating points!

Examining Some Fundamentals

      First of all, Excel uses the word chart instead of graph. Like the graph, er, chart in Figure 3-1, most chart formats have a horizontal axis and a vertical axis. Several other formats (pie, treemap, and sunburst), which I show you later in this chapter, do not. Neither the radar chart nor the box-and-whisker chart (which also appear in this chapter) has a horizontal axis.

      By convention, the horizontal axis is also called the x-axis, and the vertical axis is also called the y-axis.

      Also, by convention, what goes on the horizontal axis is called the independent variable, and what goes on the vertical axis is called the dependent variable. One of Excel’s chart formats reverses that convention, and I bring that to your attention when I cover it. Just to give you a heads-up, Excel calls that reversed-axis format a bar chart. You might have seen the chart shown in Figure 3-1 referred to as a bar chart. So have I. (Actually, I’ve seen it referred to as a bar graph, but never mind that.) Excel calls Figure 3-1 a column chart, so I say columns from now on.

      Getting back to independent and dependent, these terms imply that changes in the vertical direction depend (at least partly) on changes in the horizontal direction.

      Another fundamental principle of creating a chart: Don’t wear out the viewer’s eyes! If you put too much into a chart in the way of information or special effects, you defeat the whole purpose of the chart.

      For example, in Figure 3-1, I had to make some choices about filling in the columns. Color coded columns would have been helpful, but the page you’re looking at shows only black, white, and shades of gray.

      A lot of chart creation skill comes with experience, and you just have to use your judgment. In this case, my judgment came into play with the horizontal gridlines. In most charts, I prefer not to have them. Here, they seem to add structure and help the viewer figure out the dollar value associated with each column. But then again, that’s just my opinion.

Gauging Excel's Graphics (Chartics?) Capabilities

      As I mention in the preceding section, the chart in Figure 3-1 is a column chart. It’s one of many types of charts you can create with Excel. Of all the graphics possibilities Excel provides, however, only a few are useful for statistical work. Those are the ones I cover in this chapter.

      In addition to the column chart, I show you how to create pie charts, bar charts, line charts, and scatterplots.

When you create a chart, you insert it into a spreadsheet. This immediately clues you in that all chart creation tools are in the Charts area of the Insert tab. (See Figure 3-2.)

      FIGURE 3-2: The Charts area of the Insert tab.

      To insert a chart, follow these steps:

      1 Enter your data into a worksheet.

      2 Select the data that go into the chart.

      3 In the Charts area of the Insert tab, select Recommended Charts.The Insert Chart dialog box opens. This dialog box presents Excel’s best guesses for the kind of chart that captures your data. Choose one, and Excel creates a chart in the worksheet.

      4 Modify the chart.Click on the chart, and Excel adds a Chart Design contextual tab and a Format contextual tab to the Ribbon. These tabs allow you to make all kinds of changes to the chart. You can also double-click on a chart element (like an axis or a data point) to open a task pane on the right side of the screen. The task pane enables you to modify the element. If you keep the task pane open and click on another element, the task pane changes to accommodate the newly clicked element and the possible modifications you can make.

      It’s that simple. (Charts seem to get easier with each new version of Excel.) The next section shows what I mean.

By the way, here’s one more important concept about Excel graphics. In Excel, a chart is dynamic: After you create a chart, changing its worksheet data results in an immediate change in the chart.

      

In this example, and in all the ones to follow, Step 3 is always

      Insert | Recommended Charts

      You can, however, directly access a chart type without Excel’s recommendations. The Charts group on the Insert tab provides a button for each chart type. You can also access each chart from the All Charts tab in the Recommended Charts dialog box.

Becoming a Columnist

      In this section, I show you how to create the spiffy graph shown earlier, in Figure 3-1. Follow these steps:

      1 Enter your data into a worksheet.Figure 3-3 shows the data from Table 3-1 entered into a worksheet.FIGURE 3-3: Table 3-1 data, entered into a worksheet.

      2 Select the data that go into the chart.I selected A1:F7. The selection includes the labels for the axes but doesn’t include row 8, which holds the column totals.

      3 In the Charts area of the Insert tab, select Recommended Charts.Choosing Insert | Charts | Recommended Charts opens the Insert Chart dialog box, shown in Figure 3-4. I scrolled down the recommended charts in the left column and selected Excel’s fifth recommendation. (Apparently, Excel’s tastes are a bit different from mine — even in this edition! СКАЧАТЬ