Google Sheets：如何用ARRAYFORMULA实现月度动态求和

现有数据 #

DATE        DAY KM      SPD         MON/TOT TOTAL DIST.
                    
2022.08.21  SUN 8.47 km 8.00 km/h   AUG--5  42.79 km
2022.08.22  MON 8.62 km 7.90 km/h       
2022.08.23  TUE 8.50 km 7.79 km/h       
2022.08.25  THU 8.61 km 8.05 km/h       
2022.08.28  SUN 8.59 km 8.39 km/h       
2022.09.01  THU 9.10 km 8.25 km/h   SEP--2  10.10 km
2022.09.01  THU 1.00 km 9.90 km/h       
2022.10.01  SAT 9.60 km 8.00 km/h   OCT--4  26.30 km
2022.10.01  SAT 2.00 km 8.00 km/h       
2022.10.05  WED 5.00 km 8.70 km/h       
2022.10.05  WED 9.70 km 6.00 km/h       
2022.11.01  TUE 9.90 km 8.00 km/h   NOV--1  9.90 km

当前公式问题 #

MON/TOT 列 #

原公式依赖辅助列，逻辑冗余：

=ARRAYFORMULA(IF(B4:B="",,(IF(I4:I=J4:J,,UPPER(TEXT(B4:B,"MMM"))&IF(I4:I=J4:J,,"--")&IF(I4:I=J4:J,,COUNTIFS(B4:B,">="&B4:B, B4:B,"<="&EOMONTH(B4:B,0)) ))))) 

TOTAL DIST. 列 #

手动公式可动态求和，但无法通过ARRAYFORMULA批量应用，插入新行会失效：

=SUM(INDIRECT("D"&ROW()&":D"&ROW()+INDEX(SPLIT(INDIRECT("F"&ROW()),"--"),1,2)-1))  

优化方案 #

1. MON/TOT 列：去除辅助列，简化公式 #

直接在目标单元格输入以下数组公式，自动在当月第一条记录生成月份--次数标识：

=ARRAYFORMULA(
  IF(B4:B="",,
    IF(
      MONTH(B4:B)<>MONTH(B4:B-1),
      UPPER(TEXT(B4:B,"MMM"))&"--"&COUNTIFS(YEAR(B4:B),YEAR(B4:B),MONTH(B4:B),MONTH(B4:B)),
      ""
    )
  )
)

逻辑说明：

• 仅在当月首条记录（当前行月份与上一行不同时）生成标识
• 按「年份+月份」统计活动次数，避免跨年同月的统计错误

2. TOTAL DIST. 列：批量数组求和公式 #

直接在目标单元格输入以下公式，自动在当月第一条记录计算总距离，插入新行自动适配：

=ARRAYFORMULA(
  IF(B4:B="",,
    IF(
      MONTH(B4:B)<>MONTH(B4:B-1),
      BYROW(
        FILTER(B4:B, MONTH(B4:B)<>MONTH(B4:B-1)),
        LAMBDA(start_date,
          SUM(VALUE(REGEXEXTRACT(FILTER(D4:D,YEAR(B4:B)=YEAR(start_date),MONTH(B4:B)=MONTH(start_date)),"\d+\.?\d*")))&" km"
        )
      ),
      ""
    )
  )
)

逻辑说明：

• 筛选所有当月首条记录的日期
• 对每个首条日期，提取对应年月的所有距离数值（剥离单位）并求和
• 自动适配新插入的行，无需手动调整公式

补充注意事项 #

• 确保DATE列（B列）为标准日期格式，若为文本格式需先转换：=ARRAYFORMULA(IF(B4:B="",,DATEVALUE(SUBSTITUTE(B4:B,".","/"))))
• 若KM列（D列）已分离数值和单位，可直接用D4:D替换VALUE(REGEXEXTRACT(D4:D,"\d+\.?\d*"))

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