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）使用時需特別注意筆者提供的滑塊控件的使用限制。