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

跟我一起穿越时空吧！

在最新的连载中，我们通过充分利用5个特征，成功提取了所有销售人员的姓名。如果您对Excel中的重复值和非重复值不熟悉，可以点击头像或跳转链接转过来阅读。

时光旅行Excel之路连载7更新：SUMPRODUCT函数挑战Excel的条件计数

通过时间序列更新Excel的途径8：SUMPRODUCT函数击败Excel条件求和

通过时间序列更新Excel的路径9：MATCH函数和计算唯一值SUMPRODUCT

时光旅行·Excel升级之路连载10：索引函数和偏移函数提取唯一值

提取名字的目的是什么？没错，就是为了排名做准备。

我们需要根据总销售额对每个人进行排名。根据范围，会出现两个问题：

问题1：在所有人中，按总销售额对每个人进行排名。

问题2：根据每个人在各自领域的总销售额进行排名。

这就是今天的主题。本文将尝试分类聚合、数据透视、RANK函数、SUMPRODUCT函数等方法来完成分类问题。

让我们一起踏上Excel系列11更新之路：汇总、数据透视、排名SUMPRODUCT

1尝试通过分类和聚合的方法进行分类

我们来看看这两个问题：

问题1：在所有人中，按总销售额对每个人进行排名。

问题2：根据每个人在各自领域的总销售额进行排名。

问题1很简单。首先对卖家列表进行排序，然后按照“卖家”进行排序汇总，得到每个人的总销售额。我们在这里不做详细介绍。

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

这个时候还可以分类聚合吗？

我们先来看看我们原来的经典表。我们会发现不同门派的不同卖家是穿插的，而且有反复出现的现象，因为现在不仅仅是对每个人的总销售额进行排序，而是现在必须要进行排序。每个人的总销售额。想要按照每个人的总销售额来划分门派，绝对需要先将门派分开，然后再将人员分开，然后将同一个人的销售额合并起来计算总销售额。。

分类和概括，第一步是排序。现在这里需要的是多条件排序！

注意：如果您只是单击排序按钮，则只能按单个条件排序。例如，无论我们对七进行升序还是降序排序，我们都会发现“琼英”仍然穿插在销售额中。个人的，无法进行分类和综合。

因此，要实现多条件排序，就需要使用排序对话框。

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

2我们在主关键词中添加“宗派”，在次要关键词中添加“卖家”。确认后，宗派和人员将被整齐地组织起来。此时不会出现交错现象。

3、然后，我们可以点击“排名与汇总”按钮来尝试一下。

4

分类字段表示分类标准，我们选择“学校”；

所以默认的汇总方式是“sum”，即对同一段数据进行求和；

数据为“选定汇总项目”中的“销售额”。

单击“确定”执行。

5、Excel会分三个级别显示排名和汇总结果现在我们可以看到每个门派的汇总销售额，但无法具体到每个人。如果我们想要具体针对每个人，我们需要将他们重新分类为汇总操作。

6、再次点击分类汇总按钮，在分类栏中选择“卖家”；

汇总方式为“Sum”；选择“销售”汇总项；

然后取消选中“替换当前期间小计”。

确保取消选中此处。清除是指Excel将在当前小计的基础上再进行一次小计。

7、我们可以发现，这次Excel会显示分为4个级别的汇总结果，不仅显示了每个门派的总销售额数据，还显示了每个门派内不同人员的总销售额。

转到第3级，数据更加直观：

8、不要忘记我们需要的是定位。接下来我们应该做什么？

你可以使用CtrlG键找到“可见单元格”并将其复制到一个新表格中，然后按每个人的总销售额对它们进行排序，但这只是问题有点多。

2使用数据透视法对尝试进行排名

由于分类聚合方法最终还是需要手动分类，所以我们尝试一下其他方法，看看是否可以直接通过数据透视来实现分类。

又是前两个问题：

问题1：在所有人中，按总销售额对每个人进行排名。

问题2：根据每个人在各自领域的总销售额进行排名。

问题一的操作这里不再详细描述。我们直接解决问题二。

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

2当我们创建数据透视表时，将“School”和“Salesperson”拖放到行标签上，并将“Sales”拖放到值标签上。现在，Excel会默认按团队和个人计算总销售额，得到的结果与上面的多重分类汇总结果相同。

（如果您只需将“Salesman”拖到行标签，将“Sales”拖到值标签，然后继续执行后续步骤，您就可以解决问题一并获得所有问题的透视排名。）

3继续，在“Value”选项卡中，我们可以更改设置来获得排名。

单击求和项值字段设置，打开新对话框并切换到“值显示模式”。

调整“数值显示方式”按降序排列，基础字段选择“卖家”；

这样销量最高的就变成1，销量第二的就变成2，排名会自动生成。

4我们可以看一下效果：

以翠湖别墅为例，销量最高的琼盈（36900），现在显示为1；

销量第二高的段莫言（17200）现在显示为2；

销量最低（1200）的朱银峰现在显示为3；

排名完成。

3尝试对SUMPRODUCT函数进行分类

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

在这一部分中，我们交替解决前两个问题。

问题1：在所有人中，按总销售额对每个人进行排名。

在系列10中，我们提取了所有独特销售人员的姓名，并根据这些姓名构建了一个表：

第一列是名称，第二列是销量，

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

乘积(($D$2:$D$18K2)*1,$I$2:$I$18)

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

接下来，按销量排名。

1通过rank、rankeq、rankavg函数可以直接得到结果。

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

排名EQ(L2,$L$2:$L$9)

三个排名函数rank、rankeq和rankavg之间有什么区别？我们可以看一下下表：

排名功能是初始排名功能，现已被淘汰。我们通常使用rankingeq（相同值取最高排名）或rankingavg（相同值取平均排名）。

2使用SUMPRODUCT函数进行分类

SUMPRODUCT神级函数也可以用来分类，方法也很聪明。

在单元格N2中输入公式：

求和((L2

按回车键计算后，我们可以看到结果为2，然后拖动填充即可完成排名。

这是什么原理？如果你看过连载1，我想你就能理解。

（L2

如果这个人的销售额比别人低，你就得到1TRUE，这也表明有1个人在他前面；

有n个TRUE，也就是说他前面有n个人，所以他的排名是n1。

问题2：根据每个人在各自领域的总销售额进行排名。

1、此时需要重新制定之前的表格，增加武术一栏。

2、使用VLOOKUP结合逆向数组查询，找出每个人的宗派。

在K2中输入公式，回车计算并拖动填充：

VLOOKUP(L2,IF({1,0},$D$2:$D$18,$C$2:$C$18),2,FALSE)

当然，除了使用if构建之外，还可以使用choose构建函数，例如：

VLOOKUP(L2,SELECT({1,2},$D$2:$D$18,$C$2:$C$18),2,FALSE)

3然后我们在单元格O2中输入公式：

乘积(($K$2:$K$9K2)*(M2

按回车键计算后，向下拖动填充，得到的就是门派内每个人的销量排名。

好了，连载11的内容就到此为止了，至此，你体会到SUMPRODUCT函数的强大了吗？正如唐代大诗人王维《老将行》中的诗句：“行三千里，一剑当百万师”。基于数组的SUMPRODUCT函数非常好，不愧为神级函数。

如果有什么不懂的可以先看一下前面的系列，打好基础。

点击头像即可跳转

更多有趣内容，请关注、点赞，感谢您的支持。

（原创连载，保留个人意见，禁止除我以外的任何未经授权的账号将文章复制到其他平台发表）