excel数据平均分配给多⼈_Excel⼩技巧–如何随机且「平均」
分配项⽬,⽐如⼈员分组...
今天我⼜要来解答⽹友的问题了,给我的这个题⽬倒是有点难度,问题是这样的:我想要将⼈员平均分配组别,且⽤随机的⽅式来做。其实看起来好像很简单,但其实颇难的,如果单纯只是「随机」分配很简单,但如果同时要兼顾「平均」有点难度,我思维了⼏种做法后,想了⼀个尽可能简单的做法给⼤家参考,⽤到的函数也都是常见且易⽤的,以便⼤家能够理解,往下来看怎么做吧。
通过Excel 随机平均分配组别
先给⼤家看⼀下要做的东西长这样,假设⼈员有A 到O 共15 位,我想要将他们平均分配到甲、⼄、丙三个组别⾥,等于每组是5 ⼈的话要怎么做。
我本来想⽤⼀⾏函数来全部搞定,但怕⼤家⽆法理解内容(很复杂),所以改⽤辅助的⽅式来做。
右边的E 列先打⼊组别及名称,这边我以甲⼄丙来⽰范。
F ⾏是辅助⽤的随机码,这⼀列长度会和⼈员长度⼀样,函式使⽤的是RAND,直接在F2 输⼊=RAND()
然后在F2 将函数往下延伸,也就是F2 到F16 都是RAND()。
接下来⽐较复杂的公式来了,我们在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 的随机平均分配。
最后如果你想要直接将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 列的组别名称了。
如果你想要验证每个组别的⼈数是否有平均的话,可以⽤COUNTIF 这个函数来做,这部份阿汤就不特别解说了,COUNTIF 是⾮常简易使⽤的⼀个函数。
最后完整的含验证就长这样了。
如果你要分配的量是15,但要分到四组,会⽆法整除也可以⽤吗?是的,也可以,会像这样,某⼀组别少⼀个⼈。
由于我是⽤RAND 这个函数来做随机取数,所以当你随便输⼊⼀格后,排列都会重新来过,因此,分完组别后要固定下来的话,可以在组别的项⽬复制起来。
然后在组别的第⼀栏位右键「选择性贴上」。
选择值按下确定。
rank函数怎么用
这样就会覆盖原本的公式,变成单纯的⽂字,就不会再变动了,后⾯的DEF 列也都可以清空了。
⽂末说明⼀下为什么要⽤RAND() 来做随机码排列,因为RAND() 产⽣的随机值重复机率⾮常⾮常的低,当然如果你的样本有1 万组的话,那可能有⼀点点的机会重复,但原则上很难,会⽤这⽅式来做的原因是,如果你是要随机且不重复产⽣1~15 的数字,公式其实也不是简单就搞定,不如就⽤RAND(),反正有加⼊COUNTIF验证每组⼈数是否平均就好了,不⽤那么⿇烦。