excel数据技巧：6条最实用的透视表偏方

编按：函数学得少，所以就把劲往数据透视表上使。数据透视表也没辜负人，总有一些小东西可以解决统计上的大问题。这里的6条偏方就是这样的。

所谓偏方，就是指平时少见，但是对于特定情况有特效的方子。我们今天跟大家分享数据透视表六个“偏方”。

偏方一：空值处理

我们在对一组数据进行数据透视时经常会遇到值区域中某个字段对应数据为空白的情况。以往很多伙伴都是手动修改，其实可以通过数据透视表自定义空白显示为0。（注：只针对值区域中的空白！）

举例：

第一季度中的屏幕300*220项目购买数量为空白，现在需要将数据进行透视汇总处理。

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

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

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

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

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

偏方二：排名

日常工作中经常需要将完成数据透视后的数据进行排名，很多伙伴都是通过rank函数进行排名。其实数据透视表自带排名功能，根本无需排序、函数。

还是以采购数据为例，现在我们完成了数据透视。

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

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

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

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

偏方三：批量创建工作表

批量创建是日常经常碰到的工作，比如创建分公司、月份、季度等工作表。如果数量少，我们可以通过手工逐一创建，如果数量很多该怎么办呢？其实可以通过数据透视表批量创建工作表。

举例：现在我们需要创建4个季度的工作表。

首先在表中输入表头季度，以及四个季度名称。

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

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

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

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

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

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

是不是很简单？

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

偏方四：多表求和

将同一工作簿中的多个同格式工作表汇总求和，也可以用数据透视表完成。具体请看教程《别瞎忙乎了，多表求和用这个方法就是分分钟的事……》。

偏方五：按新增字段分组统计

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

例举两例。

例1：按日期分组统计

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

（1）选中所有数据，插入数据透视表。

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

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

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

（4）单击“季度”，然后确定。

（5）可以看到数据透视表字段中增加了“季度”字段。在左侧的透视表中，单击

符号把数据折叠，就实现了按季度统计。

例2：分数分阶段统计

下表是某班的数学成绩，只有姓名和成绩两个字段。现在我们需要统计

（1）一样的，首先建立透视表。

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

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

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

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

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

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

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

偏方六：非重复计数

从原始数据中统计机构数量、产品种数、经销商数量、供货商数量等等，属于典型的非重复计数。同样可以利用透视表完成。具体的完成方式见《围观数据透视表新功能：小东西，大作用》

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

＊＊＊＊部落窝教育－excel透视表技巧＊＊＊＊

原创：龚春光、小雅/部落窝教育（未经同意，请勿转载）