Google Sheet中ARRAYFORMULA计算取零数量返回全0问题排查

核心问题分析 #

你的公式返回全0主要有3个关键错误：

• 逻辑判断方向颠倒：公式里用B18 < 0作为触发条件，但实际场景中，只有当现有总金额大于200备用金时（即B18 = 总金额 - 200 > 0），才需要计算取出的面额数量，负数条件完全不符合业务逻辑。
• QUOTIENT参数逻辑错误：QUOTIENT(B18, A2:A16)中，若B18为负数（触发条件），除以正数面额会得到负的商，再和正的现有数量B2:B16取MIN，结果必然是负数，后续无处理逻辑导致最终返回0。
• 缺失金额递减计算：原公式没有在计算完高面额后扣除已取金额，无法正确分配各面额的取出数量，且逻辑上不满足现金结算的实际需求。

修正后的公式 #

假设B18单元格是现有总金额减去200备用金的差值（即B18 = SUM(A2:A16*B2:B16) - 200），将C列的公式替换为：

=ARRAYFORMULA(
  IF(B18 <= 0, 0,
    LET(
      target_amount, B18,
      denominations, A2:A16,
      current_counts, B2:B16,
      accumulated, SCAN(0, denominations, LAMBDA(prev_total, denom, prev_total + MIN(QUOTIENT(target_amount - prev_total, denom), current_counts)*denom)),
      MIN(QUOTIENT(target_amount - OFFSET(accumulated, 0, 0, ROWS(accumulated)-1), OFFSET(denominations, 1, 0)), OFFSET(current_counts, 1, 0))
    )
  )
)

关键逻辑说明 #

• 外层IF(B18 <= 0, 0)：当现有金额不足或刚好等于备用金时，直接返回0，无需取钱。
• LET函数：定义变量简化公式，提升可读性和维护性。
• SCAN函数：累计已取出的金额，确保计算低面额时，基于剩余需取金额计算张数，符合现金结算“先取大面额再取小面额”的逻辑。
• MIN(QUOTIENT(...), current_counts)：对每个面额，取「剩余金额可兑换的最大张数」和「现有库存张数」的较小值，避免超出库存。

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