G2:P6 區域數組公式 {=INDEX(INDIRECT(TEXT(RIGHT(SMALL(RANDBETWEEN(N(+A2:E11),999)*10^6+ROW(2:11)/1%+COLUMN(A:E),ROW(1:5)*10-10+COLUMN(A:J)),4),"!R0C00"),),ROW(A1),COLUMN(A1))
G10:P14{=INDIRECT(TEXT(SMALL(IF((COUNTIF($G$9:$P9,$A$2:$E$11)=0)*(COUNTIF($F10:F10,$A$2:$E$11)=0),ROW($2:$11)/1%+COLUMN($A:$E)),RANDBETWEEN(1,51-ROW(A1)*10+10-COLUMN(A1))),"!R0C00"),)
R2{=AND(COUNTIF(A:E,G2:P6)=1)
R10{=AND(COUNTIF(A:E,G10:P14)=1)
留言
張貼留言