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

その1はこちら。

その2はこちら。

今回は完全に自分用のチートシート。
「数式を使って表を加工する」とは言ったものの、ワークシートの数式は基本的にそれ専用に作られたものではない。
当然複雑な加工をするには複雑な数式を組まなければいけないのだが、表の加工ってけっこうパターン化されていていつも似たような数式をコピペで使っている。
ということで自分が多用する関数・数式をまとめておく（主に後半）。

よく使う関数

IMPORTRANGE関数

他のスプシファイルからテーブルをインポートする関数。
自分はもうA:AZみたいな感じで毎回範囲を固定してかなり広めに取得している。
場合によってはそのままでも問題ないし、空欄を取り除いた正確な範囲が必要な場合は後述するTBLトリミング関数で必要部分だけを切り出して使用している。

FILTER関数、SORT関数

EXCEL時代からずっとお世話になっている有名関数。説明不要。

VSTACK関数、HSTACK関数

FILTERとSORTに比べると少し知名度で劣る気がするけど、こちらもEXCEL時代から多用している。
列を追加するときとか見出し行をつけるときによく使う。

LAMBDAヘルパー関数

上級者向け関数の代名詞LAMBDA関数。一般的にはLAMBDAヘルパー関数と組み合わせて使われることが多いのかな？
ヘルパー関数は便利なものが多いが、テーブルの加工に使うという観点から、どれをどんな時に使うのかの自分なりの基準をざっくりとまとめてみた。

• BYROW関数：1行1行に何か処理を行う場合。配列を引数に取れない関数を各行に適用するときは必須。
  XLOOKUP関数などの配列を引数に取れる関数の場合は列全体を引数にすればそれで済むこともあるが、それがうまくいかないときに引数を数値にしてBYROW関数で囲うとなぜかうまくいくこともある。
• MAKEARRAY関数：その名の通り配列を作る関数なので汎用性が高い。配列内の行数、列数を引数にして簡単な計算をすることもできる。
• REDUCE関数、SCAN関数：再帰処理を行う場合。最終結果を値として得たい場合はREDUCE関数、経過を配列として得たい場合はSCAN関数。（あんまり関数名と得られる結果が合ってない気がして自分はなかなか覚えられない…）
• MAP関数：テーブル内の既存のデータを上書きする形で何か処理をする場合。しかし特定の列のみではなく配列内の全セルに処理が走る関係で数式が煩雑化しやすいので、これ使うくらいならBYROW関数など使って新しく列作った方が逆にすっきりするような気もする。

LAMBDA関数

LAMBDAヘルパー関数もけっこう使うが、LAMBDA単体でも多用している。
名前付き関数機能を使えばLAMBDAで自作関数が作れることは知られているが、
実は名前付き関数機能を使わずともLET関数の中で自作関数を定義できてしまうのだ。
これがめちゃくちゃ便利。
…と言われてもピンとこないかもしれないので、次以降の自作関数で紹介したい。

よく使う自作関数など

前述のとおりLET関数の中でLAMBDA関数を使って自作関数を定義することができるので、よく使う自作関数をいくつか。
また、LAMBDA関数ではないがよく使う処理もコピペ用にまとめた。

列番号取得関数（自作）

LAMBDA(TBL,NAME,XMATCH(NAME,CHOOSEROWS(TBL,1)))

テーブルを加工するにあたっては列を指定して値を取得したり何か操作を加えたりする機会が非常に多い。例えば列構成が「ID、氏名、所属、電話番号、住所」で構成されている表から「氏名」の列だけを抽出したいとする。このとき、

CHOOSECOLS(元テーブル,2)

みたいに「2」と直接列番号を指定するとあとから列構成が変わったときにずれてしまう。
また、初見で何の列を指定しているのかがわからない。
「2」の部分は下記のように1行目から列名を指定して列番号を取得するのが柔軟性の面で望ましいと思う。

CHOOSECOLS(元テーブル,XMATCH(“氏名”,CHOOSEROWS(元テーブル,1)))

これで柔軟に「氏名」列を取得することができたが、いろいろな加工をしていく中で「氏名」列以外の列についても列番号を取得する必要が出てくることもある。
そのときに毎回この式を書くのは長くなるし可読性も微妙だ。
そこで、LAMBDA関数を使って関数を定義し、再利用するのが便利。
第1引数TBLでテーブルを指定し、第2引数NAMEで列名を指定している。

LAMBDA(TBL,NAME,XMATCH(NAME,CHOOSEROWS(TBL,1))

あらかじめこの形で関数を例えば「列番号」という名前で定義しておけば、次回以降は

CHOOSECOLS(元テーブル,列番号(元テーブル,”氏名”))

で先ほどと同じ結果が得られ、この書き方ならどのテーブルの何という列を取得しているのかがぱっと見でわかる。
それぞれのLAMBDAパラメータ（自作関数を使う場合に渡す引数）は下記のとおり。

• TBL：対象となるテーブルをセル範囲または配列として指定
• NAME：取得したい列の列名を文字列で設定

列取得関数（自作）

先ほどの列番号取得関数と併せて最初に宣言しておくと便利なのがこちら。

LAMBDA(TBL,NAME,CHOOSECOLS(TBL,列番号(TBL,NAME)))

列番号取得関数の最後に出てきた数式をそのままLAMBDA関数で自作関数化したものだ。
こちらは同じ引数でテーブルの任意の列全体を配列として取得することができる（ただしこの書き方だと先に列番号取得関数を定義しておく必要がある）。
LAMBDAパラメータは列番号取得関数と同じ。

TBLトリミング関数（自作）

LAMBDA(TBL,TRANSPOSE(FILTER(TBL,CHOOSECOLS(TBL,1)<>"")))

（解説はその2も参照）
加工する対象となる元テーブルのデータについて、IMPORTRANGE等で取得する際は広めに取得し、このTBLトリミング関数で必要な行列数にトリミングしてから使うと良い。
原理としては、FILTER関数で空欄以外の行を抜き出したあとに行列を入れ替えるという操作を行う。
これを2回繰り返すことで、行と列の両方に対して空白除去の処理を行うというもの。
LAMBDAパラメータのTBLは対象となるテーブルをセル範囲または配列として指定する。
注意点：

• 行と列それぞれに実施する場合はこのままの形で2回重ね掛けする
• 逆に行のみに適用したい場合はTRANSPOSE関数部分を消して1回だけ使用する

自作関数3種（まとめてコピペ用）

TBLトリミング,LAMBDA(TBL,TRANSPOSE(FILTER(TBL,CHOOSECOLS(TBL,1)<>""))),
列番号,LAMBDA(TBL,NAME,XMATCH(NAME,CHOOSEROWS(TBL,1))),
列,LAMBDA(NAME,TBL,CHOOSECOLS(TBL,列番号(NAME,TBL))),

この3つの自作関数はかなり汎用性が高いため、このままコピペして3つまとめて最初に定義しておくことも多い。
最初に関数の宣言のために3行使ってしまうが、それ以降はシンプルなオリジナル数式で済む。

場合によっては3つフルで使わずもっと簡素化しても良い。
例えば列番号を単体で取得することがない場合は列取得関数だけ定義すれば足りることもあるし、加工の内容によってはTBLトリミング関数も不要な場合もある。
使うテーブルが1種類しかない場合は引数のうちTBL部分は固定でもいいかもしれない。
その辺は臨機応変に。

インデックス列生成

HSTACK(VSTACK("インデックス",SEQUENCE(ROWS(対象テーブル))),対象テーブル)

パワークエリでいう「インデックス列を追加」機能。列を一つ追加し、通し番号を振る。
処理の流れとしては下記のとおり。

1. 元のテーブルの行数をROWS関数で取得
2. SEQUENCE関数で配列作成
3. VSTACK関数でヘッダー行をくっつける
4. HSTACK関数で元のテーブルの左にくっつける

見出し行変更・見出し行削除

VSTACK(“新しい見出し”,QUERY(対象テーブル, "OFFSET 1",0))

たいてい見出し行も含めた状態で操作しててもそこまで不便はない。
しかし場合によっては最初に見出し行を削除してデータ本体だけの状態にしてから加工して、最後に見出し行をつけた方がすっきりすることがある。
見出し行を削除する場合はQUERY関数を使う。
個人的にあまりQUERY関数はあまり好きではないのだが、他に方法がないのでこの場合だけは使っている。
QURTY関数で見出しを削除したあと、新しい見出しを付ける場合はVSTACK関数で追加する。

全行同じ値が入った列を追加

VSTACK("列名",MAKEARRAY(ROWS(対象テーブル)-1,1,LAMBDA(R,C,"入れたい文字列")))

色々なやり方があるが、MAKEARRAY関数を使うのが一番シンプルな気がする。
MAKEARRAYの行数（第1引数）で「-1」を入れているのはヘッダー行の分を差し引いているもの。
その後VSTACK関数でヘッダーを追加しているので、最終的に出力される行数は対象テーブルの行数と同じになる。

特定の列に特定の文字列が含まれるかどうかを判定してTRUE/FALSEで返す

LAMBDA(TABLE,STR,COL,ARRAYFORMULA(NOT(ISERROR(FIND(STR,列(TABLE,COL)))))),

列取得関数を使う前提なので、あらかじめ定義しておく必要がある。
この後何かフィルター処理するようなときに便利。
それぞれのLAMBDAパラメータは下記のとおり。

• TABLE：対象となるテーブル範囲
• STR：探したい文字列
• COL：STRをどの列で探したいか、列名を文字列で入力

まとめ

このような長すぎる数式を使ってテーブルを加工する仲間を増やしたいので、本当はもっと数式の基本から丁寧に解説したいという気持ちもある。
しかし初心者でもわかるように数式の解説をしているサイトは無限にあるので、今回は自分が語りたい部分を自分にとってわかりやすくまとめるにとどめた。正直めっちゃ楽しかった。

テーブルを加工するのに一つのセルに数式を詰め込んで完結する方式はぱっと見かなり難しそうに見えるが、数式が1か所にまとまっていることでかえって保守性が上がったり、コピペでAIに投げやすくなったり意外と良い面も多いと思う。

今後もよく使う自作関数については随時追加していきたい。