配列数式 ArrayFormula
Google スプレッドシートで複数のデータを一括で処理したいときに便利なのが配列数式。ARRAYFORMULA を使うと配列を扱えない関数でもループして適用するかのごとく扱えるようになります。
配列数式でできないこと
しかしながら ARRAYFORMULA には制約が多くて、ちょっと複雑なことをさせようと思ったら、思ったような結果が得られなくなる、そんな経験をされた方は多いのではないでしょうか。
主な理由は、次の２点でしょう。
- 数式中に元々配列を受け取れる関数が１つでもあると、上手く展開してくれない。
- 配列展開する部分を限定できない。 行だけのループとか列だけのループとか、ここは展開しないで欲しいとか細かい制御ができない。
なお、このような弱点を補うため、様々な小技も発明されてます。
https://monoblog.jp/archives/4416
https://stackoverflow.com/a/72983822
私も以前一つその手のネタを書いてますのでご参考まで。
Googleスプレッドシートの配列数式でAND,OR関数がバグる件の対処
救世主、LAMBDA関数の登場
そんな状況を解決するために颯爽と現れたのがLAMBDA（ラムダ）関数です。
ラムダ関数とは、式の中に定義できる匿名の自作関数で、様々なプログラミング言語にもある概念なので聞いたことがあるかもしれません。
スプレッドシートにおけるラムダ関数はExcelで先に実装されたものを輸入したようです。
LAMBDA関数の書き方
LAMBDA関数のサンプルとして公式に乗ってるのはこんな感じ。
使用例
=LAMBDA(Salary, Salary*0.3)(1000)
=LAMBDA(Temp, (5/9)*(Temp-32))(85)
LAMBDA(...)(...) みたいに見慣れない括弧ブロックの連結がありますね。
１つ目の括弧の中が自作関数の定義で、SalaryとかTempは関数の引数宣言であり、次に続くのが実際の関数の処理内容です。
２つ目の括弧の中は、実際に関数に処理させたい引数データです。つまりは定義した関数の呼び出しです。
使用例では１個ですが、複数の引数定義することもできます。
引数を増やしても常に一つ目の括弧の最後の引数が関数の定義部分となります。
=LAMBDA(a,b,MAX(a,b))(A3,A10)
自作関数というと今まではGASで書いてましたが、ここではあくまでセル数式として記述します。（もちろんGASで書いた自作関数を呼び出すことも可能）
LAMBDA関数の自作関数はGASの自作関数ではなく、これまた同時期に提供された名前付き関数の匿名バージョンと考えた方がいいですね。
LAMBDA関数の結果として配列を返すことはできない。
しかし抜け道はある！
配列数式の代替としての利用法
ヘルパ関数と組み合わせる
ここまで見ると、「は？そんな書き方して何がうれしいの？」
「=LAMBDA(a,b,MAX(a,b))(A3,A10) なんて書かずに =MAX(A3,A10) って書けばいいでしょ？」
って思いますよね。
実は LAMBDA関数は LAMBDAヘルパ関数(LHF) と呼ばれる LAMBDA関数を引数に受け取る関数群と一緒に使ってこそ意味があるのです。というか、ヘルパ関数を使って初めて配列数式の代わりとなり得るのです。
ちなみに、ヘルパ関数と一緒に使う場合、LAMBDA関数の部分に二つ目の括弧は必要ありません。なぜなら適切な引数でLAMBDA関数を呼び出すのが、ヘルパ関数の役割ですから。
逆引きヘルパ関数
ヘルパ関数はこの記事を書いた時点で６種類あるようですが、ここでは用途に応じて何を使ったらいいか、逆引き形式で解説してみます。
セル範囲を行単位または列単位で処理したい
対象のセル範囲を、行または列単位でループ処理したい場合、それぞれBYCOL関数またはBYROW関数を使います。
D1セル: =BYROW(A1:C3,LAMBDA(row,MAX(row)-MIN(row)))
A4セル: =BYCOL(A1:C3,LAMBDA(col,(MAX(col)+MIN(col))/2))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 3 | 5 | 7 | 4 |
| 2 | 4 | 3 | 5 | 2 |
| 3 | 1 | 2 | 4 | 3 |
| 4 | 2.5 | 3.5 | 5.5 |
引数として与えるセル範囲は１つしか指定できないことに注意してください。
それぞれ役割の違う複数のセル範囲を引数にして処理したい
BYROW関数, BYCOL関数では引数の数が足りない時、MAP関数が使えるかも知れません。
LAMBDA関数に 任意の数の引数を与えられる のはこれだけ。
D1セル: =MAP(A1:A3,B1:B3,C1:C3,LAMBDA(prod,price,count,prod&" "&(price*count)))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品A | 5000 | 2 | 商品A 10000 |
| 2 | 商品B | 3500 | 1 | 商品B 3500 |
| 3 | 商品A | 5000 | 4 | 商品A 20000 |
ただし引数として与える各セル範囲は全て同じ要素数であることという制約があります。
ARRAYFORMULA にもあった制約ですね。
複数のセル範囲を元に一つの処理結果を求めたい
統計操作など、全データ範囲をループして統合された一つの結果を求めたい場合はREDUCE関数が使えます。
第一引数は累積値用の変数（ラムダ関数の第一引数で、ループの直前の結果を保持している）の初期値、第二引数はループ範囲。
| A | B | C | |
|---|---|---|---|
| 1 | 赤 | 青,黄 | |
| 2 | 緑,赤 | 黄 | 紫 |
| 3 | 青 | 紫,青 | 緑,黄,赤 |
式：=REDUCE("",A1:C3,LAMBDA(result,current,IF(current="",result,result&","¤t)))
結果：
,赤,青,黄,緑,赤,黄,紫,青,紫,青,緑,黄,赤
複数のセル範囲を直前の結果を参照しながらループ処理したい
REDUCE関数のように累積値用の変数を使いながらも、REDUCE関数と違って、与えたデータの数だけ結果を返したい時には、SCAN関数がつかえます。
第一引数は直前値変数（ラムダ関数の第一引数）の初期値、第二引数はループ範囲。
B1セル: =SCAN(0,A1:A5,LAMBDA(prev,x,prev+x))
| A | B | |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 3 |
| 3 | 3 | 6 |
| 4 | 4 | 10 |
| 5 | 5 | 15 |
元のセル範囲なしに指定の行数ｘ列数でループ処理したい
特に元になるセル範囲がない状態から、指定の行数ｘ列数からなる配列を作りたい時はMAKEARRAYでできます！
セルA1: =MAKEARRAY(5,5,LAMBDA(r,c,4-ABS(r-3)-ABS(c-3)))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 0 | 1 | 2 | 1 | 0 |
| 2 | 1 | 2 | 3 | 2 | 1 |
| 3 | 2 | 3 | 4 | 3 | 2 |
| 4 | 1 | 2 | 3 | 2 | 1 |
| 5 | 0 | 1 | 2 | 1 | 0 |
応用編
BYROW,BYCOLで複数セルを受け取ってそれぞれに違う処理をしたい
LAMBDA関数は配列を返せないけど、引数は配列を受け取れます。
なので、BYCOL関数またはBYROW関数に二次元配列・セル範囲を渡して、LAMBDA関数で複数のセルに対して処理することができます。
上に挙げたサンプルもMAX関数やMIN関数で複数セルを処理してるケースですね。
D1セル: =BYROW(A1:C3,LAMBDA(row,MAX(row)-MIN(row)))
A4セル: =BYCOL(A1:C3,LAMBDA(col,(MAX(col)+MIN(col))/2))
でもこれは複数セルに同じ処理をするだけだから簡単ですが、セルごとに違う意味を持たせたかったら？
INDEX関数で配列の任意の要素を取り出せる
INDEX関数を使えば配列の単一要素を取り出せます。
MAP関数のところで出したサンプルをINDEX関数で書き換えてみます。
D1セル: =MAP(A1:A3,B1:B3,C1:C3,LAMBDA(prod,price,count,prod&" "&(price*count)))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品A | 5000 | 2 | 商品A 10000 |
| 2 | 商品B | 3500 | 1 | 商品B 3500 |
| 3 | 商品A | 5000 | 4 | 商品A 20000 |
↓
D1セル: =BYROW(A1:C3,LAMBDA(row,INDEX(row,1,1)&" "&(INDEX(row,1,2)*INDEX(row,1,3))))
まあこの例だとMAP使った方がわかりやすそうですが、こういう書き方もできるということで、ご参考まで。
部分的に複数セットで取り出したいならOFFSET関数
INDEX関数は要素一つづつしか取り出せませんので、OFFSET関数を使いましょう。
E1セル: =BYROW(A1:D3,LAMBDA(row,INDEX(row,1,1)&" "&SUM(OFFSET(row,0,1,1,COUNT(row)))))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | A店 | 120 | 130 | 130 | A店 380 |
| 2 | B店 | 350 | 370 | 380 | B店 1100 |
| 3 | C店 | 180 | 220 | 200 | C店 600 |
こういうのはMAPだと「配列引数のサイズが異なります」って怒られて出来ないんですよね。
=MAP(A1:A3, B1:D3,LAMBDA(name,values,name&" "&SUM(values)))
LAMBDA関数の結果を配列で返したい
LAMBDA関数共通の制約として、結果を配列で返すことはできないというものがあります。
しかし、状況次第では工夫で解決できます。
REDUCE関数ならJOINで結合SPLITで分解
LAMBDA関数内でJOIN関数で文字列として結合し、外側でSPLIT関数で分解するという手があります。
次の例では、セパレーターにカンマを使っているため、文字列中にカンマがあるとそれも個別要素に分解されています。ここでは敢えてそれを利用して重複要素の排除をやっていますが、通常はそうならないように、対象データ中に絶対含まれない文字列をセパレーターに使うか、それが無理ならエスケープしておく（JOIN前に特定パターンの文字列に変換しておいて、SPLITのあとで逆変換する）のが普通です。
| A | B | C | |
|---|---|---|---|
| 1 | 赤 | 青,黄 | |
| 2 | 緑,赤 | 黄 | 紫 |
| 3 | 青 | 紫,青 | 緑,黄,赤 |
式
=UNIQUE(FLATTEN(SPLIT(REDUCE("",A1:C3,LAMBDA(result,current,JOIN(",",result,current))),",",FALSE,TRUE)))
| 結果 |
|---|
| 赤 |
| 青 |
| 黄 |
| 緑 |
| 紫 |
UNIQUE関数は行方向の配列しか処理できない。SPILIT関数は列方向の配列になるようなので、FLATTEN関数またはTRANSPOSE関数を介してやる必要があります。
ARRAYFORMULA 再び！？
REDUCE関数以外の、元々配列を返す関数は、JOIN＆SPLITだけでは足りません。そこで使えるのが ARRAYFORMULA関数。
融通の利かない ARRAYFORMULA にまだ使い道があったとは。
D1セル: =ARRAYFORMULA(SPLIT(BYROW(A1:C3,LAMBDA(row,MAX(row)&","&MIN(row))),","))
A4セル: =ARRAYFORMULA(TRANSPOSE(SPLIT(BYROW(TRANSPOSE(A1:C3),LAMBDA(col,(MAX(col)&","&MIN(col)))),",")))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 3 | 5 | 7 | 7 | 3 |
| 2 | 4 | 3 | 5 | 5 | 3 |
| 3 | 1 | 2 | 4 | 4 | 1 |
| 4 | 4 | 5 | 7 | ||
| 5 | 1 | 2 | 4 |
今回は結合要素２つしかないので、JOIN関数は使わず直接カンマで文字列結合してます。
BYCOLはARRAYFORMULAと相性悪いようで、意図した通りに展開できないので、元のデータ範囲をTRANSPOSE関数で変換してBYROW関数で処理して、再度TRANSPOSE関数で行方向に戻すという手間を加えてます。
他の書き方
あまり汎用性はないですが上のサンプルの場合なら、以下の様にして２つの関数の配列として書くこともできます。
D1セル: ={BYROW(A1:C3,LAMBDA(r,MAX(r))),BYROW(A1:C3,LAMBDA(r,MIN(r)))}
A4セル: ={BYCOL(A1:C3,LAMBDA(c,MAX(c)));BYCOL(A1:C3,LAMBDA(c,MIN(c)))}
実のところこれは２行／２列それぞれに式を入れるのと大差ないですね。
しかし、これをさらに別のヘルパ関数の引数として与えたりすることもできます。セルに値を入れなくとも数式中で任意の配列を作る方法として、覚えておくとよいかもしれません。
{}を使ったやり方はLAMBDAヘルパ関数の引数としても役立つ場合があります。
連続してないセル範囲を一つのヘルパ関数で処理したい場合に、BYCOL({A1:C5,E1:F5,H1:J5},LAMBDA(...)),BYROW({A1:C8;A11:C15;A18:C25},LAMBDA(...)) みたいな書き方が出来ます。
BYCOLとBYROWで {}内の区切り文字が違う ことに注目してください。カンマ","は行（縦方向）への連結、セミコロン";"は列（横方向）への連結と使い分けます。
要素数の違う複数のセル範囲を使いたい
MAP関数をもってしてもできない、異なるセル範囲を組み合わせた処理をどうするか。
ARRAYFORMULA内では意図通り動作しなかった様々な関数を上手く使いましょう。
LAMBDA関数内で検索で見つける
そもそもそういう場合ってループさせたいメインのデータは一つに絞れて、他のセル範囲はそこから導き出せるということも多いのではないでしょうか。
それならQUERY関数 が使えるかもしれません。
次の例は販売履歴シートを元に、販売集計シート（価格表も兼ねる）に型番ごとの販売個数と売上合計を出力するものです。
| 販売履歴シート | 販売集計シート |
|---|---|
 |
 |
C2セル:
=BYROW(A2:A6,LAMBDA(name,QUERY('販売履歴'!A2:B11,"SELECT SUM(B) WHERE A='"&name&"' GROUP BY A LABEL SUM(B) ''")))
D2セル:
=MAP(A2:A6,B2:B6,LAMBDA(name,price,SUM(QUERY('販売履歴'!A2:B11,"SELECT B WHERE A='"&name&"'"))*price))
セルの並びが単純ではないが計算で求められるならINDEX関数
INDEX関数でセル範囲から指定の行・列位置のデータが抜き出せます。
次の例は地図上のチェックポイントの座標を羅列したシート上に、地図データから各座標のタイルを取得するものです。
C2セル: =MAP(A2:A8,B2:B8,LAMBDA(x,y,INDEX('地図'!A1:E7,y,x)))
| 地図シート | チェックポイントシート |
|---|---|
 |
 |
一対多関係の範囲選択ならOFFSET関数でずらす
ループ元と並びは一致してるけど、一回のループごとに複数セル範囲のセットを使いたい場合、OFFSET関数がよいかもしれません。
次の例は、主キー(A,B,C,D)ごとに作られた２ｘ３テーブルから副キー(x,y,z)に対応する値を履歴データに従って取得する例です。
なんかサンプルがどんどん意味不明に・・・（現実的な例だと式も複雑になってサンプルとしてわかり辛くなるんです）
C3セル: =MAP(A1:A5,B1:B5,LAMBDA(a,b,VLOOKUP(b,OFFSET('マスタ表'!A2:H4,0,MATCH(a,'マスタ表'!1:1,0)-1,3,2),2)))
| マスタ表シート | 履歴シート |
|---|---|
 |
 |
「こんなマスター表の作り方が悪い」ごもっとも。でも現実にはデータフォーマットをこちらの都合だけでは決められないことも多いですよね
シート違いなど、さらに複雑な条件はINDIRECT関数で指定する
OFFSET関数よりもっと複雑な範囲指定をしたい場合や、異なるシートのデータを参照したい場合はセル範囲を文字列で指定できるINDIRECT関数がよいでしょう。
たとえば前項のサンプルで、マスタ表が主キーごとに別シートに用意されていたら・・・
C3セル: =MAP(A1:A5,B1:B5,LAMBDA(sheet,xyz,VLOOKUP(xyz,INDIRECT("'"&sheet&"'!A1:B3"),2)))
| Aシート | Bシート | Cシート | Dシート | 履歴シート |
|---|---|---|---|---|
 |
 |
 |
 |
|
行と列両方で同時にループしたい
行も列も複数あるセル範囲や二次元配列の全ての要素に同じ処理をしたい時
BYROWとBYCOLを入れ子にする
まず最初に思いつくのはたぶんこのやり方ですよね。
次の例は指定範囲の各文字に対応するキャラコードの表を作ります
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | あ | か | さ | た | な |
| 2 | い | き | し | ち | に |
| 3 | う | く | す | つ | ぬ |
| 4 | え | け | せ | て | ね |
| 5 | お | こ | そ | と | の |
A6セル：
=BYROW(A1:E5,LAMBDA(r,BYCOL(r,LAMBDA(c,DEC2HEX(CODE(c))))))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 6 | 3042 | 304B | 3055 | 305F | 306A |
| 7 | 3044 | 304D | 3057 | 3061 | 306B |
| 8 | 3046 | 304F | 3059 | 3064 | 306C |
| 9 | 3048 | 3051 | 305B | 3066 | 306D |
| 10 | 304A | 3053 | 305D | 3068 | 306E |
INDEXの添え字でループ
対象範囲のサイズが決まってるなら MAKEARRAY と INDEX でループすることもできます。
この方法だと、隣の値とかを参照しやすいメリットもあります。
例えば次の例では、指定範囲の上下左右の隣接する1のセルの数を計算しています。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 0 | 1 | 1 |
| 2 | 0 | 0 | 0 | 1 | 0 |
| 3 | 1 | 1 | 1 | 0 | 1 |
| 4 | 1 | 0 | 0 | 0 | 0 |
| 5 | 1 | 0 | 0 | 1 | 1 |
A6セル：
=MAKEARRAY(5,5,LAMBDA(r,c,IF(r>1,INDEX(A1:E5,r-1,c),0)+IF(r<ROWS(A1:E5),INDEX(A1:E5,r+1,c),0)+IF(c>1,INDEX(A1:E5,r,c-1),0)+IF(c<COLUMNS(A1:E5),INDEX(A1:E5,r,c+1),0)))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 6 | 1 | 1 | 2 | 2 | 1 |
| 7 | 2 | 2 | 2 | 1 | 3 |
| 8 | 2 | 2 | 1 | 3 | 0 |
| 9 | 2 | 2 | 1 | 1 | 2 |
| 10 | 1 | 1 | 1 | 1 | 1 |
他随時更新(予定)
お題があればコメント欄で承ります
まとめ
LAMBDA関数とLAMBDAヘルパ関数を使えば、複数セル範囲への反復処理をARRAYFORMULA関数よりも柔軟にこなせます。
LAMBDA関数にもいくつか制約はありますが、ARRAYFORMULA関数で上手く使えなかった「それ自身が配列を受け取る関数」が普通に使えるので、工夫次第で出来ることが広がります。