スプレッドシートでARRAYFORMULAが効かない原因｜1行しか出ない時の対処

結論：ARRAYFORMULA が効かないのは「配列を返していない」「展開先が塞がっている」「範囲が食い違っている」の3系統

ARRAYFORMULA で数式を1行にまとめたのに、1行しか出ない・赤いエラーが出る・そもそも何も変わらない——。症状はバラバラに見えますが、原因は次の3系統に収まります。切り分けは画面に出ている表示だけで付きます。

• ①中の関数が配列を返していない＝エラーは出ないのに1行しか埋まらない。SUMIF・VLOOKUP・IF の扱いの違いが原因。
• ②展開先のセルが埋まっている＝「配列結果は…展開されませんでした」と赤く出る。数式そのものは正しい。
• ③範囲の指定が食い違っている＝A2:A と B2:B10 を混ぜたときに起きる。0 や #N/A が最終行まで続く。

これに加えて、④そもそも ARRAYFORMULA が効かない書き方になっている、⑤乱用して重くなり再計算が止まっている、の2つが後から効いてきます。まずは画面に出ている表示を見て、下の手順を上から当てはめてください。

まず1分で確認する3点

数式を書き直す前に、データを壊さないための確認を先にします。ARRAYFORMULA は「壊れると1か所で数百行が同時に消える」書き方なので、直す順番を間違えると復旧に時間がかかります。

1. 数式が入っているセルを1つに特定する。 ARRAYFORMULA は先頭セル1つにしか入っていません。2行目以降に手入力の値が混ざっていないか、［Ctrl］＋［Shift］＋［↓］で選択して確かめます。
2. 展開先の列を目視で下まで見る。 遠く下の行にスペースだけのセルや、過去の手入力が1つ残っているだけで展開は止まります。見た目が空でも空ではないことがあります。
3. 直す前に［ファイル］→［変更履歴］→［変更履歴を表示］を開いておく。 消えた場合はここから戻せます。共有シートで他の人が同時に開いている場合は、先に声をかけてから触ってください（複数人で使うスプレッドシートが壊れる原因と対策）。

原因別の対処5手順

手順1：1行しか出ない＝中の関数が配列を返していない

エラーは何も出ないのに、数式を入れたセルだけ答えが出て2行目以降が空。これが一番多いパターンです。ARRAYFORMULA は公式ヘルプで「配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます」と説明されています（ARRAYFORMULA｜Google公式ヘルプ）。裏を返すと、ARRAYFORMULA は「配列を渡す」係であって、中の関数が1つの値しか返さないなら結果も1つです。

関数ごとに挙動が分かれます。

• SUMIF / COUNTIF / SUMIFS：条件の引数に範囲を渡せば行ごとに計算されます。=ARRAYFORMULA(SUMIF(A2:A,A2:A,B2:B)) のように、行ごとに変わる引数だけを範囲にするのがコツです。なお SUMIF は公式に「1 つの条件に基づく場合のみ使用できます」「複数列にわたる合計を返すことはできません」と明記があります（SUMIF｜Google公式ヘルプ）。条件が2つ以上なら SUMIFS に替えます。
• VLOOKUP：検索キーを範囲にすれば展開されます（=ARRAYFORMULA(VLOOKUP(A2:A,シート2!A:C,3,FALSE))）。ただし列番号は範囲にできません。列番号を可変にしたい場合は VLOOKUP ではなく INDEX+MATCH か XLOOKUP に替えます。
• IF：条件に範囲を渡せば行ごとに判定されます。=ARRAYFORMULA(IF(A2:A="","",B2:B*1.1)) の形が基本です。IF 自体は配列で使えないわけではなく、条件を範囲で書いていないだけであることがほとんどです。公式ヘルプも「TRUE値と FALSE値の順序は正しく指定してください。順序の間違いが、IF 関数のエラーの最も一般的な原因です」と注意しています（IF｜Google公式ヘルプ）。
• SUM / AVERAGE / MAX：これらは範囲を1つの値に畳む関数なので、ARRAYFORMULA で囲っても行ごとには分かれません。行ごとの合計が欲しいなら =ARRAYFORMULA(B2:B+C2:C) のように演算子で書くか、BYROW を使います。

ここで直らないなら原因は「中の関数」ではありません。 数式を =ARRAYFORMULA(ROW(A2:A)) だけに置き換えてみて、それでも1行しか出ないなら手順2以降を見てください。連番が最終行まで並ぶなら、中の関数の書き方に戻って直す段階です。

手順2：赤いエラー＝展開先のセルが埋まっている

数式は正しいのに、セルに赤い三角と #REF! が出て、カーソルを乗せると「配列結果は C2 のデータを上書きするため、展開されませんでした。」のようなメッセージが表示される状態です。これは展開しようとした先に既存のデータがあるという意味で、数式のエラーではありません。

対処は3つです。

1. メッセージに出ているセル番地（上の例なら C2）へ移動し、中身を確認して削除する。メッセージには必ず衝突している最初のセルが書かれているので、そこだけ見れば足ります。
2. 見た目が空なのに消えない場合は、その範囲を選択して［Delete］ではなく［編集］→［削除］→［値］、または半角スペースが入っていないかを =LEN(C2) で確認する。
3. 下の行に集計行やメモ行を置いているなら、それを別シートへ動かす。ARRAYFORMULA を使う列は最終行まで何も置かないのが前提です。

なお、これらのエラー文言は Google 公式ヘルプに一覧として掲載されていません（ARRAYFORMULA のリファレンスにもエラーの説明はありません）。上に挙げた表記は実際の画面に出るものをそのまま引いたもので、状況によって「配列結果は自動的に展開されませんでした。行（n）を挿入してください。」という別の文言になります。「上書きするため」ならデータが邪魔をしている、「行（n）を挿入してください」なら行が足りない、と読み分けてください。

ここで直らないなら原因は「展開先」ではありません。 衝突セルを空にしてもエラーが消えないなら、シートの行数そのものが足りていない可能性があります（手順3）。

手順3：「行（n）を挿入してください」＝シートの行が足りない

参照元が1万行あるのに、数式を置いたシートが1,000行しかない、というケースです。この場合は下部の「さらに◯行追加」から行を足すだけで解決します。

ただし、闇雲に行を足すのは危険です。Google は1つのスプレッドシートの上限を「Google スプレッドシートで作成したスプレッドシートまたは Google スプレッドシート形式に変換したスプレッドシートの場合は 1,000 万セルまたは 18,278 列（列 ZZZ）まで」と定めています（Google ドライブに保存できるファイル｜Google公式ヘルプ）。26列の表なら約38万行で上限に届く計算です。使わない列を大量に残したまま行だけ足していくと、上限より先に動作が耐えられなくなります（スプレッドシートが重い・開かない原因と軽くする対処法）。

ここで直らないなら原因は「行数」ではありません。 行を足してもエラーが同じ文言のまま残るなら、参照している範囲の指定を疑います。

手順4：0 や #N/A が延々と続く＝範囲の指定が食い違っている

エラーは出ないが、データが無い行にまで 0 や #N/A、あるいは 1900/1/0 が並ぶ。これはA2:A（開いた範囲）と B2:B10（閉じた範囲）を1つの数式に混ぜているときに起きます。ARRAYFORMULA は渡された範囲のうち一番大きいものに合わせて展開するので、短いほうが空として扱われ、空を計算した結果が最終行まで並びます。

直し方は次のどれかです。

• 範囲の書き方をそろえる。 A2:A と B2:B のように、どちらも開いた範囲にする。これが一番安全です。
• 空行を先に除外する。 =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) のように、判定列が空なら空文字を返す形にします。0 を消すために書式で隠すのは、後で集計を間違える元なので避けてください。
• 行数を固定する。 明細が増えないシートなら A2:A1000 と B2:B1000 のように両方を同じ行数で止めます。

ここで直らないなら原因は「範囲」ではありません。 範囲をそろえても結果が変わらないなら、そもそも ARRAYFORMULA が効いていない書き方（手順5）を確認します。

手順5：何も変わらない＝ARRAYFORMULA が効いていない書き方

ARRAYFORMULA で囲ったつもりでも、次のような書き方では意味がありません。

• 囲う位置が違う。 =SUM(ARRAYFORMULA(...)) のように外側で畳んでしまうと、結果は1つの値に戻ります。ARRAYFORMULA は数式の一番外側に置きます。
• そもそも囲う必要がなかった。 公式ヘルプは「多くの配列数式は、ARRAYFORMULA 関数を明示的に使用しなくても、隣接するセルに自動的に展開します」と書いています。FILTER・QUERY・SORT・UNIQUE・SEQUENCE などは最初から配列を返すので、ARRAYFORMULA を付けても何も変わりません。「付けたのに変わらない」のは正常です。
• 入力の仕方で付け忘れている。 数式の編集中に［Ctrl］＋［Shift］＋［Enter］を押すと、先頭に ARRAYFORMULA( が自動で追加されます（同ヘルプ）。手打ちで閉じ括弧がずれていないかも合わせて確認してください。
• 他のファイルへ書き出そうとしている。 公式ヘルプには「配列数式を書き出すことはできません」とあります。Excel 形式でダウンロードして共有する運用なら、そもそも ARRAYFORMULA に寄せない設計にします。

行ごとの処理が複雑になってきたら、ARRAYFORMULA より MAP や BYROW と LAMBDA の組み合わせのほうが読みやすくなります。MAP は「LAMBDA を各値に適用して、指定した配列内の各値を新しい値にマッピングする」関数です（MAP｜Google公式ヘルプ、LAMBDA｜Google公式ヘルプ）。

手順6：直ったのに重い＝ARRAYFORMULA の乱用

ARRAYFORMULA は「1つの数式で全行をまかなえる」のが利点ですが、開いた範囲（A2:A）を指定すると、データが無い行も計算の対象になります。これを1シートに10本、20本と置くと、1文字入力するたびに数万セル分の再計算が走り、体感で数秒〜十数秒固まるようになります。

• 開いた範囲を、実際に使う行数まで縮める。
• VLOOKUP を ARRAYFORMULA で全行に敷くのをやめ、QUERY や FILTER で必要な行だけ引く形に替える。
• 変わらない過去分は、値のみ貼り付けで固定する。
• 集計結果の置き場と入力シートを分ける（スプレッドシートでダッシュボードを作る方法）。

再計算が止まる・保存が追いつかない段階まで来ていると、「変更内容を同期できません」を直す7手順で扱っている同期エラーも同時に出はじめます。数式の問題ではなく、シートの容量の問題に切り替わっているサインです。

毎週のように起きるなら、シートの使い方が限界に来ている

ARRAYFORMULA のトラブルには、他の数式エラーとは違う厄介さがあります。1枚のシートを1本の数式が支えているので、誰かが下の行に1文字入力しただけで数百行が一斉に消え、しかも消えた理由が画面のどこにも書かれていません。書いた本人以外は、どのセルを見ればいいのかすら分からない状態になります。

「ARRAYFORMULA の列は触らないでください」と口頭で伝えて運用しているなら、それは数式が業務ルールを肩代わりしている状態です。入力してはいけない場所、計算の順番、空欄の扱い——本来はシステム側で守るべき決まりが、1つのセルの中の長い数式として書かれていて、書いた人が辞めた瞬間に誰も直せなくなります。実際、この状態のシートを引き継いだ担当者が「怖くて触れないので、隣に新しいシートを作った」というところから、同じ表が3枚に増えていくのはよくある流れです。

数式で組み続けるか、仕組みに移すかの線引きは、スプレッドシート管理の限界で整理しています。

スプレッドシートを捨てる必要はありません。データの置き場としてはむしろ優秀です。限界が来ているのは「入力と計算とチェックを、全部1枚の表の中でやっていること」のほうです。Mihata では、いま使っているシートをそのまま残したうえで、入力画面と計算だけを切り出す作り方をしています。作り直しにならないので、ARRAYFORMULA を1本ずつほどきながら移せます。