Google Sheets跨工作表指定对象列近6个月数据平均值自动计算需求

问题背景 #

现有两个工作表结构如下：

Sheet1 #

Sheet2 #

需求：为单元格a（Sheet2!C2）和b（Sheet2!D2）编写自动化公式，实现：

1. 自动识别单元格所属的对象列（如a属于Object 2列）
2. 计算所有指定工作表中该对象列内近6个月的数据平均值
3. 排除自身单元格（如计算Object 2平均值时排除a所在的Sheet2!C2）

解决方案 #

单个单元格公式（以a为例） #

在Sheet2!C2输入以下公式，自动计算Object 2列的近6个月平均值：

=AVERAGE(QUERY(
  {Sheet1!B:D; FILTER(Sheet2!B:D, ROW(Sheet2!B:D)<>2)},
  "select Col"&MATCH(C1, {Sheet1!B1:D1; Sheet2!B1:D1}, 0)&" 
   where Col1 >= date '"&TEXT(EDATE(TODAY(), -6), "yyyy-mm-dd")&"' 
   and Col"&MATCH(C1, {Sheet1!B1:D1; Sheet2!B1:D1}, 0)&" is not null",
  0
))

批量处理公式（同时计算a和b） #

如果需要一次性处理Sheet2!C2:D2的所有单元格，使用ARRAYFORMULA批量计算：

=ARRAYFORMULA(IF(C1:D1="",,
  BYCOL(C1:D1, LAMBDA(header,
    AVERAGE(QUERY(
      {Sheet1!B:D; FILTER(Sheet2!B:D, ROW(Sheet2!B:D)<>2)},
      "select Col"&MATCH(header, {Sheet1!B1:D1; Sheet2!B1:D1}, 0)&" 
       where Col1 >= date '"&TEXT(EDATE(TODAY(), -6), "yyyy-mm-dd")&"' 
       and Col"&MATCH(header, {Sheet1!B1:D1; Sheet2!B1:D1}, 0)&" is not null",
      0
    ))
  ))
))

公式说明 #

1. 数据合并与排除自身：{Sheet1!B:D; FILTER(Sheet2!B:D, ROW(Sheet2!B:D)<>2)} 将Sheet1的日期+数据列，与Sheet2排除第2行（自身单元格所在行）的日期+数据列合并，避免计算自身值。
2. 表头匹配：MATCH(header, {Sheet1!B1:D1; Sheet2!B1:D1}, 0) 自动定位当前列标题在所有指定工作表表头中的位置，确定需要提取的列索引。
3. 日期筛选：TEXT(EDATE(TODAY(), -6), "yyyy-mm-dd") 生成近6个月的起始日期，转换为QUERY可识别的格式，筛选符合时间范围的数据。
4. 平均值计算：QUERY提取符合条件的列数据后，用AVERAGE直接计算平均值。

注意事项 #

• 确保所有工作表的日期列使用标准日期格式，否则QUERY无法正确筛选。
• 若需添加更多工作表，只需在合并数组中补充（如{Sheet1!B:D; Sheet2!B:D; Sheet3!B:D}），同时调整FILTER排除对应工作表的汇总行。
• 若自身单元格不在第2行，修改ROW(Sheet2!B:D)<>2中的数字为对应行号即可。

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