H2:I2 下拉 {=IFERROR(MID(0&SMALL(IF(($F$2-ROW(INDIRECT($C$2&":"&$D$2))-TRANSPOSE(ROW(INDIRECT($C$3&":"&$D$3)))>=$C$4)*($F$2-ROW(INDIRECT($C$2&":"&$D$2))-TRANSPOSE(ROW(INDIRECT($C$3&":"&$D$3)))<=$D$4),ROW(INDIRECT($C$2&":"&$D$2))/1%+TRANSPOSE(ROW(INDIRECT($C$3&":"&$D$3)))),ROW(A1)),COLUMN(A1)*2-1,2)*1,"")
H2:I2 (數字限 ~9) {=IFERROR(MID(0&SMALL(IF(($F$2-ROW($1:$9)-COLUMN($A:$I)>=$C$4)*($F$2-ROW($1:$9)-COLUMN($A:$I)<=$D$4)*(ROW($1:$9)>=$C$2)*(ROW($1:$9)<=$D$2)*(COLUMN($A:$I)>=$C$3)*(COLUMN($A:$I)<=$D$3),ROW($1:$9)/1%+COLUMN($A:$I)),ROW(A1)),COLUMN(A1)*2-1,2)*1,"")
J2:J11=IF(H2="","",F$2-H2-I2)
留言
張貼留言