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

Original title: "if you can use Excel to make dynamic charts, why do you have to work late into the night?" "

A dynamic chart is a chart that displays different information in real time according to our choice.

It can change the data from static to dynamic, more vivid and more soulful.

For example, in a chart, you can dynamically show the sales of various goods in different quarters:

In order to know what are the best sellers in different seasons.

There is also a line chart that dynamically displays the cumulative single quantity over time:

Many people think that dynamic charts are very advanced and mysterious.

But in fact, its production principle is also very simple!

Let's talk about how the two charts mentioned above are made in Excel.

1. Dynamic chart with combo box first look at the method of making the first chart, which uses the control [combo box]. The specific operations are as follows:

❶ clicks the menu bar, finds the Development tools tab, inserts the form control, and selects the combo box.

Drag the mouse to add a combo box as shown.

(there is no [Development tools] tab, find and add in [File]-[options]-[Custom Ribbon]. )

❷ right-click * * ComboBox * *, select * * format Control * *, and set it in * * Control * *:

Data source region selection: $B$3:$B$6

Cell link: $Back8

To make the combo box more three-dimensional, check [3D Shadow].

When the settings are complete, click the OK button to close the dialog box.

❸ uses functions to reference the corresponding data.

Select the "C8:G8" area and enter the formula in the edit bar:

The function of Offset is mainly used as the offset region: the first parameter "C2:G2" is the area to be offset, and the second parameter "B8" is the number of rows offset. Press the [Ctrl+Shift+Enter] triple key, and the result is the source of the mapping data.

Finally, you will find that if you select the corresponding option in the list of the combo box, the corresponding number will appear in the B8 cell.

❹ holds down the [Ctrl] key, selects the "C2:G2" and "C8:G8" cell ranges, and inserts the chart.

After ❺ sets up the chart [at the bottom], drag the combo box to the appropriate place on the chart and group them together.

❻ finally modifies the chart title, deletes vertical axes, and adds data labels to beautify the chart.

2, scroll bar to control the time of the dynamic chart ❶ first click the menu bar, find the "Development tools" tab, [insert] form controls, and select "Scroll Bar".

As shown in the figure, drag the mouse, add a scroll bar, and select the E1 cell through the cell link.

❷ enters the formula in the D3 cell:

= IFROW (A1) = $Epist1 B3 Magi na () then drop down to the right.

The function of ROW (A1) is to copy down the formula to get a sequence number that is automatically incremented from 1. Compare the sequence number with the value of $Equi1, and use the IF function to determine whether the original data is returned or not.

❸ select the "D3:E23" cell range and insert a scatter chart with straight lines.

Come on! Guys! Pick up the mouse and click with me! Point! Point!

The dynamic graph is considered to be formed.

3. Summary the two kinds of charts we are talking about today are dynamic charts made by inserting controls into Excel:

❶ first adds the control and sets the linked cell

❷ then uses the linked cell as a parameter in the function formula to establish an auxiliary column

❸ select the secondary column and insert the chart.

In this way, a dynamic chart controlled by the control is made.

Dear friends, have you learned the method of making Excel visual dynamic charts?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang

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.