按照规律编加工编号

verna123

麻烦大神们帮忙看看，这个E列是结果列~

jerrike

1. =IF(LEFT(B4,1)="J",$E$1,IF(SUMIF(B:B,B4,D:D)<5,"J00005","J"&TEXT(MATCH(B4,UNIQUE(FILTER($B$4:$B$17,(LEFT($B$4:$B$17,1)<>"J")*(SUMIF($B$4:$B$17,$B$4:$B$17,$D$4:$D$17)>=5))))+5,"00000")))

复制代码

啸海

E4
=IF(ISNUMBER(FIND("J",B4)),"J00004",IF(SUMIF(B:B,B4,D:D)<5,"J00005",IF(COUNTIF(B$4:B4,B4)>1,VLOOKUP(B4,B:E,4,),"J"&TEXT(MAX(IFERROR(--RIGHT(E$3:E3,5),))+IF(ROW(A1)=1,6,1),"00000"))))
数组三键

我的城市你来过

=LET(x,SUMIFS($D:$D,$B:$B,$B$4:$B$17,$B:$B,"<>*J*"),IFS(x=0,0,x<5,1,x>=5,$B$4:$B$17))



下午在来做题

E学子

1. =LET(a,E1,b,B4:B17,c,D4:D17,x,ISERROR(FIND("J",b)),LEFT(a)&TEXT(REPLACE(a,1,1,0)+IFNA(MATCH(b,UNIQUE(FILTER(b,x*(SUMIF(b,b,c)>=5))),),0)+x,REPT(0,LEN(a)-1)))

复制代码

jisijie630

E4:

=IF(LEFT(B4)="J",E$1,IFNA(VLOOKUP(B4,B$1:F3,5,),TEXT(IF(SUMIF(B:B,B4,D:D)<5,MID(E$1,2,5)+1,TEXT(MAX(IFERROR(RIGHT(E$3:E3,5)+1,1)),"[=1]"&MID(E$1,2,5)+2&";0")),"J00000")))

华尔街操盘手

E4

1. =DROP(REDUCE("",B4:B17,LAMBDA(x,y,VSTACK(x,LET(i,SUMIF(B:B,y,D:D),"J"&TEXT(IFS(ISNUMBER(FIND("J",y)),--RIGHT(E1),i<5,RIGHT(E1)+1,i>=5,RIGHT(E1)+1+MATCH(y,UNIQUE(FILTER(B4:B17,SUMIF(B:B,B4:B17,D:D)>=5)),0)),"00000"))))),1)

复制代码

verna123

jerrike 发表于 2024-6-25 11:20

大神，会显示FILTER函数无效`

verna123

啸海 发表于 2024-6-25 11:22
E4
=IF(ISNUMBER(FIND("J",B4)),"J00004",IF(SUMIF(B:B,B4,D:D)1,VLOOKUP(B4,B:E,4,),"J"&TEXT(MAX(IFERRO ...

谢谢大神，可以用了~~~

inrever

1. =LET(a,IF(ISNUMBER(FIND("J",B4:B17)),E1,IF(SUMIF(B4:B17,B4:B17,D4:D17)<5,"J"&TEXT(REGEXP(E1,"\d+")+1,"00000"),B4:B17)),b,UNIQUE(FILTER(a,ISERR(FIND("J",B4:B17))*(SUMIF(B4:B17,B4:B17,D4:D17)>5))),c,HSTACK(b,"J"&TEXT(REGEXP(E1,"\d+")+1+SEQUENCE(ROWS(b)),"00000")),MAP(a,LAMBDA(n,IFNA(VLOOKUP(n,c,2,),n))))

复制代码