寻求Excel产品优先占级的多阶梯定价单单元格公式解决方案

优先使用LET函数（Excel 365/2021及以上版本） #

这个方案通过LET定义中间变量，公式可读性强、易维护，完全替代冗长的嵌套IF：

=LET(
    A, X5, B, Y5,
    P1, D2, P2, D3, P3, D4, P4, D5,
    -- 计算产品A在各等级的分配量
    A1, MIN(A, 1000),
    A2, MAX(0, MIN(A - 1000, 1000)),
    A3, MAX(0, MIN(A - 2000, 1000)),
    A4, MAX(0, A - 3000),
    -- 计算各等级剩余额度（A占满后）
    Rem1, 1000 - A1,
    Rem2, 1000 - A2,
    Rem3, 1000 - A3,
    -- 计算产品B在各等级的分配量（优先填充剩余额度）
    B1, MIN(B, Rem1),
    B2, MIN(B - B1, Rem2),
    B3, MIN(B - B1 - B2, Rem3),
    B4, MAX(0, B - B1 - B2 - B3),
    -- 计算总结账金额
    (A1+B1)*P1 + (A2+B2)*P2 + (A3+B3)*P3 + (A4+B4)*P4
)

参数说明：

• X5：产品A的数量单元格，Y5：产品B的数量单元格
• D2~D5：对应4个等级的单价单元格，可根据实际位置调整
• 变量逻辑完全匹配需求：先填满A的各等级额度，再将B分配到剩余额度中，最后按等级求和

示例验证（A=1200，B=910）：

• A1=1000，A2=200，A3=0，A4=0
• Rem1=0，Rem2=800，Rem3=1000
• B1=0，B2=800，B3=110，B4=0
• 总金额 = 1000P1 + 1000P2 + 110*P3，与需求示例一致

旧版Excel兼容方案（无LET函数） #

如果使用不支持LET的旧版Excel，可将中间变量直接展开，公式如下（同样为单单元格）：

=(MIN(X5,1000)+MIN(Y5,1000-MIN(X5,1000)))*D2 + 
 (MAX(0,MIN(X5-1000,1000))+MIN(MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))),1000-MAX(0,MIN(X5-1000,1000))))*D3 + 
 (MAX(0,MIN(X5-2000,1000))+MIN(MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))-MIN(MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))),1000-MAX(0,MIN(X5-1000,1000)))),1000-MAX(0,MIN(X5-2000,1000))))*D4 + 
 (MAX(0,X5-3000)+MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))-MIN(MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))),1000-MAX(0,MIN(X5-1000,1000)))-MIN(MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))-MIN(MAX(0,Y5-MIN(Y5,1000-MIN(X5,1000))),1000-MAX(0,MIN(X5-1000,1000)))),1000-MAX(0,MIN(X5-2000,1000)))))*D5

这个公式逻辑与LET版本完全一致，只是没有中间变量的封装，可读性稍弱，但能在旧版Excel中正常运行。

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