如何在Excel中分配正数至负数以最小化负值？

问题分析 #

你需要实现的逻辑是：将正数按比例分配给负数，要么把所有负数补到0（当正数总和≥负数绝对值总和时），要么把所有正数全部分配给负数（当正数总和<负数绝对值总和时），以此最小化负数的绝对值。

修正后的Excel公式 #

支持LET函数的版本（Excel 365/2021及以上）

使用LET函数简化公式，提升可读性：

=LET(
    val, C14,
    total_pos, SUMIF($C14:$E14, ">0"),
    total_neg_abs, ABS(SUMIF($C14:$E14, "<0")),
    IF(val < 0,
        IF(total_pos >= total_neg_abs, ABS(val), total_pos * ABS(val) / total_neg_abs),
        IF(val > 0,
            IF(total_pos >= total_neg_abs, -val * total_neg_abs / total_pos, -val),
            0
        )
    )
)

兼容旧版Excel的嵌套IF版本

如果你的Excel不支持LET，可以使用展开后的嵌套公式：

=IF(C14<0,
    IF(SUMIF($C14:$E14,">0")>=ABS(SUMIF($C14:$E14,"<0")),
        ABS(C14),
        SUMIF($C14:$E14,">0")*ABS(C14)/ABS(SUMIF($C14:$E14,"<0"))
    ),
    IF(C14>0,
        IF(SUMIF($C14:$E14,">0")>=ABS(SUMIF($C14:$E14,"<0")),
            -C14*ABS(SUMIF($C14:$E14,"<0"))/SUMIF($C14:$E14,">0"),
            -C14
        ),
        0
    )
)

原公式的问题说明 #

1. 符号错误：原公式中计算时直接使用了SUMIF($C14:$E14,"<0")（负数的总和，本身为负）作为分母或分子，导致符号逻辑混乱，需替换为ABS(SUMIF($C14:$E14,"<0"))获取负数的绝对值总和。
2. 未区分两种场景：原公式没有考虑正数总和是否足够覆盖负数绝对值总和的情况，当正数不足时，正数应全部划拨（增量为-自身值），负数按比例分配；当正数足够时，负数应被补至0，正数按比例扣除。

验证示例 #

• 示例1：(-27.5, -22, 19.5)
  总正数19.5 < 总负绝对值49.5，负数增量分别为19.5*27.5/49.5≈10.8、19.5*22/49.5≈8.7，正数增量为-19.5，最终结果(-16.7, -13.3, 0)，符合预期。
• 示例2：(14, -6, 18)
  总正数32 ≥ 总负绝对值6，负数增量为6，正数增量分别为-14*6/32=-2.625、-18*6/32=-3.375，最终结果(11.375, 0, 14.625)，符合预期。

内容的提问来源于stack exchange，提问作者James O'dare