Excelの新関数には新機能の「スピル」が使われています。「スピル」機能によって数式の常識が変わったと言われています。これまではセルに数式を入力すると、そのセルに計算結果の値を表示するというのが常識でした。それでは、この「スピル」とはどういうものか概略を見ていきましょう。
新しい関数を解説していけば「スピル」の機能に対する理解がさらに深まっていくものと思います。
「スピル」を活用している新しい関数は、「XLOOKUP」「FILTER」「SORT」「SORTBY」「UNIQUE」「SEQUENCE」「RANDARRAY」などOffice 2021にも追加された関数です。
※関数名にリンク先が設定されている場合は、そこから関数の紹介記事へジャンプできます。
「スピル」が機能するのは、サブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021です。
新しく追加されている関数とあわせて「スピル」をしっかり理解しておきましょう。
なんだか難しそうだけど、ここを理解できていないと先にすすめなくなりそうですね(^^ゞ
よろしくお願いします。
【この記事でわかることは】
・「スピル」とはどういう意味？どういう機能なのかがザックリと概略を解説
「スピル」とは
「スピル」の英単語＝ spill を調べると、自動詞としては「こぼれる」「あふれる」「はみでる」などの意味があります。
スピルの動作を確認してみます
次の画像は、テストのために初めて動的配列数式を入力してみた時のものです。
B1セルに「=A1:A4」を参照する式を入力してみました。
初めてだったので「数式がスピルされています」というメッセージが表示されました。
式はB1だけに入力していますが、B2～B4のセルに数式がスピルされているのが確認できます。表示された「詳細表示」ボタンをクリックしたら下のヘルプが表示されました。
スピルとは、数式が複数の値を返し、それらの値が隣接するセルに配置されたという意味です。
・B列の数式を見てください
・B1に入力した数式が下にスピルしています
・数式バーでB2以下では式が「ゴースト化」され薄く表示されています
・B2セルの数式を消すとすべてが消えます
・スピル領域内の最初のセルだけが編集可能です。ゴースト化されているセルは変更できません。入力すると #SPILL! エラーになります
・範囲を選択して数式入力後Ctrl+Shift+Enter で作る従来のCSE 配列数式 {=A1:A5} と似ていますが、スピルと比較した注意点がヘルプに記載されていました（左中画像と下GIF画像）
・「スピル」は範囲を選択する必要はありません
・動的配列数式「スピル」はソース範囲のデータが追加または削除されると、サイズが自動的に変更されます
・ヘルプに「CSE 配列数式は、互換性の理由で保持されます。 今後は、代わりに動的配列数式を使用する必要があります」と記載されていました
スピル範囲演算子(#)
スピル配列関数を処理する場合は、スピル範囲演算子 (#) を範囲の後に配置することで、スピル範囲全体を参照できます。 下の例は、上の二つのGIF画像右側に配列演算子(#)付きで範囲を合計する計算式を設置していたものです。
「セルE2」には “=SUM(B2#)”
B列の「スピル」の合計
「セルF2」には “=SUM(C2#)”
C列の「配列数式」の合計
スピル範囲演算子(#)付きにしてあります。
下側の画像は「セルA4」にセルを挿入して下方向にシフトした画像です。
それぞれの合計値をご確認ください。
「スピル合計」は範囲が広がっていることがわかると思います。一方「配列数式合計」は範囲が固定のままです。
E2 の数式を “=SUM(A2:A5)” と通常の範囲で指定したの場合でも、上の例のような挿入などの場合は範囲が自動で変化しますが、FILTER関数 や SORT関数 などの新しい関数でスピル範囲が変化した場合には手動で更新する必要があります。スピル範囲演算子(#)を使用することで、範囲が自動更新されるので、範囲の変更に気付く必要が無くなります。これはすごく便利で助かりますね。
＃SPILL! エラー
＃SPILL! エラーはどんな場合に発生するのでしょうか。
スピル範囲が空白ではない場合
左の動画ではB3セルに数値200を入力したところ #SPILL! エラーが発生しています。スピル範囲内に空白でないセルがあった場合も同様に #SPILL! エラーとなります。
スピル範囲に結合セルがある場合
B4:C4セルを結合していますが、同じように #SPILL! エラーが発生しました。
スピル範囲に結合セルがあると #SPILL! エラーとなります。結合セルが空白の場合でも同様です。
テーブルではスピルは使えない
テーブルではスピルした配列数式は #SPILL! エラーになります。 数式をテーブル外に移動するか、テーブルを範囲に変換して回避します。
ワークシートの端を超えて拡張する場合
B2セルに “=A:A” のように列全体を範囲に指定した場合では #SPILL! エラーになります。
Excel最大行の1,048,576を超えてしまったため発生しています。
C1セルの “=A:A” では1,048,576丁度なのでエラーになりません。
このほかにも、エラーが発生するケースはあるようですがひとまず覚えておきたいものを紹介しました。
まとめ（おわりに）
以上、Excelの新機能「スピル」についての概略だけですが解説してみました。
「スピル」を活用している新しい関数の詳しい解説を順次行うようにしていきます。その際に改めて「スピル」の機能をもっと詳しく知ることができと思います。
「スピル」は、今はまだ対応していないExcelを使っている方でも、いずれは使うことになる機能です。事前に知っておいて損はありません(^^)/
今回はザックリとしか解説していませんが、必要が生じた場合は補足・追記していきたいと思います。
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
