配列・抽出
ARRAYFORMULA関数の使い方・読み方
読み方：ARRAYFORMULA は「アレイフォーミュラ」と読みます。（一般的な読み方です。公式に読み仮名の定めはありません）
ARRAYFORMULAは、1つの数式を範囲全体に効かせる関数です。
Excelでは不要です。数式が自動で下まで広がります（スピル）。
このページの中身（22項目）
この記事の書式と対応環境の出どころ（2026-08-26 確認）
- Google スプレッドシート ヘルプ：公式の書式は
ARRAYFORMULA(配列数式)
https://support.google.com/docs/answer/3093275
関数の仕様は追加・変更されることがあります。書式が合わないときは、上の公式ページで最新をご確認ください。
通常なら数式を下方向にコピーするところを、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が不要です。
逆に、IF VLOOKUP TEXT LEFT などの単一セル向けの関数には必要です。
使えない関数
すべての関数が配列に対応しているわけではありません。
これらを全行に効かせたい場合は、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万行）を何本も置くと重くなります。
対策：
使いどころの判断
一本にまとめるべき場面と、各行にコピーするべき場面があります。
まとめるべきなのは、行が自動的に増える表です。フォームの回答、取り込んだデータ、日次で追加される記録。これらは数式のコピー漏れが起こりやすいので、一本にまとめる価値があります。
各行にコピーするべきなのは、行ごとに例外がある表です。特定の行だけ違う計算をする、手で値を上書きすることがある。こうした運用では、一本にまとめると対応できません。
また、数式が複雑な場合も、各行にコピーするほうが読みやすくなります。一本にまとめると、条件分岐と範囲の指定が入り混じって、何をしているか分からなくなります。
判断に迷ったら、そのファイルを誰が触るかで決めてください。自分だけなら一本にまとめる利点が大きく、他人が触るなら読みやすさを優先するほうが安全です。
| 状況 | 使うべきか |
|---|---|
| 行が頻繁に追加される表 | 使う |
| 複数人が編集するファイル | 使う（数式が消されない） |
| 行数が固定の小さい表 | どちらでも |
| 10万行規模のデータ | 範囲を限定して使う |
「数式をコピーし忘れて集計が漏れた」という事故を構造的に防げるのが最大の利点です。共有ファイルでは特に有効です。
一本の数式で列全体を処理する意味
各行に同じ数式をコピーする方法と、一本の数式で列全体を処理する方法。結果は同じに見えますが、運用の性質がまったく違います。
各行にコピーする方法では、行が増えたときに数式もコピーする必要があります。この作業を忘れると、新しい行だけ計算されません。フォームの回答が自動で追加される表では、追加された行に数式が入らないという問題が必ず起こります。
一本の数式で処理する方法なら、行が増えても自動的に計算されます。数式は最上部の一箇所にしかないので、誤って削除される危険も減ります。
もうひとつの利点が、数式の管理です。各行にコピーしていると、途中の行だけ数式が違う、という状態が起こりえます。誰かが一行だけ値を直接入力してしまう、といったことです。一本にまとめておけば、そうした不整合が構造的に起きません。
欠点は、数式が読みにくくなることと、行ごとに例外を作れないことです。すべての行を同じ処理で扱えることが前提になります。
展開される範囲を確保する
一本の数式で処理する場合、結果は下方向に展開されます。展開される先に何か入っていると、衝突してエラーになります。
したがって、数式を入れる前に、その列の下を完全に空けておく必要があります。過去に入力して削除したつもりのセルに、空白文字が残っていることもあるので、範囲を選択して完全に消去してから入れ直してください。
見出し行の扱いにも工夫が要ります。数式を最上部に置くと、見出しの位置に計算結果が出てしまいます。条件で分岐して、最初の行だけ見出しの文字を返すようにするのが定番の書き方です。
行数の指定も考える必要があります。範囲を列全体にすると、データがない行にも結果が出ます。空欄の行に零や空文字が並ぶことになり、見た目が悪くなるだけでなく、件数を数えるときに邪魔になります。
対処としては、条件で空欄を判定し、空欄なら何も返さないようにします。あるいは、範囲をデータのある行までに限定します。前者のほうが行の増加に対応できます。
対応していない関数がある
すべての関数が、この方法で使えるわけではありません。範囲をまとめて受け取れる関数と、一つの値しか受け取れない関数があります。
一つの値しか受け取れない関数を使うと、範囲全体が一つの値として扱われ、意図しない結果になります。エラーにならないことも多く、原因の特定に時間がかかります。
対応しているかどうかは、実際に試すのが早い判断方法です。範囲を渡して、行ごとの結果が並ぶなら対応しています。一つの値だけが返るなら対応していません。
対応していない関数を使いたい場合は、各行にコピーする方法に戻すか、別の関数で書き換えられないかを検討してください。
また、対応していても挙動が変わる関数もあります。集計の関数は、範囲を渡すと集計結果を一つ返します。行ごとの結果が欲しい場合は、範囲の取り方を工夫する必要があります。
フォームの回答を扱う定番
外部から自動的に行が追加される表では、一本の数式で列全体を処理する方法が事実上の必須になります。
フォームの回答が追加される表を考えます。回答の内容から判定を出したい、集計用のキーを作りたい、といった処理が必要になります。各行に数式をコピーしていると、新しい回答が追加された行には数式が入りません。
一本にまとめておけば、追加された行にも自動で計算が及びます。回答が何件増えても、数式は最上部の一つだけです。
このとき、見出し行の扱いを決めておく必要があります。数式を見出しの位置に置くと、そこに計算結果が出てしまいます。条件で分岐して、最初の行だけ見出しの文字を返すのが定番の書き方です。
空欄の行の扱いも決めます。範囲を列全体にすると、回答がない行にも結果が出ます。条件で空欄を判定し、空欄なら何も返さないようにしてください。
もうひとつ、回答の表そのものを直接編集しないという原則があります。自動で追加される表に手を入れると、次の追加で崩れることがあります。加工は別のシートで行い、回答の表は参照するだけにしてください。
列全体を処理するときの負荷
一本の数式で列全体を処理する場合、範囲の指定が動作の速さを左右します。
範囲を列全体にすると、データがない行まで計算されます。行数が多いシートでは、これだけで動作が重くなります。条件で空欄を判定していても、判定そのものは全行で行われます。
対処としては、範囲を必要な行までに限定します。ただし、行が増えることを想定して、少し余裕を持たせてください。
もうひとつの方法は、データの件数から範囲を動的に決めることです。件数を求めて、その行数までを対象にします。行が増えれば範囲も広がります。ただし、範囲を動的にする関数には揮発性のものがあるので、選び方に注意が必要です。
処理の内容も負荷に影響します。検索を伴う処理を列全体に適用すると、行数の二乗に近い計算量になることがあります。この場合は、範囲を絞るだけでは足りません。処理そのものを見直すか、各行にコピーする方法に戻すことを検討してください。
動作が重いと感じたら、まず範囲の指定を確認するのが定石です。ほとんどの場合、これで解決します。
「配列・抽出」の関数を全体から見る
配列・抽出は複数の値をまとめて返す関数群です。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに9本あります。
このカテゴリで共通するつまずき
展開先が空いていないと #REF! で止まります。専用のシートに分けてください。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。
どれを使うかの決め方
元の表を変えずに別の見え方を作れるのが利点です。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。
迷ったときは
→ 全関数の一覧 に目的別の索引があります。
数式を書く前の手順
書き始める前に決めておくことがあります。ここが曖昧なまま書くと、途中で行き詰まります。
1. 出したい結果を一文で書く
「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。
2. 元データの型を確認する
展開先が空いていないと #REF! で止まります。専用のシートに分けてください。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。
3. 空欄と0の扱いを決める
未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。
4. 条件の数を数える
元の表を変えずに別の見え方を作れるのが利点です。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。
5. 共有先の環境を確認する
新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。
6. 1行だけ書いて確認する
全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。
どの関数でも共通する確認
関数を問わず、結果がずれる原因は限られています。この5つを確認すれば、大半が解決します。
参照を固定したか
下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。
範囲の行数が揃っているか
複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。
型が揃っているか
=ISNUMBER(A2)数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。
前後に空白が入っていないか
=LEN(A2)&" / "&LEN(TRIM(A2))数が違えば、余分な空白が入っています。見た目では判別できません。
別の方法で検算したか
同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。
最終行まで確認したか
一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。
引き継ぐときに残すこと
数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。
何を出している数式か
一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。
どの表を参照しているか
参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。
該当しないときの扱い
空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。
触ってはいけない場所
列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。
使った関数の対応環境
新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。
確定した期間は値にする
締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。
このサイトの収録状況
現在、201本の記事をカテゴリ別に整理しています。関数名が分からない場合は、カテゴリから探してください。
| カテゴリ | 記事数 |
|---|---|
| 目的別 | 51本 |
| 集計 | 47本 |
| 文字列 | 31本 |
| 日付 | 19本 |
| 検索・参照 | 15本 |
| 条件 | 15本 |
| 機能 | 12本 |
| 配列・抽出 | 10本 ←この記事 |
| 関数一覧 | 1本 |
探し方の順番
関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。
記事の構成
どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。
よくある質問
Q. エラーになって展開されません
展開される先に何か入っています。下方向を完全に空にしてください。
Q. 空欄の行にも結果が出ます
条件で空欄を判定して、何も返さないようにしてください。
Q. 見出しの位置に計算結果が出ます
最初の行だけ見出しの文字を返すよう、条件で分岐してください。
Q. 一つの値しか返りません
その関数が範囲をまとめて受け取れません。各行にコピーする方法に戻すか、別の関数を検討してください。
Q. 動作が重くなります
範囲を列全体にしていないか確認してください。データのある範囲に絞ると改善します。
Q. 途中の行だけ値を入れたいのですが
一本にまとめた数式では、途中の行だけ上書きできません。各行にコピーする方法を使ってください。
Q. Excelでも同じことができますか
新しい環境では、範囲を渡すだけで自動的に展開される仕組みがあります。専用の指定は不要です。
Q. 条件付きで処理できますか
条件分岐の関数と組み合わせられます。行ごとに判定して、結果を変えることができます。
まとめ
- 書式は
=ARRAYFORMULA(数式)。単一セル参照を範囲に置き換える - 基本形は
=ARRAYFORMULA(IF(A2:A="", "", 数式)) - FILTER や QUERY には不要（元から配列を返す）
- 配列の中では AND は
*、OR は+で代用する #REF!は展開先にデータがあるという意味- Excelには無い（365のスピルが同等）