|
应付会计主要工作内容之一就是采购发票的校验，确认应付账款。所谓发票校验就是对发票中所开具物料的内容、数量、单价进行校验的过程。应付会计发票校验主要检查发票物料单价是否小于或等于采购订单中对应物料的单价、发票中物料数量是否小于或等于收货入库单中对应物料的数量。 在这里我们先看一看文件夹中存在：采购订单数据、收货入库数据、采购发票数据3张工作簿，如图1所示。采购订单数据主要有采购订单号、物料、数量、单价、供应商等信息；收货入库数据工作簿中主要有入库单号、收货数量等信息；采购发票数据工作簿中有发票号码、物料数量、单价等信息, 在采购发票数据工作簿中我特意预留了2处错误，用以展示Power Query关联数据、校验数据功能，如图2所示单元格有颜色填充部分。接下来我们来分享下在Power Query中是如何进行发票校验的。
图1
图2 1.单击“Power Query”选项卡下“获取外部数据”分组中“从文件”，找到存放上述3个工作簿的文件夹，选择“采购订单数据”工作簿，单击“确定”命令按钮，如图3所示。
图3 2.弹出“导航器”对话框，勾选“选择多项”和“采购订单”，单击“加载”下拉框中的“加载到”命令按钮，如图4所示。在“加载到”对话框中选择“仅创建连接”，去掉“将此数据添加到数据模型”勾选，单击“确定”命令按钮，如图5所示。
图4
图5 3.弹出“采购订单”工作簿查询窗口；同样方法加载“收货入库明细表”到工作簿中，单击“Power Query”选项卡下“组合”分组中“合并”命令按钮，如图6所示。
图6 4.弹出“合并”对话框，在“选择表和匹配列以创建合并表”中选择“采购订单”工作簿，在第二个下拉框中选择“收货入库明细表”工作簿，在两个工作簿界面中依次选择“PO”、“物料代码”、“物料名称”，注意一定要按照此顺序进行选择（一个物料可能由多个供应商供货，故需首先选择采购订单号PO，其次选择”物料代码”和“物料名称”），据此可以保证两张表关联的正确性。在“联接种类”中选择“完全外部（两者中的所有行）”选项，这是因为这样可以将两张表的不能匹配的项都显示出来，如图7所示。
图7 5.单击“确定”，这样就创建了一个采购订单与收货入库的合并关联表即“Merge1”，单击“NewColumn”右侧的双向箭头，默认选择“扩展”，去掉“使用原始列名作为前缀”勾选，如图8所示。
图8 如图9界面中展示了关联后的表格，左边展示的是订单信息，右边展示的收货入库信息。
图9 6.单击“查询编辑器”界面中“开始”选项卡下的“关闭并上载”命令按钮，如图10，保存查询“Merge1”。
图10 6.同样的方法将“采购发票数据”工作簿加载到此工作簿中，再次单击“组合”分组中“合并”命令按钮，弹出“合并”对话框，在“选择表和匹配列以创建合并表”中选择“Merge1”工作簿，在第二个下拉框中选择“采购发票明细表”工作簿，在两个工作簿界面中依次选择“入库单号”、“物料代码”、“物料名称”，这样就将两张表关联起来了（请注意按顺序进行选择关联字段），如图11所示。
图11 7.同样，单击“Newcolumn”列标题右侧的双向箭头，默认选择“扩展”，取消“使用原始列名作为前缀”前面的勾选，单击“确定”命令按钮，如图12所示，左边展示的是Merge1中的采购订单，收货入库信息，右边展示的发票明细信息（由于表格太大故截取部分信息），如图13所示。
图12
图13 在该查询中，我们可以看到：同一供应商、同一物料在采购订单、收货入库、采购发票三张工作簿中能够实现匹配关联的记录排在了同一行上；暂估入库的记录因没有开发票，所以则发票记录区域部分为null，只有采购订单与暂估入库信息匹配。 8.接下来我们在此查询中插入自定义列来自动实现发票校验的功能，对于能匹配的发票记录打上√符号，对不上的打上×的符号。单击查询编辑器界面中“添加列”选项卡下“添加自定义列”对话框，在新列名文本框中输入“数量校验”，在自定义列公式中输入如下公式：if [发票日期]=null then ' ' else if [发票数量]<=[入库数量] and="" [物料代码.2]="[物料代码.1]" then="" '√'="" else="">
图14 发票日期为null的是那些有采购订单或者入库信息的记录的，发票记录不存在的不进行匹配，故应返回空，对于满足发票数量<> 同样方法设置“单价校验”自定义列，输入公式：if [发票日期]=null then ' ' else if [单价]<=[不含税单价] and="" [物料代码.2]="[物料代码]" then="" '√'="" else="">
图15 发票日期为null的是那些有采购订单或者入库信息的记录的，发票记录不存在的不进行匹配，故应返回空，对于满足发票单价<> 9.选择Column11：Column16这些空列，右键选择“删除列”，单击“关闭并上载”，保存“Merge2”查询，结果图一部分如图16所示。
图16 其中出现'╳'号的是我此前特意预留的错误数据。单价校验列中出现'╳'号的是发票单价高于订单单价，故判定此发票存在疑问；最后一行昆山亚克西发票出现两个'╳'号的，是发票有这条物料开票记录，但采购订单和收货入库都不存在这个物料记录，故无匹配的记录存在。当数量和单价校验两者中只要有一个出现'╳'号，就可以认为发票存在疑问，这样就在Power Query中成功地实现了采购发票的校验。对于上述采购发票存在疑问的两种情况，应付会计应和采购部进行沟通，做出相应决定：退票或者调整采购订单单价。 将光标悬停在查询表中任一单元格，右键选择“刷新”，如果数据有变化，刷新后可以得到实时的发票校验结果；如果数据源路径发生变化，也可以依次修改加载基础数据的“查询设置”窗口的“应用的步骤”文本框中“源”在编辑栏处的路径，如图17所示。
图17 从以上Power Query中采购发票的校验可以看出，利用Power Query的合并关联功能可以保存并作为一个查询工具，如果以后继续有采购订单、收货入库、发票明细等数据可在查询中刷新数据得到发票校验的最新结果，更为重要的是Power query可以实现批量发票校验，相比财务系统不同供应商发票校验时只能一个供应商校验一次，不能一次校验多个供应商的多张发票具有更大的优势。 需要指出的是，这种采购发票校验方法只适合于在物料单价小于或等于订单单价的情况下使用，如果物料实际单价大于订单单价则必须有相应公司内部审批手续，这种情况属于线下控制，不在Power Query发票校验讨论范围之内。 来源：雷公子 |
|
|
