含MAP与SCAN嵌入函数的循环引用及折旧计算问题

数据定义 #

• E19:AF19：折旧年份表头
• $F$20:$AF$20：各年份资本支出追加额
• C21：资产购入年份（纯年份格式）
• E12：折旧率
• E21:AF21：目标数组，用于存放计算出的年度折旧费用

当前问题 #

现有公式因循环引用无法正常运行，原公式：

=MAP(E21:AF21;
    SCAN(0; E21:AF21; LAMBDA(acc;curr; acc + curr));
        LAMBDA(curr;sumSoFar;IF(AND(INDIRECT(ADDRESS(19; COLUMN(curr))) > $C21;sumSoFar < INDEX($F$20:$AF$20; MATCH($C21; $F$19:$AF$19; 0))); INDEX($F$20:$AF$20; MATCH($C21; $F$19:$AF$19; 0)) * $E$12; 0)))

问题根源：SCAN函数直接引用了公式要写入的目标数组E21:AF21，导致循环依赖。

目标逻辑 #

期望实现的逻辑对应逐单元格公式：

=IF(AND(G$19>$C21;SUM($E21:F21)<HLOOKUP($C21;$F$19:$AF$20;2;FALSE));HLOOKUP($C21;$F$19:$AF$20;2;FALSE)*$E$12;0)

逻辑拆解：

1. 当前列年份晚于资产购入年份时，才可能产生折旧
2. 累计折旧额未超过对应购入年份的资本支出追加额时，继续按「资本支出额×折旧率」计算当期折旧
3. 不满足上述任一条件，当期折旧为0

修复后的公式 #

方案1（简洁高效版） #

=LET(
    购入支出, INDEX($F$20:$AF$20, MATCH($C21, $F$19:$AF$19, 0)),
    年折旧额, 购入支出 * $E$12,
    年份序列, E19:AF19,
    累计折旧, SCAN(0, 年份序列, LAMBDA(acc, y,
        IF(AND(y > $C21, acc < 购入支出), acc + 年折旧额, acc)
    )),
    MAP(年份序列, 累计折旧, LAMBDA(y, curr_sum,
        IF(AND(y > $C21, curr_sum - 年折旧额 < 购入支出), 年折旧额, 0)
    ))
)

方案2（贴近原公式结构） #

=MAP(E19:AF19,
    SCAN(0, E19:AF19, LAMBDA(acc, y,
        LET(
            购入支出, INDEX($F$20:$AF$20, MATCH($C21, $F$19:$AF$19, 0)),
            年折旧额, 购入支出 * $E$12,
            IF(AND(y > $C21, acc < 购入支出), acc + 年折旧额, acc)
        )
    )),
    LAMBDA(y, sumSoFar,
        LET(
            购入支出, INDEX($F$20:$AF$20, MATCH($C21, $F$19:$AF$19, 0)),
            年折旧额, 购入支出 * $E$12,
            IF(AND(y > $C21, sumSoFar - 年折旧额 < 购入支出), 年折旧额, 0)
        )
    )
)

修复说明 #

1. 用LET函数封装重复计算的变量，减少冗余运算，提升公式可读性
2. SCAN函数改为基于年份序列E19:AF19计算累计折旧，不再引用目标数组E21:AF21，彻底解决循环引用问题
3. 通过累计折旧值反向推导当期折旧额，完全匹配原逐单元格公式的判断逻辑

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