跟我学EXCEL-56（PQ案例02合同收款核对）

封面

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

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

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

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

四、EXCEL高级篇-Power Query06

05、PQ案例02合同收款核对

（1）、案例02基础表及需求

基础表一为“合同登记”表，有“序号”、“合同号”、“合同日期”、“应收金额”四列，实际工作中这个表可能还有更多列，演示就简化了，格式如下。截图一。

截图一

基础表二为“财务收款”表，有“序号”、“合同编号”、“收款日期”、“实收款”、“备注”五列，这张表一般来说是从财务系统的“主营业务收入”贷方提取整理的数据表，格式如下。截图二。

截图二

通常我们的需求是哪些合同款项已经收齐了，哪些未收或未收齐的具体情况。

（2）、PQ操作过程

第一步、将“合同登记”表导入PQ

为防止合同号重复，可以通过“数据”->“数据验证”进行防止合同号重复的设置，或通过“开始”-> “条件格式”-> “突出显示单元格规则”-> “重复值”设置来显示重复合同号。因为这张表的合同号按理说是不应该重复的。具体设置方法前面已经讲过，这里就不重复了。

将光标放置在“合同登记”表内，点击“数据”-> “来自表格/区域”，如果这张表是普通表，会弹出的对话框，如果“表包含标题”已经默认勾选，确定即可，如果未勾选，一定要勾选。如果这张表是超级表就直接进入查询编辑器了。动图一。

动图一

第二步、识别列的类型

我们来注意看，右侧的查询步骤窗口，目前有两个步骤，一个是“源”，一个是“更改的类型”，把“合同登记”表导入到PQ后系统根据列的情况自动识别的类型，“合同日期”被识别为“日期/时间”类型，我们修改为“日期”类型，替换当前转换即可。动图二。

动图二

第三步、将合同号中所有字母转化为大写

为防止“合同登记”和“财务收款”表里的合同号大小写不一致导致的核对错误，我们提前将“合同登记”里的合同号列的字母都转换为大写。选定“合同号”列，点击“转换”->“格式”->“大写”。动图三。

动图三

第四步、修改查询名

为了看的清楚，我们将查询“表4”修改为“合同”，当然你也可以修改为其他的名字，只是为了好识别查询而已。动图四。

动图四

第五步、关闭并上载

因为这张表并不是最终需求的表，所以我们将它放置在后台，用于我们后期调用即可。点击“主页”-> “关闭并上载”右侧小三角，选择“关闭并上载至” -> “仅创建连接”。动图五。

动图五

第六步、将“财务收款”表导入到PQ

将光标放置在“财务收款”表内，点击“数据”-> “来自表格/区域”，如果这张表是普通表，会弹出的对话框，如果“表包含标题”已经默认勾选，确定即可，如果未勾选，一定要勾选。如果这张表是超级表就直接进入查询编辑器了。

同时，像上面操作一样先将“收款日期”修改为日期格式，然后将“合同号”列中字母转化为大写，选定“合同号”，点击“转换”-> “格式”->“大写”，如果“备注”没用就删除了，将查询“表1”修改为“财务”。动图六。

动图六

第七步、汇总收款数据

因为合同收款不一定就是一笔收齐，有可能多笔收款，所以我们这里用按合同号将实收款汇总了。动图七。

动图七

第八步、关闭并上载

因为这张表并不是最终需求的表，所以我们将它放置在后台，用于我们后期调用即可。点击“主页”-> “关闭并上载”右侧小三角，选择“关闭并上载至” -> “仅创建连接”。动图八。

动图八

第九步、生成需求表01

这里我们要根据这两张表生成需求表。

操作过程其实就是将这两张表横向合并，首先，我们选择“合同”，然后右键选择“合并”，对话框这里，因为你选择的是“合同”，所以这个查询已经有了，合并的第二个查询需要你选择，我们选择“财务”这个查询，选择后要注意，这里要选择合并的依据，这里就是“合同号”；“联接种类”这个选项非常重要，PQ提供了六种选项，这里我们选择“完全外部”，选择后我们会进入查询编辑器的操作界面了，我们点击“财务”标题后面的符号，这是展开或聚合的功能，默认是展开不用管，下面的选项一般我们将“使用原始列名作为前缀”勾选去除，根据需要选择“财务”中的列，第一次选择了“合同编号”，选错了，这里正好可以演示一下，在PQ里出现操作错误也可以通过步骤中的小齿轮进行修改。我们将实收款展开。动图九。

动图九

第十步、生成需求表02

这里我要提醒一下，在PQ里空值和0不是一个概念，在EXCEL里空值在计算过程中默认是0，这里不是，这里面的空值是用null表示的，null是无法计算的，所以，在计算之前我们要将null替换为0，然后我们选择“应收金额”列，点击“添加列”->“标准”->“减”，选择“实收款”，新增的列就是未收款，我们修改一下列名。动图十。

动图十

第十一步、生成需求表03

为了将来更好的透视数据，我们根据未收款增加一个类别列，凡是未收款为0的标识为相符，凡是未收款不为0的标识为不相符。

“添加列”->“条件列”，对话框中新列名默认是“自定义”，我们可以修改。列名我们选择“未收款”，运算符我们选择“等于”，值我们输入0，输出我们输入“相符”，下面的ELSE我们输入“不相符”。完成后我们关闭查询编辑器，PQ会自动将我们生成的需求表上载到新的工作表，这个工作表的名称我们可以任意修改，比如我就修改为“核对表”。动图十一。