利用POWER Query处理不同时期的产品成本核算

Flywithme2011

已于 2023-09-09 22:47:09 修改

阅读量399

点赞数

分类专栏： EXCEL数据分析文章标签： powerbi

于 2023-09-09 13:14:17 首次发布

本文链接：https://blog.csdn.net/Flywithme2011/article/details/132774932

版权

EXCEL数据分析专栏收录该内容

1 篇文章 0 订阅

订阅专栏

序言任务背景

在朋友圈群里面瞎逛，有个朋友提出来了一个问题，想问一下大家有没有好的办法来解决这个问题，具体的问题如下：

现在有两张数据表，一张是订单交易表，里面有字段（订单日期，订单编号，产品名称，产品编号，交易数量，交易金额），还有其他的字段，因为与本文数据处理关系不大，所以没有列出。另外还有一张表是产品成本表，里面有字段（添加日期，产品名称，产品编号，产品成本）。这个由于企业的生产，市场，物料，汇率等因素的影响，会导致不同的时期，产品成本的数据是不一样的，也就是说同一产品编号的产品，在不同的时期其产品成本会不一样，比如由于汇率的影响，以前1美元可以兑换6.5元人民币，现在却是1美元可以兑7.3元人民币，这样半年前的产品成本与今天的成本就会有差异。还有其他影响产品成本的时候，比如采用了新技术，员工劳动效率有提升等等，此时财务部门会对不同时期的产品成本进行核算，如果有差异，会更新该产品的产品成本，这样在产品成本表中同一个产品编号的产品有不同的产品成本。这两个表格通过产品ID关联。

现在可以说一下具体的任务了：需要新建一个表格订单成本表，用来统计每一个订单的成本或是计算产品毛利，这个功能由账务部门在本订单成本表的数据上来实现。主要的数据是来自于订单交易表和产品成本表，主要字段有订单数据，如订单日期，订单编号，产品名称，产品编号，，交易金额，产品成本等信息。根据订单日期，需要匹配到当时下单时的最新产品成本，如何解决产品出现新的成本后不会影响到原有的成本计算。当订单交易表和产品成本表中有数据插入，修改，更新后如何让这个订单成本表可以自动更新。

第一节数据说明

由于朋友没有提供具体的数据，这个也是常见现象，毕竟现在大数据时代，只要根据一点点的蛛丝马迹就有可能泄露公司的机密信息。因此为了解决此问题，只能自己根据朋友的任务需求，编造一些数据来实现此功能。

针对订单交易表，根据要求，设计了如下字段：订单日期，产品名称，产品ID和金额。为了更好的模拟功能，输入了一些模拟数据如下：

针对产品成本表，设计了字段：添加日期，产品名称，产品ID，成本。针对这两个表，输入了一些模拟的数据如下：

从产品成本表上，我们可以看到同一个产品编号有多个不同的产品成本。由于原始数据是不可以直接进行修改的，多数情况是直接从系统里面下载下来或是别的部门提供的，因此对原始数据我们只能加载到Power Query中进行处理，这样就不会影响原始数据，而且下次拿到更新后的数据，我们直接进行刷新就可以得到我们想要的数据，不需要再次修改原始数据。这也是PowerQuery的功能强大之处。接下来我们需要把数据做一下简单的清洗，以确保数据符合要求。

第二节数据清洗

由于是做功能演示，我们将两个表格是放在同一个EXCEL工作簿中，当然实际工作中可能是放在不同的文件中，甚至是不同的文件夹下面，但是导入数据不是我们今天要演示的操作，所以在此不做过多的描述。我们直接将这EXCEL文件加入到POWER Query查询中：

在出现的界面中选择浏览到EXCEL文件所在的位置并选择此excel文件，接下来的界面中勾选如下所示的几个项目：

点击”转换数据“就进入到PowerQuery的查询编辑界面。我们看到订单交易表和产品成本表都已经加载进来了，如下图：

接下来我们分别选择这两个表，做一下简单的数据清洗如删除空行，删除重复行等。

产品成本如果为空的话要替换为0.

接下来需要对订单交易表和产品成本表中的日期列进行一下排序处理，确保数据是有序排列。产品成本表中的添加日期列进行排序操作如下，订单交易表操作类似：

这样下来我们的数据符合要求了，接下将对数据进行处理，以实现我们需要的功能。

第三节功能实现（方法一）

根据要求我们要有一个新的表订单成本表来获取相应的数据，在PowerQuery编辑界面的Home主页选项卡中选择”合并查询“的下拉列表，选择”将查询合并为新查询“：

第一个表选择框中我们选择订单交易表，第二个表选择框中我们点下拉列表框，选择产品成本表。匹配列我们分别选择两个表的”产品ID“列，点击这两个列就可以选择好。由于我们的数据都是来自订单交易表且产品成本来自于产品成本表，所以联接种类下拉列表框中选择第一项，左外部连接。最后点确定返回。

在返回的界面中我们将查询名称修改为订单成本，如下图我们可以看到产品成本表中的记录作为一个表格加入到每一个交易记录，这是因为产品成本表中有多个匹配的行。

在订单交易表中，一个产品ID会对应的在产品成品表中发现有多个匹配的产品成本记录，因为订单日期下单时只能是近最近的产品成本来核算其产品成本，而不能是更早的也不可能是以后的产品成本。所以我们只能选择留下最近的产品成本这一条记录。接下来就是PowerQuery的M函数上场了。

为了展示这个实现功能的解决方法，在此一步一步作解释。

第一步：在订单交易查询的表中增加一列，这一字段名：订单产品成本。为了获取产品成本表中的产品成本，首先需要用订单日期和产品成本表中的添加日期作比较，找到所有添加日期小于订单日期的产品成本记录，由于订单日期之后的产品成本从理论上来说是不现实的，下订单时不可能知道后面的产品成本。因此第一步是找到所有产品成本中添加日期小于等于订单日期的记录。我们用Table.SelectRows函数获取符合条件的数据。

此处有用到PowerQuery里面的上下文功能，其功能类似于钻取。我们在订单查询表中还有一个列（产品成本表）的记录是一个独立的表，也就是表中有表，我们需要把外部表（查询表）中的订单日期带入到表（记录表）中作为条件进行判断。这样我们就得到了所有添加日期小于等于订单日期的记录，这个同样是一个表。接下来我们只需要找到这个表中的添加日期的最大值，就是越靠近订单日期的添加日期，此时Table.Max()就出山场了。我们取最大添加日期得到了一条记录，如下图：