如何用ARRAYFORMULA或BYROW实现多列拆分后平均值批量计算

1. ARRAYFORMULA 批量计算方案 #

直接在F3单元格输入以下公式，即可自动填充整列，无需拖拽：

=ARRAYFORMULA(
  IF(
    A3:A="",,  // 跳过空行（可根据实际调整判断条件，比如C3:C=""）
    IFERROR(
      (
        BYROW(C3:C, LAMBDA(c, IF(c="",,AVERAGE(SPLIT(c, CHAR(10)))))) +
        BYROW(D3:D, LAMBDA(d, IF(d="",,AVERAGE(SPLIT(d, CHAR(10)))))) +
        BYROW(E3:E, LAMBDA(e, IF(e="",,AVERAGE(SPLIT(e, CHAR(10))))))
      ) / 3
    )
  )
)

说明：

• 用BYROW分别处理C/D/E列的每一行，拆分换行符后求单单元格内的平均值
• 再将三列的平均值数组相加后除以3，得到总平均
• IFERROR处理单元格为空或拆分后无有效值的情况
• 外层IF用于跳过整行空数据的行

2. BYROW 逐行处理方案（更易理解） #

BYROW是Google Sheets中专门用于逐行处理数组的函数，逻辑更直观，完全复刻手动拖拽的计算逻辑：

=BYROW(
  C3:E,  // 目标数据范围：C到E列的所有行
  LAMBDA(row,
    IF(COUNTA(row)=0,,  // 跳过整行空的情况
      IFERROR(
        (
          AVERAGE(SPLIT(INDEX(row, 1), CHAR(10))) +  // 取当前行C列，拆分后求平均
          AVERAGE(SPLIT(INDEX(row, 2), CHAR(10))) +  // 取当前行D列，拆分后求平均
          AVERAGE(SPLIT(INDEX(row, 3), CHAR(10)))    // 取当前行E列，拆分后求平均
        ) / 3
      )
    )
  )
)

说明：

• LAMBDA(row)定义了对每一行的处理逻辑
• INDEX(row, n)提取当前行的第n个单元格（对应C/D/E列）
• 可读性强，适合理解逐行计算的过程

3. 另一种平均值显示格式 #

如果需要更直观的展示效果，可通过TEXT函数格式化输出：

方案1：保留两位小数的数值格式 #

=BYROW(
  C3:E,
  LAMBDA(row,
    IF(COUNTA(row)=0,,
      IFERROR(
        TEXT(
          (AVERAGE(SPLIT(INDEX(row,1),CHAR(10)))+AVERAGE(SPLIT(INDEX(row,2),CHAR(10)))+AVERAGE(SPLIT(INDEX(row,3),CHAR(10))))/3,
          "0.00"  // 格式化规则：保留两位小数，不足补0
        )
      )
    )
  )
)

方案2：组合显示各列平均与总平均 #

=BYROW(
  C3:E,
  LAMBDA(row,
    IF(COUNTA(row)=0,,
      IFERROR(
        "C列平均:"&TEXT(AVERAGE(SPLIT(INDEX(row,1),CHAR(10))),"0.00")&
        " | D列平均:"&TEXT(AVERAGE(SPLIT(INDEX(row,2),CHAR(10))),"0.00")&
        " | E列平均:"&TEXT(AVERAGE(SPLIT(INDEX(row,3),CHAR(10))),"0.00")&
        " | 总平均:"&TEXT((AVERAGE(SPLIT(INDEX(row,1),CHAR(10)))+AVERAGE(SPLIT(INDEX(row,2),CHAR(10)))+AVERAGE(SPLIT(INDEX(row,3),CHAR(10))))/3,"0.00")
      )
    )
  )
)

输入输出示例 #

输入（C-E列，单元格内包含换行值） #

输出1：总平均（保留两位小数） #

输出2：组合显示格式 #

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