如何在Excel同工作表对比两列GUID并返回不匹配记录

前置说明 #

你遇到的GUID排序异常，是因为Excel默认将带连字符的GUID识别为普通文本，排序时按逐字符ASCII码规则比对，和常规GUID的排序逻辑不一致。但两列数据比对不需要提前完成排序，直接用以下两种Excel自带功能即可实现，不需要安装第三方插件。

方法1：函数法（适合10万行以内的中小数据量） #

• 第一步先清洗脏数据：选中两列GUID所在区域，按快捷键Ctrl+H调出替换窗口，「查找内容」输入一个半角空格，「替换为」留空，点击「全部替换」，清除GUID首尾可能存在的不可见空格，避免匹配错误。
• 假设第一列name在A列、数据从A2单元格开始，第二列name在B列、数据从B2单元格开始：
  • 在空白列C2单元格输入公式，下拉填充到数据最后一行，提取A列独有、B列不存在的GUID：

    =IF(COUNTIF(B:B,A2&"*")=0,A2,"")
    

  • 在空白列D2单元格输入公式，下拉填充到数据最后一行，提取B列独有、A列不存在的GUID：

    =IF(COUNTIF(A:A,B2&"*")=0,B2,"")
    

• 最后把C、D两列所有非空单元格的值复制出来，就是两列中互不匹配的全部记录。
  公式末尾加&"*"是为了规避Excel对超长文本的匹配截断问题，比直接用裸单元格引用的匹配准确率更高

方法2：Power Query法（适合10万行以上大数据量，可一键刷新） #

• 选中两列GUID的全部数据区域（含表头），点击顶部菜单栏「数据」选项卡，选择「从表格/区域」，在弹出的窗口中勾选「表包含标题」，点击确定进入Power Query编辑器。
• 由于两列表头均为name，编辑器会自动将第二列重命名为name.1，无需手动修改。
• 选中第一列name，点击顶部「转换」选项卡，找到「逆透视列」的下拉箭头，选择「逆透视其他列」，此时所有GUID会被汇总到名为值的列中。
• 选中值列，点击顶部「主页」选项卡的「分组依据」，分组字段选择值，新列名设为出现次数，操作选择「对行进行计数」，点击确定。
• 点击出现次数列的筛选按钮，仅保留值为1的行，此时值列的所有内容就是仅在单列出现、互不匹配的GUID，点击「关闭并上载」即可导出到工作表。后续数据更新后只需要点右键刷新就能自动重新计算结果。

示例结果验证 #

用你给出的示例输入测试：

第一列GUID：
fffb91b7-f5e5-4d81-af52-ff8d3887624c
fffb7e2a-8c44-4350-a1fd-5f0879b2c5ad
ffec4706-cc0a-4cd3-89a8-2b1c0475600c
ffe5f849-b5ff-4042-842b-592ed9f134fe

第二列GUID：
fde0137b-7918-4942-bacf-db3358e92e7f
fffb7e2a-8c44-4350-a1fd-5f0879b2c5ad
f83355f6-191a-4f29-b951-77ef5148f64a
ffe5f849-b5ff-4042-842b-592ed9f134fe

最终提取的不匹配记录为：

fffb91b7-f5e5-4d81-af52-ff8d3887624c
ffec4706-cc0a-4cd3-89a8-2b1c0475600c
fde0137b-7918-4942-bacf-db3358e92e7f
f83355f6-191a-4f29-b951-77ef5148f64a

注：你给出的预期输出存在笔误，ffe5f849-b5ff-4042-842b-592ed9f134fe在两列中同时存在，属于匹配记录，不属于互不匹配的结果范围。

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