如何让Google Sheets自定义函数countColors支持indirect()与R1C1引用？

原函数无法兼容INDIRECT(R1C1)组合的区域引用，核心问题是未正确解析这类动态生成的区域格式。以下是修改方案：

修改后的函数代码 #

function countColors(targetRange, colorCell) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var count = 0;
  
  // 解析目标区域参数，转换为有效Range对象
  var range;
  if (typeof targetRange === 'string') {
    range = sheet.getRange(targetRange);
  } else if (targetRange instanceof Array) {
    var formula = SpreadsheetApp.getActiveRange().getFormula();
    var rangeMatch = formula.match(/countcolors\((.*?),(.*?)\)/i);
    if (rangeMatch) {
      var rangeStr = rangeMatch[1];
      // 处理INDIRECT组合的起止区域
      try {
        var [startRef, endRef] = rangeStr.split(':');
        var startCell = sheet.getRange(startRef);
        var endCell = sheet.getRange(endRef);
        range = sheet.getRange(startCell.getRow(), startCell.getColumn(), 
                               endCell.getRow() - startCell.getRow() + 1, 
                               endCell.getColumn() - startCell.getColumn() + 1);
      } catch(e) {
        range = sheet.getRange(rangeStr);
      }
    } else {
      throw new Error("无法解析目标区域");
    }
  } else {
    throw new Error("目标区域参数格式无效");
  }
  
  // 解析颜色单元格参数，转换为有效Range对象
  var colorRange;
  if (typeof colorCell === 'string') {
    colorRange = sheet.getRange(colorCell);
  } else if (colorCell instanceof Array) {
    var formula = SpreadsheetApp.getActiveRange().getFormula();
    var colorMatch = formula.match(/countcolors\((.*?),(.*?)\)/i);
    if (colorMatch) {
      colorRange = sheet.getRange(colorMatch[2]);
    } else {
      throw new Error("无法解析颜色单元格");
    }
  } else {
    throw new Error("颜色单元格参数格式无效");
  }
  
  // 统计同色单元格数量
  var targetColor = colorRange.getBackground();
  var allColors = range.getBackgrounds();
  for (var row of allColors) {
    for (var cellColor of row) {
      if (cellColor === targetColor) count++;
    }
  }
  
  return count;
}

关键修改说明 #

• 新增动态区域解析：针对INDIRECT("R[0]C[-2]",false):INDIRECT("R[0]C[-1]",false)这类组合引用，通过分割起止单元格地址，转换为连续Range对象。
• 兼容多格式参数：同时支持字符串格式的引用（INDIRECT返回值）和直接传入的区域数组（原生A1引用场景）。
• 保留原有功能：不影响原A1格式区域引用的使用逻辑。

使用方式 #

直接在Y2单元格使用原公式即可：

=countcolors((INDIRECT("R[0]C[-2]",false)):(indirect("R[0]C[-1]",false)),(indirect("R[0]C[-2]",false)))

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