Excel のスピルと配列数式

Excel では、これまで使われてきた静的配列数式に加え、スピルという機能による動的配列数式が使えます。スピルの基本的な考え方や LINEST 関数などでの使い方について、簡単にまとめました。

本サイトで紹介しているテキストでは、Excel の配列数式が利用されています。そのため、本サイトで提供している PDF の解説の中にも、配列数式が頻繁に出てきます。
配列数式は、複数の値をまとめて計算する方法です。Excel 2019 以前では、関数の入力時、Ctrl+Shift+Enter の3つのキーを同時に押して確定します。３つのキーの頭文字から「CSE 数式」とも呼ばれます。
これに加えて、Microsoft 365 の Excel や Excel 2021 では、配列の処理を自動的に行う「スピル」という機能が追加されました。これに合わせて新しい関数も追加されました。
このスピルを使う方法が「動的配列数式」、以前からある方法が「静的配列数式」です。

なお、Microsoft サポートにおいて、静的配列数式は「下位互換の理由で引き続きサポートされていますが、今後は使用しないでください」とアナウンスされています。スピルが使える Excel であれば、動的配列数式を優先して使うべきでしょう。

配列数式の利用：SUM 関数の例

配列は、同じ型のデータを連続的かつ多次元に並べたものですが、ここでは数値が一方向に一列に並んだ１次元配列を考えます。たとえば、{10, 16, 23}、 {5, 15, 5} などです。
SUM 関数を例にして、配列数式を説明します。SUM 関数は、引数（ひきすう）で指定したセル範囲にある数値の合計を返します。

表示１の (1) では、単価の A 列と個数の B 列を乗算して、その戻り値を金額の C 列に表示しています。C 列には数式が入力してあります。たとえば、セル C2 の数式は「=A2*B2」で、戻り値は 50 です。これらの金額の合計を SUM 関数で得ています。セル C5 の数式は「=SUM(C2:C4)」で、戻り値は 325 です。引数は「C2:C4」、すなわち「C2,C3,C4」の３つのセルの数値であり、{50, 160, 115} という1つの配列です。
なお、表示の中のオレンジ枠は、強調のために付けたものであり、実際の表示ではありません。以下、同様です。

表示１の (2) では、金額の列を作成しないで、セル C5 の SUM 関数で金額の合計を計算しています。SUM 関数の引数は、「A2:A4*B2:B4」です。これは「セル範囲*セル範囲」、すなわち、「配列×配列」になっていて、戻り値は１つです。また、数式の両端には中括弧 { } が付いています。これは、静的配列数式です。

表示１の (3) は、(2) とほぼ同じです。異なるのは、数式の両端に中括弧 { } が付いていません。これは、スピルという機能を使った動的配列数式です。
これらの入力方法は、この後で説明します。

配列数式の利用：演算子「*」の例

乗算する演算子「*」を例にして、配列数式を説明します。

表示２の (1) では、単価の A 列と個数の B 列を乗算して、その戻り値を金額の C 列に表示しています。たとえば、セル C2 の数式は「=A2*B2」です。セル C5 の数式は「=A5*B5」です。１行ずつ別々の数式を入力します。

表示２の (2) では、セル範囲 C2:C5 に同一の数式「=A2:A5*B2:B5」が入力してあり、(1) と同様の戻り値が得られています。これは「セル範囲*セル範囲」、すなわち、「配列×配列」になっていて、戻り値も配列で返されています。数式の両端には中括弧 { } が付いていますから、静的配列数式です。

表示２の (3) では、セル C2 に数式「=A2:A5*B2:B5」が入力してあります。その下のセル範囲 C3:C5 の１つのセルをクリックすると、セル C2 と同じ数式が表示されます。しかし、その表示は薄い色です。この部分は「ゴースト」と呼ばれており、実際には数式が存在しません。そのため、ゴーストの編集や、ゴースト内へのセルの挿入・削除はできません。数式の両端に中括弧 { } が付いていませんから、動的配列数式です。
次の項では、この入力方法を説明します。

静的配列数式と動的配列数式の入力方法

表示３で、乗算する演算子「*」を例にして、２種類の配列数式の入力方法を説明します。

左側に、静的配列数式の入力方法 (1) (2) (3) を示します。
(1) 結果を受ける配列を確保するために、C 列の C2:C5 をあらかじめ範囲指定します。
(2) 数式「=A2:A5*B2:B5」を入力して、Ctrl+Shift+Enter の３つのキーを同時に押して確定します。数式の両端に中括弧 { } が自動的に付きます。
(3) あらかじめ範囲指定した C2:C5 に戻り値が表示されます。

右側に動的配列数式の入力方法 (1) (2) (3) を示します。
(1) セル C2 を選択します。このセル C2 は、数値が入力してあるセル B2 に隣接しています。
(2) 数式「=A2:A5*B2:B5」を入力して、Enter キーを押して確定します。
(3) セル範囲 C2:C5 に戻り値が表示されます。このような動作をスピル（spill）といいます。これは「あふれる」「こぼれる」という意味で、セル C2 に入力した数式が下の B3:B5 のセルにこぼれるという意味のようです。戻り値が表示されたセルを選択すると、範囲を囲む枠が表示されます。

左側の静的配列数式では、戻り値を表示するセル範囲をあらかじめ指定することにより、戻り値を受ける配列のサイズが実行時に決まっています。一方、右側の動的配列数式の場合、実行時に該当するセルが自動的に検出され、それに合わせて戻り値を受ける配列のサイズが変化します。これが「静的」「動的」という意味です。

LINEST 関数の入力方法

Excel 関数の中には、LINEST 関数、FREQUENCY 関数など、戻り値を配列で返すことが前提になっている関数があります。ここでは、LINEST 関数を例にして入力方法を説明します。
表示４では、静的配列数式と動的配列数式で LINEST 関数を実行しています。LINEST 関数は、線形回帰モデルの解を返す関数です。例として、x 列と y 列の２つの配列から、単回帰分析の解を得ます。

左側に静的配列数式の入力方法 (1) (2) (3) を示します。
(1) 戻り値を表示する範囲 D2:E6 を指定します。モデルは１次式なので、傾きと定数の２列が必要です。そのため、５行×２列の２次元配列を確保します。
(2) LINEST 関数を入力します。引数は、y の範囲、x の範囲、カンマ２つの後に TRUE を指定して、Ctrl+Shift+Enter キーを同時に押して確定します。なお、カンマ２つを続けて入力して、引数を１つ省略しています。数式の両端に中括弧 { } が自動的に付きます。
(3) あらかじめ範囲指定した D2:E6 に戻り値が表示されます。

右側に動的配列数式の入力方法 (1) (2) (3) を示します
(1) セル D2 を選択します。このセルは、出力しようとする領域の左端のトップに位置します。
(2) LINEST 関数を入力します。引数は静的配列数式と同じですが、Enter キーのみを押して確定します。
(3)  戻り値が D2:E6 に表示されます。出力範囲が自動的に５行２列に設定されます。静的配列数式ではあらかじめセルを範囲指定しますが、動的配列数式の場合はその設定は不要です。戻り値が表示されたセルを選択すると、範囲を囲む枠が表示されます。

なお、LINEST 関数の出力は数値のみです。表示５のように、周囲にコメントを付けると出力の意味が分かりやすくなります。あらかじめ作成しておいたコメントをコピーする方法が簡便です。
なお、LINEST 関数の詳しい使い方は、当サイトで紹介しているテキストを参照してください。一例を下に示します。
第１部　§4.3 回帰モデルとモデルの推定 PDFファイル
第２部　§2.3 ダミー変数による質的因子の効果の推定 PDFファイル

動的配列数式の結果の参照方法とスピルの機能停止

表示６のように、C2:C5 を別のセルで参照する場合、空白のセル C8 に「=C2:C5」を入力するだけで完了します。C9:C11 の部分はゴーストです。これもスピルの機能を使っています。
また、C2:C5 が動的配列数式で得られていることを利用すると、シャープ記号を使って、セル C13 に「=C2#」を入力するだけで完了します。参照元のセル範囲のサイズを意識することなく実行できます。
セル C18 のように、「=SUM(C2#)」と入力するだけで合計が求められます。

スピルは、周囲のセルの状況や関数に合わせて自動的に機能するため、極めて便利です。しかし、「自動的に」というとは「勝手に」という側面があります。使用場面によってスピルの機能を止めたい場合、「=@SUM(・・・」のように、関数の前にアットマークを入れると、スピルの機能は停止します。

スピルの機能は、Excel の作業効率を大幅に向上させます。ここでは、LINEST 関数などでの使い方を中心に、スピルの機能のほんの一端を紹介しました。
（2024年2月4日）