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

Excelの初心者向け入門解説
最終更新日:2020-02-22

数式.スピルについて


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


2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。
スピルとは何か、その概要について説明します。

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

スピルとは

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

従来のエクセルの挙動

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

Excel エクセル スピル

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

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

Excel エクセル スピル

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

Excel エクセル スピル


スピルとは

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

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

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

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

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


スピルの数式例

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

=A1:A3
Excel エクセル スピル

=A1:C1
Excel エクセル スピル

=A2:A10*B1:J1
Excel エクセル スピル


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

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

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

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

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

ゴースト

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

Excel エクセル スピル

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

Excel エクセル スピル

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

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

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

値貼り付け

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

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

Excel エクセル スピル

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

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

Ctrl + A

または
Ctrl + Shift + Space

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

スピルのエラー表示

#SPILL!

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

Excel エクセル スピル

Excel エクセル スピル

Excel エクセル スピル


#CALC!

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

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

Excel エクセル スピル

スピル範囲演算子

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

Excel エクセル スピル

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

Excel エクセル スピル


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

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

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

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

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

Excel エクセル スピル

Excel エクセル スピル


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

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

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

Excel エクセル スピル

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

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

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

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

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



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

MOD関数(除算したときの剰余)
MROUND関数、CEILING関数、FLOOR関数(指定値の倍数)
RAND関数、RANDBETWEEN関数(乱数)
CELL関数(セルの書式、位置、内容の情報)
CONCAT関数(複数の範囲や文字列を結合)
TEXTJOIN関数(区切り記号を指定できる文字列結合)
MAXIFS関数、MINIFS関数(条件付き最大値最小値)
IFS関数(複数条件)
SWITCH関数(複数値での切替)
スピルについて
FILTER関数(範囲をフィルター処理)


新着記事NEW ・・・新着記事一覧を見る

簡易音楽プレーヤーの作成|ユーザーフォーム入門(2月22日)
VBAで音楽再生するクラスを作成|VBA技術解説(2月22日)
セル結合なんて絶対に許さないんだからね|VBAサンプル集(2月17日)
セル結合/解除でセル値を退避/回復|VBAサンプル集(2月16日)
FILTER関数(範囲をフィルター処理)|エクセル入門(2月11日)
「VBAで導関数を求めよ」ツイッターのお題をやってみた|エクセル雑感(2月9日)
Property {Get|Let|Set} ステートメント|VBA入門(2月9日)
フィボナッチ数列(再帰呼び出し)|VBA技術解説(2月8日)
エクセルで連立方程式を解く(MINVERSE,MMULT)|エクセル雑感(2月7日)
スピルって速いの?スピルの速度について|VBA技術解説(2月4日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作




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


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



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