Google Sheets：统计带颜色单元格并实现列内容变更时自动重算

Google Sheets自定义函数默认仅在引用的单元格数值变化时触发重算，单元格背景颜色修改不属于数值变更，因此原函数无法自动更新。以下是三种可行解决方案：

方案一：优化函数并添加 onChange 触发器 #

先简化原函数逻辑，直接使用传入的单元格引用，再通过触发器监听表格变更（包括颜色修改）来强制重算。

优化后的统计函数 #

function countColoredCells(countRange, colorRef) {
  var bg = countRange.getBackgrounds();
  var targetColor = colorRef.getBackground();
  
  let count = 0;
  for (const row of bg) {
    for (const cellColor of row) {
      if (cellColor === targetColor) count++;
    }
  }
  return count;
}

添加 onChange 触发器 #

1. 打开表格的「扩展功能」→「Apps脚本」
2. 点击左侧时钟形状的「触发器」图标
3. 点击「添加触发器」，配置参数：
   • 运行函数：countColoredCells
   • 部署类型：HEAD
   • 事件源：「从电子表格」
   • 事件类型：「更改」
4. 保存并完成脚本权限授权

该触发器会在表格发生任何变更（含颜色修改）时，自动触发函数重算。

方案二：用 onEdit 触发器强制刷新公式 #

如果需要更及时的刷新，可编写onEdit触发器，每次编辑单元格时重新加载所有使用目标函数的公式。

添加刷新脚本 #

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  const allFormulas = activeSheet.getDataRange().getFormulas();

  for (let i = 0; i < allFormulas.length; i++) {
    for (let j = 0; j < allFormulas[i].length; j++) {
      if (allFormulas[i][j].includes('countColoredCells')) {
        const targetCell = activeSheet.getRange(i + 1, j + 1);
        const formula = targetCell.getFormula();
        // 通过清空再重新设置公式触发重算
        targetCell.clearContent();
        targetCell.setFormula(formula);
      }
    }
  }
}

方案三：辅助列记录颜色值（无触发器依赖） #

通过辅助列提取单元格颜色的十六进制值，再用普通函数统计，利用数值变更触发自动重算：

1. 在目标数据列旁插入辅助列，输入=getBackground(A1)（需先创建下方函数）
2. 创建提取颜色值的函数：

function getBackground(cell) {
  return cell.getBackground();
}

3. 用COUNTIF统计颜色：=COUNTIF(辅助列范围, getBackground(颜色参考单元格))

配合方案二的onEdit触发器，可实现颜色修改时自动更新统计结果。

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