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次元的に得ることができる。

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

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

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