Excel如何按CLASS字段匹配合并两个数据表生成SET3

前置准备 #

将SET1（学生测试成绩明细表）、SET2（班级属性表）放在同一个Excel工作簿内，建议分开放置在两个独立工作表，可分别命名为「成绩明细」「班级属性」；提前检查两个表的CLASS字段格式统一（同为文本/同为数值，无多余前后空格），避免匹配失败。

方法一：VLOOKUP函数法（适合千行以内小数据量，操作门槛低） #

• 打开SET1所在的「成绩明细」工作表，在现有4列表头后新增2列，列名分别填写SIZE、YEARS。
• 假设表头在第1行，A到D列依次为CLASS、Student、TEST、SCORE，点击SIZE列首个数据单元格（即E2），输入公式：

  =VLOOKUP($A2,班级属性!$A:$C,2,FALSE)
  

  公式说明：$A2为当前行待匹配的班级值，锁列不锁行可保证下拉填充时自动读取对应行的班级；班级属性!$A:$C为SET2的查找范围，需保证CLASS字段在该范围的最左列（SET2表内A列为CLASS、B列为SIZE、C列为YEARS）；2代表取查找范围第2列的SIZE值；FALSE代表精确匹配，必须填写否则会出现匹配错误。
• 公式输入完成按回车，将鼠标移到E2单元格右下角，等光标变为黑色十字形后双击，即可自动填充整列SIZE的匹配结果。
• 点击YEARS列首个数据单元格（即F2），输入公式：

  =VLOOKUP($A2,班级属性!$A:$C,3,FALSE)
  

  按回车后同样双击单元格右下角填充整列，即可得到所有行的YEARS匹配值。
• 若需要固定结果避免源数据变动影响，可选中E、F两列所有填充完成的单元格，按Ctrl+C复制，再右键选择「粘贴选项-值」，将公式转换为静态数值，此时得到的就是完整的SET3数据集。

*注意：如果单元格出现#N/A报错，优先检查两个表的CLASS值是否存在多余空格、格式不一致的问题，比如一侧是数值格式的1，另一侧是文本格式的"1"，就会匹配失败。

方法二：Power Query 合并法（适合万行以上大数据量/后续需要定期更新数据的场景） #

• 分别将两个源表转为超级表：打开对应工作表，选中表内任意单元格，按Ctrl+T，勾选「表包含标题」后点击确定，可在顶部「表设计」选项卡的表名称栏将两个表分别命名为SET1、SET2方便识别。
• 选中SET1表内任意单元格，点击顶部「数据」选项卡，选择「从表格/区域」，会自动弹出Power Query编辑器。
• 在编辑器顶部「主页」选项卡中，选择「合并查询」-「将查询合并为新查询」（如果需要直接在SET1基础上加列可直接选「合并查询」）。
• 在弹出的合并设置窗口中，上方选择SET1表的CLASS列，下方下拉选中SET2表，再点击SET2表的CLASS列，连接种类选择「左外部（第一个表中的所有行，第二个表中的匹配行）」，点击确定。
• 此时编辑器内会新增一列名为SET2的列，点击列名右侧的展开箭头，仅勾选SIZE、YEARS两个字段，取消勾选「使用原始列名作为前缀」，点击确定。
• 最后点击编辑器顶部「关闭并上载」，Excel会自动生成包含6个目标字段的SET3数据表。后续如果SET1、SET2的源数据有更新，只需要右键点击SET3表选择「刷新」，即可自动同步最新结果，不需要重复操作。

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