Excel中基于批次码动态更新数组QTY的技术求助

数据结构说明 #

• 生产数组（Production JP37表）：包含字段依次为 Date、Product Code、Product Description、Location Produced、Batch Code、QTY（后续4列保留原结构）
• 内部转移数组（Internal Transfer Of Goods表）：在生产数组字段基础上，额外包含 FROM、TO 字段，用于记录库存转移方向

核心逻辑 #

按相同Batch Code匹配生产数据与转移数据：

• 若转移记录的TO为JP37，对应批次QTY加转移数量
• 若转移记录的FROM为JP37，对应批次QTY减转移数量

动态数组公式（适用于Excel 365/2021） #

用LET函数简化逻辑，直接生成完整的更新后数组：

=LET(
    // 提取JP37的生产基础数据（替代原IF公式，仅保留有效行）
    ProdData, FILTER('Production JP37'!A5:J600, 'Production JP37'!D5:D600="JP37"),
    // 提取生产数据中的Batch Code列（第5列）
    ProdBatches, INDEX(ProdData, ,5),
    // 计算每个批次的净转移调整量
    TransferAdjust, 
        SUMIFS('Internal Transfer Of Goods'!K5:K600, 'Internal Transfer Of Goods'!E5:E600, "JP37", 'Internal Transfer Of Goods'!M5:M600, ProdBatches)
        - SUMIFS('Internal Transfer Of Goods'!K5:K600, 'Internal Transfer Of Goods'!E5:E600, "JP37", 'Internal Transfer Of Goods'!L5:L600, ProdBatches),
    // 拼接调整后的完整数组
    RESULT, HSTACK(INDEX(ProdData, ,1:5), INDEX(ProdData, ,6)+TransferAdjust, INDEX(ProdData, ,7:10)),
    RESULT
)

注：公式中K为转移表的QTY列，L为FROM列，M为TO列，需根据实际表结构调整列号

兼容旧版Excel的公式 #

若无法使用动态数组函数，可在生产数组的QTY单元格中输入以下公式，下拉填充至所有行：

=INDEX('Production JP37'!F5:F600, ROW()-4) 
+ SUMIFS('Internal Transfer Of Goods'!K5:K600, 'Internal Transfer Of Goods'!E5:E600, "JP37", 'Internal Transfer Of Goods'!M5:M600, INDEX('Production JP37'!E5:E600, ROW()-4))
- SUMIFS('Internal Transfer Of Goods'!K5:K600, 'Internal Transfer Of Goods'!E5:E600, "JP37", 'Internal Transfer Of Goods'!L5:L600, INDEX('Production JP37'!E5:E600, ROW()-4))

注：ROW()-4对应生产数据从第5行开始，若起始行不同需修改偏移值

内容的提问来源于stack exchange，提问作者Jonathan Marshall