スピルの意味

これまで作成した数式の多くは、1つのセルに1つの値を返していました。動的配列を使うと、この仕組みが変わります。1つの数式でリスト全体や表形式の結果を返すことができ、スプレッドシートが各結果をそれぞれのセルに自動配置します。

この自動配置はスピルと呼ばれます。数式を一度入力してEnterキーを押すと、トレイに水を注ぐように、結果が周囲のセルへ流れ出します。

A1:A5に1から5までの数値が入っているとします。セルC1に=A1:A5*10と入力してEnterキーを押します。

1つの結果ではなく、10、20、30、40、50の5つの結果がC1:C5に縦に並びます。数式を入力したのはC1だけですが、結果は自動的にその下のセルへスピルします。

数式が値を埋めるセルの範囲をスピル範囲と呼びます。実際に数式が入っているのは、アンカーと呼ばれる左上のセルだけです。ほかのセルは自動的に埋められ、アンカーを選択すると薄い青色の枠が表示されます。

スピルしたセルを直接編集することはできません。編集しようとするとエラーになります。出力を変更するには、アンカーセルの数式を編集します。

スピル範囲は拡大または縮小する可能性があるため、結果全体をスピル演算子で参照します。これはアンカーのアドレスの後ろにハッシュ記号を付ける方法です。

C1の数式がC5までスピルする場合、C1#はスピル範囲全体を意味します。=SUM(C1#)のように別の場所で使えば、後からスピル範囲が拡大しても、常にすべてのスピル値が合計に含まれます。

スピルの本当の強みは、動的であることです。元の範囲に新しい数値を追加すると、スピル範囲はそれを含むように自動的に拡大します。値を削除すれば、スピル範囲は縮小します。

数式を下方向へコピーする必要はもうありません。1つのアンカー数式が、読み取るデータに合わせて出力のサイズを調整します。

多くの最新の関数は配列を返すように設計されているため、スピルします。

• FILTERは一致する行を返します。
• UNIQUEは重複のないリストを返します。
• SORTとSORTBYは範囲の順序を並べ替えます。
• SEQUENCEは数値のリストを生成します。

このコースではFILTERとUNIQUEを学びます。どちらも1つの数式から複数の結果を出力します。

列Aに地域名としてEast、West、East、North、Westが並んでいるとします。重複のない地域名のリストを作るには、数式を1つ入力します。

=UNIQUE(A2:A6)

East、West、Northの3つの結果がスピルします。元の範囲に別の地域を追加すると、スピルしたリストも自動的に拡大します。1つの数式で、常に最新のリストを作成できます。

スピルは下方向だけに限られません。数式は列方向にスピルしたり、2次元の表全体を埋めたりすることもできます。

たとえば=SEQUENCE(3,4)は、縦3行、横4列の範囲にスピルし、1から12までの数値で12個のセルを埋めます。方向と形状は、数式が生成する配列によって完全に決まります。

動的配列とスピルは、Excel for Microsoft 365およびExcel 2021以降で利用できます。Google Sheetsも、長年にわたって配列のスピルをサポートしています。

Excel 2016や2019などの古いバージョンではスピルしません。その場合、配列数式はCtrl+Shift+Enterで確定する必要があり、自動的に拡大することもできません。数式がスピルしない場合は、まず使用しているバージョンを確認してください。

動的配列が登場する前は、1つのセルに=B2*C2と入力し、何百行も下へドラッグしていました。スピルを使えば、一度入力するだけで済みます。

=B2:B100*C2:C100という数式は、範囲全体に行ごとの結果を1つずつスピルします。読む対象や修正対象が、同期がずれる可能性のある何百ものコピーではなく1つの数式だけになるため、保守が簡単になります。

スピルする数式を、1つの数値だけでなく、データの形状を出力する小さなプログラムだと考えてみましょう。スプレッドシートは、その形状をアンカーセルから始まるグリッド上に配置します。

必要なのは、その形状を配置できる空きスペースを確保することです。次のレッスンでは、FILTERとUNIQUEを使って役立つ形状を作る方法と、配置先がふさがっている場合の対処方法を学びます。

動的配列の数式は複数の結果を返すことができ、スプレッドシートはそれらをスピル範囲に自動的に配置することを学びました。

• 数式を保持するのはアンカーセルだけです。
• C1#のように#演算子を使ってスピル範囲全体を参照します。
• スピル範囲は元のデータに合わせて自動的に拡大・縮小します。
• FILTER、UNIQUE、SORT、SEQUENCEなどの関数はすべてスピルします。

次はFILTERを使って、必要な行だけを返す方法を学びます。