[第130期]又双叒叕——中国式排名[已结]

象山海鲜

中国式排名

说明：
     1.数据源分数，0~100，可能有1位小数，乱序。
     2.分数从高到低排名。分别为：1、2….名。
     3.名次对应的个数。第1名1个，第2名2个，第3名3个，其余都为1个。
     4.按顺序补齐数量、同分同排名。
           名次合计数量不够对应个数，下一档分数来补齐当前名次个数。
           直到当前名次合计数量与排序分数的个数 的 和 大于等于 当前名次的个数，
            以上都记为当前名次。然后再继续排下一名次。

示例：
    验证1：第1名：100，1个 合计1个，满足第1名1个要求。
       第2名：98，1个 合计1个，继续97.5,1个，合计2个 满足第2名2个要求。
       第3名：96 ，1个 合计1个，继续95.5,1个 合计2个，
                     继续90，1个 合计3个，满足3个要求。
       第4名：98，1个 合计1个，满足1个要求。
       依次到排名结束。乱序参考验证10。
    验证2：第1名：100，1个 合计1个，满足第1名1个要求。
       第2名：98，3个 合计3个 满足第2名2个要求。同分的3个98都为第2名。
       第3名：95.5 ，1个 合计1个，继续90,2个 合计3个，满足3个要求。
       第4名：76，1个 合计1个，满足1个要求。
        …….
       第8名 54，2个 合计2个 满足1个要求。同分的2个 54都为第8名。
       依次到排名结束。乱序参考验证11。

要求：
     纯函数题。版本不限。谢绝VBA、PQ、自定义名称、辅助列、迭代…。
     在公式区 C2:C17 写公式，一个公式下拉或区域完成。
     公式除A:C列不得引用，ROW 、COLUMN函数引用除外。

评分：
     一人一楼，占楼扣财富。如帖子不能编辑 可补充 添加。
    公式长度（含等号）不超过 120字符，评技术分2分。
    公式长度（含等号）不超过 150字符，评技术分1分。
     其余正确答案给予财富奖励。

象山海鲜

预设公式：

1. =-LOOKUP(,(TEXT(A$3:A$17-1,"[<4];1")/FREQUENCY(N(OFFSET(C$2,A$3:A$17*(B$3:B$17>B3),))+1,A$3:A$17)>1)-A$3:A$17)

复制代码

1.TEXT(A$3:A$17-1,"[<4];1") =》  [<4];1   高版本  亦可 [>3]1。
2.FREQUENCY(....,A$3:A$17) =》 A$3:A$17 FREQUENCY函数 忽略空值和文本 亦可 A:A 。有取巧嫌疑，但不影响运算，所以是允许的。
简化后：

1. =-LOOKUP(,(TEXT(A$3:A17-1,"[>3]1")/FREQUENCY(N(OFFSET(C$2,A$3:A17*(B$3:B17>B3),))+1,A:A)>1)-A$3:A17)

复制代码

使用计数方式
简化为

1. =COUNT(0/(TEXT(A$3:A$17,"[>4]2")-2<FREQUENCY(N(OFFSET(C$2,A$3:A17*(B$3:B17>B3),))+1,A:A)))

复制代码

看见星光

365：

1. =COUNT(0/(FREQUENCY(FILTER(C$3:C$17,B$3:B$17>B3,0),A$3:A$17-1)>TEXT(A$3:A$17,"[>4]2")-2))

复制代码

附件顺便提供VBA和Power Query解法▼

法法条

支持海鲜大神，题目已经看懂了，先认真思考，感谢海鲜大神，您出题辛苦了。


补充内容 (2020-9-29 15:24):
C列也可以引用，说明可以引用之前的结果，也就是说海版的预设公式不是数组公式而是往下拉的，这么说来生成内存数组判断2,3的路子就不是最佳的方法，再认真想想怎么下拉公式。。。。。

补充内容 (2020-10-4 11:08):
尊敬的海鲜版主，我是真不会了，我放弃了，我根本无法写出符合字符数要求的公式。本来不会就不要占楼，本人一时心急就回帖了，还望您谅解，谢谢。

补充内容 (2020-10-19 23:13):
82字符数组，无比的艰难呀
=MATCH(1=1,TEXT(A$3:A17,"[>3]1")-COUNTIFS(B$3:B17,">"&B3,C$3:C17,">="&A$3:A17)>0,)

Michael.Deng

9.21这个题目很像最近一个坛友的求助
9.25感谢老师们送的鲜花，本来不打算答题的，但是老师们的鲜花鼓励了我（捂脸哭），结果好歹是搞出来了，算是对得起各位老师了，开了个火车，做了N次嵌套，将就着看吧，我把过程附件上传一下，累计花了3、4个小时吧，做1、2、3排名花了约1个半小时，做后面的排名花了差不多2个小时，可能思路不太对吧。最近有点忙，有空再来缩减一下吧，最后再次感谢各位老师。
=IF(RANK(B3,B$3:B$17,0)=1,1,IF((MAX(B$3:B$17)>B3)*(B3>=LARGE(IF(B$3:B$17<MAX(B$3:B$17),B$3:B$17,0),2)),2,0)+IF((LARGE(IF(B$3:B$17<MAX(B$3:B$17),B$3:B$17,0),2)>B3)*(B3>=LARGE(IF(B$3:B$17<LARGE(IF(B$3:B$17<MAX(B$3:B$17),B$3:B$17,0),2),B$3:B$17,0),3)),3,0))+IF((B3<LARGE(IF(B$3:B$17<LARGE(IF(B$3:B$17<MAX(B$3:B$17),B$3:B$17,0),2),B$3:B$17,0),3)),SUMPRODUCT((B$3:B$17>B3)/COUNTIF(B$3:B$17,B$3:B$17))-SUMPRODUCT((B$3:B$17>LARGE(IF(B$3:B$17<LARGE(IF(B$3:B$17<MAX(B$3:B$17),B$3:B$17,0),2),B$3:B$17,0),3))/COUNTIF(B$3:B$17,B$3:B$17))+3,0)

丢丢表格

（65字）

1. =MATCH(1=1,COUNTIFS(B:B,">"&B3,C:C,A$3:A17)<A$3:A17^(A$3:A17<4),)

复制代码

思路也很简单：看看比当前分值大的排位有没有空 “交椅”，甭客气入坐！


上面的公式有2秒的卡顿，虽然下面公式多几个字，可它计算飞快。
=MATCH(1=1,COUNTIFS(B$1:B17,">"&B3,C$1:C17,A$3:A17)<A$3:A17^(A$3:A17<4),)

江苏永能朱兴华

常规路线：
=COUNT(0/IF(FREQUENCY(MIN(--TEXT(COUNTIFS(B:B,">="&B$3:B17),"[<"&COUNTIF(B:B,MAX(B:B))+2&"]15;0"))*{1,1,-9}+A$6:A$17^{0,1,0}-1,COUNTIFS(B:B,">="&B$3:B$17)),B$3:B17>B3))+1
海版走非常规路线：
=MATCH(1=1,FREQUENCY(IFERROR(VLOOKUP(N(IF(B$3:B$17>B3,B$3:B$17)),B:C,2,),16),A$3:A$17)<A$3:A$17^(A$3:A$17<4),)

闻西

自个试了一下公式是正确的，不过公式有点长。
=IF(SUM(--(3=IF(SUM(--(3=IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))))<3,IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))-IF(IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))>3,1,0),IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))))<3,IF(SUM(--(3=IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))))<3,IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))-IF(IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))>3,1,0),IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))-IF(IF(SUM(--(3=IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))))<3,IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))-IF(IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))>3,1,0),IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))>3,1,0),IF(SUM(--(3=IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))))<3,IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))-IF(IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))>3,1,0),IF(SUM(--(2=MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)))<2,MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)-IF(MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0)<3,0,1),MMULT((B3:B17<=TRANSPOSE(B3:B17))/TRANSPOSE(COUNTIF(B3:B17,B3:B17)),ROW(1:15)^0))))

cutecpu

=MAXIFS(C:C,B:B,">"&B3)+(COUNTIFS(B:B,">"&B3,C:C,MAXIFS(C:C,B:B,">"&B3))>=1*TEXT(MAXIFS(C:C,B:B,">"&B3),"[>3]1"))

祝順心，南無阿彌陀佛！

风中的我脸

函数难产，刚好在练VBA，先上一个

1. Sub test()
   
2.     Dim dic As Object, arr, brr, i&, p&, ou&, rank&, count&
   
3.     Set dic = CreateObject("scripting.dictionary")
   
4.     Sheet1.Activate
   
5.     arr = Range([b3], Cells(Rows.count, "b").End(xlUp))
   
6.     For i = 1 To UBound(arr)
   
7.         dic(arr(i, 1)) = ""
   
8.     Next
   
9.     ReDim brr(1 To UBound(arr))
   
10.     For ou = 1 To UBound(arr)
    
11.         rank = 1
    
12.         For i = 0 To dic.count - 1
    
13.             If arr(ou, 1) < dic.keys()(i) Then rank = rank + 1
    
14.         Next
    
15.         brr(ou) = rank
    
16.     Next
    
17.     For ou = 2 To 3
    
18.         For p = 1 To UBound(brr)
    
19.             count = 0
    
20.             For i = 1 To UBound(brr)
    
21.                 If brr(i) = ou Then count = count + 1
    
22.             Next
    
23.             If count < ou Then
    
24.                 For i = 1 To UBound(brr)
    
25.                     If brr(i) > ou Then brr(i) = brr(i) - 1
    
26.                 Next
    
27.             Else: Exit For
    
28.             End If
    
29.         Next
    
30.     Next
    
31.     Range("c3").Resize(UBound(brr), 1) = WorksheetFunction.Transpose(brr)
    
32.     Set dic = Nothing
    
33. End Sub

复制代码

硬生函数，373字符！！

1. <span style='display: inline !important; float: none; background-color: rgb(247, 247, 247); color: rgb(68, 68, 68); font-family: Tahoma,"Microsoft Yahei","Simsun"; font-size: 14px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; overflow-wrap: break-word; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;'>=SUM(SUM(--(UNIQUE($B$3:$B$17)>=B3)),-IF(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),2))>2,TEXT(SUM(--(UNIQUE($B$3:$B$17)>=B3)),IF({1,0},"[>3]1;!0","[>4]"&"!"&N(SUM(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),{3,4})))<3)&";!0")),TEXT(SUM(--(UNIQUE($B$3:$B$17)>=B3)),CHOOSE({1,2,3},"[>2]1;!0","[>4]1;!0","[>5]"&"!"&N(SUM(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),{4,5})))<3)&";!0"))))</span><b></b><i></i><u></u><strike></strike>
   

复制代码

补充内容 (2020-10-5 12:55):
=SUM(SUM(--(UNIQUE(B$3:B$17)>=B3)),-MAX(1*TEXT(SUM(--(UNIQUE(B$3:B$17)>=B3))-{2,3},IF({1,0},"!"&N(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),2))<2)&";!0;!0","[>"&1+(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),2))<2)&"]"&(SUM(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),{3,4}+(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),2))<2))))<3)+2-(COUNTIF(B$3:B$17,LARGE(UNIQUE(B$3:B$17),2))>1)&";0"))))

补充内容 (2020-10-5 14:01):
自己yy了LET函数才207字符，及格线都没到，有这个函数的朋友帮我验证下。放弃了！！等20号
=LET(x,COUNTIF(B:B,LARGE(UNIQUE(B:B),2))<2,y,SUM(--(UNIQUE(B:B)>=B3)),SUM(y,-MAX(1*TEXT(y-{2,3},IF({1,0},"!"&N(x)&";!0;!0","[>"&1+(x)&"]"&(SUM(COUNTIF(B:B,LARGE(UNIQUE(B:B),{3,4}+(x))))<3)+2-NOT(x)&";0")))))