こんにちは。業界問わず、日々の業務等でデータの管理や効率化にスプレッドシートを使用されている企業も多くなっているかと思います。
何かと便利なスプレッドシートですが、一方で以下のようなお悩みをお持ちの方も多いのではないでしょうか？
- 数式を1つ1つ打っていくのが大変
- 数式を使いすぎて、スプレッドシートがめちゃくちゃ重たくなっている
今回はこれらのお悩みを解決できるArrayformula関数について説明します。
是非、最後までご覧ください。
その他のスプレッドシートの関数は以下の記事にまとめております。
Arrayformula関数について（簡単な説明）
構文：Arrayformula（配列数式）
Arryaformula関数は、スプレッドシート限定の関数で、エクセルでは使用することができません。
しかし、Arrayformula関数は配列数式から返された値を複数行・複数列で表示することができます。
補足：Arrayformula関数のショートカットキーについて
| Windows | Mac | |
| ショートカットキー | Ctrl+Shift+Enter | Command+Shift+Enter |
Arrayformula関数の使用例
文章だけではイメージが掴みづらいと思いますので、実際に一例を紹介します。
以下のように、キャラクターごとに英語・数学・国語の得点を記載し、210点以上であれば合格、それ以下であれば不合格という条件にしました。
IF(F3>=210,”合格”,”不合格”)
エクセルであれば、下記のようにG4:G7セルにも数式を入れる必要が出てきます。
IF(F4>=210,”合格”,”不合格”)
今回の例では数が少ないので大したことはないのですが、何百・何千と数式を入力するのは大変ですし、数が多くなると処理が重くなるので挙動が遅いということにも繋がりかねません。
しかしArrayformula関数を使用すると、一番上のセルにのみ数式を適用させればオートフィルをする必要もないですし、軽量化にもつながります。
Arrayformula関数の使い方は簡単で、関数をArrayformulaで括ってあげること。
それでは実際にArrayformulaで括ってみましょう。
Arrayformula(IF(F3>=210,”合格”,”不合格”))
…あれ、入れたセルにしか数式が適用されてないじゃないか？って思った方もいるでしょう。
実は、Arrayformulaを使うにあたりよくあるミスを犯しているんです。
注目してほしいのは、数式内「IF(F3=>210…」のF3の箇所。
Arrayformula関数を適用させるには、数式を見て判別してほしい範囲を全て選ぶ必要があります。
そのため、正しくは以下のような形式になります。
Arrayformula(IF(F3:F7>=210,”合格”,”不合格”))
今回の場合、F3からF7セルの値が210以上であれば合格・そうでなければ不合格にする、という関数なので対象範囲F3:F7と記入すれば、数式はG3セルのみに入れるだけでオッケーです！
このケースではF3:F7と範囲を細かく指定していますが、何百・何千行と一気に表示させたいときに範囲を特定するのは面倒です。
例えば、1500行目まで一括で適用させたいのであれば、F3:F1500とすれば適用できますが、F列の1501行目以降のセルがすべて空っぽであれば、F3:Fと終了範囲を省略することが可能です。
基本的に、Arrayformulaによる戻り値を適用させる列は空白にしておき、終了範囲には列情報（F列ならFのみ）を入力したほうがいいでしょう。
また、Arrayformula関数は縦だけなく、横にも適用が可能です。
Arrayformula(IF(C20:G20>=210,”合格”,”不合格”))
なお、If関数だけでなく、VlookupやSumifなど様々な関数にもArrayformulaは適用できます。
Arrayformula関数の注意点
Arrayformula関数の注意点ですが、2点ほどあります。
- 関数の適用範囲内に文字等を入れている場合、数式はエラーとなる
- Arrayformulaが適用できない関数が存在する
こちらについて順に説明します。
Arrayformula関数の適用範囲内に文字等を入れている場合、エラーとなる
上図のように、Arrayformulaの出力対象位置のどこか1つでもセル内に文字等が入っていた場合は、数式を出力することができず、エラーとなってしまいます。
何かしら記録するためにセル内にメモしたりする方も多いとは思いますが、Arrayformula関数が出力される範囲は、セル内には何も入れないようにしましょう。
Arrayformulaが適用できない関数が存在する
Arrayformula関数はかなり便利な関数ですが、使用できない関数も存在します。
よく使う関数に絞ると、
- Index、Match関数の組み合わせ
- Sumifs、Countifs、Ifs関数など
が該当します。
Index・Match関数の組み合わせでArrayformula関数は使えない
上図は、Index・Match関数が適用されていない例です。
Arrayformula関数を使っていない場合、Match関数はC10:C12ではなくC10とし、下のセルにも数式をずらして適用させますが、上記の場合はArrayformula関数が適用されておりません。
Index・Match関数の組み合わせは、VlookupとSequence関数を組み合わせることで代用することも可能ですが、ここでは割愛します。
Ifs、〇〇Ifsの関数も使えない
IfsやCountifs、Sumifsなどの関数はArrayformula関数が適用できません。
このあたりの関数は、「あ、Arrayformula関数は使えないんだ」くらいで覚えてもらえれば大丈夫です。
こちらもIndex・Match同様、書き方を工夫することで別の関数で代用することができますが、ここでは割愛します。
まとめ
このように、Arrayformula関数はかなり便利な関数ですし可能性は無限大！ですが、使えない場合や未だ原因が解明されていないエラーなどもあります。
基本的な関数や処理であれば問題なく動きますし、エクセルにはない関数ですので、この関数があるからスプレッドシートを使う！という人もいるほどです。
今まで1つ1つ関数をセルに適用させて大変、という方はこの機会に是非Arrayformula関数を使ってみてはいかがでしょうか。
