从逻辑角度来说没啥难度,但是从公式角度太烦了,把ROW($1:$999)改成对应的统计区域就成N(INDIRECT(TEXT(SMALL(ROW(OFFSET($A$1,ROW()-1,,(INT(C2/10)+1)))*100+COLUMN(D:M),ROW(OFFSET($A$1,,,(INT(C2/10)+1)*10))),"R0C00"),)),这玩意有三段,所以改成以后就是- =IF(C2,MID(CONCAT(IFERROR(MID("/-/",MMULT(1-COUNTIF(OFFSET(D2:M2,,,C2/10+1),N(INDIRECT(TEXT(SMALL(ROW(OFFSET($A$1,ROW()-1,,(INT(C2/10)+1)))*100+COLUMN(D:M),ROW(OFFSET($A$1,,,(INT(C2/10)+1)*10))),"R0C00"),))-{1,-1}),{1;2}),1)&IF(1/COUNTIF(OFFSET(D2:M2,,,C2/10+1),N(INDIRECT(TEXT(SMALL(ROW(OFFSET($A$1,ROW()-1,,(INT(C2/10)+1)))*100+COLUMN(D:M),ROW(OFFSET($A$1,,,(INT(C2/10)+1)*10))),"R0C00"),)))^-1,N(INDIRECT(TEXT(SMALL(ROW(OFFSET($A$1,ROW()-1,,(INT(C2/10)+1)))*100+COLUMN(D:M),ROW(OFFSET($A$1,,,(INT(C2/10)+1)*10))),"R0C00"),))),"")),2,66),"")
复制代码 如果是365上新函数有多行多列转换函数就可以短 |