如何判断Sheet A单列值是否存在于多列存储的Sheet B数据中

需求说明 #

实现跨表值匹配校验：将Sheet A单列存储的待校验值，与Sheet B多列多行分布的全量数据做存在性校验，逐个值判断：

• 值在Sheet B中存在，返回match
• 值在Sheet B中不存在，返回No match

数据结构参考 #

• Sheet A为单列存储待校验值，结构参考：
  
• Sheet B为多列多行分布的匹配源数据，结构参考：
  
  

实现方法 #

方法1：函数公式（适合中小数据量，操作最快） #

直接在Sheet A待校验值旁的空白列输入公式下拉即可，假设：

• Sheet A第一个待校验值在A2单元格（A1为表头）
• Sheet B所有需要匹配的数据覆盖A到Z列（可根据实际数据列数调整范围）

在Sheet A的B2单元格输入以下公式，按回车后下拉填充整列即可得到全部结果：

=IF(COUNTIF(SheetB!$A:$Z,A2)>0,"match","No match")

公式说明：COUNTIF会统计待校验值在Sheet B指定多列范围内的出现次数，次数大于0就说明存在，返回匹配结果。范围前加$是绝对引用，下拉时匹配范围不会偏移。

如果遇到文本格式数字、数值格式数字混存导致匹配不准的问题，可以用以下兼容格式的公式：

=IF(COUNTIF(SheetB!$A:$Z,A2&"*")>0,"match","No match")

如果值存在前后多余空格导致匹配失败，可以搭配TRIM函数去除空格后匹配：

=IF(COUNTIF(SheetB!$A:$Z,TRIM(A2))>0,"match","No match")

方法2：Power Query（适合十万行以上大数据量，不卡顿） #

如果数据量很大，函数公式计算卡顿，可以用Power Query做匹配，步骤如下：

• 分别选中Sheet A、Sheet B的全部数据，按Ctrl+T将两个区域转为超级表
• 选中Sheet B的超级表，点击「数据」选项卡-「自表格/区域」，将数据导入Power Query编辑器
• 在Power Query里选中Sheet B所有存数据的列，点击「转换」选项卡-「逆透视列」，将多列数据合并为单列值结构
• 对逆透视后的「值」列做去重处理，将查询设置为仅创建连接不上载
• 再选中Sheet A的超级表导入Power Query，点击「合并查询」，选择左外连接，关联刚才生成的Sheet B去重值查询
• 新增自定义列，写入判断逻辑：if [匹配到的Sheet B值] <> null then "match" else "No match"
• 把最终结果上载回工作表即可

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