当前位置:首页 > 图片 > 时尚娱乐 > 正文
文章正文

excel按钮动态图表 带控件的Excel动态图表,原来如此简单

图片 > 时尚娱乐 > :excel按钮动态图表 带控件的Excel动态图表,原来如此简单是由美文导刊网(www.eorder.net.cn)为您精心收集,如果觉得好,请把这篇文章复制到您的博客或告诉您的朋友,以下是excel按钮动态图表 带控件的Excel动态图表,原来如此简单的正文:

带控件的Excel动态图表,原来如此简单

各位小伙伴们好啊,今天老祝再和大家分享一个动态图表的技巧。

先看效果:

图表中始终显示5天的PM2.5数据,当调整滚动条时,图表会自动更新结果。

首先是数据源,为某地一个时期的PM2.5数据。

步骤一

先插入一个滚动条控件。

右键单击控件,设置控件格式。

最小值选择1

最大值可以根据数据情况来决定

单元格链接选择一个空白单元格,如D6。

步骤二

Ctrl+F3,打开名称管理器,新建两个自定义名称:

日期

=OFFSET(Sheet1!$A$1,Sheet1!$D$6,,5)

数据

=OFFSET(Sheet1!$B$1,Sheet1!$D$6,,5)

OFFSET函数的行偏移量由D6单元格指定,而D6单元格则由滚动条控件来控制。

这样每单击一次滚动条,OFFSET函数的行偏移量就会发生变化。

步骤三

选中部分数据,按下图步骤插入一个折线图。

右键单击图表,选择数据,修改数据源。

(excel按钮动态图表)

将轴标签和数据系列修改为刚刚自定义的名称。

最后对图表进行简单的美化。

调整图表高度和单元格的高度,使图表的垂直刻度和单元格行高相同,再给单元格设置不同的底色。

插入一个图片素材,按Ctrl+C复制,然后单击图表数据点,按Ctrl+V粘贴。

右键单击数据系列,添加数据标签,OK。

图文制作:祝洪忠

ExcelHome,微软技术社区联盟成员返回搜狐,查看更多

这个动态图表居然是用 Excel 做的?!

向商业媒体学习图表和可视化是非常好的方式,因为他们的图表与可视化都是经过专业的设计人员、美术人员制作出来,有很高的专业性和前沿性。很多国外专家也都是向媒体学习,练习用 Excel 来实现媒体的图表。在很久以前,我也是通过购买财经杂志获得案例,来琢磨如何用 Excel 来实现它上面的图表。

今天我们看一则《财新》杂志的动态图表范例,这是他们昨天在微信公众号推出的一篇文章和动态图表,叫中国近十年境外大额房产投资的数据分析。网页链接是:http://datanews.caixin.com/2017-05-25/101094588.html,感谢动图作者韦梦的好作品。

例图分析

(excel按钮动态图表)

这个动态图表非常酷,默认显示一个类似于封面一样的页面,有个堆积柱形图,显示10年来每年分季度累计的投资额,可以看总体的变化趋势。然后有两个选择按钮,你可以选择按国家或者按房产类型来看细分的数据,这时候堆积柱形图变成了垂直方向悬空、水平底边对齐的形式。这种把堆积柱形图改成分组水平对齐的方式,可以克服堆积柱形图不容易看出各分项变化趋势的缺陷问题。还有一个刷新还原的按钮,点击又回到这个封面。

这个交互方式很有新意,尤其是切换时沙盒流动的感觉,非常灵动轻盈。在我们的实际工作中也可以借鉴应用这种交互方式,比如说对你的近十年的销售额数据的分析,分时间、分地区、分产品、分销售渠道等,来进行总体趋势和构成比较分析,都是很合适的。

如果我们用 Excel 来做这个动态图表,可能做不到那么灵动的效果,但是交互的方式和界面是可以做到差不多的。今天我们就尝试用 Excel 来实现这样一个动态交互图表。

作图思路

首先,让我们看一下做图思路。

  1. 选择器。这里财新用的是扁平化风格的两个选择按钮和一个刷新按钮,Excel的控件本身无法做到。我们在《向经济学人学图表》第2季里面介绍过利用超链接和自定义函数,把单元格变成一个选择器的做法,很巧妙很高级,但是要用到宏。这里为了简单起见,我们使用窗体控件里面的单选按钮来做,但要想办法让用户看不出来。

  2. 悬空柱形图。选择后的3个柱形图,其中有两个是细分柱形图要悬空水平对齐的形式,我们可以使用多行占位数据,把细分的柱形图给垫起来,形成水平方向对齐。

  3. 柱形图切换。这3个切换的堆积柱形图,我们其实可以把它做成一个堆积柱形图,只是当你选择某一个分类后,另外两个的数据全部取值为0,你就看不见了。

  4. 3个柱形图的类别名称标签,可以使用1组散点图来智能化地标记。

(excel按钮动态图表)

好,这个就是我们的作图思路,下面可以开始制作了。

制作步骤

1、首先第一步,是制作这个扁平化的选择器。

我们直接利用单元格写上“按国家和地区”、“按房产类型”,和一个表示刷新的特殊符号“↺”,这个圆弧箭头,你需要在那个插入符号里面找一找。然后,我们画3个空白的单选按钮放在这3个单元格的上方,锚定对齐好。他们的选择结果链接到 control工作表的 C3 单元格,D3 顺便翻出选择结果备用。

当用户选择之后,前面两个格子要做出格式反馈,可以使用条件格式。选中前面两个格子,条件格式,使用公式的条件格式,=C6=control!$D$3,即如果其值等于选择结果对应的内容,那么就把这个单元格设置为白色填充黑色字体,表示被选中了。

2、第二大步骤,是组织作图数据。

这是我们的数据源,10年的数据,有3个细分维度:

先把这个原数据,通过选择性粘贴、粘贴链接的方式引用到新的工作表。这样做是为了方便我们后期好更新源数据。

按国家和按房产类型的堆积柱形图,它是需要悬空以后水平对齐的,所以说我们就要在每行数据下面插入一个空行,组织占位数据。

图中,D16的公式:=MAX($D15:$N15)-D15+vgap_2

公式的意思是说我们取上1行的最大值减去上一格的值,然后再加上一个留空值。这个留空的间隔vgap_2,我们把它做成一个参数方便调整,暂时取值为整个分类数据里的最大值除以5。

这个公式注意写法,锁定列号,行号不锁定。向右复制到整行,然后能把这一行复制粘贴到余下的所有占位行。检查公式和结果正确。

然后,我们要根据用户的选择,分别引用这3块里面的1块,到作图数据区域,

第1块区域,Q9:=IF(control!$C$3=3,calc!D9,NA()),复制到满区域;

第2块区域,Q15:=IF(control!$C$3=1,calc!D15,NA()),复制到满区域;

第3块区域,Q40:=IF(control!$C$3=2,calc!D40,NA()),复制到满区域;

3、第三步骤,做图表。

现在我们以P8:AA50 数据区域插入堆积柱形图,并对所有的占位行数据序列的柱形图设置为无填充色。这一步你要分别通过选择器选择 按国家和地区、按房产类型,和刷新复原情况下的图表,把所有占位数据的柱形图都设置为无填充色。

现在你测试选择,应该可以看到动态图表的变化,检查确认正确。下面我们来给它加上那个类别标签。

我们需要准备一列辅助数据,是一个散点图的序列xy,也是分3块分别取值:

第1块区域,显示季度数据时,标签放右侧,x,AC9:=IF(control!$C$3=3,11,NA()),y,AD10:=SUM($Z$9:Z10)-Z10/2。这里第1行有一点特别处理,需要看看。

第2块区域,显示国家数据时,标签放左侧,x,AC15:=IF(control!$C$3=1,0.5,NA()),y,AD15:=SUM($Q$15:Q15)-Q15。往下复制到满区域。

第3块区域,显示类型数据时,标签放左侧,x,AC40:=IF(control!$C$3=2,0.5,NA()),y,AD40:=SUM($Q$40:Q40)-Q40。往下复制到满区域。

数据准备好之后,我们往图表里面添加一个新的序列,数据源暂时不管,确定,然后把这个序列更改图表类型为散点图,确认,再选择数据,给他的数据源指定xy序列,分别是AC、AD列,确定之后,图表里应该出现一组散点图,给它添加数据标签,指定为P列的的类别名称,位置靠左。

现在我们通过选择按钮来切换图表,检查标签的位置,发现当用户选择刷新复原后,那个1234季度的标签位置靠左是不合适的,单独把这四个标签的位置分别调整为居中,以及第1季度的标签靠右。

4、第四步,美化与收尾。

现在动态图表模型已经完成,切换图表确认是正确的,然后让我们来做一些格式美化。为了实现类似财新杂志的风格,我们可以把这个图表拷到ppt里面去,利用ppt里面的取色器,从财新例图取色,来对柱形图快速进行逐一的填色。

我们还可以通过柱形图的图案填充,选择一种方块格的图案,比较接近于财新的这个圆点风格。这两步都是重复操作,略微有些繁琐。

在点刷新复原按钮选的时候,封面图里面有一些文字说明,我们也可以利用一个文本框来动态引用。先在 control表的B12 写好说明文字,然后在B15:=IF(cur_sel=3,B12,""),根据用户的选择,如果等于3,就引用这个说明文字,否则为空。然后在图表区域里面插入一个文本框,在公式栏里链接等于这个B15,就可以动态链接的文字出现与否。

现在图表的基本完成,我们可以进行收尾了。我们还可以做个事情,把单选按钮的那个圆点点,用一个形状把它遮住,让用户看不出来这是个单选按钮。因为这两个单元格底色可能会发生变化,所以我们用来遮挡的形状来要利用一个条件格式单元格的链接图片,这个单元格设置条件格式,和前面选择器单元格类似。那么这个遮挡的图片就会根据用户的选择是显示为白色还是黑色,来智能化地遮住那个圆形按钮。

最后的动态图表效果如下图:

最后呢,我们可以把文件保护起来,避免用户不小心拖动了这些图表和图片。保护之前,要把这三个单选按钮的属性里面有个锁定给取消。整个图表就完成了,你可以进行一些调试检测,确认模型的制作正确性。

知识回顾

本例综合性比较强,回顾一下,我们用到了哪些知识点呢?

  1. 首先是这个扁平化选择器的DIY制作。我们是把文字放在单元格里面,单选按钮做成隐藏的、透明的,看起来是没有 Excel 痕迹的。通过条件格式来对用户的选择作出反馈。

  2. 数据组织上,这种悬空水平对齐的堆积柱形图,我们是用了一些辅助占位数据,把它垫起来。

  3. 然后这三个柱形图,事实上我们是用的一个柱形图,只是你选择某一个分类的时候这个分类的柱子才全部出来,其他分类都不出现。

  4. 然后呢,给这个图表配色,我们是把它拷到ppt里面,利用ppt里面的取色器来快速填充颜色。以及设置了填充图案。取色器移出PPT外 的技巧。

  5. 动态的标签,是使用了散点图去智能化标记,柱形图+散点图的组合图表。

  6. 还有呢,我们复制这个条件格式单元格,粘贴为链接的图片,也就是拍照方式的图片,去遮挡一个单选按钮的那个圆形按钮,智能变化黑色还是白色。

运用场景(excel按钮动态图表)

这种形式的动态图表很实用,在我们日常工作中的有很多的应用场景,比如说你公司近12个月或者近10年的销售数据,你可以呢,按时间总数来看,也可以按地域维度、产品类型来分类比较,可以使用这样一个动态图表来切换,既可以看他们的时间趋势,也可以看分类构成。

范例下载

难度系数:★★★★★

实用系数:★★★★★

如何获得范例下载?先转发本帖到朋友圈,然后在微信公众号 iamExcelPro 发送 “caixin1”下载范例文件,动手练习。如果不想从头制作,直接在data表里填入你的数据,直接套用也是可以的。

(本文为语音输入写作,个别地方略有口语风格,望见谅)

想要成为能做出这样动态图表的高手吗?参加我们的《让你的图表动起来》课程,史上最全最体系化最高阶的动态图表技术与应用大全,由浅入深,晋身顶尖高手,不容错过,点击【阅读原文】立即前往。返回搜狐,查看更多




一.如何巧用Excel控件制作动态图表(excel按钮动态图表)

方法/步骤
数据做好,在旁边增加一列辅助列,并输入如图公式,

公式下拉,报表数据有多少行,就拉到多少行

3
工具栏-开发工具-插入-组合框

4
在工作表中画出控件,右击控件,设置控件格式

5
如图设置,先把报表的各类名称依次排列下来

6
选中辅助列数据-插入图表-三维柱形图

7
右击选中控件-单击按住控件移到图表内,再进行图表美化,就成了,是不是很法畅瘁堆诓瞪搭缺但画容易,你也试制作吧


二.怎样在Excel中制作选择按钮动态图表

这里介绍一个利用组合框控件制作动态图表的方法。
软件版本:Office2007
方法如下:
1.开发工具,插入,组合框:

查看"办公软件"分类更多问题

回顶部↑

三.Excel怎么制作带控件的动态图表

1、数据做好,在旁边增加一列辅助列,并输入如图公式,

2、公式下拉,报表数据有多少行,就拉到多少行

3、工具栏-开发工具-插入-组合框

4、在工作表中画出控件,右击控件,设置控件格式

5、先把报表的各类孩珐粉貉莠股疯瘫弗凯名称依次排列下来

6、选中辅助列数据-插入图表-三维柱形图

7、右击选中控件-单击按住控件移到图表内,再进行图表美化,就成了,是不是很容易,你也试制作吧


四.excel 2007中 插入动态图表

两个办法:
1、直接做一个整月的表格,根据表格制作图表。每天在表格上填上数据,图表会自动添加曲线
2、同样制作一个整月表格
每天制作一份图表,第二天有新数据时,选中新数据,右键点“复制”,在图表空白处点右键,单击“粘贴”就成了。第一个办法方便些,也更有可比性。
OK!!!愿能帮上你。




五.Excel动态图表怎么做

一般制图过程:

1
选中数据区域,选择【插入】-【图表】,选择【柱形图】。

2
插入后的柱形图如下,蓝色的系列柱子为【数量】,横轴为【姓名】。

3
这时,我们在原始数据下面增加如下图黄色区域数据,图表不会自动更新。

动态图表解决方案:
选择功能区的【公式】-【定义名称】。

定义一个名称为【姓名】,【引用位置】处输入下面的公式:
=OFFSET(有效性!$A$2,,,COUNTA(有效性!$A:$A)-1,1)

同样的方式,定义一个【数量】,【引用位置】处输入公式:
=OFFSET(有效性!$B$2,,,COUNTA(有效性!$A:$A)-1,1)

在图表上鼠标右键,选择【选择数据窢梗促妓讵幻存潍担璃】。

选中【数量】系列,点击【编辑】。

将系列值改成如下图的=有效性!数量,并确定。

同样更改【姓名】系列如下图,并确定。

返回excel工作表界面,我们在A列和B列新增一行数据,图表就自动变化了。


六.Excel 用选项按钮怎样做才能让图表成为动态

Excel怎么制作动态图表_百度经验 jingyan.baidu.com/...4.html

excel按钮动态图表 带控件的Excel动态图表,原来如此简单由美文导刊网(www.eorder.net.cn)收集整理,转载请注明出处!原文地址http://www.eorder.net.cn/pic23920/

文章评论
Copyright © 2006 - 2016 www.eorder.net.cn All Rights Reserved
美文导刊网 版权所有