数据透视表教程行列怎么设置(20个常用的数据透视表技巧)

数据透视表是Excel中最常用的技巧,也是最实用的技巧,没有之一。

本文将对数据透视表中的一些常用技巧进行介绍,共20个技巧,分五类。

一、基本操作

1、创建数据透视表

菜单【插入】中,选择“数据透视表”,打开“创建数据透视表”对话框,选择数据源。这里选择数据源区域A1:E8,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(1)

选择完数据源之后,“确定”,生成如下图所示的数据透视表。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(2)

例如,要分析不同类型的金额,只需要将对应字段拖到右下角区域,就得到不同类型的金额总和。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(3)

2、数据源刷新

如果修改了数据源,那么只需要单击菜单中的“刷新”按钮,即可更新数据透视表。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(4)

3、更改数据源

通过菜单中的“更改数据源”可以修改数据透视表的数据源。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(5)

4、数据透视表筛选

通过数据透视表的筛选器,可以筛选数据透视表。

例如,需要按照日期进行筛选,将日期字段拖到“筛选”中即可,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(6)

5、删除数据透视表

删除透视表也很简单,全选之后,按“Delete”键,即可删除数据透视表。

二、布局样式

1、套用样式

通过Excel自带的数据透视表样式可以对数据透视表进行美化,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(7)

2、以表格形式显示

有时候,选择了多个行字段,数据透视表会呈现出以下形式。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(8)

此时只需在“报表布局”中选择“以表格形式显示”即可。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(9)

以表格形式显示的数据透视表。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(10)

3、隐藏/显示汇总项

如果不想显示中间的分类汇总项,可以通过“分类汇总”-“不显示分类汇总”来隐藏分类汇总项。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(11)

4、重复所有项目标签

如果需要第一列的标签重复显示,选择“重复所有项目标签”即可。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(12)

5、合并标签所在单元格

如果希望标签居中显示,可以做如下图所示的设置。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(13)

右击鼠标,选择“数据透视表”选项,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(14)

勾选“合并且居中排列带标签的单元格”,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(15)

最终效果如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(16)

三、计算排序

1、更改计算方式在以上数据透视表中,对于“金额”字段的计算方式默认是求和,其实我们可以自行更改计算方式。

在值区域中,单击“求和项:金额”,打开“值字段设置”,可以选择计算方式,如求和、计数、平均值、最大值、最小值等。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(17)

2、求占比(百分比)

前面已经对金额进行了求和,如果想求占比,可以做如下操作。

(1)将“金额”再次拖到值区域,得到“求和项:金额2”。

(2)在“值显示方式”中,选择“列汇总的百分比”,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(18)

“确定”后,得到金额的占比。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(19)

3、插入计算字段

在数据透视表中,还可以创建计算字段。例如,给每个金额乘以一个折扣系数0.8,得到折扣后的金额。

依次选择:【计算】-【字段、项目和集】-【计算字段】,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(20)

计算字段名称:折扣金额,公式:金额*0.8。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(21)

输入完成后,单击“添加”按钮,得到一个新的字段“折扣金额”。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(22)

4、数据透视表排序

数据透视表的排序也很简单,光标移至需要排序的列,菜单【数据】中,选择升序(A-Z)或降序(Z-A)。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(23)

注意:数据透视表中,排序只在一个类别中进行,如上,分别在食品、书籍和油费中进行降序排列。

四、组合筛选

数据透视表中还有一个“组合”功能,能够对日期、数字等进行组合。

1、按月/季度/年汇总报表例如,对于以下数据透视表,将日期字段放到了行区域。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(24)

在1月单元格,右击鼠标,选择“组合”,在“组合”对话框中,把季度、年选上。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(25)

“确定”后,可以看到数据透视表可以按季度、年份进行汇总。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(26)

2、求分段占比

对于数字类型的字段,还可以分段统计。

例如,以下数据透视表,想对年龄进行分段统计。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(27)

在“年龄”列右击鼠标,选择“组合”,在“组合”对话框中,可以设置起始值、终止值及步长。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(28)

设置完成后,就实现了对年龄的分段统计,统计出了各年龄段的购买人数、金额总和。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(29)

3、插入切片器

切片器其实就是筛选器。

例如,对于以上数据透视表,插入一个切片器。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(30)

勾选“类型”,然后“确定”,这样就得到一个切片器,它能够按照类型进行筛选。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(31)

说明:选中右上角的多选按钮,还能进行多选。

4、插入日程表(日期控件)

日程表其实就是日期控件,通过它能够对数据透视表按照日期筛选。

插入日程表,勾选“日期”,然后“确定”。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(32)

有了这个日期控件,就可以按照年/季度/月/日对数据进行多维度筛选了,拖动底部的滑块即可。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(33)

五、其他操作

1、数据透视图数据透视图,顾名思义,数据透视表 图表。

插入数据透视图,如下图所示。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(34)

选择数据源后,确定,构造如下数据透视表,会自动生成对应的图表,在此图表中,还可以按照字段“类型”对图表进行筛选。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(35)

2、多表合并透视

还可以对多个表格中的数据进行合并透视,例如,有以下三个月份的数据。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(36)

说明:这3个月份的数据分布在3个sheet中,这里为了方便介绍,截图放在一起。

如何进行多表合并透视?

(1)按一下Alt键,然后按一下D键,最后按一下P键,打开数据透视表向导对话框。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(37)

(2)选择“多重合并计算数据区域”,然后“下一步”。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(38)

(3)选择“创建单页字段”,然后“下一步”。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(39)

选择需要合并的数据区域,选择一个区域后,单击“添加”按钮。这里选择1月、2月、3月这3个sheet中的数据区域。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(40)

“下一步”后,得到如下图所示对话框,这里不用选,默认现有工作表。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(41)

单击“完成”按钮后,得到多表合并的数据透视表。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(42)

这个数据透视表是3个sheet中的数据合并的结果,可以按照“页”(表)来对数据进行筛选。

数据透视表教程行列怎么设置(20个常用的数据透视表技巧)(43)

以上是关于数据透视表的20个常用技巧,大家按需取用,希望能够给大家带来一些帮助!

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页