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

最終更新日：2020-06-02

スピルについて

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

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

スピルとは

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

従来のエクセルの挙動

配列数式

複数の値を返す数式（配列数式）は結果を表示する全てのセルを選択し、
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セルを選択して数式バーをみると薄くなっているのが分かるはずです。
これはゴーストと呼ばれています。
そして、このゴーストのセルを編集状態にすると、数式も値も何も入っていないことが確認できます。

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

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

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

値貼り付け

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

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

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

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

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の何が変わったか

Office365にスピルが登場し、2020年1月にはXLOOKUPもリリリースされ、ますますエクセルが便利になってきています。スピルは、これまでにないくらい大幅な機能変更と言えるでしょう。スピルの一般的な説明については以下を参照してください。

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

Office365を導入する企業や個人で契約する人もかなり増えてきているようです。自然とスピルや新関数を使う事も増えてくるでしょう。今まで範囲内に同じ数式を入れるときは、絶対参照・相対参照を組み合わせて入れなければならず、これを難しく感じていた人も多いでしょう。

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

エクセルにスピルが登場して、VBAのユーザー定義関数の用途も広がってきていると思います。そこで、文字列を指定した記号・文字列で区切て、セルに出力するユーザー定義関数を作ってみましょう。VBAにはSplit関数がありますが、これをワークシート関数として使えるようにしてみましょうという事です。

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

エクセル挑戦問題 2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。スピルを上手に使うと、今まで多くの作業列を使う必要があったり、とても難しい配列数式にしなければならなかったものが、かなり簡略化された数式で実現することができます。

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

エクセル挑戦問題 2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。スピルを上手に使うと、今まで多くの作業列を使う必要があったものが、かなり簡略化された数式で実現することができます。スピルの詳細については、以下を参照してください。

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

IFS関数（複数条件）
SWITCH関数（複数値での切替）
XLOOKUP関数とスピル入門
スピルについて
FILTER関数（範囲をフィルター処理）
SORT関数、SORTBY関数（範囲を並べ替え）
UNIQUE関数（一意の値）
RANDARRAY関数（ランダム数値）
SEQUENCE関数（連続数値）
XLOOKUP関数（範囲を検索し一致する対応項目を返す）
XMATCH関数（範囲から値を検索し一致する相対位置）

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.とにかく書いてみよう（Sub,End Sub）｜VBA入門
10.繰り返し処理（Do Loop）｜VBA入門

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

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

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

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