Guidance: In their daily work, leaders often like to study the trend of a certain thing over time, and they tend to present it in meetings. For example, the production manager pays attention to the change of the workshop output, the sales manager pays attention to the increase or decrease of the sales amount, and the quality manager pays attention to the change of the product qualification rate.
Problem introduction: Xiao Zhang is the new operations specialist of the company's sales department, and the manager asked her to analyze the sales from January to June 2020 on a weekly basis and make a dynamic trend chart in order to study the changes and recovery of the company's performance in a certain continuous period of time under the influence of the epidemic.
In this regard, the leader also put forward three requirements: (1) the use of a column chart to show; (2) the chart needs to dynamically display the sales volume data of any time period; (3) the title needs to dynamically display the average sales volume of the research period.
This is difficult to spoil Xiao Zhang, I think I just came to the company, the leader has arranged such a heavy responsibility, can not mess up ah, the data can be summarized, the column chart is also easy to do, but in a chart, dynamically reflect the sales trend of different time periods, but it is difficult to spoil Xiao Zhang, suddenly lost interest in dinner, worried about insomnia. If you are Xiao Zhang and encounter such difficulties in your work, how will you solve them? Think about it, leave a message to tell us!
No matter how difficult the road is, it must be walked step by step, and even the hardest bones must be chewed down in one mouth! Content with the moment, starting with a single step, Xiao Zhang began to work while cheering himself up.
First of all, the first step: Xiao Zhang summarized the weekly sales in the first half of the year according to the previous monthly daily sales data.
Then the second step: Xiao Zhang stared at the computer with two eyes and made a fool of himself, countless horses in his heart were galloping, and countless small insects in his brain were nibbling on them, it was difficult! At this moment, the savior appeared, and colleague Xiao Tan asked, "What, Zhang, what suffering have you encountered?" "Oh, it's like this" ..."Well said, a beer fried chicken, I tell you"
Idea: Using the name manager under the formula, create two dynamically changing name regions, which can automatically transform the selected data source according to the time we choose. The two names are used to get the time required and the sales, and then through the analysis of the two names, the sales trend is studied.
1. First, as shown above, enter the time period, title, start and end time of the study in E2:H3.
Where the research data entered in G2 and H2 begins in the first few weeks and ends in the first few weeks, the formula "=H3-G3+1" is entered in E2 to obtain the number of weeks of the study, about the title in F2, which is described in detail later.
2. Click "Name Manager" under the "Formula" tab, pop up the "Name Manager" dialog box, click New, and pop up the "New Name" dialog box. Enter Y at Name in the dialog box, the formula =OFFSET ($B$3, $G$3-$A$3,0, $E$3,1) at Reference Location, which is used to get sales for the specified time period, and finally click OK.
Create a sales name Yaxis
3, the same method, create a new X, enter the formula as "=offset ($B$3, $G$3-$A$3,0, $E$3,1)", the name is used to get the specified research time, and finally click "OK".
4. Select the data area A3:B6 (Note: the data area here does not require the number of rows), select "Column Chart" under "Insert", and click Insert.
Insert a base column chart
5. Right-click on the generated chart area, in the list options, click "Select Data", and the "Edit Data Source" dialog box will pop up.
Invokes the Edit Data Source dialog box
6, the next is the most important chart data reference settings! 1. Reference "Chart Data Range" under "Edit Data Source" dialog box as cells B3:B8, and then select Series 1, click the Edit button above, and the "Edit Data Series" dialog box will pop up.
In the dialog box, select the series name in cell B2, which is the sales amount, and enter "=yaxis" in the other series, which is the sales dynamic sequence set up earlier. This way we set up the vertical axis of the chart - sales.
Set the dynamic ordinate - Sales
Then click the Edit button on the right, enter "=xaxis", which is the axis you defined earlier, in the "Axis Label" dialog box that pops up, and click OK.
Set Abscissa Axis - Time (Weeks)
7. Set the dynamic title and enter the formula ="Average Sales" &AVERAGE(YAXIS)&"Ten Thousand" in cell F3. After clicking on the chart title, enter "=SHEET1! $F$3" in the formula bar so that the title of the chart dynamically displays the average sales over the study period.
8, the chart beautification, the final will make a dynamic chart, data, shading and other settings and beautification, here will not be introduced.
Simple beautification of charts
After listening to this, Xiao Zhang couldn't help but admire Xiao Tan's colleagues, thinking that he had really learned his skills today, but what was this OFFSET function? How can it be of such a great use?
Xiao Tan seemed to see his confusion, so he said to him: "The OFFSET function can implement dynamic selection of cell ranges, and the syntax structure is: OFFSET (reference, rows, cols, [height], [width]). Where, the reference parameter is used to define the starting position of the range, the rows parameter is used to define the row offset, the cols parameter is used to define the column offset, the height parameter is used to define the number of referenced rows, and the width parameter is used to define the number of columns referenced. ”
"Oh, that is: OFFSET (starting position, row offset, column offset, number of reference rows, number of reference columns), take the formula "=OFFSET($B$3, 5,0,5,1) as an example, which means to take cell B3 as the starting position, offset 5 rows, 0 columns, that is, to cell B8, and then start from cell B8, select 5 rows, 1 column, that is, select cell B8:B12. Right? ”
"Yes, that's what it means! Let's go to beer fried chicken first! ”
So what kind of beer fried chicken is delicious? Try it out with a headline search!
So this kind of dynamic demo chart based on the name manager and THE OFFSET offset function, have you learned? Think about it, and you'll be even better!
Finally, I hope you all like and pay attention to it, give support, thank you!