如何结合Average、Offset & Match实现健身追踪表周数据汇总？

假设你的数据结构如下：

• 原始数据在Sheet1：A列=日期（日期格式），B列=英里数，C列=摄入量
• 仪表盘在Sheet2：A列=每周起始日期（如周一/周日，需与计算逻辑统一）

方法1：OFFSET+MATCH组合（兼容旧版Excel） #

计算周总英里数（Sheet2 B2单元格） #

=IFERROR(SUM(OFFSET(Sheet1!$B$1,MATCH(Sheet2!A2,Sheet1!$A:$A,0)-1,0,7,1)),0)

计算周平均摄入量（Sheet2 C2单元格） #

=IFERROR(AVERAGE(OFFSET(Sheet1!$C$1,MATCH(Sheet2!A2,Sheet1!$A:$A,0)-1,0,7,1)),0)

关键注意事项（解决你之前失败的可能原因）

• MATCH第三个参数必须设为0（精确匹配），确保找到起始日期的准确行号
• 日期格式必须统一：Sheet1的日期和Sheet2的起始日期都要设为日期型，不能是文本格式
• 原始数据需按日期连续排序，否则OFFSET会取到错误的7天区间

方法2：SUMIFS/AVERAGEIFS（更稳定，无需排序） #

如果原始数据日期不连续，或者不想依赖排序，推荐用区间匹配：

计算周总英里数 #

=IFERROR(SUMIFS(Sheet1!$B:$B,Sheet1!$A:$A,">="&Sheet2!A2,Sheet1!$A:$A,"<"&Sheet2!A2+7),0)

计算周平均摄入量 #

=IFERROR(AVERAGEIFS(Sheet1!$C:$C,Sheet1!$A:$A,">="&Sheet2!A2,Sheet1!$A:$A,"<"&Sheet2!A2+7),0)

优势说明

• 无需数据排序，只要起始日期是周第一天（如周一），A2+7自动定位到下一周起始，精准覆盖7天
• 自动忽略区间内无数据的日期，计算结果更准确

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