vba 如何在Excel中的类别内排名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12623609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to rank within category in Excel?
提问by Siraj Samsudeen
I have a list of customers by region with sales value. I want to create an overall ranking of all customers by sales values as well as the ranking of customers by sales value within the region and use both the ranking to calculate a score. is there a way to do this in Excel?
我有一个按地区列出的具有销售价值的客户列表。我想按销售价值创建所有客户的总体排名以及该地区内按销售价值的客户排名，并使用这两个排名来计算分数。有没有办法在Excel中做到这一点？
Either formulae or VBA code would be helpful for me.
公式或 VBA 代码对我都有帮助。
回答by barry houdini
Clearly for overall rank you can use RANK function, e.g. with Customers in A2:A100, Regions in B2:B100 and Sales value in C2:C100 you can use this formula in D2 for overall customer rank by sales value (highest ranks 1)
显然，对于整体排名，您可以使用 RANK 函数，例如，对于 A2:A100 中的客户、B2:B100 中的区域和 C2:C100 中的销售价值，您可以在 D2 中使用此公式按销售价值计算整体客户排名（最高排名 1）
=RANK(C2,C$2:C$100)
=RANK(C2,C$2:C$100)
for RANK within region you can use this version in E2 copied down
对于区域内的 RANK，您可以在复制的 E2 中使用此版本
=SUMPRODUCT((B$2:B$100=B2)*(C$2:C$100>C2))+1
=SUMPRODUCT((B$2:B$100=B2)*(C$2:C$100>C2))+1
