Like and see, form a habit; Sincerity smooth the way to success.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
Speaking of using drop-down lists in combination with line charts, have you thought of any scenarios where this might work? In fact, there are many use scenarios, for example, we recorded a class of students last year each month test results, now we analyze the trend of each student last year’s academic performance, and then targeted to develop learning plans. In fact, what we want to see at this time is the trend chart of each student’s performance, rather than the whole class of all students in the same table. Here we can use a combination of drop-down list and line chart, drop-down list to select the student to display the trend of that student’s performance chart.
Today I’m going to share with you how to make such a dynamic line chart.
In order to facilitate the demonstration, we have prepared the score statistics table of some students in the first four months of 2020.
1. Insert line chart
First, select data B2:E10 cell area, and click “Line chart with data marked” in the “Insert Line chart or Area Chart” drop-down list of “2D Line Chart” in the “Insert Line Chart or Area Chart” command group under the “Insert” TAB.
Then select the chart, right-click to bring up the right-click menu, and select “Select Data” to bring up the Select Data Source dialog box.
At this point, each line chart is the result of all students in each month. We click “Switch row/column” to achieve a line chart is the result of a student in 4 months. Then delete other series in “Legend Item (series)” and keep “Series 1” only.
2. Insert the drop-down list
Click the “combo box” in the “Control” command group under the “Development Tools” TAB under the “Insert” drop-down list under the “Form Control”, and then move the cursor to the cell area, the cursor becomes a small black cross, and then hold down the left mouse button in the appropriate position to draw down the list control.
Right – click the drop – down list to bring up the right – click menu and choose Set Control Format to bring up the Set control format dialog box. And go to the “Control” TAB, where “Data Source area” select “AAA3: AAA10”, that is, select the name area for the drop-down list to display the name; Select “EEE1” for “cell Link” to associate drop-down list data with line chart data for the following purposes.
3. Associate drop-down list and line chart data
First let’s take a look at what the contents of the “EEE1” cell selected by “Cell Link” in the drop-down list above show.
That is, the “EEE1” cell shows the number of elements selected in the drop-down list. Then we combine the OFFSET() function to define a formula “=OFFSET(AAA2, EEE1,1,1,4)”. When we switch the drop-down list, this formula will return the corresponding student’s test scores for all months.
For a brief explanation, we input the function group “{=OFFSET(AAA2, EEE1,1,1,4)}” in cell A13:D13. After selecting Liu Jiajie in the drop-down list, cell E1 is 5, and the function group means that the A2 cell is OFFSET 5 rows down and 1 column to the right. Return 1 row and 4 columns, so return B7:E7 cell area data.
So now that we’ve got the drop-down list and the associated data, the next thing we need to do is present the associated data in a line chart. Of course we can’t associate the formula with the line chart directly, but we can assign the formula “=OFFSET(AAA2, EEE1,1,1,4)” to a name by defining the name, and then associate the name with the line chart.
Then click “define name” under the “formula” TAB to bring up the new name dialog box, then enter the name “result”, reference position “=OFFSET(AAA2, EEE1,1,1,4)”, and press the ok button.
Then right-click the chart to bring up the right menu, select “Select data”, select “Series 1” and click the Edit button. In the edit Data series dialog box, change the series value to “= Score statistics! Score”, i.e. “worksheet name +!+ name”, which is the name defined above.
The main function is done here.
4, line diagram simple beautification
Delete the following legend item in the chart and change the title to “Monthly exam Result Trend Chart”. Then double click the ordinate of the table to bring up the “Set axis Format” window. Set the maximum boundary in the axis option to 100 and the unit is 20.
Then click the “Change Color” dropdown in the “Chart Style” command group under the “Design” TAB and select the color you like.
Finally, add data labels to the line chart by clicking “below” in the “Add Chart Elements” drop down list of “Data Labels” in the “Chart Layout” command group under the “Design” TAB. Of course, you can also set the font color of the data label.
Here’s a look at the demo:
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.