依据同一数据源创建的多个数据透视图表,加上切片器的随点随筛选,可以实时查看动态结果,则用Excel就可以制作出多个动态图表联动的可视化动态仪表盘。按此方法做的动态图表,当数据源进行更新时,只需对切片器进行[刷新],就能实现对数据透视图表的更新。

一、数据透视图表

1、年订货量变化

插入透视表,以[订货日期]作为分类标签,并将[订货日期]进行组合,组合成年、季度、月份三种尺度,并保留年。再将[数量]作为统计项,得到各年的订货数量统计。

选中透视表任一单元格,在菜单栏[插入]选项卡,选择[簇状柱形图],用柱状图进行呈现。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

用鼠标右键单击图表中的[求和项:数量],在弹出的快捷菜单中选择[隐藏图表上的所有字段按钮]

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

再删除图例,更改图表标题。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

2、年订货额变化

插入透视表,以[年]为分类标签。再将[金额]作为统计项,用[簇状柱形图]呈现。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

3、各月订货量变化

插入透视表,以[月]为分类标签。再将[数量]作为统计项,用[带数据标记的折线图]呈现。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

4、各月订货金额变化

插入透视表,以[月]为分类标签。再将[金额]作为统计项,用[带数据标记的折线图]呈现。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

5、按螺纹规格的订货金额排名

插入透视表,以[螺纹规格]为分类标签。再将[金额]作为统计项,[求和项:金额][升序]排列。用[簇状条形图]呈现,删除图表的[水平(值)轴],去掉[网格线]

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

6、各季度订货金额分布

插入透视表,以[季度]为分类标签。再将[金额]作为统计项,用[圆环图]呈现,[数据标签][类别名称][百分比]设置,[圆环图圆环大小]50%设置。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

7、区域订货金额占比

展示某个区域在整体中所占的比重,如要进行动态展现,需要用两个数据透视表,一个用来获取指定区域订货金额,另一个用来获取所有区域的订货金额,再将这两个数据进行一些换算,这样就能使用饼图或圆环图展示占比关系。

插入透视表,以[客户区域]为分类标签,再将[金额]作为统计项。作为选定区域的数据。

插入透视表,以[年]为分类标签,再将[金额]作为统计项。作为所有区域的数据。

将以上两个透视表的总计数据引用到辅助区域,用选定区域剩余区域的数据,插入[圆环图],将选定区域[数据标签] [百分比]设置,[圆环图圆环大小]50%设置。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

二、配置切片器

1、插入切片器

选中任一数据透视表,在菜单栏[数据透视表分析]选项卡,选择[插入切片器],在弹出的[插入切片器]对话框,分别选择螺纹规格客户区域,插入三个切片器。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

2、连接切片器与数据透视图表

要使其他数据透视图表根据不同的螺纹规格、年份、客户区域进行筛选,可以用同一个切片器来控制,这时就需要把切片器连接到这些数据透视图表。

用鼠标右键单击切片器[螺纹规格],在弹出的快捷菜单选择[报表连接],在弹出的[数据透视表连接(螺纹规格)]对话框中,勾选需要使用螺纹规格进行筛选的数据透视表。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

再将切片器[年][客户区域]也按此设置。

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

三、数据仪表盘

将图表、切片器全部复制到一个新的工作表中,并进行适当排版。

小技巧:在拖拽或拉动图表时,按住ALT键,图表可自动锚定到Excel单元格,这样排版看起来很整齐。

2021东部区域的M12螺栓订货情况。见下图:

excel中用切片器和透视表制作动态数据图表-趣帮office教程网

2019西北区域的M16螺栓订货情况。见下图:

excel中用切片器和透视表制作动态数据图表-趣帮office教程网