エクセルで#スピル!が出る。下のセルがふさがっていると、答えを上書きせずに止まるんだよ

どければ、答えが戻る。青い点線の枠の4つのマスのいちばん上に#スピル!、3つ目にメモのカード。上で4つの玉が止まっている。先輩がメモを指さし、後輩A君が「メモのせいか!」と気づいている絵 A君の成長

おはよう、後輩A君。

式を入れたら、セルに「#スピル!」と出た。今日はこの話だよ。

このところのエクセルの記事は、エラーが出ないまま答えがずれる話が続いていたよね(31・33・34・35本目)。今日はその逆なんだ。⭐ エクセルが止まって、「ここがふさがっているよ」と知らせてくれる話だよ。

先に答えを言うね。

① #スピル!は、答えを並べたいセルに、もう何かが入っているときに出た。中にあった「メモ」は、上書きされずにそのままだった
② セルの横の「!」の印を押すと、理由の見出しが読める
③ 「阻害しているセルを選択」を押すと、ふさいでいるセルが選ばれた
④ そのセルを消したら、式を打ち直さなくても答えが戻った

⇒ #スピル!は、エクセルが壊れたんじゃないんだよ。

11月17日に、Mac版のエクセル(16.113.2)で測ったよ。式を入れるのと、E5の「メモ」を消すのは命令(プログラム)でやったんだ。「!」のメニューの写真を撮ったのと、「阻害しているセルを選択」を押したのは、わたしが自分の手でやったよ。

1. #スピル!は「答えを書く場所がふさがっているよ」という知らせなんだよ

新しいエクセルには、1つの式の答えが、下や右のセルまで並ぶ式があるんだ。今日の測定では、E3に入れた1つの式の答えが、E3〜E6の4つのセルに並んだよ。このことを、エクセルは「スピル」(あふれる)と呼んでいるんだ。

並べたい場所に何か入っていると、答えは並ばずに、式のセルに #スピル! と出たよ。今日、#スピル!が出たのはこの4つだったんだ。

試したこと 結果 「!」の理由の見出し
答えの並ぶ場所に「メモ」 ⚠️ #スピル! スピル範囲が空白ではありません
答えの並ぶ場所に結合したセル ⚠️ #スピル! スピル範囲に結合されたセルがあります
XLOOKUPで2列を返す・となりに「備考」 ⚠️ #スピル! (見ていない)
VLOOKUPの探す値をA:A(列ごと) ⚠️ #スピル! スピル範囲が大きすぎます

2. 測った表は、名簿・商品・注文の3つだよ

ファイルは命令(python)で作ったよ。中身はこうなんだ。

名簿 → A1に「部署」、A2〜A11に 総務・営業・開発・営業・総務・経理・開発・営業・経理・総務(10行・4種類)
商品 → コード・品名・単価。P01 えんぴつ 80/P02 ノート 150/P03 消しゴム 60/P04 定規 120/P05 ペン 200
注文 → A2〜A6に P02・P05・P01・P03・P04、B1に P03、G3に「備考」

式は、命令で入れたあとに読み戻して、入れたとおりの式(=UNIQUE(A2:A11))になっていることと、答えが下のセルまであふれたことを確かめたよ。

3. ふさがっていないときは、E3〜E6に4つ並んだんだよ

まずは何もじゃまがないときだよ。名簿のE3に、この式を入れたんだ。

=UNIQUE(A2:A11)

UNIQUE(ユニーク)は、同じものを1つにまとめて並べる式だよ。結果はこうだったんだ。

E3 総務
E4 営業
E5 開発
E6 経理
E7 (空のまま)

10行の部署が、4種類になって並んだよ。式を入れたのはE3だけなのに、答えはE6まで使ったんだ。

4. ⚠️ E5に「メモ」があると、#スピル!が出たんだよ

次は、答えが並ぶはずのE5に「メモ」と入れておいたよ。そうしたら、E3は #スピル! になったんだ。
名簿のE3に「#スピル!」と出ている。数式バーは =UNIQUE(A2:A11)。E3からE6のまわりに青い点線の枠があり、E5に「メモ」と入っている
E3〜E6に、青い点線の枠が出ているよね。ここが「答えを並べたかった場所」なんだ。その中のE5に「メモ」がある。⭐ そして「メモ」は、上書きされずにそのまま残っていたんだよ。

E3をクリックすると、セルの横に「!」の印が出たよ。押すと、こんなメニューが出たんだ(わたしが撮った写真だよ)。
E3の#スピル!の横の「!」の印を押して出たメニュー。見出しは「スピル範囲が空白ではありません」。その下に「このエラーに関するヘルプ(H)」「阻害しているセルを選択(S)」「エラーを無視する(I)」「数式バーで編集(F)」「エラー チェック オプション…」が並んでいる

見出し:スピル範囲が空白ではありません
このエラーに関するヘルプ(H)
阻害しているセルを選択(S)
エラーを無視する(I)
数式バーで編集(F)
エラー チェック オプション…

いちばん上の見出しが、止まった理由なんだ。「スピル範囲」は答えを並べたい場所、「空白ではありません」は、そこに何か入っている、ということだよ。

5. ⭐「阻害しているセルを選択」を押して消したら、答えが戻ったんだよ

メニューの「阻害しているセルを選択」を、わたしが押したよ。「阻害している」は、じゃまをしている、という意味だよ。

選ばれたのはE5(メモ)だったんだ。
「阻害しているセルを選択」を押したあと。名前の欄が E5、数式バーが「メモ」になっていて、E5が選ばれている。E3は#スピル!のまま
⇒ 点線の枠の中のどのセルがじゃまなのかを、エクセルが教えてくれたんだよ。

次に、E5の「メモ」を命令で消したよ。そうしたら、E3〜E6に 総務・営業・開発・経理 が戻ったんだ。
E5のメモを消したあと。数式バーは =UNIQUE(A2:A11)。E3からE6に 総務・営業・開発・経理 が並び、青い枠で囲まれている
⭐ 式は打ち直していないよ。じゃまなものをどけたら、答えが自分で並んだんだ。

⇒ だから、#スピル!が出たときにやることは、この3つだよ。

❶ #スピル!のセルをクリックして、横の「!」を押す
❷ いちばん上の見出しで、理由を読む
❸ 「阻害しているセルを選択」で、じゃまなセルを見つけてどける

⚠️ どけるセルに、大事なことが書いてあるかもしれないよね。消す前に、中身を見てからにしようか。

6. 結合したセルがあっても#スピル!。見出しがちがったんだよ

名簿のG5とG6を結合(2つのセルを1つにまとめること)して、G3に同じ =UNIQUE(A2:A11) を入れたよ。結果は #スピル! だったんだ。
名簿のG3に#スピル!が出ていて、横の「!」の印を押したメニュー。見出しは「スピル範囲に結合されたセルがあります」。「阻害しているセルを選択(S)」は押せる色になっている。となりのE3からE6には 総務・営業・開発・経理 が並んでいる

見出し:スピル範囲に結合されたセルがあります
「阻害しているセルを選択」 → 押せる色で出ていた

4章の「空白ではありません」とは、見出しがちがうよね。⇒ 同じ#スピル!でも、理由はいくつかあるんだ。だから、まず見出しを読もうか。

7. XLOOKUPでも、2列を返すと右にあふれるんだよ

このブログでは、#N/Aの記事や列を消したらVLOOKUPがずれた記事で、XLOOKUP(エックスルックアップ)は使えるなら楽、と書いてきたよね。そのXLOOKUPでも、#スピル!は出たんだ。

注文の表で、B1のコード(P03)を商品の表から探して、品名と単価の2列を返す式を入れたよ。

=XLOOKUP($B$1,商品!A2:A6,商品!B2:C6)

F3に入れた(となりのG3に「備考」がある) → ⚠️ #スピル!
F5に入れた(となりのG5は空) → ⭐ F5に消しゴム、G5に60

2列を返すと、答えは下じゃなくて右にあふれたんだ。だから、となりの列に何か入っていると止まったよ。8章の写真の右のほうに、F3の#スピル!とG3の「備考」、F5の消しゴムとG5の60が写っているよ。

⇒ XLOOKUPで何列か返すときは、右どなりの列があいているかも見ようか。

8. VLOOKUPで探す値をA:Aと列ごと選ぶと、「大きすぎます」が出たんだよ

もう1つ、理由のちがう#スピル!があったよ。注文の表のC2に、探す値をA:A(A列まるごと)にしたVLOOKUPを入れたんだ。

=VLOOKUP(A:A,商品!A:C,3,FALSE)

結果は #スピル! だったよ。⚠️ でも、C列はC2の式より下がずっと空だったんだ。ふさがっていないのに出たんだよ。
注文の表のC2に#スピル!が出ていて、横の「!」の印を押したメニュー。見出しは「スピル範囲が大きすぎます」。「阻害しているセルを選択(S)」はうすい色で押せない。D2とH2には150と出ている。右のF3に#スピル!、G3に備考、F5に消しゴム、G5に60がある

見出し:スピル範囲が大きすぎます
「阻害しているセルを選択」 → ⚠️ うすい色で押せない

C2より下は空だったから、4章のように「じゃまなセルがあって止まった」のとはちがうんだね。

探す値を変えたら、答えが出たよ。

D2 =VLOOKUP(A2,商品!A:C,3,FALSE) → ⭐ 150
H2 =VLOOKUP(@A:A,商品!A:C,3,FALSE) → ⭐ 150

A2はP02(ノート)だから、単価の150が返ってきたんだ。⇒ 「大きすぎます」が出たら、探す値をA2のように1つのセルにしようか。A:Aの前に@を付けた形でも、150が出たよ。

9. ⚠️ 今日測れなかったこと

・測ったのはMac版のエクセル(16.113.2)だよ。Windows版で同じになるかは、分からないんだ
・テーブル(表の書式を付けた範囲)の中で同じ式を入れたときは、測っていないよ
・式もメモの消し方も命令でやったんだ。手で式を打ったとき・手で消したときは、試していないよ
・「スピル範囲が大きすぎます」が、何行をこえると出るのかは分からないんだ。A:A(1列まるごと)で出た、までだよ
・「このエラーに関するヘルプ」の中身は、見ていないよ
・UNIQUEがない古いエクセルで、このファイルがどう見えるかは、試していないんだ
・XLOOKUPの#スピル!(7章)は、「!」の見出しを見ていないよ

10. エクセルの記事、これで36本目だよ

35本目は、CSVに保存する前に、番号の列を1回だけ見る話だったね。

エクセルのエラー記号の記事では、#DIV/0!・#N/A・#NAME?・#REF!・#VALUE! の5つを書いたよ。今日の#スピル!は、そこに入っていなかった6つ目なんだ。あの記事で書いたとおり、記号は困りごとの説明なんだよ。#スピル!の説明は「答えを書く場所がふさがっているよ」なんだ。

結論:#スピル!が出たら、「!」で理由を読んで、じゃまなセルをどけようか。式を打ち直さなくても答えは戻ったんだよ

今日測った結果をまとめるとこうだよ。

① 答えを並べたい場所に「メモ」があると、#スピル!が出た。「メモ」は上書きされなかった
② 「阻害しているセルを選択」でE5(メモ)が選ばれ、消したら式を打ち直さずに答えが戻った
③ 理由の見出しは3種類見た。空白ではありません/結合されたセルがあります/大きすぎます
④ XLOOKUPで2列を返すと右にあふれ、となりに「備考」があると#スピル!
⑤ VLOOKUPの探す値をA:Aにすると「大きすぎます」。A2か@A:Aにしたら150が出た

#スピル!は、エクセルが壊れたんじゃなくて、止まって、メモを残したまま知らせてくれた印なんだ。あわてずに「!」を押して、見出しを読むところから始めようか。

よくある質問

Q1. #スピル!が出たら、式をまちがえたということですか

今日の測定では、式はそのままで直ったよ。E5の「メモ」を消しただけで、E3〜E6に答えが戻ったんだ。⇒ まずは「!」を押して、見出しが「空白ではありません」なら、式より先にじゃまなセルを探そうか。

Q2. 「阻害しているセルを選択」がうすい色で押せません

今日は、見出しが「スピル範囲が大きすぎます」のときにうすい色だったよ。VLOOKUPの探す値をA:Aと列ごと選んだときなんだ。探す値をA2にするか、@A:Aにしたら、150が出たよ。⇒ うすい色のときは、今日のように式の中で列ごと選んでいないかを見てみようか。

Q3. XLOOKUPにすれば、#スピル!は出ませんか

⚠️ 出たんだ。品名と単価の2列を返すXLOOKUPは、答えが右にあふれたよ。となりのG3に「備考」があると#スピル!、となりが空のF5では、消しゴムと60が並んだんだ。⇒ XLOOKUPで何列か返すときは、右どなりがあいているかを見ようか。

【本日のミッション:#スピル!が出たら、「!」を押してみようか】

  • ☐ #スピル!のセルをクリックして、横の「!」を押し、いちばん上の見出しを読んでみようか
  • ☐ 「空白ではありません」なら、「阻害しているセルを選択」で、じゃまなセルを見つけてみようか(消す前に中身を見てね)
  • ☐ XLOOKUPで何列か返すときは、右どなりの列があいているか見てみようか
  • ☐ (後輩A君へ)#スピル!は、エクセルが止まって知らせてくれた印なんだよ。あわてず見出しを読めば、どこをどければいいか分かるんだ

📚 AI活用シリーズ(おすすめ)

▼ シリーズの全記事一覧を見る

コメント

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