Google Sheets如何通过脚本实现多条件统计指定背景色单元格

改造说明 #

原有脚本仅支持全范围统计指定背景色的单元格总数，改造后新增销售员匹配逻辑，可逐行校验销售员姓名与对应行日期列单元格背景色，仅同时满足两个匹配条件的单元格才计入统计，原有全量统计函数保留，不影响已有公式使用。

完整改造后脚本代码 #

替换原有脚本编辑器内的全部代码即可，原有函数逻辑完全保留：

function SommeCouleurs(plage,couleur) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formule = activeRange.getFormula();
  
  
  var laplage = formule.match(/\((.*)\;/).pop();
  var range = activeSheet.getRange(laplage);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var lacouleur = formule.match(/\;(.*)\)/).pop();
  var colorCell = activeSheet.getRange(lacouleur);
  var color = colorCell.getBackground();
  
  var total = 0; 
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
      total=total+(values[i][j]*1);
  return total;     

};


function CompteCouleurs(plage,couleur) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formule = activeRange.getFormula();
  
  
  var laplage = formule.match(/\((.*)\;/).pop();
  var range = activeSheet.getRange(laplage);
  var bg = range.getBackgrounds();
  
  var lacouleur = formule.match(/\;(.*)\)/).pop();
  var colorCell = activeSheet.getRange(lacouleur);
  var color = colorCell.getBackground();
  
 var count = 0;

  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
      count=count+1;
      return count;
};

// 新增：按销售员统计指定背景色单元格数量
function CompteCouleursParVendeur(plage,couleur,plageVendeur,nomCible) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formule = activeRange.getFormula();
  
  // 拆分公式内的4个参数，自动处理绝对引用符号$
  var params = formule.match(/\((.*)\)/)[1].split(';').map(function(p){return p.trim()});
  // 读取日期列范围与背景色
  var dateRange = activeSheet.getRange(params[0]);
  var dateBgs = dateRange.getBackgrounds();
  // 读取目标统计颜色
  var targetColor = activeSheet.getRange(params[1]).getBackground();
  // 读取销售员列范围与值
  var sellerRange = activeSheet.getRange(params[2]);
  var sellerValues = sellerRange.getValues();
  // 读取目标销售员名称，支持直接输入文本或引用单元格
  var targetSeller = params[3].startsWith('"') 
    ? params[3].replace(/"/g, '').trim() 
    : activeSheet.getRange(params[3]).getValue().toString().trim();

  var count = 0;
  // 逐行校验匹配条件
  for(var i=0; i<dateBgs.length; i++){
    // 销售员不匹配直接跳过
    if(sellerValues[i][0].toString().trim() !== targetSeller) continue;
    // 背景色匹配则计数+1
    if(dateBgs[i][0] === targetColor) count++;
  }
  return count;
}

新函数调用方法 #

公式格式 #

=CompteCouleursParVendeur(日期列范围, 颜色参照单元格, 销售员列范围, 目标销售员)

使用示例 #

如果表格结构为F列存销售员姓名、G列存日期，红色参照样本在C4单元格，要统计E6单元格内存储的销售员对应的G列红色单元格数量，公式写为：

=CompteCouleursParVendeur($G$6:$G;$C$4;$F$6:$F;E6)

公式下拉即可批量生成所有销售员的统计结果。

参数说明 #

• 第一个参数：需要统计背景色的日期列范围，注意和销售员列的起始、结束行号完全对齐，建议加$锁定为绝对引用
• 第二个参数：存放目标统计颜色的参照单元格，建议加$锁定
• 第三个参数：销售员姓名列的对应范围，必须和第一个参数的行范围完全一致，建议加$锁定
• 第四个参数：要统计的目标销售员，支持直接输入带双引号的姓名文本（如"张三"），也支持引用存储姓名的单元格

注意：如果修改了单元格背景色后统计值没有自动刷新，按F9手动触发重算即可，自定义函数不会自动感知背景色变化触发刷新。

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