Excelで数式を入力したとき、結果が1セルではなく複数行・複数列に広がるはずなのに「#SPILL!」と表示されることがあります。
#SPILL!は、数式が返そうとしている複数の結果を、必要なセル範囲へ展開できないときに発生するエラーです。 まず数式そのものを書き直すのではなく、「どこまで結果が広がる予定なのか」を確認してください。
特に多い原因は、出力予定範囲に既存データがある、Excelテーブル内に動的配列数式を入力している、列全体などを参照して出力範囲がワークシートの端を超えている、といったケースです。Microsoftのサポートでも、これらは#SPILL!の代表的な原因として案内されています。
この記事では、#SPILL!を「予定範囲の確認→障害物→テーブル→大きすぎる出力」の順番で切り分ける方法を解説します。
Excelの#SPILL!とは
動的配列に対応したExcelでは、1つの数式から複数の値を返し、その結果を周囲のセルへ自動的に展開できます。この動作を「スピル」と呼びます。
たとえば、次のような数式です。
=SORT(A2:A10)SORT関数で複数件のデータが返される場合、数式を入力したセルだけではなく、その下のセルにも結果が自動的に展開されます。
ところが、展開先に別の値が入っているなどの理由で必要な範囲を確保できないと、Excelは結果を途中まで表示するのではなく「#SPILL!」を返します。
したがって、#SPILL!が出たときは次の順番で確認すると原因を絞り込みやすくなります。
| 確認順 | 主な原因 | 対処 |
|---|---|---|
| 1 | 出力予定範囲に既存データがある | 障害になっているセルを確認する |
| 2 | 結合セルなどが出力を妨げている | 結合を解除するか数式を移動する |
| 3 | Excelテーブル内に数式がある | 数式をテーブル外へ移動する |
| 4 | 出力範囲が大きすぎる | 参照範囲を必要な行だけに絞る |
原因が分からないまま周囲のセルを削除するより、この順番で切り分ける方が安全です。
まず#SPILL!セルを選び、出力予定範囲を確認する
最初に行うべきことは、#SPILL!が表示されているセルの選択です。
スピルできない数式を選択すると、Excelでは結果が展開される予定だった範囲が点線の枠で示されます。Microsoftの案内でも、この予定範囲を確認して障害となっているセルを特定する方法が説明されています。
たとえば、数式の結果が本来D2:D10へ表示される予定で、D7だけに文字が入力されている場合、D7がスピルを妨げます。
ここで重要なのは、数式が入力されているセルだけを見ても原因が分からないことがある点です。
数式自体は正しくても、数行先に入力されている値が原因で#SPILL!になることがあります。
出力予定範囲に既存データがないか確認する
最も分かりやすい原因は、スピル先にすでにデータが入っているケースです。
エラーの確認メニューに「障害となっているセルを選択する」趣旨の項目が表示される場合は、それを使うと競合しているセルを探しやすくなります。
障害セルが見つかったら、次のどちらかで対処します。
- 既存データを別の場所へ移動する
- 動的配列数式を十分な空き領域へ移動する
出力予定範囲を一括削除するのは避ける
#SPILL!を直すために、点線で囲まれた範囲をすべて選択してDeleteキーで消す方法はおすすめできません。
障害になっているのが1セルだけでも、範囲内には必要なデータや別の数式が含まれている可能性があります。
たとえば、次のような状態です。
D2 ← 動的配列数式
D3 空白
D4 空白
D5 手入力した重要な値
D6 空白この場合、問題なのはD5だけです。
D2:D6をまとめて削除するのではなく、D5の内容を確認し、必要なら別の場所へ退避してから修正します。
「#SPILL!だから周囲を消す」ではなく、「何がスピルを止めているのかを特定する」ことが基本です。
結合セルが出力範囲に入っていないか確認する
見た目には空白でも、セルの結合がスピルを妨げる場合があります。
動的配列の結果は、結合セルをまたいで正常に展開できません。Microsoftも、出力予定範囲と結合セルが重なる場合は、結合を解除するか、数式を別の場所へ移動する方法を案内しています。
特に帳票形式のExcelファイルでは、タイトルやレイアウト調整のために結合セルが使われていることがあります。
見た目が空白だから問題ないと判断せず、点線で示された出力予定範囲に結合セルが含まれていないか確認してください。
Excelテーブル内なら数式をテーブル外へ移動する
既存データにも結合セルにも問題がない場合は、数式を入力している場所を確認します。
スピルする動的配列数式はExcelテーブル内ではサポートされません。
Microsoftも、テーブル内で#SPILL!が発生する場合は、動的配列数式をテーブル外へ移動するか、必要に応じてテーブルを通常範囲へ変換する方法を案内しています。
たとえば、商品一覧をExcelテーブルにしていて、その中へ次のような数式を入力するケースです。
=UNIQUE(A2:A100)UNIQUE関数は複数セルへ結果を展開する可能性があります。そのため、一覧を取り出したいのであれば、基本的にはテーブルの外側に数式を置きます。
テーブルを通常範囲へ変換する前に確認する
#SPILL!を直すだけなら、まず数式をテーブル外へ移動する方が影響を抑えられます。
テーブルを通常のセル範囲へ変換すると、ブック内でテーブルを前提として使っている機能や数式への影響を確認する必要があります。
そのため、
「動的配列を使いたいからテーブルを解除する」
とすぐ判断するのではなく、
「動的配列の結果だけをテーブル外へ出せないか」
を先に検討すると安全です。
列全体を参照している場合は範囲を必要な行まで絞る
空きセルにもテーブルにも問題がない場合は、数式が返そうとしている結果の大きさを確認します。
特に注意したいのが、次のような列全体の参照です。
=VLOOKUP(A:A,A:C,2,FALSE)動的配列に対応したExcelでは、A:Aを大量の入力値として扱い、多数の結果を返そうとする場合があります。
Excelのワークシートには行数の上限があるため、数式の入力位置によっては結果がシート末尾を超え、#SPILL!になります。
Microsoftのサポートでも、列全体を参照したVLOOKUPを例に、動的配列によって結果がワークシートの端を超えるケースが説明されています。
実際に使う範囲だけを指定する
たとえば必要なデータが2行目から1000行目までなら、列全体ではなく次のように範囲を限定します。
=VLOOKUP(A2:A1000,A:C,2,FALSE)検索対象がA2:A1000なら、最大999件の結果を返すため、出力される量を把握しやすくなります。
動的配列では、昔から使っていた「とりあえず列全体を指定する」数式が、想定以上に大きな配列を生成することがあります。
#SPILL!が出たときは、入力範囲だけでなく、数式が最終的に何個の結果を返そうとしているかを見ることが重要です。
「メモリ不足」と表示される場合も範囲を小さくする
非常に大きな配列を生成しようとすると、ワークシートの端だけでなく、使用可能なメモリが問題になる場合もあります。
Microsoftのサポートでは、スピル配列によってメモリ不足になる場合、より小さな配列またはセル範囲を参照するよう案内しています。
たとえば、
=A:A*B:Bのように列全体を配列計算へ渡すより、実際に必要な行が5000行までなら、
=A2:A5000*B2:B5000のように必要な範囲へ限定した方が、計算対象を明確にできます。
#SPILL!の解消だけでなく、不要に巨大な配列を作らないという意味でも有効です。
#SPILL!が直らないときの確認ポイント
ここまで確認しても原因が見つからない場合は、数式が返す配列サイズそのものが安定しているかも確認します。
Microsoftのサポートでは、再計算するたびに配列サイズが変化し、Excelが出力範囲を確定できない場合にも#SPILL!が発生すると説明されています。
例として挙げられているのが、RANDBETWEEN関数によってSEQUENCE関数の行数が変わるような数式です。
=SEQUENCE(RANDBETWEEN(1,1000))このタイプは「セルが埋まっている」という問題とは原因が異なります。
最初から難しい数式を疑う必要はありませんが、空きセル、結合セル、テーブル、シート端を確認しても解消しない場合は、配列サイズが計算のたびに変動していないか確認します。
#SPILL!と#CALC!は原因が異なる
動的配列を使っていると、#SPILL!以外のエラーが表示されることもあります。
たとえばFILTER関数で条件に一致するデータが存在しない場合などに見かける#CALC!は、#SPILL!とは切り分けて考える必要があります。
#SPILL!は基本的に、返そうとしている結果をセルへ展開できない問題です。
そのため、まず見るべきなのは検索条件や計算結果ではなく、
- どこへ結果を出そうとしているか
- その場所が空いているか
- テーブル内ではないか
- 出力件数が大きすぎないか
という「出力先」の状態です。
エラーコードごとに原因を分けて考えると、不要な数式修正を減らせます。
ピボットテーブルの#SPILL!は通常の動的配列と分けて考える
Microsoftの#SPILL!解説にはピボットテーブルに関する記述もありますが、同ページではPivotTable #SPILL!についてMicrosoft 365 Insider向けである旨が明記されています。
そのため、通常版Excelで発生するすべての#SPILL!を「ピボットテーブルでも同じ仕様」と一般化しない方が適切です。
通常のワークシートで数式を入力して#SPILL!になった場合は、まず動的配列数式の問題として切り分けてください。
#SPILL!は「数式」より先に「出力先」を確認する
Excelで#SPILL!が表示されたときは、いきなり数式を書き換える必要はありません。
まず#SPILL!セルを選択し、点線で示される出力予定範囲を確認します。
そのうえで、
- 出力予定範囲に値や数式がないか
- 結合セルが含まれていないか
- 数式をExcelテーブル内へ入力していないか
- 列全体参照などで巨大な配列になっていないか
- 出力先がワークシートの端を超えていないか
の順番で確認してください。
特に実務では、障害セルを確認せず出力予定範囲を一括削除しないことが重要です。必要なデータまで消す可能性があるためです。
原因が既存データなら必要なセルだけを移動し、テーブルが原因なら数式をテーブル外へ移します。範囲が大きすぎるなら、実際に必要な行だけを参照するよう数式を見直します。
#SPILL!は「数式が間違っている」というより、「数式が返す結果を置く場所をExcelが確保できない」と考えると、原因を見つけやすくなります。
コメント