excel计算班级总分排名_在Excel中计算排名

最新推荐文章于 2024-09-14 15:35:55 发布

culiao2169

最新推荐文章于 2024-09-14 15:35:55 发布

阅读量3.7k

点赞数 1

文章标签： excel 人工智能区块链 python 神经网络

原文链接：https://contexturesblog.com/archives/2010/03/22/calculating-rank-in-excel/

版权

excel计算班级总分排名

To do some research on sorting, I hauled one of the big, dusty Excel books off my shelf, to see if there were any scintillating sorting secrets to uncover. Under Sorting, I saw "rank calculations" so I turned to that page, to see what it said about calculating rank in Excel.

为了对分类进行一些研究，我从书架上拖了一大本尘土飞扬的Excel书籍，以查看是否有任何令人发指的分类秘密。在“排序”下，我看到了“排名计算”，因此转到该页面，查看它在Excel中如何计算排名。

The referenced page explained the RANK function, and included this warning, "Be sure the data set is sorted in either ascending or descending order." Hmmm...I'd never heard that before, and a quick check in Excel's Help proved that statement was wrong – the data set does NOT have to be sorted. Whew! I've been doing it right all along.

参考页解释了RANK功能，并包含以下警告：“确保数据集按升序或降序排序。” 嗯...我以前从未听说过，快速检查一下Excel的“帮助”证明该语句是错误的-不必对数据集进行排序。 ew！我一直都在做。

您是否使用RANK功能？ (Do You Use the RANK Function?)

Maybe you use the RANK function every day, but I rarely need it. If I want to see which products have the highest prices, or which students have the best scores, I'd probably just sort the list.

也许您每天都使用RANK功能，但我很少需要它。如果我想查看哪些产品的价格最高，或者哪些学生的分数最高，我可能只需对列表进行排序即可。

But now that I was in Excel Help anyway, I decided to learn a bit more about the RANK function. Maybe it has exciting features and hidden tricks to discover. (No, I hadn't been drinking, I'm just optimistic.)

但是现在无论如何我都在Excel Help中，所以我决定进一步了解RANK函数。也许它具有令人兴奋的功能和隐藏的窍门。 (不，我没喝酒，我只是乐观。)

RANK功能基础 (RANK Function Basics)

If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order.

如果给RANK函数一个数字和一个数字列表，它将告诉您该数字在列表中的升序或降序。

For example, here's a list of 10 student test scores, in cells B2:B11. To find the rank of the score in cell B2, enter this formula in cell C2:

例如，这是在单元格B2：B11中列出的10个学生考试成绩的列表。要在单元格B2中找到分数的排名，请在单元格C2中输入以下公式：

=RANK(B2,$B$2:$B$11)

= RANK(B2，$ B $ 2：$ B $ 11)

There are 3 arguments for the RANK function:

RANK函数有3个参数：

number: in this example, the number to rank is in cell B2
number ：在此示例中，要排名的数字在单元格B2中
ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. I used an absolute reference, so the referenced range will stay the same when we copy the formula down to the cells below
ref ：我们要将数字与$ B $ 2：$ B $ 11单元格中的数字列表进行比较。我使用了绝对引用，因此当我们将公式复制到下面的单元格时，引用范围将保持不变
order: (optional) Use zero, or leave this argument empty, to find the rank in the list in descending order. For ascending order, type a 1, or any other number except zero. I left this blank, to find the rand in descending order. If you were comparing golf scores, you could type a 1, to rank in ascending order.
order ：(可选)使用零，或将此参数保留为空，以降序查找列表中的排名。对于升序，请键入1或除零以外的任何其他数字。我将此空白留空，以按降序查找randint。如果您要比较高尔夫成绩，则可以输入1以升序排列。

I copied the formula down to cell C11, and the scores were ranked in descending order, as promised.

我将公式复制到单元格C11中，并且分数按承诺的降序排列。

带有领带的RANK功能 (RANK Function With Ties)

What happens to the ranking if some of the scores are tied? I was going to use the Olympics as an example, but it looks like every sport has different rules, so that won't work!

如果某些分数并列，排名会怎样？我将以奥运会为例，但看起来每项运动都有不同的规则，所以这行不通！

In our example, if I change cell B7 to 43, it's tied with cell B3. Both cells are now ranked as 2, and it doesn't affect any of the other rankings. The score of 32 is still 4th, not 3rd, because there are 3 scores ahead of it.

在我们的示例中，如果我将单元格B7更改为43，则它将与单元格B3绑定在一起。这两个单元格现在都排名为2，并且不会影响其他任何排名。 32分的得分仍然是4，而不是3，因为前面还有3分。

So, if you were handing out awards to the top students,

因此，如果您要向优秀学生颁发奖项，

the score of 45 would get the first place blue ribbon
45分将获得第一名
the two students with 43 would each get a second place red ribbon
两个43岁的学生将分别获得第二名
no one would get a third place ribbon (green? white?)
没有人会得到第三名的丝带(绿色？白色？)
everyone else gets one of those orange "Participant" ribbons 😉
所有人都会得到橙色的“参与者”丝带之一😉

使用RANK功能打破关系 (Breaking Ties With the RANK Function)

In some cases, ties aren't allowed, so you have to find a way to break the tie. We could keep track of the number of minutes that each student worked on the test, and use that time to break any ties.

在某些情况下，不允许平局，因此您必须找到打破平局的方法。我们可以跟踪每个学生在测试中花费的分钟数，并利用这段时间打破任何关系。

I added the Test Times in column A, and a TieBreak formula in column D.

我在A栏中添加了“测试时间”，并在D栏中添加了TieBreak公式。

=IF(COUNTIF($B$2:$B$11,B2)>1,RANK(A2,$A$2:$A$11,1)/100,0)

= IF(COUNTIF($ B $ 2：$ B $ 11，B2)> 1，RANK(A2，$ A $ 2：$ A $ 11,1)/ 100,0)

The TieBreak formula checks to see if there's more than one instance of the number in the entire list ($B$2:$B$11).

TieBreak公式检查整个列表中是否存在一个以上的数字实例($ B $ 2：$ B $ 11)。

If there is more than one instance, it ranks the Times in ascending order and divides that by 100, to get a decimal amount. Note: The divisor, 100, could be changed to another number, if you were working with a longer list.
如果实例不止一个，它将按递增顺序对Times进行排名，然后将其除以100，以获得十进制数。注意：如果要使用更长的列表，则除数100可以更改为另一个数字。
If there is only one instance, the result is zero.
如果只有一个实例，则结果为零。

Finally, you can combine the RANK function results with the TieBreak results, to get the final ranking.

最后，您可以将RANK函数结果与TieBreak结果结合起来，以获得最终排名。