メインコンテンツへスキップ
見出し画像

【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とセットでの利用となります。

    画像
    関数サポートでも 必ず LAMBDAを入れろと出る



    2. 引数の数や返り値など型が決まっている

    これは「パック」なので当然なのですが、引数の数や どういった形で結果を返すかといった型が決まっています。(LAMBDAが自由すぎて忘れちゃいそうになりますw)

    特に、MAP や REDUCE をGASで扱ってる人からすると、自由度が低く窮屈さを感じるかもしれません。

    型ですが、今回の後半で紹介する BYROWを例にすると

    画像
    こんな感じ

    これを具体例を使った動きでイメージにすると

    画像
    ②③④を 1回目、2回目、3回目と繰り返す

    こんな感じです。

    順々に処理が実行されるイメージは、プログラミングの 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不要。一つのセルに入れるだけで自動でスピります。

    画像
    =BYROW(B2:D4,LAMBDA(row,SUM(row))

    これだけでも人によっては凄い!(上級魔法?)って感じますよね?
    でも、これは基本です。

    バーン様的には「今のはメラだ」です。

    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))

    ARRAYFORMULA使うなら SEQUENCEを0乗して1にする書き方で少し短くできる

    BYROWの登場で 範囲・配列どちらのケースでも、よりシンプルな記述で行毎の合計算出が可能となりました。


    BYROWが他に使えそうなケース

    ※行を列に置き換えれば BYCOLにもあてはまります。

    ・行毎の 最大値 または 最小値 算出
    ・行毎の COUNTA → 組み合わせて  全ての列が空白の行削除 FILTER
    ・行毎の TEXTJOIN
     
    ・行毎に INDIRECTで他のシートの値を取得 (串刺し計算 風)
    ・行毎に空白ではない一番右のセルの値を取得
    ・複数列、全てが空白の行を削除するようなFILTER

    ・フィルタ機能と連動したSUMIF (subtotal活用) 
    ・行ごとに 空白セルを削除して 左詰め (追加処理が必要)
    ・Query関数でピボットした時の「総計」列(行) の追加

    ざっと思いつくケース
    画像
    フィルタ連動 SUMIFが作業列なしで可能に!

    特に フィルタ連動の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だけでも、関数の限界突破って感じで、オラ ワクワクすんぞ! ってなった方は、きっと関数ヲタクです。沼にはまらないようにご注意ください。

    残りのヘルパー関数は次回以降に取り上げていきます。


    今回紹介のヘルパー関数 公式 (掲載時 日本語未対応)



    ■このシリーズの次の記事


     
     

    mir

     
     
    元Excel職人・VBA使いから、Googleスプレッドシート職人・GAS使いにジョブチェンジ。謎解き感覚で お題(課題)を解決していくような記事を書こうかなと。その他、AIやらGeminiやらGoogleWorkspaceネタ全般

    あなたへのおすすめ