Excel多条件判断公式求助：求和匹配目标值问题

问题分析 #

你需要为P29及右侧单元格编写公式，满足三个核心逻辑：

• P27为空时返回空值；
• 当P27与已填充的P29及左侧单元格总和达到C27的90%时，返回C29/5；
• P29及右侧单元格的总和需恰好等于C29，达到后后续单元格返回空。

原公式的问题在于：未处理最后一次填充的收尾逻辑（无法凑满C29），也没有判断累计总和是否已达标，导致后续单元格直接为空，总和无法达到C29。

修正后的公式 #

在P29单元格输入以下公式，然后向右拖动填充：

=IF(P27="","",
    LET(
        累计总和, IFERROR(SUM($P29:INDIRECT(ADDRESS(ROW(), COLUMN()-1))), 0),
        触发阈值, $C$27*0.9,
        单次金额, $C$29/5,
        IF(累计总和=$C$29, "",
            IF(SUM($P$27, 累计总和)>=触发阈值,
                MIN(单次金额, $C$29-累计总和),
                ""
            )
        )
    )
)

公式解释 #

1. 基础判断：IF(P27="","",...)直接满足“P27为空则返回空”的要求；
2. 变量定义：用LET简化公式可读性：
   • 累计总和：计算当前单元格左侧所有已填充的P29系列单元格的总和，P29因无左侧单元格，用IFERROR返回0；
   • 触发阈值：计算C27的90%，作为开启填充的条件；
   • 单次金额：预计算C29/5，避免重复计算；
3. 总和达标判断：IF(累计总和=$C$29, "",...)确保累计总和达到C29后，后续单元格返回空；
4. 触发与填充逻辑：
   • 先判断P27与累计总和的合计是否达到触发阈值；
   • 达标时，取单次金额和C29-累计总和中的较小值：既保证常规填充为C29/5，又能在最后一次填充时刚好凑满C29，避免超出；
   • 未达标时返回空值。

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