Excel スピル ``#SPILL!``エラー
Excelの#SPILL!
エラーについて紹介します.
#SPILL!
エラー
Excelにスピル機能が導入されたことにより,#SPILL!
エラーを見た人もいると思います.
なお,Excel Onlineでは#スピル!
と日本語で表示されるようです.
(他,参考ページ: https://support.office.com/en-us/article/-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023)
これはスピル機能に伴って導入された新しい種類のエラーで,主にスピル先のセルに既に値が入っている時に発生するエラーです.
例
スピルが起こる範囲の一部であるB3セルに文字列が入っているため,エラーになっています. 本来スピルが起こる範囲が点線で囲まれていて,わかるようになっています.
デスクトップ版Excelでは,エラーで出てくるメニューに「阻害しているセルを選択」という項目が表示されます.
これを選択すると,スピルを阻害しているセルが選択されます. スピルを阻害しているセルの値を削除すると,即座にスピルが正常に行われます.
その他の#SPILL!
エラーの原因
実は他にも#SPILL!
エラーが起こる原因があります.
スピルのサイズが決まらない
再計算のたびに結果の配列のサイズが変わるなど,スピルのサイズが決まらない場合にも#SPILL!
エラーが発生します.
この例では,乱数で返る配列のサイズが変わるので,エラーになっています.
公式のヘルプによると,通常RAND, RANDARRAY, RANDBETWEEN関数が関わる式でエラーが発生する可能性がありということです.(なお,OFFSET, INDIRECT, TODAY関数は揮発性だが計算のパスごとに異なる値は返さないので大丈夫とのこと)
ワークシートの端を超えている
スピルの範囲がワークシートの最大の行数・列数を超えていると#SPILL!
エラーになります.(参考)
可能性としてあるのが,列全体を参照しているケースです.
B2セルには,すべて=A:A
と入力されていますが,スピルの範囲がB列のワークシート一番下のセルの1つ下のセルまで広がっているため,エラーになっています.
解決するには,以下の方法があります.
=A2
のように,単純にセル参照する=A2:A4
のように,列全体でなくて明示的にセル範囲を指定する=@A:A
のように,暗黙的なインターセクション演算子を利用する(旧Excelの=A:A
と同じ挙動になります)
テーブル内での使用
Excelのテーブル内でスピルさせると,#SPILL!
エラーになります.(参考)
なお,テーブルを参照した結果がスピルすることはできます. これはむしろMicrosoftがウリにしている使い方です.
メモリ不足
めったに無いと思いますが,メモリ不足になると#SPILL!
エラーになります.(参考)
指定したセル範囲を小さくして試してくださいということです.
スピル範囲に結合セルがある
スピルする範囲に結合セルがあると,#SPILL!
エラーになります.(参考)
この例では,B3:C3セルが結合されているので,エラーになっています.
エラーのメニューから「阻害しているセルを選択」を選んで,問題となっている結合セルにジャンプできます.
まあ,やはり結合セルは様々な面倒ごとの元なので可能な限り避けるべきですね.
#CALC!
エラー
動的配列数式に伴って,#CALC!
エラーというのも新しく登場したようです.
下記では,FILTER関数の結果が空の配列になっているため,#CALC!
エラーが返っています.
公式のドキュメントは,下記にあります.
#CALC! errors in Excel - Office Support
ただ,あげられていた3つの例のうち,2019年12月19日現在,Excel Onlineでエラーが再現したのは空の配列だけだったので,
#CALC!
エラーは無くなっていく方向なのかもしれません.
ちなみに,他の例:
=MUNIT({1,2})
➡ エラーにならなかった=OFFSET(A1,0,0,{2,3})
➡#VALUE!
エラーになった
まとめ
スピルの#SPILL!
エラーやその他の細かい挙動について書きました.
使いこなせると便利な機能になりそうです.
関連
この記事は,元々はSpreadsheets/Excel Advent Calendar 201911日目の記事です. スピルについていろいろ調べたら書ききれなくなったのでこの記事で追記しました.
6日目に書いた記事は↓です.
暗黙のインターセクション演算子や,スピルのエッジケースについての記事はこちらに分けました.