Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 37

Rather than vary a single formula input at a time — as in the one-input data table I discuss in the preceding section — Excel also lets you kick things up a notch by enabling you to set up a two-input data table. As you might have guessed, a two-input data table is one that varies two formula inputs at the same time. For example, in a loan payment worksheet, you could set up a two-input data table that varies both the interest rate and the term.

To set up a two-input data table, you must set up two ranges of input cells. One range must appear in a column directly below the formula, and the other range must appear in a row directly to the right of the formula. Here are the steps to follow:

1 Type the input values:To enter the column values, start the column one cell down and one cell to the left of the cell containing the formula.To enter the row values, start the row one cell up and one cell to the right of the cell containing the formula.Figure 2-4 shows an example.FIGURE 2-4: For a two-input data table, enter one set of values in a column and the other in a row.

2 Select the range that includes the input values and the formula.In the example shown in Figure 2-4, you'd select the range B7:F15.

3 Choose Data ⇒ What-If Analysis ⇒ Data Table to open the Data Table dialog box.

4 In the Row Input Cell text box, enter the cell address of the input cell that corresponds to the row values you entered.In the example shown in Figure 2-4, the row values are term inputs, so the input cell is C3 (see Figure 2-5).

5 In the Column Input Cell text box, enter the cell address of the input cell you want to use for the column values.In the example shown in Figure 2-4, the column values are interest rate inputs, so the input cell is C2 (refer to Figure 2-5).

6 Click OK.Excel displays the results. Figure 2-6 shows the results of the example two-input data table.

FIGURE 2-5: Enter the addresses of the input cells.

FIGURE 2-6: The two-input data table results.

When you run the Data Table command, Excel enters an array formula in the interior of the data table. The formula is a TABLE function (a special function available only by using the Data Table command) with the following syntax:

{=TABLE(row_input_ref, column_input_ref)}

Here, row_input_ref and column_input_ref are the cell references you entered in the Data Table dialog box. The braces ({ }) indicate an array, which means you can't change or delete individual elements in the results. If you want to change the results, you need to select the entire data table and then run the Data Table command again. If you want to delete the results, you must select the entire array and then delete it.