隨機連續區域不重複 依人員數 (10277)

D2下拉 {=INDEX(B:B,SMALL(IF(COUNTIF(D$1:D1,B$2:B$9)=INT((ROW(A1)-1)/(COUNTA(B:B)-1)),ROW($2:$9)),RANDBETWEEN(1,COUNTA(B:B)-MOD(ROW(A1)-1,COUNTA(B:B)-1)-1)))

格式化 D列=MOD(INT((ROW(A1)-1)/(COUNTA(B:B)-1)),2)*(D2<>"")