スプレッドシートで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以降を見てください。連番が最終行まで並ぶなら、中の関数の書き方に戻って直す段階です。

少し寄り道になりますが、私たちは ARRAYFORMULA で一括計算しているようなシートを、そのまま残したまま入力画面だけ社内アプリに切り出すお仕事もしております。数式を1本ずつほどきながら移せますので、よろしければ合わせてご覧いただけたら嬉しいです。

手順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手順で扱っている同期エラーも同時に出はじめます。数式の問題ではなく、シートの容量の問題に切り替わっているサインです。

「配列の結果が展開されると、既存のデータが上書きされます」——邪魔をしているセルの特定手順

手順2のメッセージはセル番地まで教えてくれます。ところが同じ原因でも、番地が書かれていない文言が出ることがあります。セルにカーソルを乗せると「配列の結果が展開されると、既存のデータが上書きされます」とだけ表示され、どこを消せばいいのか分からない状態です。英語表示のアカウントでは「Array result was not expanded because it would overwrite data in …」という形で、末尾にセル番地が付く表記になります。

先に正直に書いておきます。これらのエラー文言は、Google 公式ヘルプのどのページにも掲載されていません。 配列の解説ページ（Google スプレッドシートで配列を使用する｜Google公式ヘルプ）にも ARRAYFORMULA のリファレンスにも、エラー文言の一覧はありません。ここに挙げた表記はいずれも実際の画面から引いたもので、Google の表示文言は予告なく変わります。文言を覚えるより、下の特定手順を覚えるほうが長持ちします。

特定1：塞いでいる最初の行を数式で出す

展開先の列を目で追うのは、1,000行を超えたあたりで現実的ではなくなります。どこかに1文字でも残っていれば展開は止まるので、空いている列に次の数式を一時的に置いて、塞いでいる最初の行を機械的に出します。展開先が C 列なら、C 列を丸ごと調べる形です。

1. ARRAYFORMULA を入れていたセルを、いったん別の場所へ退避する。 数式バーの中身をコピーして、メモ欄かテキストエディタへ貼っておきます。セルは空にします。
2. 空いている列（例として Z1）に =MATCH(TRUE,ARRAYFORMULA(C2:C<>""),0)+1 を入れる。 返ってきた数字が、C 列で最初に中身が入っている行番号です。これが邪魔をしているセルです。
3. 件数も見る。 =COUNTA(C2:C) が 1 なら残骸は1つだけ、数十あるなら過去の手入力が丸ごと残っています。後者なら列ごと作り直すほうが早いです。
4. 該当セルを選択し、［Delete］で消す。 消したら Z1 の数式も消して、退避した ARRAYFORMULA を元のセルへ戻します。

MATCH が #N/A を返すなら、C 列には何も入っていません。その場合、原因は展開先ではありません。 書式やメモだけが残っている可能性もあるので、次の特定2へ進みます。

特定2：見た目が空なのに消えない残骸を見つける

数式でも検出できないのに展開されない場合、セルの「中身」ではなく周辺に原因があります。順に当たります。

• ［Ctrl］＋［End］でシートの最終セルへ飛ぶ。 表の最終行よりはるか下に飛ぶなら、そこまでの範囲に何かが触られた履歴があります。最終セルまでの行をまとめて選択し、［編集］→［削除］→［行］ではなく右クリック→［行を削除］で物理的に減らします。
• ［編集］→［検索と置換］で［正規表現を含む］にチェックを入れ、検索欄に ^\s+$ を入れる。 空白文字だけのセルが引っかかります。見た目が空のセルの正体は、ほとんどがこれです。
• セルのメモ（コメントではなくメモ）を疑う。 展開先の範囲を選択して右クリックし、［メモを挿入］の表示が［メモを編集］になっていれば、メモが残っています。
• 結合セルを解除する。 展開先に結合セルが1つでもあると展開できません。範囲を選択して［表示形式］→［セルを結合］→［結合を解除］で戻します。

ここまでやっても消えないなら、展開先ではなく行数の問題（手順3）です。 特定1の MATCH が #N/A、［Ctrl］＋［End］も表の最終行で止まる、という2つが揃ったら、行を足す側を疑ってください。

Excel の配列数式（Ctrl+Shift+Enter）との違い

「Excel では Ctrl+Shift+Enter で配列数式にしていた。スプレッドシートでは ARRAYFORMULA を使うらしい」——この対応付けを持ち込むと、かえって直せなくなります。似ているのは見た目だけで、仕組みは3つに分かれているからです。

Excel 側の Ctrl+Shift+Enter は、公式に「今後は使わないで」と書かれている

Microsoft は配列数式の入力について「Ctrl+Shift+Enter キーを押すと、Excel は数式を中かっこ ({ }) で囲み、選択した範囲の各セルに数式のインスタンスを挿入します」「これらは一般に CSE 数式と呼ばれます」と説明しています（配列数式のガイドラインと例｜Microsoft サポート）。

そして現在の Excel では、この入力作法は過去のものになりました。同社は「動的配列数式は、既存の関数を使用しているか動的配列関数を使用しているかに関係なく、1 つのセルに入力してから Enter キーを押して確認するだけで済みます。以前の従来の配列数式では、最初に出力範囲全体を選択してから、Ctrl+Shift+Enter キーで数式を確認する必要がありました」とし、「レガシ配列数式は、下位互換の理由で引き続きサポートされていますが、今後は使用しないでください」と明記しています（動的配列数式とスピルされた配列の動作｜Microsoft サポート）。

ここが誤解の入り口です。Excel の Ctrl+Shift+Enter は「配列として確定する」操作でしたが、スプレッドシートの同じキーは「数式の先頭に ARRAYFORMULA( を書き足す」だけの入力補助です。Google 公式ヘルプも「数式の編集中に Ctrl+Shift+Enter を押すと、数式の先頭に ARRAYFORMULA( を追加できます」と、文字を足す機能として説明しています（ARRAYFORMULA｜Google公式ヘルプ）。押したのに結果が変わらないのは、押す前から ARRAYFORMULA が要らない数式だったか、中の関数が配列を返していないかのどちらかで、キーの押し方の問題ではありません。

暗黙的な展開があるぶん、スプレッドシート側は原因が見えにくい

Google スプレッドシートは、ARRAYFORMULA を書かなくても結果が複数セルに広がります。公式ヘルプの「多くの配列数式は、ARRAYFORMULA 関数を明示的に使用しなくても、隣接するセルに自動的に展開します」という記述がこれです（同ヘルプ）。本記事ではこれを暗黙的な展開と呼びます。FILTER や QUERY を書いた覚えしかないのに「上書き」のエラーが出るのは、この暗黙的な展開が起きているからです。

暗黙的な展開は便利ですが、トラブルのときは不利に働きます。「自分は配列数式を書いていない」と思っている人の画面で、配列の衝突が起きるからです。Excel なら #SPILL! という専用の表示が出て、Microsoft 公式も「#SPILL を含むセルを選択します。エラーを表示して、意図したスピル範囲を示す破線の境界線を表示します」として［障害物セルの選択］という専用の操作を用意しています（Excel での #SPILL! error - スピル範囲が空白ではない｜Microsoft サポート）。

スプレッドシートに、この［障害物セルの選択］に相当するボタンはありません。 範囲が破線で表示されることもありません。だからこそ、上の「特定1」の数式で塞いでいる行を自力で割り出す手順が要ります。Excel から移ってきて「エラーの直し方が分からない」と感じるのは、関数の知識不足ではなく画面が教えてくれる情報量の差です。

ARRAYFORMULAの中でVLOOKUPを使っていて、一部の行だけ #N/A になる場合は、展開の問題ではなく検索キーの一致の問題です。スペースや数値と文字列の違いを1分で判定する方法はVLOOKUPが#N/Aで見つからない原因5つにまとめています。

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

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

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

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

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