编按：

如何同时满足多次随机抽取、每次不重复值这两种条件呢？需要自动排除已抽取的，然后对还没有抽过的数据赋予不同的数字，最后在这些数字中随机抽取。一起来看看吧！

公司近期由于生产任务紧张，需要每周随机抽调业务部门的一名业务员去支援生产，对于已经抽调过的就不再重复抽调直到下轮开始。那么如何来实现这样的需求呢？

要注意两个特别点：

（1）会进行多次随机抽取，而不是一次抽取

（2）每次随机抽取前都要排除已抽取的人员，避免前后抽取中出现重复

也就是说，必须在多次进行的随机抽取中自动排除已抽值确保前后每次都不重复。当前网上绝大多数的随机抽取教程是办不到的。

我们的解决思路就是只为每位还没有抽过的人员生成一个动态数字，然后在这些数字中随机抽取。

1.添加动态序号

被抽到的员工将记录到A15:C24区域（区域行数等于员工总数），我们只为没有抽到的人员添加序号。

定位到A2单元格输入公式“=IF(OR(C2=$C$15:$C$24),"",COUNT($A$1:A1)+1)”，接着按CTRL+SHIFT+ENTER组合键完成数组公式的输入，然后下拉填充。

公式解释：

IF函数判断值，先使用OR数组函数对“C2=$C$15:$C$24”判断，如果C2出现在C15:C24（即已经抽调过），那么序号就显示为空；否则将“COUNT($A$1:A1)”计数结果加1后作为序号。此公式自动排除了已抽取的人员，确保不管随机抽取多少次，前后抽取的都不会重复。

2.计算周次

定位到D2单元格输入公式“="第"&WEEKNUM(TODAY())&"周"”，计算当天所属周次。这样每周一打开文档后可以自动识别周次。

3.随机抽取

定位到E2单元格输入公式“=IFERROR(VLOOKUP(RANDBETWEEN(1,MAX($A$2:$A$11)),$A$2:$C$11,COLUMN(B1),0),"已抽完")”。然后右拉填充公式。

公式解释：

使用“RANDBETWEEN(1,MAX(A2:A11))”生成一个随机数作为VLOOKUP的查找条件，最小值是1，最大值是序号列最大序号值。由于RANDBETWEEN是随机函数，按下F9键就会变化，从而实现了随机抽取。

最后在外层嵌套IFERROR函数，当员工全部被抽取后，RANDBETWEEN(1,MAX(A2:A11))会出现错误，此时强制显示为“已抽完”。

4.自动记录随机值

为了方便记录每次抽取的随机值，我们使用宏来自动复制。点击“开发工具→录制宏”，按提示新建一个名为“复制记录”的宏，并设置快捷键如Ctrl+X。

确定后执行下面的操作：

1）选中D2:F2

2）按Ctrl+C复制

3）选中A15

4）点击“开始→粘贴→选择性粘贴→数值”，将D2:F2粘贴为值

5）按Esc取消键取消拷贝模式。

点击“停止录制”按钮。

按ALT+F11打开VBA编辑，可以看到录制好的宏代码如下。

刚才的5个操作都记录在代码中，见图中红色框内。

这时，如果我们按F9随机抽取人员，再按Ctrl+X记录，始终只能在A15:C15处记录一条信息。

因此需要修改一下代码：

用“Range("a65536").End(xlUp).Offset(1, 0).Select”覆盖“Range("A15").Select”。

并加一句Range("D2:F2").Select，让每次粘贴后鼠标都回到D2:F2处。

到此，主要工作完成！剩下的是设置颜色提醒。

5.颜色标记已抽取人员和已抽完

选择E2:F2，点击“开始→条件格式→使用公式确定要设置格式的单元格”，输入公式“=E2="已抽完"”，单元格填充为棕色。

同上，选中A2:C11,公式“=A2=""”，颜色填充为黄色，标记已抽取人员。

6.实际使用

每周一打开文件，长按F9抽取人员，然后按Ctrl+X记录抽取结果。

当A2:C11区域颜色全变为黄色，E2:F2变为棕色，表示“已抽完”。此时删除A15:C24的数据，又可以开始抽取了。大家可以举一反三，可以每次抽取多名人员，也可以将上述例子变为多次抽奖的随机抽奖工具。

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

提取不重复值并统计数量的三个方法，一秒完成！

如何提取唯一值？试试TEXTJOIN函数搭配VBA自定义！

Excel教程：如何制作带有层次和透视感的图表？

八大查找函数公式，轻松搞定数据中的多条件查找

版权申明：

本文作者ITFANS；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。