• <tr id="437819f3"><strong id="5ba058d0"></strong><small id="664e939c"></small><button id="3f1e9043"></button><li id="64c67bcc"><noscript id="695d4392"><big id="75993963"></big><dt id="6e54f94"></dt></noscript> </tr><ol id="4aed682f"><option id="1bbdc2be"><table id="250a83a4"><blockquote id="5f2c4114"><tbody id="15a709fe"></tbody></blockquote></table></option></ol><u id="6a18b606"></u><kbd id="6185d2c"><kbd id="7013003b"></kbd></kbd>
  • 千赢国际教程下载和软件下载中心,Microsoft技术社区联盟成员,全球极具影响力的千赢国际门户,Office技术培训社区

    千赢国际高阶图表,牛到不行了

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


    涉及的知识点:
    • 切片器(链接数据透视表,切片器格式化)
    • Worksheet_PivotTableUpdate透视表更新事件
    • VBA自动筛选程序(AutoFilter代码用于筛选该城市经销商)
    • 定义名称(offset函数动态引用区域,用于制作图表时添加序列)
    • 通过添加序列的方式制作常规图表
    • VBA代码添加放大及返回的宏按钮 之前在千赢国际连接SQL Server数据库的文章中,曾经介绍过切片器及数据透视表更新事件的使用。本例中与其原理完全一致。

      不太容易构思之处在于:
      1)通过VBA自动筛选程序将该城市自动筛选后,粘贴进指定工作表,继而通过定义好的名称动态捕捉作图数据,用于生成各种图表。
      2)用切片器替代了省市二级下拉菜单的制作,不仅制作过程简单,而且选择项目方便,目视设计美观。

      01

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


      02

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


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


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

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


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

      03

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



      这段代码实现的功能是将第一步准备的数据源表,以第一行为筛选行,以第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

      其他图表放大代码依此类推即可。在开发工具插入宏按钮,将以上代码指定给相应宏按钮,将宏按钮放置在每个图表的右下角即可。
      至此,大功告成。
      写到这,可能有人心里会有疑问:切片器和数据透视图的组合,不是最经典的用法并且自带交互效果吗?
      本方法与切片器常规应用方法的区别
      常规用法:以数据透视表为桥梁,将切片器与数据透视图进行关联,让切片器充当动态交互式图表的控件,对透视表切片可在透视图中取得动态交互效果。数据透视图中展示的数据是按照切片器筛选条件,在透视表中运算后的聚合结果。

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

      转载请注明来源"千赢国际Home"并保留原文链接。
      固定链接:http://ryansdrew.com/lesson/animation/excel/1944.html
    分享到:
    Copyright 1999 - 2017 千赢国际 Home.All Rights Reserved.
    本站特聘法律顾问:徐怀玉律师 李志群律师   沪ICP备11019229号
           
    网站地图