みなさまおつかれさまです。
いきなりですが、Googleスプレッドシートの関数のひとつ「Arrayformula関数」はご存知ですか？
普段業務でスプレッドシートを活用している方は多くいらっしゃると思いますが、それなりに活用している方でも意外にも知られていない関数で、かつ知っているだけでスプシテクが数段一気にレベルアップするような可能性を秘めた関数です。
Arrayformula関数はこんな悩みを解決してくれます。
- ・数式を1つずつ打ちこんでいくのが大変すぎる
- ・スプレッドシート内で数式を入れすぎて、シート自体がとんでもなく重たくなってしまっている
この関数は特に営業職やウェブマーケターなど、日々膨大な取引データをスプレッドシートを使って分析する機会のある方は必見です。
まだご存知でない方、名前だけは知っているという方、ぜひこちらを読んでスプレッドシートの達人へ昇華していってください！
目次
Arrayformula関数とは
この関数の良くないところは、調べてみてもわかりにくいことです。
Google公式のページでは、
配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
ドキュメントエディタヘルプより
ね。全然わかりませんよね。
わかりやすくいうとこの関数は、「数式を１つのセルに入れるだけで、指定した範囲すべてに数式の結果を反映してくれる」、関数です。
実践で見てみましょう。
使い方
例えば、こんなリストがあったとします。
テストが70点以上を合格とし、合否の列にそれぞれの結果を表示したいとします。
この場合は、IF関数を使ってこのように記述するのが一般的ですよね。
こうすることで、ほしい合否の結果が入力されました。
上記の例ではそもそも数が少ないので大したことはないのですが、もしこれが500列、はたまた1000列以上あるような膨大なデータの場合にはどうでしょう。
すべてのセルに数式を入力するのは工数的にもかなり大変ですし、数式が多くなるとスプレッドシート側での処理に負荷を与えるため重くなってしまうことにもありえます。
そこで「Arrayformula関数」が役に立ちます。
先程のシートで実践してみます。
合否の結果を表示する一番上のセルを選択して、数式＝のところに「=Arrayformula()」で数式全体をくくります。
実はArrayformula関数はショートカットがあります。
これだけではまだこのセルにしか合否結果は反映されません。
もう一つ、計算してほしい部分を範囲選択する必要があります。
今回であればIF関数の中を、「C3」セルではなく「C3:C16」と記述することで範囲選択になります。
すると…
熊倉菊男さんより下のメンバーの合否結果も、数式を入れていないのに反映されるようになりました。
このように、Arrayformula関数を使うことで、一つのセルに数式を入れるだけで表示させたい他のセルにも結果を反映させることができるのです。
Arrayformula関数の魅力
改めて整理すると、Arrayformula関数を使うメリットとしては、以下が挙げられます。
- ほしい結果を一つの数式で表示できるため、数式の入力の工数が削減できる
- リストの途中で新たにデータを挿入したとしても、漏れる心配がない
- 膨大なデータの処理を行う場合でも、スプレッドシートの処理が重くなりにくい
スプレッドシートを多用される方やデータ分析をされる方は必ず抑えておきたい機能が盛りだくさんですね…！
Arrayformula関数の注意点
かなり万能に見えるこの関数にも、実はいくつか注意点があります。
Arrayformulaが適用できない関数が存在する
実はArrayformula関数が使えない関数があります。
SUM関数
実はSUM関数は使えません。
理由としては、SUM関数は範囲選択したら個別に計算するわけではなく、一括で合算処理するため、帰ってくる値が1つしかないためです。
これを初めて知る方はさぞ残念そうな気持ちになっているでしょう。
「一番使う頻度が高いSUM関数が使えないなんて意味ないじゃん！」
そんな方に朗報です。
TIPS：SUM関数での使い方
SUM関数では使えませんが、SUM関数と同じ動作を、他の関数を組み合わせることでArrayformula関数の機能をつけることができます。
それがこちら。
=MMULT(範囲,TRANSPOSE(ARRAYFORMULA(範囲の1行目^0)))
なかなか見慣れない関数かと思います。
MMULT関数は2つの行列の行列積を計算する関数で、計算にあたってTRANSPOSE関数で行と列を入れ替えてうまく合計値を計算しています。
詳細の説明はしませんが、もしSUM関数をArrayformula関数で使いたい場合には、上の数式をコピペして計算しましょう。
AVERAGE関数
SUM関数と同様の理由で、AVERAGE関数も使えません。
ただAVERAGE関数は平均値を取得するだけなので、普通に（合計÷個数）の式で記述すれば解決できると思います。
Index関数、Match関数
Index関数、Match関数も使えません。
ただ後でご紹介する、Arrayformula関数と相性のいい関数の一つにVLOOKUP関数がありますので、こちらも代用可能です。
使える関数でも、記述が少し複雑になることがある
Arrayformula関数を適用するにあたって、普段どおりの数式では機能しないことがあります。
例えばIF関数。
先程の合否判定で、「複数条件」の場合を考えてみましょう。
もしテストが2つあり、「両方で70点以上取ることが合格の条件」の場合を考えます。
この場合、普通であればIF関数とAND関数を組み合わせて
【=IF(AND(条件1,条件２)”TRUEの表示”,”FALSEの表示”)】
で記述するのが一般的ですよね。
でも実は、この数式にArrayformula関数を組み合わせても、うまく反映されません。
この理由を説明するのは少しむずかしいのですが、AND関数を使ってしまうと、それぞれの計算ではなく、選択した部分全体を一括で処理してしまうために、個別の結果が帰ってこないのです。
（同じようにSUM関数では複数選択しても合算して処理してしまうために、値は１つしか帰ってこないですよね。）
なので、AND関数を使わずに記述する必要があるのです。
正しくはこのように記述します。
【=ArrayFormula(IF((C47:C60>70)*(D47:D60>70),”合格”,”不合格”))】
IF関数の中でそれぞれの条件式を「＊」でくくることで、実質的にAND関数と同じ意味合いとなります。
このように、使う関数によってはいつもと同じ感覚で記述してしまうと、値が帰ってこないことがあります。
使用の際には、都度調べることが重要になりますね。
Arrayformula関数とよく組み合わせられる関数
Arrayformula関数と相性がいい（よく使用する）のは以下です。
- IF関数
- VLOOKUP関数
おそらくこの2つが世の中的にも最もArrayformula関数が使われるシチュエーションだと思います。
今回VLOOKUP関数の場面は取り上げませんでしたが、一列すべてを検索値から引っ張ってくる場合には数式の入力が大変なため、Arrayformula関数はかなり重宝すると思います。
まとめ
いかがでしたでしょうか。
意外と知られていない関数ですが、データ分析界隈ではかなり重宝されている関数です。
ショートカットキーが与えられているところを見るに、Googleからも推奨されてそうですね笑。
私はスプレッドシートの精通具合は、「Arrayformula関数を使いこなせているかどうか」が一つの判断軸になると考えています。
知っているだけで効率的なデータ分析に素早く移行できる素晴らしい関数ですので、ぜひこの機会にマスターしましょう！
ではまた次の記事で。
