本文总共1554个字,阅读需6分钟,全文加载时间:2.094s,本站办公入门专栏收录该内容! 字体大小:

文章导读:有一阵子没写 Power Query 了,后台的呼声就起来了,今天马上安排。 公式、数据透视表、Power Query,没有孰好孰坏,只有最合适,哪种场景用哪个最方便,那就是它了。 有些事,用公式难于登天,但是在 Power……各位看官请向下阅读:

有一阵子没写 Power Query 了,后台的呼声就起来了,今天马上安排。

公式、数据透视表、Power Query,没有孰好孰坏,只有最合适,哪种场景用哪个最方便,那就是它了。

有些事,用公式难于登天,但是在 Power Query 中轻点两下鼠标就能办到,那为何不学着点呢?

案例:

某公司有 4 个业务职能部门,每个部门又分成了 5 个小部门。

老板要求各部门提交业绩报表及 4 季度工作计划,下图 1 是已经提交了业绩报表的部门列表。

要求还原一张完整的部门表,并列出哪些部门已经提交,哪些部门尚未。

效果如下图 2 所示。

解决方案:

1. 在 C 列复制一个所有部门名称列表,不需要列出分部。

2. 选中 C 列数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”

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

数据表已上传至 Power Query。

4. 选择菜单栏的“添加列”-->“自定义列”

5. 在弹出的对话框的“自定义列公式”区域输入以下公式 --> 点击“确定”:

={"1".."5"}

  • M 函数中的“..” 表示以前后两个数值为起始值,在区间内顺序填充;
  • 数值加上双引号,可以将其变成文本,因为部门名称中的数字是文本。

6. 点击“自定义”列旁边的扩展箭头 --> 选择“扩展到新行”

7. 选择菜单栏的“添加列”-->“自定义列”

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

  • 新列名:输入“所有部门”
  • 自定义列公式:输入“=[部门]&[自定义]&"部"

所有部门就还原出来了。这个过程,在 Excel 中用公式的话挺复杂的,但是在 Power Query 中就变得那么简单,这个还原过程也可以称为求笛卡尔乘积。有关笛卡尔乘积的案例,请参阅:

9. 选中前面两列 --> 选择菜单栏的“主页”-->“删除列”

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

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

部门的完整列表就还原出来了,接下来只要匹配 A 列就可以了。

12. 选中 A 列数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”

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

14. 点开左侧的“查询”区域 --> 选择前面还原出来的的完整部门表

15. 选择菜单栏的“主页”-->“合并查询”

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

  • 选中“表1”的“所有部门”列
  • 在下方的下拉菜单中选择“表3”--> 选中“部门”列
  • 联接种类:选择“左外部(第一个中的所有行,第二个中的匹配行)”

17. 点击“表3”右边的扩展箭头 --> 按以下方式设置 --> 点击“确定”

  • 选择“展开”,默认已勾选“部门”
  • 取消勾选“使用原始列名作为前缀”

18. 将第二列的列名修改为“已提交部门”

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

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

E 列的表格就自动刷新并拓展了,F 列中列出了已经提交报表的部门,其余空的就是尚未提交的。

当 A 列有新增的部门时,只要在绿色的表格区域刷新一下,F 列就能自动更新。

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

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

想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注