全文约1900字

大家好，我是古老师。今天，我将介绍一个专门为仓库管理设计的库龄分析报表。这个报表的设计源于某工厂仓库主管的需求。由于他们的ERP软件未包含WMS系统中的物料库龄分析模块，领导要求对仓库中的物料进行库龄分析。面对这一任务，仓库主管感到无从下手，因此向我寻求帮助，希望我能设计一份表格形式的全自动库龄分析报表。

接受到这个请求后，我马上与仓库主管进行了沟通，明确了具体需求，并向他获取了两份关键报表：《仓库现存量明细表》和《采购入库明细表》。在掌握了这些信息之后，我便着手开始设计这份自动化库龄分析报表。

为了分析仓库中资材的库龄，我们需要从ERP系统导出最新的《仓库现存量报表》。这份报表记录了仓库中所有物料编码对应的库存数量、单价和总金额。数据格式为标准的一维表格，A到D列分别表示：“存货编码”、“库存数量”、“单价”和“总金额”。

为了方便后续使用动态数组公式进行引用，在A到D列旁边，我们将创建等量的动态数组公式列，以便于处理和分析数据。具体来说，我们将在F到I列录入以下公式：

标题行（F1:I1）：=A1:D1

F2（存货编码动态数组）：=TOCOL(A2:A10000,1)

G2（库存数量动态数组）：=TOCOL(B2:B10000,1)

H2（单价动态数组）：=TOCOL(C2:C10000,1)

I2（总金额动态数组）：=TOCOL(D2:D10000,1)

这样设置后，每当更新《仓库现存量报表》时，动态数组将自动调整以反映最新的库存信息，确保库龄分析的准确性。

效果如下图所示：

为了进行物料库龄分析，我们需要设定一个时间标准来判断库存物料的存放时长。在这个例子中，我们将基于最近60天内的采购入库记录进行分析。具体来说，我们从ERP系统导出最近60天的《采购入库记录明细表》，用以确定每种物料的库龄。例如，如果物料A当前库存为300件，而在12月1日有100件入库，则可以推断至少有200件的库龄超过60天，而最新入库的100件的库龄为8天（截至12月9日）。

因此，我们可以从ERP系统导出《采购入库明细表》并复制到工作表中，命名为《2. 采购入库》。这个表格采用标准的一维数据结构，其中A1:E1单元格分别对应标题：“入库日期”、“存货编码”、“入库数量”、“单价”和“总金额”，其下则为具体的记录数据。

接下来，为了方便后续的库龄分析，需要对这些数据进行整理和清洗。以下是用于创建动态引用列的公式，以便于处理和分析。

G1=A1:E1,引用对应标题

G2=INDEX(SORT(WRAPROWS(TOCOL(A2:E30000,3),5),2),,1)

H2=INDEX(SORT(WRAPROWS(TOCOL(A2:E30000,3),5),2),,2)

I2=INDEX(SORT(WRAPROWS(TOCOL(A2:E30000,3),5),2),,3)

J2=INDEX(SORT(WRAPROWS(TOCOL(A2:E30000,3),5),2),,4)

K2=INDEX(SORT(WRAPROWS(TOCOL(A2:E30000,3),5),2),,5)

公式解释：

先通过TOCOL函数把多列转换在一列（二维转一维），再用参数3屏蔽预留范围（A2:E30000）中的空值，接着用WRAPROWS函数转换成5列，把一列转成多列（一维转二维）。

继续用SORT函数对这个二维数据进行按存货编码（第2列）进行升序排序，最后用INDEX函数把这个二维数据进行分拆成单列的数据，以方便后续动态引用。

在对《1. 现存量》表和《2. 采购入库》表进行了基本的整理与清洗后，我们可以着手设计全自动库龄分析报表了。首先，我们需要判断哪些物料的库龄超过了60天。为此，在进行判断之前，我们应在《2. 采购入库》表中引用《1. 现存量》表中对应存货编码的库存数量。可以通过录入以下公式来实现这一索引：

L2=XLOOKUP(H2#,'1.现存量'!F2#,'1.现存量'!G2#)

公式解释：

此公式使用了XLOOKUP函数，它会在《1. 现存量》表的F列（即“存货编码”）中查找与《2. 采购入库》表H列中的存货编码相匹配的值，并返回《1. 现存量》表G列（即“库存数量”）中对应的库存数量。

在引用了库存数量之后，我们需要汇总《2. 采购入库》表中相同存货编码的采购入库数据。由于入库数据量超过10000行，使用传统的SUMIFS函数进行汇总可能会导致运算效率低下。因此，这里我们采用聚合函数结合引用函数的方式来提高效率。可以录入以下动态数组公式来实现这一目的：

M2=VLOOKUP(H2#,GROUPBY(H2#,I2#,SUM),2,0)

公式解释：

GROUPBY这部分公式将根据《2. 采购入库》表中的“存货编码”（H列）对“入库数量”（I列）进行分组，并计算每个存货编码对应的总入库数量。

VLOOKUP函数，在由GROUPBY生成的汇总结果中查找与当前行存货编码相匹配的记录，并返回第二列（即汇总后的入库数量）的值。

有了库存数量和累计采购入库数量后，我们就可以快速判断库龄超过60天的物料数量了。可以录入以下公式来实现这一目的：：

=LET(A,L2#-M2#,IF(A<0,0,A))

公式解释：

LET 函数用于定义可重用的名称或计算结果，以简化复杂公式的编写。

在这里，LET 定义了一个名为 A 的变量，其值为 L2#（引用的库存数量）减去 M2#（累计采购入库数量）的结果。

接着，使用 IF 函数检查 A 是否小于 0；如果是，则返回 0（表示没有超过60天库龄的库存），否则返回 A 的值（即超过60天库龄的库存数量）。

这样，通过这个公式，我们可以准确地计算出每个存货编码下库龄超过60天的库存数量，从而更有效地进行库龄分析。

到这里，第一章的内容基本完成。明天我将继续设计第二章的内容，重点将放在分析60天内的库龄上。请大家持续关注古老师的系列文章，获取更多更新和深入的分析。