Google Sheets基于可用表自动填充无重复人员表及替补列表需求

核心需求 #

• 主表依据可用人员的志愿自动填充，绝对避免姓名重复
• 主表对应职位满员后，自动填充至替补区域，替补人员的职位统一改为其第一志愿
• 允许将「三个志愿中有不可用职位」的人员纳入替补池
• 全程使用公式实现，无需脚本/代码

解决方案（分模块） #

1. 主表无重复填充公式 #

假设主表职位列是C列，可用人员数据在可用表的D:N区域（D列为「可用状态」，E/G/I列为志愿1/2/3，N列为姓名），将以下公式放在主表姓名列首行（如A2），可实现按志愿优先级填充且自动排除已选人员：

=ARRAYFORMULA(IF(C2:C="",,IFERROR(
  QUERY(UNIQUE(FILTER('可用表'!$N:$N, '可用表'!$D:$D=TRUE, '可用表'!$E:$E=C2, ISNA(MATCH('可用表'!$N:$N, $A$1:A1, 0)))), "limit 1"),
  IFERROR(
    QUERY(UNIQUE(FILTER('可用表'!$N:$N, '可用表'!$D:$D=TRUE, '可用表'!$G:$G=C2, ISNA(MATCH('可用表'!$N:$N, $A$1:A1, 0)))), "limit 1"),
    IFERROR(
      QUERY(UNIQUE(FILTER('可用表'!$N:$N, '可用表'!$D:$D=TRUE, '可用表'!$I:$I=C2, ISNA(MATCH('可用表'!$N:$N, $A$1:A1, 0)))), "limit 1"),
      Positions!$B$2
    )
  )
)))

注：$A$1:A1用于排除当前行以上已选中的姓名，彻底避免重复；按志愿1→志愿2→志愿3的优先级匹配，无匹配项时返回Positions!$B$2的默认值。

2. 替补区域自动填充公式 #

替补区需筛选未被主表选中的可用人员，且将职位统一改为其第一志愿：

=ARRAYFORMULA(IFERROR(QUERY(
  FILTER('可用表'!$E:$N, '可用表'!$D:$D=TRUE, ISNA(MATCH('可用表'!$N:$N, 主表!$A:$A, 0))),
  "select Col10, Col1 where Col1 is not null"
), ""))

注：Col10对应可用表的姓名列，Col1对应第一志愿列；主表!$A:$A是主表的姓名列，用于排除已选人员；结果会自动列出所有符合条件的替补人员，姓名和第一志愿一一对应。

3. 志愿含不可用人员的替补纳入逻辑 #

上述替补公式已自动包含此类人员——只要可用表中该人员的「可用状态」为TRUE，即使其志愿中有不可用职位，只要未被主表选中，就会被纳入替补池。

关键逻辑说明 #

• 用MATCH函数标记已选人员，结合ISNA排除重复，彻底解决VLOOKUP的重复问题
• UNIQUE+QUERY limit 1确保每个职位只匹配1名未被选的人员
• 全程使用ARRAYFORMULA实现批量填充，无需逐行复制公式

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