Google Sheets含空值单元格拆分公式优化求助

问题背景 #

现有产品测试数据，需将每行中按换行分隔的测试类型1/测试要求1、测试类型2/测试要求2拆分为独立行，同时保留对应产品的基础信息（物料编码、物料名称、生产代码）。原公式因对齐逻辑错误，输出结果不符合预期。

原公式问题分析 #

1. 先FLATTEN整列再SPLIT，会破坏原行的对应关系，当不同行的测试项数量不一致时，HSTACK无法正确匹配基础信息与测试项
2. 未处理test_type_2/test_req_2为空的场景，导致空值行干扰结果
3. 未统一处理多组测试项的拆分与合并逻辑，结构混乱

优化后公式 #

=LET(
  data, A4:I9,
  process_row, LAMBDA(row,
    LET(
      base, CHOOSECOLS(row, 1,2,3,4,5),
      item_code, TEXTJOIN("",, CHOOSECOLS(base,1,2,3)),
      item_name, INDEX(base,4),
      production_code, INDEX(base,5),
      // 处理第一组测试项
      t1, SPLIT(INDEX(row,6), CHAR(10)),
      r1, SPLIT(INDEX(row,7), CHAR(10)),
      // 处理第二组测试项
      t2, IFERROR(SPLIT(INDEX(row,8), CHAR(10)), ""),
      r2, IFERROR(SPLIT(INDEX(row,9), CHAR(10)), ""),
      // 合并两组测试项，补全空值对齐
      max_rows, MAX(ROWS(t1), ROWS(t2)),
      t1_filled, IFERROR(INDEX(t1, SEQUENCE(max_rows)), ""),
      r1_filled, IFERROR(INDEX(r1, SEQUENCE(max_rows)), ""),
      t2_filled, IFERROR(INDEX(t2, SEQUENCE(max_rows)), ""),
      r2_filled, IFERROR(INDEX(r2, SEQUENCE(max_rows)), ""),
      // 组合基础信息与测试项
      HSTACK(
        item_code,
        item_name,
        production_code,
        t1_filled,
        r1_filled,
        t2_filled,
        r2_filled
      )
    )
  ),
  // 逐行处理后合并结果，过滤全空测试项的行
  result, FLATTEN(BYROW(data, process_row)),
  filtered, FILTER(result, INDEX(result,0,4)<>""),
  // 重塑为7列的表格
  MAKEARRAY(ROWS(filtered)/7, 7, LAMBDA(r,c, INDEX(filtered, (r-1)*7 + c)))
)

公式逻辑说明 #

1. LET定义变量，data指向原始数据区域
2. process_row自定义函数，单独处理每一行数据：
   • 提取当前行的基础信息（物料编码、名称、生产代码）
   • 分别拆分两组测试类型与要求，用IFERROR处理空值
   • 按最大测试项行数补全空值，保证两组测试项行数对齐
   • 将基础信息与对齐后的测试项横向组合
3. BYROW遍历每一行执行处理，FLATTEN合并所有行结果
4. 过滤掉测试类型1为空的无效行，最后用MAKEARRAY将扁平化数据重塑为7列表格

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