Googleスプレッドシートの数式でExcelのパワークエリみたいに表を加工する（その2）

その1はこちら

その3はこちら

今回はデモ用に作ったスプシの数式の解説。

パワークエリもどき

全部でシートが4つあるが、見てほしいのは「まとめ」シート。数式はA1セルにしか入っておらず他は全てスピルになっている。
このまとめシートには、「注文テーブル」「顧客テーブル」「商品テーブル」の情報を統合して、累計購入金額が多い順に顧客を表示するという数式が組んである。

=let(
tblトリミング,lambda(tbl,transpose(filter(tbl,choosecols(tbl,1)<>""))),
注文tbl,tblトリミング(tblトリミング('注文テーブル'!A:AZ)),
顧客tbl,tblトリミング(tblトリミング('顧客テーブル'!A:AZ)),
商品tbl,tblトリミング(tblトリミング('商品テーブル'!A:AZ)),
列番号,lambda(tbl,name,xmatch(name,chooserows(tbl,1))),
列,lambda(tbl,name,choosecols(tbl,列番号(tbl,name))),
接続用商品tbl,byrow(列(注文tbl,"商品ID"),lambda(row,xlookup(row,列(商品tbl,"商品ID"),商品tbl))),
統合,hstack(注文tbl,接続用商品tbl),
重複列削除,choosecols(統合,arrayformula(列番号(統合,unique(chooserows(統合,1),1)))),
価格列,byrow(重複列削除,lambda(row,choosecols(row,列番号(重複列削除,"単価"))*choosecols(row,列番号(重複列削除,"注文個数")))),
価格列ヘッダー修正,vstack("価格",query(価格列,"OFFSET 1",0)),
価格列追加,hstack(重複列削除,価格列ヘッダー修正),
顧客tbl成型,filter(顧客tbl,choosecols(顧客tbl,1)<>""),
購入額集計列,byrow(顧客tbl成型,lambda(row,iferror(sum(filter(列(価格列追加,"価格"),列(価格列追加,"顧客ID")=choosecols(row,列番号(顧客tbl,"顧客ID")))),0))),
購入額集計列ヘッダー修正,vstack("購入額集計",query(購入額集計列,"OFFSET 1",0)),
購入額集計列追加,hstack(顧客tbl,購入額集計列ヘッダー修正),
ヘッダー,chooserows(購入額集計列追加,1),
本体,query(購入額集計列追加,"OFFSET 1",0),
降順ソート,sort(本体,列番号(購入額集計列追加,"購入額集計"),0),
ヘッダー統合,vstack(ヘッダー,降順ソート),
ヘッダー統合
)

全体のざっくり構成はこんな感じになっている。

• 1～7行目：データソースを取得したり後から使う関数を定義したりという前準備
• 8～12行目：注文テーブルと価格テーブルの結合
• 13～15行目：顧客ごとの合計購入額の集計
• 16～19行目：合計購入額で降順ソート
• 20行目：最終出力

一行ずつ内容を解説していくので少しでも雰囲気が伝わればいいなと思う。

各行解説

1行目

=let(

前回説明したとおり、全体をLET関数で囲んでいる。基本的に各行が奇数引数で始まるように改行してある。

2行目

tblトリミング,lambda(tbl,transpose(filter(tbl,choosecols(tbl,1)<>""))),

いきなりややこしい数式が出てきて恐縮だが、ここではLAMBDA関数を使って今後使うある関数を定義している。考え方としては、

1. 別のシートからデータを引っ張ってきて配列として使用する場合、空白を含むシート全体をそのまま使うのではなくちゃんとデータが入っている必要十分な行数と列数のみを使いたい
2. しかしいちいち対象のシートを確認して「'注文テーブル'!A1:E20」などと具体的なセル範囲を入力するわけにはいかないし、シートの中の値が入っている範囲だけを取得する関数はスプレッドシートには存在しない
3. そこで、指定した範囲から空白を除去して使用されている範囲のみをトリミングするという操作を行う関数を自作した

ということになる。
つまり、まずテーブルをトリミングする関数を定義して（2行目）、それを使ってソースデータを必要な行列数にトリミングする（3～5行目）、というのが最初にやりたいことである。

で、その関数の中身についてだが、引数であるtblに対して、

1. filter関数を使って1列目が空欄となっている行を取り除く
2. フィルター後のテーブルの行と列を入れ替える
   という意外とシンプルな操作になっている。
   実際に使うときはこの操作を必ず2回重ね掛けすることで行と列の両方に対して空欄を取り除く処理を行う。

filter関数には列方向にフィルタリングする機能がないため、一度行列を入れ替えてから同じ処理をかけ、また行列を元に戻す、という力業でこのような形になった。
今後スプシにもExcelでいうスピル範囲演算子（#）やトリム参照のような機能が追加されればこんなまどろっこしいことはしなくて済むのだが…今のところはこの形が一番シンプルな気がしている。

3～5行目

注文tbl,tblトリミング(tblトリミング('注文テーブル'!A:AZ)),
顧客tbl,tblトリミング(tblトリミング('顧客テーブル'!A:AZ)),
商品tbl,tblトリミング(tblトリミング('商品テーブル'!A:AZ)),

前の行で定義したtblトリミング関数に今回のソースデータである3つのシートを突っ込む（×2回ずつ）。なお、参照する際に存在しない行まで含めても特にエラーは出ないようなので、自分はいつもA～AZ列というかなり広めな範囲を入れている。

6行目

列番号,lambda(tbl,name,xmatch(name,chooserows(tbl,1))),

ここではテーブル名と列名を引数とする関数を定義している。これにより、例えば

列番号(注文tbl,”注文日時”)

と書けば注文tblの中で注文日時という列が何列目にあるのかを取得することができる。テーブルを加工するにあたっては個別の列を指定する場面がかなり多いため、これから多用していくことになる。

7行目

列,lambda(tbl,name,choosecols(tbl,列番号(tbl,name))),

前の行で定義した列番号関数は列番号、すなわち数値を取得する関数だったが、この列関数は同じ引数から配列としての列そのものを取得する。これも列番号関数と併せてかなり多用しているお気に入り関数。

8行目

接続用商品tbl,byrow(列(注文tbl,"商品ID"),lambda(row,xlookup(row,列(商品tbl,"商品ID"),商品tbl))),

ここから実際にテーブルを加工していくことになる。まずやろうとしていることは注文tblと商品tblの接続。注文tblの右の方に列を追加し、商品IDから商品情報を参照して表示するというもの。
で、この行はそのための下準備として、商品tblの内容を注文tblの右にそのままくっつけられるように並べ替えるのが目的。
スプレッドシートで主キーを使ってテーブル同士を接続する際はxlookup関数がよく使われる。
この行ではxlookup関数をbyrow関数で囲って使用することで、注文tblにおける各行に対して個別にxlookup関数をかけるということが可能になる。
なお、xlookup関数の第3引数は特定の列を指定することが多いと思うが、ここではテーブル全体を指定することで、戻り値として行全体が取得できるようにしてある。

9行目

統合,hstack(注文tbl,接続用商品tbl),

前の行で作成した接続用商品tblを、元々の注文tblの右に並べる。これで注文tblと商品tblをSQLでいうところの左外部結合したものが作れた。

10行目

価格列,byrow(統合,lambda(row,choosecols(row,列番号(統合,"単価"))*choosecols(row,列番号(統合,"注文個数")))),

次にやりたいのは、単価×注文個数で注文ごとの合計金額を計算して新たに「価格」という列を作ること。
ここでも8行目と同様byrow関数を使って、各行に対して個別に計算を行っていく。
choosecols関数が入っていてわかりにくいが、これは行ごとに処理を行う際に単価列と注文個数列を動的に指定しているだけで、計算としてはただの単価×注文個数の掛け算。

11行目

価格列ヘッダー修正,vstack("価格",query(価格列,"OFFSET 1",0)),

前の行で各行に対して単価×注文個数で計算した「価格列」を作ったが、ここである問題が発生する。
スプレッドシートの数式内で二次元配列（列全体を配列として参照する際、内部的にはn行×1列の二次元として扱われるらしい）を扱うにあたってはヘッダーという概念がないため、ヘッダー行についても本体と同様の掛け算処理を行ってしまい、ヘッダー行だけエラーになってしまうのである。
そこで、配列の1行目だけ削除することでヘッダーを削除し、代わりに新しいヘッダーを付加するという操作を行うのがこの行の目的。

個人的にはあまりquery関数が好きではないのだが、2次元配列の1行目だけ削除するという操作を一番シンプルに実現できるのがquery関数の「OFFSET 1」であるため、この操作を行うときはお世話になっている。
query関数で1行目だけ削除した配列にvstack関数で「価格」という文字列を追加することで、新しいヘッダーを付けている。

12行目

価格列追加,hstack(重複列削除,価格列ヘッダー修正),

前の行でめでたく価格列のヘッダーも修正できたので、hstack関数でくっつけて一つのテーブルにまとめる。

13行目

購入額集計列,byrow(顧客tbl,lambda(row,iferror(sum(filter(列(価格列追加,"価格"),列(価格列追加,"顧客ID")=choosecols(row,列番号(顧客tbl,"顧客ID")))),0))),

前の行までで「どの注文でいくらの購入が発生したか」が一つのテーブルにまとめられた。次に顧客ごとに「誰が合計いくら購入したのか」を集計したい。
普通にスプレッドシートでこの集計を行う場合はsumif関数などを使うのが一般的だと思う。
しかし、ここでsumif関数を使ってみるとなぜかうまくいかなかった（byrow関数や自作の列関数など色々組み合わせているので何か相性が良くなかったのかも）ので、filter関数で該当行を抽出してからsum関数で合計するという若干回りくどい手法をとってある。
（配列を扱う関数とセル範囲を扱う関数で挙動が違う？みたいな話を聞いたことがある）
なお、一度も購入していない顧客の行はfilter関数で何も引っかかってこなくてエラーになるため、sum関数をiferror関数で囲って一度も購入していない顧客の行には0が入るようにしている。

14、15行目

購入額集計列ヘッダー修正,vstack("購入額集計",query(購入額集計列,"OFFSET 1",0)),
購入額集計列追加,hstack(顧客tbl,購入額集計列ヘッダー修正),

前の行で作成した新たな列はそのままだとヘッダーに0が入ってしまっているためヘッダーを修正する。それが終わったら元のテーブルの右側にくっつける。数式の内容としては11、12行目と同様なので割愛。

16～19行目

ヘッダー,chooserows(購入額集計列追加,1),
本体,query(購入額集計列追加,"OFFSET 1",0),
降順ソート,sort(本体,列番号(購入額集計列追加,"購入額集計"),0),
ヘッダー統合,vstack(ヘッダー,降順ソート),

最後に購入額が大きい順に降順ソートをかけたいのだが、そのままsort関数をかけてしまうとヘッダーも含めたテーブル全体をソートしてしまう。
そこで、まず16行目でヘッダーのみを、17行目で本体のみを分離してそれぞれ定義する。
そして18行目で本体のみにソートをかけ、先に分離したヘッダーを19行目で再びくっつける。
このように、場合によってはただソートをかけるだけなのに4行も使ってしまうのはちょっと不便ではある。

20行目

ヘッダー統合

一応前の行で計算処理自体は終了するので、前の行における一つ目の引数で名前を指定せずに直接関数を入力しても結果は変わらない。
しかし、すべての計算処理に名前を付けておき、最終引数では名前だけを指定するというこの形にすることで、最終行の名前を書き換えればデバッグができるという利点がある。
この辺は好みの問題もありそう。

作成時のデバッグなど

この長さの数式を一息で書ききるのはさすがに現実的ではなく、1行ずつデバッグしながら順番に書いていく。
最終行の説明で少し触れたが、すべての計算処理に名前を付けておくことでデバッグが少しだけしやすくなるのでおすすめ。

例えば上記の式が完成したあとにデータソースの変更などに伴って何らかのエラーが出た場合、20行目だけを「ヘッダー統合」から「価格列追加」（12行目で定義した名前）に変更してみる。
ここで同じようにエラーが出れば、12行目よりも前の何らかの処理でエラーが発生していることになるし、エラーが出なければその後の何らかの工程が原因と考えられる。

なお、保守性の観点から理想を言うと本来は1処理1行にしたいところではある。
例えば11行目のヘッダー修正の処理は、①query関数を使って1行目を削除するという処理と②それに対してvstack関数で新しい1行目を付加するという処理に分けられる。
こういうのは場合によっては①と②の行を分けた方が可読性が上がると思う。
今回は少しでも行数を減らしたいというのと、合わせて一つの処理と見なせそうだったので同じ行で完結させた。

余談だが、こういう数式を作るときに一番不便なのはコードの中にコメントが残せないことだなと思う。実は結構プログラミング的な処理を行っているので、初見だと解読にかなり時間がかかりそう。まあそれはパワークエリも同じだが…

ヘッダー

もう一つ、パワークエリと違って不便なのが、スプシのほとんどの数式がヘッダー行を認識せず全部まとめて配列として扱ってしまう点。
唯一QUERY関数はヘッダー行を認識して2行目以降についての操作も可能だが、すべての操作をQUERY関数だけで賄えるほどの汎用性はない。
今回は途中でヘッダーと本体を分けて、本体にソート処理してから再度くっつけるという操作をしたが、場合によっては最初から最後までヘッダー込みでも問題ない場合や、一番最初にヘッダー分離してごちゃごちゃ操作してから最後にヘッダーをつけた方が良い場合も考えられる。

まとめ

めちゃくちゃ長くなってしまったが1行ずつ解説してきた。
おそらくこの説明を読んでもわからない人はわからないし、わかる人はむしろこんなの読まなくてもわかる人なのではないかという気もするが…

前回の冒頭で全3回くらいを予定していると書いたが、最終回の次回はこんな感じのテーブルの処理を行う際によく使う数式ブロックをまとめたチートシート及び各数式の軽い解説を作成して終わろうと思う。
ものぐさな自分はこういうのをちゃんとまとめることはしてこず、今は自作関数などをGoogle Keepにベタ打ちしてあって毎回コピペしているので、ほぼ自分用の備忘録である。
それではまた。