関数紹介arrayformula

僕が最もよく使い、最も素晴らしい関数だと思っている関数

googleの紹介

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。 詳細

これだけだと全く意味がわからないので、使い方の詳細を紹介して行けたらと思います。

arrayformulaという関数単体では利用できないので、他の関数と組み合わせながら使う関数です！

今回紹介のarrayformulaという関数のメリット

- オートフィルという面倒な作業をなくすことができる

- オートフィルをした結果計算する個数が多くなり、spreadsheetが重くなるのを防ぐことができる

僕はよくvlookup関数なんかと組み合わせることが多く、組み合わせる関数は他にもcountif関数や、sumif関数、find関数、mid関数などとも組み合わせて使います。

さてではこの関数で何ができるかということをご紹介して行けたらと思います。

このような感じのデータがあるとします。

B~C列は各商品と単価を表し、E~F列には販売された商品とその個数が記録されていくものととします。

この時にG列に単価を記載したいとするとvlookup関数とオートフィルを利用して、埋めていくことをしていくかと思います。

このオートフィルの部分を勝手に繰り返す役割を持つのがarrayformulaという関数です。

通常E2セルに対してB2:C6の範囲の内容を参照するvlookup関数を行う時は以下のように関数を記述します

=vlookup(E2,B2:C6,2,false)

通常ですとB2:C6の部分に$などをつけて絶対参照するようにしてから、オートフィルをすることで、参照元の情報がずれないようにしつつ関数の情報をコピーしていくということが通常のやり方かと思います。

今回紹介するarrayformulaでは、画像のように記述します。

=arrayformula(vlookup(E2:E11,B2:C6,2,false))

注目すべき点は通常のvlookupではE2と記述する部分をE2:E11と入力することでE2からE11までvlookupの内容が繰り返されるという点です！

E11まで処理を繰り返すという指示を出すことで以下の画像のように1つの関数で全てのE2~E11まで自動で埋めていくことができるようになっています！

では実務で使う上でどうするか？

実務ではE2~E11と決まっている訳ではなく、どんどん情報が増えていくかと思います。

spreadsheetのセルの数に余裕がある(一つのspreadsheetの制限で500万セルという制限があります)場合には以下のような記述をすることで全ての行に適用することが可能です。

=arrayformula(vlookup(E2:E,B2:C6,2,false))

しかしE2:Eで終わりを指定しない場合以下の画像のように終点がないので#N/Aが最終行まで永遠と繰り返されることになります

vlookupをiferrorなどで囲うことで、見た目上エラーが起きていないようにすることも可能ですが、終点をE列やF列の最終行までと指定することも可能です。

indirectという関数とcounta関数をを組み合わせることで、これらの問題は解決することが可能です。

まず記述としては下記の通り

=arrayformula(vlookup(indirect("E2:E"&counta(E:E)),B2:C6,2,false))

一気に訳がわからなくなりましたね (;´д｀)ﾄﾎﾎ…

indirectの詳しい解説は別記事に譲りますが、

indirect("E2:E"&counta(F:F))という部分について

counta(F:F)でF列の数を数えています。F列のセルに格納されているデータの数は10個

"E2:E"&counta(F:F)でE2:E10ということを表現しています。

indirectなしで"E2:E"&counta(F:F)と記述するとvlookupがどこを指定しているのかわからなくなってしまうのですが、indirectを入れてあげることで、vlookup関数が機能を果たすべき場所が認識できるようにしてあげているということをしています。

なんかもっとうまい解説の記事を用意したいものですね〜

最後の応用編の部分の解説がうまくできませんでしたが、もっとうまい表現がないかということは日々アップデートできればと思います。

今回のarrayformulaという関数のメリット

- オートフィルという面倒な作業をなくすことができる

- オートフィルをした結果計算する個数が多くなり、spreadsheetが重くなるのを防ぐことができる

上記のような素敵なメリットを兼ね備えた素敵な関数であるということが伝わると嬉しいです！！