另辟蹊径的交互式仪表板

hercules028 2019-04-19

展开全文

涉及的知识点：

单选按钮作为控件并为其指定宏；
VBA为矢量图设置颜色及透明度；
单击某个城市时，设置其突出显示效果（红色外框线）；
index和vlookup查询函数的使用；
基础图表：柱状图、条形图、折线图、饼状图的制作及美化。

因之前的文章中详细地介绍过数据地图制作方法，本文对于一些细节将不再赘述。只重点介绍一些制图的关键点。

—

城市数据准备

全国各城市数据地图制作方法，与制作全国各省份及省内各城市基本一致，只不过这里边是对全国342个城市都进行命名，并通过For循环一次性为其指定颜色和透明度。用到的数据及透明度数值计算过程如下：

注：按如上格式准备数据源，其中index函数用于根据“当前单选按钮所选”来查询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)”

—

制作数据地图

设置四个单选按钮，分别按需求命名。设置其链接单元格为model工作表中B3单元格。并为其赋宏：fill_nationcolor。

其中ThisWorkbook.fill_nationcolor代码如下：

Sub fill_nationcolor() Application.ScreenUpdating = False '暂停刷新屏幕 '根据单选按钮的值为不同指标设置不同颜色 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 '为数据源的起始和结束行号

        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

注：以上代码在新建一个模型时需要手动运行一次。

—

创建仪表盘

单击某城市时，会将该城市名字传递给Nationmap工作表U2单元格，并以此单元格作为vlookup函数的查询依据，将查询结果存储在J3:W3单元格。制图时分别从J3:W3单元格取数制作柱状图、条形图、折线图、饼状图并美化即可。