如何让Excel识别套装内商品并扣减对应库存数量？

如图所示，SUMIF公式无法从套装的销售中扣减对应SKU的库存。

假设套装A包含商品A100、B100和C100，现有三张表格：

• 套装表：列出各套装及其包含的商品与对应数量
• 库存表：记录各商品的剩余库存数量
• 销售表：记录单个商品及套装的销售情况

目标是计算各商品的期末库存：普通商品销售只需用期初库存减去SUMIF(商品SKU区域,商品SKU,销售数量)即可，但套装销售时，需要让Excel识别套装包含的SKU，并分别扣减对应商品的库存。

试过用Vlookup，但只能返回单个值；也试过Filter函数，但不知道如何和期初库存关联计算，现在无从下手。

可以通过组合公式实现套装库存的自动扣减，分兼容全版本和动态数组简化版两种方案：

方法1：兼容所有Excel版本（SUMPRODUCT+SUMIFS） #

假设表格区域定义：

• 库存表：A列=SKU，B列=期初库存
• 销售表：A列=销售SKU（含单品/套装），B列=销售数量
• 套装表：A列=套装SKU，B列=包含的商品SKU，C列=对应数量

在库存表的C列（期末库存）输入公式：

=B2 - SUMIFS(销售表!$B:$B,销售表!$A:$A,A2) - SUMPRODUCT(套装表!$C:$C, SUMIFS(销售表!$B:$B,销售表!$A:$A,套装表!$A:$A), --(套装表!$B:$B=A2))

公式逻辑：

1. SUMIFS(销售表!$B:$B,销售表!$A:$A,A2)：计算当前SKU的单品销售总扣减
2. SUMPRODUCT(...)：计算当前SKU因套装销售产生的总扣减
   • 先算出每个套装的销售总量，再乘以套装中该SKU的包含数量，最后求和得到总扣减数

方法2：Excel 365/2021 动态数组简化版 #

用SUM+XLOOKUP+FILTER组合，公式更直观：

=B2 - SUMIF(销售表!$A:$A,A2,销售表!$B:$B) - SUM(FILTER(套装表!$C:$C,套装表!$B:$B=A2)*XLOOKUP(套装表!$A:$A,销售表!$A:$A,销售表!$B:$B,0))

公式逻辑：

1. 前半部分计算单品销售扣减
2. FILTER(套装表!$C:$C,套装表!$B:$B=A2)：筛选当前SKU在所有套装里的包含数量
3. XLOOKUP(...)：匹配每个对应套装的销售总量
4. 两者相乘后求和，得到套装销售带来的该SKU总扣减

注意事项 #

• 确保所有表格的SKU格式统一（无空格、大小写一致）
• 尽量用实际数据区域替代整列引用（比如$A$2:$A$100代替$A:$A），提升计算速度

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