E2:H7{=INDEX($B:$B,SMALL(IF((COUNTIF($D$2:D$7,$B$2:$B$25)=0)*(COUNTIF(E$1:E1,$B$2:$B$25)=0)*IF(COLUMN(A1)=4,1,$B$2:$B$25<>""),ROW($2:$25)),RANDBETWEEN(1,IF(COLUMN(A1)=4,7,COUNTA($B:$B)-COLUMN(A1)*6+6)-ROW(A1))))&""
區域數組公式 E2:H7{=INDEX(B:B,RIGHT(LARGE(RANDBETWEEN(ROW(2:25)^0+(ROW(1:24)<19)*9,IF(ROW(1:24)>18,9,99))/1%+RIGHT(LARGE(IF(B2:B25<>"",RANDBETWEEN(ROW(2:25)^0,999),)/1%+ROW(2:25),ROW(1:24)),2),ROW(1:6)+{1,2,3,4}*6-6),2))&""
J2=AND(COUNTIF(E:H,B2:B25)=(B2:B25<>"")*1)
留言
張貼留言