Google表格滚动式年度数据求和：寻求公式/Apps Script解决方案

一、公式方案（适合自动实时刷新） #

假设原始数据在名为「Monthly Data」的工作表中，A列为日期（格式需识别为日期类型，如YYYY-MM）、B列为consumption kWh、C列为Cost。直接在「yearly data」工作表中使用以下QUERY公式，即可自动筛选出12个月数据齐全的年份并完成汇总：

=QUERY('Monthly Data'!A:C,"SELECT YEAR(A), SUM(B), SUM(C) WHERE A IS NOT NULL GROUP BY YEAR(A) HAVING COUNT(DISTINCT MONTH(A))=12 LABEL YEAR(A) '年份', SUM(B) '总耗电量(kWh)', SUM(C) '总费用'")

公式说明： #

• 自动按年份分组，仅保留包含12个不同月份的年份
• 实时同步原始数据的更新，实现滚动式持续汇总
• 输出结果直接包含年份、总耗电量、总费用三列，无需手动维护

如果需要保留“数据不全”的提示，可改用SUMIFS结合COUNTIF的数组公式：
在「yearly data」的A2单元格输入年份列表：

=UNIQUE(FILTER(TEXT('Monthly Data'!A:A,"YYYY"),'Monthly Data'!A:A<>""))

在B2单元格输入总耗电量公式：

=ARRAYFORMULA(IF(A2:A="",,IF(COUNTIF(TEXT('Monthly Data'!A:A,"YYYY"),A2:A)=12,SUMIFS('Monthly Data'!B:B,TEXT('Monthly Data'!A:A,"YYYY"),A2:A),"数据不全")))

C2单元格的总费用公式同理：

=ARRAYFORMULA(IF(A2:A="",,IF(COUNTIF(TEXT('Monthly Data'!A:A,"YYYY"),A2:A)=12,SUMIFS('Monthly Data'!C:C,TEXT('Monthly Data'!A:A,"YYYY"),A2:A),"数据不全")))

二、Google Apps Script方案（适合自动写入、避免重复汇总） #

如果需要自动将符合条件的年份汇总数据写入「yearly data」且不重复执行，可使用以下脚本：

1. 打开目标表格，点击「扩展程序」→「Apps Script」
2. 替换默认代码为以下内容（注意修改工作表名称为你的实际表名）：

function rollupYearlyData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const monthlySheet = ss.getSheetByName('Monthly Data'); // 替换为你的原始数据工作表名
  const yearlySheet = ss.getSheetByName('yearly data');
  if (!monthlySheet || !yearlySheet) return;

  // 获取原始数据（A列日期、B列耗电量、C列费用）
  const data = monthlySheet.getDataRange().getValues();
  const yearStats = {};

  // 遍历数据统计各年份的月份数、总耗电量、总费用
  data.forEach(row => {
    const date = row[0];
    if (!(date instanceof Date)) return;
    const year = date.getFullYear();
    const month = date.getMonth();

    if (!yearStats[year]) {
      yearStats[year] = {
        months: new Set(),
        totalConsumption: 0,
        totalCost: 0
      };
    }

    yearStats[year].months.add(month);
    yearStats[year].totalConsumption += row[1] || 0;
    yearStats[year].totalCost += row[2] || 0;
  });

  // 获取已存在的汇总年份，避免重复写入
  const existingYears = yearlySheet.getDataRange().getValues()
    .filter(row => row[0] && typeof row[0] === 'number')
    .map(row => row[0]);

  // 准备待写入的汇总数据
  const newRows = [];
  Object.keys(yearStats).forEach(year => {
    const yearNum = parseInt(year);
    if (yearStats[year].months.size === 12 && !existingYears.includes(yearNum)) {
      newRows.push([yearNum, yearStats[year].totalConsumption, yearStats[year].totalCost]);
    }
  });

  // 写入到「yearly data」表的末尾
  if (newRows.length > 0) {
    const lastRow = yearlySheet.getLastRow();
    yearlySheet.getRange(lastRow + 1, 1, newRows.length, 3).setValues(newRows);
  }
}

// 创建触发事件，可选编辑触发或定时触发
function setupTrigger() {
  // 每次编辑表格时自动检查汇总
  ScriptApp.newTrigger('rollupYearlyData').onEdit().create();
  // 如需每日定时触发，替换为以下代码：
  // ScriptApp.newTrigger('rollupYearlyData').timeBased().everyDays(1).create();
}

3. 保存脚本，先运行一次rollupYearlyData完成权限授权，再运行setupTrigger创建自动触发规则。此后每次编辑原始数据，脚本会自动检查并写入符合条件的年度汇总数据。

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