[GAS][スプレッドシート]指定した背景色のセルの合計を計算する

参考にしたソース

しゃのんあどべんとかれんだー 2日目 (忘年会幹事のちょっとしたお悩みをちょっと解決してくれるかもしれない GAS)

スクリプトエディタを開く

スクリプトを書く

// 指定範囲のセル背景色に合致するセルの合計数を返します
// color: 背景色    rangeSpecification: 範囲指定文字列
function countCellsWithBackgroundColor(color, rangeSpecification) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  
  var x = 0;
  var i = 0;
  var j = 0;
  var cell;
  for (i = 1; i <= range.getNumRows(); i++) {
    for (j = 1; j <= range.getNumColumns(); j++) {
      
      cell = range.getCell(i, j);
      
      if(cell.getBackgroundColor() == color)
        if (+cell.getValue())
          x += cell.getValue();
    }
  }
  
  return x;
}

// 指定範囲のセル背景色に合致するセルの合計数を返します
// color: 背景色    rangeSpecification: 範囲指定文字列
function countCellsWithoutBackgroundColor(color, rangeSpecification) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  
  var x = 0;
  var i = 0;
  var j = 0;
  var cell;
  for (i = 1; i <= range.getNumRows(); i++) {
    for (j = 1; j <= range.getNumColumns(); j++) {
      
      cell = range.getCell(i, j);
      
      if(cell.getBackgroundColor() != color)
        if (+cell.getValue())
          x += cell.getValue();
    }
  }
  
  return x;
}

// 指定セルの背景色を取得します。
function getBackgroundColor(rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  return sheet.getRange(rangeSpecification).getBackgroundColor();
}

セルに関数を設定

参考元のサイトには一色のケースしか載っていなかったのと、
セルの指定が文字列のため、オートフィルが難しかったり行列の削除でずれたりするため、改良しました。

例えば複数の色を合計する必要がある場合、ホワイトリスト（加算）の場合は以下のようにします。

=countCellsWithBackgroundColor(getBackgroundColor(CELL("address", $A$1)), CELL("address", C2)&":"&CELL("address", C13))
+countCellsWithBackgroundColor(getBackgroundColor(CELL("address", $A$2)), CELL("address", C2)&":"&CELL("address", C13))

ブラックリストの場合は以下のようにします。

=SUM(C2:C13)-countCellsWithBackgroundColor(getBackgroundColor(CELL("address", $A$3)), CELL("address", C2)&":"&CELL("address", C13))