Excel透视表神技压箱底的干货助你快速完成工作

1、空值处理。

我们在对一组数据插入数据透视时经常会遇到值区域中某个字段对应数据为空白的情况。以往很多小伙伴都是手动修改,其实可以通过数据透视表自定义空白显示为0。(注:只针对值区域中的空白!)。举例:第一季度中的屏幕300*220项目购买数量为空白,现在需要将数据进行透视汇总处理。

Excel透视表神技压箱底的干货助你快速完成工作

完成数据透视后我们看到C13单元格为空白。

Excel透视表神技压箱底的干货助你快速完成工作

单击数据透视表右击鼠标,选择【数据透视表选项】。

Excel透视表神技压箱底的干货助你快速完成工作

打开【数据透视表选项】对话框,勾选【布局和格式】中的【对于空白单元格,显示】,同时在右侧的编辑栏中输入“无数据”。

Excel透视表神技压箱底的干货助你快速完成工作

单击确定后数据透视表中所有的空白将被“无数据”填充。

Excel透视表神技压箱底的干货助你快速完成工作

注意:这里我们可以将空白通过定义填充为任意文本、数字或者符号。

2、排名。

日常工作中经常需要将完成数据透视后的数据进行排名,很多小伙伴都是通过rank函数进行排名。其实数据透视表自带排名功能,根本无需排序、函数。还是以采购数据为例,现在我们完成了数据透视。

Excel透视表神技压箱底的干货助你快速完成工作

选中数据透视表右击鼠标,选择【值显示方式】,在子菜单中选择【降序排序】。

Excel透视表神技压箱底的干货助你快速完成工作

选择以项目为基本字段进行排序,单击【确定】。

Excel透视表神技压箱底的干货助你快速完成工作

最后我们看到原本的购买数据信息变成了排名信息。

Excel透视表神技压箱底的干货助你快速完成工作

如果我们需要同时保留购买数据以及排名信息,只需要在值字段中再次添加购买数量即可。

Excel透视表神技压箱底的干货助你快速完成工作

3、批量创建工作表。

批量创建工作表是日常工作中经常碰到的问题,比如创建分公司、月份、季度等工作表。如果数量少,我们可以通过手工逐一创建,如果数量很多该怎么办呢?其实可以通过数据透视表批量创建工作表。举例:现在我们需要创建4个季度的工作表。首先在表中输入表头“季度”,以及四个季度名称。

Excel透视表神技压箱底的干货助你快速完成工作

然后选中A列数据,单击【插入】选项卡中的【数据透视表】。

Excel透视表神技压箱底的干货助你快速完成工作

在打开的【创建数据透视表】对话框中,选择数据透视表的位置为现有工作表。

Excel透视表神技压箱底的干货助你快速完成工作

确定后将【季度】字段拖至筛选框内。

Excel透视表神技压箱底的干货助你快速完成工作

单击数据透视表,然后单击【分析】选项卡中【选项】-【显示报表筛选页】。

Excel透视表神技压箱底的干货助你快速完成工作

出现【显示报表筛选页】对话框,直接单击确定,我们就可以看到批量创建的工作表。

Excel透视表神技压箱底的干货助你快速完成工作

Excel透视表神技压箱底的干货助你快速完成工作

选中所创建的所有工作表,然后在任意一个工作表中选中表格中不需要的数据,选择“开始”-“清除”-“全部清除”,即可完成工作表的批量创建。

Excel透视表神技压箱底的干货助你快速完成工作

是不是很简单?

注:批量创建的工作表是自动按工作表名称排序的。譬如这里的第一到第四季度,创建出来的工作表依次是第二、第三、第四、第一季度。如果想按季度顺序创建工作表,则输入时改成阿拉伯数字,如第1、第2、第3、第4等季度。如果想按自己输入的名称顺序创建工作表,有一个简易方法,就是在输入时每个名称前依次添加阿拉伯数字1、2、3等,则工作表按输入顺序创建。

4、多表求和。

将同一工作簿中的多个同格式工作表汇总求和,也可以用数据透视表完成。

5、按新增字段分组统计

将数据按新增字段分组进行统计,也是经常做的一件事。譬如,数据中没有月份、季度,但领导要求你按月、按季度统计;数据中没有一等品、二等品、三等品,但领导要求你按一、二、三等品进行统计。对于这类把原始数据按新指定字段进行统计的,利用透视表可以非常简便的实现。例举两例。

例1:按日期分组统计 、数据源是按日登记的销售额。现在要按月、季度分组统计销售额。

Excel透视表神技压箱底的干货助你快速完成工作

(1)选中所有数据,插入数据透视表。

Excel透视表神技压箱底的干货助你快速完成工作

(2)将“销售日期”字段拖入行区域中,Excel会自动增加一个“月”字段(需要是2016版本),右侧透视表中行标签按月显示。(注:如果用的低版本,则需要按下方设置“季度”字段的方式进行设置,增加“月”字段后才能按月统计。)然后将“销量”拖入值区域中。

Excel透视表神技压箱底的干货助你快速完成工作

(3)下面我们通过分组设置,实现季度统计。在透视表行标签下任意一个数据上右击,选择“组合”命令(也可以单击【分析】-【分组字段】或【分组选择】)打开【组合】对话框。可以看到当前已经选中了两个步长“日”和“月”。

Excel透视表神技压箱底的干货助你快速完成工作

起始于、终止于数据会自动根据数据源生成,不用管它。

(4)单击“季度”,然后确定。

Excel透视表神技压箱底的干货助你快速完成工作

(5)可以看到数据透视表字段中增加了“季度”字段。在左侧的透视表中,单击Excel透视表神技压箱底的干货助你快速完成工作符号把数据折叠,就实现了按季度统计。

Excel透视表神技压箱底的干货助你快速完成工作

例2:分数分阶段统计

下表是某班的数学成绩,只有姓名和成绩两个字段。现在我们需要统计<60、60-79、80-100各阶段的人数。

Excel透视表神技压箱底的干货助你快速完成工作

(1)一样的,首先建立透视表。

Excel透视表神技压箱底的干货助你快速完成工作

(2)把“成绩”字段拖入行区域中。这时左侧透视表的行标签下方出现一列分数值。

Excel透视表神技压箱底的干货助你快速完成工作

(3)在透视表行标签下任意一个分数上右击,选择“组合”命令,打开组合对话框。

Excel透视表神技压箱底的干货助你快速完成工作

(4)现在按需要修改起始值和终止值、步长。设置起始于60,终止于100,步长20,如下。

Excel透视表神技压箱底的干货助你快速完成工作

(5)单击“确定”后,行标签变成了我们需要的三个分数段。

Excel透视表神技压箱底的干货助你快速完成工作

(6)将“成绩”字段拖到值区域中,实现了人数统计,如不及格的有11人。

Excel透视表神技压箱底的干货助你快速完成工作

(7)如果想进一步看到各阶段的姓名,则可以把“姓名”字段拖入行区域中。

Excel透视表神技压箱底的干货助你快速完成工作

如果想更自由分段,不受步长限制,那可以在第(3)步的时候改变做法。譬如选中0-59,右击,选择“组合”,生成“数据组1”,选中“数据组1”,在编辑栏中输入“D”,把“数据组1”改成“D”,这就是成绩D阶段;选中60-79,右击组合后改成“C”;选中80-90,右击组合后改成“B”;选中90以上的,右击组合后改成“A”。如此就把成绩分成了ABCD四个阶段进行统计。

Excel透视表神技压箱底的干货助你快速完成工作

6、非重复计数

从原始数据中统计机构数量、产品种数、经销商数量、供货商数量等等,属于典型的非重复计数。同样可以利用透视表完成。

总结

今天跟大家分享了6个数据透视表实用的技巧。这些技巧都很高效,可以取代复杂的函数工作,提高效率。大家在平时工作中多留意一些功能和选项,多一些思考,就会多挖掘一个技巧,让Excel运行更由心。

未经允许不得转载:飞鹰图表 » Excel透视表神技压箱底的干货助你快速完成工作
分享到:

评论抢沙发

评论前必须登录!