PMC精益库存管理与订单分配自动化设计WPS一站式报表模板

2024年03月优秀创作者

在PMC（生产计划控制）的日常工作中，存在这样一个场景：当接到客户订单后，首先按照客户下单的顺序制作《订单明细表》；随后，根据仓库实际的产品入库数量，编制《产品入库明细表》。接着，我们将依据产品入库的数量，并结合订单下达的顺序，来核查现有库存是否足以满足订单需求。若数量满足，则可以进行出货安排。

在执行数量分配核查的过程中，需要遵循两个原则：

首先，遵循“先下单、先分配”的原则，即按照下单日期的先后顺序进行分配，越早下单的订单优先获得数量分配。

其次，在同一天内下达的包含相同产品的多个订单中，优先分配给订购数量较少的订单批次。

模拟数据如附图所示，其中表1名为《订单明细表》，记录了客户下达的不同订单，对于同一产品可能存在多个订单编号，这表示该产品需分批进行出货。而表2为《产品入库明细表》，详实地记载了不同日期下的各类产品入库数量。

现期望设计一款WPS报表模板，能够实现一键计算并确定哪些订单已具备完整发货条件（即齐套订单）。

需求分析

根据上述需求阐述，我们可以明确识别出两个核心策略：一是遵循“先下单先分配”原则，二是同一天内的订单优先分配数量较小的产品批次。为了落实这两个原则，我们需要首先对表1《订单明细表》中的数据进行整理操作，具体而言，利用排序功能，将相同产品的订单按下单日期由早到晚进行升序排列，并同时考虑每个订单的产品数量大小进行进一步的排序处理。

完成排序之后，接下来是对库存进行相应的扣减操作。由于库存系统中，同一种产品可能有多次不同的入库记录，因此，在此步骤中，需要将相同产品的、具有不同入库日期的数量进行整合汇总，以计算出每种产品的累计总库存量。

最终，通过将计算得到的每种产品的总库存数量减去相应订单所需的产品数量，若结果为负值，则表明该订单的产品数量尚未齐套，无法满足发货要求。

总结来说，为了实现这个WPS报表模板的设计目标，我们将会运用到一系列函数，包括但不限于排序函数（用于订单日期和数量的排序）、求和或汇总函数（用于统计产品的入库总量）、以及逻辑判断函数（用于判断订单是否满足发货条件）。

排序数据

由于需要按照特定顺序对产品进行扣减操作，此处需借助排序函数进行数据排序，设定三个排序条件，依次为：日期、产品名称和数量。排序的顺序至关重要，必须确保首先按照产品名称排序，其次按照日期升序排列，最后依据数量进行排序。这里可以采用多条件排序函数SORTBY来处理：

录入公式：

=SORTBY(B3:E11,D3:D11,1,B3:B11,1,E3:E11,1)

效果如下图所示：

实际上，尽管多条件排序通常更为直观且适用面广，但在某些情况下，也可使用单条件筛选函数逐一进行排序以减少对数据区域的重复引用次数。

录入公式：

=SORT(B3:E11,{3,1,4})

函数释义：

分别对第3列产品、第1列日期、第4列数量进行排序；效果如下图所示：

库存信息

在完成对产品的排序之后，紧接着我们需要引入产品的库存信息，以便于进行订单需求量与入库数量之间的对比分析与判断。在录入前把上面排序的数组区域用选择列函数转成单列后再引用

录入函数：

日期=CHOOSECOLS(SORT(B3:E11,{3,1,4}),1)

订单=CHOOSECOLS(SORT(B3:E11,{3,1,4}),2)

产品=CHOOSECOLS(SORT(B3:E11,{3,1,4}),3)

数量=CHOOSECOLS(SORT(B3:E11,{3,1,4}),4)

边上新建一列库存，引用表2的产品入库信息；

录入以下函数：

=SUMIFS(I3:I6,H3:H6,M3#)

函数释义：

在表2中，针对产品进行基于条件的求和操作时，请注意“M3#”代表的是一列数据，这一列是由上方的选择列函数动态生成的，体现了WPS动态数组的应用方式。

效果如下图所示：

累计订单

如果直接在这里采用库存总量一次性减去订单数量的做法，将会导致问题出现，因为对于同一产品的库存数据反映的是该产品的汇总库存总量，而订单需求却分布在不同行中。因此，逐行将库存总量减去每条订单的订购数量显然不合逻辑。正确的方法应该是从库存总量中减去所有相关订单的累计需求量。

输入累计订单的公式：

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

函数释义：

确保订单数量与产品记录相邻，在处理过程中，针对产品的每一条记录，可以通过比较其上下两行的产品是否相同来决定数量如何处理：若产品相同，则将数量相加，以实现累加效果；若产品不相同，则保留当前行的数量不变。这样就能逐步积累出每种产品的订单累计需求量。

效果如下图所示：

判断齐套

首先，通过将库存数量减去累计订单数量，得出的结果可能是正数、零或负数。如果得出的结果是正数或零，这意味着库存数量足以分配给对应的订单，此时可直接返回订单数量作为可分配数量。

若计算结果为负数，则取其绝对值，并与订单数量进行比较。如果这个绝对值小于订单数量，那么将订单数量与之前的判断结果（即负数）相加，作为暂时无法完全满足的订单部分。

如果这个绝对值大于订单数量，则表示库存已经分配完了，所以返回0；

对应的IF判断条件如下：

=IF(Q3#>0,N3#,IF(ABS(Q3#)<N3#,N3#+Q3#,0))

最后，在每次分配完成后，加入剩余库存的实时更新提示，这样便能动态展示每次分配后仍剩余多少库存量。这样一来，当PMC生产计划部门面对销售团队查询产品库存情况时，就能够迅速且准确地回应，提高工作效率。

一键公式：

若追求极致简化，可以省略辅助列的判断信息，仅通过一个公式直接得到预期结果。

录入以下公式：

=LET(g,UNIQUE(D4:D12),REDUCE({"日期","订单","产品","订单数量","分配"},g,LAMBDA(X,Y,VSTACK(X,LET(k,SORT(FILTER(B4:E12,D4:D12=Y),{3,1,4}),t,TAKE(k,,-1),HSTACK(k,DDB(t,DDB(SCAN(,t,SUM),SUMIF(H4:H7,Y,I4:I7),1,1),1,1)))))))