Excelのスピルエラーを直すには？必要な件数と出力場所を保って確かめる

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

Excelのスピルエラーを直すときは、何件の結果を出したいかを残したまま、広がる先と数式の参照を確かめます。エラーのセルから障害を調べ、必要なメモを残せる出力先を選び直す方法があります。数式を一件用に変えてエラーが消えた場合は、必要な残りの結果まで出たかを別に確認してください。

「スピル」は、一つの式の複数の結果が周囲のセルへ広がる動作です。その動作自体が失敗なのではありません。本稿では、三件を並べたい道具表を例に、三件を保つ直し方と、一件だけになってしまう変更を比べます。既存の表や大切なメモを消さず、必要な一覧へ戻すための考え方です。

主対象は、動的配列を扱うWindowsデスクトップ版のExcel for Microsoft 365・Excel 2024・Excel 2021です。2026年10月28日に確認したMicrosoftの公式資料を基に整理しています。Mac、Web、旧来の配列数式、Insider向けピボットテーブルの動作を、同じ操作として扱いません。画面の表示名は使用している版で確かめてください。

直す前に、必要な三件と残すセルを決める

三行一列の一覧を、元の道具表から決める

小さな道具の管理表を整理する場面を考えます。A1に「コード」、B1に「名称」、C1に「保管場所」という見出しがあり、A2:C4へ次の三件を置く設定です。検索するコードはすべて元表の先頭列にあり、重複もないものとします。実際の業務表に同じ条件があるかは、その表で確かめる必要があります。

※表は横にスクロールできます。

作りたいのは、P01、P02、P03に対応する名称を、その順序で並べた一覧です。必要な答えは「ランタン」「マット」「フック」の三つで、三行一列と決めます。保管場所の棚A・棚Bは元表にはありますが、今回は出力しません。「元表の全部を出す」のか「名称だけを出す」のかを先に分けることで、空ける場所の幅も決まります。

この設定なら、一行目にランタンが出ただけでは完成ではありません。三つのセルが埋まっていても、全部がランタンなら対応が違います。件数、順序、コードと名称の対応を組にして見る必要があります。後で一件を返す式へ変更する際にも、この三つを基準に戻って判断します。

E2の式が要求する場所と、E3のメモを重ねて見る

最初の出力先をE2とし、次の式を入力する案を考えます。検索値にはA2:A4の三セルを渡し、検索する表は$A$2:$C$4、返す列は表の左から二列目、照合方法は完全一致を表すFALSEです。ここではVLOOKUPのすべての使い方へ広げず、最初の引数が一セルか三セルかに注目します。

=VLOOKUP(A2:A4,$A$2:$C$4,2,FALSE)

動的配列として三つの名称を返す設計なので、E2から下へE4までが必要になります。しかし、例ではE3に「返却確認」という既存のメモがあります。E3は三件の出力領域に入るため、単に右隣の空欄を見るだけでは足りません。縦に広がる三セルと、残したいメモの位置を同じ配置図として読みます。

この時点で、メモを不要なものと決めてはいません。返却を誰かが確認するための情報なら、消して場所を作ることは別の問題を生みます。一方、名称の三件を一件へ減らせばよいという要求にも変えていません。元表、三件の一覧、E3のメモという三つを残せる変更を選ぶことが、この例の課題です。

スピルの基本的な意味と起点の扱いは、Microsoft公式「動的配列数式とスピル配列の動作」で確認できます。結果が広がった先の各セルを、独立した三つの式がある場所と取り違えないことも大切です。式を編集する基準となるのは左上の起点です。

通常のスピル、出力の障害、違う答えを区別する

三つの名称がE2:E4へ正しく広がるなら、それは予定した出力です。「セルへ値が広がった」という理由だけで、止める対象にしません。反対に、#SPILL!や日本語表示の#スピル!が出ていれば、結果を配置できない理由を調べます。さらに、エラー表示がなくても一件しかない状態は、三件の要求に対して不足です。

確認用の記録には、「予定は三行」「表示された名称」「E3のメモが残っているか」を別の欄にします。予定は設計から書けますが、表示された内容は実際に見てから記録します。本稿の教材では後者は未観測です。確認していない欄を0件と書くと、「調べた結果が0件」と読めてしまうため、未確認と区別します。

ここからの変更案は、枝ごとに分けた作業用コピーで比べる想定です。新しい三件の出力、行別の三つの式、結合セルのある配置などを、同じ場所へ順番に混ぜて入力する手順ではありません。各枝の開始条件をそろえると、「何を変えた結果、三件が保たれたか」を説明しやすくなります。

まずは三件の答えを短く手元へ残してください。本例なら「P01→ランタン、P02→マット、P03→フック、E3の返却確認を保持」です。ここまで決めれば、次の章でH列へ出力先を変えた場合にも、必要な答えそのものは変わりません。

出力先が塞がったら、メモを残して置き場所を変える

エラーの起点から、実際に重なるセルを特定する

E2にスピルエラーが出た枝では、最初にそのE2を選びます。Microsoft公式には、意図したスピル範囲を破線で確かめ、エラーチェックの通知から［障害物セルの選択］を使って、妨げているセルへ移る方法が案内されています。表示された説明を読み、単に式の入力が間違っていると決める前に、要求している範囲と重なりを確認します。

本例で調べるのはE2:E4と、その中のE3です。E3の「返却確認」が見つかった場合は、内容を読んで保持する情報だと判断します。実際の表では、例と違うセルが障害になっている可能性があります。画面で特定する手順を省き、「本稿にE3とあるからE3を削除する」という操作にはしないでください。

障害を調べる操作と、障害を消す操作も分けます。前者は原因の位置を知るため、後者は中身を変更する作業です。ここでの目標は三件とメモの両方を残すことなので、必要なメモを消す案を最初の選択にはしません。別の出力先を使えるか、三行一列という必要量に照らして検討します。

詳しいエラー別の公式説明は、Microsoft公式「#SPILL!の修正方法」を参照してください。本稿のメモの枝で分かったことを、不確定な配列サイズやメモリなど別の原因へそのまま当てはめることはできません。

H2へ同じ式を新しく入力し、三つの答えを照合する

次に、H2:H4を候補にします。三セルの値や式、結合の有無、テーブル内かどうかを確認し、今回の出力に使える場所かを判断します。見出しをH1に置く場合も、既に書かれたものを上書きしないよう確かめます。H列が空いていることは教材の配置条件で、利用者の実際のH列まで空いているという意味ではありません。

この枝では、H2へ最初と同じ文字列を新規入力します。入力する式は、引き続き=VLOOKUP(A2:A4,$A$2:$C$4,2,FALSE)です。式の置き場所をH2にしただけで、探すコードのA2:A4を別の場所へ変えてはいません。元表の範囲、返す二列目、完全一致も同じです。

期待する出力位置はH2がランタン、H3がマット、H4がフックです。三つを元表のP01、P02、P03へ戻って照合し、E3の「返却確認」も残っているかを確かめます。H列で何かが表示されたことと、元の三件を保てたことを同一視しません。名称の並びまで見れば、先頭だけ合って残りが違う状態も見つけやすくなります。

H2での照合が終わった後、旧E2の式をどうするかを判断します。元の出力を参照している別の式や利用者がいないかも確認対象です。新しい一覧ができたからといって、旧式を自動的に削除する手順にはしません。反対に、古いエラーを説明なしで残し続けることも完了条件ではありません。どの位置を正式な一覧にするかを決め、その影響を確認して整理します。

E2からH2へのコピーは、同じ文字列の再入力と違う

ここで起こりやすいのが、「H2へ出したいから、E2をコピーして貼れば同じ」という取り違えです。E列からH列へは三列右に動きます。最初の式の検索値A2:A4は相対参照なので、セルのコピーではD2:D4へ変わる関係になります。一方、$A$2:$C$4は列も行も固定した参照です。

H2へ同じ文字列を新規入力する案
=VLOOKUP(A2:A4,$A$2:$C$4,2,FALSE)

E2を三列右のH2へセルコピーする場合の参照関係
=VLOOKUP(D2:D4,$A$2:$C$4,2,FALSE)

後者は、P01・P02・P03があるA列を検索値としていません。三行の形だけは残っていても、中身の要求が変わっています。本例ではD列にコードを設定していないため、この変更案を三件の成功として受け入れることはできません。実際にどの値やエラーが表示されたかは、実機で読んだ結果を記録する欄で確認します。

切り取りによる移動も、コピーと同じ言葉でまとめないようにします。公式説明では、切り取りで式を移動したときは式内の参照が変わらず、セルをコピーしたときは相対参照が変わります。Microsoft公式「Excelで数式を移動またはコピーする」は、移動時とコピー時の参照の違いを説明しています。本稿の主手順は、比較を明確にするために「H2へ同じ式を新規入力する案」です。コピー、切り取り、再入力のいずれを使ったかを記録に残してください。

式の見た目が似ているから安全とも、絶対参照が一か所あるから全体が固定されるとも判断できません。H2を選んで数式バーを読み、検索値がA2:A4、元表が$A$2:$C$4になっているかを確認します。この枝の合格は、出力先を変更しつつ、三つのコードとメモを保持できたときです。

一件だけに変わる修正を、成功と取り違えない

A2:A4をA2に変えると、求める件数が変わる

元のE2の式を、=VLOOKUP(A2,$A$2:$C$4,2,FALSE)へ変える案を考えます。最初の引数が三つのセルからA2一つへ変わりました。P01を検索してランタンを返す一件用の設計です。下のE3へ三件を広げる必要がなくなれば、メモとの重なりの条件も変わります。しかし、P02とP03を求める処理を、この一つの式で済ませたわけではありません。

この案が悪い数式なのではありません。「今はP01の名称だけがほしい」という要求なら、一件を返す式を選ぶ理由があります。本例で不足になるのは、要求を三件のままにして、式だけ一件用へ変えてしまう場合です。修正の前後で、要求も変えたのか、場所だけを直したのかを区別します。

H2に一件用の式を置き、H3とH4が空いている状態を想定してください。先頭のランタンは正しくても、マットとフックがまだありません。このとき「エラーなし」「先頭一致」「三件完了」の三つは、違う判定になります。先頭一致だけを見て三件完了へ進めないことが、この比較の要点です。

@A:Aは、その式と同じ行のコードを選ぶ

別の案として、次の式を考えます。@A:Aは、A列から、その数式と同じ行にある値を選ぶ意味で読みます。H2に置いた枝ではA2のP01、H3に置いた枝ではA3のP02を検索値にする関係です。列全体を渡し続けて三件を広げる指定とは違います。

=VLOOKUP(@A:A,$A$2:$C$4,2,FALSE)

ここで、一件用のA2との違いを比べます。H3へ=VLOOKUP(A2,$A$2:$C$4,2,FALSE)という同じ文字列を新規入力した場合、検索するのは引き続きA2です。H3へ@A:Aの式を置いた場合は、行3という位置からA3を選びます。両方とも一件の式でも、「どの一件を選ぶか」が同じとは限りません。

この比較は、どちらかを既存のスピル結果の途中へ入力する操作ではありません。枝を分けたコピーで、指定した起点へそれぞれ置く案です。既にH2:H4へ一つの式の結果が広がっているところへ、H3だけ別の式を入れる使い方と混同しないでください。

Microsoft公式「演算子の共通部分：@」は、範囲から同じ行または列の値を取る場合と、配列から左上の値を取る場合を分けて説明しています。本例の@A:Aは範囲に対する話です。どこへ@を付けても同じ一件になる、古い式に付いた@は全部消す、といった一律の処理にはしません。

三行のスカラー式なら、三件を満たす設計もできる

一件を返す式を一つ置くだけでは不足でも、H2、H3、H4へ行ごとの正しい式を用意する方法はあります。H2がA2、H3がA3、H4がA4を検索するようにすれば、それぞれがランタン、マット、フックに対応する設計になります。三件を得る手段が、動的配列一つだけだという二者択一にはしません。

※表は横にスクロールできます。

行別の三式を使う枝では、=VLOOKUP(A2,$A$2:$C$4,2,FALSE)をH2から下へコピーするとき、検索値がA3、A4へ変わり、固定した元表の範囲は同じかを確かめます。単に同じ文字列を三セルへ再入力した場合は、全部がA2を検索する別の案です。先ほどの横方向コピーの問題と、ここで意図して行を進めるコピーを分けて考えます。

@の三式でも、行2・3・4に置くことが今回の対応を支えています。見やすさのために一覧だけを行10へ移し、式の行位置が変わるなら、同じコードを読む設計かを再検討します。「同じ文字列だから入力のキーも同じ」とは限りません。配列式を選ぶか、行別の式を選ぶかは、配置と更新方法を含めて決めます。

方式の違いを、修正の記録にも残す

配列方式なら、H2の一つの式と、その出力領域H2:H4を組にして確認します。行別方式なら、H2:H4に置いた三つの式をそれぞれ確認します。表示された三名称だけを保存しても、後からどちらの方式で更新する一覧なのかが分からなくなるため、起点か各行の式かという管理単位を残してください。

例えば「方式：配列一式、検索キー：A2:A4、出力：H2:H4」と書く案と、「方式：行別三式、検索キー：A2・A3・A4、出力：H2・H3・H4」と書く案では、増やすときの作業も違います。前者は必要な範囲を改め、後者は新しい行の式も用意することになります。現在三件が一致した事実から、将来の追加も自動で済むとは判断しません。

VLOOKUPの完全一致や列番号の意味は、Microsoft公式「VLOOKUP関数」でも確認できます。返す列を別の列へ変えてしまえば、三行あっても名称一覧ではなくなります。検索値の形だけでなく、返す内容も元の要求と照合して、この枝の判定を終えます。

テーブルと結合セル、シートの端を別の枝で直す

元表がテーブルであることと、式を置く場所を分ける

これまでの通常のセル範囲とは別に、元の道具表をExcelテーブルとして管理する案を考えます。元表をテーブルにすることと、三件を広げる式をそのテーブルの中に置くことは、同じ判断ではありません。Microsoftの公式説明では、スピルする配列数式はテーブルの外側へ配置する必要があると案内されています。

本例なら、元データのA1:C4をテーブルとして保持し、出力をH2からの通常のセル範囲にする配置を検討できます。H2が別のテーブルの内側であれば、「A列の元表の外だから大丈夫」とは判断できません。式を置こうとするH2自身の所属も見ます。元表の種類だけを変えても、出力先の制約が自動的になくなるわけではありません。

三件を保って出力先を直したい場合、元表のテーブルをいきなり通常の範囲へ変換する必要があるとは限りません。元表を管理する都合を残しつつ、配列の出力先をテーブル外の空き領域へ置く案があります。元表の運用を変える案と、結果の置き場所を変える案を分けて、その影響を比べてください。

テーブル名や列名を使う構造化参照には、元データの増減に応じて参照が調整される性質があります。これはMicrosoft公式「Excelテーブルでの構造化参照の使い方」に説明されています。ただし、本例で提示した固定のA2:A4という指定まで、自動的に四件へ広がるという意味ではありません。どの式が、どの参照方式を使うかを実際に読みます。

当サイトのExcel FILTER関数の使い方では、元表のテーブルと出力先を分け、抽出条件を更新する例を扱っています。条件で対象行を選ぶ仕事なら、そちらの役割も確認してください。本稿では、三つのコードを名称へ対応させる要求を保ったまま、出力方式と配置を選ぶことに絞ります。

H3:I3の結合を残すなら、出力先を選び直す

別のコピーでは、H3:I3が見出し用に結合されている設定とします。H2から三行を使う予定なら、二行目のH3がその結合に重なります。値が見えない、色が付いていない、見出しが空白、といった見た目だけでは、通常の空きセルだと判断できません。

結合セルへスピルできない場合の公式な選択肢には、結合を解除することや、結合と交差しない範囲へ式を置くことがあります。この例では、その見出しのレイアウトを残したいという条件を加えます。すると、H3の値を消す案ではなく、結合を維持したまま別の三行一列を探す案が適しています。

例えばK2:K4を候補にするなら、K列の空きと結合、テーブルの内外を改めて確認します。同じ文字列の式をK2へ新規入力する案では、検索値はA2:A4、元表は$A$2:$C$4のままです。期待する三名称の位置だけがK2、K3、K4へ変わります。H列からのセルコピーなら相対参照の検討が別に必要になるため、操作名も省略しません。

この枝で保持するのは、三つの名称、元表、E3のメモに加えて、H3:I3の結合です。保持するものが増えた分、出力先の選択条件も増えています。結合を解除してよいかが分からない共有表では、体裁を変える判断を先に済ませるか、影響のない別領域を選びます。値だけを消して結合の問題まで直ったと扱わないことが大切です。

全列を求める式と、有限の三件を求める式を区別する

シートの端を超える枝は、E3のメモとは別の原因です。Microsoft公式は、検索値へ列全体を渡す式が、動的配列として大量の結果を要求する場合を説明しています。Excelの一列は1,048,576行ですが、2行目から下へ使えるのは1,048,575行です。列全体と同じ行数の結果を2行目から下へ配置するなら、一行分がシートの下端を超えます。

本例の三件では、H2からH4までしか必要としません。一方、最初の引数をA:Aにした案では、表に三件の道具しか見えていなくても、指定する検索値の範囲は列全体です。「元表が三行だから出力要求も三行」とは限らない点を分けて読みます。探す値の範囲と、検索する元表の範囲は別の引数です。

三件が必要なら、検索値をA2:A4へ限定することは、その要求と合う案です。A2一つへ限定する案は一件の設計なので、その一式だけで三件を満たしたとは判定できません。行別方式を選ぶなら、前章のように残りの二式も含めて設計します。範囲を短くしたという理由だけで、必要な件数まで減らしていないかを確認してください。

行数の上限はここでは配置の算術として使い、実機の負荷や処理時間を測った値ではありません。三件へ限定すればすべてのスピルエラーが消えるという保証もしません。場所が塞がっている、式がテーブル内にある、結合に重なる、といった条件はそれぞれ別に残り得ます。この章では、どの枝の条件を変えたかを混ぜずに記録します。

検索関数の返す方向や複数列の扱いを整理したいときは、当サイトのExcelのXLOOKUP関数の使い方も参考になります。関数を替える場合にも、必要な件数と出力領域を保てたかという判定は残ります。別の関数へ変更したこと自体を、今回の要求を満たした証拠にはしません。

名称の変更と四件目の追加で、直した範囲を確かめる

P02の名称だけを変え、三件の対応を確かめる

最後に、三件を出せるよう設計した枝を基に、更新の影響を分けて調べます。最初はP02の名称を「マット」から「折りたたみマット」へ変更する設定です。変えるのはB3で、コードP02、元表の三件、出力する三行という要求はそのままにします。保管場所や他の名称まで一緒に変更しないよう、試す条件を一つに絞ります。

配列方式のH2では、検索値A2:A4と表$A$2:$C$4がそのままなら、期待する並びはランタン、折りたたみマット、フックです。H3に対応する名称だけが変わり、先頭と末尾の名称は同じという照合になります。三件のうち一件を変える試験なので、三行すべてが変わることを成功条件にはしません。

行別方式を選んだ枝なら、H2・H3・H4の三式に対して同じ三つの期待値を使えます。H3だけA2を参照したままなら、P02の名称変更へ対応する設計になっていません。三件の表示数が同じでも、どの行を検索しているかが違えば、更新の伝わり方も違います。名称の変更は、この差を確認するための具体的な試験です。

この変更前後では、E3の「返却確認」に変更を加える必要はありません。メモまで変わっていたら、別の編集を混ぜていないかを調べます。また、表示された結果を実際に確認していない段階では、期待値を書いただけです。確認時には、どのコピーで、どの式を使い、三つの位置に何が見えたかを読み取って記録してください。

P04が増えても、三件の要求は勝手に四件にならない

次の試験は別の開始状態へ戻し、A5:C5へ四件目を追加する設定です。コードはP04、名称はロープ、保管場所は棚Cとします。ここでも実際の在庫や購入価格を示しているわけではありません。元表の件数が増えたときに、何件の一覧を要求するかを考えるための追加です。

要求が「P01、P02、P03の三件を出す」のままなら、検索値A2:A4にP04は入っていません。三件の一覧へロープが出ないことだけを見て、直ちに欠落エラーとはしません。元表の全件一覧ではなく、指定した三つのコードの名称を返す設計だからです。出力結果だけでなく、依頼している対象の範囲を読み直します。

この区別をしないと、元表へ一行追加するたびに、不要な対象まで一覧へ含める変更をしてしまいます。例えば、今回の提出対象は三件の貸出品だけで、P04は別の扱いという要求も考えられます。必要な対象が変わったのか、元表に新しい情報が加わっただけなのかを、先に決めてください。

反対に、依頼が「P04も含む四件を出す」へ変わったら、そこで初めて成功条件を四行一列へ改めます。今までの三件のままでよいという判定を、その新しい要求へ持ち越すことはできません。名称変更と対象追加、要求変更を分けておけば、何が更新不足なのかを具体的に説明できます。

四件へ変えるなら、検索値・元表・出力先を合わせる

四件を出す配列方式の案は、次の式です。検索値をA2:A5へ広げるだけでなく、元表も$A$2:$C$5へ広げます。出力する場所はH2:H5となるため、新たに必要になったH5が使えるかも確認します。元表の範囲を古いままにすればP04を検索する条件が整わず、場所を三行分しか確保しなければ、出力するための条件が足りません。

=VLOOKUP(A2:A5,$A$2:$C$5,2,FALSE)

期待する並びは、名称変更を混ぜない開始状態なら、ランタン、マット、フック、ロープです。名称変更も正式に採用した状態なら、二件目は折りたたみマットになります。どちらの開始状態から試したかを記録し、異なるコピーの答えを並べて不一致としないようにします。

行別方式で四件へ変える場合は、四つ目の式をH5へ用意する必要があります。さらに、P04を参照できるよう各式の元表の範囲も確かめます。既存三式を見ずに新しい一式だけ追加すると、行ごとに参照している表の世代が違う状態になり得ます。先頭から四件目まで、同じ要求に対する式として読み直してください。

H5に合計欄や別のメモがある配置なら、三件のときに合格したH2:H4を、そのまま四件の合格へ延長できません。保持する欄を決め、四行使える別の出力先を検討するなど、第二章の判断へ戻ります。追加によって必要な空間が変わったことを、元のエラーが再発しただけと片付けずに捉えます。

期待した答えと、実際に読んだ答えを別々に残す

確認記録は、「変更点」「必要な件数と並び」「実際の表示」「残すもの」の四つに分けると使いやすくなります。例えば名称だけの変更なら三件、P04を対象に加える要求変更なら四件です。どの行を足したかだけでなく、どの要求へ合わせて式を直したかを書いておくと、次の担当者も判断を追えます。

未確認の結果欄は未確認のままにします。計測して0件だった場合、値が空欄である場合、試験の対象外だった場合も、同じ印にはしません。Pythonなどで参照範囲や期待名称の対応を整理したとしても、それはExcelの画面で表示を確かめた結果とは別です。使用版での操作と実際の答えを確認して、初めてその環境の記録になります。

提出用に数式を残すか、確認時点の値として渡すかも、利用目的に合わせて決めます。値だけの一覧は、その後に元表の名称を変えても数式による追従を期待するものではありません。列分割など別の加工と一緒に扱う場合は、当サイトのExcelで文字列を分割する方法にある、出力項目と更新方法を確かめる視点も参考にしてください。

スピルエラーへの対処は、表示を消して終える作業ではありません。本例では、三件という要求を保つなら三名称とメモを守り、四件へ変えるなら式と場所も四件へ合わせます。一つの配列式、行ごとの式、場所の選び直しを比較し、何を変えて何を残したかを説明できる状態まで確かめましょう。