excel BI儀表板：關(guān)聯(lián)

當(dāng)在儀表板中使用了層級(jí)關(guān)系后，就必須使用交互的方法來(lái)使不同的層次關(guān)聯(lián)在一起。在Excel中我們可以借助工作表控件和部件本身來(lái)完成交互，在儀表板中，此類交互往往需要牽一發(fā)而動(dòng)全身，一個(gè)觸發(fā)事件需要使多個(gè)圖表部件的數(shù)據(jù)展示發(fā)生變化。

聯(lián)系

1．切換

提供快捷簡(jiǎn)單的方法，來(lái)方便使用者通過(guò)鼠標(biāo)單擊的方式切換圖表部件的不同數(shù)據(jù)顯示。尤其是當(dāng)儀表板包含多個(gè)面板時(shí)，需要添加切換選擇按鈕或鏈接，以方便使用者在不同面板間切換。在Excel中可以使用工作表控件按鈕或是圖表部件的VBA鼠標(biāo)單擊事件來(lái)實(shí)現(xiàn)。

2．檢索

當(dāng)某個(gè)部件或?qū)蛹?jí)是另一部件和層級(jí)的細(xì)化說(shuō)明時(shí)，需提供可供使用者選擇的相關(guān)篩選觸發(fā)器，以便對(duì)某個(gè)圖表部件或?qū)蛹?jí)進(jìn)行數(shù)據(jù)展示的切片或是截段。在Excel中，下拉選框、滾動(dòng)條都可以完成這些操作。筆者在第14章提供的滑塊控件，可協(xié)助獲取某一范圍區(qū)間或時(shí)間跨度的數(shù)據(jù)。

交互

1．簡(jiǎn)化

在選擇和使用按鈕、列表框、滑塊等控件時(shí)，要考慮到盡量簡(jiǎn)單，并有意減少使用的個(gè)數(shù)，不要把儀表板變成數(shù)據(jù)游戲的道具。交互體驗(yàn)越是繁雜，使用者獲取到的信息就越少，甚至有時(shí)會(huì)嚴(yán)重影響使用者的決策判斷。

2．自然

儀表盤(pán)設(shè)計(jì)時(shí)應(yīng)充分考慮使用者將如何使用這些交互，其應(yīng)該可以方便地從多個(gè)維度去解釋數(shù)據(jù)。站在使用者的角度去考慮：?jiǎn)螕舭粹o、鏈接等應(yīng)該有怎樣的外觀，并通過(guò)使用者眼球和鼠標(biāo)單擊的動(dòng)線設(shè)計(jì)來(lái)決定其放置位置，使作業(yè)流程無(wú)縫嵌入到儀表盤(pán)，具有自然流暢的使用感受，這樣使用者的關(guān)注焦點(diǎn)就被鎖定在了數(shù)據(jù)上。

3．接口

使用Excel來(lái)實(shí)現(xiàn)儀表盤(pán)的交互時(shí)，不同圖表部件間的關(guān)聯(lián)關(guān)系都是通過(guò)函數(shù)公式或定義名稱完成。規(guī)劃和設(shè)計(jì)這些公式時(shí)，必須要考慮儀表板的升級(jí)和維護(hù)作業(yè)，將公式設(shè)計(jì)得簡(jiǎn)單易讀將會(huì)是省時(shí)省力的一個(gè)好習(xí)慣。

案例

圖15.3-1是在圖15.2-1儀表板案例基礎(chǔ)上添加了交互控件的效果，其中滾動(dòng)條用來(lái)使6個(gè)儀表盤(pán)顯示具體到某個(gè)月的數(shù)據(jù)，下拉列表框則用來(lái)切換6個(gè)儀表盤(pán)顯示特定的銷售數(shù)據(jù)，同時(shí)兩個(gè)條形圖也和這兩個(gè)控件關(guān)聯(lián)。筆者在第14章提供的滑塊控件和4個(gè)折線圖關(guān)聯(lián)，4個(gè)折線圖同時(shí)和當(dāng)前選中商品相關(guān)聯(lián)，6個(gè)商品的數(shù)據(jù)切換，則使用儀表盤(pán)的VBA圖表事件來(lái)完成。以下是詳細(xì)制作步驟：

圖15.3-1　在圖15.2-1案例基礎(chǔ)上增加了交互的效果

1．在“Test1”工作表6個(gè)儀表盤(pán)下方添加如圖15.3-1所示的滾動(dòng)條工作表控件，該控件和“Q17”單元格相關(guān)聯(lián)，如圖15.3-2所示。

圖15.3-2　制作步驟1～3圖示

2．在“Test1”工作表單元格區(qū)域“M17:O17”間增加下拉列表框工作表控件，數(shù)據(jù)源區(qū)域?yàn)椤吧唐?”儀表盤(pán)下方的“C11:D13”單元格（如圖15.3-3所示），并和單元格“L17”相關(guān)聯(lián)，將單元格區(qū)域“K17:Q17”設(shè)置和圖15.3-2所示一致。

圖15.3-3　制作步驟2圖示

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

=TEXT(OFFSET(Data!$J$5,$Q$17,0),"yyyy-mm")&"月"& CHOOSE($L$17,"零售","批發(fā)","網(wǎng)絡(luò)")&"數(shù)量比"結(jié)果如圖15.3-2所示。

4．依次鍵盤(pán)Ctrl鍵，將“Test1”工作表6個(gè)儀表盤(pán)名稱更改為“Chart 1”…“Chart 6”，在VBE中添加如下代碼段，以完成儀表盤(pán)為觸發(fā)器的設(shè)定：

程序代碼：15.3-1　ChartClass類模塊XChart_MouseDown　圖表鼠標(biāo)單擊事件

程序代碼：15.3-2　模塊1中的XChart_MouseDown　將儀表盤(pán)和類模塊關(guān)聯(lián)

程序代碼：15.3-3　模塊1中的XChart_MouseDown　釋放儀表盤(pán)和類模塊的關(guān)聯(lián)

注：此處設(shè)定顯示比例的目的是筆者在第14章提供的滑塊控件在激活類模塊關(guān)聯(lián)時(shí)，非100％顯示比例時(shí)，該控件的外觀大小會(huì)改變。

5．在VBE中添加如下代碼段，以完成圖表事件類模塊初始化和釋放關(guān)聯(lián)到工作簿的打開(kāi)與關(guān)閉事件中：

程序代碼：15.3-4　工作簿打開(kāi)事件

注：此處Application.OnTime是因?yàn)閃orkbook_Open事件發(fā)生時(shí)，直接執(zhí)行將儀表盤(pán)和類模塊的關(guān)聯(lián)往往不起作用，所以需要待Excel工作簿完全被加載。

程序代碼：15.3-5　工作簿關(guān)閉事件

6．依次設(shè)定“Test1”工作表的B4、K4、T4、AC4、AL4、AU4這6個(gè)單元格的條件格式如圖15.3-4所示：

圖15.3-4　儀表盤(pán)是否選中的條件格式

7．依次設(shè)置“Chart”工作表E5、G5、I5、K5、M5、O5單元格和E7、G7、I7、K7、M7、O7單元格的公式，該公式可參見(jiàn)圖15.3-5箭頭所指位置，不同商品請(qǐng)注意公式中OFFSET函數(shù)的rows參數(shù)：129所乘系數(shù)依次為0～5。如圖15.3-5所示：

圖15.3-5　儀表盤(pán)是否選中的條件格式

8．在“Test1”工作表中依次設(shè)定儀表盤(pán)相對(duì)應(yīng)的數(shù)據(jù)狀態(tài)標(biāo)示，圖15.3-6以“商品1”為例說(shuō)明了相關(guān)設(shè)定，其他5個(gè)儀表盤(pán)的設(shè)定與此相仿。

圖15.3-6　儀表盤(pán)是否選中的條件格式

9．在“Test1”工作表“T19:BB19”單元格區(qū)域中添加筆者在第14章提供的滑塊控件（在窗體控件的其他控件中查找控件)，并設(shè)置“AZ17:BC17”單元格區(qū)域外觀樣式如圖15.3-7所示。在VBE中添加如下代碼段：

圖15.3-7　制作步驟9圖示

程序代碼：15.3-4　Test1工作表的ctlRangeSlider1_Change　滑塊滑動(dòng)事件

注：此處沒(méi)有使用控件ctlRangeSlider1_Scroll的事件，是因?yàn)榛瑒?dòng)過(guò)程頻繁的Excel函數(shù)公式的自動(dòng)重算，會(huì)導(dǎo)致執(zhí)行效率低下。

10．合并“Test1”工作表“T17:AD18”單元格區(qū)域，并添加如下公式：

結(jié)果如圖15.3-8所示。

圖15.3-8　制作步驟10圖示

11．合并“Test1”工作表“C33:J34”單元格區(qū)域，并添加如下公式：

結(jié)果如圖15.3-9所示。

圖15.3-9　制作步驟11圖示

12．添加如下定義名稱：

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

不同商品的數(shù)量對(duì)比

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

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

3類銷售數(shù)量對(duì)比

系列1：=SERIES("數(shù)量比較",Data!$D$5:$F$5,Test1!Bdata,1)

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

零售數(shù)據(jù)

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

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

批發(fā)數(shù)據(jù)

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

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

網(wǎng)購(gòu)數(shù)據(jù)

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

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

平均單價(jià)

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

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

14．向商品1的儀表盤(pán)圖表中添加一個(gè)矩形，并將其拖曳到大小與繪圖區(qū)相同，設(shè)置為無(wú)邊框，透明度100％，然后修改其名稱為“點(diǎn)擊查看商品1細(xì)節(jié)數(shù)據(jù)”。選中并復(fù)制到其他5個(gè)儀表盤(pán)，同時(shí)根據(jù)儀表盤(pán)對(duì)應(yīng)的商品名修改矩形名稱。

注：此處使用矩形的目的是，當(dāng)鼠標(biāo)指向儀表盤(pán)時(shí)提供反饋給使用者去單擊儀表盤(pán)，切換不同商品的詳細(xì)數(shù)據(jù)，但在Excel 2007中該方法無(wú)法提供反饋。

學(xué)習(xí)思考

特別說(shuō)明：

1）該案例并未包含目標(biāo)值參考基準(zhǔn)線，儀表盤(pán)的數(shù)據(jù)狀態(tài)都是和歷史最佳狀態(tài)相比較的結(jié)果，現(xiàn)實(shí)使用中，這個(gè)比較基準(zhǔn)應(yīng)該是動(dòng)態(tài)給出的。

2）這個(gè)案例由于要考慮多個(gè)版本的兼容問(wèn)題，并沒(méi)有過(guò)多美化，如果借助2007和2010可進(jìn)行適當(dāng)美化。

3）使用時(shí)需特別注意筆者提供的滑塊控件的使用限制。