【LAMBDA】Googleスプレッドシート新関数 検証 -1 BYROW / BYCOL
これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。といいつつ、こっちの方が最新ネタだからか人気ですが。。
可能な範囲で、土日に新しい記事を出していこうかなと思います。
先週投稿した
【LAMBDA / XLOOKUP】Googleスプレッドシート新関数 動向 -2
2022年9月から使えるようになった LAMBDA関数とヘルパー関数。
前回の記事で紹介したLAMBDA、ヘルパー関数をもう少し掘り下げていきましょう。
新関数・新機能のリリース状況なんかを追ってきたんで 記事のタイトルを「動向」としていましたが、先週の段階でほぼ展開は完了したかと思います。さらに前回までの記事で全体感は紹介できたかなと。
というわけで、今回からタイトルを「検証」に変更しました。
雑談記事というわりにシリーズ化してしまいましたが、ヘルパー関数各種と、XLOOKUだけは最新ネタ ですし、個人的に 面白いネタ なんで、もうちょっと継続して取り上げていこうかなと。
ヘルパー関数 の基本的な使い方
6種3系統の ヘルパー関数。
個々を取り上げる前に、まずはヘルパー関数のどれを使う上でも前提となる 基本的な使い方を説明します。
■ヘルパー関数 基本的な使い方
1. 必ず LAMBDAとセットで使う必要がある
2. 引数の数や返り値など型が決まっている
3. 変数名は関数名やセル番地と重複を避ける
4. 2つ以上のヘルパー関数を組み合わせることが出来る
1. 必ず LAMBDAとセットで使う必要がある
まず、ヘルパー関数は 独立した関数ではありません。前回の記事で書いた通り、関数ツクールである LAMBDA で自作関数を作成する際に、便利な素材（処理）が詰め合わせになった 課金パックです。（無料です）
だから、BYROWだけ、もしくは MAPだけみたいにヘルパー関数単体で使うことは出来ません。必ず LAMBDAとセットでの利用となります。
2. 引数の数や返り値など型が決まっている
これは「パック」なので当然なのですが、引数の数や どういった形で結果を返すかといった型が決まっています。（LAMBDAが自由すぎて忘れちゃいそうになりますｗ）
特に、MAP や REDUCE をGASで扱ってる人からすると、自由度が低く窮屈さを感じるかもしれません。
型ですが、今回の後半で紹介する BYROWを例にすると
これを具体例を使った動きでイメージにすると
こんな感じです。
順々に処理が実行されるイメージは、プログラミングの for や forEach、while といったループ処理のコードを書いたことがある人や、関数でArrayformula が使える人なら 理解しやすいかもしれません。
3. 変数名は関数名やセル番地と重複を避ける
上の解説で変数名 は「なんでもよい」と書きましたが、ある程度の制限はあります。
基本的にはセル番地とみなされるものや、関数名と見なされるもの、他の変数と重複するものは使えません。
つまり、↓ これはOKだけど
=REDUCE(0,A1:A4,LAMBDA(pv,cv,pv+cv))
↓ これは v1やv2 はセル番地と見なされるから×です。
数字を絡める場合は _ アンダーバーを挟むなどしましょう。
=REDUCE(0,A1:A4,LAMBDA(v1,v2,v1+v2))
関数名は、そのLAMBDA式内でかぶらなければ大丈夫みたいです。
↓これだと Ok
=BYROW(A1:C3,LAMBDA(row,MAX(row)))
↓ LAMBDA内で ROW関数を使う場合は関数名と重複と見なされ ×
=BYROW(A1:C3,LAMBDA(row,ROW(row)))
変数部分は少し制限はあるものの「日本語」も使えます。
でも、日本語だと表記のブレのリスクがあるし 見た目がカッコ悪いんで、半角英数で英語ベースの記述がおススメです。
また、変数によっては入力補助が働いて少しウザかったりします。
変数 row が 勝手にROW( に変換されちゃったり。
4. 2つ以上のヘルパー関数を組み合わせることが出来る
この6つのヘルパー関数は、個々でLAMBDAと組み合わせて使っても強力ですが、さらに 2つ以上を組み合わせて使うことが出来ます。
違う属性の魔法を2つ同時に！？
「メドローア」じゃないですが、かなり強そうですよね。
たとえば、BYROWで取り出した row の中身を、さらに REDUCE で累積（繰り返し）処理。こんなことも可能です。
活用例としては、以下のように 関数だけで 差し込み印刷風の 複数置換処理だって出来ちゃうわけです。
もちろんヘルパー関数どうしだけではなく、従来の関数も組み合わせることが出来ます。例えば BYROW と FILTERを組み合わせることで、いままでは難しかった条件設定でのフィルタが可能になりました。
とにかくヘルパー関数を組み合わせることで、出来ることの可能性が無限に広がった気がします。それくらい mir的には大きな変化です。（あんま世間的には騒がれてませんが・・・）
ヘルパー関数全体としてのざっくり解説は、以上になります。
ここから個別にみていきましょう。
BYROW (わかりやすくて、使いやすい）
もっともわかりやすく、使いどころが明確にイメージできる BYROW は、まさに魔法レベル1 の初級冒険者の訓練が 火の呪文 を覚えるから始めるのと一緒。LAMBDA の使い方を覚えたいって人に、最初に おススメしたいへルーパー関数です。
というわけでBYROWから取り上げましょう。
Excelでも同じように使えるケースも多いので、参考になるかと思います。
文字通り「行毎」に処理をしていくヘルパー関数。
書き方や 動きのイメージは上で書いちゃったんで割愛します。
行毎の合計に使える
一番シンプルな例は、行毎の合計算出でしょう。
ARRAYFORMULA不要。一つのセルに入れるだけで自動でスピります。
これだけでも人によっては凄い！（上級魔法？）って感じますよね？
でも、これは基本です。
バーン様的には「今のはメラだ」です。
1つの式で行毎の合計算出は、LAMBDA前からも可能でした。
ただ、LAMBDAが登場する前の世界だと、
ARRAYFORMULA + SUMIF の ↓ こんな式や
=ARRAYFORMULA(SUMIF(IF(B2:D4,ROW(B2:D4),),ROW(B2:D4),B2:D4))
MMULT （条件付きの場合は +ARRAYFORMULA ） の ↓ こんな式
=MMULT(B2:D4,SEQUENCE(COLUMNS(B2:D4),1,1,0))
でやってたわけです。
うーん、直観的にわかりにくいですよね？
ちなみにSUMIFが使えるのは、リアルな範囲のみという制限があります。FILTERやQUERYで生成した、もしくは importrangeで他のスプレッドシートから引っ張ってきたバーチャルな配列の場合は、SUMIFは使えません。
配列の場合は MMULT を使えばいいんですが、計算対象の範囲内に空白があるとエラーになるのが困りものだし、その配列の列数分の1を縦にならべた配列を用意するってのも面倒です。
その対処で ARRAYFORMULA して N関数をかませて 0化したりってやってると、さらに式が複雑化していきます。
=ARRAYFORMULA(MMULT(N(B2:D4),SEQUENCE(COLUMNS(B2:D4))^0))
BYROWの登場で 範囲・配列どちらのケースでも、よりシンプルな記述で行毎の合計算出が可能となりました。
BYROWが他に使えそうなケース
※行を列に置き換えれば BYCOLにもあてはまります。
・行毎の 最大値 または 最小値 算出
・行毎の COUNTA → 組み合わせて 全ての列が空白の行削除 FILTER
・行毎の TEXTJOIN
・行毎に INDIRECTで他のシートの値を取得 （串刺し計算 風）
・行毎に空白ではない一番右のセルの値を取得
・複数列、全てが空白の行を削除するようなFILTER
・フィルタ機能と連動したSUMIF （subtotal活用)
・行ごとに 空白セルを削除して 左詰め （追加処理が必要）
・Query関数でピボットした時の「総計」列(行) の追加
特に フィルタ連動のSUMIF（表示されてるデータに対してのSUMIF）は、ExcelだとINDIRECTかませた式で なんとか出来るけど、Googleスプレッドシートでは どうやっても作業列使う方法しかなかったんで画期的な進歩です。
ちゃんと解説した方が良さそうなんで、これは早めに記事書きます。
BYROW 利用時の注意点（※2022年12月変更）
ここまで BYROW の利点を取り上げてきましたが、惜しいと感じる部分もあります。
BYROWは、個々の行に対して実行した処理の結果は 一意の値(1セル)である必要があり、最終的な結果が 縦1列の配列でなければならない、という制限があります。
下方向に展開する配列が無理なのはわかりますが、残念ながら 横方向に展開する配列もダメです。エラーに書いてある通り、配列のネスト（入れ子）が出来ないってことですね。
つまり、需要がありそうな 行毎に FILTERで絞り込んだ 結果（1行複数列の配列）を返す なんてことが出来ません。（そのままでは出来ません）
↑こちらの仕様は 2022年のこっそりアップデートで変更されています。
行毎に 1行、または1列の配列が返せるようになりました！
工夫すれば制限も回避可能
ただ、これはあくまでも行毎の 最終的な結果が配列が×ってだけであって、途中経過であれば、 FILTERやSORT、UNIQUEなどがフルで使えます。
ここが Arrayformulaのスピり方とは違うところ。
途中計算に関しては圧倒的に自由度が高いです。
要は色々工夫したり、組み合わせたりすれば、出来ることはかなり広がりそうってことです。
BYCOL (BYROWの 列版、同じ感覚で使える）
BYROWと解説も一緒です。出来ることも注意点も一緒。
というわけで、かなり端折ります。
もちろん、BYROWと同じく非常に優秀でわかりやすい関数です。
でも、実務処理をする上で 列単位より行単位 の方が多いから 、BYROWの方が出番多そう。
BYCOLは今後のQA記事で実例が出てきた時に、もう少し詳しく触れたいと思います。
BYCOLの式書いてると、頭の中で
「列ごと処理なら、ゴー BYCOL」
って流れ出す。。
なんか、BYROW、BYCOLだけでも、関数の限界突破って感じで、オラ ワクワクすんぞ！ ってなった方は、きっと関数ヲタクです。沼にはまらないようにご注意ください。
残りのヘルパー関数は次回以降に取り上げていきます。
今回紹介のヘルパー関数 公式 （掲載時 日本語未対応）
■このシリーズの次の記事
この記事が気に入ったらサポートをしてみませんか？