Excel #SPILL!エラーの原因と直し方|説明文から逆引き

スポンサーリンク

Excelで数式を入れたのに、結果の代わりに #SPILL! とだけ表示されて手が止まっていませんか。

数式は合っているはずなのに、どこを直せばいいのか分かりにくいエラーですよね。原因が数式の外にあると、式を何度書き直しても消えません。

#SPILL! は、数式の結果をセルに広げられないときに出る合図。確認の手順は次の3つです。

  1. エラーのセルを選ぶ
  2. 破線の枠を見る
  3. エラーの説明を読む

説明の言葉が分かれば、早見表から原因と直し方を引けます。当てはまる原因の節だけ読めば大丈夫。

対応バージョン: #SPILL! は、結果が隣のセルへ広がる動的配列の機能で出るエラーです。対応版は Microsoft 365・Excel 2021・Excel 2024・Excel for the web。Excel 2019 以前は FILTER や UNIQUE が使えないため、この記事の例はそのまま試せません。

Excelの#SPILL!エラーが出たらまず確認する3ステップ

#SPILL! の「スピル」は、1つのセルに入れた数式の結果が隣のセルへ自動で広がる動きのこと。仕組みから知りたい方はUNIQUE・FILTER・SORT・SEQUENCE関数の入門記事をご覧ください。

ここでは次の表を例にします。セル範囲は A1:C7 です。

担当者部署売上
田中営業120
鈴木総務80
佐藤営業150
高橋開発90
伊藤営業200
渡辺総務60

E2 に、部署が「営業」の担当者を取り出すFILTER関数を入れました。

=FILTER(A2:A7,B2:B7="営業")

営業は3人なので、結果は E2:E4 に並ぶはず。ところが E3 に「メモ」という文字が残っていると、#SPILL! になります。

①エラーのセルを選ぶ

#SPILL! と表示されているセルをクリックします。例の表なら E2 です。ここから先の確認は、このセルを選んだ状態で進めましょう。

②破線の枠を見る

エラーのセルを選ぶと、結果を広げようとしている範囲が破線の枠で示されます。例では E2:E4 を囲む枠の中に、E3 の「メモ」が入っている状態。

枠の中に文字や数字が見えたら、それが邪魔をしているセルの候補です。空に見える場合は、原因1の「見た目は空なのに邪魔される」も確かめてください。

③エラーの説明を読む

エラーのセルを選ぶと、横にマークが出ます。カーソルを合わせるかクリックしてメニューを開くと、エラーの種類を示す短い説明が読めます。その言葉を、次の早見表で探してみてください。

マークや説明が見当たらないときは、破線の枠の中身を確かめてから、早見表の原因1から順に当たりましょう。

【早見表】エラーの説明文から原因を逆引き

左の列は、公式ヘルプの見出しをもとにした7種類の説明です。画面の言い回しはこの見出しと同じとは限らないので、意味が近い行を探してください。

エラーの説明(公式ヘルプの見出し)原因直し方読む節
スピル範囲が空ではない広げる先に値や数式が入っている邪魔なセルを消すか移す原因1
結合されたセルにスピルする広げる先に結合セルがある結合を解除するか数式を移す原因2
テーブルの数式テーブルの中に数式を入れたテーブルの外へ移すか範囲に変換する原因3
ワークシートの端を超えて拡大する列全体の参照で結果がシートの下端を超える参照を必要な範囲に絞る原因4
不確定なサイズ結果の大きさが計算のたびに変わる公式の手順はなし。大きさが変わる部分を見直す原因5
メモリが不足している配列が大きすぎてメモリが足りないより小さい範囲を参照する原因6
認識されない/フォールバックExcelが原因を特定できない数式の引数がそろっているか確かめるその他

検索で見かける「スピル範囲が空白ではありません」のような言い方も、意味としては原因1の行に当たります。

原因1:スピル先が空白でない

「スピル範囲が空ではない」:値や数式が入っている

スピル先のセルに値や数式が残っていると、Excelは結果を広げられません。入力済みのセルを勝手に上書きしないための動きと考えると、納得しやすいはず。

直し方は次の2つです。

  • 中身が要らないセルは、選んで Delete キーで消す
  • 中身が必要なセルは別の場所へ移す。または、数式を空いた場所へ移す

邪魔なセルが空になると、数式を入れ直さなくても結果が広がります。

FILTERの結果の行数は、条件に合う件数で変わります。そのためデータが増えた日に、昨日まで動いていた数式が #SPILL! になることも。FILTERの条件の書き方はFILTER関数の使い方で確かめられます。

見た目は空なのに邪魔される:スペースと数式の空文字””

枠の中が空に見えるのに #SPILL! が消えないなら、見えない中身を疑いましょう。たとえば E3 にスペースを1つだけ入れると、見た目は空でもエラーのままです。

スピル範囲は空である必要があります。見た目が空でも中身があるセルは空ではないので、結果を広げられません。

確かめたい中身は次の3つ。

  • スペース: スペースキーで文字を消したつもりのセルには、スペースが残っている
  • 数式の空文字 "": =IF(D3="","",D3) のような数式は空に見える結果を返すだけで、セルは空ではない
  • 値を隠す ;;; の表示形式や白い文字: 表示されていないだけで、値が残っている

"" の例を画面で見ると、セルには何も見えないのに数式バーには数式が表示されています。

確かめる方法は、セルを選んで数式バーを見ること。数式や文字が見えたら、そのセルには中身があります。

スペースは数式バーでも見分けにくいものです。枠の外の空いたセルに、次の関数を入れてみてください。

=ISBLANK(E3)

ISBLANK は、セルが本当に空なら TRUE、スペースや数式が入っていれば FALSE を返します。

文字数を数える =LEN(E3) でも確かめられますが、注意点が1つ。空文字を返す数式は、本当に空のセルと同じ 0 になります。見分けるなら ISBLANK が向いています。

邪魔なセルの見つけ方

まず試したいのは、破線の枠の中を順に見ていく方法です。

  1. エラーのセルを選び、破線の枠の範囲を確かめる
  2. 枠の中のセルを1つずつ選び、数式バーで中身を見る
  3. 要らないと確かめたセルを Delete キーで消す

Delete キーを押す前に中身を見れば、必要な数式を消してしまう失敗を防げます。

エラーのセルの横に出るマークのメニューから、邪魔しているセルへ移動する項目を選ぶ方法もあります。公式ヘルプでは「障害物セルの選択」と書かれていますが、画面の表記が同じとは限りません。

Excel for the web でこの項目が使えるかは確認が取れていません。メニューに見当たらないときは、上の手順で進めてください。

原因2〜6:結合セル・テーブル内・列全体参照・サイズ不定など

原因2:「結合されたセルにスピルする」(スピル先に結合セルがある)

UNIQUE関数で、部署の一覧を E2 から出す例を見てみましょう。

=UNIQUE(B2:B7)

結果は「営業」「総務」「開発」の3件。E3:F3 が結合されていると、#SPILL! になります。

スピル先に結合セルがあると、結果を広げられません。直し方は次の2つです。

  • セルの結合を解除する
  • 結合セルと重ならない場所へ数式を移す

結合セルは、原因1と同じように破線の枠の中で探せます。結合を残さずに見た目を整える方法はExcelでセル結合を使ってはいけない理由と代替方法3選にまとめました。

原因3:「テーブルの数式」(Excelのテーブル内に数式を入れた)

ここでいうテーブルは、「挿入」タブの「テーブル」で作る表のこと。公式ヘルプによると、スピルする配列数式はテーブルの中ではサポートされていません。

次の画面は、例の表をテーブルにして「並べ替え」列を足し、その D2 にSORT関数を入れた例です。

=SORT(C2:C7,,-1)

対処は次のどちらかです。

  • 数式を、テーブルから1列以上離れたセルへ移す
  • テーブルを範囲に変換する(「テーブル デザイン」タブ →「ツール」→「範囲に変換」)

テーブルのまま使いたいなら、外へ移すほうが手軽。上のメニューは公式ヘルプの手順で、Excel for the web の画面とは異なる場合があります。

原因4:「ワークシートの端を超えて拡大する」(列全体を参照している)

E2 に入れたVLOOKUPの検索値に、列全体の A:A を渡した例です。

=VLOOKUP(A:A,A:C,2,FALSE)

A:A と書くと、Excelは列のすべての行を検索値として扱います。結果もシートの一番下まで返そうとするので、E2 から下では行が足りません。

直し方は3通り。使う場所に合わせて選びましょう。

直し方数式結果の広がりテーブル内
検索値を必要な範囲だけにする=VLOOKUP(A2:A7,A:C,2,FALSE)スピルする使えない
同じ行の値だけを指定して下へコピーする=VLOOKUP(A2,A:C,2,FALSE)スピルしない使える
@ を付けて下へコピーする=VLOOKUP(@A:A,A:C,2,FALSE)スピルしない使える

テーブルの外なら、上の表の1行目の式が手軽です。例の表では E2:E7 に「営業」「総務」「営業」「開発」「営業」「総務」と並びます。

@ は、列全体のうち数式と同じ行の値だけを使う指定のこと。テーブルの中で使うときは、上の表の2行目か3行目の式を選んでください。

空の行や列を除いて参照する TRIMRANGE 関数もあります。公式ヘルプの対象は Microsoft 365 で、ほかの版で使えるかは確認が取れていません。

原因5:「不確定なサイズ」(結果の大きさが計算のたびに変わる)

公式ヘルプが挙げている例が、次の数式です。

=SEQUENCE(RANDBETWEEN(1,1000))

RANDBETWEEN は、計算のたびに違う数を返す関数。SEQUENCE関数の行数に使うと結果の大きさが毎回変わるので、Excelは広げる範囲を決められません。公式ヘルプは RAND・RANDARRAY も同じ種類の例に挙げています。

公式ヘルプに、具体的な直し方の記載はありません。原因が大きさの変動なので、行数や列数の指定に乱数が入っていないかを見直すのが出発点になるでしょう。

原因6:「メモリが不足している」(配列が大きすぎる)

スピルさせる配列が大きすぎて、メモリが足りなくなった状態です。公式ヘルプの対処は、より小さい配列や範囲を参照すること。

まずは参照先を、データが入っている範囲に絞ってみてください。

その他:「認識されない/フォールバック」(原因を特定できない)

Excelが原因を特定できないときの分類です。公式ヘルプは、必要な引数が数式にすべてそろっているかの確認を挙げています。

抜けている引数がないか見直してから、もう一度エラーの説明を読んでみましょう。

直ったかの確認とよくある質問

直ったかの確認方法

#SPILL! が消え、結果が想定の範囲に並べば解決です。最初の例なら、E3 の「メモ」を消すと3人が並びます。

スピルした範囲の中のセルを選ぶと、範囲を囲む枠が表示されます。この範囲をまとめて別の数式で使う書き方はスピル範囲演算子「A2#」の記事で解説しました。

Q. 邪魔なセルを消してよいか迷うときは?

無理に消さなくても大丈夫。数式のほうを、結果が広がっても何にもぶつからない空いた列へ移せば解決します。

入っているのが数式なら、ほかのセルから参照されているかもしれません。消す前に内容を確かめ、必要なら別の場所へ写しておくと安心です。

Q. 直したはずなのに#SPILL!が消えないときは?

次の順で確かめてみてください。

  1. 破線の枠の中に、スペースや "" のセルが残っていないか
  2. 枠の中に、結合セルが残っていないか
  3. エラーの説明が変わっていないか

原因が1つとは限りません。1つ直したら、もう一度3ステップに戻りましょう。

Q. 別のエラーや別の症状が出たときは?

#SPILL! が消えた後に #VALUE! が出たら、#VALUE!エラーの直し方へ進んでください。

ほかのエラー値はExcelエラー値12種類の原因と対処法一覧で探せます。エラーは出ないのに結果が変わらないときは、Excel 計算されない原因と直し方|症状別早見表で即解決が入口です。

まとめ|#SPILL!は破線の枠と説明から原因を探す

#SPILL! は、結果を広げられないというExcelからの合図です。セルを選び、破線の枠を見て、説明を読む。この順で確かめると、原因の見当がつけやすくなります。

主な原因と直し方は次の4つ。

  • スピル先に中身がある: 見た目が空でも、スペースや "" がないか確かめて消す
  • 結合セルがある: 結合を解除するか、数式を移す
  • テーブルの中にある: テーブルの外へ移すか、範囲に変換する
  • 列全体を参照している: 参照を必要な範囲に絞る

説明が読めないときも、枠の中のセルを数式バーで1つずつ見れば手がかりがつかめるはず。

FILTERやUNIQUEを使う表では、数式の右と下を空けておきましょう。結果が増えても、#SPILL! が出にくくなります。

まずはエラーのセルを1つ選んで、破線の枠を確かめてみてください。

タイトルとURLをコピーしました