ARRAYFORMULA関数の活用術 [GoogleSpreadsheet関数]

今回は、ARRAYFORMULA関数の活用術を紹介します。

この関数は、IMPORTRANGE関数やQUERY関数のようにGoogle Spreadsheetのみで使える関数です。

正直、ARRAYFORMULA関数を知った時には、自分の中でプチイノベーションが起きました。

ARRAYFORMULA関数は、配列となる結果を持たせると、その結果を返してくれる関数です。

どういう事かよくわからん。という方は↓を参照してみてください。

https://docs.google.com/spreadsheets/d/1UMoL52wcLQlVG_pS3jtrR52s7TifleqRnTEGS0446vI/edit#gid=0
※セキュリティの都合上、編集はできません。閲覧権限のみの設定としています。

EXCELにはこの種の関数が実装されていないので、イマイチ理解しにくいかと思いますが、解説していきます。

【基本編】

ARRAYFORMULA(A1:B21)

引数に範囲を指定しています。
この「範囲指定」が配列となっています。
本来であれば、D1セルに[=A1]、E1セルに[=B1]、D2セルに[=A2].....というように入力するところを、
範囲指定した引数を一挙に返してくれています。

【応用編その1】

ARRAYFORMULA(VLOOKUP(H2:H12,$A$2:$B$21,2,false))

引数にVLOOKUP関数を指定しています。
しかし、VLOOKUP関数の引数指定に違和感を抱きませんか？
第一引数に注目すると、引数の内容が範囲指定になっています。
これは、【基本編】と似たような感じですね。
本来であれば、単一の指定しかできないところを、範囲指定にする事で、その範囲まで計算を行った
うえで、返り値を受け取ることができるのです。

【応用編その2】

ARRAYFORMULA(vlookup(INDIRECT("L2:L"&COUNTA(L:L)),$A$2:$B$21,2,false))

引数にVLOOKUP関数を指定しています。
ここまでは、【応用編その1】と同様です。
しかし、VLOOKUP関数の第一引数が値ではなく、INDIRECT関数になっています。
更に、INDIRECT関数の中でCOUNTA関数を利用しています。
これは、VLOOKUP関数の範囲指定の末行をL列に存在している値の数にするという計算です。
こうする事で、可変するような行に対応する事ができます。
これがとても便利！プチイノベーション的な利用法でした。
ただし、INDIRECT関数内ではテキストとして列を入力する必要があるため、列の追加に弱いのが
問題です。
まぁ、この部分の解決法は独自関数で、COLUMN関数を引数として、返り値をアルファベットの
列名に変換する事だろうなー。と思っています。
この独自関数は別途開発の上、公開していきます。

今回は、VLOOKUP関数によるサンプルでARRAYFORMULA関数の活用術を書きましたが、SUMIF関数やCOUNTIF関数、IF関数なんかにも、もちろん応用が可能です。結構幅広く応用がききますね。

こんな感じで、とても便利に使えるのがARRAYFORMULA関数です。

おまけに、ARRAYFORMULA関数は内部的に関数の結果をまとめて処理したうえで、返していることから、計算結果を返すスピードが速いです。

特に、計算をさせるカラム数が多ければ多いほど、この計算速度のメリットを享受する事ができると思われます。

ますますEXCELが不要な世界が近づいていることを感じますね。