如何用多种方法实现RANK排序效果？

楼主 lrlxxqxa	Q：如何用多种方法实现RANK排序效果？ A：按照数据源中是否有重复分开来看这个问题；先来看当数据不重复时，我们怎么做；方法1数组公式 =SUM(N($B$2:$B$16>=B2)) 方法2 =SUMPRODUCT(N($B$2:$B$16>=B2)) 方法3数组公式 =COUNT(IF($B$2:$B$16>=B2,1)) 方法4 =COUNTIF($B$2:$B$16,">="&B2) 方法5数组公式 =SUM(N(FREQUENCY(IF($B$2:$B$16>=B2,$B$2:$B$16),$B$2:$B$16))) 方法6数组公式 =MMULT(N($B$2:$B$16<=TRANSPOSE($B$2:$B$16)),ROW($B$2:$B$16)^0) 方法7数组公式 =ROUNDDOWN(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),)+1 方法8 =ROUNDDOWN(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),)+1-(MIN($B$2:$B$16)=B2) 方法9 =ROUNDUP(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),)+(MAX($B$2:$B$16)=B2) 方法10 =INT(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)))+1-(MIN($B$2:$B$16)=B2) 方法11 =TRUNC(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)))+1-(MIN($B$2:$B$16)=B2) 方法12 =CEILING(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),1)+(MAX($B$2:$B$16)=B2) 方法13 =FLOOR(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),1)+1-(MIN($B$2:$B$16)=B2) 再来看当数据中有重复时，下述13个公式返回结果会如何变化，以及如何调整才能和RANK排序结果一致：当数据源中存在重复值时，上述公式需要进行如下调整（顺序一一对应）：* 2数组 =SUM(N($B$2:$B$16>B2))+1 3 =SUMPRODUCT(N($B$2:$B$16>B2))+1 4数组 =COUNT(IF($B$2:$B$16>B2,1))+1 5 =COUNTIF($B$2:$B$16,">"&B2)+1 6数组 =SUM(FREQUENCY(IF($B$2:$B$16>B2,$B$2:$B$16),$B$2:$B$16))+1 7区域数组 =MMULT(N($B$2:$B$16<TRANSPOSE($B$2:$B$16)),ROW($B$2:$B$16)^0)+1 8 =ROUNDDOWN(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),)+2-COUNTIF($B$2:$B$16,B2)-(MIN($B$2:$B$16)=B2) 9 =ROUNDUP(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),)-COUNTIF($B$2:$B$16,B2)+1+(MAX($B$2:$B$16)=B2) 10 =INT(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)))+2-COUNTIF($B$2:$B$16,B2)-(MIN($B$2:$B$16)=B2) 11 =TRUNC(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),)+2-COUNTIF($B$2:$B$16,B2)-(MIN($B$2:$B$16)=B2) 12 =CEILING(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),1)-COUNTIF($B$2:$B$16,B2)+1+(MAX($B$2:$B$16)=B2) 13 =FLOOR(COUNTA($B$2:$B$16)(1-PERCENTRANK($B$2:$B$16,B2)),1)+2-COUNTIF($B$2:$B$16,B2)-(MIN($B$2:$B$16)=B2) 请自行对比。排序多种方法.rar 排序解析.rar
2楼 tonysun	高手呀，谢谢分享
3楼 zhanghi	先赞一个正好学习排序
4楼 zhanghi	很认真的学习了lrlxxqxa 版主的方法，由于能力有限，后几种方法还需要继续消化，特别是MMULT函数一直没搞懂是怎样计算的，希望版主能为我解惑。另外：在排名并列的情况下，前6各方法都会和rank排出来的有一点区别，rank在并列的情况下的靠前排的，如果将前6种方法的“=”去掉，结果+1就能完全与rank排出来的相同，另外方法5貌视可以不用N。以上纯属个人观点，望版主别介意。
5楼 lrlxxqxa	关于MMULT的计算过程，简单说一下：在上图中的D2：D16输入区域数组公式 =MMULT(N($B$2:$B$16<TRANSPOSE($B$2:$B$16)),ROW($B$2:$B$16)^0)+1 下面分步来看：可见在MMULT的运算下（蓝色区域）效果是等同于SUM行求和功能（黄色区域的），详见附件。 MMULT.rar
6楼 lrlxxqxa	看得很认真，你说的FREQUENCY中的N函数确实可以省略；谢谢补充；关于rank排名和后续的其他方法，关键就在于等号的运用以及误差值的调整；最后的几种方法都是舍入取整函数的变化，原理一致的。
7楼 zhanghi	谢谢你的耐心解说，可MMULT的基本用法我都还没搞懂，帮助文件也没有找到有用的信息，下图是帮助文件的示例，麻烦再说说是怎样计算的。谢谢！
8楼 lrlxxqxa	[<font color=red>提问</font>] MMULT函数计算步骤是怎样的？ http://www.exceltip.net/thread-13330-1-1.html [函数与公式] 结合实例来帮助理解矩阵乘法 http://www.exceltip.net/thread-13333-1-1.html