スピルについて｜エクセル入門

公開日：2020-02-03　最終更新日：2022-10-25

スピルについて

2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。
この革新的なスピルとは何か、その概要について説明します。

こちらも参考にしてください。
出題と解答形式でスピルについて徹底解説しています。
スピルとは：スピル基礎から応用までの問題集

・目次・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2 ・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編・サムはイフと恋をした編・君はヤマダなのかい編・毎月月末は彼女とデートなんだよ編・SEQUENCE編1 ・SEQUENCE編2 ・FILTER編1 ・FILTER編2 ・SORTBY編1 ・SORTBY編2 ・UNIQUE（そろそろLETも）編・XLOOKUP編1 ・XLOOKUP編2 ・ここまでの総合演習1 ・ここまでの総合演習2 ・ここまでの総合演習3 ・ここまでの総合演習4 ・ここまでの総合演習5 ・ここまでの総合演習6

以下で、「従来」または「旧」と呼んでいるのはスピルしないエクセルを指しています。

スピルとは

スピルを説明するには、まず配列についての説明が必要になります。
まずは、従来のエクセルでの「配列数式」と「共通部分の参照」について説明します。

従来のエクセルの挙動

配列数式

複数の値を返す数式（配列数式）は結果を表示する全てのセルを選択し、
Ctrl + Shift + Enterで数式を一括入力することで、{=数式}のように{}で囲まれた配列数式となります。
この配列数式はCSEと略されます。
例えば、
B1:B3を選択し、=A1:A3をCtrl + Shift + Enterで入力すると、
数式は{=A1:A3}となり、B1:B3はA1:A3を参照するようになります。

共通部分の参照

暗黙的に共通部分を参照する機能があります。
これは、同一行または同一列の値のみが返される機能です。

B1セルに=A1:A3と入力すると、A1セルが参照されます。
B2セルに=A1:A3と入力すると、A2セルが参照されます。
B3セルに=A1:A3と入力すると、A3セルが参照されます。

A2セルに=A1:C1と入力すると、A1セルが参照されます。
B2セルに=A1:C1と入力すると、B1セルが参照されます。
C2セルに=A1:C1と入力すると、C1セルが参照されます。

スピルとは

スピル(spill)とは、こぼれる、あふれる、と言うような意味です。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。

スピルは動的配列数式とも呼ばれます。
スピルは数式の結果の複数の値が隣接するセルに自動的に出力されます。
複数の値を返す配列数式を該当セル範囲の先頭（左上セル）に入力すると、結果の複数の値が入力したセルからこぼれ出して隣接するセルに出力されます。

従来なら配列数式としてCSE(Ctrl+Shift+Enter)で入力しなければならなかったものが、
数式をEnter入力することで、該当セル範囲（スピルする範囲）に結果が出力されます。
スピルする範囲は、数式の結果データの縦横（行列）の大きさによって決まります。
したがって、数式が変更されれはスピル範囲も変わりますし、数式が参照しているセル値によっても大きさが動的に変化します。

スピルによって新しく追加された関数

関数名	説明
FILTER	フィルターは定義した条件に基づいたデータ範囲です。
SORT	範囲または配列の内容を並べ替えます。
SORTBY	範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
UNIQUE	一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY	0から1までのランダムな数値の配列を返します。
SEQUENCE	1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP	範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。
XMATCH	配列またはセル範囲内の項目の相対的な位置を返します。

スピルの数式例

数式の結果が複数の値を返すものはスピルになります。

=A1:A3

=A1:C1

=A2:A10*B1:J1

複数の値(配列)を返す数式はスピルします！

従来のスピルしないエクセルで開いたとき

=A1:A3
この数式を従来のエクセルで開いたときは、
{=A1:A3}
このように配列数式に変更されます。
スピルするエクセルで開き直すと、配列数式ではなく元のスピル数式で表示されます。

ただし、従来のスピルしないエクセルで配列数式として入れ直してしまうと、スピルするエクセルで開いた時にも配列数式のままとなります。

・スピル数式をスピルしないエクセルで開くと配列数式で表示
・配列数式(CSE)で入力した数式は新旧どちらでも配列数式

ゴースト

スピルによってこぼれだした先のセルを選択すると、数式が薄く表示されます。

上記ではD2およびD3セルを選択して数式バーをみると薄くなっているのが分かるはずです。
これはゴーストと呼ばれています。
そして、このゴーストのセルを編集状態にすると、数式も値も何も入っていないことが確認できます。

このゴーストのセルに数式や数値・文字列を入れると、スピル数式を入れたセルが「#スピル!」のエラーとなります。

スピル範囲での独特な挙動について

スピル範囲（つまりスピルでこぼれだした範囲）では、他のセル範囲とは違う動きになるものがあります。

値貼り付け

スピル範囲に文字数値をいれると「#スピル!」となります。
では、スピル範囲の一部を値貼り付けするとどうなるか・・・

スピル範囲の一部を値貼り付けすることはできません。
元のスピルした状態に強制的に戻されてしまいます。
スピル範囲全体でしか値貼り付けできません。

範囲の選択ショートカット

スピル範囲は枠線で囲まれて分かり易くなっていますが、このスピル範囲を選択したい場合のショートカットがあります。

Ctrl + A
または
Ctrl + Shift + Space

スピル範囲以外では連続セル範囲が選択されますが、スピル範囲内においてはスピル範囲だけが選択されます。

スピルのエラー表示

「#スピル!」・・・以前は「#SPILL!」でした。

・スピル範囲が空でない場合に発生します。
・結合されたセルにスピルすることはできません。
・シートの端を超えてスピルすることはできません。
・テーブル内でスピルさせることはできません。

#CALC!

公式ドキュメントには、
Excelの計算エンジンが配列で指定されていない計算エラーを検出した場合に発生します。
そのほか、配列の配列や空の配列等々が記載されています。

実際に発生しうる簡単な例としては、FILTER関数が空の場合になります。

スピル範囲演算子

スピルによってこぼれる範囲（スピル範囲）は数式およびデータにより変化します。
このスピル範囲の全体を他の数式で参照する場合は、スピルの数式を入れたセル番地に#を付けて参照できます。
この#を「スピル範囲演算子」と呼びます。

C1セルにスピル数式が入っています。
スピル範囲を参照するには、数式において C1# とします。

従来のスピルしないエクセルで開いたとき(_xlfn.ANCHORARRAY)

=SUM(C1#)
この数式を従来のエクセルで開いたときは、
=SUM(_xlfn.ANCHORARRAY(C1))
このように変更されて表示されます。

値はいったんは表示されますが、
当該セルが再計算されると#NAME?のエラーとなります。
かつ、この再計算を元に戻して値を表示し直すこともできません。
ただし、スピルするエクセルで開き直せば正しく再計算されます。

暗黙的なインターセクション演算子

従来のエクセルでは、「共通部分の参照」として=A1:A3のような数式を入れることが出来ました。
しかし、スピルするエクセルでは、この数式はスピルしてしまいます。
つまり従来では、「共通部分の参照」として同一行や同一列を参照していたものが、勝手にスピルしてしまいます。
そこで、従来のように「共通部分の参照」としてセル範囲指定を使いたい場合は、セル範囲の先頭に@を付けます。
つまり、@をつけることで従来通りの動作となりスピルしなくなります。
この@を「暗黙的なインターセクション演算子」と呼びます。

従来のスピルしないエクセルで開いたとき

=@A1:A3
この数式を従来のエクセルで開いたときは、
=A1:A3
このように変更されて表示されます。
そして、従来のエクセルで、
=A1:A3
と入力してあるものは、
スピルするエクセルでは、
=@A1:A3
このように変更されます。
つまり、
従来の「共通部分の参照」と@演算子は相互変換されるという事です。

従来のエクセルと互換性が無い使い方の警告

このような警告メッセージが表示されます。
これは、
=@(A1:A3+B1:B3)
このように数式をいれています。
@が無ければスピルします。
それをスピルさせないようにしたものです。
このような場合、ほとんどの場合は「はい」で良いはずです。

「はい」で意図した結果にならない場合は、
従来の「共通部分の参照」では使えない使い方になっていると思われます。
簡単な例としては、上記数式を別の行、例えばD4セルにいれているような場合です。
スピルするエクセルでは、それでも正しく計算されますが、行がずれているので「共通部分の参照」になっていないため従来のエクセルではエラヘとなります。
さすがに、このような使い方は避けたほうが良いと思います。

従来のスピルしないエクセルとの互換性についての注意点

新しく追加された関数を使った場合に、旧エクセルでエラーになるのは仕方ありませんし今までも常にそうでした。
しかし、問題はスピルです。
関数ではないので、特に意識せずにスピルを使ってしまう事はありえます。
そして、旧エクセルでは配列で表示されるため気づかずに運用してしまう事もこれからは発生しうる思われます。

インターセクション演算子@は、「共通部分の参照」の使い方をしている限りは問題なく機能しているようです。
しかし、
スピル範囲演算子#は、スピルしない旧エクセルでは正しく変換されませんし使う事が出来ません。
セルの数式に使っていれば、_xlfn.ANCHORARRAYが見て取れてすぐに気が付きますが、
名前定義、条件付き書式、入力規則、これらで#演算子を使ってしまうと、従来のエクセルで開いたときに原因を特定するのが難しくなってしまうと思われます。
この点は特に気を付けておくべきでしょう。

スピル関連記事

スピルとは：スピル基礎から応用までの問題集 ・・・解答は別ページになっています。

スピルでVBAの何が変わったか

・Rangeオブジェクトに追加されたプロパティ・セルに数式を設定する場合・1次元配列を返すユーザー定義関数・2次元配列を返すユーザー定義関数・JAG配列を返すユーザー定義関数・スピルのVBAでの活用について

スピルって速いの?スピルの速度について

・速度検証に使う関数・速度検証に使うシート・スピルの速度を検証するVBAコード・スピルの速度を検証した結果・スピルの速度の最後に

スピルに対応したXSPLITユーザー定義関数（文字区切り）

・区切り位置ウィザード・ワークシートの関数で文字区切りする場合・ユーザー定義関数のVBAｺｰﾄﾞ・XSPLIT関数の使用例・ユーザー定義関数の実践使用例

スピルを使って日付の一覧を出力｜エクセル練習問題

・エクセル練習問題・シンキングタイム・エクセル練習問題解答

スピルを使って文字列を1セルずつに分解｜エクセル練習問題

・エクセル練習問題・シンキングタイム・エクセル練習問題解答

同じテーマ「エクセル入門」の記事

IFS関数（複数条件）
SWITCH関数（複数値での切替）
XLOOKUP関数とスピル入門
スピルについて
FILTER関数（範囲をフィルター処理）
SORT関数、SORTBY関数（範囲を並べ替え）
UNIQUE関数（一意の値）
RANDARRAY関数（ランダム数値）
SEQUENCE関数（連続数値）
XLOOKUP関数（VLOOKUP関数を拡張した新関数）
XMATCH関数（MATCH関数を拡張した新関数）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.ひらがな⇔カタカナの変換｜エクセル基本操作
6.RangeとCellsの使い方｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.FILTER関数（範囲をフィルター処理）｜エクセル入門
10.条件分岐（Select Case）｜VBA入門

ホーム
エクセル全般
エクセル入門
スピルについて

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

エクセル入門
スピルについて