ARRAYFORMULA関数の使い方｜1つの数式を全行に効かせる

ARRAYFORMULAは、1つの数式を範囲全体に効かせる関数です。

通常なら数式を下方向にコピーするところを、1セルに書くだけで全行に適用できます。行が増えても数式をコピーし直す必要がありません。

Excelにはありません（Microsoft 365では「スピル」が同等の役割を果たします）。スプレッドシートで大量データを扱うなら、覚える価値が最も高い関数のひとつです。

書式

=ARRAYFORMULA(数式)

数式の中の単一セル参照を、範囲に置き換えるのがコツです。

基本の使い方

C列に「A列 × B列」を出す例です。

通常のやり方

C2に  =A2*B2

と書いて、下方向にコピー。

ARRAYFORMULAのやり方

C2に  =ARRAYFORMULA(A2:A*B2:B)

これだけです。C3以降には何も入力しません。 A列にデータを追加すると、C列にも自動で結果が入ります。

A2:A は「A2から下すべて」という書き方です。終わりの行番号を書かないことで、行が増えても追従します。

見出しを一緒に出す

上の書き方だと、A列が空の行にも 0 が入ってしまいます。見た目を整えるには IF と組み合わせます。

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

「A列が空なら空欄、そうでなければ掛け算」という意味です。この形が実用上の基本形になります。

見出しごと1つの数式で出すなら、{} で結合します。

=ARRAYFORMULA({"金額"; IF(A2:A="", "", A2:A*B2:B)})

{} の中の ; は縦方向の結合です。C1に書けば、見出しと全行のデータが一気に出ます。

VLOOKUPと組み合わせる

VLOOKUP を全行に効かせる書き方です。

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

数式が1本になるので、管理が非常に楽になります。 誰かが途中の行の数式を消してしまう、という事故も起きません。

エラー処理も一緒に書けます。

=ARRAYFORMULA(IF(A2:A="", "", IFERROR(VLOOKUP(A2:A, マスタ!A:B, 2, FALSE), "未登録")))

自動で番号を振る

=ARRAYFORMULA(IF(A2:A="", "", ROW(A2:A)-1))

A列にデータがある行だけ、1から連番が入ります。行を削除しても番号が振り直されます。

使わなくていい関数

一部の関数は、もともと配列を返すのでARRAYFORMULAが不要です。

関数	ARRAYFORMULA
FILTER	不要
QUERY	不要
SORT	不要
UNIQUE	不要
SPLIT	不要
IMPORTRANGE	不要

逆に、IF VLOOKUP TEXT LEFT などの単一セル向けの関数には必要です。

使えない関数

すべての関数が配列に対応しているわけではありません。

• – SUMIF / COUNTIF — 条件の側に範囲を渡すと動くが、挙動が直感と違う
• – INDIRECT — 配列を渡しても展開されない
• – OFFSET — 同上

これらを全行に効かせたい場合は、QUERY や FILTER で書き直すほうが確実です。

また、AND と OR は配列の中でうまく働きません。掛け算 * と足し算 + で代用してください。

誤り： =ARRAYFORMULA(IF(AND(A2:A>0, B2:B>0), "OK", ""))
正しい： =ARRAYFORMULA(IF((A2:A>0)*(B2:B>0), "OK", ""))

エラーの対処

#REF! が出る

結果の展開先にデータが入っています。 ARRAYFORMULAは下方向に結果を広げるため、その領域が空いている必要があります。C3以降に古い数式や値が残っていないか確認してください。

結果が1行しか出ない

参照が単一セルのままです。A2 ではなく A2:A と範囲で書いてください。

空行に0や#VALUE!が並ぶ

IF(A2:A="", "", ...) で空欄を除外してください。

動作が重い

ARRAYFORMULAは範囲全体を計算します。A2:A（10万行）を何本も置くと重くなります。

対策：

• – 範囲を実際に使う分だけに限定する（A2:A5000）
• – VLOOKUP を大量に含むARRAYFORMULAは、QUERY に置き換えられないか検討する

使いどころの判断

状況	使うべきか
行が頻繁に追加される表	使う
複数人が編集するファイル	使う（数式が消されない）
行数が固定の小さい表	どちらでも
10万行規模のデータ	範囲を限定して使う

「数式をコピーし忘れて集計が漏れた」という事故を構造的に防げるのが最大の利点です。共有ファイルでは特に有効です。

まとめ

• – 書式は =ARRAYFORMULA(数式)。単一セル参照を範囲に置き換える
• – 基本形は =ARRAYFORMULA(IF(A2:A="", "", 数式))
• – FILTER や QUERY には不要（元から配列を返す）
• – 配列の中では AND は *、OR は + で代用する
• – #REF! は展開先にデータがあるという意味
• – Excelには無い（365のスピルが同等）

関連する関数

• – FILTER — 元から配列を返す
• – QUERY — 大量データの集計はこちらが速い
• – IF — 空欄除外に必須
• – VLOOKUP — 全行に効かせる定番の組み合わせ
• – 関数一覧に戻る