定制化库龄分析报表提升仓储效率与决策精准度-2

职场计划有古哥 2024-12-11 06:00:06

全文约1400字

大家好,我是古老师。在昨天的课程中,我们完成了第一章的内容,对《现存量》报表里物料库龄超过60天的数据进行了分析。其原理是通过对比《采购入库》报表来实现的,因为那份报表仅包含最近60天的物料入库记录。通过将现存量与这60天内的入库数量进行比对,我们可以确定哪些物料的库龄已超过60天。

今天,我将继续分享《定制化库龄分析报表》第二章的内容,这次我们将重点关注60天内入库的物料,并对其进行详细的库龄分析。

辅助入库序号

为了确保每次入库都有一个唯一的编号,我们针对每一个物料编码,在《采购入库》表中新增了一列作为辅助序号列,用于记录每个物料编码的入库顺序。例如,当某个物料第一次入库时,会生成数字1;随着该物料的多次入库,相应的序号将依次递增为2、3等。

为了实现这一功能,我们在表格中录入了动态数组公式:

=SCAN(0,H2#,LAMBDA(X,Y,IF(Y<>OFFSET(Y,-1,),1,X+1)))

公式解释:

SCAN 函数用于累积计算,并返回一个数组。

0 是初始值,表示在序列开始之前没有之前的值(即起始计数为0)。

H2# 指的是从H2单元格开始的数据范围,这里假设是物料编码的列表。

LAMBDA(X, Y, IF(Y<>OFFSET(Y, -1, 0), 1, X+1)) 是一个匿名函数,其中 X 表示累计的结果,而 Y 表示当前处理的值(即当前行的物料编码)。如果当前行的物料编码与上一行不同,则重置计数为1;否则,基于前一次的累计结果加1。

效果如下图所示:

累计入库数

在分析库龄之前,我们需要对入库数量进行累计计算,以支持库存分配的运算。为此,我们将新增一列,并将其命名为“累计入库”。然后,在该列中录入以下动态数组公式:

=SCAN(0,I2#,LAMBDA(X,Y,LET(A,OFFSET(Y,,1),IF(Y=OFFSET(Y,-1,),X+A,A))))

公式解释:

I2# 是物料编码所在的列,而其右侧相邻的一列(即 OFFSET(Y, 0, 1))代表每次入库的数量。

SCAN 函数用于累积计算并返回一个数组结果。

0 是初始值,表示累计开始前的起始数值为零。

LAMBDA(X, Y, LET(A, OFFSET(Y, 0, 1), IF(Y = OFFSET(Y, -1, 0), X + A, A))) 是一个匿名函数,其中 X 表示累计的结果,Y 表示当前行的物料编码。

具体来说,这个公式的逻辑是:如果当前行的物料编码与上一行相同,则将之前的累计值 X 和当前行的入库数量 A 相加;如果不同,则将当前行的入库数量 A 作为新的累计起始值。这样做可以确保每个物料编码对应的累计入库数量是准确的,从而为后续的库龄分析提供可靠的数据基础

库存分析运算

我们首先新建一列辅助列,并将其命名为“库存-累计”,用于计算每个物料的当前库存数量减去累计入库数量。在该列中录入以下公式:

N2=P2#-M2#,其中,P2#为库存数量,M2#为累计入库数量。

接下来,我们再新增一列,并将其命名为“分配数”。这一列的目的是根据现有库存对不同批次的入库记录进行分配。例如,如果某物料A有300单位的库存,而入库记录分别为100、100和50单位,则这些记录将被完全分配,剩余的50单位则被认为是超过60天库龄的库存。

为此,在“分配数”列中录入如下动态数组公式:

=LET(J,J2#,N,N2#,IF(N>=0,J,IF(J>ABS(N),J+N,0)))

公式解释:

J 列代表入库明细(即每次入库的数量)。

N 列是之前计算出的“库存-累计”值,表示库存减去累计入库后的剩余量。

如果 N 大于或等于0,表示当前库存足够覆盖此次入库记录,则返回 J(即本次入库的数量)。

如果 N 小于0,说明当前库存不足以覆盖此次入库记录:

如果入库数量 J 大于 N 的绝对值,表示部分可以分配,则返回 J + N(即分配完之后剩余的负库存量)。

否则,如果 J 不大于 N 的绝对值,则返回0,意味着没有足够的库存来分配这次入库记录

到这里,我们已经完成了60天内库龄分析中最为关键的库存数量分配。接下来,在第三章中,我将对这些已分配数量的物料进行库龄金额分析。请继续关注古老师的《定制化库龄分析》系列报表,了解更多详细内容。

0 阅读:1