Google Sheets数组公式：多条件计算税前折扣税节省（第二部分）

公式实现（直接在N2单元格输入） #

=ARRAYFORMULA(
  IF(
    BYROW(
      C2:Z,  // 替换为包含所有Discount/Pretax列对的实际单元格范围
      LAMBDA(row,
        OR(
          // 条件1：任意单元格包含问号
          REGEXMATCH(TO_TEXT(row), "\?"),
          // 条件2：Discount是数值，但对应Pretax非Y/N
          SUMPRODUCT(
            ISNUMBER(INDEX(row, SEQUENCE(COLUMNS(row)/2,1,1,2))),
            NOT(REGEXMATCH(INDEX(row, SEQUENCE(COLUMNS(row)/2,1,2,2)), "^Y$|^N$"))
          )>0,
          // 条件3：Pretax有值，但对应Discount非数值
          SUMPRODUCT(
            NOT(ISBLANK(INDEX(row, SEQUENCE(COLUMNS(row)/2,1,2,2)))),
            NOT(ISNUMBER(INDEX(row, SEQUENCE(COLUMNS(row)/2,1,1,2))))
          )>0
        )
      )
    ),
    "??",
    // 正常计算逻辑：Pretax为Y时，Discount*税率求和
    BYROW(
      C2:Z,
      LAMBDA(row,
        SUMPRODUCT(
          INDEX(row, SEQUENCE(COLUMNS(row)/2,1,1,2)),
          (INDEX(row, SEQUENCE(COLUMNS(row)/2,1,2,2))="Y"),
          B2:B
        )
      )
    )
  )
)

核心逻辑说明 #

1. 错误校验：

   • 用BYROW逐行扫描目标列范围：
     • 通过REGEXMATCH(TO_TEXT(row), "\?")检查整行是否存在问号
     • 两组SUMPRODUCT分别统计「Discount是数值但Pretax非Y/N」「Pretax非空但Discount非数值」的违规列对数量，只要存在违规就触发错误
   • 满足任一错误条件时，该行返回??

2. 正常计算：

   • 用SEQUENCE精准提取所有Discount列（奇数位）和对应Pretax列（偶数位）
   • 仅当Pretax值为Y时，将对应Discount值乘以该行B列的税率，最终用SUMPRODUCT求和得到税节省金额

使用注意事项 #

• 必须将公式中的C2:Z替换为你表格中实际包含所有Discount/Pretax列对的范围，且确保列数为偶数（每对占2列）
• 若你的Discount/Pretax列并非从奇数位开始，需调整SEQUENCE的起始参数（比如从第2列开始就把1,2改为2,2）

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