这是一个网友的求助,有两个二维数据表,两个表的行标题并不完全一致,甚至列标题也有可能不一致,但是要把两个表合二为一,行列坐标一致的话,交叉处的数据加总。

有人给出了公式解法,可是公式长得看不下去,显然不是很友好的解决方案。

遇到类似需求,想都不用想,Power Query 事半功倍,几乎不用动脑,就点几下菜单。看上去步骤挺多的,实际操作的时候最多几分钟。

案例:

将下图 1 中的两个二维表合并成一个,纵、横标题相同的交点处,数据加总;找不到相同的坐标就新增,效果如下图 2 所示。

excel同时设置两条公式(将两个行列标题不同的)(1)

excel同时设置两条公式(将两个行列标题不同的)(2)

解决方案:

1. 选中上方数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”

excel同时设置两条公式(将两个行列标题不同的)(3)

2. 在弹出的对话框中保留默认设置 --> 点击“确定”:

excel同时设置两条公式(将两个行列标题不同的)(4)

表格已上传至 Power Query。

excel同时设置两条公式(将两个行列标题不同的)(5)

3. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”

excel同时设置两条公式(将两个行列标题不同的)(6)

4. 在弹出的对话框中选择“仅创建连接”--> 点击“加载”

excel同时设置两条公式(将两个行列标题不同的)(7)

5. 选中下方数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”

excel同时设置两条公式(将两个行列标题不同的)(8)

6. 在弹出的对话框中点击“确定”

excel同时设置两条公式(将两个行列标题不同的)(9)

第二个表格也上传到了 Power Query 中。

excel同时设置两条公式(将两个行列标题不同的)(10)

7. 点开左侧的“查询”导航 --> 选中“表1”--> 选中“姓名”列 --> 选择菜单栏的“转换”-->“逆透视”-->“逆透视其他列”

excel同时设置两条公式(将两个行列标题不同的)(11)

excel同时设置两条公式(将两个行列标题不同的)(12)

8. 选中“查询”中的“表2”--> 用同样的方法逆透视除“姓名”外的其他列

excel同时设置两条公式(将两个行列标题不同的)(13)

excel同时设置两条公式(将两个行列标题不同的)(14)

9. 选中“表1”--> 选择菜单栏的“主页”-->“追加查询”

excel同时设置两条公式(将两个行列标题不同的)(15)

10. 在弹出的对话框中选择“表2”作为“要追加的表”--> 点击“确定”

excel同时设置两条公式(将两个行列标题不同的)(16)

excel同时设置两条公式(将两个行列标题不同的)(17)

11. 选中“属性”列 --> 选择菜单栏的“转换”-->“透视列”

excel同时设置两条公式(将两个行列标题不同的)(18)

12. 在弹出的对话框中按以下方式设置 --> 点击“确定”:

excel同时设置两条公式(将两个行列标题不同的)(19)

excel同时设置两条公式(将两个行列标题不同的)(20)

13. 手动拖动列顺序,使星期按顺序排列。

excel同时设置两条公式(将两个行列标题不同的)(21)

14. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”

excel同时设置两条公式(将两个行列标题不同的)(22)

15. 在弹出的对话框中选择“仅创建连接”--> 点击“加载”

excel同时设置两条公式(将两个行列标题不同的)(23)

16. 在 Excel 右侧的“工作簿查询”区域选中“表1”--> 右键单击 --> 在弹出的菜单中选择“加载到”

excel同时设置两条公式(将两个行列标题不同的)(24)

17. 在弹出的对话框中选择“表”--> 选择“现有工作表”及所需上传到的位置 --> 点击“加载”

excel同时设置两条公式(将两个行列标题不同的)(25)

绿色区域就是合并后的结果。

excel同时设置两条公式(将两个行列标题不同的)(26)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

,