EXCEL，如何比较2个表里的数据差异（使用数据透视表）

目录

1 问题: 需要比较如下2个表的内容差异

1.1 原始数据喝问题

1.2 提前总结

2 使用EXCEL公式方法

2.1 新增辅助列： 辅助index

2.2 具体公式+ 配合条件格式 使用

3 数据透视表方法

3.1 新增辅助列： 辅助index 

3.2 需要先打开 数据透视表向导

3.3 数据透视表比较的基本步骤

3.3.1 使用数据透视表向导

3.3.2  一定要：自定义页字段，其实就是定义2个区域表的名称，否则 区域表名会默认为 “项1，项2” 且不好修改

  3.3.3 分别选2个区域

 3.3.4  输出到新工作表或 现在sheet选个单元格作为左上角区域都可以

3.3.5 生成的基本数据透视表

3.3.5 生成的基本数据透视表的 变换

3.3.6  取消 分类汇总数据，没啥意义

 3.3.7  最终的数据透视表比较的效果

​编辑

 3.3.8  如果想加入差值，最终效果

---

1 问题: 需要比较如下2个表的内容差异

1.1 原始数据喝问题

• 表1：暂时命名A 或 AA
• 表1：暂时命名B 或 BB
• 这2个表，基本内容是相同的，列名查差不多，但是可能新增列
• 行的对象基本都是同类的东西
• 因此可以进行比较

 

1.2 提前总结

• 方法1： 写公式判断
• 方法2： 用数据透视表比较

2 使用EXCEL公式方法

2.1 新增辅助列： 辅助index

• B3&C3
• 因为有些数据可能有个多个index，这里举例子是2个，还可以更多
• 加辅助列就可以简单的把多个index 合成一个 唯一的index 识别列

2.2 具体公式+ 配合条件格式 使用

• =IF(ISNA(VLOOKUP($D3,B!$C:$Z,MATCH(E$2,B!$1:$1,0)-2,FALSE)),"B表里无此数据",IF(ISNA(VLOOKUP($D3,A!$C:$Z,MATCH(E$2,A!$1:$1,0)-2,FALSE)),"A表里无此数据",IF(VLOOKUP($D3,B!$C:$Z,MATCH(E$2,B!$1:$1,0)-2,FALSE)=VLOOKUP($D3,A!$C:$Z,MATCH(E$2,A!$1:$1,0)-2,FALSE),"相同","不同")))
• 为了判断B表里是否有这个列属性对应的数据ISNA(VLOOKUP($D3,B!$C:$Z,MATCH(E$2,B!$1:$1,0)-2,FALSE)),"B表里无此数据"
• 为了判断A表里是否有这个列属性对应的数据IF(ISNA(VLOOKUP($D3,A!$C:$Z,MATCH(E$2,A!$1:$1,0)-2,FALSE)),"A表里无此数据"
• 比较这个列属性在前面2个表里分别的数值VLOOKUP($D3,B!$C:$Z,MATCH(E$2,B!$1:$1,0)-2,FALSE)=VLOOKUP($D3,A!$C:$Z,MATCH(E$2,A!$1:$1,0)-2,FALSE),"相同","不同"
• 配合条件格式，会更显著

3 数据透视表方法

• 数据透视表一般用来汇总，其实也可以用来对比

3.1 新增辅助列： 辅助index 

• 也需要做辅助的index列

3.2 需要先打开 数据透视表向导

• 因为涉及了多张表，需要先从文件--选项里，打开 数据透视表向导

3.3 数据透视表比较的基本步骤

3.3.1 使用数据透视表向导

• 使用数据透视表向导，因为需要考虑多区域的数据
• 也可以不使用数据透视表向导，但是那样则需要 手动把2个表合并到一张表上，比较麻烦，且不直观，我选择 多种数据区域的数据透视表

3.3.2  一定要：自定义页字段，其实就是定义2个区域表的名称，否则 区域表名会默认为 “项1，项2” 且不好修改

  3.3.3 分别选2个区域

• 添加区域1
• 设置字段数目1（没必要设置多个字段吧！）
• 设置字段1的名字
• 添加区域2 ---如果选不动，记得从 向上箭头⬆这选
• 设置字段数目2
• 设置字段2的名字

 3.3.4  输出到新工作表或 现在sheet选个单元格作为左上角区域都可以

 

3.3.5 生成的基本数据透视表

• 行，
• 列，
• 值，数据
• 页：也就是 不同的数据表的区域，可以是表名或其他

3.3.5 生成的基本数据透视表的 变换

• 先把页移动到行里，最后
• 先把页移动到列里，最后

3.3.6  取消 分类汇总数据，没啥意义

 3.3.7  最终的数据透视表比较的效果

 3.3.8  如果想加入差值，最终效果

• 设置差值
• 需要先选择，页名--也就是表名位置，然后选择计算项来设置
• 修改字段名：差值‘
• 修改公式：BB-AA