How does excel realize grouping random lottery?

Assuming that A is listed as a department and B as a name, 8 people are randomly selected from 5 departments, and each department is at least 1 person.

Add an auxiliary column C, the formula is =rand (), that is, generate random numbers in column C.

The extraction formula is =LET(a, REDUCE ("name "), C2: C32, lambda (x, y, if (y = max (filter (C2: C32, A2: A32 = offset (y, -2))), vstack (x, offset (y, -2). TAKE(SORTBY(FILTER(B2:B32，ISNA(MATCH(B2:B32，a，))，RANDARRAY(26))，3)))

The idea is: first extract the members with the highest score in each department, so as to ensure that each department has a quota first. Then remove the five people who have just been selected, arrange the rest randomly, and then take the top three.

Because the value of column C is a random number, three people are randomly arranged after extraction, which ensures that eight random and non-repetitive people are extracted every refresh.