「表計算芸人」という病

先日の記事を書いてから、一応そこそこのリアクションを頂いています。ありがとうございますが、基本的に大道芸の側に近い事をしているので、技術として受け入れてもそんなに幸せにはなれないと思います。
それはさておきもうちょっと書きたいというか、「Googleスプレッドシートをお砂場に見立ててお砂あそびをしたい」という発作が引き続き出ているのでやります。

どうしてそのためにアンケートフォームまで作るんですか？

というわけでまずはお砂場に砂を入れるところから開始します。なんだこれ鉄腕DASHか？
フォームの回答はいつも通りスプレッドシートに出力して、これまたいつも通りに修正/集計だけを行う別シートを用意しています。今回は全容をさらけ出すために一部編集可能な状態にしてあります。多分コピーしたら色々おかしなると思うんですけど、この記事の全文読んで自分でも何かしたいと思ってしまう同じ病の人ならどうとでもすると思うので自己責任でお願いします。

勿論アンケートは答えられます。当たり障りのない事しか聞いてないのでレコード足しておいてください。

いつものシート（もしくは表計算芸人症候群の一般的な症例）

読み進める前に……

今このシートで実現しようとしていることは具体的に、「スプレッドシートとリンクしたGoogleフォームの回答結果をスプレッドシートをマスターレコードとして、そのレコードを破壊せずに管理者が修正したレコードを作る」というケースを想定しています。で、実はGoogleフォームの回答結果はcsvでエクスポートが可能なので、これをマスターレコードとして保存しておけます。また、リンクされたスプレッドシートはフォームの結果を流し込んでいるだけでその時点での回答とは全く同期していません。
つまり一番簡単な方法は「出力されたスプレッドシートを直に直す」です。

それでは話したいだけの表計算芸人の業の話を続けます。
お帰りはこちらです。

シート解説

方針の再確認

前にも触れたと思うんですが、目指す目標は「流し込まれるレコードに対して自動的に必要な分だけ計算して、いい感じに出力する」です。ここで言ういい感じには「空白のセルに余計な計算式を仕込んでおかない」、「メンテナンス作業として計算式をD&Dでコピーしに行かない」、「同じ計算をするならbyrow()やmap()でまとめて計算する」を指します。何度でも言いますが、Googleスプレッドシートにも構造化参照があれば一連のオペレーションにはほぼ価値がありません。はよせい。

逆ピボット

表計算のオペレーションにおいての七不思議に近いのですが、リスト形式の表をクロス形式にすることは容易なのに、クロス形式の表をリスト形式にすることはかなりの難題という困った現実があります。現代のExcelではPowerQueryを介してのピボット解除は比較的容易に出来るのですが、残念ながらGoogleスプレッドシートにはそんなものはなく、実際にそれをするのであれば拡張機能を探す、などの方法を活用することになります。まぁここでは素でやるんですが。やり方についてはarrayformula()を利用して多少強引にやります。ちなみにナレッジは既にあり、「google spreadsheet unpivot」や「googleスプレッドシート 逆ピボット」のような検索で結構出てきますし、思いっきり参考にしています。

というわけでまずはarrayformula()がどういった挙動をするかという話です。分かりやすい例をeg: arrayformulaシートに用意してあるので確認してみてください……。こういう感じで、行列のインデックスを使って一気に計算してしまえる関数なんですが、用途は比較的限られています。具体的にこの中にvlookup()あたりを書いてしまったら滅茶滅茶です、確か。
話を戻して、こうやって二次元配列を引き出せます。例えば行と列のインデックスを文字列として、これを文字列結合して、flatten()で一次の配列に畳めば……今回の例だと、タイムスタンプとフィールド名が結合した文字列のすべての組み合わせを出力させられます。これを二列に開いて、1列目にタイムスタンプ、2列目にフィールド名の行列にしたいです。それには区切りが必要です。じゃあ何か絶対に出てこなさそうな文字で区切って、split()でもう一度分割しましょう、というのがこの方法です。参考にした例では絵文字を使っていますが、今回はタブ文字を使っています。勿論スプレッドシートのセルの中でtabキーを押すと右のセルに移動するだけなので、ここではchar(9)を使っています。おそらくはgoogleフォーム中でもtabキーを押してタブ文字を入れるのは絵文字を入れる以上に難儀するはずなので好都合です。

という一連の作業をする計算式がunpivot!a2に入っています。確認してみて下さい。メンテナンス性と読みやすさを考慮してある程度インデントをしています。いつもの作業としてoffset()で空のセルを除外したり、メンテナンスのためにフィールドとして"除外"という要素を足したりしています。

さて、逆ピボットした票が出来上がりました。ただ、現状はレコードのユニークな番号としてのタイムスタンプとフィールド名が並んでいるだけなので、それに対応する要素をまず並べるのですが、こういう時はvlookup()を使うものです、で済むので省略します。他の逆ピボットの解説ではもうちょっと賢い方法を使っているはずなのですが、今回は元の表と生成する表のフィールドが合わないのでvlookup()で覗きなおしをしています。具体的には"除外"フィールドを増やしているので上手く行かないんです。
これで入力されたデータを確認しながら修正データを打ち込めます。それがしたいための一連の作業でした。あとは修正が必要な部分にのみ修正データを入れておき、「修正データが空じゃなければ修正データ、そうじゃなければ入力データ」を出力してください。答えは……いいでしょ？簡単よ？

除外と出力

先にも触れましたが、逆ピボットしたシートには本来の表が持っていない「除外」フィールドがあります。ここをtrueにしておけば加工後のシートにはそのレコードは出てこない様にしておきたいです。というわけでrejectシートでその下ごしらえをします。unpivotシートのレコードのうち、除外フィールドの値がtrueになっているタイムスタンプだけをfilter()で引き出します。あとはこのタイムスタンプをキーにして、preシートのレコードから同じようにfilter()でタイムスタンプ以外のフィールドを引き出します。念の為レコードを確認しながら除外理由をメモしておけるようにするためです。実際に使うのはタイムスタンプの部分のみです。

それではpostシートで修正したレコードを生成します。再度確認ですがタイムスタンプをユニークなレコードの値として使用しており、それはrejectシートのレコードでも共通です。というわけでpreシートのレコードのタイムスタンプの各値のうちrejectレコードのタイムスタンプに同様の値が見つかった場合は弾く、という計算をします。具体的にはmatch()で確認して、なければ#N/Aをかえしてくるのでisna()で確認しています。post!a2を確認してみてください。
ここまで来ればやることは全て一緒で、vlookup()して終わりです。更に分析をしたい場合は……僕の場合は別なスプレッドシートを用意してimportrange()でレコードを渡していますが、これはあくまでもアクセスコントロールのための手順です。

特に〆の話もなく、これで終わります。
あとこの病気が治るいい病院があったら教えてください。

P.S.: ちょっといいアドオンの話

さり気にGoogleスプレッドシートは必要のない列は消せてしまいます。よく$a$2:$aみたいなrangeの表記をするので、filter()で舐める時に余計な空白はない方がいいよなぁと思っているので積極的に消しているのですが、それをするのにちょうどいいアドオンがあるのでご紹介しておきます。

P.P.S.: Googleさんへ

これでよく書いてるfilter($a$2:$a, $a$2:$a<>"")とか、offset($b$2:$e, 0, 0, counta($a$2:$a))みたいなやつ、手早く書ける関数作りません？自作関数？いちいちインポートめんどくさいんですよ。