Excel高阶图表，牛到不行了

今天为大家分享一个非常实用的图表技巧。
点击左侧省市二级联动切片器，即可控制整个可视化仪表板。对于单个图表，设有放大镜功能，以便更清晰地查看图表内容。
闲言少叙，先上效果图：

涉及的知识点：

切片器（链接数据透视表，切片器格式化）
Worksheet_PivotTableUpdate透视表更新事件
VBA自动筛选程序（AutoFilter代码用于筛选该城市经销商）
定义名称（offset函数动态引用区域，用于制作图表时添加序列）
通过添加序列的方式制作常规图表
VBA代码添加放大及返回的宏按钮

之前在Excel连接SQL Server数据库的文章中，曾经介绍过切片器及数据透视表更新事件的使用。本例中与其原理完全一致。
不太容易构思之处在于：
1）通过VBA自动筛选程序将该城市自动筛选后，粘贴进指定工作表，继而通过定义好的名称动态捕捉作图数据，用于生成各种图表。
2）用切片器替代了省市二级下拉菜单的制作，不仅制作过程简单，而且选择项目方便，目视设计美观。

01
—
图表构思和数据整理
根据待分析的数据源和期望呈现的可视化仪表板效果，勾勒草图，以终为始，寻找到能实现和可实现两者的平衡点。按此要求整合数据。
本例中用到的是汽车行业经销商分析数据，整理格式如下：

02
—

准备数据透视表和切片器
2.1为仪表板准备由省市数据源制作的数据透视表，将城市拖入行标签。

注：为了数据透视表更新事件能正常运行，这里建议将透视表与仪表板放在同一张工作表内。
2.2为仪表板准备选择控件-切片器

2.3切片器自定义格式化
选中要设置格式的切片器-选项-新建切片器样式。这里可以对整个切片器，以及页面，已选择的带有/无数据的项目，悬停带有/无数据的项目进行自定义格式化。本例中比较巧妙的一点是对于"已取消选择的无数据的项目"均设置为黑色，相当于将城市切片器上不相关的选项卡"藏"了起来。

此外，还可以右键单击切片器，在大小和属性中进行设置。在框架-按钮高度可设置按钮的高度和宽度以及列数，设置好后勾选禁止调整大小和移动，这样可以避免用户更改你的自定义切片器格式。为了更好滴避免被误操作，还需进行工作表保护，为了不影响切片器功能，还要在属性中将说定复选框取消勾选。

通过数据透视表更新事件，在点击切片器时，触发事件，执行相应代码，实现动态效果。在此过程中，切片器充当的是等同于常规控件的作用，但其交互效果更好，尤其是对切片器进行自定义格式化后。

03
—

设置数据透视表更新事件
数据透视表更新事件PivotTableUpdate：VBA中的事件就像是一个声控开关，当有声音响起时灯就会亮起，同理当数据透视表更新时，就会触发事件代码运行。这里切片器对数据透视表进行切片时，相当于一个声控开关，Excel会捕捉到这一瞬间，并执行以下蓝色字体的自定义代码。本案中是对相应城市的所有经销商进行自动筛选，并将筛选结果粘贴到新表中，用于定义名称及生成动态图表。

这段代码实现的功能是将第一步准备的数据源表，以第一行为筛选行，以第31列（城市列）为筛选字段，以仪表板工作表的CB3单元格（数据透视表切片后结果单元格，即城市）为筛选条件进行自动筛选，将结果粘贴到“抓取”工作表中，用于下一步制图。

04
—

定义名称动态引用区域
定义名称被广泛地应用于动态交互式图表中，通过OFFSET函数生成一个动态引用区域，并在生成图表时将该定义好的名称用于图表的数据区域中，使图表捕捉的制图数据源区域具备自动延展的动态效果。

注：

因每个城市经销商数量不同，COUNTA函数起到了计算非空行也即城市数量的作用；
对于没有经销商的城市，这里外套了一个if函数，即将行标题作为结果返回。否则会弹出定义名称引用区域错误的对话框。

05
—

添加序列制作图表
常规图表中的图形数据源，是由不同的序列组成的。以二手车销量和置换的图表为例，这里定义了两个序列和一个水平分类轴标签，分别为：
二手车置换量，数据引用区域='4.切片器控制仪表板.xlsm'!二手车置换量
二手车销售量，数据引用区域='4.切片器控制仪表板.xlsm'!二手车销售量
水平分类轴标签，数据引用区域='4.切片器控制仪表板.xlsm'!经销商名称
注：这里的数据引用区域引用的是之前定义过的名称，这一步非常关键。

通过以上操作，即可生成二手车的包含两个序列的图表，并且这个图表的水平分类轴标签和Y轴序列所对应的数据区域，均是根据该城市的经销商数量自动延展的，没有任何冗余。

06
—

添加放大和返回按钮
通过选择对应的range区域，设置合适的缩放级别，便可实现放大和返回的效果。
Sub 返回()
Application.ScreenUpdating = False '暂停刷新屏幕
Sheets("仪表板").Select
ActiveWindow.Zoom = 40
Application.ScreenUpdating = True '恢复刷新屏幕
Range("A1").Select
End Sub

Sub 放大图表1()
Application.ScreenUpdating = False '暂停刷新屏幕
Application.DisplayFullScreen = True
Sheets("仪表板").Select
Range("A1:Q37").Select
ActiveWindow.Zoom = True
Application.ScreenUpdating = True '恢复刷新屏幕
End Sub

Sub 放大图表2()
Application.ScreenUpdating = False '暂停刷新屏幕
Application.DisplayFullScreen = True
Sheets("仪表板").Select
Range("Q1:AF37").Select
ActiveWindow.Zoom = True
Application.ScreenUpdating = True '恢复刷新屏幕
End Sub
其他图表放大代码依此类推即可。在开发工具插入宏按钮，将以上代码指定给相应宏按钮，将宏按钮放置在每个图表的右下角即可。

至此，大功告成。

写到这，可能有人心里会有疑问：切片器和数据透视图的组合，不是最经典的用法并且自带交互效果吗？
本方法与切片器常规应用方法的区别

常规用法：以数据透视表为桥梁，将切片器与数据透视图进行关联，让切片器充当动态交互式图表的控件，对透视表切片可在透视图中取得动态交互效果。数据透视图中展示的数据是按照切片器筛选条件，在透视表中运算后的聚合结果。

本例方法：并未将切片器与数据透视图进行关联，而是以切片器为开关，触发了数据透视表更新事件，后续通过自动筛选和定义名称，生成了系列图表。这是两者本质上的差别。

怎么样，小伙伴们学会了吗？如果你觉得有帮助，请记得给我好看哦。

如需示例文件，可长按以下二维码关注作者微信号后，在作者微信号回复关键词"仪表盘"即可。

Excel知识管理

[url=][/url]
专业的职场技能充电站