excel BI仪表板：关联

当在仪表板中使用了层级关系后，就必须使用交互的方法来使不同的层次关联在一起。在Excel中我们可以借助工作表控件和部件本身来完成交互，在仪表板中，此类交互往往需要牵一发而动全身，一个触发事件需要使多个图表部件的数据展示发生变化。

联系

1．切换

提供快捷简单的方法，来方便使用者通过鼠标单击的方式切换图表部件的不同数据显示。尤其是当仪表板包含多个面板时，需要添加切换选择按钮或链接，以方便使用者在不同面板间切换。在Excel中可以使用工作表控件按钮或是图表部件的VBA鼠标单击事件来实现。

2．检索

当某个部件或层级是另一部件和层级的细化说明时，需提供可供使用者选择的相关筛选触发器，以便对某个图表部件或层级进行数据展示的切片或是截段。在Excel中，下拉选框、滚动条都可以完成这些操作。笔者在第14章提供的滑块控件，可协助获取某一范围区间或时间跨度的数据。

交互

1．简化

在选择和使用按钮、列表框、滑块等控件时，要考虑到尽量简单，并有意减少使用的个数，不要把仪表板变成数据游戏的道具。交互体验越是繁杂，使用者获取到的信息就越少，甚至有时会严重影响使用者的决策判断。

2．自然

仪表盘设计时应充分考虑使用者将如何使用这些交互，其应该可以方便地从多个维度去解释数据。站在使用者的角度去考虑：单击按钮、链接等应该有怎样的外观，并通过使用者眼球和鼠标单击的动线设计来决定其放置位置，使作业流程无缝嵌入到仪表盘，具有自然流畅的使用感受，这样使用者的关注焦点就被锁定在了数据上。

3．接口

使用Excel来实现仪表盘的交互时，不同图表部件间的关联关系都是通过函数公式或定义名称完成。规划和设计这些公式时，必须要考虑仪表板的升级和维护作业，将公式设计得简单易读将会是省时省力的一个好习惯。

案例

图15.3-1是在图15.2-1仪表板案例基础上添加了交互控件的效果，其中滚动条用来使6个仪表盘显示具体到某个月的数据，下拉列表框则用来切换6个仪表盘显示特定的销售数据，同时两个条形图也和这两个控件关联。笔者在第14章提供的滑块控件和4个折线图关联，4个折线图同时和当前选中商品相关联，6个商品的数据切换，则使用仪表盘的VBA图表事件来完成。以下是详细制作步骤：

图15.3-1　在图15.2-1案例基础上增加了交互的效果

1．在“Test1”工作表6个仪表盘下方添加如图15.3-1所示的滚动条工作表控件，该控件和“Q17”单元格相关联，如图15.3-2所示。

图15.3-2　制作步骤1～3图示

2．在“Test1”工作表单元格区域“M17:O17”间增加下拉列表框工作表控件，数据源区域为“商品1”仪表盘下方的“C11:D13”单元格（如图15.3-3所示），并和单元格“L17”相关联，将单元格区域“K17:Q17”设置和图15.3-2所示一致。

图15.3-3　制作步骤2图示

3．合并“Test1”工作表“C17:I18”单元格，并添加如下公式：

=TEXT(OFFSET(Data!$J$5,$Q$17,0),"yyyy-mm")&"月"& CHOOSE($L$17,"零售","批发","网络")&"数量比"结果如图15.3-2所示。

4．依次键盘Ctrl键，将“Test1”工作表6个仪表盘名称更改为“Chart 1”…“Chart 6”，在VBE中添加如下代码段，以完成仪表盘为触发器的设定：

程序代码：15.3-1　ChartClass类模块XChart_MouseDown　图表鼠标单击事件

程序代码：15.3-2　模块1中的XChart_MouseDown　将仪表盘和类模块关联

程序代码：15.3-3　模块1中的XChart_MouseDown　释放仪表盘和类模块的关联

注：此处设定显示比例的目的是笔者在第14章提供的滑块控件在激活类模块关联时，非100％显示比例时，该控件的外观大小会改变。

5．在VBE中添加如下代码段，以完成图表事件类模块初始化和释放关联到工作簿的打开与关闭事件中：

程序代码：15.3-4　工作簿打开事件

注：此处Application.OnTime是因为Workbook_Open事件发生时，直接执行将仪表盘和类模块的关联往往不起作用，所以需要待Excel工作簿完全被加载。

程序代码：15.3-5　工作簿关闭事件

6．依次设定“Test1”工作表的B4、K4、T4、AC4、AL4、AU4这6个单元格的条件格式如图15.3-4所示：

图15.3-4　仪表盘是否选中的条件格式

7．依次设置“Chart”工作表E5、G5、I5、K5、M5、O5单元格和E7、G7、I7、K7、M7、O7单元格的公式，该公式可参见图15.3-5箭头所指位置，不同商品请注意公式中OFFSET函数的rows参数：129所乘系数依次为0～5。如图15.3-5所示：

图15.3-5　仪表盘是否选中的条件格式

8．在“Test1”工作表中依次设定仪表盘相对应的数据状态标示，图15.3-6以“商品1”为例说明了相关设定，其他5个仪表盘的设定与此相仿。

图15.3-6　仪表盘是否选中的条件格式

9．在“Test1”工作表“T19:BB19”单元格区域中添加笔者在第14章提供的滑块控件（在窗体控件的其他控件中查找控件)，并设置“AZ17:BC17”单元格区域外观样式如图15.3-7所示。在VBE中添加如下代码段：

图15.3-7　制作步骤9图示

程序代码：15.3-4　Test1工作表的ctlRangeSlider1_Change　滑块滑动事件

注：此处没有使用控件ctlRangeSlider1_Scroll的事件，是因为滑动过程频繁的Excel函数公式的自动重算，会导致执行效率低下。

10．合并“Test1”工作表“T17:AD18”单元格区域，并添加如下公式：

结果如图15.3-8所示。

图15.3-8　制作步骤10图示

11．合并“Test1”工作表“C33:J34”单元格区域，并添加如下公式：

结果如图15.3-9所示。

图15.3-9　制作步骤11图示

12．添加如下定义名称：

13．修改以下图表的SERIES公式：

不同商品的数量对比

系列1：=SERIES(Test1!$C$17,Data!$B$6:$B$11,Test1!Adata,1)

系列2：=SERIES(,Data!$B$6:$B$11,Test1!Y1Max,2)

3类销售数量对比

系列1：=SERIES("数量比较",Data!$D$5:$F$5,Test1!Bdata,1)

系列2：=SERIES(,Data!$D$5:$F$5,Test1!Y2Max,2)

零售数据

系列1：=SERIES(,Test1!XData,Test1!Y1Data,1)

系列2：=SERIES(,Test1!XData,Test1!Y2Max,2)

批发数据

系列1：=SERIES(,Test1!XData,Test1!Y2Data,1)

系列2：=SERIES(,Test1!XData,Test1!Y2Max,2)

网购数据

系列1：=SERIES(,Test1!XData,Test1!Y3Data,1)

系列2：=SERIES(,Test1!XData,Test1!Y2Max,2)

平均单价

系列1：=SERIES(,Test1!XData,Test1!Y4Data,1)

系列2：=SERIES(,Test1!XData,Test1!Y3Max,2)

14．向商品1的仪表盘图表中添加一个矩形，并将其拖曳到大小与绘图区相同，设置为无边框，透明度100％，然后修改其名称为“点击查看商品1细节数据”。选中并复制到其他5个仪表盘，同时根据仪表盘对应的商品名修改矩形名称。

注：此处使用矩形的目的是，当鼠标指向仪表盘时提供反馈给使用者去单击仪表盘，切换不同商品的详细数据，但在Excel 2007中该方法无法提供反馈。

学习思考

特别说明：

1）该案例并未包含目标值参考基准线，仪表盘的数据状态都是和历史最佳状态相比较的结果，现实使用中，这个比较基准应该是动态给出的。

2）这个案例由于要考虑多个版本的兼容问题，并没有过多美化，如果借助2007和2010可进行适当美化。

3）使用时需特别注意笔者提供的滑块控件的使用限制。

……