Excel スピル ``#SPILL!``エラーや豆知識

この記事は,Spreadsheets/Excel Advent Calendar 201911日目の記事です.

6日目に書いた記事は↓です.

kamocyc.hatenablog.com

スピルについていろいろ調べたら書ききれなくなったのでこの記事で追記します.

目次:

スピル関連のエラー

スピル機能に伴って,Excelに新しいエラー#SPILL!エラーができました. (Excel Onlineでは#スピル!と日本語で表示されるようです.)

これは,主にスピル先のセルに既に値が入っている時に発生するエラーです.

f:id:kamocyc:20191206184345p:plain

スピルが起こる範囲の一部であるB3セルに文字列が入っているため,エラーになっています. 本来スピルが起こる範囲が点線で囲まれていて,わかるようになっています.

デスクトップ版Excelでは,エラーで出てくるメニューに「阻害しているセルを選択」という項目が表示されます.

f:id:kamocyc:20191206184847p:plain

これを選択すると,スピルを阻害しているセルが選択されます. スピルを阻害しているセルの値を削除すると,即座にスピルが正常に行われます.

その他の#SPILL!エラーの原因

実は他にも#SPILL!エラーが起こる原因があります.

スピルのサイズが決まらない

再計算のたびに結果の配列のサイズが変わるなど,スピルのサイズが決まらない場合にも#SPILL!エラーが発生します.

f:id:kamocyc:20191206185540p:plain

この例では,乱数で返る配列のサイズが変わるので,エラーになっています.

公式のヘルプによると,通常RAND, RANDARRAY, RANDBETWEEN関数が関わる式でエラーが発生する可能性がありということです.(なお,OFFSET, INDIRECT, TODAY関数は揮発性だが計算のパスごとに異なる値は返さないので大丈夫とのこと)

ワークシートの端を超えている

スピルの範囲がワークシートの最大の行数・列数を超えていると#SPILL!エラーになります.(参考)

可能性としてあるのが,列全体を参照しているケースです.

f:id:kamocyc:20191206190711p:plain

B2セルには,すべて=A:Aと入力されていますが,スピルの範囲がB列のワークシート一番下のセルの1つ下のセルまで広がっているため,エラーになっています.

解決するには,以下の方法があります.

  1. =A2のように,単純にセル参照する
  2. =A2:A4のように,列全体でなくて明示的にセル範囲を指定する
  3. =@A:Aのように,暗黙的なインターセクション演算子を利用する(旧Excel=A:Aと同じ挙動になります)

テーブル内での使用

Excelテーブル内でスピルさせると,#SPILL!エラーになります.(参考)

f:id:kamocyc:20191206191432p:plain

なお,テーブルを参照した結果がスピルすることはできます. これはむしろMicrosoftがウリにしている使い方です.

f:id:kamocyc:20191206192130p:plain

メモリ不足

めったに無いと思いますが,メモリ不足になると#SPILL!エラーになります.(参考)

指定したセル範囲を小さくして試してくださいということです.

スピル範囲に結合セルがある

スピルする範囲に結合セルがあると,#SPILL!エラーになります.(参考)

f:id:kamocyc:20191206191800p:plain

この例では,B3:C3セルが結合されているので,エラーになっています.

エラーのメニューから「阻害しているセルを選択」を選んで,問題となっている結合セルにジャンプできます.

まあ,やはり結合セルは様々な面倒ごとの元なので可能な限り避けるべきですね.

#CALC!エラー

動的配列数式に伴って,#CALC!エラーというのも新しく登場したようです.

下記では,FILTER関数の結果が空の配列になっているため,#CALC!エラーが返っています.

f:id:kamocyc:20191211064525p:plain

公式のドキュメントは,下記にあります.

#CALC! errors in Excel - Office Support

ただ,あげられていた3つの例のうち,2019年12月19日現在,Excel Onlineでエラーが再現したのは空の配列だけだったので, #CALC!エラーは無くなっていく方向なのかもしれません.

ちなみに,他の例:

  • =MUNIT({1,2}) ➡ エラーにならなかった
  • =OFFSET(A1,0,0,{2,3})#VALUE!エラーになった

暗黙的なインターセクション演算子@について補足

@演算子の挙動(旧Excelの「共通部分の参照」の挙動)は以下のとおりです.

  • 単一の値が渡されたならば,その値をそのまま返す
  • セル範囲が渡されたならば,同じ行または同じ列のセルの値を返す
  • 配列が渡されたならば,一番左上の値を返す

配列に関して,例えば,3×3の配列定数に対して,新Excel@を適用すると,

f:id:kamocyc:20191205194233p:plain

スピルさせると,

f:id:kamocyc:20191205194259p:plain

となります.

Excelのこんな感じでちょっと複雑な配列数式は,

f:id:kamocyc:20191205195659p:plain

Excelではこんな感じになりました.*1

f:id:kamocyc:20191205195916p:plain

Excelで挙動の分かりづらかった共通部分の参照が,どこで起きているかわかりますね.

Microsoftのページでは以下のようなことも書いてありました.

  • なんで@という記号なの? ➡ テーブルのカラムを参照する時に,=[@Column1]みたいに書ける機能がもともとあったから.

  • @を取り除いてもいい? ➡ 単一の値を返すなら除ける.@を除いた数式を旧Excelで開くと配列数式になる.

  • どんな時に@が数式に付く? ➡ セル範囲や配列を返す関数に付く(例えばINDEX, OFFSET,セル範囲を返すユーザ定義関数).例外は,範囲や配列を受け取る関数(SUM, AVERAGEなど)に包まれているときなど」.

スピルのエッジケース

このページに,スピルの問題点について書いてありました.

それを受けて私が試してみた結果を記します.

@が旧Excel用に自動変換されない場合

前の記事で書いたように基本的に@演算子は旧Excelで自動的に取り除かれます.

f:id:kamocyc:20191206075557p:plain

f:id:kamocyc:20191206075619p:plain

数式の@が取り除かれていますね.

しかし,@の位置によっては,単純に取り除けないことがあります.

f:id:kamocyc:20191206075742p:plain

今度は,A1:A3+1に対して@演算子が適用されています.これは,A1:A3+1 = {1;2;3}+1 = {2;3;4}に対して@演算子が適用されて,配列の一番左上の要素が返されています.

この数式は,現在(2019年12月)旧Excelで開くと下記のようにエラーになります.

f:id:kamocyc:20191206080137p:plain

SINGLEという関数が現れましたが,そんな関数は無いので#NAME?エラーになりました.

SINGLE関数?

SINGLE関数というのは,@演算子の初期の名前です.@演算子が変換できない場合は昔の名残でSINGLE関数が現れています.

なお,新Excel=@(A1:A3+1)のような旧Excelに自動変換できない数式を入力すると,下記のような確認が表示されます.(Excel 365 デスクトップ版の場合)

f:id:kamocyc:20191206080933p:plain

なお「代わりにこのバリエーションを使用しますか」と提案された数式は,必ずしも元の式と同じ結果を返すわけではないので気をつけましょう.

ただ,このまま続行すると旧Excelでエラーになる数式になってしまうので,避ける方法があれば避けるべきでしょう.

Excelでも@演算子使いたいとき

Excelでも明示的に@演算子のように配列の一部を取り出したいときは,代わりにINDEX関数が使えそうです.

f:id:kamocyc:20191206081554p:plain

書式は以下のとおりです.

INDEX(対象範囲,行番号,列番号)

引数の指定の仕方にバリエーションがあります. また,@とは違って参照の行番号は自動的に設定されません.(@のように,「同じ行または列」とはならない)

数式の入力順で計算結果が変わる

先ほど紹介したページの中で次のような例があげられていました.

f:id:kamocyc:20191206082614p:plain

D4セルには=A1:A2が,C5セルには=A1:B1という式が入っています. ポイントは,2つの数式がスピルする範囲が重複していることです.

上の図では,最初にC5セルの式を,次にD4セルの式を入力した場合の結果です.

一方,数式の入力順を逆にすると下記のようになります.

f:id:kamocyc:20191206082849p:plain

#スピル!エラーになるセルが変わっています.どうやら,先に入力した数式を優先させる仕様のようです.

そもそもエラーにならないようにすればいいという話なのかもしれませんが,同じ数式なのに結果が異なるのは少し気持ち悪いです.

まとめ

スピルの#SPILL!エラーやその他の細かい挙動について書きました.

使いこなせると便利な機能になりそうです.

*1:コメントがホバーで表示できないので,横のセルに書いています.