スプレッドシートで同じ数式を何十行もコピペしていませんか？
行が増えるたびにコピーし直すのは地味に手間がかかりますし、コピー漏れで空白行ができてしまった経験がある方も多いはずです。
ARRAYFORMULA関数を使えば、1つの数式を1つのセルに入れるだけで、その計算を列全体に自動で広げることができます。
とはいえ、いざ使ってみると「1行しか出ない」「エラーが出た」とつまずきやすい関数でもあります。
この記事では、ARRAYFORMULAの基本の使い方から、初心者がよく引っかかるポイントまでまとめてご紹介します。
ARRAYFORMULA関数とは？何ができるのか
ARRAYFORMULAは、1つのセルに入力した数式を複数のセルに一括で適用できる関数です。Excelにはない、Googleスプレッドシート特有の関数のひとつです。この関数を使うと、数式のコピペ作業そのものが不要になります。
僕、いつも同じ数式を下までズドーンってコピペしてるんだけど、それが要らなくなるってこと？
そうそう。一番上のセルに1つだけ数式を入れれば、あとは自動で下まで展開されるよ。
ARRAYFORMULAは、1つの数式を複数セルに一括適用するための関数である。数式管理が1か所に集約されるのが最大の特徴である。
オートフィルとの違い・使うメリット
オートフィル（数式のコピペ）でも同じ結果は作れますが、行を追加するたびにコピーし直す必要があり、コピー漏れや参照ずれのミスが起きやすくなります。ARRAYFORMULAなら数式が1か所にまとまるため、修正するときも1回で済みます。
後から数式を直したくなったとき、1個直せばいいなら安心だね。
そうそう。行が増えても数式を足す必要がなく、ミスの少ないシートになるね！
オートフィルは手軽だが、行追加のたびにコピーが必要で、コピー漏れや参照ずれのミスが起きやすい。ARRAYFORMULAはこの手間とミスをまとめて減らせる。
基本の書き方（構文とシンプルな例）
基本の形は「=ARRAYFORMULA(計算式)」です。計算式の中で、1つのセルではなく「A2:A」のように範囲を指定するのがポイントです。例えばC列にA列とB列を足した結果を出したい場合、C2セルに「=ARRAYFORMULA(A2:A+B2:B)」と入力するだけで、C列全体に結果が一括表示されます。
「A2:A」って下まで全部ってこと？どこまで続くのか怖いんだけど。
うん、列の最後まで自動で伸びます。行を増やしても数式を書き直さなくていいんだよ。
基本構文は「=ARRAYFORMULA(処理内容)」である。処理内容の中で単体セルではなく範囲を指定することが、結果を複数セルへ展開させる条件となる。
SUMやAVERAGEに使っても意味がない理由
ARRAYFORMULAをつけても効果が出ない関数があります。SUM・AVERAGE・MAXなどは、範囲をまとめて1つの値に畳んでしまう関数のため、ARRAYFORMULAで囲んでも行ごとには分かれません。行ごとの計算がしたい場合は「=ARRAYFORMULA(B2:B+C2:C)」のように演算子で書く必要があります。
僕、SUMを範囲ごとARRAYFORMULAで囲んだのに1個しか値が出なくて焦ったことある…。
それは正常な動きだよ。1つに畳む関数か、行ごとに計算する関数かを見分けるのがコツだね。
SUM・AVERAGE・MAXなど範囲を1つの値に集約する関数は、ARRAYFORMULAで囲んでも複数行には展開されない。行ごとの結果が欲しい場合は演算子で書くか、BYROWなど別の関数を使う必要がある。
IF・VLOOKUPと組み合わせる時のコツ
IFやVLOOKUPは、条件や検索キーの部分を範囲にすれば行ごとに展開されます。「=ARRAYFORMULA(IF(A2:A=””,””,B2:B*1.1))」のように、判定したい列を範囲で渡すのが基本形です。VLOOKUPは検索キーは範囲にできますが、列番号は範囲にできない点に注意してください。
IFって配列の中で使えないと思ってたよ。書き方が悪かっただけなんだね。
そうなんだよ。条件を範囲で書いていないだけ、というケースがほとんどだよ。
IFやVLOOKUPは条件・検索キーの引数を範囲で渡せば行ごとに展開される。VLOOKUPの列番号は範囲にできないため、可変にしたい場合はINDEX＋MATCHなど別の方法を検討する必要がある。
「配列結果は展開されませんでした」の意味と直し方
赤い三角マークとともに「配列結果は◯◯のデータを上書きするため、展開されませんでした」と出ることがあります。これは数式が間違っているのではなく、展開先のセルにすでにデータが入っているという意味です。メッセージに書かれているセル番地の中身を確認し、削除すれば直ります。
見た目は空っぽのセルなのに直らない時があったんだけど、あれは何だったの？
半角スペースだけが残っていることがあるよ。LEN関数で文字数を確認すると見つけやすいよ！
「展開されませんでした」というエラーは数式の誤りではなく、展開先に既存データがあることを示す表示である。メッセージに書かれたセル番地を確認し、中身を空にすれば解消する。
0やN/Aが延々と並ぶ時の原因
エラーは出ないのに、データがない行にまで0や#N/Aが最終行まで並んでしまうことがあります。これは「A2:A」のような開いた範囲と「B2:B10」のような閉じた範囲を1つの数式に混ぜたときに起きます。範囲の書き方をどちらも開いた形にそろえるか、空欄なら空文字を返す形にすると解消します。
0が並んでるだけだと、エラーじゃないから気づかないまま集計しちゃいそう…。
そこが怖いところだね。書式で0を隠すのではなく、範囲を揃えて根本から直すのが安全だよ。
開いた範囲と閉じた範囲を混ぜると、短い範囲が空として扱われ0やN/Aが最終行まで並ぶ。範囲の形式をそろえるか、空欄を空文字で返す形にすることで解消できる。
Ctrl+Shift+Enterで自動入力する裏ワザ
数式を入力中に「Ctrl＋Shift＋Enter」を押すと、先頭に「ARRAYFORMULA(」が自動で追加されます。手入力で括弧の対応がずれるミスを防げるため、慣れないうちはこの方法で入力するのがおすすめです。
それなら僕でも括弧を数え間違えずに済みそう！
うん、最初のうちは手入力より断然おすすめの方法だよ。
数式入力中にCtrl+Shift+Enterを押すと、ARRAYFORMULAが自動で付与される。手打ちによる括弧のずれを防げるため、初心者にはこの入力方法が向いている。
使いすぎ注意：シートが重くなる時の対処
ARRAYFORMULAは便利ですが、「A2:A」のような開いた範囲を1シートに何本も置くと、データがない行まで再計算の対象になり、入力のたびにシートが重くなります。実際に使う行数まで範囲を絞る、必要な行だけを引くQUERYやFILTERに置き換えるといった対処が有効です。
便利だからってあちこちに置きまくると、逆に扱いづらくなっちゃうんだね。
そうだね。範囲を絞る、他の関数に置き換えるなど、使う場所を見極めることが大切でだよ。
開いた範囲のARRAYFORMULAを多用すると再計算の負荷が増え、動作が重くなる。範囲を実際の使用行数に絞る、QUERYやFILTERに置き換えるなどの対処で軽減できる。
まとめ：ARRAYFORMULA早見表
最後に、ARRAYFORMULAでよく起きる症状と原因・対処法を一覧にまとめました。
| 症状 | 原因 | 対処 |
|---|---|---|
| 1行しか出ない | 中の関数が配列を返していない | SUMIF・VLOOKUPなどは条件・検索キーを範囲で渡す |
| 赤い三角＋展開エラー | 展開先にデータが残っている | 該当セルの中身を削除、半角スペースも確認 |
| 「行を挿入してください」 | シートの行数が足りない | 下部から行数を追加する |
| 0やN/Aが最終行まで続く | 範囲の指定が食い違っている | 開いた範囲・閉じた範囲をそろえる |
| 付けても何も変わらない | FILTER・QUERYなど元から配列を返す関数だった | ARRAYFORMULAは不要、そのままでOK |
これ一枚保存しておけば、困ったときすぐ見返せそう！
うん、症状から逆引きできるので、エラーに出会ったらまずここを見てみてね。
ARRAYFORMULAのトラブルは「配列を返していない」「展開先が塞がっている」「範囲が食い違っている」の3系統に整理できる。症状から原因を逆引きすれば、初心者でも落ち着いて対処できる。
