Create either one-variable or two-variable data tables, depending on the number of variables and formulas that you need to test. Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a one-variable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function.
You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row. Use a two-variable data table to see how different values of two variables in one formula will change the results of that formula.
For example, you can use a two-variable data table to see how different combinations of interest rates and loan terms will affect a monthly mortgage payment. Whenever a worksheet recalculates, any data tables will also recalculate—even if there has been no change to the data.
To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not the data tables. To learn more, see the section Speed up calculation in a worksheet that contains data tables.
A one-variable data table contain its input values either in a single column column-oriented , or across a row row-oriented. Any formula in a one-variable data table must refer to only one input cell. Type the list of values that you want to substitute in the input cell—either down one column or across one row. Leave a few empty rows and columns on either side of the values. If the data table is column-oriented your variable values are in a column , type the formula in the cell one row above and one cell to the right of the column of values.
This one-variable data table is column-oriented, and the formula is contained in cell D2. If you want to examine the effects of various values on other formulas, enter the additional formulas in cells to the right of the first formula.
If the data table is row-oriented your variable values are in a row , type the formula in the cell one column to the left of the first value and one cell below the row of values. If you want to examine the effects of various values on other formulas, enter the additional formulas in cells below the first formula. Select the range of cells that contains the formulas and values that you want to substitute. In the figure above, this range is C2:D5. If the data table is column-oriented, enter the cell reference for the input cell in the Column input cell field.
In the figure above, the input cell is B3. If the data table is row-oriented, enter the cell reference for the input cell in the Row input cell field. Note: After you create your data table, you might want to change the format of the result cells. In the figure, the result cells are formatted as currency.
If the data table is column-oriented, enter the new formula in a blank cell to the right of an existing formula in the top row of the data table.
If the data table is row-oriented, enter the new formula in a empty cell below an existing formula in the first column of the data table. If the data table is column-oriented, enter the cell reference for the input cell in the Column input cell box.
If the data table is row-oriented, enter the cell reference for the input cell in the Row input cell box. A two-variable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.
Select the range of cells that contains the formula C2 , both the row and column of values C3:C5 and D2:E2 , and the cells in which you want the calculated values D3:E5. In the Row input cell field, enter the reference to the input cell for the input values in the row.
Type cell B4 in the Row input cell box. In the Column input cell field, enter the reference to the input cell for the input values in the column. Type B3 in the Column input cell box. A two-variable data table can show how different combinations of interest rates and loan terms will affect a monthly mortgage payment.
When you set this calculation option, no data-table calculations occur when a recalculation is done on the entire workbook. To manually recalculate your data table, select its formulas and then press F9. In the Calculation options section, under Calculate , click Automatic except for data tables.
You can use a few other Excel tools to perform what-if analysis if you have specific goals or larger sets of variable data. Using Solver to determine the optimal product mix. Define and solve a problem by using Solver. Analysis ToolPak Add-in. Overview of formulas in Excel.
How to avoid broken formulas. Detect errors in formulas. Keyboard shortcuts in Excel. Excel functions alphabetical. Excel functions by category. Changing cells 2. Result cell 1. Result cell If several people have specific information in separate workbooks that you want to use in scenarios, you can collect those workbooks and merge their scenarios. Cells B1, B2, and B3 are the values for the loan amount, term length, and interest rate.
Use Data Tables to see the effects of one or two variables on a formula. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen.
Incorrect instructions. The selected cell range is converted into a Table. To accept the selected cell range for your Table , click OK. To convert a Table back to a data range: Select a cell within the Table. From the scroll box, select Form Click Add.
Select a cell within the Table. Click New. The form is cleared and ready for you to enter a new record. In each text box, type the desired information. To move between fields, press [Tab].
Editing a Record Access the Form dialog box. In the appropriate Form dialog box text boxes, make the desired changes.
When finished, click Close. Deleting a Record Access the Form dialog box. Click Delete. A confirmation dialog box appears. Click Clsoe. The record is permanently deleted from your database. Searching for a Record The Criteria feature allows you to search according to desired criteria. Click Criteria. In the appropriate field s , type your search criteria. Click Find Next or Find Prev. Repeat steps as necessary.
Click Close. Using the Worksheet Window If you need to make only minor changes in your Table , it may be quicker to make them in the worksheet view.
0コメント