如何使用QUERY函数根据另一工作表的条件排除指定名称

你可以直接使用QUERY函数结合TEXTJOIN实现跨工作表读取排除条件，批量过滤不符合要求的名称条目，具体操作方案如下：

基础实现公式 #

=QUERY(工作表1!A:C, "SELECT * WHERE A NOT IN ('"&TEXTJOIN("','", TRUE, 排除表!A2:A)&"')", 1)

参数说明 #

• 第一个参数工作表1!A:C：替换为你实际的原始数据源范围
• 名称匹配列：公式里的A对应数据源中存储待匹配名称的列号，可根据实际需求调整
• 排除范围排除表!A2:A：替换为你存储排除条件的工作表+单元格范围，建议排除内容从第二行开始，第一行留作表头
• 末尾参数1：代表原始数据源第一行为表头，若你的数据源无表头可改为0

常见场景适配 #

• 需匹配带单引号的名称：使用以下转义版本公式避免语法错误：

  =QUERY(工作表1!A:C, "SELECT * WHERE A NOT IN ('"&TEXTJOIN("','", TRUE, SUBSTITUTE(排除表!A2:A, "'", "\'"))&"')", 1)
  

• 不区分大小写匹配：统一转换为小写匹配，公式如下：

  =QUERY(工作表1!A:C, "SELECT * WHERE LOWER(A) NOT IN ('"&TEXTJOIN("','", TRUE, LOWER(排除表!A2:A))&"')", 1)
  

• 仅查询指定列：将SELECT *替换为需要返回的列号即可，例如仅返回A、B、D三列就改为SELECT A,B,D

注意事项 #

• 如果排除列表为空，公式会自动返回全部原始数据源内容，不会触发报错
• 若排除条目超过50000个，TEXTJOIN会存在字符上限，此时可改用FILTER+ISNA/MATCH组合实现，公式如下：

  =FILTER(工作表1!A:C, ISNA(MATCH(工作表1!A:A, 排除表!A:A, 0)))
  

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