プログラマーのためのスプレッドシート関数入門

学校法人角川ドワンゴ学園職員の @ohataken です。N高等学校・N中等部の生徒たちにプログラミングの楽しさを伝える仕事をしています。以前はいわゆるRailsエンジニアというやつでしたが、最近はアプリケーションを作ることは少なく、せっかく学校法人に所属してプログラミング教育やってるということで学問的な領域をやろうと思い、競技プログラミングやったり数学やったりしています。

プログラマーは別に表計算ソフトに詳しいわけじゃない

さて、弊学の教職員もふつうの会社と同じくGoogle スプレッドシートで情報を記録したり共有したりしています。私はというとスプレッドシートの知識は高校生のころ情報の授業程度の知識しかなかったので都度都度ググって解決していたのですが、これは見通しが悪いので、よくあるプログラミング言語の解説書の流れでエッセンシャルを列挙しようと思いました。

文字列 結合演算子 &

= "Hello"&" "&"world!" 

文字列結合が + ではないものに割り当てられるのは、一周回ってイマドキな感じがする。

文字列 多変長引数関数で結合する CONCATENATE

= CONCATENATE("a", "b", "c", "d", "e", "f", "g")

文字列長を取得する LEN

= LEN("0123456789")

文字列のスライス MID

= MID("0123456789", 1, 3)

LEFT, RIGHTという関数もあるらしい。

数値型 和


= 0 + 1

数値型 差

= 2 - 1

数値型 積

= 2 * 3

数値型 商

/ を使うと float っぽくなってしまう。ここJavaScriptっぽい。

= QUOTIENT(10, 3)

数値型 剰余

上に同じく / を使ってしまうと float っぽくなってしまう。

= MOD(10, 3)

論理型 NOT

= NOT(FALSE)

ふつう

論理型 AND


= AND(TRUE, FALSE)




論理型 OR


= OR(TRUE, FALSE)




IF 関数

= IF(TRUE, "Yes", "No")

IFが関数なのはLispみがある。

比較演算子

= 1 = 1

イコールは代入に使われないので、比較演算子に使うことができる。ここだけ取り出して見るとものすごい違和感があるが。

ここまででFizzBuzzを書く

これをオートフィルするとFizzBuzzができます。

= IF(MOD(ROW(A1), 15) = 0, "FizzBuzz",
    IF(MOD(ROW(A1), 3) = 0, "Fizz",
      IF(MOD(ROW(A1), 5) = 0, "Buzz", ROW(A1))
    )
  )

読みやすさのために改行とインデントをしていて、こういうことが許されるのは意外な気がしますが便利なのでOKです。しかし、ただ条件分岐しているだけなのにコールバック地獄みたいになるのはしんどい。

IFS を使ってFizzBuzzを書く

と思ったらIFSがあった。

= IFS(
  MOD(ROW(A1), 15) = 0, "FizzBuzz",
  MOD(ROW(A1), 3) = 0, "Fizz",
  MOD(ROW(A1), 5) = 0, "Buzz",
  TRUE, ROW(A1)
)

オートフィルのキーボードショートカット

10,000行のオートフィル、スクロールしてボーッと待っていたこともありました。

1. オートフィル元のセルを選択して、コピー
2. Shift + Ctrl + ↓ で底まで選択
3. Ctrl + V で貼り付け (するとオートフィル扱いになる)

2次元配列のポインタ操作系

スプレッドシートには変数も関数もないけど2次元配列はあって、添字のようなものでアクセスできる。

2次元配列の添字呼び出し

いちばん左上のセルを、つまり[0][0]を呼び出す例。


= INDIRECT(ADDRESS(1, 1))

多くのプログラミング言語が0-basedなのに対して、スプレッドシートは1-basedなので (1, 1) となるところまではいい。だが INDIRECT(ADDRESS(1, 1,)) は冗長すぎるだろ、と今のところは思っていただく。

2次元配列の添字呼び出し省略記法

= A1

A1表記はたしかに2次元配列のDomain Specific Languageみたいなものと考えれば合理的だけど、C言語的なポインタ操作にはなじまないので、プログラマーの発想をするなら INDIRECT(ADDRESS(1, 1)) みたいにしたほうが整数演算でセル位置を指定できて自然だと思う。前項の ADDRESS は 1, 1 を A1 に読み替える関数で、 INDIRECT はポインタを解決する関数。

じゃぁなんでA1表記が一般的なのかというと、オートフィルが高機能すぎてA1表記をしたほうがオートフィルの運用に沿うというから。

セルから行番号を求める

C言語の & のようなイメージで、つまり変数からアドレスを求めたい時に使う。

= ROW(A1)

セルから列番号を求める

= COL(A1)

例 対角方向のオートフィル

= INDIRECT(ADDRESS(ROW($A$1), ROW(A1)))

これを縦にオートフィルすると、 ADDRESS の列成分だけが増えていくので、行と列を読み替えたい時にイディオムとして便利。

まとめ

スプレッドシートについてよく知らないうちから、たとえばいきなりVLOOKUPするのって気持ち悪くないですか？いわゆる「役に立つ」関数を単語帳的に覚えていく前に「スプレッドシートもまぁプログラミング言語みたいなものか」と感じておいたほうが気持ちいいのではないでしょうか？(実際、エクセルはチューリング完全らしいし)

プログラマーはほかのスプレッドシートのユーザーに対してデータモデリングの知見が当然優れているので、スプレッドシートについてプログラマーになにか質問するとしたら「こういうふうに整理したくて、こういうふうなシートにしているんだけどどう思う？」みたいな聞き方をするほうが、正直お互いのために幸せだと思います。

あと「スプレッドシートで管理」とかいうフレーズはよく耳にしますが、私は(おそらくあなたも)それは無理ある技術選定だと考えていて、それでもそこをある範囲で解決できないかと考えたりしています。書けたら書きます。