Dynamic data generation in Excel

For example, I want to dynamically retrieve the data for each quarter. Let's take Q1 as an example!

How Excel implements dynamic data.

Copy a1:a4 to a7:a10, because the first quarter is called, so the "b7" value is set to "1" first;

How Excel implements dynamic data.

The next step is to use the index function to solve the problem;

How Excel implements dynamic data.

The index function is generally to find the value of an intersection within a range. The function formula is "=index(range, row number, column number)";

How Excel implements dynamic data.

So here, the formula for b8 is "=index(b2:e4,1,b7)". where b2:b4 is the selection range, 1 is the first row in the range, b7=1, so it is the first column;

How Excel implements dynamic data.

In the same way, we can get a single indicator for the first quarter by changing the line number of the formula slightly;

How Excel implements dynamic data.

In addition, by changing the value of "b7", i.e. column number, data for other quarters can be obtained. For example, when you change 1 to 2, the data for the second quarter appears, and so on.

How Excel implements dynamic data.

Copy the Excle template, select "Paste Special" in Word, select the "Paste Link" button in the "Paste Special" dialog box, and you can achieve it at this time, and update it synchronously!

In Excel, there are several ways to automatically generate data:

1. Formula method, as far as your problem is concerned, you can use the following method B1 to enter.

a1*, then pull down, or double-click the fill handle.

d1＝b1*sum(c:c)

2. Random number method, enter the formula =rand() in the space, and the required data can be randomly generated.

3. The procedural method is to write a small program with VBA, generate some required data by the program, and fill in the results in the appropriate position.

Method steps.

First of all, you need to add an add-in in Excel - the data analysis library, and then you can automatically generate the data, which is called "random number generator" in professional terms. Click Excel Options > Add-ins > Go to add work to the Analysis Tool Gallery.

After the analysis tool library is added, on the "Data" tab of Excel, there will be an additional menu bar of "Analysis" on the far right, click "Data Analysis".

Select Random Number Generator under Data Analysis.

On the pop-up interface, set the parameters for the random number generator interface. For example, if you set the number of variables = 5 and the number of random numbers = 10, then you will generate a data with 5 columns and 10 rows.

Next, select the distribution type of random data, take "normal distribution" as an example, and set the mean = 50 and standard deviation = 5.

Select the output location of the data, you can select the region, create a new worksheet, create a new workbook, in this case, take the selected region as an example, and choose to export the data to: a1:e10 region.

The data output was successful

The data source of the graph is dynamic and automatically displayed in the graph in Excel as the data is entered

Middle. ok!!!May it help you.

Insert the chart directly and select the style.

Most people know how to make charts, but it can be difficult for many people to make dynamic charts, but here we talk about a very simple and straightforward one.

Set conditional formatting with column b selected. The formula for conditional formatting can be:

a1=$d$1

Take a look at the example diagram above:

Select the area you want to format, or select the entire column b, click Conditional Formatting--- create a new rule at the top--- use the formula to determine the cells you want to format, and then enter this formula in the formula bar:

a1="fu"

Then click the Format button to set the format.

Finally, click OK.

1. If cell A1 is empty, cell B1 is F, cell C1 is H, cell D1 is K, and you want to generate 0 in cell A2, 1 in cell B2, 2 in cell C2, and 3 in cell D2, you can use the if function.

Step: Inside cell A2, type: "=if(a1="",0,if(a1="f",1,if(a1="h",2,if(a1="k",3,4))) Press enter, you can get 0 in cell A2; Then use automatic dragging, which can be generated in B2, C2, and D2 respectively

2. If you want to have more correspondings, then you can follow the steps below:

a.First, in a new sheet (the sheet name is "corresponding table"), write the corresponding items, such as enter "m" in cell A1 and "0" in cell B1; Enter "F" in cell A2 and "1" in cell B2; Enter "3" in cell A3 and "3" in cell B3......And so on.

b.In a new sheet, enter the string you need in cell A1, such as "mf30lab", and use the column (data - column) wizard (with fixed width) to sort the string into different cells, the result is: A1 cell - M, B1 cell - F, C1 cell - 3, D1 cell - 0......

c.Enter the vlookup function in cell A2: "=vlookup(A1, corresponding to the table!")

a:$b,2,false)", press enter, you can generate "0" in the A2 cell, and with auto drag, you can generate 1,3,0,2,1 in B2,C2,D2,E2,F2,G2

d.Finally, enter "=a2&b2&c2&d2&e2&f2&g2" in cell h2 to get the corresponding string "013021".

The randomly generated data formula =rand() produces a random number between 0-1.