今天继续跟大家讲解水晶易表动态仪表盘的高级用法——多选择器交互用法。
关于选择器的用法,之前的几篇零零碎碎的讲了些,今天是专门讲解水晶易表中几种重要的选择器用法——标签式菜单(在案例1中曾经讲过,不过具体用法不同,那里是匹配的原数据,按行插入,这里仅仅作为按钮选择工具,按值插入目标)、单选按钮(第一篇案例中同样也有使用)、组合框。
在讲解本篇之前,你需要有点儿excel函数基础:index、match、offset、&文本合并函数等。(因为里面需要使用函数嵌套工具,这也是学习Xcelsius的入门门槛)。
本篇教程分为两大部分,excel数据模型搭建、水晶易表仪表盘创建过程。
由于该篇文章高度依赖动态数据模型,所以我会将重心放在excel数据模型上,之后的水晶易表环节纯属操作性技能,都是些固有的套路,看下就明白了。(仔细体会水晶易表中选择器与excel中开发工具控件的区别)。
首先还是浏览下我们最后将要成图的作品:
图中你可以通过菜单方式切换三个维度(年度、产品类型、地区)时间动态交互。
原始数据来源于沈浩老师的水晶易表教程,奈何做法与思路实在是高深奥妙,令人费解,反正本宝宝看了好久始终没有消化(虽然那个方法看起来很棒),结果一怒之下宝宝就自己操刀改了数据,使用函数嵌套(使用正常套路)给模型搭建完毕,并导入水晶易表完成交互功能效果。
原数据是一个标准的一维表,展示年份、产品类型、地区三个维度279(3年*3类产品*31地区)个数据。
数据中除了以上信息之外,你可以看到我特意添加了查询标准字段,这列字段将每一条数据的年份、产品类型、地区合并成一个单元格(关于合并单元格信息,请参考小魔方的历史文章),这列数据将作为后期的重要查询标准。
集合以上图表中的三个选择器和数据表,我的大体思路是这样的:
通过标签式选择菜单传递产品类型参数、通过单选按钮传递年份参数、通过复选框传递地区参数。
然后通过index函数将参数转化为具体的指标(index只能传递数字序号)。
通过&文本合并函数将三个指标合并。
通过offset+match函数嵌套在源数据表中匹配对应指标的12个月份值。
最后完成数据模型构建,就可以导入水晶易表链接数据,创建可视化动态交互仪表盘。
excel数据模型:
首先定义三个选择器数据区域以及目标值插入位置:
T4:T6位置作为单选按钮标签链接位置,U3为其目标插入位置。
当用鼠标依次单击单选按钮(2006、2006、2008)时,将会在U2依次输出1、2、3值。
同理,T8:T10为标签菜单的标签链接位置,目标插入位置为U7,用鼠标单击标签——产品A、产品B、产品C,依次在U7单元格返回1、2、3值。
T12:T42单元格区域将作为复选框标签链接区域,U11作为复选框目标插入位置,显示选择的地区参数。在复选框的下拉菜单中依次分别单击北京、天津、河北……新疆,则U11单元格会依次输出1、2、3……31。
三个选择器的数据源以及目标参数输出位置(目标插入)设置完成之后,利用index函数将参数值转化为实际指标(参数对应的实际年份、产品类型、地区名称等)。
函数公式依次为:
C5=INDEX(T4:T6,U3)
C6=INDEX(T8:T10,U7)
C7=INDEX(T12:T42,U11)
完成之后,在C3单元格利用&函数将C4,C5,C6单元格转化后的指标合并成一个指标(现在你明白为啥我要在原始数据表中添加一列(年份&产品类型&地区名称)的合并数据了吧,就是为了作为查询依据。
C3=C5&C6&C7
至此,查询条件设置完毕,接下来我们就要按照该查询条件在原数据表中匹配出某一年、某一产品类型、某一地区的销售额。
在D3单元格中使用offset+match函数嵌套实现C3单元格的数据匹配查找。公式如下:
D3=OFFSET(D9,MATCH($C$3,C$10,$C$288,0)0,1,1)
这里涉及到match函数与offset函数的用法, 不打算详细讲解,不熟悉的请参考小魔方之前的历史文章。
match函数的意思是,在C10:C288单元格区域(年份、产品类型、地区名称信息合并)中查找C3(三个选择器返回的年份、产品类型、地区合并信息)单元格所在行,查找方式为精确查找(0代表精确查找)。
match函数输出的行号将作为offset函数的第二个参数。
这里offset函数的意思是:从D9单元格开始,向下移动(match返回值)行,向右移动0列,然后选择1行1列(也就是选择该单元格)。【请注意内部的决定引用与相对引用的用法区别,D9要使用相对引用,这样才能向右填充函数,其他参数作为查询参照,需要使用绝对引用】。
将D3单元格函数向右填充至R3单元格,至此完成所有excel动态数据模型搭建工作。
此时你可以随意更改U3(1~3之内)、U7(1~3之内)、U11(1~31之内)单元格值(在范围内更改),看我们所设置的所有带函数的单元格是否成功变动(C5:C7、C3:R3)。如果正常变动,则动态数据源设置成功,导入进水晶易表开始搭建动态仪表盘工作。
水晶仪表盘搭建:
进入水晶易表,分别插入标签式菜单、组合框、单选按钮。(部件选择器中插入)。
标签式菜单、组合框、单选按钮的参数设置如下:
三个选择器全部配置完成之后,在部件中插入统计图-折线图。
至此,动态仪表盘全部配置完毕,可以通过预览功能查看动态交互性能是否完好,没有问题之后,可以通过水晶易表的主题、颜色以及统计图属性菜单中的外观项目精修字体、配色和背景,也可以在部件中插入专门的背景色块做衬托。
最后比较满意,可以发布,导出成swf格式嵌入其他文件中待演示用。