スプレッドシートの引数に復数シート範囲は使えない

スプレッドシートの引数に復数シート範囲は使えない

エクセルではsum(sheet1:sheet3!A1:A3)というように復数シートの範囲を引数にすることができますが、スプレッドシートではできません。

このシートから、このシートまでは指定できない

エクセルの場合にはセルの範囲指定と同様にシートでもsheet1:sheet3のような指定ができます。しかし、スプレッドシートではできません。

エクセルからスプレッドシートに変換した場合

エクセルをグーグルドライブにアップロードして、スプレッドシートで開けばスプレッドシートに変換されます。この時にエクセル上のsum(Sheet1:Sheet3!B1:B3)がどうなるのか試してみました。

結果はsum(Sheet1:Sheet3!B1:B3)がそのまま入力されていますが、スプレッドシートでは認識されずに#NAME?とエラー表示になりました。

同様の関数を使用したい場合にはアップススクリプトで関数を定義しなければならないようです。

復数シート範囲指定できるSUM関数サンプル

エクセルのように復数のシートを範囲指定できるオリジナルのsum関数のサンプルコードです。ただし、引数が文字列になっているため、計算対象範囲の数値を書き換えても自動的に再計算はされません。

使用準備は、「ツール>スクリプトエディタ」を開いて、以下のサンプルコードをコピペして保存するだけです。そうすればスプレッドシートで=originalSUM("Sheet1:Sheet3!A1:A5")というように使用できます。引数の"をお忘れなく。

function originalSUM(str) {

  var area = str.split("!");
  var sheetRange = area[0].split(":");
  var cellRange = area[1].split(":");

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  var sheetFrom = 0;
  for(var i = 0i < sheets.length; i ++){
    if(sheets[i].getName() != sheetRange[0]) continue;
    sheetFrom = i;
    break;
  }

  var sheetTo = sheets.length;
  for(var i = 0i < sheets.length; i ++){
    if(sheets[i].getName() != sheetRange[1]) continue;
    sheetTo = i;
    break;
  }

  var sum = 0;
  for(var i = sheetFromi <= sheetToi ++){
    var firstCell = sheets[i].getRange(cellRange[0]);
    var lastCell = sheets[i].getRange(cellRange[1]);
    var startRow = firstCell.getRow();
    var startColumn = firstCell.getColumn();
    var numRows = lastCell.getRow() - startRow + 1;
    var numColumns = lastCell.getColumn() - startColumn + 1;
    var values = sheets[i].getSheetValues(startRowstartColumnnumRowsnumColumns);
    for(var j = 0j < values.length; j ++){
      for(var k = 0k < values[j].length; k ++){
        if(!values[j][k]) continue;
        if(isNaN(values[j][k])) continue;
        sum += values[j][k];
      }
    }
  }

  return sum;
}