合并单元格的排名

Sabrinalijie

那就更新到最新的   365 版本， 或者

1. 
   
2. =LET(S,SCAN(0,A2:A28>0,LAMBDA(X,Y,X+Y)),C,B2:B28,HSTACK(RANK(C,C),MAP(S,C,LAMBDA(X,Y,MATCH(Y,-SORT(-FILTER(C,S=X)),)))))

复制代码

leepoison

1. =LET(a,SCAN(,A2:A28,LAMBDA(x,y,IF(y="",x,y))),b,C2:C28,MAP(b,LAMBDA(w,SUM(1*(FILTER(b,a=INDEX(a,MATCH(w,b,0)))<=w)))))

复制代码

袁勇

低版本：

1. =COUNTIF(OFFSET(B$1,MATCH(LOOKUP("座",A$2:A2),A:A,)-1,,SUMPRODUCT((COUNTA(A$2:A3)=SUBTOTAL(3,OFFSET(A$2,,,ROW($1:$50))))*1)),">"&B2)+1

复制代码

你是那块小饼干

1. =LET(T,SCAN(,A2:A28,LAMBDA(x,y,IF(y="",x,y))),_T,UNIQUE(T),DROP(REDUCE("",_T,LAMBDA(X,Y,LET(_X,FILTER(B2:B28,Y=T,""),VSTACK(X,MATCH(_X,SORT(_X)))))),1))

复制代码

yellowhawk

用比较笨的办法解：D2输入下拉即可，请测试：（我自己测试是成功的）
=RANK(B2,OFFSET($B$1,SMALL((IF($A$2:$A$28="",28,($A$2:$A$28<>"")*ROW($1:$27))),COUNTA(A$2:A2)),,SMALL(IF($A$2:$A$28="",28,($A$2:$A$28<>"")*ROW($1:$27)),COUNTA(A$2:A2)+1)-SMALL((IF($A$2:$A$28="",28,($A$2:$A$28<>"")*ROW($1:$27))),COUNTA(A$2:A2)),))
合适低版本

00JTR00

1. =SUMPRODUCT((LOOKUP(ROW(1:27),ROW(1:27)/(A$2:A$28>0),A$2:A$28)=LOOKUP("座",A$2:A2))*(B$2:B$28>B2))+1

复制代码

mykitami

=LET(a,SCAN(,A2:A28,LAMBDA(x,y,IF(y<>"",y,x))),DROP(REDUCE("",UNIQUE(a),LAMBDA(m,n,LET(b,FILTER(B2:B28,a=n),VSTACK(m,MATCH(b,SORT(b,,-1),))))),1))

roserice

用PQ也可以的

yuppice

=SUM(N((CONCAT(A$2:A2)=SCAN(,A$2:A$28,CONCAT))*B$2:B$28>B2))+1

yuppice

Sabrinalijie 发表于 2024-10-10 13:54