Excel has a powerful built-in formulas. But sometimes when doing data analysis, we want to see in the input value is different, the results of the formula and what will happen to change; sometimes we want to repeat a certain operation, and finally see the results of the operation.

Although a similar effect can be achieved by fixing the formula with the "$" sign and then dragging, it is too cumbersome. If you locate the wrong formula, it will lead to the entire data table calculation results in error. So what is a fast and accurate way to perform simulation operations?

The simulation table is a shortcut to operate this type of simulation inside Excel. The simulation table feature can be found in Data > Predictive Analytics > Simulation Tables.

Simulation of the operation of the table is very simple and requires only two parameters: reference rows of cells and reference columns of cells. The meaning and usage of these two parameters will be explained later in the example.

The basic principle of the analog table is to write the formula in the upper left corner of the table, and then change the line corresponding to the "reference to the row of cells", and change the column corresponding to the "reference to the column of cells", and finally the results will be output to the matrix of the table.

If you only want one variable to change in a simulation, then a single-variable simulation table is appropriate.

Take a loan calculation, for example. Suppose you want to calculate a loan of $20,000 with 3 years of equal monthly payments. Now you want to know what happens to the monthly repayments at different interest rates.

Using Excel's PMT formula, you can quickly come up with the results for one scenario:

Then we want to change the value of the interest rate to see how much the repayments would be at different interest rates. Using an analog worksheet, you can create a table of interest rate changes as follows. Note that the first row is left blank so that the formula can be referenced.

In the first row of the simulation table, quote the formula you just used.

In the "Repayment Amount" column in the first line with "=" just quote the PMT formula:

Or enter the formula directly

In short, in the simulation of the first line of the arithmetic table, there must be a reference to other cell's formula, otherwise Excel will not know what parameters you want to change Oh.

(1) Select the table. This step is very easy to go wrong, the general policy is, pay attention not to select the title line, pay attention not to select the title line, pay attention not to select the title line! Because the title line is what we use to visualize, Excel in the calculation and can not be included in the calculation of the text, so do not select the text Oh;

(2) data & gt; predictive analytics & gt; simulation of arithmetic tables;

(3) because we put the interest rate change in the columns, and therefore, in the " Cells of the referenced column" in the PMT formula to select the interest rate;

(4) point OK, to generate the simulation worksheet.

Done!

If the table is transposed, then the parameters should be filled in the reference row of cells.

Still the loan example, this time introducing a new variable. We want to know how the repayment amount per installment will change if the repayment term and the interest rate change at the same time.

In the case of a two-variable scenario, the operation is the same as in the case of a single variable. So this time we simplify the steps a bit to get results faster.

As shown in the figure. We take the rows of the matrix as parameters for the change in the repayment term and the columns as parameters for the change in the interest rate.

In the upper left corner of the table, enter the PMT formula we want to calculate:

Note that this "upper left corner" is very important, the formula must be written at the intersection of the rows and columns.

Two-factor arithmetic follows the same steps as one-factor arithmetic.

(1) Select the table. Note the range of boxes

(2) Data > Predictive Analytics > Simulation Arithmetic Table

(3) Because the rows represent the change in repayment term and the columns represent the change in interest rate. Therefore, in the "quote line" fill in the quote of the repayment period, in the "quote column" fill in the change of interest rate

(4) point OK, generate simulation arithmetic table

Completed!

Due to different computer performance, some computers may take longer to calculate the bivariate simulation. At this point, do not point the left mouse button or press the Esc key to interrupt the calculation, and so on, when Excel calculations are complete, the results will automatically be presented in the table.

If there are a lot of "0" values in the calculation results, especially in front of the data are normal, the back of the data appeared a large number of "0", it may be due to a misuse of the interruption of the Excel calculation. At this point, please delete the form data, recalculate.

In addition to changing the formula variable simulation, analog operation table can help us do what? In fact, there is also a "hidden function" of the analog operation table, that is, repeat the analog operation.

Suppose we have a dice game where two people compete to see who has the most points. The person with the larger number of points wins a monetary prize. This dice rolling process is repeated 100 times, and we want to use Excel to generate a simulation of the results of each contest, and finally figure out how much money A won.

We can choose to drag the formula to repeat the simulation 100 times, but what if 1000 times, 10,000 times? Simulations with larger amounts of data are tougher to do manually. This repeated simulation process can also be done with a simulation arithmetic table.

First, let's assume we have two dice. With the RANDBETWEEN() function you can get a new random number with every refresh (Excel's refresh key is F9).

After that, we generate a sequence of 1-100. An easy way to generate a sequence is to first enter the first value of the sequence and keep it selected:

Start > Fill > Sequence

In Sequence, select Generate by Columns, Equivariant Type, Step Size, and Terminating Value.

Confirm and it is straightforward to get a sequence of 1-100.

After completing the sequence, we enter a judgment statement on the first line.

Be sure to enter a blank line before the 1, because the analog table does not take into account the word "sequence".

After that, follow the steps for single-variable simulation, selecting the op table and then choosing Simulate Op Table. In the input parameters, find a blank cell, fill in the "reference to the column of cells" (because our sequence of times in the column)

Confirmation, you can finish! After that, you can use the simulation results for statistical analysis.