はじめに
こんにちは、生産プラットフォーム開発本部のstakmeです。
本稿では、スプレッドシートの作業に「手続き的なアプローチ」と「宣言的なアプローチ」という観点を持ち込み、ふたつを対比しながら紹介します。Google Sheetsの多彩な関数を駆使して、日常的な問題に効率的に対応するための具体的なテクニックやヒントを提供します。また注意点やリスクを指摘し、スプレッドシートをより強力に活用するための知識を提供します。
目次
背景・課題
筆者は生産プラットフォーム開発本部に所属するソフトウェアエンジニアです。弊本部は「Made by ZOZO」を支えるシステム開発に従事しています。筆者の役割は、BigQueryを中心とする事業データ利活用の基盤を整えることを通じて、目標達成に向かうビジネス上の意思決定を支援することです。
直近の案件において、「人間による入力」に対する「システムによる評価フィードバック」をすばやく得ることで意思決定を効率化したいというケースがありました。入力インタフェース（手での入力やコピーペースト）の強力さ、フィードバック反映のスピードなどから判断すると、Google Sheetsが最適と考えられるケースでした。
Google Sheetsは非常に強力なスプレッドシート製品であり、データの入力、蓄積、加工、表示という一連の流れを単体で処理できます。ただし今回のように不定件数の入力データを加工するときには、若干の「慣れ」が必要となります。素朴に関数をコピーペーストして実装すると、データ増加に伴って関数がコピーされていないセルが生まれ、処理が壊れてしまうのです。
振り返ってみると、ここで筆者が苦しんだ原因は「何を実現したいのか」ではなく「どのセルでどのような演算をするか」という抽象度の低い内容をそのまま記述してしまった点にあると思います。プログラミングでいうと、for文を使わずに何千回もメモリを直接操作しているような状態でしょうか。そう喩えてみると、いかにも壊れやすそうですね。プログラミングでは避けられることがスプレッドシートでは上手く回避できない、という部分にこの「慣れ」の要素があると思います。
本稿の目的
本稿は、そのようなスプレッドシートにおける「慣れ」の実体を叙述するために、プログラミングの領域からふたつの概念を借用します。
- 抽象度が低く、人間の管理に依存する、手続き的に構築されたスプレッドシート
- 抽象度が高く、データ構造に依存する、宣言的に記述されたスプレッドシート
両アプローチを比較しながら、具体的なGoogle Sheetsの利用テクニックを紹介します。この紹介を通じて、読者はGoogle Sheetsにおける実装の特徴について言語化する術を獲得し、場面によって適切なアプローチを意識的に採用できるようになるでしょう。
この目的を達成するため、手元で試して学べるサンプルを多く含めるようにしました。実際にGoogle Sheetsの動作を試しつつ、読み進めることをおすすめします！
規則的な処理を繰り返すケース
例えば以下のように、「A1の日付（2023/01/01）を基準として、その0日後、7日後、14日後… という日付を6回だけ生成して表示したい」とします。
手続き的に構築された例
このシートのB列は、下記のような関数で表現できるでしょう。
=A1 =B1+7 =B2+7 =B3+7 =B4+7 =B5+7
7日ずつ日付を進めたいので、+7を繰り返し記述しています。これを6回だけ行いたいので、6個のセルで関数を実行しています。とてもシンプルですが、誤って途中のセルを削除すると次の図のようになります。
削除したB3セルが空欄になるだけでなく、それ以降の日付は1900年1月になってしまいました1。あきらかに意図と異なる値ですが、日付データそのものは生成されてしまっており、問題に気づきにくい状態です。
宣言的に記述された例
最初に具体例を示します。B1セルにこのように入力してください。
=ARRAYFORMULA(A1 + SEQUENCE(6, 1, 0, 7))
このときB2からB6セルは空欄としてください。そうすると、B列に先ほどと同じ日付が表示されます。
この例に含まれている関数について、ひとつずつ説明します。
SEQUENCE
まずはSEQUENCE(6, 1, 0, 7)という部分です。SEQUENCE関数は、連続する数値を生成します。SEQUENCE(6, 1, 0, 7)は、縦に1列で0, 7, 14, 21, 28, 35という6つの数値を生成します（下図のA列）。
設定を変えると、ゼロではない数値から始めたり、複数の列にわたる数値も生成できます。たとえばSEQUENCE(6, 3, 700, 7)を実行すると、下図のC列からE列のようになります。実際にさまざまな設定を試してみると、仕組みがよく分かると思います。
ARRAYFORMULA
次にARRAYFORMULAです。ARRAYFORMULA関数を理解するために、まずは下記の3つの例を確認してください。
={10; 20; 30}
=100 + {10; 20; 30}
=ARRAYFORMULA(100 + {10; 20; 30})
{10; 20; 30}という記述は「縦に並んだ10、20、30という数値の組み合わせ」を表しています（1つのセルにこれを記述すると、そこから下に3つの数値が表示されます）。このような構造を配列（Array）といいます。配列と数値は異なるものであり、100 + {10; 20; 30}というような足し算はできません。配列という特別な構造を踏まえつつ、その中身をひとつずつ処理するにはARRAYFORMULA関数を利用します。
こちらも言葉で説明すると複雑ですが、実際の動作を比べるとARRAYFORMULA関数の役割が分かると思います。
関数の組み合わせ
このふたつを組み合わせた=ARRAYFORMULA(A1 + SEQUENCE(6, 1, 0, 7))は、結局こんな処理を意味しています。
①最初の状態
ARRAYFORMULA(A1 + SEQUENCE(6, 1, 0, 7))
②SEQUENCE関数により、縦に並んだ6つの数値を生成
=ARRAYFORMULA(A1 + {0; 7; 14; 21; 28; 35})
③ARRAYFORMULA関数により、縦並びの構造を保ちながらA1との足し算を実行
={A1+0; A1+7; A1+14; A1+21; A1+28; A1+35}
④結果を表示
ちなみに、MAP関数という別の関数を利用して=MAP(SEQUENCE(6, 1, 0, 7), LAMBDA(i, A1 + i))などと書くこともできます（結果の見た目は同じです）。
なぜ「宣言的」なのか
もともとこのケースは、「A1の日付を基準として、その0日後、7日後、14日後… という日付を6回だけ生成して表示したい」という明確な意図があると想定していました。手作業で=B2 + 7と書いたり消したりしたのは、この意図をすばやく実現するための手続きを進めていたにすぎません。人間のミスは、このような手続きにおいて発生するものです。
だとすると、人間が手続きをやめれば、手続きのミスは問題しなくなるはずです。
人間は「こういう処理をしたい」と宣言だけして、あとの処理はスプレッドシートに任せてしまう。そうすれば、手続きでミスを起こして壊れる余地はなくなります。また、スプレッドシート作成者の意図がほかの人々にも理解しやすくなるでしょう。そこで本稿はこうしたアプローチを「宣言的な記述」と呼び、人間がデータ処理の流れをひとつずつ実装する「手続き的な構築」と対比することにしました。
データが徐々に増えるケース
データが増えるたび、なんらかの処理を行いたいケースを考えます。
例えば、以下のように「処理済みのID」と「チェック対象のID」それぞれのリストがあります。あるIDが処理済みリストに含まれるかをVLOOKUP関数で調べたいとします。「チェック対象のID」はどんどん増えると想定します。
手続き的に構築された例
データが増えるたびにVLOOKUPをコピー&ペーストすることで対応できます。
=VLOOKUP(C2, $A$2:$A$4, 1, false) =VLOOKUP(C3, $A$2:$A$4, 1, false) =VLOOKUP(C4, $A$2:$A$4, 1, false) ...
宣言的に記述された例
手続き的なアプローチは、対象のIDが増えるたびに手作業が必要です。ただし、毎回まったく新しい作業をするわけでもありません。C2, C3, C4, ...という部分だけ入れ替え、同じことを繰り返しています。
ということは、{C2; C3; C4; ...}という無限に続く配列があれば、ARRAYFORMULA関数を利用して一気に処理できるはずです。Google Sheetsでは、このような配列をC2:Cと表現できます。C2:Cは「C2からC列の最後まで」という意味です。ここでは、以下のように記述できます。
=ARRAYFORMULA(VLOOKUP(C2:C, $A$2:$A$4, 1, false))
これを実行すると、下図E列のようになります。
今はまだチェック対象のIDが存在しない行も含めて、C2より下にあるすべてのIDをチェックできています。
より読みやすい表示を実現するのであれば、IF関数、ISNA関数、NOT関数を利用して改良できます。
=ARRAYFORMULA(IF(C2:C, NOT(ISNA(VLOOKUP(C2:C, $A$2:$A$4, 1, false))), ""))
別の見せ方でデータを表示したいケース
すでにあるデータを、別の見せ方で表示したいことがあります。例えば、作成済みのテーブルの列を入れ替え、名前も変えて表示したいとします。
手続き的に構築された例
値をコピーして、手で入れ替え、「会員番号」「名前」と入力するだけです。今後のデータ更新を考慮しない場合であれば、この方法で十分です。
宣言的に記述された例
常に最新のデータを異なる見せ方で表示したい場合は、単純なコピーでは対応できません。QUERY関数を利用することで「同じデータを、違う順番で、異なるラベルをつけて表示する」という処理を宣言的に記述します。
=QUERY(A1:B, "select B, A label A '名前', B '会員番号'")
これをD1セル（D2ではないことに注意してください）に記述し、E1セルを空欄にすると、下図のようになります。
QUERY関数を利用すれば、必要な列だけを再利用したり、特定条件のある行だけに絞り込んだり、データを加工したうえで表示したりできます。ただし、絞り込みや加工の自由度はあまり高くありません。FILTER関数やSORT関数などの関数と使い分けるとよいでしょう。場合によっては、現在のデータをそのまま表示する={A1:A}のような書き方が有効というケースもあるかもしれません。
なおQUERY関数の生成する結果はそのままARRAYFORMULA関数に渡すことができます。この組み合わせに慣れると、さまざまな処理を宣言的に記述できるようになります。
=ARRAYFORMULA(QUERY(A1:B, "select B where A = 'Alice'", 0) + 100)
やりすぎのケース
すでに明らかかもしれませんが、単純なコピー&ペーストで済む場面であれば、あえて複雑に実装する必要はありません。実装時のコストと必要性を天秤にかけて、適切な方法を選択することが重要です。その点に注意を促すため、実装・読解コストが高いと思われる例を紹介します。
数値の合計値を行ごとに求めて、右側のF列に表示したい場合を想定してください。
手続き的に構築された例
スプレッドシートに慣れた人であれば、迷うことはないと思います。
=sum(A1:E1) =sum(A2:E2) =sum(A3:E3)
意図が明瞭であり、非常にシンプルです。
宣言的に記述された例
宣言的に記述するなら、このような書き方ができるでしょう。
①SUMIFパターン =ARRAYFORMULA(SUMIF(IF(COLUMN(A1:E1), ROW(A1:A1000)), ROW(A1:A1000), A1:E1000)) ②MMULTパターン =ARRAYFORMULA(MMULT(A1:E3, SEQUENCE(COLUMNS(A1:E3), 1, 1, 0)))
どちらも手続き的な記述より複雑です。なにより大きな問題は、実装の都合によりSUM関数が使われていないことです。SUM関数が存在しないため、「行の合計値を求める」という人間の意図が曖昧になっています。少なくともSUMIFパターンは設定すべきパラメータも多く、本当に必要なケースでしか利用すべきではありません。
このようなケースでは、シンプルさを重視して手続き的に構築することも合理的な選択肢です。宣言的に記述するとしても、MMULTパターンを名前を付けた関数として定義し、処理の意図をあきらかにしたうえで利用することをおすすめします（たとえばSumByEachRowなど）。
まとめ
本記事では、Google Sheetsにおける処理アプローチを言語化するために「手続き的」「宣言的」というふたつの概念を持ち込み、それぞれの具体例や関数のリファレンスを示しました。これらが読者の皆さんの選択肢に加わり、スプレッドシートの利用がより効率的になることを願っています2。
ZOZOでは、一緒にサービスを作り上げてくれる方を募集中です。Made by ZOZOを支えるソフトウェアエンジニアのポジションも募集しています (Go, TypeScript)。ご興味のある方は、以下のリンクからぜひご応募ください！
- Google Sheetsが内部的に「1899年12月30日＝ゼロ」として管理していること、空白セルを数値として扱うとゼロになることが原因のようです。前者については、Lotus 1-2-3互換に関連する挙動であるという説明もあります。↩
- 本稿における「手続き的・宣言的」という単語借用のアイデアについては、VisiCalcに対するテッド・ネルソンのコメントから刺激を受けました。“Where conventional programming was thought of as a sequence of steps, this new thing was no longer sequential in effect” (Nelson, T. (1989). In S. Brand (Ed.), Whole Earth Software Catalog for 1986 (p. 66). Quantum Press/Doubleday.)↩
