【GAS】スプレッドシートの数式機能まとめ【サンプルソース付】

GAS開発者向けにスプレッドシートの数式機能をすべてまとめました。

たくさんある便利な関数を数式で簡単に設定できるようになると、やれることが無限大に拡がります。

特にスプレッドシートはExcelにはない超強力な関数があるので、積極的に使うべきです。

A1形式やR1C1形式で設定できます。最初はややこしいかもしれませんが、絶対参照や相対参照の理解にも結び付くはずです。

用途に応じて使い分け、自分のやりやすいやり方を見つけてもらえればと思います。

数式に関するメソッドをすべて載せてます。少しでも参考になれば幸いです。

Google Apps Script スプレッドシート基礎リファレンス
セル範囲クラス[Range]の数式設定
セル範囲リストクラス[RangeList]の数式設定
1. 数式設定A1形式[setFormula(式)]
2. 数式設定R1C1形式[setFormulaR1C1(式)]
セル範囲クラス[Range]の数式取得
最後に

Google Apps Script スプレッドシート基礎リファレンス

当ページは、GAS公式リファレンスを元にスプレッドシートの機能毎にまとめた基礎的な内容となっております。

若干、文言がおかしい可能性がありますが、あらかじめご了承ください。

GAS基本メソッドまとめ一覧はコチラ↓↓↓です。スプレッドシートの基本的な機能をまとめてますので、ぜひご活用ください。

【GAS】スプレッドシート基本メソッドまとめ一覧【初心者入門】

GAS開発者向けにスプレッドシートの基本的なメソッドを一覧にまとめました。各機能ごとのクラス、メソッドごとのサンプルソース付ページへリンクする一覧です。初心者にとって、スプレッドシートの基本的な操作・機能単位のメソッドを知...

まずは実際に動かしてみることをオススメします。マクロの登録方法は以下を参照いただければと思います。

【GAS】Googleスプレッドシートでマクロの使い方入門【動画付】

Googleの「Google Apps Script」通称GAS。GoogleマップやGメールなど、いろんなGoogle関連のサービスと自由に連携できたり、独自のWebアプリを開発することもできる、いろんな可能性を秘めたワクワクの...

GASの本はまだまだ少ないですね。基礎レベルであれば、以下の参考書が初心者にはベストの本です。

基本的な仕様やよく使われる機能の設定方法がまとまっています。

リンク

※JavaScriptの知識が既にある程度ある人にとっては冗長かもしれません。

セル範囲クラス[Range]の数式設定

セル範囲の数式設定メソッドです。複数セルへの設定もあります。

数式設定A1形式[setFormula(式)]

setFormula(formula)
この範囲の式を更新します。与えられた式はA1表記でなければなりません。

function setFormulaSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var cell = sheet.getRange("B5");
  cell.setFormula("=SUM(B3:B4)");
}

Parameters
名前	タイプ	説明
formula	String	セルに設定する数式を表す文字列。

Return
Range - 連鎖用のセル範囲。

数式設定R1C1形式[setFormulaR1C1(式)]

setFormulaR1C1(formula)
この範囲の式を更新します。与えられた式はR1C1表記でなければなりません。

function setFormulaR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var cell = sheet.getRange("B5");
  // これは、式をB5より上の3行の合計になるように設定します
  cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");
}

Parameters
名前	タイプ	説明
formula	String	文字列式です。

Return
Range - 連鎖用のセル範囲。

数式配列設定A1形式[setFormula(式's)]

setFormulas(formulas)
式の2次元配列を設定します（この範囲のサイズと一致している必要があります）。与えられた式はA1表記でなければなりません。このメソッドは、行、次に列でインデックス付けされた式の2次元配列を取ります。配列の次元は範囲の次元に対応していなければなりません。

function setFormulasSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // これにより、式は合計の行になり、そのすぐ下に平均の行が続きます。
  // 2次元配列のサイズは範囲のサイズと一致する必要があります。
  var formulas = [
    ["=SUM(B2:B4)", "=SUM(C2:C4)", "=SUM(D2:D4)"],
    ["=AVERAGE(B2:B4)", "=AVERAGE(C2:C4)", "=AVERAGE(D2:D4)"]
  ];

  var cell = sheet.getRange("B5:D6");
  cell.setFormulas(formulas);
}

Parameters
名前	タイプ	説明
formulas	String[][]	式の2次元文字列配列です。

Return
Range - 連鎖用のセル範囲。

数式配列設定R1C1形式[setFormula(式's)]

setFormulasR1C1(formulas)
式の2次元配列を設定します（この範囲のサイズと一致している必要があります）。与えられた式はR1C1表記でなければなりません。

function setFormulasR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // これにより、一連の合計に続いて一連の平均に対応する式が作成されます。
  var sumOfRowsAbove = "=SUM(R[-3]C[0]:R[-1]C[0])";
  var averageOfRowsAbove = "=AVERAGE(R[-4]C[0]:R[-2]C[0])";

  // 2次元配列のサイズは範囲のサイズと一致する必要があります。
  var formulas = [
    [sumOfRowsAbove, sumOfRowsAbove, sumOfRowsAbove],
    [averageOfRowsAbove, averageOfRowsAbove, averageOfRowsAbove]
  ];

  var cell = sheet.getRange("B5:D6");
  // これは、式をB5より上の3行の合計になるように設定します。
  cell.setFormulasR1C1(formulas);
}

Parameters
名前	タイプ	説明
formulas	String[][]	R1C1形式の式の2次元配列。

Return
Range - 連鎖用のセル範囲。

セル範囲リストクラス[RangeList]の数式設定

セル範囲リストの数式設定メソッドです。

数式設定A1形式[setFormula(式)]

setFormula(formula)
範囲リスト内の各範囲の式を更新します。与えられた式はA1表記でなければなりません。

function setFormulaSample() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeList = sheet.getRangeList(['A11', 'C11']);
  rangeList.setFormula('=SUM(B1:B10)');
}

Parameters
名前	タイプ	説明
formula	String	設定する式を表す文字列。

Return
RangeList - 連鎖用のセル範囲リスト。

数式設定R1C1形式[setFormulaR1C1(式)]

setFormulaR1C1(formula)
範囲リスト内の各範囲の式を更新します。与えられた式はR1C1表記でなければなりません。

function setFormulaR1C1Sample() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rangeList = sheet.getRangeList(['A11', 'C11']);
  // これは、式をB5より上の3行の合計になるように設定します
  rangeList.setFormulaR1C1('=SUM(R[-3]C[0]:R[-1]C[0])');
}

Parameters
名前	タイプ	説明
formula	String	文字列式です。

Return
RangeList - 連鎖用のセル範囲リスト。

セル範囲クラス[Range]の数式取得

セル範囲の数式取得メソッドです。

数式取得A1形式[getFormula()]

getFormula()
範囲の左上のセルの数式（A1表記）を返します。セルが空の場合、または数式が含まれていない場合は、空の文字列を返します。

function getFormulaSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // これはあなたがB2:B4を合計する
  // B5の関数を持っていると仮定します
  var range = sheet.getRange("B5");

  // 計算値と計算式を記録します
  Logger.log("Calculated value: %s Formula: %s",
  range.getValue(),
  range.getFormula());
}

Return
String - セルの数式です。

数式取得R1C1形式[getFormulaR1C1()]

getFormulaR1C1()
Returns the formula (R1C1 notation) for a given cell, or null if none.

function getFormulaR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B5");
  var formula = range.getFormulaR1C1();
  Logger.log(formula);
}

Return
String - R1C1表記の式です。

数式配列取得A1形式[getFormulas()]

getFormulas()
範囲内のセルの数式（A1表記）を返します。 2D配列のエントリは、数式がないセルの空の文字列です。

function getFormulasSample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B5:C6");
  var formulas = range.getFormulas();
  for (var i in formulas) {
    for (var j in formulas[i]) {
      Logger.log(formulas[i][j]);
    }
  }
}

Return
String[][] - 文字列形式の数式の2次元配列です。

数式配列取得R1C1形式[getFormulasR1C1()]

getFormulasR1C1()
範囲内のセルの数式（R1C1表記）を返します。式のないセルの場合、2D配列のエントリはnullです。

function getFormulasR1C1Sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B5:C6");
  var formulas = range.getFormulasR1C1();
  for (var i in formulas) {
    for (var j in formulas[i]) {
      Logger.log(formulas[i][j]);
    }
  }
}

Return
String[][] - R1C1表記の式の2次元配列です。

最後に

GAS数式機能のまとめをご紹介させていただきました。

必要に応じて、有益な情報でさらに更新していきたいと思います。

プログラミングスキルを向上させるためには、本を読むだけでは脳にインプットされませんね。

実際に手を動かしてアウトプットしまくるのが一番の近道だと思います。

実際にサンプルソースをコピーして実行した結果がどうなるかを体験してみてください。

自分なりのアレンジを加えたり、なぜエラーになるのかを考え、何度も試行錯誤して問題を解決しまくり、着実に理解しながらスキルを向上させていきましょう。

GASはExcelVBAとはかなり違いますが、ベースとなっているJavaScriptは覚えて損はないプログラミング言語なのでショートカットキーなどと組み合わせてぜひ活用してみてください。

Googleスプレッドシート全ショートカットキー一覧はこちら↓↓↓

Googleスプレッドシートの使い方や機能がわかるショートカットキー全まとめ一覧【初心者こそ必見】

表計算ソフトとして、まだまだExcelのシェア率は高いですが、GoogleスプレッドシートにはExcelにはない超強力な関数があったり、Excelとは違って常に最新版を無料で使用できます。なにより、インターネットを使ったオンライン...

ショートカットキー一覧にもGASメソッドを載せています。どの操作からどのメソッドが呼ばれるのかイメージしてください。

Google Apps Script スプレッドシート 基礎 リファレンス

セル範囲クラス[Range]の数式設定

数式設定A1形式[setFormula(式)]

数式設定R1C1形式[setFormulaR1C1(式)]

数式配列設定A1形式[setFormula(式's)]

数式配列設定R1C1形式[setFormula(式's)]

セル範囲リストクラス[RangeList]の数式設定

数式設定A1形式[setFormula(式)]

数式設定R1C1形式[setFormulaR1C1(式)]

セル範囲クラス[Range]の数式取得

数式取得A1形式[getFormula()]

数式取得R1C1形式[getFormulaR1C1()]

数式配列取得A1形式[getFormulas()]

数式配列取得R1C1形式[getFormulasR1C1()]

最後に

コメント

Google Apps Script スプレッドシート基礎リファレンス