1. 首页
  2. 技术教程
  3. excel使用小技巧

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

今天我又要来解答网友的问题了,给我的这个题目倒是有点难度,问题是这样的:我想要将人员平均分配组别,且用随机的方式来做。其实看起来好像很简单,但其实颇难的,如果单纯只是「随机」分配很简单,但如果同时要兼顾「平均」有点难度,我思维了几种做法后,想了一个尽可能简单的做法给大家参考,用到的函数也都是常见且易用的,以便大家能够理解,往下来看怎么做吧。

通过Excel 随机平均分配组别

先给大家看一下要做的东西长这样,假设人员有A 到O 共15 位,我想要将他们平均分配到甲、乙、丙三个组别里,等于每组是5 人的话要怎么做。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

我本来想用一行函数来全部搞定,但怕大家无法理解内容(很复杂),所以改用辅助的方式来做。

右边的E 列先打入组别及名称,这边我以甲乙丙来示范。

F 行是辅助用的随机码,这一列长度会和人员长度一样,函式使用的是RAND,直接在F2 输入

=RAND()

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

然后在F2 将函数往下延伸,也就是F2 到F16 都是RAND()。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

接下来比较复杂的公式来了,我们在C2 那栏输入以下公式:

=INDIRECT("E"&((MOD(RANK(F2,$F$2:$F$16),3)+1)+1))

然后一样从C2 延伸公式到C16(就是滑鼠移到C2 栏位右下角会出现十字,按住后往下拖曳到C16 即可)。

接下来说明一下公式,我们先讲一下最里面的RANK 这一段

=RANK(F2,$F$2:$F$16)

这是将F2~F16 排名出来1~15 名,所以单纯打这一段会出现1~15 的数字(大家可以试试)。

然后再透过MOD 这个函数来处理排名

=MOD(RANK(F2,$F$2:$F$16),3)+1

如果是打这一段,你会发现会显示的就是1~3 的数字,如果你的组别是有五个,那其中那个3 就改成5 就可以。

MOD这是用来显示余数使用的,通过这个函数会以1~15来说,除以3后会得到的余数会是0~2,所以在公式的尾数多一个+ 1来补成1~3。

如果你不需要甲、乙、丙的组别名称的话,其实到这个步骤就搞定了,就已经区分出1~3 的随机平均分配。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

最后如果你想要直接将E 列的甲、乙、丙直接带入的话,最终公式就是长这样

=INDIRECT("E"&((MOD(RANK(F2,$F$2:$F$16),3)+1)+1))

或者

=INDIRECT("E"&(MOD(RANK(F2,$F$2:$F$16),3)+2))

INDIRECT 这个公式是用来显示栏位值,比如INDIRECT(E2),那就会显示E2 的值,也就是甲,我们刚刚C 列做到MOD 为止是显示1~3,但我们要指向的内容是E2~E4,所以在公式尾把多加了个1,或者将里面的+1 改成+2 都可以,这样就会显示E 列的组别名称了。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

如果你想要验证每个组别的人数是否有平均的话,可以用COUNTIF 这个函数来做,这部份阿汤就不特别解说了,COUNTIF 是非常简易使用的一个函数。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

最后完整的含验证就长这样了。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

如果你要分配的量是15,但要分到四组,会无法整除也可以用吗?是的,也可以,会像这样,某一组别少一个人。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

由于我是用RAND 这个函数来做随机取数,所以当你随便输入一格后,排列都会重新来过,因此,分完组别后要固定下来的话,可以在组别的项目复制起来。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

然后在组别的第一栏位右键「选择性贴上」。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

选择值按下确定。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

这样就会覆盖原本的公式,变成单纯的文字,就不会再变动了,后面的DEF 列也都可以清空了。

Excel小技巧– 如何随机且「平均」分配项目,比如人员分组

文末说明一下为什么要用RAND() 来做随机码排列,因为RAND() 产生的随机值重复机率非常非常的低,当然如果你的样本有1 万组的话,那可能有一点点的机会重复,但原则上很难,会用这方式来做的原因是,如果你是要随机且不重复产生1~15 的数字,公式其实也不是简单就搞定,不如就用RAND(),反正有加入COUNTIF验证每组人数是否平均就好了,不用那么麻烦。

发布者:yuanyuan,转转请注明出处:https://www.xiaoyuanjiu.com/11278.html

发表评论

登录后才能评论