EXCEL中如何用函数，从符合一定条件的数据中随机取一个值（如图）？
4 个回答
D列是用RAND()函数生成的随机数。以前都是用LARGE函数来获得不同的随机数，再获取不同的人员，这样可以得到不重复的人员，不过此例中由于被抽取的数量少于需要抽取的数量，所以只能用RANDBWEEN，这样将会产生重复。如果要放在两个表，函数相关数组前增加【表二!】这种标记就行了。
方法一
B2=VLOOKUP(LARGE(($E$2:$E$13=A2)*$D$2:$D$13,RANDBETWEEN(1,COUNTIF($E$2:$E$13,A2))),$D$2:$F$13,3,0)，按ctrl+shift+enter，按F9随机抽取。D列随机数放在F列账户左侧时可以用这个。
方法二
C2=INDEX($F$2:$F$13,MATCH(LARGE(($E$2:$E$13=A2)*$D$2:$D$13,RANDBETWEEN(1,COUNTIF($E$2:$E$13,A2))),$D$2:$D$13,0),1)，按ctrl+shift+enter，按F9随机抽取。D列随机数放在F列的左侧或右侧时都可以。
以下表为例：
需求
从【表二】中查找【表一】用户部门 对应的 随机一个 用户账户
需求实现
假设当前单元格为【表一!B2】
- 确定当前单元格对应的查找范围：即【表一!B2】对应的查找范围为：【表二!B2:B4】
CONCAT(IF(表二!$A$2:$A$100000=$A2,表二!$B$2:$B$100000,""))
公式解释：
IF(表二!$A$2:$A$100000=$A2,表二!$B$2:$B$100000,"")
返回【表二!$A$2:$A$100000】中与【$A2】匹配时，【表二!$B$2:$B$100000】所对应的的值组成的数组，即{"cwxxxxx",...,"cwxxxxx"}
CONCAT(...)
将返回的数组连接成字符串，即"cwxxxxx...cwxxxxx"- 确定随机的值：即确定上述返回的字符串中的第几个数据
RANDBETWEEN(0,COUNTIF(表二!$A$2:$A$100000,"="&$A2)-1)*LEN(VLOOKUP($A2,表二!$A$2:$B$100000,2,FALSE))+1)
COUNTIF(表二!$A$2:$A$100000,"="&$A2)
条件计数函数：计算【表二!$A$2:$A$100000】中与【$A2】匹配的有多少行 ，即有多少个财务行
VLOOKUP($A2,表二!$A$2:$B$100000,2,FALSE)
在【表二!$A$2:$B$100000】精确查找【$A2】对应行的第2列的值，即 第一个财务部账号
LEN(第一个财务部账号)
财务部账号的字符串长度
RANDBETWEEN(0, (财务行个数-1))*财务部账号的字符串长度+1
随机生成财务部账号在字符串中的位置- 最终公式：在返回结果拼接成的字符串中，根据随机的位置，取得随机的账号
=MID(CONCAT(IF(表二!$A$2:$A$100000=$A2,表二!$B$2:$B$100000,"")),RANDBETWEEN(0,COUNTIF(表二!$A$2:$A$100000,"="&$A2)-1)*LEN(VLOOKUP($A2,表二!$A$2:$B$100000,2,FALSE))+1,LEN(VLOOKUP($A2,表二!$A:$B,2,FALSE)))
公式解释
MID(上述字符串,上述位置,财务部账号的字符串长度)将最终公式填入【表一!B2】，按【Ctrl+Shift+Enter】，下拉填充至其余单元格
因公式计算过程中包含数组，所以需要通过组合键来完成计算，直接【Enter】则计算不完整。
以上，问题解决啦，撒花！！！
ps: 如果想取巧的话，可以这么写公式
财务部
=MID(CONCAT(表二!$B$2:$B$4),RANDBETWEEN(0,3-1)*7+1,7)
人力资源部
=MID(CONCAT(表二!$B$5:$B$17),RANDBETWEEN(0,13-1)*7+1,7)
可以参考如下做法（用到函数index与randbetween的嵌套）：
使用index或者offset公式，嵌套match和randbetween函数
=INDEX($B$2:$B$12,RANDBETWEEN(MATCH(F2,$A$2:$A$12,0),MATCH(F2,$A$2:$A$12)))或者
=OFFSET($B$1,RANDBETWEEN(MATCH(F2,$A$2:$A$12,0),MATCH(F2,$A$2:$A$12)),)
