【スプレッドシートの便利な使い方】ARRAYFORMULA関数、BYROW関数でダメなときのMAP関数

要約

スプレッドシートで、複数行のデータに対して同じ処理をしたいとき、ARRAYFORMULA関数、BYROW関数を使うことがあります。
ただ、それぞれの関数には制限があり、うまくできないことがあります。
そんなときに便利なのがMAP関数です。

ARRAYFORMULA関数、BYROW関数とは

複数行のデータに対して同じ処理をしたいとき、ARRAYFORMULA関数、BYROW関数を使うことがあります。
それぞれの関数の使い方は以下の記事をご覧ください。

ARRAYFORMULA関数の使い方
https://note.com/kzautomation/n/n7d3290e26c35

BYROW関数の使い方
https://note.com/kzautomation/n/n76a5780071b5

これらには以下の弱点がありました。
ARRAYFORMULA関数: 範囲に対して使う関数を使用することができない
BYROW関数: 一つの列範囲に対してしか使えない

上記でうまくできない場合、MAP関数を使うことで解決できる場合があります。

MAP 関数とは

Google のヘルプでは、MAP 関数は次のように説明されています。

MAP(A1:A5, LAMBDA(セル, セル*2)): 範囲を入力として指定した MAP 関数。
MAP(A1:A5, B1:B5, LAMBDA(セル 1, セル 2, MAX(セル 1, セル 2))): 複数の範囲を入力として指定した MAP 関数。
MAP(UNIQUE(A1:A10), LAMBDA(数値, 数値 + 1)): 配列を入力として指定した MAP 関数。

参照：MAP関数

LAMBDAという言葉が入っていますので、BYROW関数と同じLAMBDAヘルパー関数です。
上記の例では、実は1番目はBYROW関数と同じ引数、同じ結果が得られます。
今回は2番目の例を活用する方法を紹介します。

今回の記事の例

今回使用したスプレッドシートは下記のリンクから見ることができます。合わせてご覧ください。
https://docs.google.com/spreadsheets/d/1gyRHe9obHOWCKaaWt8tk-34kMHWEpMODiEp4g2awyDc/edit#gid=1306874598

MAP関数の引数

MAP関数では、複数の引数を指定しますが、前半にセル範囲を複数個、後半にLAMBDAを指定します。
BYROW関数に似ていますが、ポイントはセル範囲を複数指定できるところになります。

=map(E5:E8,F5:F8,lambda(pref,threshold, ~~~~))

というように書きます。
前半は、セルの範囲を、行数が同じになるように複数列を指定します。

後半のLAMBDA内は、BYROW関数と同じように、まず変数名を指定して、その後に処理を記述します。
指定する範囲が複数なので、変数名も同数分指定します。
上記の例では、E列とF列の2列を指定しているので、変数名もpref、thresholdと2つ指定しています。

LAMBDAの中での処理

LAMBDAの中はBYROW関数と同じように処理を記述しますが、指定した範囲が複数なので、それぞれの変数名を使って処理を記述します。
QUERY関数と組み合わせてみます。
QUERY関数については、下記の記事をご覧ください。
https://note.com/kzautomation/n/n0b76dcbedec7

今回は下記のQUERY関数と組み合わせてみました。
query(A:C,"select count(A), avg(C), sum(C) where A like '%"&pref&"' and C >= "&threshold&" label count(A) '', avg(C) '', sum(C) ''" )

select count(A), avg(C), sum(C)の部分がA列の値の数、C列の値の平均、C列の値の合計を表示することを意味しています。
where A like '%"&pref&"' and C >= "&threshold&"の部分が、A列の値がprefの文字で終わるものを対象に、C列の値がthreshold以上の値になっているものを抽出するという条件を意味しています。
label count(A) '', avg(C) '', sum(C) ''の部分は、それぞれの結果にラベル不要であることを意味しています。

つまり、都道府県別にC列の値がthreshold以上のものについて、A列の値の数、C列の値の平均、C列の値の合計を抽出するということを意味しています。

QUERY関数は該当する行がない場合はエラーになるため、IFERROR関数を使ってエラーが出た場合は0を表示するようにします。

これらを組み合わせると関数は
=map(E5:E8,F5:F8,lambda(pref,threshold, iferror(query(A:C,"select count(A), avg(C), sum(C) where A like '%"&pref&"' and C >= "&threshold&" label count(A) '', avg(C) '', sum(C) ''" ),{0,0,0})))
となります。

こんなことしなくてもできるのでは？

今回の例では、COUNTIFS関数、AVERAGEIFS関数、SUMIFS関数を使っても同じことができます。
=countifs($A:$A,""&$K5,$C:$C,">="&$L5)
=averageifs($C:$C,$A:$A,""&$K5,$C:$C,">="&$L5)
=sumifs($C:$C,$A:$A,"*"&$K5,$C:$C,">="&$L5)

ARRAYFORMULA関数を使うとうまく計算できません。

BYROW関数では引数の範囲が一つしか指定できないためうまく計算できません。

今回の都道府県の例であれば、COUNTIFS関数、AVERAGEIFS関数、SUMIFS関数を使うことで同じことができますが、4種類しかない場合はそれで十分ですが、100種類、1000種類となると、それぞれの関数を記述しておく必要があります。

一方で、MAP関数であれば、下記のように行範囲を下の表まで指定しておけば、新しいデータが追加されたときに自動で計算してくれます。
=map(W5:W,X5:X,lambda(pref,threshold, if(pref <>"",iferror(query(A:C,"select count(A), avg(C), sum(C) where A like '%"&pref&"' and C >= "&threshold&" label count(A) '', avg(C) '', sum(C) ''" ),{0,0,0}),{"","",""}))

都道府県の種類が増える機会はかなりのレアケースになってしまいますが、例えば、抽出の条件を増やしてみて、川県という行を追加しました。
最後に川がつく県を対象にしています。
このように分類としきい値を増やすだけで、新しいデータが追加されたときに自動で計算してくれます。

データが次々増えていくという場面において、MAP関数で処理を記述しておくことで、データ追加時に自動で計算してくれるというメリットがあります。

まとめ

MAP関数は、ARRAYFORMULA関数、BYROW関数で対応できない場合に使うことができる関数です。
特に、複数の範囲を指定して処理を記述することができるため、データが次々増えていく場面において、データ追加時に自動で計算してくれるというメリットがあります。
スプレッドシートの作業効率を上げるために、MAP関数を使いこなしてみてください。