今回は、私がよく使っているARRAYFORMULA関数をご紹介したいと思います。

▼今回紹介すること

• ARRAYFORMULA関数の使い方
• ARRAYFORMULA関数と組み合わせて使えない関数と使える関数
• ARRAYFORMULA関数を使うメリットとデメリット

▼ARRAYFORMULA関数の使い方

ARRAYFORMULA関数とは。私なりに説明すると、「計算式をひとつのセルに入れるだけで、指定した範囲すべてに計算式の結果を表示してくれる関数」です。

早速ですが、たとえば、以下のような売上リストがあったとします。

製品の情報（JANコード・型番）と単価、数量、合計（単価×合計）が一覧になっているオーソドックスな表です。ただ、消費税の計算の仕方が異なっています。消費税の処理に「外税/伝票計」と書いてある製品は合計金額が「税抜価格」になっていて、「内税」と書いてある製品は合計金額が「税込価格」になっています。この売上リストの合計金額を「税抜価格」に揃えたいと思います。消費税は10%とします。

（やりたいこと）

• 合計金額を「税抜価格」に揃えたい

（条件）

• 消費税の処理に「外税/伝票計」と入力されていたら合計金額には「税抜価格」が入力されている
• 消費税の処理に「内税」と入力されていたら合計金額には「税込価格」が入力されている
• 消費税は10%とする

「税抜価格」を求めるために、私はこんな計算式を作りました。

=IF(F4=”内税”,E4/1.1,E4)

この計算式は、「もし、セル：F4に「内税」と入っていたら、セル：E4の値を1.1で割った値を入れてください、セル：F4に「内税」が入っていなかったら、セル：E4の値を入れてください」という意味になります（なぜか私はお願い口調で覚えてます（汗）。

すなわち、消費税の処理に「内税」と入力されていたら、合計金額を1.1で割って税抜価格を求め、消費税の処理に「内税」以外が入力されていたら、合計金額をそのまま入力することになります。

この計算式をセル：H4に作り、セル：H5〜H12に計算式をコピペすると、それぞれの製品の「税抜価格」を求めることができます。

では、ARRAYFORMULA関数を使うと計算式はどう変化するでしょうか。結論から書くと、こうなります。

=ARRAYFORMULA(IF(F4:F=”内税”,E4:E/1.1,E4:E))

やたら計算式が長くなって、訳がわからなくなったようにも思えますが、順を追って理解すればとてもシンプルです。

【Step 1】

まず、始めに作った「=IF(F4=”内税”,E4/1.1,E4)」の頭に「ARRAYFORMULA(」をくっつけます。

=ARRAYFORMULA(IF(F4=”内税”,E4/1.1,E4))

【Step 2】

次に、参照するセルひとつを指定するのではなく、参照するセルの範囲を指定します。範囲は、SUM関数で使う「コロン（：）」を使います。「コロン（：）」を「から（〜）」に読み替えると理解しやすいです。

=ARRAYFORMULA(IF(F4:F=”内税”,E4/1.1,E4)）

※「F4:F」は「セル：F4からF列の最終行まで」という意味になります。行が増える可能性がある場合や最終行を調べるのが面倒な時に使える便利な書き方です。

【Step 3】

計算結果もセルではなく、セルの範囲に書き換えます。

=ARRAYFORMULA(IF(F4:F=”内税”,E4:E/1.1,E4:E))

※ 「E4:E」は「セル：E4からE列の最終行まで」という意味になります。

はじめに作った計算式から【Step 1】〜【Step 3】の計算式を順番に並べてみると、ちょっとずつ文字が追加されているだけなのがわかります。

• 【はじめ】 =IF(F4=”内税”,E4/1.1,E4)
• 【Step 1】=ARRAYFORMULA(IF(F4=”内税”,E4/1.1,E4))
• 【Step 2】=ARRAYFORMULA(IF(F4:F=”内税”,E4/1.1,E4))
• 【Step 3】=ARRAYFORMULA(IF(F4:F=”内税”,E4:E/1.1,E4:E))

はじめに作った計算式とARRAYFORMULAを追加した計算式をいきなり見比べると、長くてよくわからなくなったように思えますが、順を追って見てみると、「ARRAYFORMULA」をくっつけて、指定したり参照したりする「セル」を「セルの範囲」に変更していただけでした。仕組みがわかってしまえば、まずは怖くないですね！

また、ARRAYFORMULA関数で指定する範囲は1列だけでなく、複数の行列を指定することもできます。

• ARRAYFORMULA関数を使わない計算式：=A4
• ARRAYFORMULA関数を使った場合の計算式：=ARRAYFORMULA(A4:C)

（ARRAYFORMULA関数を使わない場合）

セル：E4に入力した計算式「=A4」をセル：E4からセルG9までコピペすると、セル：A4からC9のデータが転記されます。

（ARRAYFORMULA関数を使った場合）

計算式「=ARRAYFORMULA(A4:C)」をセル：E4に入れるだけで、セル：A4からC9のデータが転記されます。

▼ARRAYFORMULA関数と組み合わせて使えない関数と使える関数

ARRAYFORMULA関数と組み合わせて使えない関数

前述の「▼ARRAYFORMULA関数の使い方」でご紹介したとおり、ARRAYFORMULA関数を使うときは指定したり参照したりする「セル」を「セルの範囲」に書き換えていました。ということは、この書き換えができない計算式では、ARRAYFORMULA関数を使うことができません。ARRAYFORMULA関数を使えない代表例が、SUM関数です。

・【使えない】SUM関数の場合

たとえば、製品ごとに毎月の出荷数をまとめた表があり、その合計を計算させたいとき。

=SUM(B3:D3)

で計算することができます。

では、先ほどの「セル」を「セルの範囲」に書き換えるルールを使ってこの計算式でARRAYFORMULA関数をくっつけてみると、

=ARRAYFORMULA(SUM(B3:B:D3:D))

になるはずなのですが、やってみるとエラーが出てしまいます。

セルの範囲はあくまでコロンの前が開始セル、コロンの後ろが終了セルのルールなので、こんなにコロンがあると、どこからどこがなんの範囲なのかが判別できないみたいです。計算式の中にもともと「セルの範囲」が入っている場合は、ARRAYFORMULA関数を使うことができません。こんなときは、ちょっと工夫して、

=B3+C3+D3

に書き換えてみます。この計算式であれば、ARRAYFORMULA関数をくっつけて「セル」を「セルの範囲」に書き換えればきちんと計算してくれます。

=ARRAYFORMULA(B3:B+C3:C+D3:D)

ちなみに、これでもいけます。

=ARRAYFORMULA(SUM(B3:B,C3:C,D3:D))

実際は、合計を出したいデータの個数が10個、20個、30個とあると思うのですが、私の場合は、あとあとのメンテナンスが楽なんだからと自分に言い聞かせて、地道に、

=A1+B1+C1+….

とすべてプラス（+）でセルを足し合わせて計算式を作ります。そして、また地道に、

=ARRAYFORMULA(A1:A+B1:B+C1:C+…)

と書き換えています。

・【使えない】AVERAGE関数の場合

=AVERAGE（B3:D3)

も使えません。元々、計算式に範囲が含まれているからです。SUM関数のときと同じように、

=(B3+C3+D3)/3

にすれば、

=ARRAYFORMULA((B3:B+C3:C+D3:D)/3)

という具合に使うことができます。

ARRAYFORMULA関数と組み合わせて使える関数

・【使える】VLOOKUP関数の場合

先ほど、計算式に「セルの範囲」が含まれていたらARRAYFORMULA関数は使えないと書いたばかりですが、 VLOOKUP関数の場合は、検索キーにあたるセルを「セルの範囲」に書き換えることでARRAYFORMULA関数が使えます。

=VLOOKUP(H4,$A:$E,5,false)

にARRAYFORMULA関数をくっつけると、

=ARRAYFORMULA(VLOOKUP(H4:H,$A:$E,5,false))

なります。検索キーにあたる「セル：H4」を「H4:H」に書き換えて、他はそのままです。ただ、VLOOKUP関数は、検索キー（セル：H4）が検索する表の範囲内（$A:$E）に存在しないと「#N/A」というエラーが出てしまいます。そこで、IFERROR関数を挟みます。

=ARRAYFORMULA(IFERROR(VLOOKUP(H4:H,$A:$E,5,false)))

こうしておくと、検索キーが表の範囲内になくてエラーが出ても、セルには「#N/A」が表示されず、空欄になります。

・【使える】IF関数の場合

冒頭の「▼ARRYAFORMULA関数の使い方」でご紹介した計算式は、実はIF関数にARRAYFORMULA関数をくっつけたものでした。

=ARRAYFORMULA(IF(F4:F=”内税”,E4:E/1.1,E4:E))

また、先ほどご紹介したVLOOKUP関数の例で使ったIFERROR関数の代わりに、IF関数を使うこともできます。

• IFERROR関数を使った場合：=ARRAYFORMULA(IFERROR(VLOOKUP(H4:H,$A:$E,5,false)))
• IF関数を使った場合：=ARRAYFORMULA(IF(H4:H=“”,“”,VLOOKUP(H4:H,$A:$E,5,false)))
  • （元の計算式）=IF(H4=“”,“”,VLOOKUP(H4,$A:$E,5,false))

IFERROR関数だとなにかしらのエラーが発生したときに空欄になってしまうので、どんなエラーが出ているのかに気づきづらいかもしれません。その点、IF関数で「セル：H4が空白だったら、空白」という条件にしておけば、万が一エラーが発生した場合に、「セル：H4が空白（何もデータが入力されていない状態）であること以外のエラーが発生している」ということに気づくことができます（たとえば、検索キーが表の中に存在しないなど）。

VLOOKUP関数とIF関数はARRAYFORMULA関数と組み合わせて使うヘビロテパターンです。私はあまり使ったことがないですが、SUMIF関数やCOUNTIF関数も使えるみたいです。

・【使える】SUMIF関数の場合

• ARRAYFORMULA関数なしの場合の計算式：=SUMIF(E:E,A4,I:I)
• ARRAYFORMULA関数ありの場合の計算式：=ARRAYFOMRULA(SUMIF(E:E,A4:A,I:I))

・【使える】COUNTIF関数の場合

• ARRAYFORMULA関数なしの場合の計算式：=COUNTIF(E:E,A4)
• ARRAYFORMULA関数ありの場合の計算式：=ARRAYFOMRULA(COUNTIF(E:E,A4:A))

▼ARRAYFORMULA関数を使うメリットとデメリット

Googleのヘルプページ（ARRAYFORMULA – ドキュメント エディタ ヘルプ）に、ARRAYFORMULA関数とは「配列数式が（なんとかかんとか。。。）」と書いてあって、はじめはなんだかよくわからないなぁと感じたかもしれませんが、だんだん使えそうな気がしてきたでしょうか。慣れてくるとかなり便利なARRAYFORMULA関数なのですが、実は、ARRAYFORMULA関数を使うとよいシチュエーションと使えないシチュエーションがあります。ARRAYFORMULA関数のメリットとデメリット、使えるシチュエーションと使えないシチュエーションをまとめてみました。

メリット

1. 計算式をひとつ入れれば、指定した範囲すべてに計算結果を表示してくれる。計算式を変更するときは、そのひとつの計算式を変更すればよいので、変更モレを防げる。
2. 行を追加したときに計算式がうっかり抜けてしまうことを防げる。
3. 勝手にデータを変更して欲しくない場合、ARRAYFORMULA関数の範囲に指定されているセルにデータを入力すると、それ以外のデータが消えてエラーが発生するので、実質、変更ができない状態にできる（デメリット2.の逆）。
4. 入力する計算式が少ない分、ファイルの動きが軽くなる（と言われているけれど、私は実感したことがない。7万行とか10万行といった表だと、表が大きすぎて計算式云々のまえにファイルが重いのかもしれない）。

デメリット

1. 見慣れない計算式になってしまい、わかりづらい。
2. 計算式の範囲に含まれるセルに何かデータを入力すると、そのセル以外の範囲に入力されていたデータがエラーのために表示されなくなってしまう（メリット3.の逆）。
3. 計算式を入れたセル以外は、どのような計算をしているかがわかりづらくなる。

たとえば、JANコードを入力すると、「単価」が自動で入力され、「数量」を自分で入力すると、「合計」が自動で計算される表を作ったとします。「単価」はVLOOKUP関数で『商品データベース』から入力させ、「合計」は「単価×数量」という計算式を入れておきます。

セル：G4に入力した

=VLOOKUP(E4,A:C,3,false)

という計算式をセル：G5からG10までコピペすると、G列すべてに単価が自動入力されるようになります。この計算式にARRAYFORMULA関数をくっつけると、

=ARRAYFORMULA(VLOOKUP(E4:E,A:C,3,false))

になります（後ほど説明しますが、ARRAYFORMULA関数に書き換えたら、先ほどコピペしたセル：G5からG10の計算式は削除しておきます）。I列は、セル：I4に

=G4*H4

を入力して、セル：I4からI10までにコピペしておきました。

ここで、11行目に行を追加して、セル：E11にJANコードを入力します。すると、『商品データベース』からセル：G11に単価が転記されました。

行を増やしたところに計算式を他のセルからコピペしなくても、きちんと計算してくれる ことがARRAYFORMUA関数を使うときの最大のメリットだと私は感じています。逆に、セル：I4に入力した

=G4*H4

はARRAYFORMULA関数にしていないので、行を増やしたセル：I11には計算式が入っておらず、空欄になっています。セル：I10の計算式をコピペすれば事足りることですが、何百行、何千行とある表では作業の途中で行を追加したけれど、一番下の行まで計算式が入っているかを確認するのを忘れて、そのままピボットテーブルにしてしまったという苦い経験が私にもあります。行が増える可能性がある表は、ARRAYFORMULA関数を使っておくと安心です。

では、この表の「単価」を一部修正してみます。売上リスト 7行目の「3456789123456 AQUOS Sense3 用ケース」を、単価 3,000円から300円値引きして売ったので、単価を2,700円に入力し直します。

大事件が起こりました（汗）。入力した「2700」以外の単価が消えてしまいました。単価が消えたので合計も消えてしまいました。セル：G4の「#REF!」にカーソルをあわせて、エラーの内容を確認すると「配列結果は G7 のデータを上書きするため、展開されませんでした。」と書いてあります。

つまり、私がセル：G7に「2700」と入力したために、エラーが発生してしまったのです。これもARRAYFORMULA関数の大きな特徴です。ARRAYFORMULA関数は計算式で指定した範囲に、データや計算式を入力すると、エラーが発生してしまいます。

この特徴をメリットと捉えると、誰かがうっかりデータを入力してしまった場合、他のデータがすっかり消えてしまうので、この表は運用上、勝手にデータを変更してはならないことになります。逆に、この特徴をデメリットと捉えると、「値引きしたから今回だけ単価を変更したい」といった変更作業が一切できないことになってしまいます。なので、私は

• データを手入力する必要のないところやデータを勝手に変更されては困るところはARRAYFORMULA関数を使う
• データを手入力で直す可能性があるところはARRAYFORMULA関数を使わず、通常通りの計算式を入れる

というように使い分けています。ARRAYFORMULA関数を使っていない表に行や列を追加するときは、追加した分の計算式が入っているかどうかを確認することは忘れずに。ちなみに、本筋からはずれますが、「計算式が入っているセルに色をつける」という設定ができるのでよく使っています。設定の仕方は以下のとおりです。

• 色をつけたい行や列を選択して、「条件付き書式設定」を開く。
• 「セルの書式設定の条件…」のプルダウンから「カスタム数式」を選択して、「値または数式」の欄に「=ISFORMULA(G1)」と入力する。計算式の「G1」の部分は指定している範囲の一番左上のセルを入れる。

▼まとめ

• ARRAYFORMULA関数の使い方
  • 計算式の頭に「ARRAYFORMULA(」をつける
  • 計算式の中の「セル」を「セルの範囲」に書き換える　例）H4 → H4:H
• ARRAYFORMULA関数と組み合わせて使えない関数と使える関数
  • 使えない関数→SUM関数、AVERAGE関数（ただし、計算式を工夫することで、ARRAYFORMULA関数と組み合わせて使えるようにできる）
  • 使える関数→VLOOKUP関数、IF関数、SUMIF関数、COUNTIF関数 など
• ARRAYFORMULA関数を使うメリットとデメリット
  • （メリット）行や列を増やしても、計算式をコピペする必要がない
  • （メリットでもありデメリットでもある）ARRAYFORMULA関数で指定している範囲にデータを入力するとエラーが発生するため、表の使い方に応じて、ARRAYFORMULA関数を使うかどうかを判断する

組み合わせることができる関数・できない関数、使えるシチュエーション・使えないシチュエーションなど、自分で判断できるようになるまでにちょっと時間がかかるかもしれませんが、いったんいつも通り計算式を作ってみて、ARRAYFORMULA関数をくっつけてエラーが出たら、原因とか考えずはじめに作った計算式に戻すことを繰り返していくうちに、使えるときと使えないときがなんとなくわかってきます。組み合わせることができる関数は、私がまだ試していないものもあると思いますので、他にも使ってみたいシチュエーションがあれば是非チャレンジしてみてください。

★

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

 
▶ 【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技
▶ 計算式のコピペ漏れを防ぐ！　気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜（本記事）
▶ VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
▶ Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
▶ IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
▶ 値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
▶ Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう！
▶ 「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜
▶ わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」
▶ 地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則（プルダウン）用のデータリストをつくる方法
▶ Googleスプレッドシートのフィルタ表示機能が便利
▶ Googleスプレッドシートでチェックリストを5分で作ってみる
▶ 営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた