Excel实用工具12：随机分组模板

 

我们经常会碰到这样的需求，需要将一群人随机分成几组。本文介绍的模板，可以帮助你容易地创建随机分组。你需要输入人员列表以及要创建的分组数，剩下的就是让Excel为你自动分配了，如下图1所示。

图1

 

在单元格区域A2:A17中是人员姓名，你可以在末尾添加更多的人名。在单元格E2中是想要创建的分组数，其于此分组数，在列G至列P中创建分组。本模板支持最多10个分组。

 

在单元格E2中输入想要创建的分组数，单击“创建随机分组”，就可在右侧的单元格中显示各组。

 

工作原理

工作表中使用了辅助列，即列B和列C。将区域A1:C17转换成表，Excel自动将其命名为Table1。这样，就可以自动调整表中的数据而保持结构不变，即可以随意增加或删除人员姓名。

 

在列B中的公式为：

=RANDBETWEEN(1,COUNTA([姓名])) + ROW()/100

其中，COUNTA函数统计列A中的人数，RANDBETWEEN函数生成在1和总人数之间的随机数。由于RANDBETWEEN函数可能会产生重复数，因此添加ROW()/100来使随机数唯一，如图2所示。

图2

 

在单元格C2中的公式为：

=RANK([@唯一值],[唯一值])

给列B中的值进行排序。由于列B中的所有值都是唯一的，因此该公式生成的列C中的唯一整数列表是从1至人员数之间的整数值。

图3

 

在单元格G1中的公式为：

=IF(COLUMNS($G$1:G1)>$E$2,'',COLUMNS($G$1:G1))

将公式拖至单元格P1。公式返回当前列与列G间隔的列数值，因此单元格G1值是1，H1是2，依此类推。数值不会超过单元格E2中的值，超过的单元格中返回空。

图4

 

在单元格G2中的公式为：

=IFERROR(IF(G$1<>'',INDEX(Table1[姓名],INDEX(Table1[排序],G$1+$E$2*(ROWS($F$2:F2)-1))),''),'')

将其拖拉至G2:P17，根据生成的随机数提取列A中的值。

 

由于RANDBETWEEN函数是易失的，因此每当工作表中有变化时都会自动刷新，这可能不是我们想看到的，因此，我们将计算设置为“手动重算”，如图5所示。

图5

 

现在，按F9键才会刷新工作簿，随机变化分组名。我们可以在工作表中添加按钮并指定VBA代码，通过单击来刷新工作表。

 

VBA代码很简单：

Sub refresh()

    Worksheets('TeamGenerator').Calculate