Googleスプレッドシートのオリジナル関数作ってみた

Advent Calendar 2021 GAS

こんにちは、エニグモ嘉松です。

この記事はEnigmo Advent Calendar 2021の17日目の記事です。

はじめに

毎日毎日、それこそ仕事で使わない日は無いくらい、いつもお世話になっている表計算ソフト。 昔はみんなExcelを使っていましたが、最近は社内でもGoogleスプレッドシートを使うことが増えているように感じます。 （因みに私はLotus 1-2-3やNumbersは使ったこと無いです。）

さて、皆さんいろいろな関数を駆使して作業を効率化していることかと思います。

代表的な関数をいくつか紹介すると、

  • SUM

    • 表計算ソフトの関数の代表格といえば何と言ってもSUMですかね。
    • 指定した範囲の合計値を求めてくれます。
    • 「=SUM(A1:A10)」といった形で、合計したいセルの範囲を指定してあげます。

  • COUNTIF

    • 条件に一致するデータの個数を求めてくれます。
    • シンタックスはCOUNTIF（範囲, 検索条件）です。
    • 「=COUNTIF(A1:A10, "<100")」といった形で、検索の対象とする範囲と、検索する条件を指定します。
    • 結果としては条件に合致したセルの数を返します。
    • 実は私、ほとんど使ったことないですが、よく聞くので紹介しました。

  • VLOOKUP

    • VLOOKUPを使えると表計算ソフトのスキルは中級くらいのレベルになりますかね。
    • この関数はホントに便利です。もはや無いと生きていけないレベル。
    • シンタックスはVLOOKUP（検索値, 範囲, 列番号, 検索方法）です。
    • 範囲で指定した先頭の列を上から順に検索して検索値に一致する値を探します。見つかったセルと同じ行の列番号にあるセルの値を結果として返します。
    • 検索方法はFALSEしか使ったことないです。

ちなみに、Googleスプレッドシートの関数の一覧はこちら。

Google スプレッドシートの関数リスト - ドキュメント エディタ ヘルプ

何十、何百の関数が用意されていて、使ったことも無い関数が殆どなのですが、自作のオリジナル関数を作ることも出来ます。

えっ、関数を自分で作る？そんな事できるの？と思われた方も多いかとも思いますが、私も最近知りました（笑）

では、これからオリジナル関数を自作する方法を紹介します。

消費税を求めるオリジナル関数を作ってみる

オリジナル関数、どう作るか？

そのためにはGoogle Apps Scriptでスクリプトを書く必要があります。

Google Apps Script（GAS）とは、Googleが開発・提供しているプログラミング言語です。

JavaScriptをベースに開発されたそうです。

スクリプトエディタを起動する

Googleスプレッドシートを開いたら、タブから「拡張機能」＞「Apps Script」を押下します。

f:id:enigmo7:20211210112651p:plain
スクリプトエディタを起動

新しいタブが追加されて、以下のようなスクリプトエディタが起動されます。

f:id:enigmo7:20211210113818p:plain
スクリプトエディタが起動

スクリプトを書く

スクリプトエディタが起動されたら、ここにオリジナル関数のスクリプトを書きます。

ここでは消費税を求める関数myTAX関数を作ってみます。

/* 消費税を返す関数myTAX */
function myTAX(price){
  const taxRate = 0.1; //消費税率
  return price * taxRate;
}

functionのあとのmyTAXは関数の名前です。

priceは、関数の引数です。この関数の引数は１つなので、このように指定します。

const taxRate = 0.1; //消費税率はtaxRateという定数を作成して、税率の0.1（10%）を代入してあげます。

そしてreturn price * taxRate;で引数で指定されたpriceに消費税率taxRateを掛けて、その結果をreturnで返してあげます。

スクリプトができたら、フロッピーディスクのアイコンをクリックして保存してあげます。 （いまの若い方はフロッピーディスクなんて知らないと思いますが、これでイメージ付くのかな？）

オリジナル関数の使い方

では、作った関数を使ってみましょう。

使い方は簡単で、標準の関数と同様に=の後に関数の名前と引数を指定するだけです。

f:id:enigmo7:20211210120142p:plain
オリジナル関数の使い方

はい、このように消費税の金額が求められました。

f:id:enigmo7:20211210120700p:plain
オリジナル関数の結果

B2のセルの右下の小さな四角を下に引っ張れば、B2のセルがコピーされて、下の行の消費税が算出されます。

f:id:enigmo7:20211210121320p:plain
関数のコピー

消費税の算出だけであれば、セルに直接=A2 * 0.1とすればできます。

ただ、オリジナル関数を作って消費税を算出するメリットは、消費税の税率が変わった時にスクリプトの消費税率を変えてあげるだけで、変更後の消費税が取得できます。

  const taxRate = 0.12; //消費税率

こんな感じですね。

ABテストの信頼度を算出するオリジナル関数を作ってみた

実は、というか当然ですが私は消費税を算出するオリジナル関数を作りたかったわけではありません。

我々は日常的にABテストを実施しています。

例えばクーポンを配布した時に、配布したグループと配布しなかったグループで注文率に差があるが、どの程度差がでるか、クーポンを配布することで注文は増えるけど利益はどうなのか？といったようなことです。

この時、以下のような結果が出たとしましょう。

ABテストではクーポンの配布を実施したグループをトリートメントグループ、クーポンの効果を検証するために敢えてクーポンを配布しないグループをコントロールグループと呼びます。

対象者 注文者 注文率
トリートメントグループ 179,120 1,677 0.94%
コントロールグループ 11,800 98 0.83%

この結果からクーポンを配布したトリートメントグループの注文率が0.94%とコントロールグループの0.83%を上回っているので「クーポンの効果があった」と判断したくなります。

ただ、世の中、誤差は付き物です。 サイコロを10回振った時に1が10回連続で出たとしても、必ずしもそのサイコロがイカサマとは決めつけられません。偶然10回連続で1が出る確率は少なからずあります。

同様にクーポンのABテストでも偶然トリートメントグループの注文率が高かった、ということは無くはないです。

そんな事言ったらABテストを行う意味がないではないか、というとそうではありません。 今回の結果が偶然なのか、それとも偶然ではないのかを統計的な手法を使って判断することが可能です。 その方法を統計の用語で検定と呼びます。

検定の方法はいくつかあるのですが、今回はカイ二乗検定という検定方法を使って検定を行います。

詳細は省略しますがカイ二乗検定の方法は、以下の順序で行います。

①実績値を集計

注文あり 注文なし 合計
トリートメントグループ 1,677 177,443 179,120
コントロールグループ 98 11,702 11,800
合計 1,775 189,145 190,920

②期待値を算出

注文あり 注文なし 合計
トリートメントグループ 1,665 177,455 179,120
コントールグループ 110 11,690 11,800
合計 1,775 189,145 190,920

③実績値と期待度数の乖離値を求める

④乖離値を合算してカイ二乗値を求める

⑤カイ二乗値からp値を求める

⑥p値から有意差を求める

一般的に有意差が95%を超えると有意な差がある、つまり偶然ではないと判断します。

このように有意差を求めるには多くの手順が必要となります。

そこでオリジナル関数の出番です。

今回は①〜④のカイ二乗検値を求めるところまでをオリジナル関数で行い、⑤〜⑥は標準で用意されている関数を使いました。

function myGETCHISQ(a, b, c, d){

  // 実績値
  var o = [
    a - b,
    b,
    c - d,
    d,
  ];
  
  var s = o[0]+o[1]+o[2]+o[3];

  // 期待値
  var e = [
    (o[0]+o[1]) * (o[0]+o[2]) / s,
      (o[0]+o[1]) * (o[1]+o[3]) / s,
        (o[2]+o[3]) * (o[0]+o[2]) / s,
          (o[2]+o[3]) * (o[1]+o[3]) / s,
            ];
  
  // カイ二乗検値
  var chisq = 0;
  for (i=0; i<=3; i++){
    chisq += (o[i] - e[i]) * (o[i] - e[i]) / e[i];
  }

  return chisq;
}

この関数のシンタックスは、以下のとおりです。

myGETCHISQ（試行回数A, 注文数A, 試行回数B, 注文数B）

上記の例だと、

=myGETCHISQ（179120, 1665, 11800, 98）

となります。もちろん引数にはセルの値を参照させることもできますよ。

この関数で求められたカイ二乗検値を標準で用意されているCHISQ.DIST.RT関数に与えてあげることでp値が求められます。

CHISQ.DIST.RT(カイ二乗値, 自由度)

そして、1からp値を引いて100を掛けてあげれば有意差が求まります。

因みにこの例の有意差は75.36%で95%を下回っているので、有意な差が出ているとは言えない、となります。

最後に

今回はGoogle Apps Script（GAS）を使ってGoogleスプレッドシートのオリジナル関数を作る方法を紹介しました。

また、より実践的な例としてABテストの信頼度を算出するオリジナル関数を作ってみました。

GASを使うと日頃の作業を上手に、そして簡単に効率化することが出来ます。

是非参考にしていただければと思います。

明日の記事の担当はデザイナーの本田さんです。お楽しみに。

株式会社エニグモ すべての求人一覧

hrmos.co