【LAMBDA】Googleスプレッドシート新関数検証 -2 MAP / MAKEARRAY

2022年10月1日 21:27

これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。といいつつ、こっちの方が最新ネタだからか人気ですが。。
可能な範囲で、土日に新しい記事を出していこうかなと思います。

【LAMBDA / XLOOKUP】Googleスプレッドシート新関数検証 -1

2022年9月から使えるようになった LAMBDA関数とヘルパー関数。

↑ 先週の記事では、LAMBDA の6つのヘルパー関数の中でも基本となるBYROW,BYCOLについて書きました。今回はMAP、MAKEARRAYの配列系ヘルパー関数を検証していきます。

もし検証1を読んでない場合は、そちらに前提となるヘルパー関数を使った式のお作法についても書いているので、先に検証1から読むことをお勧めします。

新関数、名前付き関数は条件付き書式でも使えるのか？

本題であるヘルパー関数の掘り下げの前に、少し気になった「条件付き書式」での動作確認をしてみましょう。

条件付き書式には、相対参照・絶対参照の理解が必要。

条件付き書式は、自分でルール（条件）を設定し、それを満たした(TRUEとなる)場合に指定した書式を返すものです。

シートのデータを視覚的にわかりやすくするには、非常に便利な機能ですね。Excel時代から手動でチマチマ塗りつぶししてる人がいたら、ささっとこうすればいいんですよって設定してあげてました。プチ自動化です。

簡単な条件であればテンプレートから設定すればよいですが、少し複雑な条件による書式設定を実現したい場合は 「カスタム数式」で自分で設定してあげる必要があります。

たまにカスタム数式の挙動を理解してない人が、自分自身のセルを取得するためにわざわざ

=INDIRECT(ADDRESS(ROW(),COLUMN()))

こんな式を駆使してたりしますが、まったく意味がないです。

条件付き書式の式は、適用する範囲内で自動でスピる（相対参照される）ので、範囲の開始位置（一番左上のセル）の式を作ればよいのです。

逆に注意点としては、（参照を）動かしたくない式内の列、行、またはセルには $を付けて絶対参照にしてあげる必要があります。

たとえば、A列が条件にマッチしたら同じ行のAからF列を色付けしたい。
なんて時は、

こんな感じで、Aの前に$をつけて 列だけを絶対参照にしてあげればよいです。

絶対参照・相対参照がよくわからんって人は以下のサイトや、検索すれば色々解説してるサイトがあります。それらを参考に。

セル内であれば、ショートカット F4 での参照形式切り替えが使えますが、残念ながら条件付き書式のカスタム数式では F4が使えません。
手動で$をつけるか、セル内で式を完成させてからコピペしましょう。

条件付き書式は LAMBDAのMAPに近い

この条件付き書式のカスタム数式は、かなり自由度が高く 最終的にTRUE、FALSEで結果を返しさえすれば OK。計算過程においては Arrayformulaが使えないQueryやFilterもスピらせることが出来ます。これは、LAMBDAヘルパー関数、特に MAPに近い動きです。

だから、ぶっちゃけ条件付き書式内で LAMBDA関数や名前付き関数を使う必要性はあまりないんです。

というか、使いどころがまったく思いつきませんｗ
でも、せっかくの新機能なんで検証してみましょう。

条件付き書式で LAMBDAを試す

指定したセルの数値が一つ左のセルより大きければTRUE、そうでなければFALSEを返すという適当な関数を作成、これをカスタム数式に入れて動かしてみましたが、ちゃんとに色付けされてますね。

LAMBDA関数は、条件付き書式内で動きました。

条件付き書式で「名前付き関数」を試す

上で使ったLAMBDA式を 名前付き関数にしてみましょう。
とりあえず COMP_LEFT という関数名にします。

これを先ほどの条件付き書式のカスタム数式に入れた LAMBDA式と差し替えてみましょう。使えるなら同じ動きをするはずですが・・・。

「計算式が無効です」とエラー表示になり、「完了」ボタンが押せません。

名前付き関数（自作関数）は、条件付き書式では使えない

という結果になりました。残念。

条件付き書式内のカスタム数式は自由度が高いと上で書きましたが、importrangeは使えなかったり、他のシートを参照するときにINDIRECTをかませる必要があったりと、シート内で使える関数に比べ制限される部分もあります。

原則スプレッドシート単位で定義されている 名前付き関数は、条件付き書式では対応していないってことでしょう。ま、影響は少なそうですが。

配列系ヘルパー関数　MAP、MAKEARRAY

ここから本題です。

今回取り上げるヘルパー関数は、条件付き書式の動きに近いと紹介した MAP と MAKEARRAY の配列系です。

配列系と分類している通り、結果は行・列方向に広がる配列を返します。結果の配列が1列や１行という縛りのある行列系（BYROW、BYCOL）に比べると自由度はあがりますが、扱いが難しいという難点もあります。

それぞれ見ていきましょう。

MAP (1つ処理して 1つ返すの繰り返し）

GASやjavascript における map メソッドに慣れていると、どうにも違和感があるのが、ヘルパー関数の MAPです。

EXCELの方のMAP関数の解説ですが、「いきなり答える備忘録」さんも参考になります。

ヘルパー関数 MAPの特徴

配列の要素を一つずつ処理して同じ配列を返す。
という特性のMAP関数。

動きはGASのmapと同じなんですが、残念ながら基本的には添字（何番目か？）が使えないしデータも二次元配列という扱いではないので、本当に1セルずつの処理となります。

一応 ヘルパー関数の中で唯一、自由に複数の引数をもてる、という特徴があります。

サイズ（縦横）が同じ配列であれば、第２、第３・・・の引数にすることで、後ろのLAMBD式内の条件分岐に利用することが出来ます。

添字は作れる！

なんか一昔前のCMのキャッチフレーズ「かわいいは作れる」みたいですが・・・。

上で「基本的には添字（何番目か）が使えない」と書きましたが、同じサイズの配列を第２引数として使えるというメリットを理解すれば、添字を用意することが出来るわけです。

本来の対象範囲を A1:D5 （array1）とした場合、この行数、列数はそれぞれ

行数　ROWS(A1:D5)
列数　COLUMNS(A1:D5)

と表すことが出来ます。
ちなみにROWやCOLUMNはリアルなセル範囲でしか使えませんが、こちらはバーチャルな配列でも利用可能。

これをSEQUENCEと組み合わせて、

=SEQUENCE(ROWS(A1:D5),COLUMNS(A1:D5))

こうすることで、

同じサイズの連番（0ではなく1はじまりの添字）配列を生成することが出来ます。これを使って〇番目の時はこの処理をするといったことも可能に。

ただ、先に書いた通り基本的には二次元配列的な処理が出来ないので、添字が使えても活用シーンがあまり無いかも。

配列内の個々の要素に対して処理をして、同じサイズの配列を返す。動きとしては REDUCEに比べればわかりやすいし便利だとは思うのですが・・・。

たとえば、よくMAPの解説（サンプル）で登場する以下のような利用方法

これって ARRAYFORMULA と一緒じゃね？

ってなりますよね。
ARRAYFORMULA使った方がシンプルだし、
スピル使える Excelなら　=A3:C6*E1　で済む式です。
わざわざ式を複雑にする意味がありません。

悪魔の実で例えると、「火を焼き尽くすマグマ」ってなに？って感じかもｗ

じゃあ Googleスプレッドシートの場合は、

ARRAYFORMULAが使いこなせれば MAPは必要ないのか？

というと、そんなことも無いんです。

ARRAYFORMULAが使えない関数こそMAPの出番

BYROWや条件付き書式のカスタム数式と同じように、ヘルパー関数特有のチート能力を MAPも持っています。

個々のセルの値に対しての途中の処理は、FILTER、SORT、UNIQUEなんでも使ってOK。つまり、最終的に個々の結果が一つの値（１セル）になってさえいれば、途中の計算はかなり自由に組めます。

ARRAYFORMULAでは無理な、途中で複雑な配列処理を絡めるケースでは、活用できそうな気がしてきましたね。

たとえば、上記のようなリスト形式の予定表から、予定入り月カレンダーを生成するなんてのも、個々の日付でFILTERが使えるMAPを FILTER、SEQUENCE、TEXTJOINをいい感じに絡めることで可能となります。

同じく以下のような、なぜか？一意の値を返すのに ARRAYFORMULAが効かない関数も MAPなら扱えます。

■配列を返すわけでもないのに ARRAYFORMULAが効かない系の関数
　ISDATE (日付かどうかを判定）
　ISFORMULA　（数式かどうかを判定）
　FORMULATEXT （数式を文字列として取得）
　CELL　（セルに関する情報を取得）

他にも INDEX や OFFSETも利用可能

一例です。他にもあります

これらを FILTERと組み合わせて、例えば A列が日付データの B列のみに絞り込んで出力とか、A列の日付データの1つ下のセルだけ取得、こんなことが出来ます。

他の例としては、ISFORMULAとFORMULATEXTをMAPで使うことで、

「指定したシートで使われている数式を全部取得して1列に表示。」

なんてことも可能になりました。

無理やり特殊な例を書きましたが、ほとんどのケースは ARRAYFORMULAで済むので、今のところ活躍の場は多くないかも。

でも、ポテンシャルは高そうなんで、今後色々な活用例が発見されるかもしれません。

MAKEARRAY （領域展開できるヘルパー関数）

ヘルパー関数の中でも実力者風だけど、謎が多く使いどころが悩ましいのがこのMAKEARRAYです。

敵の幹部によくいる、フードをかぶっていて顔が見えない思わせぶりなキャラって感じでしょうか。

ジャンプでテコ入れが必要な作品だったら、
「メ、MAKEARRAY ！おまえ女だったのか？」
って展開もありですねｗ

妄想はこれくらいにして、解説していきましょう。

MAKEARRAYは名前の通り自由に配列を生成できる能力、
つまり 領域展開 というチート能力があります。

ヘルパー関数 MAKEARRAYの特徴

ヘルパー関数の中で 自由なサイズの配列を返せるのは、このMAKEARRAY と REDUCE だけです。そしてMAKEARRAYの方が、REDUCEより分かりやすい記述ができるという印象。

行数と列数を指定して、それぞれの行番号、列番号を使った処理の結果（1セルの値）を、最終的に指定したサイズの配列として返す。

上記のような処理や公式のサンプルだと、実務での使いどころがピンとこないかもしれませんが、実はこれ結構使えるんです。

INDEX関数と組み合わせて二次元配列的な処理

サンプルとして書いた MAKEARRAYの式が ↓

=MAKEARRAY(5,4,LAMBDA(r,c,r&","&c))

この結果（上の画像）って GASでsetValuesで取得した二次元配列を扱ったことのある人なら、 array[i][j] っぽく使えそう って感じるんじゃないでしょうか？

そう、このMAKEARRAYは二次元配列的な処理をするのに適した関数なのです。もちろん array[r][c] という記述は出来ませんが、これはINDEX関数を使うことで代用できます。

少し式は複雑ですが、わかりやすい例を紹介しましょう。

  =LAMBDA(arr,makerow,makecol,padd,
    MAKEARRAY(makerow,makecol,
      LAMBDA(r,c,
        IFERROR(
          INDEX(arr,r,c),padd)
        )
      )
    )
  (A1:C3,I8,I9,I10)

↑ これは 4つの引数をとる MAKEARRAYを使った式です。

引数1 arr　　　・・・　指定したセル範囲
引数2 makerow ・・・　指定行数
引数3 makecol ・・・　指定列数
引数4 padd　・・・　パディング(埋める）文字

と思ってください。
わかりやすいようにLAMBDAでくくっています。

実際に動かすと

こんな感じで INDEXを組みわせることで、元の配列をベースに縮小または拡張した違うサイズの配列を返すことが出来ます。

関数詳しい人なら、これって ARRAY_CONSTRAIN関数の拡張版？ってことに気づくかもしれません。

ARRAY_CONSTRAINもマニアックな関数なんですが、この関数はサイズを抑える（縮小）はできても、拡大はできないのが不便だなって思ってたんですよね。さらに拡大した部分を埋める文字が指定出来たらよいなと。

Excelだと最新関数で EXPAND があります。これが一番近い関数ですが、EXPANDは削減（行、列数を減らす）だとエラーになるんですよね。これが微妙。

というわけで、MAKEARRAYで自作してみました。

これがなんの役に立つか？というと、異なるサイズの範囲（配列）を結合させる時なんかに活用できます。

これは、またそのうち紹介したいと思います。

MAKEARRAYが使えそうなその他のケース

いざ使うとなるとなかなか難しいMAKEARRAY。
じゃあどんな場面で使えそうか？ 思いつく例をあげてみました。

■MAKEARRAYが使えそうなケース
・行、または列単位で空白を詰める処理
・直積（組み合わせ全パターン抽出）
・リスト表からのクロス表作成

空白詰めや直積は、TEXTJOINを使って一度文字列化すれば他の関数の組み合わせで比較的簡単に処理できますが、文字列化を経由したくないって時にTEXTJOIN使わない方法だと MAKEARRAYが簡単そうです。

リスト表からのクロス表作成は、Query関数を絡めた複雑な処理が必要。
一応ヘルパー関数なかった時代でも頑張れば作れたんですが、LAMBDA,MAKEARRAYを使うとだいぶシンプルな記述になります。

まだまだ底が見えないというか 呪力総量が一番多そうなMAKEARRAYは、乙骨先輩的な関数と言ってもいいかもしれません。

以上が、MAPとMAKEARRAYの大まかな検証となります。

ヘルパー関数は毎回難易度高めなんで、自分は書いてて楽しいですが、一体どれだけの人が興味があって、ついてこれるのかという不安が・・・。

しかも、どちらかというとライト層が多い noteで書くにはマニアックすぎる内容ｗ

市場に対して供給するサービスがマッチしてない？

という気がしなくもないですが、
そのうち物好きな読者も増えてくるでしょう。

ってことで、次回もこのノリで ヘルパー関数の最後にして最強クラス、REDUCE、SCAN の累積系を取り上げたい思います。

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

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

この記事が気に入ったらサポートをしてみませんか？