Excel2021のバージョンから新登場した「スピル」機能をご存じでしょうか。
エクセルの「スピル」機能は、数式を挿入したセル以外にもその結果を拡張してくれる革新的な機能です。
例えば、下の図のように、D2セルに「=B2:B6」と入力すると、下のセルにもB列の値が自動で拡張されるこの現象のことを「スピル」といいます。
この記事では、「スピルとは何なのか？」「スピルの基本的な操作方法」「関数とスピルを組みわせた事例」を紹介します。
スピルが実装された関数も最近多く登場してきています。今後、Excelの必須スキルになるので、この機会にぜひ習得してください。
スピルが利用できるExcelバージョンと活用できる関数
スピルが利用できるExcelバージョンは、Microsoft365(サブスク)とExcel2021以降のバージョンです。
また、スピル機能を活用した関数として、以下の６つの関数がすでに登場しています。(2024/11月時点)
スピルが使える関数
これらの関数でスピルを使った簡単な事例を後半の見出しで紹介していますが、それぞれの関数の使い方を詳しく知りたい方は、内部リンク記事へジャンプしてください。
Excelのスピルとは？
それでは、スピル機能とは何なのかを解説します。
スピルは英語で「Spill」と書き、「こぼれる」「あふれる」といった意味です。
つまり、エクセルのスピルとは、「数式の結果がセルからあふれて、隣接したセルにも拡張されて出力される機能」です。
下の図のように、D2セルに「=B2:B6」と入力すると、数式を入力していない下のセルにもB列の値が自動で拡張されます。この機能がスピルです。
スピルの基本知識
D2セルに「=B2:B6」と入力すると、スピルが働き、D2セルからD6セルにまでB列の値が入力されます。
これは、D2セルに「=B2」と入力せず「=B2:B6」と入力したことで『「=B3」「=B4」「=B5」「=B6」という結果も欲しいんだよね？』と、エクセルのスピル機能が暗黙的に判断して結果を拡張して表示した、ということです。
また、アクティブセルをD4セルに合わて数式バーを確認すると、数式がグレー表記されていることがわかります。
これを「ゴースト」と呼び、スピルで拡張された数式や結果はD2セル以外からは削除することができません。
スピル範囲演算子
スピルの表示結果が表示されるセル範囲は、どういった数式を使ったか、もしくは元データのセル範囲によって変化します。
このスピルの結果を他の数式でも利用することができるような機能も実装されています。
「スピル範囲演算子 」と呼ばれる「#」の記号 を範囲の後に配置することで、スピル範囲全体を参照できます。
例えば、「=B2:B6」と入力されているD2セルに対して、F2セルに以下のSUM関数を利用します。
=SUM(D2#)
SUM関数はD2セルしか指定していませんが、スピル範囲演算子「#」を使ったことで、スピルのセル範囲D2～D6セルの値も合計されて「31」と表示されます。
スピルを使うメリット
このスピル機能の最大のメリットは、１つのセルに入力するだけで複数のセルに自動で数式の結果が表示されることです。
また、スピル範囲演算子を使うことで、スピルの結果をさらにCOUNTIF関数やSUMIF関数など別の関数に利用することもできます。
スピルのメリット
その１．関数や数式を別のセルにコピーする手間が省ける
その２．時短となり業務効率につながる
その３．スピルの結果をCOUNTIF関数などにも組み合わせて利用できる
『動的配列数式』と従来の『配列数式』とは？
スピルは隣接しているセルに自動で結果が出力されるので、「動的配列数式」とも呼ばれます。
これに対して、スピルが使えない従来の旧エクセルで、スピルのように複数のセルに結果を表示したい場合は、結果を表示したいすべてのセルを選択して、「Ctrl＋Shift+Enter」キーを押す必要がありました。
この機能のことを「配列数式」と呼びます。
例えば、行と列を入れ替えることができるTRANSPOSE関数を配列数式で利用すると以下のようになり、数式は「{}」で囲われます。
{＝TRANSPOSE(B2:E7)}
TRANSPOSE関数を動的配列で使う手順を以下の記事で紹介しているので、スピルとの違いを確認したい方は参考にしてください。
【基本編】スピルを使用する方法
それでは、スピルを使うと、どのように数式の結果が隣接しているセルに拡張されるか実際に試してみてください。
また、スピルの結果を値貼り付けする方法や、エラーが発生する場合の対処方法、スピルを止める方法などの基本操作も説明します。
スピルの使用例その１．行×行、列×列
下の図のように、２行目と３行目のセル範囲の数値をそれぞれ掛け算したい時は、スピルを使った以下の数式を挿入します。
=B2:E2*B3:E3
「Enter」キーを押すと、スピルが働き、自動的に数式の結果が５行目に拡張されて表示されます。
B列とC列のセル範囲の数値をそれぞれ掛け算したい時は、以下の数式を挿入すると、同様に結果がスピルされます。
=B2:B5*C2:C5
参照している数値が同じ行や列になかったとしても、参照しているセル数が同じであれば、「=B2:B5*C3:C6」という数式を挿入しても、正しく結果はスピルされます。
スピルの使用例２．行×列
スピルを使って、行と列の数値をそれぞれ四則演算することも可能です。
例えば、下の表のC3セルに以下の数式を挿入します。
=B3:B7*C2:G2
数式を確定すると、B列と２行目の数値がそれぞれ掛け算されて、数式を挿入したC2セルからG7セルまで結果が表示されました。
このように、スピルは行と列のセル範囲を組み合わせて使用することもできます。
スピルの結果を値貼り付けする方法
スピルによって表示されるセルの値は、通常のセルと同じように値貼り付けしたり、数式で参照することができます。
例えば、下の表のE列の値はスピルされた値ですが、右クリックメニューの値貼り付けやショートカットキーなどを使って、別のセルに貼り付けすることができます。
前半の見出しでも少し触れましたが、スピルの結果を数式や関数で利用することもできます。
スピルされたセルの最下行にSUM関数を挿入すると、「=SUM(E2#)」となり、スピル範囲演算子の記号「#」が自動で付いて、値が合計されます。
スピルでエラーが発生する場合の対処方法
スピルが上手くいかず、エラー値「#N/A」か「#スピル!」が発生する場合があります。
例えば、下の表のB列とC列の値を掛け算するために、E2セルに「=B2:B5*C2:C6」と入力するとエラー値「#N/A」が発生します。
エラーの原因は、「B2:B5」に対して「C2:C6」は１セル分だけセル範囲が広いため、スピルがどのように働いていいのか判断できなくなっている、ということです。
この場合は、数式に使用しているセル範囲を修正すれば、エラーは解消します。
エラー値「#スピル!」が発生するのは、結果が表示されるセル範囲に文字や数値が入力されていることが原因です。この場合は、文字を削除してください。
また補足ですが、スピルエラーは昔は「#SPILL!」という表記でしたが、「#スピル!」という表記に変更されました。
スピルを止める方法(＠の利用)
数式の結果を拡張させて表示させたくない場合、スピルを止める方法があります。
例えば、D2セルに「＝B2:B5*4」と入力すると、下のセル範囲にスピルが働きます。
このスピルを止めるためには、セル範囲を指定している「B2:B5」の先頭に「@」(アットマーク)を入力します。そうすると、結果は拡張されず、「B2×4」の結果だけが表示されます。
＝@B2:B5*4
「=B2:B5*C2:C5」という２つのセル範囲を掛け算した数式の場合は、下の数式のように、それぞれのセル範囲の先頭に@を追加すると、スピルは発生しません。
=@B2:B5*@C2:C5
【応用編】スピルを関数で利用した事例
スピル機能は、関数と一緒に利用することで、その利便性をより感じることが出来ます。
今回は、スピルを使った応用編のテクニックとして、スピルと相性の良い以下の関数の使用例を５つ紹介します。
ポイント
・XLOOKUP関数、SORT(BY)関数、FILTER関数、UNIQUE関数、SUMIF関数
事例１．XLOOKUP関数×スピル
XLOOKUP関数は、VLOOKUP関数の後継関数です。
VLOOKUP関数の使いづらかった弱点を克服した新関数で、スピルと組み合わせることができます。
XLOOKUPの基本的な使い方を知りたい方は、『【エクセル】XLOOKUP関数の使い方を初心者向けに紹介』記事を参考にしてください。
それでは、スピルにXLOOKUP関数を使った事例を２パターン紹介します。
1つ目の事例は、XLOOKUP関数の第１引数「検索値」にスピルを使用する方法です。
下の表に対して、H3セルに以下のXLOOKUP関数を挿入します。通常の使い方だと、第１引数「検索値」は１つのセルのみを指定しますが、「G3:G5」とセル範囲を指定します。
=XLOOKUP(G3:G5,B:B,E:E)
そうすると、スピルが働き、G3セルの商品番号「15」だけでなく、すぐ下の商品番号「18」「78」の値段もまとめて抽出することができます。
２つ目の事例は、XLOOKUP関数の第３引数「戻り範囲」にスピルを使用する方法です。
下の表に対して、G5セルに以下のXLOOKUP関数を挿入します。通常の使い方だと、第３引数「戻り範囲」は１つのセルのみを指定しますが、「G3:E8」とセル範囲を指定します。
=XLOOKUP(G2,B3:B8,B3:E8)
そうすると、スピルが働き、G3セルの商品番号「15」だけでなく、「商品カテゴリ」「商品名」「値段」の情報も一括で抽出することができます。
事例２．SORT関数×スピル
SORT関数とSORTBY関数は、指定したデータを別の空いたスペースで並べ替えする関数です。
SORT関数の書式は以下のとおりで、１つのセルにSORT関数を挿入するだけでスピルが働き、表のデータを並べ替えできます。
＝SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
例えば、左下の表の「生年月日」列を昇順で並べ替えするために、G3セルに以下のSORT関数を挿入します。そうすると、G3セルに隣接したセルにスピルが働き、並べ替えが実行されます。
=SORT(B3:E11,4,1,FALSE)
SORTBY関数は複数の条件で並べ替えする関数で、SORT関数と同じくスピルを使います。
SORT関数とSORTBY関数の詳しい使い方は以下の記事で紹介しているので、関数に興味のある方は参考にしてください。
事例３．FILTER関数×スピル
FILTER関数は、条件に合うデータを抽出する関数です。
FILTER関数の書式は以下のとおりで、１つのセルにFILTER関数を挿入するだけでスピルが働き、データから特定のデータを抽出することができます。
＝FILTER( 配列,含む,[空の場合]）
例えば、左下の表の「D」列の値が「70以上」の値を抽出するために、F3セルに以下のFILTER関数を挿入します。そうすると、F3セルに隣接したセルにスピルが働き、条件に該当するデータが抽出されます。
=FILTER(B3:D11,D3:D11>=70,"")
FILTER関数の詳しい使い方は以下の記事で紹介しているので、関数に興味のある方は参考にしてください。
事例４．UNIQUE関数×スピル
UNIQUE関数とは、１列もしくは複数列からユニークな値のデータを返す関数です。
UNIQUE関数の書式は以下のとおりで、１つのセルにUNIQUE関数を挿入するだけでスピルが働きます。
=UNIQUE(配列,[列の比較],[回数指定])
例えば、下の表のB列の「氏名」からユニークな値を抽出するために、F3セルに以下のUNIQUE関数を挿入します。そうすると、F3セルに隣接したセルにスピルが働き、ユニークな氏名のリストが表示されます。
=UNIQUE(B3:B14)
UNIQUE関数の詳しい使い方は以下の記事で紹介しているので、関数に興味のある方は参考にしてください。
事例５．SUMIF関数×スピル範囲演算子
上の見出しで紹介したUNIQUE関数を使うと、ユニークな値を抽出することができます。
その結果に対してSUMIF関数とスピル演算子「#」を利用することで、ユニークな値の数値の合計を計算することができます。
例えば、UNIQUE関数を挿入した右隣のG3セルに、スピル範囲演算子「#」使った以下のSUMIF関数を挿入します。
=SUMIF(B3:B14,F3#,D3:D14)
この数式は、検索条件の【F3セルからF6セル】の氏名と一致するデータを「B3:B14」から探し、「D3:D14」の範囲内から合計する、という意味です。
そうすると、「石井」さんの算数の合計得点が「205」点といったように、ユニークな氏名の合計得点をすべて表示することができます。