Excel中如何按行递减SKU对应采购量并匹配供应商成本核算利润

前置准备 #

• 先整理2张基础表，提前做好排序：
  1. 销售订单表：同SKU的订单按销售时间升序排列，确保先成交的订单排在前面，至少保留3列：SKU、单订单销售数量、累计销量（同SKU从第一笔到当前订单的销量总和）
  2. 供应商采购批次表：同SKU的采购批次按采购时间升序排列，确保先采购的批次排在前面，至少保留5列：SKU、供应商名称、单位采购成本、单次采购量、批次累计采购量（同SKU从第一个批次到当前批次的采购总量）

公式配置 #

累计值计算 #

1. 销售订单表累计销量：假设SKU在A列、单订单销量在B列、累计销量在C列，C2单元格输入公式=IF(A2=A1,C1+B2,B2)，下拉填充即可
2. 采购批次表累计采购量：假设采购表放在Sheet2，SKU在A列、单次采购量在D列、累计采购量在E列，E2单元格输入公式=IF(A2=A1,E1+D2,D2)，下拉填充即可

成本匹配公式 #

Excel 365/2021及以上版本（支持XLOOKUP）

回到销售订单表，要放匹配成本的列（假设是D列），D2输入公式：
=XLOOKUP(1,(Sheet2!A:A=A2)*(Sheet2!E:E>=C2),Sheet2!C:C,"采购量不足")
下拉填充即可完成成本匹配，如果要同步匹配供应商名称，把公式中Sheet2!C:C换成采购表中供应商名称所在列即可。

旧版本Excel

使用INDEX+MATCH数组公式，D2输入：
=IFERROR(INDEX(Sheet2!C:C,MATCH(1,(Sheet2!A:A=A2)*(Sheet2!E:E>=C2),0)),"采购量不足")
输入完成后按下Ctrl+Shift+Enter触发数组计算，再下拉填充即可。

注意事项 #

• 如果SKU的总销量超过所有采购批次的总采购量，公式会返回预设的「采购量不足」提示，你可以根据需要调整提示内容
• 排序是核心前提，如果订单或采购批次的顺序错了，匹配结果会完全不符合先进先出逻辑

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