Excelの新しい常識!?「スピル」の使い方

本連載は今回で最終回となる。これまでに色々な関数の使い方や活用方法を紹介してきたが、最後にExcelの「スピル」について詳しく説明しておこう。スピルは“数式や関数の使い方"に大きな影響を及ぼす機能となる。Excelの未来を大きく変える可能性を秘めているので、その使い方を必ず学んでおく必要がある。

スピルを使った数式の入力

最新版のExcel 2021やOffice 365には「スピル」と呼ばれる機能が実装されている。スピル(spill)は、日本語に翻訳すると「こぼれる」とか、「あふれる」といった意味を持つ単語だ。この機能を利用すると、数式や関数を入力したセルの周囲に“あふれる"ように“複数の結果"を出力することが可能となる。

• 

  Excelの新機能「スピル」の使い方

簡単な例を交えながら使い方を紹介していこう。まずは、従来通りの手法で計算を行った例だ。ここでは単価が380円の「コーヒー」の料金早見表を作成する。以下の図のように数式を入力する。

• 

  380円×「個数」を計算する数式

続いて、この数式をオートフィルでコピーすると、料金の早見表を作成できる。

• 

  数式をオートフィルでコピー

• 

  380円×「個数」の計算結果

これと同様の作業を、オートフィル(数式のコピー)を使わずに実現する機能がスピルとなる。今度は「ビール」の単価を550円として話を進めていこう。以下の図に示したように「=550＊B4:B13」という数式を入力する。

• 

  550円×「個数のセル範囲」の数式を入力

「Enter」キーを押して数式を実行すると、数式を入力したD4セルだけでなく、D5～D13のセルにも計算結果が表示されるのを確認できる。つまり、1つの数式で10個の計算結果を得られた訳だ。

• 

  スピル出力された計算結果

このように、周囲のセルに“あふれる"ように結果を出力できる機能が「スピル」となる。先ほどの例について、もう少し詳しく説明していこう。

通常、数式を記述するときは、「=550＊B4」のように“数値"や“セル"を記述するのが一般的だ。一方、先ほど示した数式(=550＊B4:B13)には「B4:B13」という“セル範囲"が記述されている。

このように数式内に“セル範囲"を記述した場合は、その範囲内にある各セルについて個別に計算が行われていく仕組みになっている。「=550＊B4:B13」と記述した場合、「=550＊B4」、「=550＊B5」、「=550＊B6」、……、「=550＊B13」という10個の計算が行われることになる。

当然ながら、その計算結果も10個になる。これらは隣接するセルに“あふれる"ように出力されていく。今回の例では、数式内のセル範囲がB4、B5、B6、……と下方向に変化していくので、計算結果も下方向に“あふれる"ように出力されていく。これが「スピル」の基本的な考え方となる。

理解を深めるために、別の例も紹介しておこう。以下の図は、各項目の「割合」を求める数式をスピルを使って入力した例だ。今回は数式内に「C3:C7」という“セル範囲"が含まれている。

• 

  「回答数のセル範囲」÷「合計」の数式を入力

この場合、「=C3/C8」、「=C4/C8」、「=C5/C8」、……、「C7/C8」という計算が行われることになる。その結果は以下の図の通り。たった1つの数式で、各項目の「回答数」÷「合計」を一気に算出することが可能となる。

• 

  スピル出力された計算結果

これを従来の方法で計算する場合は、「=C3/$D$8」のように絶対参照を使って数式を入力し、それをオートフィルでコピーする、という手順を踏まなければならない(詳しくは前回の連載を参照)。一方、スピルを使った場合は、数式をコピーすることなく、すべての結果を得ることが可能となる。

「数式をコピーしなくても済む」ということは、「絶対参照で記述しなくてもよい」ということであり、それだけ数式の記述は容易になる(見やすくなる)。これもスピルの大きなメリットといえるだろう。

「単価」と「数量」のように、2つの“セル範囲"を指定して計算を行うことも可能だ。以下の例は「C3:C9」と「D3:D9」のセル範囲を掛け算した例だ。

• 

  「セル範囲」×「セル範囲」の数式を入力

この場合、それぞれ対応するセルが順番に掛け算されていく。具体的には「=C3＊D3」、「=C4＊D4」、「=C5＊D5」、……、「=C9＊D9」といった計算が行われ、その結果がスピル出力されることになる。

• 

  スピル出力された計算結果

この方法でも、各行について「単価」×「数量」を計算することが可能だ。従来の方法のように数式をコピーする必要はない。

なお、ワークシートの7～9行目は「単価」と「数量」が空白になっているが、この場合も計算は行われる。空白セルは0(ゼロ)として扱われるので、0×0＝0の計算結果がスピル出力されることになる。

最後に、「1行」と「1列」のセル範囲を数式で計算した例を紹介しておこう。以下の図は、先ほど紹介した料金早見表に「枝豆」と「ポテト」を追加したものだ。今回は、それぞれの単価を“数値データ"としてC4～F4セルに入力してある。

この場合、それぞれの「単価」(C4:F4)と「個数」(B5:B13)を掛け算することで「単価」×「個数」の計算結果を2次元的に得ることができる。

• 

  「セル範囲」×「セル範囲」の数式を入力

• 

  スピル出力された計算結果

同様の処理を「数式のコピー」で行う場合は、「行」または「列」だけを固定する絶対参照を使用しなければならない(詳しくは前回の連載を参照)。一方、スピルを使用する場合は、それぞれのセル範囲を掛け算するだけで正しい計算結果を得られる。絶対参照の指定方法に頭を悩ます必要はない。

スピルを使った関数の入力

数式だけでなく、関数でスピルを使用することも可能だ。こちらも具体的な例を使って説明していこう。

以下の図は、「過不足」の状況を確認する関数IFを入力した例だ。「過不足」が0より大きい場合は“OK"、そうでない場合は“在庫不足!!"という文字を表示するように引数を指定してある。

• 

  関数IFの条件式に「セル範囲」を指定

ここで注目すべきポイントは、第1引数となる「条件式」に“セル範囲"が記述されていることだ。通常、関数IFの条件式は、1つの値(セル参照)を対象に判定するのが基本だ。一方、先ほどの例では「E3:E7」というセル範囲が指定されている。

この場合、「E3>0」、「E4>0」、「E5>0」、「E6>0」、「E7>0」という5つの判定が行われ、それぞれの真偽に応じた処理結果がスピル出力される。

• 

  スピル出力された関数IFの結果

このように、本来であれば値(セル参照)を記述すべき部分に“セル範囲"を指定することで、複数の結果をまとめてスピル出力できるケースもある。従来の手法のように、関数をオートフィルでコピーする必要はない。

スピル範囲演算子「#」の活用

先ほど示した例は「過不足」の計算にもスピルが活用されている。念のため、その数式を紹介しておこう。

• 

  「過不足」を計算する数式

実際には「=C3-D3」、「=C4-D4」、「=C5-D5」、……、「=C7-D7」の計算が行われ、その結果が「E3:E7」のセル範囲にスピル出力される。この場合、関数IFに指定するセル範囲を「E3#」と記述しても構わない。

• 

  スピル範囲演算子「#」を使ったセル範囲の指定

「#」の記号は「スピル範囲演算子」と呼ばれるものだ。上図の場合、E3セルに入力した数式(関数)により「スピル出力されたセル範囲」として処理される。つまり、「E3:E7」のセル範囲と同じ扱いになる。

このため、関数IFの記述は「=IF(E3:E7>0,"OK","在庫不足!!")」と解釈されることになる。関数IFの内容は同じなので、先ほどと同じ結果がスピル出力されることになる。

• 

  関数IFの結果

このように、「スピル出力した結果」を使って別の処理を行うときに「スピル範囲演算子」を活用できるケースもある。上手に使えば、セル範囲を“状況に応じて可変"にすることも可能なので、あわせて覚えておくとよいだろう。

スピル出力が前提になる関数

ここ2～3年の間に新しく登場した関数は、スピルを使うことを前提に設計されているものもある。その代表例が、XLOOKUP、FILTER、UNIQUE、SORTといった関数だ。

ここでは、関数FILTERを使って「条件に合致するデータ」を抽出する例を紹介しておこう。データを取得する範囲は「A2:D27」で、この中から「氏名」(C2:C27)がG2セルに一致するデータだけを取得している。

• 

  関数FILTERの入力

今回は、条件(G2セル)に「酒井 真洋」を指定しているので、氏名が「酒井 真洋」の行について、「No」、「日付」、「氏名」、「購入金額」のデータがスピル出力されることになる。

• 

  関数FILTERの出力結果

続いて、条件を「弓削 英範」に変更した例を紹介しておこう。この場合は、3件のレコードが条件に一致するので、「No」、「日付」、「氏名」、「購入金額」のデータがそれぞれ3個ずつ、計12個のデータがスピル出力されることになる。

• 

  検索条件を変更した場合

このように「出力されるデータの個数」が状況に応じて変化するケースもある。この場合、スピル出力されるセル範囲の書式に注意しておく必要がある。

上図に示した例では、1行目だけが「太字」で表示されており、2行目以降は「通常」の文字になっている。また、「日付」がシリアル値で取得されていることにも対応しなければならない。

セルの書式と「#スピル!」のエラー

スピル出力されたデータは、各セルの書式に従って表示される仕組みになっている。先ほど示した例では、出力先の1行目だけに「太字」を指定していた。このため、1行目だけが「太字」で表示され、2行目以降は「通常」の文字で表示される。

スピル出力された結果を適切に表示するには、あらかじめ「出力先のセル範囲」に書式を指定しておく必要がある。以下の図は、出力先として10行分のセル範囲に太字と罫線を指定し、G列に「日付」、I列に「通貨」の表示形式を指定した例だ。

• 

  スピル出力されそうな範囲に書式を指定

このようにセルの書式を指定しておくと、「スピル出力されたデータ」を適切な形で表示できるようになる。

そのほか、スピル出力されるセル範囲を「空白セル」にしておく必要もある。スピル出力されるセル範囲に“何らかのデータ"が入力されていた場合は、「#スピル!」のエラーが発生してしまう。

• 

  スピル出力する範囲にデータが入力されていた場合

上図の例では、H6セルに「Name」というデータが入力されているため、正しくスピル出力できないことを示す「#スピル!」のエラーが発生している。このエラーを解消するには、「Name」のデータを削除し、スピル出力されるセル範囲(緑色の点線で囲まれた範囲)をすべて「空白セル」にしておく必要がある。

関数FILTERのように「何行分のデータが出力されるか？」が不明な関数を使用するときは、十分なスペースを「空白セル」として確保しておかなければならない。忘れないように注意しておこう。

ということで、本連載はこれで終了となる。最後に紹介したのは「関数」ではなく、「スピル」となったが、今後、Excelの使い方を大きく変える可能性を秘めているので、必ず理解しておくこと。

ここ10年くらいで、Excelは「2013」→「2016」→「2019」→「2021」とアップグレードを繰り返してきたが、「あまり変化を感じられない」、「古いExcelでも十分に使える」と考えている方も沢山いるだろう。しかし、その状況は一変するかもしれない(サブスク版のMicrosoft 365は常に最新の機能が提供されているため、あまり関係のない話だが……)。

買い切り版のExcel 2021とExcel 2019の間には、「スピルを使える／使えない」という大きな断絶がある。

今後、スピルがどれくらい積極的に活用されていくかは不明だが、もしも「スピルを使えるのが当たり前」という時代になった場合、「Excel 2019以前では業務に支障が出る……」となる可能性も否めない。

それ以前の問題として、スピルの使い方を知らない人は、いつのまにか時代から取り残されてしまう恐れがある。Excelに慣れている中級者の方も、今のうちに「スピル」の“考え方"と“使い方"をマスターしておくとよいだろう。

スピルは便利な機能であるが、古いExcelとの互換性を考慮して「今は使用を控えている……」という方も多いかもしれない。とはいえ、このような配慮は時間の経過とともに減少していくはず。よって、今のうちからスピルに慣れておく必要がある。