如何在excel中使用条件排名?
首先,帮我做一下,打开 Excel 工作簿并尝试输入 RANKIF。你会想知道为什么Excel中没有条件排名的功能。
是的,没有人。
现在这样想,您是否曾经遇到过需要使用某些特定标准对值进行排名的情况?如果是的话,你如何解决这个问题,因为你知道Excel中没有RANKIF函数?
没有把握?
让我告诉你一件事,每当你想根据特定标准或类别排名创建条件排名时,最好的方法就是使用 SUMPRODUCT。是的,你没理解错,就是 SUMProduct。
我已经爱上这个函数好几年了,今天在这篇文章中我将向您展示一种使用 SUMPRODUCT 对带有条件的值进行排序的简单方法。这项技术可以将您从 Excel 初学者转变为高级 Excel 用户。
让我们开始吧。
在此示例中,我们有一个学生列表,其中包含不同科目的分数。您可以在此处下载此示例文件以继续操作。
在这里,我们的目标是对每个科目的所有学生进行排名。这意味着,根据学生的成绩,对每个科目(如金融、运营等)从第一名到最后一名学生进行排名
将其用作 RANKIF 的条件公式
- 首先,在表格末尾添加一个新列并将其命名为“Subject Wise Rank”。
- 在单元格 D4 中,输入此公式 =SUMPRODUCT((–(C2=$C$2:$C$121)),(–(B2<$B$2:$B$121)))+1 并按 Enter。
- 之后,将此公式应用到列的末尾,直到最后一个单元格。
恭喜,您已经为学生添加了学科排名,您认为您花了几秒钟吗?
是不是既简单又有效呢?但重要的是要理解这个公式是如何运作的。相信我,当您得知您使用此功能在这里创造了一些魔力时,您会感到惊讶。
这个条件 RANKIF 公式如何工作?
为了理解这一点,我们需要将这个公式分为三个部分。请记住,SUMPRODUCT 是一个函数,即使您没有将公式应用为数组,它也可以采用数组。
第 1 部分:比较名称
在第一部分中,您使用(–(C2=$C$2:$C$121))
将主题名称与整个范围进行比较。它将返回一个数组,其中所有这些值都为 true 并对应于主题名称“Finance”。
要进行检查,只需在单元格 D4 中编辑公式,仅选择公式的第一部分,然后按 F9。它将显示数组中的所有值。
这里,所有与单元格D4的主题名称匹配的值都是TRUE,其余的都是FALSE。所以重点是它在主题名称匹配的整个数组中返回 TRUE。
最后需要使用双减号将 TRUE 和 FALSE 转换为 1 和 0。
公式这部分的结果:主语匹配时为 1,主语不匹配时为 0。
第 2 部分:检查值是否大于
在第二部分中,您使用(--(B2<$B$2:$B$121))
来检查其他学生的分数是否高于 Tameka 的分数。它返回一个数组,其中所有值都为 TRUE,其中标记大于 Tameka。
要进行检查,只需在单元格 D4 中编辑公式,仅选择公式的第二部分,然后按 F9。它将显示数组中的所有值。
这里所有大于“24”的值都是TRUE,其他的都是FALSE。所以关键是它在整个表中返回一个 TRUE,其中分数大于“24”。
最后你需要使用双减号将 TRUE 和 FALSE 转换为 1 和 0。现在它看起来像这样。
公式这部分的结果:分数较大时为 1,分数等于或小于时为 0。
第 3 部分:两个数组相乘
现在深呼吸并放松。放慢你的思维,这样思考。此时我们有两个不同的表。
- 在第一个表中,主题匹配的所有值都为 1,如果不匹配,则为 0。
- 在第二个表中,学生得分较高的所有值都有一个,如果等于或较低,则为零。
现在,当 SUMPRODUCT 将这两个表相乘时,仅对于学科匹配且分数高于 Tameka 的学生,您将得到 1。
看看这个,金融科成绩比Tameka好的同学还有9个。
第 4 部分:添加 + 1
如果您好奇为什么需要在最终公式中加 1,那么原因是:此时,您知道共有 9 名学生的成绩比 Tameka 的成绩高。
因此,如果有 9 名学生,Tameka 应该排在第 10 位。这就是为什么需要在公式末尾添加 1。
获取 Excel 文件
结论
如果你问我,我相信 SUMPRODUCT 是 Excel 库中最强大的函数之一,而且我们上面使用的方法简单有效。
使用 SUMPRODUCT,您无需编写长嵌套条件公式。你只需要这个魔术来添加条件排名。我希望这个技巧对您的工作有所帮助,现在告诉我一件事。
您知道使用 RANKIF 的另一种方法吗?
请在评论部分与我分享您的观点,我很乐意听取您的意见,并且请不要忘记与您的朋友分享此技巧。