【Excel】まだ間に合う!!「スピル」機能をマスターする★

HARU｜指先で余暇を生み出す仕事術

2023年6月20日 18:00

こんにちは、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#"と記述しスピル範囲を参照する。