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が不要な世界が近づいていることを感じますね。

この記事の内容が良いなと思ったら寄付をしてもらえるととても喜びます。