上一篇我们教了如何用公式进行多条件查找并对结果求和,因为求助者不喜欢用数据透视表(数据透视表很无辜),所以我教了两种公式解法,详情请参阅 。
不过说句真心话,类似这种案例,公式真的不是最优解,所以今天我必须良心奉上另一种简单好用,又可随数据源实时自动更新结果的 Power Query 解法。
好久没写 Power Query 了,大家来温习一下。
案例:
根据下图 1 中右侧数据表中的条件,分别查找出左侧表中满足条件的订单数并求和。
效果如下图 2 所示。
解决方案:
1. 选中左侧数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”
2. 在弹出的对话框中点击“确定”
表格已上传至 Power Query。
3. 选择菜单栏的“主页”-->“分组依据”
4. 在弹出的对话框中按以下方式设置 --> 点击“确定”:
- 选择“高级”
- 在下拉菜单中选择“部门”--> 点击“添加分组”按钮
- 在第二个下拉菜单中选择“姓名”
- 新列名:输入“订单总数”
- 操作:选择“求和”
- 柱:选择“订单数”
5. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至...”
6. 在弹出的对话框中选择“仅创建连接”--> 点击“加载”
现在回到了 Excel。
7. 选中右侧数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”
8. 在弹出的对话框中点击“确定”
9. 选择菜单栏的“主页”-->“合并查询”
10. 在弹出的对话框中按以下方式设置 --> 点击“确定”:
- 选择“表2”的“姓名”列
- 在下拉菜单中选择“表1”--> 选择“姓名”列
- 联接种类:选择“左外部(第一个中的所有行,第二个中的匹配行)”
11. 点击“表1”旁边的扩展箭头 --> 选择“展开”--> 仅勾选“订单总数”--> 不要勾选“使用原始列名作为前缀”--> 点击“确定”
12. 选择菜单栏的“主页”-->“关闭并上载”
Excel 中就会自动创建一个新的工作表,放置最终的匹配结果表。
今后源数据表或查询条件如果有更新,只需要刷新这个绿色的表格就能得到同步的查询结果。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。