在Excel中创建随机化实验伙伴矩阵

Question

我想使用Excel生成一个随机的实验室合作伙伴列表，而不使用VB (由于PC上的安全设置)。

参数如下：

学生人数: 10-30人，每个合作伙伴总数一个工作表:前两个实验室三个，其他四个-五个实验室两个。实验室的数量:10个重复:理想情况下没有，但允许学生在前两个实验室中有一个重复的伙伴。Excel版本: 2007

为了澄清，每个学生将有两个实验室，他们与其他两个学生共用一个实验室，最大的实验室规模为30名学生。在此之后，他们将严格限制在每站两名学生，最多给20名学生。每个学生将有四个这些有限的实验室，总共有五个这样的实验室，以便允许奇数班，或班级的大小在21-30之间。

每个学生的编号从1到30，例如，一个单元格可以将"5，24“作为该实验室的两个学生。

真正的RNG并不重要，实际上，只需要执行一次就可以生成这些矩阵。

Answer 1

我认为这在不使用VBA的情况下有点棘手，但是这里有一种方法可以用于小型组。我试过使用一组只有九个，这样屏幕截图应该是可读的。

该方法是基本的费舍-耶茨。

A Start with a group of students size n represented by a list of numbers 1 to n.

B Generate a random number r in range 1 to n

C Pick the rth element from the list

D Remove the rth element from the list

E Reduce n by 1

F Repeat from B until n=1.

在Excel中：

填A2:A10和D2:L2，编号1-9

将以下内容放在B2中并拉下：-

=RANDBETWEEN(1,10-A2)

把这个放进C2然后拉下来：-

=OFFSET(D2,0,B2-1)

把这个放进D3，然后拉下来，穿过：-

=IF(D2>=$C2,E2,D2)

ID在C列，所以前三个在第1组，下三个在第2组，等等。

​

​

顺便问一下，您的问题是生成非重复随机数的特例，请参阅

生成不带VBA的唯一随机数

这里描述的数组公式只需一步修改就可以解决这个问题。

=SMALL(IF(COUNTIF(C$1:C1,ROW(INDIRECT("1:9")))=0,ROW(INDIRECT("1:9"))),RANDBETWEEN(1,(9-ROWS(C$2:C2)+1)))