在Google Sheets中如何生成A列剔除B列值后的非重复值列？

问题概述 #

• 场景：A列与B列存在大量相似值，内容不完全一致、顺序不同，且禁止修改两源列
• 需求：在C列生成结果，仅保留A列中未出现在B列的非空值
• 示例：
  • A列：Ed, Ned, Kyle, Myles, Ruby, Crystal, Eric, Jimi
  • B列：Kyle, Ruby, Crystal, Eric, Ed
  • 预期C列：Ned, Myles, Jimi

你尝试的公式因逻辑错误导致数组过滤失效：

=FILTER( ARRAYFORMULA({FILTER($D$11:$D,$D$11:$D<>"");FILTER(Import!$B$3:$B,Import!$B$3:$B<>"")}), ARRAYFORMULA({FILTER($D$11:$D,$D$11:$D<>"");FILTER(Import!$B$3:$B,Import!$B$3:$B<>"")})<>Import!$B$3:$B)

错误原因分析 #

1. 筛选范围偏离目标：你堆叠了A列（$D$11:$D）和B列（Import!$B$3:$B）作为筛选对象，但需求是仅提取A列的内容，合并两列完全不符合需求逻辑。
2. 数组长度不匹配：合并后的数组行数是A+B列的总行数，而筛选条件中对比的B列（Import!$B$3:$B）仅为B列本身行数，Google Sheets会自动扩展短数组填充，导致逻辑判断完全混乱，无法得到正确结果。

简洁解决方案 #

以下两种方法均无需多步骤嵌套（如ISERROR(SEARCH())），直接实现需求：

方法1：FILTER + MATCH 组合（推荐） #

公式：

=FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)), A:A<>"")

• 逻辑：
  • MATCH(A:A, B:B, 0)：查找A列每个值在B列中的精确匹配位置，找不到则返回#N/A
  • ISNA(...)：筛选出匹配不到（即不在B列）的A列值
  • 最后一个条件A:A<>""过滤掉A列的空单元格

方法2：QUERY函数 #

公式：

=QUERY(A:A, "SELECT A WHERE A <> '' AND NOT A MATCHES '"&TEXTJOIN("|", TRUE, B:B)&"'", 1)

• 逻辑：
  • TEXTJOIN("|", TRUE, B:B)：将B列非空值用|连接，生成正则匹配的模式串
  • QUERY筛选A列非空且不匹配该模式的内容
  • 最后参数1表示A列有表头，若无表头可改为0

验证示例 #

将示例中的A列、B列代入方法1的公式，会自动返回Ned, Myles, Jimi，完全符合预期。

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