こんにちは、HARUです！

今回は、Excelの「スピル」について解説します。

スピルは2018年9月に、Microsoft365向けに実装された「動的配列数式」で発動する機能です。

使えるバージョンは限られますが、対象のバージョンの普及率は拡大傾向にあります。
（以下は当チャンネルの視聴者様にお勤め先のExcel(Office)バージョンについてアンケートをとった結果です。n=1500以上）

これまでの表計算の常識が覆り、これからのExcelのスタンダードになっていく超画期的なアイテムですので、その挙動と活用術を今のうちにおさえておきましょう。

ぜひ最後までご一読ください！

スピルの基本動作とスピル範囲演算子

スピルの挙動

A1セルからA5セルに、1から5の数値が直接入力されています。
これと同じ値を別のセルに返すには、=A1と入力します。

この段階では数式を入力していないセルには何も入力されません。

そのためこれまでは、最初に入力した数式をオートフィルで引き延ばしたり、下へコピー＆ペーストしたり、VBAで一括代入したりする必要がありました。

対して、A1セルからA5セルをまとめて参照すると、数式を入力したB1セル以外にも、参照した範囲と同じ行数だけ、値が表示されます。

これがスピルです。

スピル（spill）は「あふれる」「こぼれる」「流れ出る」を意味する英語です。
先頭に入力した数式が冒頭に触れた「動的配列数式」で、これにより別のセルへあふれるように結果が返されることを「スピル」というのです。

ここで数式バーを見てみると、数式を入力したセルはいつも通りの色味となっていますが、スピル範囲のその他のセルは薄い字で表現されています。

これは「ゴースト」といって、先頭の動的配列数式によって生成されたデータではあるけれども、そのセル自体には何も入力されていないことを示しています。

ゴーストセルを編集モードにしても何も表示されず、DeleteやBackspaceを押しても全体に影響はありません。

スピル範囲の内容が更新または削除されるのは、先頭の数式を触ったときだけです。

ただし、スピル対象の範囲に何らかのデータが入力されていると、数式を入力したセルにスピルエラーが返ります。
（下図はB2セルに"-"(ハイフン)が入力されている状態）

スピルの行く手を阻むことのないように、対象のセルはあらかじめ空白にしておきましょう。

ちなみに、ゴーストセル自体には何も入っていませんが、スピルで表示された内容を別の場所で参照すると、そのデータが取得できます。
範囲選択すれば、ステータスバーで合計や平均、データの個数もわかります。
（下図はC3セルでB3セルを参照した状態）

スピル範囲演算子

元のデータとスピルで取得した範囲をそれぞれSUM関数で合算してみます。

元のデータは各セルに値が入っているので、SUM関数で範囲選択すると、":"(コロン)でつながれたよく見る参照形式になります。

対してスピルで生成された範囲をすべて参照すると、先頭の数式が入力されたセル番地に"#"(ハッシュ)記号がつきます。

このハッシュを「スピル範囲演算子」といい、数式内でスピル範囲をすべて選択した瞬間、この表記に変換されます。

このスピル範囲演算子は直接記述することでも機能します。
別の場所に"=B1#"と入力すれば、B1セルを先頭に生成されたスピル範囲が選択されます。

なお、ゴーストセルのセル番地に#を付与しても、スピル範囲全体を参照することはできません。

あくまでスピルの起点となっている動的配列数式が入力されたセル番地を指示することで効力を発揮する演算子です。

一度おさらいします。

①セル範囲（配列）を参照する「動的配列数式」
②それを先頭に発動する「スピル」
③先頭以外のセルに表示される「ゴースト」データ
④スピル対象範囲にデータが存在することで発生する「スピルエラー」
⑤スピル範囲を"#"で指示する「スピル範囲演算子」

この5つをスピルに関する基本知識としておさえておきましょう。
ここから、具体的なユースケースを見ていきます。

参照のしくみ

１つ目のサンプルは商品毎の単価と発注数量をかけて金額を求める表です。

これまでは、単価のセルと数量のセルをアスタリスクでつなげて値を求めてから、この数式を下へコピーする、といったアクションをしていました。

コピーが不要

スピルの場合は、単価の範囲と数量の範囲をまとめて乗じます。

これにより、同じ行数同士をかけ算した結果が一気に表示されます。

※動的配列数式で特定範囲を参照したとき、そのセル情報のすべてを取得するのではなく、入力されたデータのみを表示します。
今回はあらかじめD列以降の範囲を桁区切りの表示形式に設定しておきましたが、実際にはスピル範囲の表示形式を再設定する必要があることを覚えておきましょう。

さて、計算対象となる複数の参照範囲は、同じ行数であることがポイントです。

たとえば単価の範囲は5行分、数量の範囲は4行分選択すると、単価の5行目の演算対象が見つからず#N/Aエラーが返ります。

対して、開始位置を１つズラして参照しても、行数が同じであれば計算は成り立ちます。

先頭は500×20で10000、2行目は600×15で9000、といったように参照範囲内の上から順に演算していきます。

こんな参照の仕方はめったにしませんが、同じ行数、または列数が選択できていれば正常に機能することだけ、参考までにおさえておきましょう。

絶対参照が不要

続いて消費税を求めます。

従来は、それぞれもとめた金額に税率をかけ、この数式を他の範囲へコピーすることを考慮して、共通の参照元は絶対参照にしていました。

対して、スピル範囲となっている税別金額はD2#ですべて選択できる上、かけ合わせるセルを１つだけ選択することで、絶対参照にせずともすべての金額に共通の値「10％」が乗じられるのです。

先ほど、単価と数量で異なる行数を参照したときに、５行目だけは計算対象が見つからずエラーとなりましたよね。

単価5つに対して数量3つであっても、5つに対して2つでかけてもエラーが返ります。

ただ、単一セルを参照したときはそこにスピルが発動せず、すべての単価に共通の値が乗じられます。

一番上の6,000は300×20の結果であり、続けて500×20で10,000、600×20で12,000といったイメージで、共通の値「20」が計算対象となり替わるのです。

最後に税込金額です。
税別金額と消費税のたし算ですので、=D2#+E2#だけでOKです。

非常に簡単ですよね！

スピルでマトリクス表を生成

次のサンプルは、日別、商品別、向け先別の出荷量が無造作に蓄積された表です。

今回はこの表から、縦に「日付」、横に「向け先」を並べて、商品に限らず各向け先にどれだけの出荷量があったかを日ごとに求めていきます。

欄外にマトリックス表を構築する際に、1つずつ日付や都市名を入力していっても良いのですが、必要な情報は元のデータに揃っています。

これらを見出しとして並べることができたら便利ですよね。
そこで、このスピル機能が実装されたあとにリリースされた関数を１つご紹介します。

それがUNIQUE関数です。
ダブりデータが多く存在する範囲を参照すると、複数回登場するデータを重複なく取得できます。

※前述の桁区切り表示と同じく、日付の場合も動的配列数式で参照すると表示形式は引き継がれず、シリアル値が返ります。
今回はあらかじめF列に日付の表示形式を設定しておきましたが、実際には表示形式を再設定する必要があります。

UNIQUE関数の詳細はまた別の記事で解説します。

次に、向け先を横に並べます。
UNIQUE関数で向け先の範囲を選択します。

対象の出荷先が取り出せましたが、縦にスピルしています。
これを横に変換するために、参照範囲に行と列を入れ替えるTRANSPOSE関数にネストします。

これにより、縦に日付、横に向け先を並べたマトリクス表ができます。

複合参照が不要

さて、今回のように日付・向け先といった複数条件にマッチするデータの合計を求めるには、SUMIFS関数を用います。

▶従来のステップ
①SUMIFS関数を挿入する。
②第１引数「合計対象範囲」に数量の範囲を選択する。
（数式を縦横にコピーするため絶対参照にしておく）
③第２引数「条件範囲１」に日付の範囲を選択する。
（絶対参照にしておく）
④第３引数「条件１」に日付の見出しを選択する。
（数式を下へコピーするに従い参照をスライドさせたいが、右へコピーするときは動いてほしくないため、列だけ固定する複合参照にする）
⑤第４引数「条件範囲２」に向け先の範囲を選択する。
（絶対参照にしておく）
⑥第５引数「条件２」に向け先の見出しを選択する。
（数式を右へコピーするに従い参照をスライドさせたいが、下へコピーするときは動いてほしくないため、行だけ固定する複合参照にする）

⑦対象範囲を選択し、下方向、右方向へコピーする。

これと同じことを、スピルでやってみます。

▶スピルの場合
①SUMIFS関数を挿入する。
②第１引数「合計対象範囲」は数量の範囲を選択する。
（コピーする必要がないため相対参照のままとする）
③第２引数「条件範囲１」に日付の範囲を選択する。
（相対参照のまま）
④第３引数「条件１」は日付の見出しをすべて選択するか、直接"F2#"と記述しスピル範囲を参照する。
⑤第４引数「条件範囲２」に向け先の範囲を選択する。
（相対参照のまま）
⑥第５引数「条件２」は向け先の見出しをすべて選択するか、直接"G1#"と記述しスピル範囲を参照する。

これにより、先ほどと同じ結果がスピルで返されます。

これくらいのデータ分析であればピボットテーブルでもできますが、これまで関数で処理していたケースで、他の範囲へのコピーや参照形式を意識しなくて済むのはありがたいですよね！

スピル参照演算子でリスト化

スピル範囲の参照は、ドロップダウンリストにも活用できます。

すべての向け先を並べるのではなく、リストから選択した向け先の出荷量を都度表示させる構成にしたいとなったとします。

キーボードの[Alt]→[D]→[L]を順に押し、「データの入力規則」を開きます。
入力値の種類から「リスト」を選択し、元の値に向け先見出しの先頭である"=G1"に"#"をつけて決定します。

これにより、G1セルからM1セルに生成されているスピル範囲のデータをリスト化できます。

前述のスピル範囲を参照した手順でSUMIFS関数を構築していき、最後の「条件２」のみこのドロップダウンリストを参照します。

リストから選択した都市名に応じて、向け先別のデータを単体で返すことができます。

スピルはドロップダウンリストでも効力を発揮するのです。
こちらも大変有効ですよね！

スピル範囲内の移動

１つ１つのセルに数式を入力しているケースと異なり、スピルは個々のセルがどの値を参照しているかがたどりにくいという課題があります。

▶従来の参照形式ですべてのセルへ数式をコピーした場合

▶スピルの場合

共有先のExcelがスピルを使えない可能性も考慮し、実務ではシーンに応じて使い分けていきましょう。

ちなみにスピル機能が実装されているバージョンでスピルを発動させたくないときは、動的配列数式の先頭やスピル範囲演算子の前に＠マークをつけます。

使う機会はほとんどありませんが、参考までにお伝えしておきます。

また、起点となる先頭の動的配列数式を素早くメンテナンスするために、スピル範囲内の移動方法についてもおさえましょう。

通常の表であれば、[Ctrl]+[A]で選択したときと、[Ctrl]+[Shift]+[:(*)]で選択したときの差は、アクティブセルが配置される位置がその操作を行ったセルに留まるか、アクティブセル領域内の先頭になるか、の違いだけでした。

▶C7セルで[Ctrl]+[A]を実行した場合

▶C7セルで[Ctrl]+[Shift]+[:(*)]を実行した場合

スピル範囲の場合、[Ctrl]+[A]を押したときの選択領域がスピル範囲で一度止まります。

次に、②隣接・接触しているデータ範囲、→③最後にワークシート全体、と段階を踏んで選択していきます。

[Ctrl]+[A]でスピル範囲を選択したあと、選択範囲内の４隅をまわる[Ctrl]+[.]を押すことで、実際に数式が入力されているセルにジャンプできます。

スピル範囲の各セルが、どの参照元から演算・集計されたデータなのかはここでチェックしましょう。

まとめ

以上のように今回は、Excelの「スピル」について解説しました。

スピルには、
・他の対象範囲への数式コピーが不要
・付随して、参照形式の変更が不要
・スピル範囲演算子でスピルデータの参照が容易
・スピル範囲を参照しておけば元データの増減や変更にも即対応
・メンテナンス対象は先頭の動的配列数式だけ

といったメリットがあります。 

本日触れたUNIQUE関数のように、スピルの効力を活かした関数が段階的に実装されていますので、今後の記事でそれぞれの関数の使用例を取り上げていきます。

ぜひチェックしてくださいね！

↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。

↓↓Excel操作をとにかく高速化したい方へ↓↓

↓↓実務直結の関数活用術を網羅的に学びたい方へ↓↓