跟我学EXCEL-52（PQ案例演示1）

封面

亲爱的小伙伴们，跟我学EXCEL系列福利来了，从初级一直到高级学习EXCEL系列文章，结合财务实际应用讲解，配合动图细节演示，通俗易懂，是一套比较系统的不可多见学习EXCEL的好文章。持续更新中！

本系列文章包括基础篇（包括技巧、函数）、进阶篇（主要是数据透视表）、高级篇（主要是Power Query）。

希望大家喜欢，欢迎提出宝贵意见和建议！

大家好，我们继续学习Power Query。

四、EXCEL高级篇-Power Query02

02、Power Query实操案例演示

（1）、PQ学习前言

PQ操作和学习是有一定难度的，而且PQ也存在基础和高级操作的区别，本套课件本着实用、简单的学习原则，所以在PQ的学习上尽量使用模块化命令操作，这也是PQ的基础操作。

PQ的高级操作主要依托多达七百多专用M函数实现的，对于初次学习PQ来说，这些函数的操作是比较复杂的，所以我们从基础的开始学习。PQ为了实现普通操作者也能上手，将这些函数进行了命令模块化，也就是PQ将一些基础的PQ操作做成了菜单命令，例如，类似EXCEL中的“分列”功能，在PQ里差不多的功能叫“拆分列”，当然PQ里的功能要远远比EXCEL里的菜单功能多而且强大。

所以对于初学者来说尽量使用PQ模块化命令来学习更容易理解一些。

（2）、PQ实操案例1演示

为了更好的学习PQ我们先用一个案例演示来看利用PQ操作能实现什么样的效果。（演示数据信息在文末）

我们以财务最常见的合同为例。我们有两个基础表，一张是“合同登记”，有四列，分别是“序号”、“合同号”、“合同日期”、“应收金额”，有153个合同。这张表用于我们常用的合同登记情况，实际合同登记项目可能比这张表多，我们这里简化了。动图一。

动图一

另外一张基础表是我们从账套里提取的带合同号的实际收款的记录，一般来说我们是记入了“主营业务收入”了，至于怎么从账套里得到这个实收款的表格是另外的问题了，我们暂且忽略这个过程。但最好不要用普通办法一笔一笔单独手工录制一个实收款表，或者在合同登记表里手工录入实收款，因为账套里有一套实收数据，手工再登记一套，这样就显得重复工作了，而且手工录制还容易出错。

这个基础表叫“财务收款”，有五列，分别是“序号”、“合同编号”、“实收款”、“收款日期”、“备注”，收款记录有388条，有的合同是一笔收款，有的合同是多笔收款。动图二。

动图二

通常我们的需求就是看看登记的合同应收款和实收款的对比。

常规做法是在“合同登记”表里增加一列，用SUMIFS函数将“财务收款”表里的实收款按合同号汇总以后填入，再增加一列，用应收款减去实收款，从中筛选出不为零的记录，看看哪些不一致。同时我们还得核对一下是否有实际收款了漏登记的合同，用我们前面讲过的技巧也好，函数也好都可以实现这个需求。这里就不作具体演示了，相信大家都能操作了。

或者用数据透视表透视财务收款记录再和合同登记表对比。

在PQ里当然也能实现这个需求，具体来说，在PQ里的这个需求有这么几种做法。

①、第一种做法

直接得到不相符结果的表格，就是直接将所有未收款不为零的记录提取出来，形成一个新的表格，再根据这个表修改不正确的记录或用作他处，我们将这个表命名为“核对表”。截图一。

截图一

“核对表”有六列，分别是 “序号”、“合同号”、“合同编号”、“应收金额”、“实收金额”、“未收金额”（这些列名都可以自定义，列数也可以任意减少或增加），这个“核对表”里的序号是“合同登记”里的序号，合同号是“合同登记”的合同号，合同编号是“财务收款”里的合同编号，“应收金额”是“合同登记”里的，“实收金额”是“财务收款”里的实收款汇总后的金额，“未收金额”就是应收金额减去实收金额。

我们来看，一共得到了不相符的15条记录，其中“合同登记”里有14条合同的应收款没有收齐，还有1条记录的合同号在“合同登记”里没有记录，但有收款记录，假设我们经过核查是漏登记了，合同应收金额就是19740，日期是2019-1-31，我们只需要在“合同登记”里补上这,条记录，再刷新“核对表”即可。添加了漏登记的合同记录以后，刷新核对表，这条不相符的记录就消失了，剩下的就是合同应收没有收齐的14条记录了。动图三。

动图三

这个操作看似和我们常规操作也差不多，但其实还是区别挺大的，一来是双向核对，不管是“合同登记”有记录，“财务收款”全部没有或部分没有记录，还是“财务收款”有记录“合同登记”没有的记录都可以一次性提取出来，二来，最关键的是，如果我们是连续每月有这样的需求，我们只需要将合同记录和收款记录持续添加到各自的基础表上以后，刷新“核对表”即可，这样就大量减少了重复常规操作的步骤了，否则常规操作的话，我们每个月都得用那些技巧和函数来操作一遍，才能得到我们的需求表格。

这就体现了PQ的好处：适合大量重复性的EXCEL操作，而且丝毫不会改变原始两个基础表的任何东西。

②、第二种做法

得到一个全部核对记录的表格，就是形成所有相符不相符的记录表，我们将这个表命名为“合同款项统计表”。为了方便使用数据透视表，我们在PQ操作过程中添加了一个辅助列“收款状态”，这个全纪录表比前面的核对表多了一列“收款状态”。一共是154行记录，连标题栏算上是155行，凡是应收款减去实收款是0的我们标记为“相符”，凡是不为0的一律标记为“不相符”，这样就将收款情况分为了两大类。

然后透视“合同款项统计表”，用“收款状态”为主字段透视，这样不仅可以得到不相符的记录，也可以看到全部合同应收款、已收款、未收款的全貌。动图四。