H2:L5區域數組公式{=RIGHT(SMALL(RANDBETWEEN((ROW(1:4)*5-5+COLUMN(A:E))^0,999)*10^8+B2:F5,ROW(1:4)*5-5+COLUMN(A:E)),8)*1
另解 H2:L5區域數組公式{=SMALL(B2:F5,RIGHT(SMALL(RANDBETWEEN(ROW(1:20)^0,999)/1%+ROW(1:20),ROW(1:4)*5-5+COLUMN(A:E)),2))
H9:L12數組公式{=INDIRECT(TEXT(RIGHT(SMALL(IF((COUNTIF($H$8:$L8,$B$9:$F$12)=0)*(COUNTIF($G9:G9,$B$9:$F$12)=0),ROW($9:$12)/1%+COLUMN($B:$F)),RANDBETWEEN(1,21-ROW(A1)*5+5-COLUMN(A1))),4),"!R0C00"),)
N2{=AND(COUNTIF(B2:F5,H2:L5)=1)
留言
張貼留言