如何基于复合键合并两个Excel工作表并处理缺失键？

数据背景 #

Sheet1数据 #

说明：LOOK_UP_KEY_1 是 LOC_NBR、CATEG_CODE、KEY_CODE 的拼接值。

Sheet2数据 #

说明：LOOK_UP_KEY_2 是 LOC_NBR、CATEG_CODE、KEY_CODE 的拼接值。

预期结果（Sheet3） #

核心需求 #

• 基于LOOK_UP_KEY_1和LOOK_UP_KEY_2实现两表的全量合并（包含两边独有的数据）
• 键匹配时，同时显示两表的DATA_TXT值
• Sheet1独有的键：Sheet2对应列显示Not Present，DATA_TXT_2显示NA
• Sheet2独有的键：Sheet1对应列显示Not Present，DATA_TXT_1显示NA

实现方法 #

方法一：Power Query（推荐，适合大数量级数据） #

这是高效稳定的方式，支持自动刷新：

1. 导入数据到Power Query
   • 选中Sheet1任意数据单元格，点击「数据」选项卡→「从表格/区域」，勾选「我的表格有标题」，确定进入编辑器
   • 重复操作导入Sheet2的数据
2. 整理列数据
   • Sheet1查询中，保留LOOK_UP_KEY_1和DATA_TXT列，删除其他列；右键DATA_TXT重命名为DATA_TXT_1
   • Sheet2查询中，保留LOOK_UP_KEY_2和DATA_TXT列，删除其他列；右键DATA_TXT重命名为DATA_TXT_2
3. 全外连接两表
   • 回到Sheet1查询编辑器，点击「主页」→「合并查询」→「合并查询作为新查询」
   • 合并设置：
     • 上半部分选Sheet1查询，匹配列选LOOK_UP_KEY_1
     • 下半部分选Sheet2查询，匹配列选LOOK_UP_KEY_2
     • 连接种类选「全外部（所有行，两边匹配的行）」
   • 确定后，点击合并列右侧的展开按钮，只勾选LOOK_UP_KEY_2和DATA_TXT_2，取消「使用原始列名作为前缀」，确定
4. 处理空值
   • 选中LOOK_UP_KEY_1列，点击「转换」→「替换值」，空值替换为Not Present
   • 选中LOOK_UP_KEY_2列，重复上述操作替换空值
   • 分别选中DATA_TXT_1和DATA_TXT_2列，将空值替换为NA
5. 加载结果到Sheet3
   • 点击「主页」→「关闭并上载至」，选择「仅创建连接」，再指定加载到Sheet3的起始单元格，确定

方法二：函数公式法（适合小数量级数据） #

数据量小时可手动用公式实现：

1. 提取所有唯一键
   • 在Sheet3的A列（辅助列），复制Sheet1的LOOK_UP_KEY_1和Sheet2的LOOK_UP_KEY_2所有值
   • 选中A列，点击「数据」→「删除重复值」，得到所有唯一匹配键
2. 填充各列公式
   • LOOK_UP_KEY_1列（Sheet3 B2）：=IFERROR(VLOOKUP(A2,Sheet1!$E:$E,1,FALSE),"Not Present")，下拉填充
   • LOOK_UP_KEY_2列（Sheet3 C2）：=IFERROR(VLOOKUP(A2,Sheet2!$E:$E,1,FALSE),"Not Present")，下拉填充
   • DATA_TXT_1列（Sheet3 D2）：=IFERROR(VLOOKUP(A2,Sheet1!$E:$D,2,FALSE),"NA")，下拉填充
   • DATA_TXT_2列（Sheet3 E2）：=IFERROR(VLOOKUP(A2,Sheet2!$E:$D,2,FALSE),"NA")，下拉填充
3. 隐藏辅助列：完成后隐藏Sheet3的A列即可

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