Shulou(Shulou.com)11/24 Report--

The original title: "who says it's hard to make a chart move?" I have a trick. I can do it in three steps. I can't learn how to hit me. "

The company is going to have a business analysis meeting soon, and the boss needs help to make a chart. The demand is as follows:

The sales volume, amount and profit data of a total of six stores are required to be presented in a bubble chart.

PS: a bubble chart is a chart showing the relationship between three variables. The horizontal axis, the vertical axis, and the size of the bubble represent a variable.

In the following data, the mobile phone sales data of each store have three indicators: sales volume, amount and profit.

Therefore, it is more appropriate to use the bubble diagram to present.

A chart made by ordinary people might look like this:

Since it is the data of six stores, each store has to make a similar chart, with a total of six bubble charts. Wow, look.

Colleagues who are well versed in dynamic graph making may make a chart like this:

With only one chart, the mobile phone performance information of multiple stores is displayed.

What kind of plan would you Pick if you were the boss?

I think many people can't help but choose the second kind.

Because the dynamic chart uses controls, it is more interactive, and the data of which store the boss wants to see can be presented with a direct mouse click.

So next, let's learn how to make such a chart with the help of functions and controls.

Because the purpose of this tutorial is to let you master the principle of making dynamic diagrams, the cases in the text are appropriately simplified and easier to understand.

The data sources used are as follows:

The final effect is as follows:

01, dynamic chart principle before learning how to operate, let's first understand the principle of making dynamic charts with the help of functions and controls.

❶ inserts the line chart through H1:H5, and the change of data in H2:H5 leads to the change of line chart.

The data in ❷ H2:H5 is fetched from the B2:E5 area with the help of a function, and one of the parameters of the function uses the A7 cell.

The numerical change of ❸ A7 cell leads to the change of function value, which in turn leads to the change of H2:H5 region data.

❹ controls are linked to A7 cells, and different controls are selected, resulting in changes in A7 cell values.

02, operating steps to understand the working principle behind the dynamic chart, we can make a dynamic chart through the following three steps.

▌ inserts the basic chart first copies the B2:B5 data into the H2:H5, and inserts the basic line chart according to the H2:H5 data.

The ▌ insert control step uses the insert Control command in the Development tools tab.

If you do not have this tab in the ribbon, you need to call [Development tools] through [File]-[options]-[Custom Ribbon].

As shown below:

You can then insert a control for the chart: the option button (form control) and set the properties of the control to link it to any cell (here linked to the A7 cell).

Warm Tip: when selecting a control, it is more convenient to select it by right-clicking than clicking the left button.

Then change the names of the four controls to the names of four stores: Wangfujing, Wangjing, Xidan and Zhongguancun (this step is no longer demonstrated).

When you click on the control at this time, the value of the A7 cell will change from 1 to 4.

The moving picture is shown as follows:

When we do this, we find that the line chart has not changed.

This is because the data region referenced by the line chart is H2:H5, and the data in this area, which is still the value of the B2:B5 region copied in step ❶, is fixed.

▌ with the help of function dynamic reference data if you want to make the chart "move", you need to use the function to make H2:H5, according to the change of A7 cell value, dynamically reference the value of the B2:E5 area.

Next, we introduce three functions (OFFSET, CHOOSE, INDEX) to achieve this purpose, and you can master one of them.

❶ with the help of OFFSET function

= OFFSET with A2 as the reference, move down 0 rows, move 4 columns (the value of A7 cells) to the right, take 4 rows and 1 column.

This function means that the value of H2 takes A2 as a reference, moving down 0 rows refers to taking the current row (the row where A2 is located), moving 4 columns to the right, to the E2 cell, and taking 4 rows and 1 column refers to taking out the value of the E2:E5 area.

Thus the value of H2:H5 is taken out through this function.

With the selection of the control button, the value of the A7 cell changes, which makes the value taken out by H2:H5 change, which leads to the dynamic change of the chart.

The operation is demonstrated as follows:

❷ with the help of CHOOSE function

Using the CHOOSE function can also achieve the same effect as the OFFSET function, the difference lies in the use of the function.

= CHOOSE ($Aqi7 B2PowerC2PowerE2) refers to taking the value of the cell from the B2Magazine C2PowerE2. Which one should be taken is determined by the value of the 1st parameter. The current value of A7 is 4. This formula means: return the value of the 4th (that is, the value of the E2 cell) in the B2MagneC2menD2E2.

The other operations are exactly the same as those of OFFSET, and then only demonstrate the operation of the CHOOSE function:

❸ with the help of INDEX function

We can also use the INDEX function to get the number, except that the function is different, the other operations are the same as above.

= INDEX (B2Glue Egraine A7) takes a number from the range of B2:E5 cells, and the second parameter refers to the number of rows, where this parameter is empty, which means that each line should be taken out. The last parameter represents the column, where A7 is used, and if the value is 2, it represents the second column of the region.

So the value of C2:C5 is taken out through this function.

The operation is demonstrated as follows:

These are the tutorials for making dynamic charts with the help of functions and controls.

Finally, let's review the steps of making a dynamic chart.

❶ inserts the underlying chart

❷ insert control and link it to the same cell

❸ refers to data dynamically with the help of functions (this article introduces three functions, OFFSET, CHOOSE and INDEX, and you can master one of them).

Have you mastered the practice of dynamic charts?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: song Zhenzhong Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.