Excel 实现同一分组随机分配相同值的公式求解

可用的Excel公式方案 #

适用Excel 365/2021及以上版本 #

先确认你的数据布局：

• Group列在A列，数据从A2行开始
• 候选值的Random列表存放在D2:D10区域（可根据你的实际范围调整）
• 需要填充的RandomName列为B列，从B2开始填结果

在B2单元格输入以下公式，直接下拉填充即可：
=INDEX($D$2:$D$10,XLOOKUP(A2,UNIQUE($A$2:$A$100),RANDARRAY(COUNTA(UNIQUE($A$2:$A$100)),,1,COUNTA($D$2:$D$10),TRUE)))
公式逻辑：先提取所有唯一的Group值，给每个唯一Group分配1到Random列表总数之间的固定随机整数，相同Group会匹配到同一个随机索引，所以取出的Random值完全一致

适用所有Excel版本（含2019及更早版本） #

需要加1列辅助列实现，操作步骤如下：

1. 新增辅助列C列，在C2输入公式下拉填充，计算每个Group第一次出现的行号：
   =MATCH(A2,$A$2:$A$100,0)
2. 在B2输入以下公式下拉填充即可：
   =INDEX($D$2:$D$10,MOD(RANDBETWEEN(1,9999)+C2,COUNTA($D$2:$D$10))+1)
   说明：相同Group的辅助列C值完全一致，会生成相同的随机索引，所以最终取出的Random值也保持一致。如果不需要保留辅助列，选中B列所有结果复制后右键粘贴为值，就可以删除C列了

注意事项 #

• 公式中的范围$A$2:$A$100、$D$2:$D$10请根据你实际的数据范围修改，注意不要漏掉绝对引用的$符号
• 如果需要固定结果不再随机变动，选中所有RandomName列的单元格，右键粘贴为值即可

内容的提问来源于stack exchange，提问作者karweng