基于Google Sheets的神秘圣诞老人生成器：如何规避同家庭配对

1. 新增家庭分组列 #

先给表格加一列标记家庭组（比如新增C列，列名Family），两种实现方式：

• 自动提取姓氏：如果姓名格式是「名 姓.」（如Louise H.），用公式自动提取姓氏作为家庭标识：

  =REGEXEXTRACT(B2,"(\w+)\.$")
  

  下拉填充所有行，这样同姓氏的成员会被归为同一家庭。
• 手动指定：如果姓名格式不统一，直接手动输入家庭编号/名称（如H、C、D），准确性更高。

2. 替换配对公式（排除自己+同家庭） #

原有的RANK+VLOOKUP逻辑无法过滤同家庭，改用筛选+随机选择的方式，在Giftee列（原E列，调整后为F列）使用以下公式：

逐行公式（简单易上手） #

在F2单元格输入，下拉填充：

=INDEX(
  FILTER($B$2:$B$7, 
    $B$2:$B$7<>B2, 
    $C$2:$C$7<>C2
  ),
  RANDBETWEEN(1, COUNTA(FILTER($B$2:$B$7, $B$2:$B$7<>B2, $C$2:$C$7<>C2)))
)

逻辑说明：

1. FILTER 先筛选出不是自己且不同家庭的所有参与者
2. RANDBETWEEN 从筛选结果里随机选一个位置
3. INDEX 提取对应位置的姓名作为配对对象

数组公式（一次性生成无重复配对） #

如果要避免重复分配同一个人，先启用迭代计算（文件>设置>计算>迭代计算，勾选并设迭代次数为100），再在F2单元格输入数组公式：

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(
      SEQUENCE(ROWS(B2:B7)),
      SORT(
        FILTER(
          {SEQUENCE(ROWS(B2:B7)), B2:B7, C2:C7, RANDARRAY(ROWS(B2:B7))},
          NOT(COUNTIFS(F$1:F1, B2:B7))
        ),
        4, TRUE
      ),
      2, FALSE
    )
  )
)

注：要保证每个参与者至少有1个符合条件的配对对象，否则会出现错误。

3. 更新错误检查公式 #

原Run Again?列（原F列，调整后为G列）需要同时检查自配对和同家庭配对，公式改为：

=IF(
  OR(B2=F2, C2=VLOOKUP(F2,$B$2:$C$7,2,FALSE)),
  "Error - Run Again",
  ""
)

只要出现自配对或同家庭配对，就提示重新生成。

调整后表格结构示例 #

注意事项 #

• 若某参与者没有符合条件的配对对象（比如仅2个同家庭成员），公式会返回错误，需调整参与人员或分组。
• 按Ctrl+R刷新表格可重新生成配对，直到所有错误提示消失。

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