ARRAYFORMULAで関数の打ち直しとおさらばする

Googleスプレッドシートやエクセルなどで、特定の列に対して関数を組むことは多いと思います。ただ、修正が必要な時や書き換えたい時、関数を書き直してさらに全行コピーし直すのって物凄く面倒じゃないですか？
そんな時に便利なのが「ARRAYFORMULA関数」です。Googleスプレッドシート限定ですが、使いこなせば作業効率がかなり上がるはずなので、この記事で紹介します。まだ使っていない方はぜひ使ってみてください！

ARRAYFORMULA関数とは

ARRAYFORMULA関数とは、数式を配列形式で表示させることが出来る関数です。例えばVLOOKUP関数を使われる方は多いと思いますが、値を表示したいセルすべてに関数を組むのが一般的です。
これに対し、ARRAYFORMULAを使えば、1つのセルに関数を組み、その中に配列を記載することで、複数のセルに値を返すことが出来ます。
このARRAYFORMULAのメリットは、作業効率が上がることです。関数を記載するコストやメンテナンスコストがかなり下がります。

ARRAYFORMULAの使い方の例

実際に使い方を見てみると、わかりやすいです。

【1】VLOOKUP関数と組み合わせる

例えば下記のような商品の購入データがあり、別の「商品マスタ」を使ってIDと商品名を紐づけたいとします。

この時、ARRAYFORMULAを使わない場合はE2に下記の関数を入れて、行の終わりまでコピペするのが普通でしょう。

=VLOOKUP(C2,$G$3:$H$11,2,0)

しかし、ARRAYFORMULAを使えば、最初の行で関数を組むだけで終わりの行までVLOOKUPを適用させることが出来ます。

=ARRAYFORMULA(VLOOKUP(C2:C,$G$3:$H$11,2,0))

ポイントは検索キーの「C2」をARRAYFORMULA適用後は「C2:C」としているところです。これで、最下部の行までVLOOKUPが適用されます。

ついでに「IFERROR」も組み合わせて「#N/A」が出ないようにしましょう。

=ARRAYFORMULA(IFERROR(VLOOKUP(C2:C,$G$3:$H$11,2,0)))

このように、ARRAYFORMULAを組み合わせることで1つのセルで関数を組むだけで、簡単に欲しい結果を得られるようになりました。

【2】IF関数と組み合わせる

ARRAYFORMULAは様々な関数と組み合わせられますが、私がよく使うのはIF関数です。例えば先ほどと同じデータで、商品単価が5000円以上の場合、”高単価フラグ”をつけたいというニーズがあったとします。

通常の場合は下記のように記載し、下の行までコピーしていくと思います。

=(IF(D2>=5000,1,0))

ARRAYFORMULAを使う場合はこうなります。D2セルだけでなく、D2:Dで範囲を指定しています。

=ARRAYFORMULA(IF(D2:D>=5000,1,0))

無事、フラグを立てることが出来ました。

【3】ROW関数と組み合わせて連番を自動生成する

地味に便利なのが、連番作成です。たまに資料として連番の記載が必要な時に、番号を記載した後に行の順番を修正しなければいけない時ってありませんか？手動入力している場合は都度書き直しになりますが、ARRAYFORMULAで記載しておけば自動で連番を生成できます。
例えば下記の場所に連番を作成したいとします。

通常の場合だと下記のように関数を記載し、下の行までコピペする形になります。もしくは数字を入力し、コピペです。

=ROW()-1

これをARRAYFORMULAで作成するとこうなります。

=ARRAYFORMULA(ROW(A2:A)-1)

これで行を入れ替えたり追加しても連番が自動で生成されるようになりました。地味に便利です。

ARRAYFORMULAを適用させる範囲を自動化する

今まで紹介した例では、ARRAYFORMULAを記載する際の配列を『D2:D』のように列全体としていました。ただ、作りたいものによって、範囲を『D2:D15』のように限定していて、データが追加される度に範囲を拡張したい場合もあると思います。事例を踏まえてお話します。
今G列には、

=ARRAYFORMULA(IF(E2:E15>=5000,1,0))

が入っています。
青で囲った部分が元のデータで赤が加えられたデータとします。
現状では青のデータに対してIF関数でフラグを立てています。

追加された赤の部分のデータに対してもフラグを立てたいですが、ARRAYFORMULAの範囲は『E2:E15』となっているので、本来であればこれを『E2:E23』に書き直さないといけません。
しかし毎回書き直すのはあまりにも面倒なので、「INDIRECT」と「COUNTA」を使います。

【元の関数】

=ARRAYFORMULA(IF(E2:E15>=5000,1,0))

【変更後】

=ARRAYFORMULA(IF(E2:INDIRECT("E"&COUNTA(E2:E)+1)>=5000,1,0))

ポイントは、「E2:E15」を「E2:INDIRECT("E"&COUNTA(E2:E)+1)」に書き換えているところです。COUNTAの箇所で最終行の番号をもらい、INDIRECTで最終行の、今回の場合は「E23」にして使っています。
これにより、今後データが加えられても、自動でARRAYFORMULAの範囲も加えられるので、関数を組み直す必要がなくなりました。

以上、ARRAYFORMULA関数の紹介でした。めちゃめちゃ便利なので、使ったことがない方はぜひ使ってみて下さい！
最後までお読み頂きありがとうございました。

------------------------------------
よかったらtwitterのフォローもどうぞ。
https://twitter.com/MasayukiAbe7
------------------------------------