エクセルの新しいエラー「#SPILL!」って何？ スピル機能を使った複数セルの一括計算

　Excelの操作中に「#N/A」「#DIV/0!」「#REF」のエラーがたまに表示されますよね。それぞれ「値がない」「0で割っている」「参照先が無効」といったエラーです。ほかにも「#NAME?」「#NULL」「#NUM!」「#VALUE!」といったエラーもあります。

　見慣れた（？）エラーが表示されてもそれほど焦りませんが、「#SPILL!」というエラーに驚いた人もいるのではないでしょうか。これは、Excel 2021/2019とMicrosoft 365のExcelに実装された「スピル」の機能が正しく動いていないことを意味します。

　以前の記事で紹介したFILTER関数やSORT関数、XLOOKUP関数などのスピルに対応する関数や数式の結果が表示される範囲に、データが存在する場合などに表示されます。

「#SPILL!」エラーの例。スピルの結果が表示される範囲に既存のデータがあると表示される

　「スピル」（SPILL：あふれる、こぼれる）は、ひとつのセルに入力した数式から、複数の結果をまとめて取得できる機能です。本稿執筆時点でExcel 2021/2019とMicrosoft 365のExcelのみの対応となりますが、従来のExcelと使い勝手がひと味違います。今回は簡単にスピルの動作を紹介します。

スピルを使って複数の列を一括で計算する

　スピルを利用するとセル範囲を参照して、演算の結果をまとめて取得できます。実際に動きを見てみましょう。例えば、複数行に掛け算を入力する際、最初のセルに数式を入力しますよね。以下の例では「単価」と「数量」を掛け合わせた「金額」を計算します。

　従来の単一の数式「=C3*D3」を入力してからコピーする必要はありません。スピルで処理する場合は「=C3:C6*D3:D6」とひとつの式を入力するだけ済みます。

「単価」と「数量」を掛け合わせた「金額」をスピルで計算する。セルE3に「=C3:C6*D3:D6」と入力する

セルE11のほか、セルE12～14にも計算結果が表示された

　「=C3:C6*D3:D6」といった記述方法に慣れる必要がありますが、簡略化できるのは確かです。配列×配列の計算に利用する定番のSUMPRODUCT関数も使わなくなりそうです。

　スピルによって表示されたセル範囲はうっすらと立体表示されることがわかります。セルE12～E14にはどのような状態になっているのか見てみましょう。

セルE12を選択した状態。セルE11に入力した数式が灰色で表示されていることがわかる

セルE11を選択した状態。数式は黒色で表示されている

　セルE12～E14は「動的配列」や「ゴースト」と呼ばれ、数式は挿入されずに自動的に演算された結果が表示されます。数式を修正する場合は、最初のセル（ここではセルE11）を編集することになります。

VLOOKUP関数と組み合わせる

　スピルによって、VLOOKUP関数の記述も簡略化できます。「商品No」から「商品名」と「単価」を取得する例を考えて、以下では、商品マスタとして入力されているセルA15～C24のデータを参照します。

　従来のVLOOKUP関数の使い方は、セルB3に「=VLOOKUP(A3,$A$16:$C$24,2,FALSE)」と入力。参照するセル範囲がずれないように絶対参照にしてからコピー、と操作していました。

従来はセルB3に「=VLOOKUP(A3,$A$16:$C$24,2,FALSE)」と入力する

　しかし、スピルでは違います。入力する数式は「=VLOOKUP(A3:A6,A16:C24,2,FALSE)」のみ。絶対参照も必要ありません。ひとつめの引数「A3:A6」は不思議な感じがしますが問題ありません。結果はスピルで表示されます。

セルB3に「=VLOOKUP(A3:A6,A16:C24,2,FALSE)」と入力して［Enter］キーを押す

セルB3～B6までスピルで結果が表示された

　残念ながら横方向へはスピルしません。「単価」列を取得するセルC3には「=VLOOKUP(A3:A6,A16:C24,3,FALSE)」と入力する必要がありますが、2つの数式を入力するだけで結果を取得できるメリットは大きいですね。

セルC3に「=VLOOKUP(A3:A6,A16:C24,3,FALSE)」と入力した

スピルの結果を参照して計算する

　最初に紹介した「単価」と「数量」を掛け合わせる「金額」の計算について振り返ってみましょう。セルE3には「=C3:C6*D3:D6」と入力しましたが、別の記述方法もあることを覚えておいてください。

　「単価」列をスピルで取得した場合、スピルの結果が表示される動的配列のセルC3～C6は「C3#」と表現できるため、セルE3には「=C3#*D3:D6」としても同じ結果が得られます。

セルE3の数式を「=C3#*D3:D6」と修正する

セルE3～E6に計算結果が表示された

　「C3#」のような動的配列を参照する表現は活用範囲が広く、スピルの結果のセル範囲が広がれば、自動的に参照する範囲も広がります。つまり、範囲が可変のデータにも対応可能なのです。

　古いバージョンのExcelで作業する場合、スピルを利用した数式は自動修正されるか、エラーとなるかのどちらかです。複数のバージョンが混在する環境では、スピルの利用はおすすめできません。しかし、スピルの登場により、Excelの使い方は変わっていくと思います。最新のExcelをお使いなら、従来とは異なる記述方法に慣れておいてはいかがでしょう。

［2023年1月23日編集部注］ Excel 2019でスピルに対応しているのはXLOOKUPやFILTERといった関数に限られます。配列の計算やSORT関数などは非対応です。