|
小伙伴们好，昨天分享了如何用函数实现升序排序的效果。今天再来说下降序排序的方法。数据源和昨天的一样，如下图所示。以分数为关键字对左表降序排序，结果如右表所示。分数相同的按先后顺序排序。 思路和昨天的基本差不多，都是把分数和行号结合在一起。只不过在一些细节上有所差别。在E2单元格输入公式=INDEX(A:A,100-MOD(LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1)),100))，按ctrl+shift+enter三键结束，向右向下填充，完成。 =$B$2:$B$13/1%这部分是将分数乘以100，结果如下图C列所示。 =$B$2:$B$13/1%-ROW($2:$13)这部分就是用分数乘以100减去对应的行号，结果如下图C列所示。这里为什么要减去行号呢？首先对于分数不相同的，经过这种运算后的结果不会改变原来分数的大小。比如第2行和第3行的80和85，80小于85，运算后的7998还是小于8497。其次，对于分数相同的，会按先后顺序取值。比如第3行和第10行都是85，运算后的结果分别是8497和8490，而且8497大于8490。因为现在是降序排序，所以是从大到小的顺序取数，这样就保证了先取第3行的8497，然后取第10行的8490。 =LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1))这部分是用large取出第1个最大值9496，公式向下填充就取第2个最大值8497，以此类推。结果如C列所示。 =MOD(LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1)),100)这部分用mod对上一步的结果除以100求余数。但是得到的余数并不是我们要的行号。比如96是9496的后两位，9496是哪里来的呢？是95*100-4得来的，也就是由第4行的95分通过运算得来的。所以4才是我们要的行号。 =100-MOD(LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1)),100)这部分就是用100减去mod的结果，这样才得到了我们要的行号。比如上一步mod的结果是96，100-96=4得到了对应的行号。 =INDEX(A:A,100-MOD(LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1)),100))这部分就是用index返回单元格内容。这样就得到了降序排序后的效果。 其实，这个公式还可以写为=INDEX(A:A,-MOD(LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1)),-100))，按ctrl+shift+enter三键结束。这里mod的第2参数用的-100，不知道大家还记不记得mod参数中负数的用法。 突然又想到还可以用=INDEX(A:A,MOD(-LARGE($B$2:$B$13/1%-ROW($2:$13),ROW(A1)),100))这个公式，让mod的第1参数变为负数，还能少1个字符。 不用按三键的公式为=INDEX(A:A,-MOD(LARGE(MMULT($B$2:$B$13/1%-ROW($2:$13),1),ROW(A1)),-100))。 如果你有其他的方法，欢迎在留言区写出你的答案，让我们一起学习。 文件链接： https://pan.baidu.com/s/1SMWR4vsI6JzoW2A2i_V-9g 提取码：sb84 |
|
|
