Arrayformula関数とは？便利な使い方や実例も！（Googleスプレッドシート）
こんにちは、エドです。
エクセルと似たような使い方ができますが、スプレッドシートにしかない便利な関数がいくつかあります。
今回は超絶便利な関数「Arrayformula関数」について解説します！
読み方はたぶんアレイフォーミュラ。
Arrayformulaを使う前に、こんな悩みがありませんか？
・データが大量にある時、VLOOKUP関数を全セルにコピーするのは煩わしい
・データの最終行がわからないけど、関数を入れておきたい
・データが増えたり減ったりするときに自動で値を計算したい
・列ごとに関数を入れるのが大変
これらを解決できる神関数がArrayformulaです！
Arrayformula関数とは？
Arrayformula関数は配列変数を受け取りセルに展開してくれる関数です。
なんのこっちゃ？と思われるかもしれませんが、要は複数セルの関数をまとめて1セルで記載できるよ、ということですね。
例えばA1～B4シートの値を1セルに関数を入れるだけで関数の入っていないセルにまでコピーしたりできます。赤セルにだけ関数が書いてあります
=Arrayformula(A1:B4)
この「１つ関数を書くだけで何も書いていないセルまで更新できる」のがArrayformula関数の素晴らしいところです！
※Arrayformula関数はそれだけじゃないんですけど、上記が一番便利な使い方ですね
Arrayformula関数の構文
=Arrayformula( 配列を返す数式 )
配列を返す数式のところに多種多様な数式を書けるのが魅力です
Arrayformula関数の使い方
この関数は本当に色々な使い方があるので、アイディア次第でなんでもできちゃいます。そのなかでも特に便利に使えそうな使い方を紹介します！
１．配列をそのまま返す（セルのコピー）
まずは他のセルの値を丸ごとコピーする方法
=ARRAYFORMULA( [セルの範囲] )
関数を入力したセルはE1だけ！
【解説】
Arrayformula関数は「配列を返す」関数なので、アウトプットも複数返すことができます。この場合は範囲指定でセル情報をA2～B4まで6個分指定しています。
1セルのコピーは「=A2」のように指定しますよね。
それの6個分の入力情報があるということです。6個のセルの情報を受け取ったのでArrayformulaは6個分のセル情報を吐き出しています。
範囲指定には「行列の情報」も含まれています。
ですから行列のそのままコピーされるというわけです。
ちょっとまった、配列ってなに？
配列とは縦と横で構成された二次元の表みたいな考え方です。（三次元以上もあります）
そう考えるとエクセルやスプレッドシートのセルも配列と言えます。
もちろん1列だけでも1行だけでも複数個あれば配列です。
２．複数セルまとめて計算
こんなデータがあったとします。利益の部分を「売上 - 原価」にしたいとき、
通常は 「 = B2 - C2 」と計算しますね。
それを全部のD列のセルにコピペして・・・
これがArrayformula関数なら１セルでまとめて計算できます。
=Arrayformula( B2 : B5 - C2 : C5 )
ちなみに
✕ =Arrayformula( B2 - C2 )
○ =Arrayformula( B2 : B5 - C2 : C5 )
です。
これはArrayformula関数の特徴で「入力側も配列にしないと配列が返らない」からです。実は項番１の範囲指定も「配列」なのです。
３．文字列の一括置換
まとめて関数で置換したい場合、SUBSTITUTE関数を使用します。エクセルではすべてのC列のセルに関数をコピペしなくてはなりません。
しかしArrayformulaを使用すれば1セルに関数を書くだけで実現できます。
=arrayformula( substitute( A2:A5 , "牛乳" , "" ) )
（よくあるミス）
範囲指定するのを忘れていた、もしくはArrayformula関数で括っていないときにこの指定をしても値は１セルしか更新されません。
４．VLOOKUPとの組み合わせ（複数行の一括抽出）
真骨頂です。VlookUPと組み合わせることによって、1列全部のVlookupを1回の関数で対応できちゃいます。
まずは普通のVlookupで１セルだけ値を取得する方法
Arrayformulaを使うとこの通り。赤いセル以外は何も関数は入っていません。
=Arrayformula( VLOOKUP ( A2:A5 , $D$2:$E$5 , 2 , false ) )
注意する点はいつもの通り赤字の範囲指定部分ですね。比較対象のほうも配列で指定してあげる必要があります。
５．VLOOKUPとの組み合わせ（複数行列の一括抽出）
Arrayformulaを使ってVlookUpをすると非常に便利なことがわかりました。
では下図のような場合はいかがでしょうか？
赤いセル２つにそれぞれのArrayformulaで括ったVlookupを設定しますか？もちろんそれでも実現は可能です。
B2セル =Arrayformula(VLOOKUP(A2:A5,$E$2:$G$5,2,false))
C2セル =Arrayformula(VLOOKUP(A2:A5,$E$2:$G$5,3,false))
これも列を配列で返すことによってなんと関数は１つで実現可能なんです。
=Arrayformula ( VLOOKUP ( A2:A5 , $E$2:$G$5 , {2,3} , false ) )
ポイントはVlookupの列を返す部分。{ }で括られていますね。これにより列も配列にすることが可能になります。
関数は赤いセルに１つだけ。2列くらいならあまり恩恵はありませんが、これが10列だったらどうでしょう？便利なことがわかるかと思います。
６．出力するデータの範囲が決まっていない場合
例えば売上データを毎日追加していくタイプの表を運用していたとします。
データは毎日追加されますが、関数をその都度範囲を変更するのは面倒ですね。
今日 =Arrayformula(B2:B5-C2:C5)
次の日 =Arrayformula(B2:B6-C2:C6)
効率厨の私からすると面倒でたまりません。
この場合、範囲を指定せずに列丸ごと指定することも可能です。
この場合、B6、C6が更新されると自動的にD6も更新されるようになります。
=Arrayformula( B2:B - C2:C )
B列、C列の終わりを指定しない（数字を書かないと全列指定、行も同じ）
データがない行に変な値がずらっと並ぶのはがダサいんだけど？
おっしゃる通りでございます。この場合は「ここにデータがある時だけデータを更新する」という指定を入れて解決です。
=Arrayformula( IF ( A2 : A = "" , "" , B2:B - C2:C ) )
赤字が追加した部分です。
「もしA2が空白だったら、空白を返す、それ以外なら計算してね」という意味です。
ここでもIF文は配列で書いてあげる必要があるので注意ですね。
Arrayformula関数の注意点
ここまで見ていただければとても便利に使える関数だということは分かったかと思います。ですが万能に見えるこの関数にも弱点があります。
使えない関数
この関数は「対応していない関数」が存在しています。
・Index関数、Match関数
・Sumifs、Countifs、Ifs関数
・sum、average関数 ※工夫すれば行ける
これらは「基本的には」使用できないので注意しましょう。
特にIndex/Match、お前は対応していてほしかった・・・
更新する範囲に文字を手入力すると全部エラー
Arrayformulaでの出力結果範囲内に、手入力で文字を入れたり他の関数で上書きするようになっているとエラーが発生します。
（めちゃめちゃよくあります）
いかがでしたでしょうか？
今回は神関数Arrayformulaを解説しました！
機会があれば他の機能についても解説してみたいと思います！
