Excel无重复随机填充：50行表格仅填40个不重复姓名的问题

核心需求 #

• 50行表格，仅对Active值为1的行填充姓名
• 需使用40个不重复的姓名，所有姓名必须被用到
• Active值非1的行留空

现有公式问题 #

当前使用的公式：

=@IF([@Active]=1,INDEX((SortlistDB[Spotcheckers]),RANK(@SortList!J:J,(SortlistDB[SCSorter])),1),"")

问题在于：公式会对所有行（包括Active≠1的行）计算排名，当Active=1的行数量不足40时，排名靠前的部分姓名会被分配给Active≠1的行（但这些行留空），导致这部分姓名实际未被使用，最终仅用到部分姓名。

解决方案 #

我们需要先筛选出所有Active=1的行，再给这些行分配全部40个随机不重复姓名，以下是两种可行方法：

方法1：动态数组+LET函数（Excel 365/2021及以上） #

在表格姓名列的首行输入公式，自动向下填充：

=LET(
    activeRows, FILTER(ROW([@]), [@Active]=1),
    randomNameSeq, INDEX(SortlistDB[Spotcheckers], RANDARRAY(40,1,1,40,TRUE)),
    IF([@Active]=1, XLOOKUP(ROW([@]), activeRows, randomNameSeq), "")
)

逻辑说明：

• activeRows：提取所有Active=1的行号
• randomNameSeq：生成40个姓名的随机不重复序列
• XLOOKUP：将随机姓名精准匹配到对应的Active=1行，非激活行自动留空

方法2：传统数组公式（兼容旧版Excel） #

在姓名列第一个单元格输入以下公式，按Ctrl+Shift+Enter确认数组公式后，向下填充到所有行：

=IF([@Active]=1,INDEX(SortlistDB[Spotcheckers],SMALL(IF(COUNTIF($C$1:C1,SortlistDB[Spotcheckers])=0,ROW(SortlistDB[Spotcheckers])-MIN(ROW(SortlistDB[Spotcheckers]))+1),INT(RAND()*(40-COUNT($C$1:C1))+1))),"")

注意：公式中$C$1:C1需替换为姓名列的实际单元格范围（如姓名列是D列则改为$D$1:D1），逻辑为每次填充时自动排除已使用的姓名，确保不重复，且仅在激活行生效。

关键优化点 #

• 先锁定Active=1的目标行，再分配姓名，避免姓名浪费在非激活行
• 确保随机序列仅针对需要填充的行，保证40个姓名全部被使用

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