Google Sheets用Filter实现数组差集(Set Difference)的两个问题

问题1解答：原有逻辑错误原因及替代方案 #

• 现有写法执行失败的核心原因是COUNTIF的参数顺序写反：COUNTIF(匹配范围, 校验条件)的第一个参数应该是被用来对比的数组B，第二个参数是要逐一校验的数组A的元素，原有写法把A和B的位置搞反才会出现匹配错误。
• 修正参数顺序后的COUNTIF方案可正常通过所有测试用例：

  FILTER(A1:1, COUNTIF(B1:1, A1:1)=0)
  

• 你提到的MATCH函数也是可行方案，对长数组来说性能开销更低，写法如下：

  FILTER(A1:1, ISERROR(MATCH(A1:1, B1:1, 0)))
  

  第三个参数0代表精确匹配，ISERROR用来筛选出在B中没有匹配结果的A元素。

问题2解答：分隔符文本转数组的优化方案 #

• 你当前使用的补位+ARRAY_CONSTRAIN操作完全多余，SPLIT直接输出的变长数组不需要对齐长度，就可以直接在FILTER、MATCH等函数中使用，额外的字符串拼接和截断操作只会增加不必要的性能开销。
• 针对分隔符文本的完整差集计算代码如下（可自动处理元素前后空格、直接输出拼接完成的结果字符串）：

  =TEXTJOIN(", ", TRUE, FILTER(TRIM(SPLIT(A1, ",")), ISERROR(MATCH(TRIM(SPLIT(A1, ",")), TRIM(SPLIT(B1, ",")), 0))))
  

  各部分作用说明：
  • TRIM用来处理分隔符后附带的空格，避免"b"和" b"匹配失败的问题
  • TEXTJOIN用来把过滤后的数组合并回你需要的分隔符字符串格式
• 如果只需要输出数组供其他函数调用，直接用TRIM(SPLIT(对应单元格, ","))即可，不需要做额外处理。

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