弊社では最近までグループウェアにMicrosoft365を使っていたが、先日GoogleWorkspaceに乗り換えた。
自分はエンジニアでも何でもないが、今までパワークエリヘビーユーザーだった。
ありとあらゆる表をパワークエリに突っ込んで加工してきたと言っても過言ではない。
GWSに移行したことで全体的に出来ることが増えて満足ではあるが、パワークエリにあたる機能がないことだけが不満。
LookerStudioなどで事足りることも多いのかもしれないが、スプシの形になっていた方がその結果をもとにGASでメールを送ったりできて何かと便利。
そこで、スプシの数式だけでパワークエリのように非破壊的な表の加工ができないかやってみたのがこちら。
全部でシートが4つあるが、見てほしいのは「まとめ」シート。
「注文テーブル」「顧客テーブル」「商品テーブル」の情報を統合して累計購入金額が多い順に顧客を表示するという数式が組んである。
で、このシートはA1セルにしか数式が入っておらず他は全てスピルになっている。
意外とそれっぽくなったが、あんまり同じようなことやってる人の話を聞かない。
そしてびっくりするくらい数式が長くなってしまって自分以外の人が見たら意味不明だと思う。
（多少の手動コピペや補助列とか使えばもっと初心者にもわかりやすくなるんだろうけど…やはり全てをプロシージャルに一つの数式で完結させるのは男のロマンである）
なので自分用の備忘録兼後任へのスキル継承用に初Qiita投稿してみる。
長くなりそうなので全3回くらいを予定。初回はさわりだけ。
パワークエリの構造
もっとちゃんとした解説はネット上に無限にたくさんあるので関連する部分だけをざっくりと。
パワークエリではユーザーがGUI上でソートしたり列の加除をしたりといった表の加工を行い、その加工の手順が内部的に記録される。
元データに更新があった場合にもこの記録された手順通りに表が加工されるため、元の表が更新されたときにデータの体裁を変えずに中身だけ更新できるという仕組み。
加工の手順はこんな感じのM言語の関数という形で記録され、最終的には処理nの結果が出力される。
=LET(
テーブル名,元テーブルのパス,
処理1,第一段階の加工の数式(引数:テーブル名),
処理2,第二段階の加工の数式(引数:処理1),
…
処理n,最終段階の加工の数式(引数:処理n-1),
処理n
)
全体を囲んでいるLET関数という関数が土台となる。
LET関数は途中経過の数式に名前をつけて再利用できるという関数である。
前から（この式の場合は改行しているので上から）順番に実行されていくので、
以下のような流れで次々と数式による表の加工を「重ね掛け」していく。
- 上記の式で言うと、まず1つ目の引数で最初に「テーブル名」という名前を定義する
- この「テーブル名」の中身を表すのが次の第2引数。テーブルのパスを記載する
- 第3,4引数（2行目）も同様。まず第3引数の中で「処理1」という名前を定義する
- 第4引数では「処理1」の中身として表を加工する数式を記載。例えば「テーブル名」を引数として、昇順ソートするという関数を記載する
- 第5,6引数（3行目）ではまた別の処理を行う関数を「処理2」として定義する。そしてこの処理2の中では「処理1」を引数として記載する。こうすることで、元データを処理1によって昇順ソートした状態の表をさらに処理2にかけることができる
これを繰り返していくと、処理1→処理2→処理3…処理nと手順の多い加工を
順番に行っていく複雑な操作を巨大な一つの数式で表現することができる。
スプシで再現するには
パワークエリでは主に
- 全体をLET関数で囲う
- 奇数個目の引数で名前を定義する
- 偶数個目の引数でM言語の数式を記述する
- 最後の引数（奇数個目）に最終計算結果の名前を記述する
という構造をしていた。これをスプシで再現するにあたって、
124はスプシにおいてもLET関数が同じ形で使えるので問題ない。
3さえなんとかして「ワークシート関数を使って表を加工する」ということができれば原理上パワークエリの機能が再現できる。
これがまた複雑なんだけど楽しい。
ということで、次回は冒頭に載せたスプシの長すぎる数式について解説していく。