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