1. 遇到的问题:

在Excel中,用PivotTable来做数据报告展示:

问题1:在同一个Sheet页里,多个PivotTable如何实现同步刷新?

问题2:在不同Sheet页之间,多个PivotTable如何实现同步刷新?

2. 前提介绍

在同一个Sheet页里面,有两个PivotTable,数据源在同一个模型中,维度相同,由于种种原因(不同的度量值等等,在这里不再深究为什么分开,只谈分开之后如何处理),需要分开两个PivotTable显示。

Pivot Table系列之切片器 (Slicer)-LMLPHP

3. 数据解释

如上图所示,例如第一个PivotTable的数据是每个月的实际发生数据,每个月会保存一版月度最终版本数据;第二个PivotTable是全年的预测数据。

4. 需求

需要查看不同版本的实际月份时,在同一年全年预测数据不变的;同时切换到不同年份的数据版本时,第二个PivotTable的年度预测数据需要随之显示成当前年的预测数据。

5. 问题现象

如上图所示,当使用过滤器(Filter)进行数据版本切换时,第二个PivotTable是没有随之发生联动的;也就是说第一个PivotTable的过滤器的作用域只是自己的PivotTable。

6. 解决办法

使用切片器(Slicer)进行同步刷新多个PivotTable

1)      切片器位置

ANALYZE 选项卡中,Filter组中。

Pivot Table系列之切片器 (Slicer)-LMLPHP

2)      点击【Insert Slicer】来插入一个切片器

3)      在弹出的窗口中,会显示当前数据集使用的维度和事实;也可以点击[全部]来切换到全部的维度和事实。

在这里,我们选择使用DIM_MONTH_VERSION来作为切片器的筛选条件。

Pivot Table系列之切片器 (Slicer)-LMLPHP

Pivot Table系列之切片器 (Slicer)-LMLPHP

4)      点击OK,切片器创建成功

注:

黑色维度:表示在事实表中存在此维度数据,如201512、201608、201612;

同理,灰色维度:表示在事实表中不存在此维度数据,如201501等等。

Pivot Table系列之切片器 (Slicer)-LMLPHP

5)      创建成功之后,依然发现两个PivotTable没有同步数据。

第二个PivotTable的MONTH_KEY筛选器还是All.

Pivot Table系列之切片器 (Slicer)-LMLPHP

6)      此时,我们需要对切片器的作用域进行设置。因为在创建切片器时,选中了其中的一个PivotTable,所以默认的作用域就是当前PivotTable。

在切片器上,点击鼠标右键,选择【Report Connections…】

Pivot Table系列之切片器 (Slicer)-LMLPHP

7)      会发现在当前Sheet2页中,只有PivotTable1 (第一个PivotTable)被选中了。为了把PivotTable2加入到切片器的作用域中,把PivotTable2也选中;然后OK。

Pivot Table系列之切片器 (Slicer)-LMLPHP

Pivot Table系列之切片器 (Slicer)-LMLPHP

8)      然后,我们看到第二个PivotTable的MONTH_KEY的Filter值也变成了201512。

Pivot Table系列之切片器 (Slicer)-LMLPHP

这里并不是手动在第二个PivotTable选择MONTH_KEY的结果;为了澄清结果,把两个PivotTable的MONTH_KEY的Filter去掉

Pivot Table系列之切片器 (Slicer)-LMLPHP

点击切片器,切换到201608数据版本。

Pivot Table系列之切片器 (Slicer)-LMLPHP

9)      同理,可以设置多个PivotTable数据同步,即使不在一个Sheet页里面,也是可以设置生效的,因为在切片器的Report Connections属性卡里面列出来当前Excel文件所有的PivotTable.

Pivot Table系列之切片器 (Slicer)-LMLPHP

7. 切片器其他属性介绍

在切片器的右键属性中

Pivot Table系列之切片器 (Slicer)-LMLPHP

1)      刷新(Refresh)

Pivot Table系列之切片器 (Slicer)-LMLPHP
刷新切片器和切片器作用域内所有PivotTable。

刷新切片器的意思是:如果在事实表中插入了201501版本的数据,那么点击刷新之后,会在切片器中把201501显示成黑色,来表示事实表中事实数据存在;即使只有一个事实表中存在,也会表示出来。

2)      排序功能

Pivot Table系列之切片器 (Slicer)-LMLPHP

从小到大,从大到小,按照数据源顺序。比较简单,可以点击看不同结果。

3)      清空筛选条件…(Clear Filter from…)

Pivot Table系列之切片器 (Slicer)-LMLPHP

会选中所有切片器中维度的所有数据。然后变灰,如果想再次选择数据版本,只需点击切片器数据版本即可。

Pivot Table系列之切片器 (Slicer)-LMLPHP

4)      报表连接…(Report Connections…)

Pivot Table系列之切片器 (Slicer)-LMLPHP

如上示例所示,对当前Excel文件的所有PivotTable的设置切片器的作用域。

5)      移除…(Remove…)

Pivot Table系列之切片器 (Slicer)-LMLPHP

即删除当前切片器

6)      组合(Group)

如果有多个切片器,可以对某几个或者全部切片器,进行组合,以方便显示和拖拽。

Pivot Table系列之切片器 (Slicer)-LMLPHP

7)      前置显示/后置显示(Bring to Front/Send to Back/)

设置切片器显示的层次。

8)      指定宏…(Assign Macro..)

Pivot Table系列之切片器 (Slicer)-LMLPHP

关联VBA脚本,来设置点击切片器或者切片器发生变化时,需要触发的事件;处理的逻辑需要在VBA中开发完成。

9)      尺寸和属性(Size and Property…)

Pivot Table系列之切片器 (Slicer)-LMLPHP

根据需要设置布局位置的外观。

10)   切片器设置(Slicer Setting)

Pivot Table系列之切片器 (Slicer)-LMLPHP

Pivot Table系列之切片器 (Slicer)-LMLPHP

  • 名称(Name): 给切片器命名。在文件中存在多个切片器时,可以以名字来区分。
  • 显示头部(Display header):是否显示切片器表头。

   不显示

  Pivot Table系列之切片器 (Slicer)-LMLPHP

显示

Pivot Table系列之切片器 (Slicer)-LMLPHP

  • 标题(Caption)

表头位置显示的标题内容。方便用户理解。

例如:以上示例中,可以命名成Month Version

Pivot Table系列之切片器 (Slicer)-LMLPHP

Pivot Table系列之切片器 (Slicer)-LMLPHP

  • 排序(Item Sorting)

同排序功能

  • 显示筛选(Item Filtering)

是否需要隐藏没有数据的维度

例如:

  • 如果选择隐藏没有数据的维度

  Pivot Table系列之切片器 (Slicer)-LMLPHP

  灰色维度数据即不显示

  Pivot Table系列之切片器 (Slicer)-LMLPHP

  • 如果选择显示没有数据的维度

直观显示没有数据的维度(Visually indicate items with no data)和在最后显示没有数据的维度(Show item with no data last)是默认选择的。

实际显示效果,可以尝试取消选择进行测试。

Pivot Table系列之切片器 (Slicer)-LMLPHP

8. 使用注意

1)      切片器就是一个特殊的筛选器,能扩大筛选器的作用域

2)      如果把切片器和筛选器同时设定,它们之间的是一致的。

05-06 22:26