スプレッドシートのARRAYFORMULAの使い方｜1つの数式を列全体に適用してオートフィルをなくす

スプレッドシートで数式を1行目に入れて、下までドラッグしてコピー——毎回これをやっていませんか。

行が増えるたびに数式をコピーし直し、コピー漏れがあれば計算されない。この面倒がまるごと消えるのが ARRAYFORMULA（アレイフォーミュラ）です。

1行目に1回書くだけで、列全体に自動で適用されます。しかもあとから行を追加しても、そのまま計算されます。

Excelにはない、スプレッドシートならではの機能です。この記事では、使い方とつまずきどころを解説します。

ARRAYFORMULAとは

ひとことで言うと、「1つのセルに書いた数式を、範囲全体に広げる」関数です。

これまでのやり方

単価×数量を計算する場合、C2に次の数式を入れて、下までコピーしていました。

=A2*B2

100行あればC2〜C101まで、同じ数式が100個並びます。

ARRAYFORMULAを使うと

C2にこの1つだけ入力します。

=ARRAYFORMULA(A2:A*B2:B)

基本の書き方

難しいことはありません。いつもの数式を ARRAYFORMULA() で囲み、セル参照を範囲に変えるだけです。

これまで（1行ずつ）	ARRAYFORMULA
=A2*B2	=ARRAYFORMULA(A2:A*B2:B)
=A2&B2	=ARRAYFORMULA(A2:A&B2:B)
=ROUND(A2,0)	=ARRAYFORMULA(ROUND(A2:A,0))
=IF(A2>100,"○","×")	=ARRAYFORMULA(IF(A2:A>100,"○","×"))

入力を省略する方法

数式を書いたあと、Ctrl + Shift + Enter で確定すると、ARRAYFORMULAが自動で付きます。毎回手で打つ必要はありません。

範囲の指定でつまずかないために

終わりを指定しない書き方が便利

書き方	意味
A2:A100	2〜100行目だけ。101行目を足しても計算されない
A2:A	2行目から最終行まで。行が増えても自動対応

基本は A2:A の形を使ってください。これがARRAYFORMULAの最大の利点を活かす書き方です。

空白行に0や結果が並んでしまうとき

A2:A と書くと、データがない行にも計算結果が出てしまうことがあります。単価×数量なら「0」がずらっと並びます。

これを防ぐには、空欄なら空欄を返すようにします。

=ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))

「A列が空欄なら何も表示しない、そうでなければ計算する」という書き方です。この形は定番なので覚えておくと便利です。

見出し行も一緒に作る

1行目に見出しを置きたい場合、数式は2行目からになります。しかし見出しごとARRAYFORMULAで作ってしまう方法もあります。

=ARRAYFORMULA(IF(ROW(A:A)=1, "金額", IF(A:A="", "", A:A*B:B)))

「1行目なら見出しの文字、それ以外なら計算」という指定です。1つの数式で列全体が完成するので、シートを他の人に渡すときに壊れにくくなります。

よく使う組み合わせ

VLOOKUPを列全体に適用する

=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, 商品マスタ!A:B, 2, FALSE)))

商品コードから商品名を引く処理を、1つの数式で全行分まかなえます。行が増えても自動で引いてくれます。

文字列を結合する

=ARRAYFORMULA(A2:A&" "&B2:B)

姓と名を結合するような処理も1行で済みます。

条件で分岐する

=ARRAYFORMULA(IF(C2:C>=10000, "送料無料", "送料500円"))

【注意】ARRAYFORMULAが使えない関数がある

関数	ARRAYFORMULAでの挙動	代わりに使うもの
SUMIF / COUNTIF	動くが書き方にコツが要る	条件範囲を配列で渡す
SUM / AVERAGE	合計値が1つ返るだけ	行ごとに計算したいなら不要
INDIRECT	配列に対応しない	別の方法を検討する
OFFSET	配列に対応しない	INDEXなどで代替

「1行ずつ結果が欲しい計算」には向き、「全体を1つに集計する計算」には向かない——この区別を押さえておけば迷いません。

SUMIFを行ごとに使いたい場合

条件に合う合計を各行に出したいときは、条件の範囲も配列で渡します。

=ARRAYFORMULA(IF(A2:A="", "", SUMIF(A:A, A2:A, B:B)))

これで「同じ担当者の合計」を各行に表示できます。

ExcelのスピルとARRAYFORMULAの違い

Excel（Microsoft 365）にはスピルという似た仕組みがあります。

	スプレッドシート	Excel（365）
書き方	ARRAYFORMULAで囲む	囲まなくてよい（自動でスピル）
結果の広がり	指定した範囲	データの数だけ自動で広がる
行の追加	A2:Aなら自動対応	テーブル化すれば自動対応
古いバージョン	—	2019以前は使えない

Excel 365では =A2:A100*B2:B100 と書くだけで同じことができます。考え方は同じで、書き方だけが違うと理解しておけば、両方使う方も混乱しません。

詳しくはExcelとGoogleスプレッドシートの違いもあわせてご覧ください。

よくあるエラーと対処

「結果が1つしか表示されない」

セル参照が範囲になっていない可能性があります。A2 ではなく A2:A のように範囲で指定してください。

「#REF! 結果が拡張されるとデータが上書きされます」

計算結果を出す先にすでに何か入力されているときのエラーです。下のセルを空にしてください。

数式をコピーした名残が残っていることが多いので、列全体を選んで一度削除してから入れ直すと確実です。

「動作が重くなった」

A:A のように列全体を指定すると、空行まで含めて計算するため重くなることがあります。データ量が多いシートでは A2:A10000 のように上限を決めると改善します。

「一部の行だけ結果が違う」

ARRAYFORMULAの結果は数式が入っているセルからしか編集できません。途中の行を直接書き換えようとすると弾かれます。修正するなら元の数式を直してください。

よくある質問（FAQ）

ARRAYFORMULAは何行まで対応できる?

スプレッドシート全体の上限（合計1,000万セル）の範囲内であれば制限はありません。ただし数万行を超えると動作が重くなるため、範囲に上限を設けることをおすすめします。

ARRAYFORMULAの中で別のARRAYFORMULAは使える?

入れ子にする必要はありません。いちばん外側で1回囲めば、中の計算はすべて配列として処理されます。

結果をコピーして値だけ残したい

結果の範囲をコピーし、「特殊貼り付け」→「値のみ貼り付け」を選びます。数式が消えて値だけが残ります。

QUERY関数とどちらを使うべき?

用途が違います。行ごとに同じ計算をするならARRAYFORMULA、条件で抽出したり集計したりするならQUERYです。組み合わせて使うこともできます。

まとめ

• ARRAYFORMULAは1つの数式を列全体に適用する関数。オートフィルが不要になる
• 書き方はいつもの数式を ARRAYFORMULA() で囲み、参照を範囲に変えるだけ
• 範囲は A2:A の形にすると、行を追加しても自動で計算される
• 空白行に結果が出るときは IF(A2:A="", "", 計算) で囲む（定番の書き方）
• 入力は Ctrl + Shift + Enter で自動的にARRAYFORMULAが付く
• SUMやAVERAGEのような集計関数には向かない。「行ごとに結果が欲しい計算」に使う
• Excelのスピルと考え方は同じ。Excel 365なら囲まずに書ける

まずは既存のシートで、コピーして並べている数式を1つ選び、ARRAYFORMULAに置き換えてみてください。行を追加したときの手間がなくなる感覚がつかめるはずです。

関連記事：QUERY関数の使い方／IMPORTRANGE関数の使い方／ExcelとGoogleスプレッドシートの違い