另辟蹊徑的交互式儀表板

hercules028 2019-04-19

展開全文

涉及的知識點：

單選按鈕作為控件并為其指定宏；
VBA為矢量圖設置顏色及透明度；
單擊某個城市時，設置其突出顯示效果（紅色外框線）；
index和vlookup查詢函數(shù)的使用；
基礎圖表：柱狀圖、條形圖、折線圖、餅狀圖的制作及美化。

因之前的文章中詳細地介紹過數(shù)據(jù)地圖制作方法，本文對于一些細節(jié)將不再贅述。只重點介紹一些制圖的關鍵點。

—

城市數(shù)據(jù)準備

全國各城市數(shù)據(jù)地圖制作方法，與制作全國各省份及省內(nèi)各城市基本一致，只不過這里邊是對全國342個城市都進行命名，并通過For循環(huán)一次性為其指定顏色和透明度。用到的數(shù)據(jù)及透明度數(shù)值計算過程如下：

注：按如上格式準備數(shù)據(jù)源，其中index函數(shù)用于根據(jù)“當前單選按鈕所選”來查詢D列的指標值，繼而用于E列透明度的計算。透明度計算過程，用到了如下公式。設置好首行公式后，向下填充即可。

D3單元格公式為
“=INDEX($F$6:$I$347,ROW(A1),$B$3)”
E3單元格公式為
“=($G$3-$D6)/($G$3-$I$3)*90%”
'Max:'也即G3單元格公式為
“=MAX($D$6:$D$347)”
'Min:'也即I3單元格公式為
“=MAX($D$6:$D$347)”

—

制作數(shù)據(jù)地圖

設置四個單選按鈕，分別按需求命名。設置其鏈接單元格為model工作表中B3單元格。并為其賦宏：fill_nationcolor。

其中ThisWorkbook.fill_nationcolor代碼如下：

Sub fill_nationcolor() Application.ScreenUpdating = False '暫停刷新屏幕 '根據(jù)單選按鈕的值為不同指標設置不同顏色 Select Case Range('model!B3').Value Case 1: Range('model!E3').Interior.ColorIndex = 1 Case 2: Range('model!E3').Interior.ColorIndex = 53 Case 3: Range('model!E3').Interior.ColorIndex = 52 Case 4: Range('model!E3').Interior.ColorIndex = 51 End Select For I = 6 To 347 '為數(shù)據(jù)源的起始和結束行號

        Sheets('Nationmap').Shapes(Range('model!c' & I).Value).Fill.ForeColor.RGB = Range('model!E3').Interior.Color

'使用選定的顏色填充圖形 Sheets('Nationmap').Shapes(Range('model!c' & I).Value).Fill.Transparency = Range('model!e' & I).Value '按匹配的透明度值設置圖形的透明度 Next I Sheets('Nationmap').Shapes('Nationmap_legend').Fill.ForeColor.RGB = Range('model!E3').Interior.Color '設置圖例的填充色 Sheets('Nationmap').Shapes('Nationmap_legend').Fill.OneColorGradient msoGradientVertical, 2, 0.23 '設置圖例的漸變效果 Application.ScreenUpdating = True '恢復刷新屏幕 End Sub

—

單擊城市突出顯示

當想深入探索某一個城市時，可以將該城市的外框線顯示為紅色，作為告知用戶已選中該城市的交互式反饋。

Sub user_click_Nationmap()   Application.ScreenUpdating = False '暫停刷新屏幕   ActiveSheet.Shapes(Range('AZ1').Value).Line.ForeColor.SchemeColor = 23    '將之前選中的地圖圖形還原邊框色    Range('AZ1').Value = ActiveSheet.Shapes(Application.Caller).Name    '將當前選擇的地圖版塊名稱填值到AZ1單元格         ActiveSheet.Shapes(Range('AZ1').Value).Line.ForeColor.SchemeColor = 60    '將當前選中的地圖圖形邊框色設為紅色       Application.ScreenUpdating = True '恢復刷新屏幕 End Sub

—

批量設置突出顯示

利用宏代碼批量為全國342個城市設置單擊城市突出顯示，代碼如下：

Sub auto_add_macro_Nationmap() '新建一個模型時手動運行，一次性添加宏 For I = 1 To Sheets('Nationmap').Shapes.Count '5表示對象類型是地圖版塊 If Sheets('Nationmap').Shapes(I).Type = 5 Then Sheets('Nationmap').Shapes(I).OnAction = ''thisworkbook.user_click_Nationmap'' End If

Next I

End Sub

注：以上代碼在新建一個模型時需要手動運行一次。

—

創(chuàng)建儀表盤

單擊某城市時，會將該城市名字傳遞給Nationmap工作表U2單元格，并以此單元格作為vlookup函數(shù)的查詢依據(jù)，將查詢結果存儲在J3:W3單元格。制圖時分別從J3:W3單元格取數(shù)制作柱狀圖、條形圖、折線圖、餅狀圖并美化即可。