84 全自动《仓库进销存报表》-07

前面几天的文章，通过对仓库资材的入库、出库进行数据分析，并依次建立对应的图表模型,让数据可视化，并能够起到提醒预警的功能，仓库的管理资材还一项重要的指标就是，仓库资材的库龄分析。

仓库资材的库龄分析对于信息化软件来说非常简单，目前主流的信息化软件都可以实现精准的库龄分析，也可以看基于未来时间的库龄分析，但是对于一些没有上信息化软件的工厂，还在使用Excel登记资材管理的仓库，如何实现用Excel自动判断每一个物料对应库龄呢？

首先需要约定的一个事实就是，无论是信息化软件还是Excel，判断库龄都是默认“先进先出”的模式来判断。举例说明就是先入库的物料一定是先发出去的，当实际仓库中不是这样的操作，如后入库的先发料，就会造成仓库的实物库龄与账面上的库龄不一致。

基于“先进先出”的模式来设计Excel版本的库龄判断，需要做的第一步就是在表1.入库明细新增加一列，库龄天数判断，录入公式：=TODAY()-[@入库日期]，公式中出现“中文”，一般情况就是引用区域使用的是“表”（Ctrl+T）创建的。公式自动填充后，就可以看到每一个物料的入库时间；

到这一步，只是所有入库的物料的库龄天数，此时有些物料可能会领用，可能会剩下，所以需要过滤库存为0的物料，只分析有库存的，此时就会出现“一对多”的情况。举例说明：库存为1000，对应多个入库时间的批次，批次1，400，批次2，600等；

设计公式前先，手动计算某个物料的天数，如下图物料WL-03的当前库存是1623 个，按先进先出原则，入库日期前的全部都应该先发，所以只需要按入库日期降序排列，把1623个库存分别分配给到对应的入库数量，就可以得到对应剩下1623个库存分别对应哪个批次日期的入库单，手动分配的结果如下：

原理知道了，开始写公式了

表1：M2=IFERROR(VLOOKUP(D2,'4.现存量'!A:F,6,0),0),这是入库明细表中判断有无现存量物料的辅助列；有现存量的就返回大于0的数字，没有的就返回0；

新建表7库龄分析表,把表1的入库明细表标题复制过来，然后在A2输入公式：=FILTER(表1[[入库日期]:[现存量]],表1[现存量]0)，这步的目的就是把有库存的所有库明细通过筛选函数FILTER筛选到这里，筛选后发现数据是乱序的，没有办法执行按领料日期；

对于这样的数据，用函数加一层排序判断就可以了，录入函数=SORT(SORT(FILTER(表1[[入库日期]:[现存量]],表1[现存量]0),1,-1),2,1),这里需要特别注意的是，多次排序的判断。在此处如果用SORTBY判断的话，非常复杂，换个思路用回SORT判断，这样就简单而高效了；

到了这一步就可以在边上写自动判断扣减库存的函数了，为了方便大家学习，用辅助列分别写出，后续可以直接合并

判断1：=K2-SUMIFS($E$2:E2,$B$2:B2,B2)，用库存减去累计入库数量；

判断2：=IF(L2>0,E2,E2+L2)，如果是大于0，说明库存分配到这张单上的全部数量，小于0，就是相加，就是可以扣部分；

判断3：=IF(M2>0,M2,0)，如果大于0就是显示回判断2，否则显示为0

到这一步就可以看到每个物料的库龄了，但是这样分析起来不方便，需要做一个天数范围分析定义，如15天内，15-30天，30天到60天，60到90天，大于90天以上这样的范围，我们只需要提前录入好范围，再用VLOOKUP的模糊判断返回这个定义范围就可以了；

判断4：=IF(N2=0,0,VLOOKUP(J2,Q:R,2,1))

最后一步就是分析了,金额这一列，录入公式=SUMIFS(G:G,O:O,R2)，效果如下图

未完待续……

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！