Spreadsheet自动填充列S遇数组公式扩展错误求助

需基于C列（时间格式）和O列内容自动填充S列，规则如下：

• C列时间处于办公时间（08:30-17:30）：S列对应单元格设为$2
• C列时间处于**非办公时间（<08:30或>17:30）**且O列值为"N.A"：S列对应单元格设为$42
• C列时间处于非办公时间且O列值不为"N.A"：S列对应单元格设为$63

初始普通公式（可正常运行） #

输入在表头后的第2行单元格：

=IF((C:C >= TIME(8,30,0)) * (C:C <= TIME(17,30,0)), "$2",
 IF((C:C < TIME(8,30,0)) + (C:C > TIME(17,30,0)) * (O:O = "N.A"), "$42",
     IF((C:C < TIME(8,30,0)) + (C:C > TIME(17,30,0)) * (O:O <> "N.A"), "$63")))

数组公式报错问题 #

为实现新增条目自动填充，改用ArrayFormula后触发报错：
公式内容：

=ArrayFormula(
IF((C:C >= TIME(8,30,0)) * (C:C <= TIME(17,30,0)), "$2",
    IF((C:C < TIME(8,30,0)) + (C:C > TIME(17,30,0)) * (O:O = "N.A"), "$42",
        IF((C:C < TIME(8,30,0)) + (C:C > TIME(17,30,0)) * (O:O <> "N.A"), "$63")))

报错场景：

1. S列无数据时：Result was not automatically expanded, please insert more rows (1)
2. S3已有数据时：Array result was not expanded because it would overwrite data in S3

解决方案 #

方案1：限制范围+空行判断 #

避免整列引用导致的无效填充，仅处理有数据的行，同时跳过空行：

=ArrayFormula(
  IF(ROW(C:C)=1, "S列表头",  // 替换为S列实际表头，无需保留可删除此行
  IF(ISBLANK(C:C), "",
    IF((C:C >= TIME(8,30,0)) * (C:C <= TIME(17,30,0)), "$2",
      IF(AND((C:C < TIME(8,30,0)) + (C:C > TIME(17,30,0)), O:O = "N.A"), "$42", "$63")
    )
  )
)

• 新增ISBLANK(C:C)判断：空行返回空值，避免无意义填充
• 简化嵌套逻辑：最后一个条件直接用默认值，无需重复判断非办公时间

方案2：使用BYROW函数（Google Sheets 2022+支持） #

逐行处理数据，逻辑更直观，自动适配新增行：

=BYROW(C2:O, LAMBDA(row,
  LET(time, INDEX(row,1), o_val, INDEX(row,14),
    IF(time="", "",
      IF(AND(time >= TIME(8,30,0), time <= TIME(17,30,0)), "$2",
        IF(o_val="N.A", "$42", "$63")
      )
    )
  )
))

• C2:O指定从第2行开始的数据范围，自动识别新增条目
• LAMBDA+LET简化变量引用，提升公式可读性

报错根源 #

1. 整列引用冲突：ArrayFormula搭配整列引用（C:C/O:O）会尝试填充所有行，若S列已有数据则触发覆盖保护
2. 空行填充溢出：无数据时公式仍尝试填充全表行，超出当前工作表行数限制时触发扩展提示

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