Excel技术问询：如何筛选Sheet2中含未匹配Sheet1 ID的行？

方法一：辅助列公式法 #

1. 在Sheet2右侧新增一列（比如E列），表头设为「ID匹配状态」
2. 在E2单元格输入以下公式，下拉填充到所有行：

   =SUMPRODUCT(--ISNUMBER(SEARCH(Sheet1!$A$2:$A$3, B2)) + --ISNUMBER(SEARCH(Sheet1!$A$2:$A$3, C2)) + --ISNUMBER(SEARCH(Sheet1!$A$2:$A$3, D2)))>0
   

   公式说明：
   • 分别检查Comment(B列)、Comment2(C列)、Comment3(D列)的文本中是否包含Sheet1里的任意ID
   • 返回TRUE表示该行匹配到了有效ID，FALSE表示未匹配
3. 筛选E列中值为FALSE的行，就是你要找的ID不匹配的整行

方法二：Power Query法（适合大数据量） #

1. 将Sheet1和Sheet2的数据分别转为Excel表格（选中数据→按Ctrl+T→勾选「我的表格有标题」）
2. 进入「数据」选项卡，点击「自表格/区域」，分别将两个表格导入Power Query编辑器
3. 处理Sheet1的查询：只保留「ID number」列，右键重命名为「有效ID」，关闭并上载为仅连接
4. 处理Sheet2的查询：
   • 添加自定义列，公式输入：

     List.Count(List.Select(Sheet1[有效ID], (x) => Text.Contains([Comment], x) or Text.Contains([Comment2], x) or Text.Contains([Comment3], x))) > 0
     

   • 筛选自定义列中值为FALSE的行
   • 点击「关闭并上载」，将结果导出到新工作表

注意事项 #

• 如果Sheet1的ID数量较多，把公式里的Sheet1!$A$2:$A$3替换为动态范围，比如Table1[ID number]（Table1是Sheet1转成的表格名称）
• 公式中用SEARCH不区分大小写，若需要严格区分，替换为FIND

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