Excel目标分配公式优化：达标后剩余金额按比例分配问题

需求概述 #

将$D$1（£39,000）按C列（Allocation %）的比例分配至D列（Allocated）：

• 当某行分配额达到B列（Target Amount）时，停止对该行的额外分配
• 剩余未分配金额，按未达目标的行的Allocation %比例重新分配

现有公式问题 #

原公式未对最终计算结果做上限校验，导致部分行分配额超出目标值（如D4超过£15,000），同时剩余金额的分配逻辑未准确过滤已达目标的行。

优化公式（兼容所有Excel版本） #

在D3单元格输入以下公式，下拉应用至D4:D6：

=MIN(B3, MAX(0, $D$1 - SUMPRODUCT(($B$3:$B2 < $D$1*$C$3:$C2)*$B$3:$B2) - SUMPRODUCT(($B$3:$B2 > $D$1*$C$3:$C2)*$D$1*$C$3:$C2)) * C3 / MAX(SUMPRODUCT(($B3:$B$6 > $D$1*$C3:$C$6)*C3:$C$6), 1))

公式逻辑解析 #

1. 计算已分配金额总和：
   • 第一部分SUMPRODUCT(($B$3:$B2 < $D$1*$C$3:$C2)*$B$3:$B2)：统计上方行中，初始比例分配额超过目标的行，直接取目标值的总和
   • 第二部分SUMPRODUCT(($B$3:$B2 > $D$1*$C$3:$C2)*$D$1*$C$3:$C2)：统计上方行中，初始比例分配额未达目标的行，按比例分配的总和
2. 剩余可分配金额：用总金额减去已分配总和，MAX(0, ...)避免出现负数
3. 当前行的分配权重：C3 / MAX(SUMPRODUCT(($B3:$B$6 > $D$1*$C3:$C$6)*C3:$C$6), 1)，分母为当前行及下方未达目标行的比例总和，MAX(...,1)防止除以0
4. 上限限制：MIN(B3, ...)确保最终分配额不超过该行目标值

Excel 365+ 动态数组简化方案 #

若使用Excel 365或更高版本，可借助动态数组函数实现更清晰的逻辑，输入D3后自动填充整列：

=LET(
    total, $D$1,
    targets, $B$3:$B$6,
    ratios, $C$3:$C$6,
    initial_alloc, total*ratios,
    locked_amounts, IF(initial_alloc<=targets, initial_alloc, targets),
    used_total, SUM(locked_amounts),
    remaining_fund, MAX(0, total-used_total),
    eligible_rows, initial_alloc>targets,
    eligible_ratio_sum, SUM(IF(eligible_rows, ratios, 0)),
    final_alloc, IF(eligible_rows, targets, initial_alloc + remaining_fund*ratios/eligible_ratio_sum),
    final_alloc
)

动态数组逻辑说明 #

• 先计算所有行的初始比例分配额initial_alloc
• 锁定已达目标的金额locked_amounts
• 计算剩余可分配资金remaining_fund
• 给未达目标的行按比例分配剩余资金，最终确保所有行分配额不超过目标

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