スピルについて2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。スピルとは何か、その概要について説明します。以下で、「従来」または「旧」と呼んでいるのはスピルしないエクセルを指しています。スピルとはスピルを説明するには、まず配列についての説明が必要になります。従来のエクセルでの「配列数式」と「共通部分の参照」について説明します。Enterで数式を一括入力することで、{=数式}のように{}で囲まれた配列数式となります。この配列数式はCSEと略されます。数式は{=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)とは、こぼれる、あふれる、と言うような意味です。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。スピルは動的配列数式とも呼ばれます。スピルは数式の結果の複数の値が隣接するセルに自動的に出力されます。複数の値を返す配列数式を該当セル範囲の先頭(左上セル)に入力すると、結果の複数の値が入力したセルからこぼれ出して隣接するセルに出力されます。数式をEnter入力することで、該当セル範囲(スピルする範囲)に結果が出力されます。スピルする範囲は、数式の結果データの縦横(行列)の大きさによって決まります。したがって、数式が変更されれはスピル範囲も変わりますし、数式が参照しているセル値によっても大きさが動的に変化します。フィルターは定義した条件に基づいたデータ範囲です。範囲または配列の内容を並べ替えます。範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。一覧表または範囲内から重複データを削除した一覧を返します。0から1までのランダムな数値の配列を返します。1、2、3、4など、配列内の連続した数値の一覧を生成します。範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。一致を返すことができます。配列またはセル範囲内の項目の相対的な位置を返します。数式の結果が複数の値を返すものはスピルになります。=A1:A3=A1:C1=A2:A10*B1:J1複数の値(配列)を返す数式はスピルします!従来のスピルしないエクセルで開いたとき=A1:A3この数式を従来のエクセルで開いたときは、{=A1:A3}このように配列数式に変更されます。スピルするエクセルで開き直すと、配列数式ではなく元のスピル数式で表示されます。従来のスピルしないエクセルで配列数式として入れ直してしまうと、スピルするエクセルで開いた時にも配列数式のままとなります。・配列数式(CSE)で入力した数式は新旧どちらでも配列数式ゴーストスピルによってこぼれだした先のセルを選択すると、数式が薄く表示されます。上記ではD2およびD3セルを選択して数式バーをみると薄くなっているのが分かるはずです。これはゴーストと呼ばれています。このゴーストのセルを編集状態にすると、数式も値も何も入っていないことが確認できます。このゴーストのセルに数式や数値・文字列を入れると、スピル数式を入れたセルが#SPILL!のエラーとなります。スピル範囲での独特な挙動についてスピル範囲(つまりスピルでこぼれだした範囲)では、他のセル範囲とは違う動きになるものがあります。値貼り付けスピル範囲に文字数値をいれると#SPILL!となります。スピル範囲の一部を値貼り付けするとどうなるか・・・スピル範囲の一部を値貼り付けすることはできません。元のスピルした状態に強制的に戻されてしまいます。スピル範囲全体でしか値貼り付けできません。範囲の選択ショートカットスピル範囲は枠線で囲まれて分かり易くなっていますが、このスピル範囲を選択したい場合のショートカットがあります。Spaceスピル範囲以外では連続セル範囲が選択されますが、スピル範囲内においてはスピル範囲だけが選択されます。スピルのエラー表示#SPILL!・スピル範囲が空でない場合に発生します。・結合されたセルにスピルすることはできません。・シートの端を超えてスピルすることはできません。・テーブル内でスピルさせることはできません。#CALC!公式ドキュメントには、Excelの計算エンジンが配列で指定されていない計算エラーを検出した場合に発生します。そのほか、配列の配列や空の配列等々が記載されています。実際に発生しうる簡単な例としては、FILTER関数が空の場合になります。スピル範囲演算子スピルによってこぼれる範囲(スピル範囲)は数式およびデータにより変化します。このスピル範囲の全体を他の数式で参照する場合は、スピルの数式を入れたセル番地に#を付けて参照できます。この#を「スピル範囲演算子」と呼びます。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セルにいれているような場合です。スピルするエクセルでは、それでも正しく計算されますが、行がずれているので「共通部分の参照」になっていないため従来のエクセルではエラヘとなります。このような使い方は避けたほうが良いと思います。新しく追加された関数を使った場合に、旧エクセルでエラーになるのは仕方ありませんし今までも常にそうでした。関数ではないので、特に意識せずにスピルを使ってしまう事はありえます。旧エクセルでは配列で表示されるため気づかずに運用してしまう事もこれからは発生しうる思われます。インターセクション演算子@は、「共通部分の参照」の使い方をしている限りは問題なく機能しているようです。スピル範囲演算子#は、スピルしない旧エクセルでは正しく変換されませんし使う事が出来ません。き書式、入力規則、これらで#演算子を使ってしまうと、従来のエクセルで開いたときに原因を特定するのが難しくなってしまうと思われます。この点は特に気を付けておくべきでしょう。Next)|VBA入門8.セルに文字を入れるとは(Range,Value)|VBA入門9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説10.ひらがな⇔カタカナの変換|エクセル基本操作ホームエクセル全般エクセル入門スピルについてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。GASスプレッドシート入門スプレッドシート応用GAS入門GAS応用ホームエクセル全般エクセル入門スピルについてこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。