本帖最后由 我走我流 于 2021-6-13 13:53 编辑
=IFERROR(ROUND(MOD(TEXT(LARGE(TEXT(FREQUENCY(IFERROR(--TEXT(IF(LEN($B2:$G2),$B2:$G2+TRANSPOSE($H$2:$K$2)),"[<=0] ;[>33] ;0"),""),ROW($1:$33))+FREQUENCY(IFERROR(--TEXT(IF(LEN($B2:$G2),$B2:$G2-TRANSPOSE($H$2:$K$2)),"[<=0] ;[>33] ;0"),""),ROW($1:$33)),"[>1]0;!0")/1%%+1/ROW($1:$34),COLUMN(A1)),"[<1000]!A"),100)^-1,),"")
借用版主老师的思路,,先 转成一维数组
=IFERROR(-MOD(TEXT(LARGE(INDEX(FREQUENCY(IF(LEN($B2:$G2),$B2:$G2+SMALL($H$2:$K$2*{1;-1},ROW($1:$8))),ROW($1:$99)-1),N(IF({1},ROW($2:$34))))/1%%+100-ROW($1:$33),COLUMN(A1)),"[<2e4]!a"),-100),"") |