4步,教你制作一张高大上的excel动态图表

2019年12月24日16:44:11 1 401

4步,教你制作一张高大上的excel动态图表

在企业生产过程中,经常需要做产量分析,如果有一张图表,它能根据分析者的选择,而动态显示任意时间段的产量情况,并且还能通过标题栏给出对应时间段的平均和最低产量数据,那将使我们的效率大大提升。(效果如下图所示)

 

4步,教你制作一张高大上的excel动态图表

 

首先,我们来分析需求:①图表需要动态显示任意时间段的产量数据;②标题栏需要动态显示对应时间段的平均和最低产量数据。

说到excel中的“动态”二字,那就不得不提到OFFSET函数了。在制作动态图表时,首先需要用OFFSET函数定义动态区域。

步骤:

①  定义两个名称区域

本例需要创建两个名称区域,作用是定义两个单元格区域。它们选取的范围是动态的,能够根据用户的操作而自动改变,这两个动态单元格区域中的数据,将作为我们制作图表的数据源。

我们在工作表E3单元格输入任意起始日期,在F3单元格输入任意结束日期,在C3单元格输入公式“=F3-E3+1”,得到起始日期到结束日期的总天数。

4步,教你制作一张高大上的excel动态图表

接着在“公式”选项卡的“定义的名称”组中单击“定义名称”,弹出“新建名称”对话框。在对话框的“名称”处输入“Yaxis”,在“引用位置”处输入公式“=OFFSET(Sheet1!$B$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”,该名称用于获取指定时间段对应的产量区域。最后单击“确定”。

4步,教你制作一张高大上的excel动态图表

按照上述步骤,打开“新建名称”对话框,在对话框的“名称”处输入“Xaxis”,在“引用位置”处输入公式“=OFFSET(Sheet1!$A$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”,该名称用于获取指定时间段对应的日期区域。

4步,教你制作一张高大上的excel动态图表

OFFSET函数可以实现对单元格区域的动态选择,语法结构为:OFFSET(reference,rows,cols,[height],[width])。其中,reference参数用来定义区域的起始位置,rows参数用来定义行偏移量,cols参数用于定义列偏移量,height参数用于定义引用的行数,width参数用于定义引用的列数。以第一个公式“=OFFSET(Sheet1!$B$3,Sheet1!$E$3-Sheet1!$A$3,0,Sheet1!$C$3,1)”为例,意思是以B3单元格为起始位置,偏移E3单元格(起始日期)与A3单元格(2019年1月1日)的差的行数不偏移列,引用一个“C3”行(C3单元格中的数据指定引用的行数),1列的数据区域。

对OFFSET函数不太熟悉的小伙伴可以点击《Excel进阶之路必学函数:动态统计之王——OFFSET(上篇)》教程学习一下。

②  设置图表数据

在定义了动态区域后,接着就可以创建图表啦。将图表的数据系列和水平坐标轴标签指定为需要的动态区域,即可实现动态图表。

选择A3:B8单元格区域,插入“簇状柱形图”。鼠标右键单击图表,在弹出的关联菜单中,点击“选择数据”,弹出 “选择数据源”对话框。

 

4步,教你制作一张高大上的excel动态图表

 

在对话框的左侧“图例项(系列)”栏中单击“编辑”按钮,弹出“编辑数据系列”对话框。在对话框中的“系列名称”处输入“=Sheet1!$B$2”(直接点击表格中的B2单元格即可),在“系列值”处输入公式“=Sheet1!Yaxis”,表示该数据系列指定为“Yaxis”名称区域中的数据,完成设置后单击“确定”按钮关闭对话框。

 

4步,教你制作一张高大上的excel动态图表

 

接着,在“选择数据源”对话框的右侧“水平(分类)轴标签”栏中单击“编辑”按钮,弹出“轴标签”对话框。在对话框的“轴标签区域”处输入公式“=Sheet1!Xaxis”,表示将轴标签设置为“Xaxis”名称区域中的数据,完成设置后单击“确定”按钮关闭对话框。

 

4步,教你制作一张高大上的excel动态图表

 

通过上述步骤的设置,只需要在E3单元格和F3单元格中分别输入任意的起始日期和结束日期,就能动态的在图表中显示对应时间段的产量情况。

 

4步,教你制作一张高大上的excel动态图表

 

③  设置动态标题

 

完成了第一个需求,接下来我们来完成第二个:使标题栏动态显示对应时间段的平均和最低产量数据。

 

怎样让图表标题动态显示分析数据?我们在一个单元格中,使用公式对数据进行计算,在图表中插入标题,再让标题框引用该单元格数据就可以了。

 

在D3单元格中输入公式“="平均产量为"&ROUND(AVERAGE(Yaxis),1)&"方"&",日产量高于"&ROUND(MIN(Yaxis),1)&"方"”。

4步,教你制作一张高大上的excel动态图表

使用AVERAGE函数和MIN函数分别求出指定时间段产量区域“Yaxis”中的平均值和最低产量数据。再使用ROUND函数,将得到的结果按指定位数进行四舍五入。最后用连接符“&”,将各个字段进行连接。

 

然后在图表中添加图表标题,双击标题,在编辑栏中输入公式“=Sheet1!$D$3”。引用D3单元格的数据作为图表标题。

4步,教你制作一张高大上的excel动态图表

这样一来,标题栏就能动态显示数据分析结果,查询数据是不是变得既方便又直观?

④  美化图表

 

双击图表中数据系列弹出“设置数据系列格式”对话框,在“系列选项”设置栏中将“分类间距”设置为100%。

 

4步,教你制作一张高大上的excel动态图表

 

在“图表工具”栏中点击“设计”,选择自己喜欢的图表样式,并适当调小标题字体,删除网格线和纵坐标轴,添加“数据标签”,图表制作完成的效果如下。

4步,教你制作一张高大上的excel动态图表

易卜拉欣-互联网深度爱好者,专注企业网站建设优化,研究网络营销方法,对搜索引擎营销情有独钟。

微信及QQ:975197969,欢迎加入QQ交流群:959689918,感谢阅读!
写文章不易,如果您觉得文章对你有帮助。
打赏激励下作者吧,谢谢支持! ~(@^_^@)~!

生成海报
继续阅读
  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的名片
  • 电子名片扫一扫
  • weinxin
avatar
  • 本文由 发表于 2019年12月24日16:44:11
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

评论:1   其中:访客  1   博主  0
    • avatar 软件帝 1

      个人博客还在坚持更新不易 留言支持下 加油