穿越时间Excel升级之路连载11：分类汇总 数据透视 SUMPRODUCT排名

跟我一起，穿越时间！

在上一期的连载中，我们通过5个函数的综合运用，成功地提取了所有的销售人员的姓名，如果你对Excel中的重复值、不重复值还不熟悉，可以点击头像或链接跳转阅读。

穿越时间Excel升级之路连载8：SUMPRODUCT函数决胜Excel条件求和

穿越时间Excel升级之路连载9:MATCH函数及SUMPRODUCT不重复值计数

穿越时间Excel升级之路连载10：Index函数Offset函数提取不重复值

提取出姓名是用来干什么呢？没错，是为排名来做准备。

我们需要以各人的销售总额为依据进行排名，根据范围的不同，产生了两个问题：

问题一：在所有人中，根据每个人的销售总额进行排名。

问题二：在各自的门派中，根据每个人的销售总额进行排名。

这就是今天的主题，本文会尝试分类汇总、数据透视、RANK函数、SUMPRODUCT函数等方法来完成排名的问题。

让我们走到Excel升级之路连载11：分类汇总、数据透视、SUMPRODUCT排名

我们来看这两个问题：

问题一：在所有人中，根据每个人的销售总额进行排名。

问题二：在各自的门派中，根据每个人的销售总额进行排名。

问题一是非常简单的，首先对销售人员列表进行排序，然后根据“销售人员”进行分类汇总，得出每个人的销售总额排序即可，在此我们就不多说了。

我们直接来看问题二：在各自门派中根据每个人的销售总额进行排名排序。

这时分类汇总还可以吗？

先来看一下我们经典的原始表格，我们会发现不同门派、不同的销售人员是穿插的，而且都有重复出现的现象，因为现在不是仅仅对每个人都销售总额排序，而是现在需要在每个门派内根据每个人的销售总额进行排序，肯定需要先分出门派，再分出人员，再把同一个人的销售情况合并，把销售总额算出来。

要想分类汇总，第一步需要排序，现在这里需要的还是多条件排序！

注意：如果仅仅通过点击排序按钮，那只能实现单条件排序，例如无论我们将门派进行了升序排列还是降序排列，我们会发现销售人员中“琼英”仍然是穿插的，这样无法进行分类汇总。

因此，为了实现多条件排序，我们需要使用排序对话框。

1、排序对话框位于“数据”选项卡中。

2、我们在主要关键字中添加“门派”，在次要关键字中添加“销售人员”，确定之后，门派、人员都会被排列整齐。这时就不会有穿插的现象了。

3、然后，我们可以点击“分类汇总”按钮进行尝试。

4、

分类字段的意思就是分类标准，我们选择“门派”；

然后汇总方式默认是“求和”，意思是同一门派的数据会被求和；

而数据，也就是“选定汇总项”中的“销售额”。

点击确定执行。

5、Excel会以3层来显示分类汇总结果，我们现在已经可以看到每个门派的汇总销售额了，但是还不能具体到每个人，如果要具体到每个人，我们还需要再进行一次分类汇总操作。

6、再次点击分类汇总按钮，分类字段选择“销售人员”；

汇总方式“求和”；选定汇总项“销售额”；

然后取消勾选“替换当期分类汇总”。

这里一定要取消勾选，取消勾选表示Excel会在当前分类汇总的前提下再执行一次分类汇总。

7、我们可以发现，这次Excel会以4层来显示分类汇总结果，不仅每个门派的销售数据总额得到了显示，每个门派内不同人的销售总额也得到了显示。

切换到第3层，数据更加直观：

8、不要忘了我们需要的是排名，后面怎么做呢？

可以通过Ctrl+G键，定位并复制出“可见单元格”到新的表格里，然后根据每个人都销售总额进行排序排名，只是比较麻烦一点。

既然分类汇总法的最后仍旧需要手动排名，那我们就再尝试一下其他办法，试试数据透视是否可以直接得到排名。

还是最初的两个问题：

问题一：在所有人中，根据每个人的销售总额进行排名。

问题二：在各自的门派中，根据每个人的销售总额进行排名。

问题一的操作这里就不赘述了，我们直接解决问题二。

1、插入数据透视表，位置位于Excel的“插入”选项卡第一项。

2、我们在创建数据透视表时，将“门派”、“销售人员”拖放到行标签，将“销售额”拖放到数值标签，这时Excel默认会分门派分人员求出销售总额，得到的结果就如同上面多重分类汇总的结果。

（如果只将“销售人员”拖放到行标签，将“销售额”拖放到数值标签，再继续后续操作的话，则可以解决问题一，实现所有人范围内的透视排名。）

3、继续，在“数值”标签里，我们可以更改设置，从而实现排名。

点击求和项-值字段设置，打开新的对话框，切换到“值显示方式”中，

将“值显示方式”调整为降序排列，基本字段选择“销售人员”；

这样销售额最大的就会成为1，销售额次大的会成为2，排名即可自动生成。

4、我们可以看一下效果：

以翠湖山庄为例，销售额（36900）最高的琼英，现在显示为1；

销售额（17200）次高的段莫言，现在显示为2；

销售额（1200）最低的祝吟风，现在显示为3；

排名完成。

当然，排名的问题通过函数公式也可以实现，而且更快更高效。

这一部分我们依次解决最初的两个问题。

问题一：在所有人中，根据每个人的销售总额进行排名。

在连载10中，我们已经提取了所有不重复的销售人员的姓名，以其为基础，我们构建一个表格：

第一列为姓名，第二列为销售额，

在L2中输入条件求和的公式：

=SUMPRODUCT(($D$2:$D$18=K2)*1,$I$2:$I$18)

按Enter键计算，然后向下拖动填充即可得到每个人的销售总额。

接下来根据销售额进行排名。

1、通过函数rank、rank.eq、rank.avg可以直接得到结果。

在M2单元格中输入公式，然后拖动填充即可：

=RANK.EQ(L2,$L$2:$L$9)

rank、rank.eq、rank.avg三个排名函数有什么不同呢？我们可以看一下下表：

rank函数是早期的排名函数，现在已经淘汰了，我们通常使用rank.eq（对相同的数值取最高排位）或rank.avg（对相同的数值取平均数排位）

2、通过SUMPRODUCT函数进行排名

神级函数SUMPRODUCT也可以进行排名，办法也很巧妙。

在N2单元格中输入公式：

=SUMPRODUCT((L2