求Google Sheets滚动6个月考勤积分自动计算公式

核心思路 #

通过动态日期函数定位时间范围，结合索引匹配获取目标数据列，实现打开表格时自动更新滚动6个月的积分求和。

具体公式 #

假设日期表头在第6行（C6:CS6），需求和的行对应C7、C9、C11、C13、C15，使用以下公式：

=SUM(
  INDEX(C7:CS7, MATCH(EDATE(TODAY(), -6), C6:CS6, 1)):CS7,
  INDEX(C9:CS9, MATCH(EDATE(TODAY(), -6), C6:CS6, 1)):CS9,
  INDEX(C11:CS11, MATCH(EDATE(TODAY(), -6), C6:CS6, 1)):CS11,
  INDEX(C13:CS13, MATCH(EDATE(TODAY(), -6), C6:CS6, 1)):CS13,
  INDEX(C15:CS15, MATCH(EDATE(TODAY(), -6), C6:CS6, 1)):CS15
)

公式拆解 #

• TODAY()：获取打开表格时的当前日期
• EDATE(TODAY(), -6)：计算当前日期往前推6个月的起始日期
• MATCH(..., C6:CS6, 1)：在日期表头中找到小于等于起始日期的最后一列位置，锁定滚动范围的起始列
• INDEX(...):CSx：定位每个目标行中从起始列到最后一列的范围，最终对所有范围求和

注意事项 #

• 确保第6行的日期是Google Sheets可识别的标准格式（如YYYY-MM-DD），否则匹配会失效
• 若日期表头不在第6行，需将公式中的C6:CS6替换为实际表头范围
• 若要统计完整6个自然月（而非滚动6个月的精确天数），可将EDATE(TODAY(), -6)替换为EDATE(EOMONTH(TODAY(), -1), -5)，确保包含当前月份在内的完整6个月数据

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