如何在Excel中确定最优批量采购订单组合

核心逻辑 #

因为选项B的单位采购成本（约6.67$/件）远低于选项A（16$/件），最优解仅需要对比两种场景的总花费即可，无需复杂嵌套判断：

• 场景1：采购最大可覆盖需求的整数份选项B，剩余不足30件的部分用选项A补齐
• 场景2：比场景1多采购1份选项B，无需采购选项A
  取两种场景中总花费更低的组合即可覆盖所有需求情况。

预设前提 #

假设所需采购的总件数录入在单元格D2中，固定采购规则如下：

• 选项A：5件/份，单价80$
• 选项B：30件/份，单价200$

具体公式 #

计算最优选项B采购份数 #

=IF((FLOOR(D2/30,1)*200 + CEILING(MAX(D2-FLOOR(D2/30,1)*30,0)/5,1)*80) < (FLOOR(D2/30,1)+1)*200, FLOOR(D2/30,1), FLOOR(D2/30,1)+1)

计算最优选项A采购份数 #

=IF((FLOOR(D2/30,1)*200 + CEILING(MAX(D2-FLOOR(D2/30,1)*30,0)/5,1)*80) < (FLOOR(D2/30,1)+1)*200, CEILING(MAX(D2-FLOOR(D2/30,1)*30,0)/5,1), 0)

计算最低总采购成本 #

=MIN(FLOOR(D2/30,1)*200 + CEILING(MAX(D2-FLOOR(D2/30,1)*30,0)/5,1)*80, (FLOOR(D2/30,1)+1)*200)

效果验证 #

以需求34件为例：

• 场景1总花费：1份B（200$） + 1份A（80$）= 280$
• 场景2总花费：2份B = 400$
  公式自动选择场景1，输出B份数1、A份数1，符合预期。

以需求28件为例：

• 场景1总花费：6份A = 480$
• 场景2总花费：1份B = 200$
  公式自动选择场景2，输出B份数1、A份数0，符合最优解。

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