S2{=SUM(OFFSET(A2,,IFERROR(SMALL(IF(A2:Q2<>B2:R2,COLUMN(A:Q)),MODE(MMULT(N(ROW($1:$18)>IF(A2:Q2<>B2:R2,COLUMN(A:Q))),ROW(1:17)^0))),),,MAX(FREQUENCY(COLUMN(A:R),IF(A2:Q2<>B2:R2,COLUMN(A:Q))))))
另解{=SUM((MODE(MMULT(N(ROW($1:$18)>IF(A2:Q2<>B2:R2,COLUMN(A:Q))),ROW(1:17)^0))=MMULT(N(ROW($1:$18)>IF(A2:Q2<>B2:R2,COLUMN(A:Q))),ROW(1:17)^0))*TRANSPOSE(A2:R2))
留言
張貼留言