用Excel做一个真正能用的进销存:多物料、月末一次加权平均法

一个事实：很多中小型企业，并没有购买专业的进销存软件，而是使用Excel来计算进销存，这算是一个经典的题材，但是大部分教程通常存在以下的问题：

1. 逃避问题，一个月用一个工作簿，不能合并在一起
2. 逃避问题，只计算数量不计算金额
3. 计算发出存货成本的方式不够智能，需要手工维护

这篇文章旨在解决以上的问题，提出一种通用的进销存解决方案

请使用Microsoft 365或2021以上版本

本文使用月末一次加权平均法计算发出存货成本

数据准备

请注意，这里只使用了必要的字段，如有需要可以增加，但是有可能需要改公式

物料表，必要字段是物料编码和物料名称 

日期表，每个月向下拉一次即可，用EOMONTH函数返回上一行日期的下个月的最后一天 

入库明细表，必要字段是日期，物料编码，数量，单价；物料名称使用vlookup从物料表查询而来，入库金额使用数量乘以单价计算而来

出库明细表，必要字段是日期，物料编码，数量 

计算思路

一个正常的进销存表应该是什么样子的？字段大概是物料编码、物料名称、期初数量、期初单价、期初金额、入库数量、入库单价、入库金额、出库数量、出库单价、出库金额、期末数量、期末单价、期末金额。

其实大部分都好解决 一个物料在最开始是没有期初的，期初就是上个月的期末，所以期初可以舍去

然后是入库数量、入库单价、入库金额、出库数量，这四个可以从入库明细表和出库明细表中取得

出库金额等于出库数量乘以出库单价 期末数量等于该物料从一开始的所有入库数量减所有出库数量 期末单价等于出库单价 期末金额等于期末数量乘以期末单价

现在只剩下一个了，那就是出库单价

月末一次加权平均法的发出存货单价 = （期初金额 + 入库金额）/（期初数量 + 入库数量），这是我们都知道的

现在我们假设只有一种存货，做一个进销存出来

大概长这个样子，没有用的期初单价、出库金额、期末单价我没有列上，只保留必要字段 

获取所有期间，注意最上面空出来一行，因为期初等于上一期期末，留着一行好做公式 

公式=日期表[日期]

期初数量和金额，取第一行的期末，相对引用，这样往下拽公式之后就对劲了 

期初数量公式=H5

期初金额公式=I5

入库数量和入库金额，使用SUMIFS从入库明细表中取得 

入库数量公式=SUMIFS(入库明细[数量],入库明细[物料编码],$A$1,入库明细[日期],"<="&A6,入库明细[日期],">"&EOMONTH(A6,-1))入库金额公式=SUMIFS(入库明细[入库金额],入库明细[物料编码],$A$1,入库明细[日期],"<="&A6,入库明细[日期],">"&EOMONTH(A6,-1))

日期这里，由于会计期间是最后一天，但是入库在哪一天都可能发生，所以取小于等于最后一天，大于上个月最后一天之间的所有入库 

出库数量，用同样的办法取得 

出库数量公式=SUMIFS(出库明细[数量],出库明细[物料编码],$A$1,出库明细[出库日期],"<="&A6,出库明细[出库日期],">"&EOMONTH(A6,-1))

出库单价 = （期初金额 + 入库金额）/（期初数量 + 入库数量） 

注意使用IFERROR去掉除数为零的影响 

并不是每行都有出库单价？没事儿，一会就对劲了

期末数量 = 期初数量 + 入库数量 – 出库数量 

期末金额 = 期末数量 × 出库单价 

现在出库单价显示全了

好了，只有一种存货的情况已经演示完了，非常简单

多种存货的计算思路

事实是，每个企业都不会只有一种存货的，我们不能逃避问题

首先是最简单的一种：Excel反正有这么多行，给每种存货预留出来一定的行就行了，比如我预计公司能活五年，那每种存货我就预留出来5 × 12 = 60行就行了，Excel有104万行，够17476种物料用的，太充裕了！要是一个公司活了五年还用Excel做进销存，那真是没谁了！

就像这样，你也可以增加一列把物料编码放进去，这样复制起来更容易： 

更优雅的解法

如果你觉得上面的解法不够优雅，那么我这里抛砖引玉，给出一种解法

首先，我们要解决一个问题，如何把所有编码所有会计期间做成两列，我的解法是用笛卡尔积

在Excel中，一个横向一维数组乘以一个纵向一维数据，就会返回它们之间的所有排列组合，也就是笛卡尔积，像是这样 

公式：=B1:D1&A2:A4

但是我们的物料列和期间列都是纵向的，怎么办？

把其中一个变成横的就好了，用TOROW函数 就像这样

公式=TOROW(日期表[日期])

然后，再连接上物料列，就变成了这样

公式=TOROW(日期表[日期]) & 物料[物料编码]

得到的是一个矩阵，再嵌套一层TOCOL函数，变成列就好了 

公式=TOCOL(TOROW(日期表[日期]) & 物料[物料编码])

现在期间和物料编码紧紧的连在一起，这样后续不好做公式，因为公式里面需要分别引用这两个字段，所以需要分开它们，日期是固定的五位，可以利用这个特点拆分 

公式=LEFT(TOCOL(TOROW(日期表[日期]) & 物料[物料编码]),5)*1

后面的乘以1，是因为LEFT拆分出来的会变成文本，乘以1就变成数字了，只有数字才能转化为日期 

同样的道理，把物料编码拆分出来，用MID函数从第六位开始取即可 

公式=MID(TOCOL(TOROW(日期表[日期]) & 物料[物料编码]),6,99)

最上面添加一行，用来放列名，然后开始一列列做公式，这次的列做的比较全

首先，是可以从明细表直接取数的入库数量，入库金额，出库数量，公式和上面一样，这里不再赘述

然后是可以计算出来的，包括以下几列： 

物料名称，使用VLOOKUP函数从物料表中查找引用 

期初单价 = 期初金额 / 期初数量，注意除数为零的情况，下面不再赘述 

入库单价 = 入库金额 / 入库数量，注意这里的单价是该物料在该期间的平均单价，只能这么算出来，不能从入库明细表中取 

出库单价 = （期初金额 + 入库金额）/（期初数量 + 入库数量） 

出库金额 = 出库数量 × 出库单价 

期末单价 = 出库单价 

期末金额 = 期末数量 × 期末单价，当然，乘以出库单价也是一样的

好了，现在是真正体现技术的时候了，还差期初数量，期初金额，期末数量三列

注意，这时候期初就不能像一种存货的例子那样，直接引用上个月的期末了，因为现在期间和物料编码列是动态数组，没办法准确引用到那一行，而且，也没法保证整列的公式一致

回想一下，期初是什么？是上个月的期末，期末数就在表里，用统一的这个规律取就好了！

对期间列用EOMONTH函数，就能得到上个月的期末值，再使用SUMIFS函数，以物料编码和上个月的期末值为条件，就可以取到上个月的期末数量金额了 

期初数量 =SUMIFS(M:M,B:B,B2,A:A,EOMONTH(A2,-1))

期初金额 =SUMIFS(O:O,B:B,B2,A:A,EOMONTH(A2,-1))

现在还没有数，一会就有了

现在还有最后一列：期末数量

需要注意的是，期末数量不可以用 期末数量 = 期初数量 + 入库数量 - 出库数量这个公式了，因为期初取自期末，如果期末这里又引用了期初，就会造成循环引用，因此，我们需要另一种方式来计算期末数量

也就是我前面说过的：物料从一开始到现在的累计入库减去累计出库，就是期末数量 

期末数量 =SUMIFS(入库明细[数量],入库明细[物料编码],B2,入库明细[日期],"<="&A2)-SUMIFS(出库明细[数量],出库明细[物料编码],B2,出库明细[出库日期],"<="&A2)

以物料编码作为条件，通过“小于等于”期间，得到累计入库出库数量

好的，这样就完成了，每个新的月份，将日期表下拉一行，然后将进销存表下拉到和AB两列同一行就可以了，当然，也可以一次拉一年的。之后正常维护物料表和入库出库表，进销存即可正常计算。

享受所有数据都在一起的便捷吧！