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

Another important fundamental principle is the way Excel references worksheet cells. Consider again the worksheet shown in Figure 1-8. Each autofilled formula is slightly different from the original. This, remember, is the formula in cell H2:

= D2 + E2 + F2 + G2

After autofill, the formula in H3 is

= D3 + E3 + F3 + G3

and the formula in H4 is — well, you get the picture.

This is perfectly appropriate. You want the total in each row, so Excel adjusts the formula accordingly as it automatically inserts it into each cell. This is called relative referencing — the reference (the cell label) gets adjusted relative to where it is in the worksheet. Here, the formula directs Excel to total up the numbers in the cells in the four columns immediately to the left.

Now for another possibility. Suppose you want to know each row total's proportion of the grand total (the number in H11). That should be straightforward, right? Create a formula for I2, and then autofill cells I3 through I10.

Similar to the earlier example, you start by entering this formula into I2:

=H2/H11

Press Enter and the proportion appears in I2. Position the cursor on the fill handle, drag through column I, release in I10, and — d'oh! Figure 1-12 shows the unhappy result — the extremely ugly #/DIV0! in I3 through I10. What's the story?

FIGURE 1-12: Whoops! Incorrect autofill!

The story is this: Unless you tell it not to, Excel uses relative referencing when you autofill. So, the formula inserted into I3 is not

=H3/H11

Instead, it's

=H3/H12

Why does H11 become H12? Relative referencing assumes that the formula means, “Divide the number in the cell by whatever number is nine cells south of here in the same column.” Because H12 has nothing in it, the formula is telling Excel to divide by zero, which is a no-no.

The idea is to tell Excel to divide all numbers by the number in H11, not by “whatever number is nine cells south of here.” To do this, you work with absolute referencing. You show absolute referencing by adding dollar signs ($) to the cell ID. The correct formula for I2 is

= H2/$H$11

This line tells Excel to not adjust the column and to not adjust the row when you autofill. Figure 1-13 shows the worksheet with the proportions, and you can see the correct formula in the formula bar (the area above the worksheet and below the Ribbon).

FIGURE 1-13: Autofill, based on absolute referencing.

To convert a relative reference into absolute reference format, select the cell address (or addresses) you want to convert, press and hold the Fn key, and then press F4. Fn+F4 is a toggle that switches among relative reference (H11, for example), absolute reference for both the row and column in the address ($H$11), absolute reference for the row-part only (H$11), and absolute reference for the column-part only ($H11). You might have to experiment a bit with this — some keyboards only require F4 (without Fn).

A Mac shortcut for this is Command+T.

Here’s how you do it on the iPad. After you enter a formula in this type of context, like

= H2/H11

iPad suspects what you’re up to and highlights the part you might want to work with a bit more — in this case, H11. Tap on that term to pop up a menu. Choose Reference Type from that menu to open the Reference Type menu, shown in Figure 1-14. Tap the desired reference type — in this case, the first one — and then proceed to autofill column I.

FIGURE 1-14: Changing from relative to absolute reference on the iPad.